Excercises for Relational Databases 2 - IfIS

Technische Universität Braunschweig
Institut für Informationssysteme
http://www.ifis.cs.tu-bs.de
Wolf-Tilo Balke, Christoph Lofi
SQL-Praktikum – 5 - Solution (until 29.01.2009)
Solution:
Exercise 1.a
Write a java method establishing a connection to the DB2 database (running at
is54.idb.cs.tu-bs.de, port 50000, database SQLKURS). Use your own username and password to log in. The connection object is to be returned by the method.
/**
* Connect to database.
*
* @return the connection object
* @throws SQLException
*
when connection failed
*/
public static Connection getConnection() throws SQLException {
// setup URL components
String url = "jdbc:db2://is54.idb.cs.tu-bs.de:50000/SQLKURS";
// your username and password here
String user = "skurs69";
String password = "2sbD94SJ";
// load driver
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
// set custom properties
// as defined above
//
Properties properties = new Properties();
properties.setProperty("user", user);
properties.setProperty("password", password);
properties
.setProperty("retrieveMessagesFromServerOnGetMessage", "true");
// create the connection object
Connection connection = DriverManager.getConnection(url, properties);
return connection;
}
Exercises for Relational Databases 1
Institut für Informationssysteme – TU Braunschweig - http://www.ifis.cs.tu-bs.de
Technische Universität Braunschweig
Institut für Informationssysteme
http://www.ifis.cs.tu-bs.de
Wolf-Tilo Balke, Christoph Lofi
Exercise 1.b
Write another method which reads and prints some metadata of the database.
Following information should be printed (try to format it so that it can be read without getting an headache):



