3. Java 7: JDBC API Flashcards
What is something you should always do when establishing a database connections using the DriverManager prior to JDBC 4.0?
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) {}
How can you set up a connection to a database using the DriverManager? Name the three methods.
DriverManager.getConnection(String url);
DriverManager.getConnection(String url, Properties properties);
DriverManager.getConnection(String url, String user, String password);
There are two types of RowSet’s, name these two.
Connected RowSet - keeps it’s connection alive
Disconnected RowSet - closes the connection once the command (query) is finished.
What is a RowSet?
RowSet is a interface that is used to retrieve and change data from a database-source.
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
RowSetFactory factory = RowSetProvider.newFactory();
JdbcRowSet rowSet = factory.createJdbcRowSet();
rowSet.setUrl(url);
rowSet.setUsername(user);
rowSet.setPassword(password);
rowSet.setCommand(query);
rowSet.execute();
How can you create a RowSetFactory?
RowSetProvider.newFactory();
RowSetProvider.newFactory("com.sun.rowset.RowSetFactoryImpl", null);Name the 5 sub-interfaces of the RowSet-interface.
CachedRowSet FilteredRowSet JdbcRowSet JoinRowSet WebRowSet
What are the two main goals that transactions achieve?
- Make sure that all the steps of certain logical work are executed.
- If a step fails all previous steps steps can be undone and the database will revert to a state before the first step.
How should a transaction be commited using an Connection object?
connection.commit();
What method is called when a transactie is reverted?
connection.rollback();
What is a transaction in the JDBC API?
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.
True or False:
A JDBC connection defaults to auto-committing statements
True.
By Default every statement that is executed using a Connection is commited directly.
How can you enable/disable auto-commits using a Datasource object?
Connection connection = dataSource.getConnection();
connection.setAutoCommit(false);
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
1,2 and 3
When chaning auto commit all open transactions are committed
Given I have a Datasource object how can I create a statement?
Connection connection = dataSource.getConnection();
connection.createStatement();
Name the 3 Statement types and what they are used for.
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.
There are 3 ways to execute a Query using a Statement. Name the three method signatures and their uses.
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.
Given a ResultSet with two rows, how do you iterate through them and print the column ID as a String?
while(resultSet.next()) {
System.out.println(resultSet.getString(“ID”);
}
What is the result of the following code:
ResultSet resultSet = statement.executeQuery(“SELECT * FROM USERS”);
System.out.println(resultSet.getString(“ID”);
Exception! Cursor (ResultSet) should first move to the first row using resultSet.next();
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);
}
Exception! Invalid column index! Colums start at 1 not at 0.
Given a Connection object create a PreparedStatement for the following query: UPDATE PERSON SET NAME = “JAN”. Where JAN is a input parameter.
PreparedStatement statement = connection.prepareStatement(“UPDATE PERSON SET NAME = ?;”);
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 = ?;”
statement. setString(1, “JAN”);
statement. setInt(2, 99);
statement. executeUpdate();
Note: that the first parameter is 1 and not 0.
Describe how you can clear the parameters of a PreparedStatement.
Call the clearParameters(); method on the statement.
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.
PreparedStatement statement = connection.prepareStatement(query);
statement. setString(1, “Jan”);
statement. setInt(2, 99);
statement. addBatch();
statement. setString(1, “Henk”);
statement. addBatch();
statement. execute();
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.
CallableStatement statement = connection.prepareCall(“{?= call upgradeUser[(?,?)]}”)
statement. setString(1, “Jan”);
statement. setInt(2, 99);
