JDBC Questions

1. What is JDBC?
JDBC may stand for Java Database Connectivity. It is also a trade mark. JDBC is a layer of abstraction that allows users to choose between databases. It allows you to change to a different database engine and to write to a single API. JDBC allows you to write database applications in Java without having to concern yourself with the underlying details of a particular database.
________________________________________
2. What are the two major components of JDBC?
One implementation interface for database manufacturers, the other implementation interface for application and applet writers.
________________________________________
3. What is JDBC Driver interface?
The JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API. Each vendor driver must provide implementations of the java.sql.Connection,Statement,PreparedStatement, CallableStatement, ResultSet and Driver.
________________________________________
4. What are the common tasks of JDBC?
o Create an instance of a JDBC driver or load JDBC drivers through jdbc.drivers
o Register a driver
o Specify a database
o Open a database connection
o Submit a query
o Receive results
________________________________________
5. How to use JDBC to connect Microsoft Access?
Please see this page for detailed information.
________________________________________
6. What are four types of JDBC driver?
1. Type 1 Drivers
Bridge drivers such as the jdbc-odbc bridge. They rely on an intermediary such as ODBC to transfer the SQL calls to the database and also often rely on native code. It is not a serious solution for an application
2. Type 2 Drivers
Use the existing database API to communicate with the database on the client. Faster than Type 1, but need native code and require additional permissions to work in an applet. Good for client-side connection.
3. Type 3 Drivers
JDBC-Net pure Java driver. It translates JDBC calls to a DBMS-independent net protocol, which is then translated to a DBMS protocol by a server. Flexible. Pure Java and no native code.
4. Type 4 Drivers
Native-protocol pure Java driver. It converts JDBC calls directly into the network protocol used by DBMSs. This allows a direct call from the client machine to the DBMS server.
7. Recommended by Sun's tutorial, driver type 1 and 2 are interim solutions where direct pure Java drivers are not yet available. Driver type 3 and 4 are the preferred way to access databases using the JDBC API, because they offer all the advantages of Java technology, including automatic installation. For more info, visit Sun JDBC page
8. ________________________________________
9. What packages are used by JDBC?
There are at least 8 packages:
0. java.sql.Driver
1. Connection
2. Statement
3. PreparedStatement
4. CallableStatement
5. ResultSet
6. ResultSetMetaData
7. DatabaseMetaData
________________________________________
10. There are three basic types of SQL statements, what are they?
0. Statement
1. CallableStatement
2. PreparedStatement
________________________________________
11. What are the flow statements of JDBC?
A URL string -->getConnection-->DriverManager-->Driver-->Connection-->Statement-->executeQuery-->ResultSet.
________________________________________
12. What are the steps involved in establishing a connection?
This involves two steps: (1) loading the driver and (2) making the connection.
________________________________________
13. How can you load the drivers?
Loading the driver or drivers you want to use is very simple and involves just one line of code. If, for example, you want to use the JDBC-ODBC Bridge driver, the following code will load it:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Your driver documentation will give you the class name to use. For instance, if the class name is jdbc.DriverXYZ , you would load the driver with the following line of code:
Class.forName("jdbc.DriverXYZ");
________________________________________
14. What Class.forName will do while loading driver?
It is used to create an instance of a driver and register it with the DriverManager. When you have loaded a driver, it is available for making a connection with DBMS.
________________________________________
15. How can you make the connection?
When establishing a connection, have the appropriate driver connect to DBMS. The following line of code illustrates the general idea:
String url = "jdbc:odbc:Fred";
Connection con = DriverManager.getConnection(url, "Fernanda", "J8");
________________________________________
16. How can you create JDBC statement?
A Statement object is what sends your SQL statement to the DBMS. You simply create a Statement object and then execute it by supplying the appropriate execute method with the SQL statement you want to send. For a SELECT statement, the method to use is executeQuery. For statements that create or modify tables, the method to use is executeUpdate. It takes an instance of an active connection to create a Statement object. In the following example, we use our Connection object con to create the Statement object stmt :
Statement stmt = con.createStatement();
________________________________________
17. How to make a query?
Create a Statement object and call the Statement.executeQuery method to select data from the database. The results of the query are returned in a ResultSet object.
Statement stmt = con.createStatement();
ResultSet results = stmt.executeQuery("SELECT data FROM aDatabase ");
________________________________________
18. How can you retrieve data from the ResultSet?
Use getXXX() methods to retrieve data from returned ResultSet object.
ResultSet rs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
String s = rs.getString("COF_NAME");
The method getString() is invoked on the ResultSet object rs , so getString() will retrieve the value stored in the column COF_NAME in the current row of rs
________________________________________
19. How to navigate the ResultSet?
By default the result set cursor points to the row before the first row of the result set. A call to next() retrieves the first result set row. The cursor can also be moved by calling one of the following ResultSet methods:
o beforeFirst(): Default position. Puts cursor before the first row of the result set.
o first(): Puts cursor on the first row of the result set.
o last(): Puts cursor before the last row of the result set.
o afterLast() Puts cursor beyond last row of the result set. Calls to previous moves backwards through the ResultSet.
o absolute(pos): Puts cursor at the row number position where absolute(1) is the first row and absolute(-1) is the last row.
o relative(pos): Puts cursor at a row relative to its current position where relative(1) moves row cursor one row forward.
________________________________________
20. What are the different types of Statements?
0. Statement (use createStatement method)
1. Prepared Statement (Use prepareStatement method)
2. Callable Statement (Use prepareCall)
________________________________________
21. If you want to use the percent sign (%) as the percent sign and not have it interpreted as the SQL wildcard used in SQL LIKE queries, how to do that?
Use escape keyword. For example:
stmt.executeQuery("select tax from sales where tax like '10\%' {escape '\'}");
________________________________________
22. How to escape ' symbol found in the input line?
You may use a method to do so:
static public String escapeLine(String s) {
String retvalue = s;
if (s.indexOf ("'") != -1 ) {
StringBuffer hold = new StringBuffer();
char c;
for(int i=0; i < s.length(); i++ ) {
if ((c=s.charAt(i)) == '\'' ) {
hold.append ("''");
}else {
hold.append(c);
}
}
retvalue = hold.toString();
}
return retvalue;
}
Note that such method can be extended to escape any other characters that the database driver may interprete another way.
________________________________________
23. How to make an update?
Creates a Statement object and calls the Statement.executeUpdate method.

