3. Java 7: JDBC API

Question 1

What is something you should always do when establishing a database connections using the DriverManager prior to JDBC 4.0?

Answer 1

Since JDBC 4.0 database drivers are loaded automatically. Prior to JDBC 4.0 they should be loaded manually:

try {
Class.forName(“oracle.jdbc.drive.OracleDriver”);
catch(ClassNotFoundException e) {}

Question 2

How can you set up a connection to a database using the DriverManager? Name the three methods.

Answer 2

DriverManager.getConnection(String url);
DriverManager.getConnection(String url, Properties properties);
DriverManager.getConnection(String url, String user, String password);

Question 3

There are two types of RowSet’s, name these two.

Answer 3

Connected RowSet - keeps it’s connection alive

Disconnected RowSet - closes the connection once the command (query) is finished.

Question 4

What is a RowSet?

Answer 4

RowSet is a interface that is used to retrieve and change data from a database-source.

Question 5

Given the following information, how do you execute the query using a JdbcRowSet?

user: admin
passworod: 123
url: mysql:/localhost:1433
query: select name from user

Answer 5

RowSetFactory factory = RowSetProvider.newFactory();
JdbcRowSet rowSet = factory.createJdbcRowSet();
rowSet.setUrl(url);
rowSet.setUsername(user);
rowSet.setPassword(password);
rowSet.setCommand(query);
rowSet.execute();

Question 6

How can you create a RowSetFactory?

Answer 6

RowSetProvider.newFactory();
RowSetProvider.newFactory("com.sun.rowset.RowSetFactoryImpl", null);

Question 7

Name the 5 sub-interfaces of the RowSet-interface.

Answer 7

CachedRowSet
FilteredRowSet
JdbcRowSet
JoinRowSet
WebRowSet

Question 8

What are the two main goals that transactions achieve?

Answer 8

1. Make sure that all the steps of certain logical work are executed.
2. If a step fails all previous steps steps can be undone and the database will revert to a state before the first step.

Question 9

How should a transaction be commited using an Connection object?

Answer 9

connection.commit();

Question 10

What method is called when a transactie is reverted?

Answer 10

connection.rollback();

Question 11

What is a transaction in the JDBC API?

Answer 11

All transaction logic happens at the Connection object. A Connection object may exist of zero or more Statement objects. When the commit method is called all Statements will be commited as the new permanent state. When the system calls the Rollback method all Statements currently inside the Connection will be rolled back.

Question 12

True or False:

A JDBC connection defaults to auto-committing statements

Answer 12

True.

By Default every statement that is executed using a Connection is commited directly.

Question 13

How can you enable/disable auto-commits using a Datasource object?

Answer 13

Connection connection = dataSource.getConnection();

connection.setAutoCommit(false);

Question 14

Given the following code:
Connection connection = dataSource.getConnection();
connection.setAutoCommit(false);
connection.execute(statement1);
connection.execute(statement2);
connection.setAutoCommit(true);
connection.execute(statement3);

Which of the statements are committed after the code completes

Answer 14

1,2 and 3

When chaning auto commit all open transactions are committed

Question 15

Given I have a Datasource object how can I create a statement?

Answer 15

Connection connection = dataSource.getConnection();

connection.createStatement();

Question 16

Name the 3 Statement types and what they are used for.

Answer 16

Statement - execute simple static SQL statements without parameters.
PreparedStatement - used to execute pre-compiled SQL statements that contain input parameters.
CallableStatement - used to call stored procedures that may contain input and/or output parameters.

Question 17

There are 3 ways to execute a Query using a Statement. Name the three method signatures and their uses.

Answer 17

boolean execute(String query) throws SQLException;
- returns true if the first object returned by the query is a ResultSet object. Use this if you expect one or more ResultSet objects from the query. Retrieve the ResultSet objects by calling statement.getResultSet repeatedly.
ResultSet executeQuery(String query) throws SQLException;
- you can use this if the query returns one ResultSet object. Can't be used on a PreparedStatement or CallableStatement.
int executeUpdate(String query) throws SQLException;
- used for INSERT, UPDATE, DELETE and other statements that don't return anything.

Question 18

Given a ResultSet with two rows, how do you iterate through them and print the column ID as a String?

Answer 18

while(resultSet.next()) {
System.out.println(resultSet.getString(“ID”);
}

Question 19

What is the result of the following code:
ResultSet resultSet = statement.executeQuery(“SELECT * FROM USERS”);
System.out.println(resultSet.getString(“ID”);

Answer 19

Exception! Cursor (ResultSet) should first move to the first row using resultSet.next();

Question 20

What is the result of the following code:
ResultSet resultSet = statement.executeQuery(“SELECT * FROM USERS”);
while(resultSet.next()) {
System.out.println(resultSet.getString(0);
System.out.println(resultSet.getString(1);
}

Answer 20

Exception! Invalid column index! Colums start at 1 not at 0.

Question 21

Given a Connection object create a PreparedStatement for the following query: UPDATE PERSON SET NAME = “JAN”. Where JAN is a input parameter.

Answer 21

PreparedStatement statement = connection.prepareStatement(“UPDATE PERSON SET NAME = ?;”);

Question 22

Given the following PreparedStatement (as statement) how can is set the input parameters and execute it? Given Name is a String and ID is a int.

“UPDATE PERSON SET NAME = ? WHERE ID = ?;”

Answer 22

statement. setString(1, “JAN”);
statement. setInt(2, 99);
statement. executeUpdate();

Note: that the first parameter is 1 and not 0.

Question 23

Describe how you can clear the parameters of a PreparedStatement.

Answer 23

Call the clearParameters(); method on the statement.

Question 24

Given the following Query:
“UPDATE PERSON SET NAME = ? WHERE ID = ?;”
Make use of the PreparedStatement batch to change the name first to Jan and then to Henk for user with id 99.

Answer 24

PreparedStatement statement = connection.prepareStatement(query);

statement. setString(1, “Jan”);
statement. setInt(2, 99);
statement. addBatch();
statement. setString(1, “Henk”);
statement. addBatch();
statement. execute();

Question 25

Given the following storedprocedure; upgradeUser wich takes two input parameters of the type String and int. And returns an int. How do you create the statement and execute it.

Answer 25

CallableStatement statement = connection.prepareCall(“{?= call upgradeUser[(?,?)]}”)

statement. setString(1, “Jan”);
statement. setInt(2, 99);