1. What are the types of statements in JDBC?-
Ans: Statement: To be used createStatement() method for executing single SQL statement
PreparedStatement: To be used preparedStatement() method for executing same SQL statement over and over.
CallableStatement: To be used prepareCall() method for multiple SQL statements over and over.
2. How to Use Updatable ResultSet in JDBC?
Ans: A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. An updatable result set allows modification to data in a table through the result set. The following code makes a result set that is scrollable and insensitive to updates by others:
try {
// Create a statement that will return updatable result sets
Statement stmt = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
//Primary key EmployeeID must be specified
//so that the result set is updatable
ResultSet resultSet = stmt.executeQuery(
"SELECT EmployeeID, Name, Office FROM employees");
} catch (SQLException e) {
}
The updatable result set may be used few ways:
To update a column value in the current row: In a scrollable ResultSet object, the cursor can be moved backwards and forwards, to an absolute position, or to a position relative to the current row. The following code fragment updates the Office column in the fifth row of the ResultSet object rs and then uses the method updateRow to update the data source table from which rs was derived.
rs.absolute(5); // moves the cursor to the fifth row of rs
rs.updateString("Office", "HQ222"); // updates the
// Office column of row 5 to be HQ222
rs.updateRow(); // updates the row in the data source
To insert column values into the insert row. An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a three-column row, and inserts it into rs and into the data source table using the method insertRow.
rs.moveToInsertRow(); // moves cursor to the insert row
rs.updateInt("EmployeeID", 1001);
rs.updateString("Name", "Divad Walker");
rs.updateString("Office", "HQ101");
rs.insertRow();
rs.moveToCurrentRow();
To delete a row. The following code fragment moves to the first row of the ResultSet object rs and then uses the method deleteRow to delete the data source table from which rs was derived.
rs.first(); // moves cursor to the deleting row
rs.deleteRow();
A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.
cancelRowUpdates cancels the updates made to the current row in this ResultSet object. This method may be called after calling an updater method(s) and before calling the method updateRow to roll back the updates made to a row. If no updates have been made or updateRow has already been called, this method has no effect.
3. What does setAutoCommit do?
Ans:When a connection is created, it is in auto-commit mode.
This means that each individual SQL statement is treated as a transaction and will be automatically committed right after it is executed. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode:con.setAutoCommit(false);
Once auto-commit mode is disabled, no SQL statements will be committed until you call the method commit explicitly.
4. What form of statement would you use to include user-supplied values? (Hint: PreparedStatement)
5. Why might a preparedStatement be more efficient than a statement? (Hint: Execution plan cache.)
6. How would you prevent an SQL injection attack in JDBC? (Hint: PreparsedStatement )
7. What is the performance impact of testing against NULL in WHERE clause on Oracle? (Hint: Full table scan)
8. List advantages and disadvantages in using stored procedures?
Ans:
Pro: Integration with existing database, reduced network traffic
Con: not portable, mutliple language knowledge required
9. What is the difference between sql.Date, sql.Time, and sql.Timestamp?
(Hint: Date only, time only, date and time )
10. If you had a missing int value how do you indicate this to PreparedStatement? (Hint: setNull(pos, TYPE))
11. How can I perform multiple inserts in one database interaction? (Hint: executeBatch)
Given this problem: Program reads 100,000 rows, converts to Java class in list, then converts list to XML file using reflection. Runs out of program memory. How would you fix? (Hint: Read one row at time, limit select, allocate more heap (result set = cursor) )
12. How might you model object inheritance in database tables? (Hint: Table per hierarchy, table per class, table per concrete class)
Ans: Statement: To be used createStatement() method for executing single SQL statement
PreparedStatement: To be used preparedStatement() method for executing same SQL statement over and over.
CallableStatement: To be used prepareCall() method for multiple SQL statements over and over.
2. How to Use Updatable ResultSet in JDBC?
Ans: A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. An updatable result set allows modification to data in a table through the result set. The following code makes a result set that is scrollable and insensitive to updates by others:
try {
// Create a statement that will return updatable result sets
Statement stmt = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
//Primary key EmployeeID must be specified
//so that the result set is updatable
ResultSet resultSet = stmt.executeQuery(
"SELECT EmployeeID, Name, Office FROM employees");
} catch (SQLException e) {
}
The updatable result set may be used few ways:
To update a column value in the current row: In a scrollable ResultSet object, the cursor can be moved backwards and forwards, to an absolute position, or to a position relative to the current row. The following code fragment updates the Office column in the fifth row of the ResultSet object rs and then uses the method updateRow to update the data source table from which rs was derived.
rs.absolute(5); // moves the cursor to the fifth row of rs
rs.updateString("Office", "HQ222"); // updates the
// Office column of row 5 to be HQ222
rs.updateRow(); // updates the row in the data source
To insert column values into the insert row. An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a three-column row, and inserts it into rs and into the data source table using the method insertRow.
rs.moveToInsertRow(); // moves cursor to the insert row
rs.updateInt("EmployeeID", 1001);
rs.updateString("Name", "Divad Walker");
rs.updateString("Office", "HQ101");
rs.insertRow();
rs.moveToCurrentRow();
To delete a row. The following code fragment moves to the first row of the ResultSet object rs and then uses the method deleteRow to delete the data source table from which rs was derived.
rs.first(); // moves cursor to the deleting row
rs.deleteRow();
A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.
cancelRowUpdates cancels the updates made to the current row in this ResultSet object. This method may be called after calling an updater method(s) and before calling the method updateRow to roll back the updates made to a row. If no updates have been made or updateRow has already been called, this method has no effect.
3. What does setAutoCommit do?
Ans:When a connection is created, it is in auto-commit mode.
This means that each individual SQL statement is treated as a transaction and will be automatically committed right after it is executed. The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode:con.setAutoCommit(false);
Once auto-commit mode is disabled, no SQL statements will be committed until you call the method commit explicitly.
4. What form of statement would you use to include user-supplied values? (Hint: PreparedStatement)
5. Why might a preparedStatement be more efficient than a statement? (Hint: Execution plan cache.)
6. How would you prevent an SQL injection attack in JDBC? (Hint: PreparsedStatement )
7. What is the performance impact of testing against NULL in WHERE clause on Oracle? (Hint: Full table scan)
8. List advantages and disadvantages in using stored procedures?
Ans:
Pro: Integration with existing database, reduced network traffic
Con: not portable, mutliple language knowledge required
9. What is the difference between sql.Date, sql.Time, and sql.Timestamp?
(Hint: Date only, time only, date and time )
10. If you had a missing int value how do you indicate this to PreparedStatement? (Hint: setNull(pos, TYPE))
11. How can I perform multiple inserts in one database interaction? (Hint: executeBatch)
Given this problem: Program reads 100,000 rows, converts to Java class in list, then converts list to XML file using reflection. Runs out of program memory. How would you fix? (Hint: Read one row at time, limit select, allocate more heap (result set = cursor) )
12. How might you model object inheritance in database tables? (Hint: Table per hierarchy, table per class, table per concrete class)