String updateString = "INSERT INTO aDatabase VALUES (some text)";
int count = stmt.executeUpdate(updateString);
________________________________________
24. How to update a ResultSet?
You can update a value in a result set by calling the ResultSet.update method on the row where the cursor is positioned. The type value here is the same used when retrieving a value from the result set, for example, updateString updates a String value and updateDouble updates a double value in the result set.
rs.first();
updateDouble("balance", rs.getDouble("balance") - 5.00);
The update applies only to the result set until the call to rs.updateRow(), which updates the underlying database.
To delete the current row, use rs.deleteRow().
To insert a new row, use rs.moveToInsertRow().
________________________________________
25. How can you use PreparedStatement?
This special type of statement is derived from the more general class, Statement. If you want to execute a Statement object many times, it will normally reduce execution time to use a PreparedStatement object instead. The advantage to this is that in most cases, this SQL statement will be sent to the DBMS right away, where it will be compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement 's SQL statement without having to compile it first.
PreparedStatement updateSales = con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
________________________________________
26. How to call a Stored Procedure from JDBC?
The first step is to create a CallableStatement object. As with Statement an and PreparedStatement objects, this is done with an open Connection object. A CallableStatement object contains a call to a stored procedure;
CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();
________________________________________
27. How to Retrieve Warnings?
SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object
SQLWarning warning = stmt.getWarnings();
if (warning != null) {

while (warning != null) {
System.out.println("Message: " + warning.getMessage());
System.out.println("SQLState: " + warning.getSQLState());
System.out.print("Vendor error code: ");
System.out.println(warning.getErrorCode());
warning = warning.getNextWarning();
}
}
________________________________________
28. How to Make Updates to Update ResultSets?
Another new feature in the JDBC 2.0 API is the ability to update rows in a result set using methods in the Java programming language rather than having to send an SQL command. But before you can take advantage of this capability, you need to create a ResultSet object that is updatable. In order to do this, you supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method.
Connection con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName");
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = ("SELECT COF_NAME, PRICE FROM COFFEES");
________________________________________
29. How to set a scroll type?
Both Statements and PreparedStatements have an additional constructor that accepts a scroll type and an update type parameter. The scroll type value can be one of the following values:
o ResultSet.TYPE_FORWARD_ONLY Default behavior in JDBC 1.0, application can only call next() on the result set.
o ResultSet.SCROLL_SENSITIVE ResultSet is fully navigable and updates are reflected in the result set as they occur.
o ResultSet.SCROLL_INSENSITIVE Result set is fully navigable, but updates are only visible after the result set is closed. You need to create a new result set to see the results.
________________________________________
30. How to set update type parameter?
In the constructors of Statements and PreparedStatements, you may use
o ResultSet.CONCUR_READ_ONLY The result set is read only.
o ResultSet.CONCUR_UPDATABLE The result set can be updated.
You may verify that your database supports these types by calling con.getMetaData().supportsResultSetConcurrency(ResultSet.SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
________________________________________
31. How to do a batch job?
By default, every JDBC statement is sent to the database individually. To send multiple statements at one time , use addBatch() method to append statements to the original statement and call executeBatch() method to submit entire statement.
Statement stmt = con.createStatement();
stmt.addBatch("update registration set balance=balance-5.00 where theuser="+theuser);
stmt.addBatch("insert into auctionitems(description, startprice) values("+description+","+startprice+")");
...
int[] results = stmt.executeBatch();
The return result of the addBatch() method is an array of row counts affected for each statement executed in the batch job. If a problem occurred, a java.sql.BatchUpdateException is thrown. An incomplete array of row counts can be obtained from BatchUpdateException by calling its getUpdateCounts() method.
32. How to store and retrieve an image?
To store an image, you may use the code:
int itemnumber=400456;

File file = new File(itemnumber+".jpg");
FileInputStream fis = new FileInputStream(file);
PreparedStatement pstmt = con.prepareStatement("update auctionitems set theimage=? where id= ?");
pstmt.setBinaryStream(1, fis, (int)file.length()):
pstmt.setInt(2, itemnumber);
pstmt.executeUpdate();
pstmt.close();
fis.close();
To retrieve an image:
int itemnumber=400456;
byte[] imageBytes;//hold an image bytes to pass to createImage().

PreparedStatement pstmt = con.prepareStatement("select theimage from auctionitems where id= ?");
pstmt.setInt(1, itemnumber);
ResultSet rs=pstmt.executeQuery();
if(rs.next()) {
imageBytes = rs.getBytes(1);
}
pstmt.close();
rs.close();

Image auctionimage = Toolkit.getDefaultToolkit().createImage(imageBytes);
________________________________________
33. How to store and retrive an object?
A class can be serialized to a binary database field in much the same way as the image. You may use the code above to store and retrive an object.
________________________________________
34. How to use meta data to check a column type?
Use getMetaData().getColumnType() method to check data type. For example to retrieve an Integer, you may check it first:
int count=0;
Connection con=getConnection();
Statement stmt= con.createStatement();
stmt.executeQuery("select counter from aTable");
ResultSet rs = stmt.getResultSet();
if(rs.next()) {
if(rs.getMetaData().getColumnType(1) == Types.INTEGER) {
Integer i=(Integer)rs.getObject(1);
count=i.intValue();
}
}
rs.close();
________________________________________
35. Why cannot java.util.Date match with java.sql.Date?
Because java.util.Date represents both date and time. SQL has three types to represent date and time.
o java.sql.Date -- (00/00/00)
o java.sql.Time -- (00:00:00)
o java.sql.Timestamp -- in nanoseconds
Note that they are subclasses of java.util.Date.
________________________________________
36. How to convert java.util.Date value to java.sql.Date?
Use the code below:
Calendar currenttime=Calendar.getInstance();
java.sql.Date startdate= new java.sql.Date((currenttime.getTime()).getTime());

or

SimpleDateFormat template = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date enddate = new java.util.Date("10/31/99");
java.sql.Date sqlDate = java.sql.Date.valueOf(template.format(enddate));