Database product name and major and minor version
Number of available schemas and a list of all schema names
For the schema matching the current user name, a list of all available tables and for each
table, a list of columns and their data type.
Also, if the table has any indexes, they should be listed with their name and the column
they are indexing.
public static void printMetaData(Connection connection) throws SQLException
{
// get the meta data...
DatabaseMetaData meta = connection.getMetaData();
// ...and print the information
// using the format function
//
//
//
//
%s is placeholder for strings
%10s is placeholder for string of length 10
the values are given as parameters, where first occurrence of %s
will be filled with first parameter etc.
// \n is newline
// version
System.out.format("DBMS : %s \nVersion %s.%s\n\n",
meta.getDatabaseProductName(),
meta.getDatabaseMajorVersion(),
meta.getDatabaseMinorVersion());
// schemas
System.out.println("Available Schemas\n-----------------");
ResultSet schemas = meta.getSchemas();
// while there is yet another item in the resultSet
while (schemas.next()) {
// print schema name
System.out.format(" %16s\n", schemas.getString(1));
}
schemas.close();
// Print all own tables
System.out.println("\n\nTABLE INFO \n-----------------");
Exercises for Relational Databases 1
Institut für Informationssysteme – TU Braunschweig - http://www.ifis.cs.tu-bs.de
Technische Universität Braunschweig
Institut für Informationssysteme
http://www.ifis.cs.tu-bs.de
Wolf-Tilo Balke, Christoph Lofi
//get userName
String userName=meta.getUserName();
// get all tables with pattern
// (catalog, schemaPattern, tableNamePattern, types)
// we use only the userName as schemaPattern here
ResultSet tables = meta.getTables(null, userName, null, null);
// for each table
while (tables.next()) {
String tableName = tables.getString("TABLE_NAME");
System.out.format("TABLE : %s\n", tableName);
// Columns
ResultSet columns = meta
.getColumns(null, userName, tableName, null);
while (columns.next()) {
//print name and type of the current column
System.out.format("%17s : %10s\n",
columns.getString("COLUMN_NAME"),
columns.getString("TYPE_NAME"));
}
// Indexes
// meta.getIndexInfo(catalog, schema, table, unique,
approximate)
ResultSet indexes = meta.getIndexInfo(null, userName,
tableName,false, false);
while (indexes.next()) {
System.out.format(" Index: %s (%s)\n",
indexes.getString("INDEX_NAME"),
indexes.getString("COLUMN_NAME"));
}
}
tables.close();
}
Exercise 1.c
Create a new program (which, of course, may use the connect method of a)). After being
started, the program should request the entry of a year of the user. Then, the number of
movies which were released in that year and the average number of actors and the average
number of actresses (read: two averages, one for each gender) of those movies is returned.
The program then asks for a new year and repeats until the user wants to quit.
public class JdbcTask_C {
/**
* A prepared statement for counting the movies in a given year
*
* @param conn
Exercises for Relational Databases 1
Institut für Informationssysteme – TU Braunschweig - http://www.ifis.cs.tu-bs.de
Technische Universität Braunschweig
Institut für Informationssysteme
http://www.ifis.cs.tu-bs.de
Wolf-Tilo Balke, Christoph Lofi
* @return
* @throws SQLException
*/
public static PreparedStatement getStatementMovies(Connection conn)
throws SQLException {
// prepare a string containing the SQL statement
// that expects a year
String sql = "SELECT count(t.title) FROM IMDB.title t ";
sql += "WHERE t.production_year = ?";
// create and return the prepared statement
return conn.prepareStatement(sql);
}
/**
* A prepared statement for counting the average number of actors and
* actresses of all movies released in a given year
*
* @param conn
* @return
* @throws SQLException
*/
public static PreparedStatement getStatementActors(Connection conn)
throws SQLException {
// prepare a string containing the SQL statement
// that expects a year
String sql = "SELECT avg(CAST(numOfActors AS double)),";
sql += "avg(CAST(numOfActresses AS double)) FROM (";
// count actors as 1, others as 0
sql += "SELECT sum(CASE WHEN role_id = 1 THEN 1 ELSE 0
END)numOfActors, ";
// count actresses as 1, other as 0
sql += "sum(CASE WHEN role_id = 2 THEN 1 ELSE 0 END) numOfActresses";
sql += "FROM IMDB.title t, IMDB.cast_info c ";
sql += "WHERE t.production_year=? AND c.movie_id=t.id ";
sql += "AND (c.role_id=1 OR c.role_id=2)";
sql += "GROUP BY c.movie_id)";
// create and return the prepared statement
return conn.prepareStatement(sql.toString());
}
/**
* Runs the stuff
*
* @param args
*/
public static void main(String[] args) {
try {
// prepare connection and statements
Connection conn = JdbcTaskA_B.getConnection();
PreparedStatement statementMovies = getStatementMovies(conn);
Exercises for Relational Databases 1
Institut für Informationssysteme – TU Braunschweig - http://www.ifis.cs.tu-bs.de
Technische Universität Braunschweig
Institut für Informationssysteme
http://www.ifis.cs.tu-bs.de
Wolf-Tilo Balke, Christoph Lofi
PreparedStatement statementActors = getStatementActors(conn);
// get input scanner
Scanner in = new Scanner(System.in);
while (true) {
// get user input
System.out.print("Enter a year (0 for exit) : ");
int year = in.nextInt();
System.out.println("You entered : " + year);
if (year == 0) {
break;
}
// run query
//retrieve the number of movies
statementMovies.setInt(1, year);
ResultSet rs = statementMovies.executeQuery();
//retrieve the number of actors
statementActors.setInt(1, year);
ResultSet rs_a = statementActors.executeQuery();
// print result
rs.next(); // set result set to first entry
rs_a.next(); // set result set to first entry
System.out.format(
"%d movies in year %d with %f actors and %f
actresses in mean.\n",
rs.getInt(1), year, rs_a.getDouble(1),rs_a.getDouble(2));
}
System.out.println("Exit.");
} catch (Exception e) {
e.printStackTrace();
}
}
}
Create another new program. When being executed, the program should request two numbers of the user (numOfCols: between 1 and 14, numOfRows: between 1 and 50000).
After that, the program should create a new table called “numbers” having as many columns
as the user entered before (between 1 and 14. Those columns should be named col_1 to
col_14.). The first half of columns should be of type integer, the second of type double (e.g.:
7 columns -> col_1 to col_4 are int, col_5 to col_7 are double).
Then, the program inserts as many randomly generated rows as the user has entered before
(1 to 50000). Each random number should be between 1 and 100 respecting the data type of
the current column.
Exercises for Relational Databases 1
Institut für Informationssysteme – TU Braunschweig - http://www.ifis.cs.tu-bs.de
Technische Universität Braunschweig
Institut für Informationssysteme
http://www.ifis.cs.tu-bs.de
Wolf-Tilo Balke, Christoph Lofi
After that, the program computes and returns the average value of each column and deletes
the table.
Write 3 versions of this program: One version using simple insert statements, one using
prepared insert statements, and the last one using an updateable ResultSet and its insert method. Run all three versions for 15 columns and 50000 rows and measure the time needed
for execution.
Also, run the first two versions of the program with transactions enabled (i.e. all insert
statements are considered being one huge transaction) and without transactions (just execute each insert immediately).
public class JdbcTaskD {
private static String tableName = "numbers";
private static Random random = new Random();
/**
* Creates a new table with given number of columns.
*
* @param connection
* @param numOfCols
* @throws SQLException
*/
private static void createTable(Connection connection, int numOfCols)
throws SQLException {
String sql = "CREATE TABLE "+tableName+" (";
for (int i = 0; i < numOfCols; i++) {
sql+="col"+i;
if (i <= numOfCols / 2) {
sql+=" int";
} else {
sql+=" double";
}
if (i < numOfCols - 1) {
sql+=", ";
}
}
sql+=")";
Statement createStatement = connection.createStatement();
createStatement.execute(sql);
createStatement.close();
System.out.println("Table created : " + sql );
}
/**
* Removes table.
*
* @param connection
* @throws SQLException
*/
Exercises for Relational Databases 1
Institut für Informationssysteme – TU Braunschweig - http://www.ifis.cs.tu-bs.de
Technische Universität Braunschweig
Institut für Informationssysteme
http://www.ifis.cs.tu-bs.de
Wolf-Tilo Balke, Christoph Lofi
private static void dropTable(Connection connection) throws SQLException {
Statement createStatement = connection.createStatement();
createStatement.execute("DROP TABLE " + tableName);
createStatement.close();
System.out.println("Table dropped");
}
private static void fillTablePrepared(Connection conn, int numOfCols,
int numOfRows) throws SQLException {
// activate / deactivate autoCommit
boolean autoCommit = true;
conn.setAutoCommit(autoCommit);
//get time at start
long startMillis = System.currentTimeMillis();
// prepare statement-string
String sql = "INSERT INTO "+tableName+" VALUES (";
for (int col = 0; col < numOfCols; col++) {
sql+="?";
if (col < numOfCols - 1) {
sql+=",";
}
}
sql+=")";
//prepare statement
PreparedStatement insertStmt = conn.prepareStatement(sql);
// fill rows
for (int row = 0; row < numOfRows; row++) {
if (row % 100 == 0) {
System.out.print('.');
}
for (int col = 0; col < numOfCols; col++) {
if (col <= numOfCols / 2) {
insertStmt.setInt(col + 1, random.nextInt(100));
} else {
insertStmt.setDouble(col + 1, random.nextDouble() *
100);
}
}
insertStmt.executeUpdate();
}
if(!autoCommit){
//commit transaction
conn.commit();
//
System.out.format("\nExecution time for Prepared Statements with
Transaction %f\n",(System.currentTimeMillis() - startMillis) /
1000.0);
} else{
Exercises for Relational Databases 1
Institut für Informationssysteme – TU Braunschweig - http://www.ifis.cs.tu-bs.de
Technische Universität Braunschweig
Institut für Informationssysteme
http://www.ifis.cs.tu-bs.de
Wolf-Tilo Balke, Christoph Lofi
//
System.out.format("\nExecution time for Prepared Statements w/o
Transaction %f\n",(System.currentTimeMillis() - startMillis) /
1000.0);
}
}
private static void fillTableResultset(Connection conn, int numOfCols,
int numOfRows) throws SQLException{
// prepare statement
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
//get time at start
long startMillis = System.currentTimeMillis();
ResultSet rs= stmt.executeQuery("SELECT * FROM "+tableName);
// fill rows
for (int row = 0; row < numOfRows; row++) {
if (row % 100 == 0) {
System.out.print('.');
}
// move to insertRow
rs.moveToInsertRow();
//fill the row
for (int col = 0; col < numOfCols; col++) {
if (col <= numOfCols / 2) {
rs.updateInt(col+1, random.nextInt(100));
} else {
rs.updateDouble(col+1, random.nextDouble()*100);
}
}
//insert the row
rs.insertRow();
}
//measure time
System.out.format("\nExecution time for Updatable Resultset %f\n",
(System.currentTimeMillis() - startMillis) / 1000.0);
}
private static void fillTableSimple(Connection conn, int numOfCols,
int numOfRows) throws SQLException {
// activate / deactivate autoCommit
boolean autoCommit = false;
conn.setAutoCommit(autoCommit);
Exercises for Relational Databases 1
Institut für Informationssysteme – TU Braunschweig - http://www.ifis.cs.tu-bs.de
Technische Universität Braunschweig
Institut für Informationssysteme
http://www.ifis.cs.tu-bs.de
Wolf-Tilo Balke, Christoph Lofi
// prepare statement
Statement insertStmt = conn.createStatement();
//get time at start
long startMillis = System.currentTimeMillis();
// fill rows
for (int row = 0; row < numOfRows; row++) {
if (row % 100 == 0) {
System.out.print('.');
}
String sql = "INSERT INTO "+tableName+" VALUES (";
for (int col = 0; col < numOfCols; col++) {
if (col <= numOfCols / 2) {
sql+=random.nextInt(100);
} else {
sql+=random.nextDouble() * 100;
}
if (col < numOfCols - 1) {
sql+=",";
}
}
sql+=")";
insertStmt.executeUpdate(sql);
}
if(!autoCommit){
//commit transaction
conn.commit();
//
System.out.format("\nExecution time for Simple Statements
action %f\n",(System.currentTimeMillis() - startMillis) /
} else{
//
System.out.format("\nExecution time for Simple Statements
action %f\n",(System.currentTimeMillis() - startMillis) /
}
}
public static void main(String[] args) {
Scanner in = new Scanner(System.in);
System.out.print("\nEnter number of columns (1-15) : ");
int numOfCols = in.nextInt();
System.out.print("\nEnter number of rows (1-50000) : ");
int numOfRows = in.nextInt();
if (numOfCols < 1 || numOfCols > 15 || numOfRows < 1
|| numOfRows > 50000) {
Exercises for Relational Databases 1
Institut für Informationssysteme – TU Braunschweig - http://www.ifis.cs.tu-bs.de
with Trans1000.0);
w/o Trans1000.0);
Technische Universität Braunschweig
Institut für Informationssysteme
http://www.ifis.cs.tu-bs.de
Wolf-Tilo Balke, Christoph Lofi
System.out.println("You are a moron and your paramters are
invalid.");
System.exit(-1);
}
// run the program
Connection connection = null;
try {
connection = JdbcTaskA_B.getConnection();
try {
dropTable(connection);
} catch (SQLException e) {
}
createTable(connection, numOfCols);
// call according function
fillTablePrepared(connection, numOfCols, numOfRows);
fillTableSimple(connection, numOfCols, numOfRows);
fillTableResultset(connection, numOfCols, numOfRows);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null)
try {
// dropTable(connection);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
Exercises for Relational Databases 1
Institut für Informationssysteme – TU Braunschweig - http://www.ifis.cs.tu-bs.de