Set - 6

Question 6 :

Can I use the JDBC-ODBC bridge driver in an applet?

Answer :

Short answer: No.
Longer answer: You may create a digitally signed applet using a Certicate to circumvent the security sandbox of the browser.

Question 7 :

Which is the preferred collection class to use for storing database result sets?

Answer :

When retrieving database results, the best collection implementation to use is the LinkedList. The benefits include:
* Retains the original retrieval order
* Has quick insertion at the head/tail
* Doesn't have an internal size limitation like a Vector where when the size is exceeded a new internal structure is created (or you have to find out size beforehand to size properly)
* Permits user-controlled synchronization unlike the pre-Collections Vector which is always synchronized

ResultSet result = stmt.executeQuery("...");
List list = new LinkedList();
while( {

If there are multiple columns in the result set, you'll have to combine them into their own data structure for each row. Arrays work well for that as you know the size, though a custom class might be best so you can convert the contents to the proper type when extracting from databse, instead of later.

Question 8 :

The java.sql package contains mostly interfaces. When and how are these interfaces implemented while connecting to database?

Answer :

The implementation of these interfaces is all part of the driver. A JDBC driver is not just one class - it is a complete set of database-specific implementations for the interfaces defined by the JDBC.
These driver classes come into being through a bootstrap process. This is best shown by stepping through the process of using JDBC to connect to a database, using Oracle's type 4 JDBC driver as an example:

* First, the main driver class must be loaded into the VM:
The specified driver must implement the Driver interface. A class initializer (static code block) within the OracleDriver class registers the driver with the DriverManager.
* Next, we need to obtain a connection to the database:
String jdbcURL = "";
Connection connection = DriverManager.getConnection(jdbcURL);
DriverManager determines which registered driver to use by invoking the acceptsURL(String url) method of each driver, passing each the JDBC URL. The first driver to return "true" in response will be used for this connection. In this example, OracleDriver will return "true", so DriverManager then invokes the connect() method of OracleDriver to obtain an instance of OracleConnection. It is this database-specific connection instance implementing the Connection interface that is passed back from the DriverManager.getConnection() call.
* The bootstrap process continues when you create a statement:
Statement statement = connection.createStatement();
The connection reference points to an instance of OracleConnection. This database-specific implementation of Connection returns a database-specific implementation of Statement, namely OracleStatement
* Invoking the execute() method of this statement object will execute the database-specific code necessary to issue an SQL statement and retrieve the results:
ResultSet result = statement.executeQuery("SELECT * FROM TABLE");
Again, what is actually returned is an instance of OracleResultSet, which is an Oracle-specific implementation of the ResultSet interface.
So the purpose of a JDBC driver is to provide these implementations that hide all the database-specific details behind standard Java interfaces.

Question 9 :

How can I manage special characters (for example: " _ ' % ) when I execute an INSERT query? If I don't filter the quoting marks or the apostrophe, for example, the SQL string will cause an error?

Answer :

In JDBC, strings containing SQL commands are just normal strings - the SQL is not parsed or interpreted by the Java compiler. So there is no special mechanism for dealing with special characters; if you need to use a quote (") within a Java string, you must escape it.
The Java programming language supports all the standard C escapes, such as \n for newline, \t for tab, etc. In this case, you would use \" to represent a quote within a string literal:

String stringWithQuote =
"\"No,\" he replied, \"I did not like that salted licorice.\"";

This only takes care of one part of the problem: letting us control the exact string that is passed on to the database. If you want tell the database to interpret characters like a single quote (') literally (and not as string delimiters, for instance), you need to use a different method. JDBC allows you to specify a separate, SQL escape character that causes the character following to be interpreted literally, rather than as a special character.

An example of this is if you want to issue the following SQL command:

WHERE SPECIES='Williamson's Sapsucker'

In this case, the apostrophe in "Williamson's" is going to cause a problem for the database because SQL will interpret it as a string delimiter. It is not good enough to use the C-style escape \', because that substitution would be made by the Java compiler before the string is sent to the database.
Different flavors of SQL provide different methods to deal with this situation. JDBC abstracts these methods and provides a solution that works for all databases. With JDBC you could write the SQL as follows:

Statement statement = // obtain reference to a Statement
"SELECT * FROM BIRDS WHERE SPECIES='Williamson/'s Sapsucker' {escape '/'}");

The clause in curly braces, namely {escape '/'}, is special syntax used to inform JDBC drivers what character the programmer has chosen as an escape character. The forward slash used as the SQL escape has no special meaning to the Java compiler; this escape sequence is interpreted by the JDBC driver and translated into database-specific SQL before the SQL command is issued to the database.
Escape characters are also important when using the SQL LIKE clause. This usage is explicitly addressed in section 11.5 of the JDBC specification:

The characters "%" and "_" have special meaning in SQL LIKE clauses (to match zero or more characters, or exactly one character, respectively). In order to interpret them literally, they can be preceded with a special escape character in strings, e.g. "\". In order to specify the escape character used to quote these characters, include the following syntax on the end of the query:
{escape 'escape-character'}
For example, the query
finds identifier names that begin with an underbar.

Question 10 :

How can I make batch updates using JDBC?

Answer :

One of the more advanced features of JDBC 2.0 is the ability to submit multiple update statements to the database for processing as a single unit. This batch updating can be significantly more efficient compared to JDBC 1.0, where each update statement has to be executed separately.

Consider the following code segment demonstrating a batch update:
try {
Statement stmt= dbCon.createStatement();
stmt.addBatch("INSERT INTO bugs "+
"VALUES (1007, 'Server stack overflow', 1,2,{d '1999-01-01'})");
stmt.addBatch("INSERT INTO bugs "+
"VALUES (1008,'Cannot load DLL', 3,1,{d '1999-01-01'})");
stmt.addBatch("INSERT INTO bugs "+
"VALUES (1009,'Applet locks up',2,2,{d '1999-01-01'})");

int[] updCnt = stmt.executeBatch();

} catch (BatchUpdateException be) {

//handle batch update exception
int[] counts = be.getUpdateCounts();
for (int i=0; I counts.length; i++) {
System.out.println("Statement["+i+"] :"+counts[i]);
catch (SQLException e) {

//handle SQL exception

Before carrying out a batch update, it is important to disable the auto-commit mode by calling setAutoCommit(false). This way, you will be able to rollback the batch transaction in case one of the updates fail for any reason. When the Statement object is created, it is automatically associated a "command list", which is initially empty. We then add our SQL update statements to this command list, by making successive calls to the addBatch() method. On calling executeBatch(), the entire command list is sent over to the database, and are then executed in the order they were added to the list. If all the commands in the list are executed successfully, their corresponding update counts are returned as an array of integers. Please note that you always have to clear the existing batch by calling clearBatch() before creating a new one.
If any of the updates fail to execute within the database, a BatchUpdateException is thrown in response to it. In case there is a problem in returning the update counts of each SQL statement, a SQLException will be thrown to indicate the error.