JDBC

11) I have the choice of manipulating database data using a byte[] or a java.sql.Blob. Which has best performance?
 

java.sql.Blob, since it does not extract any data from the database until you explicitly ask it to. The Java platform 2 type Blob wraps a database locator (which is essentially a pointer to byte). That pointer is a rather large number (between 32 and 256 bits in size) - but the effort to extract it from the database is insignificant next to extracting the full blob content. For insertion into the database, you should use a byte[] since data has not been uploaded to the database yet. Thus, use the Blob class only for extraction.

Conclusion: use the java.sql.Blob class for extraction whenever you can.

12) I have the choice of manipulating database data using a String or a java.sql.Clob. Which has best performance?
 

java.sql.Clob, since it does not extract any data from the database until you explicitly ask it to. The Java platform 2 type Clob wraps a database locator (which is essentially a pointer to char). That pointer is a rather large number (between 32 and 256 bits in size) - but the effort to extract it from the database is insignificant next to extracting the full Clob content. For insertion into the database, you should use a String since data need not been downloaded from the database. Thus, use the Clob class only for extraction.

Conclusion: Unless you always intend to extract the full textual data stored in the particular table cell, use the java.sql.Clob class for extraction whenever you can.

13) Do I need to commit after an INSERT call in JDBC or does JDBC do it automatically in the DB?
 

If your autoCommit flag (managed by the Connection.setAutoCommit method) is false, you are required to call the commit() method - and vice versa.

14) How can I retrieve only the first n rows, second n rows of a database using a particular WHERE clause ? For example, if a SELECT typically returns a 1000 rows, how do first retrieve the 100 rows, then go back and retrieve the next 100 rows and so on ?
 

Use the Statement.setFetchSize method to indicate the size of each database fetch. Note that this method is only available in the Java 2 platform. For Jdk 1.1.X and Jdk 1.0.X, no standardized way of setting the fetch size exists. Please consult the Db driver manual.

15) What does ResultSet actually contain? Is it the actual data of the result or some links to databases? If it is the actual data then why can't we access it after connection is closed?
 

A ResultSet is an interface. Its implementation depends on the driver and hence ,what it "contains" depends partially on the driver and what the query returns.

For example with the Odbc bridge what the underlying implementation layer contains is an ODBC result set. A Type 4 driver executing a stored procedure that returns a cursor - on an oracle database it actually returns a cursor in the database. The oracle cursor can however be processed like a ResultSet would be from the client.

Closing a connection closes all interaction with the database and releases any locks that might have been obtained in the process.

16) What are SQL3 data types?
 

The next version of the ANSI/ISO SQL standard defines some new datatypes, commonly referred to as the SQL3 types. The primary SQL3 types are:

STRUCT: This is the default mapping for any SQL structured type, and is manifest by the java.sql.Struct type.

REF: Serves as a reference to SQL data within the database. Can be passed as a parameter to a SQL statement. Mapped to the java.sql.Ref type.

BLOB: Holds binary large objects. Mapped to the java.sql.Blob type.

CLOB: Contains character large objects. Mapped to the java.sql.Clob type.

ARRAY: Can store values of a specified type. Mapped to the java.sql.Array type.

You can retrieve, store and update SQL3 types using the corresponding getXXX(), setXXX(), and updateXXX() methods defined in ResultSet interface

17) 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.

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

SELECT NAME FROM IDENTIFIERS WHERE ID LIKE '\_%' {escape '\'}

finds identifier names that begin with an underbar.

18) What is SQLJ and why would I want to use it instead of JDBC?
 

SQL/J is a technology, originally developed by Oracle Corporation, that enables you to embed SQL statements in Java. The purpose of the SQLJ API is to simplify the development requirements of the JDBC API while doing the same thing. Some major databases (Oracle, Sybase) support SQLJ, but others do not. Currently, SQLJ has not been accepted as a standard, so if you have to learn one of the two technologies, I recommend JDBC.

19) How do I insert an image file (or other raw data) into a database?
 

All raw data types (including binary documents or images) should be read and uploaded to the database as an array of bytes, byte[]. Originating from a binary file,

1. Read all data from the file using a FileInputStream.
2. Create a byte array from the read data.
3. Use method setBytes(int index, byte[] data); of java.sql.PreparedStatement to upload the data.

20) How can I pool my database connections so I don't have to keep reconnecting to the database?

• you gets a reference to the pool
• you gets a free connection from the pool
• you performs your different tasks
• you frees the connection to the pool

Since your application retrieves a pooled connection, you don't consume your time to connect / disconnect from your data source.