Java / JDBC

Java Database Connectivity (JDBC) is a standard Java API for database-independent connectivity between Java and a wide range of databases.

JDBC is language and platform dependent. On the other hand, the ODBC is language and platform dependent.

ODBC(Open Database Connectivity) is complex and hard to learn. However, the code for JDBC is simpler.

JDBC Driver is a software component that enables Java application to interact with the database.

There are 4 types of driver.

• JDBC-ODBC Bridge or Type 1,
• Native API or Type 2 (partially implemented using Java),
• Network protocol driver or Type 3 (complete Java),
• Thin driver or Type 4 driver (complete Java).

Thin driver directly converts the JDBC calls into vendor database specific protocols without any intermediate transformation. It is implemented using Java.

Advantages: faster performance compared to the other drivers.

Disadvantages: database specific and hence dependent on type of database.

This driver converts the JDBC calls into ODBC function calls and utilizes an ODBC driver to connect to the database. This type of driver is not recommended.

The advantage is easy to use and connect to the database while the disadvantages are,

• Degraded performance due to ODBC call conversion,
• Requires ODBC driver to be installed on client machine.

Native-API driver utilizes the client side native libraries to connect to the database by converting JDBC calls to native API calls. It is implemented partially using Java

The performance is better than the JDBC ODBC driver however the native libraries need to be installed on each client machine.

In type 3 driver, JDBC clients use standard network sockets to communicate with the application server and the server translates it to the call format supported by the database and sent to the database server.

The main advantage is no client libraries required to install on client machines and the driver is compatible with multiple database types.

Thin driver or Type 4 driver is the pure Java driver as it is completely implemented using Java.

JDBC is a Java module and having the driver implemented in Java makes it easier to invoke methods directly without any additional translations.

JDBC API enables loose coupling by using Java REfelection API. Class.forName() method is usually used to register the drivers that make sure application doesn't work directly with Drivers API. This also enables to change drivers easily when we migrate from one database to the other.

Creating JDBC connection in a Java program involves 2 steps.

Load the JDBC Driver using Class.forName (String driverClass). Driver gets registered with the DriverManager and loads.

Invoke the getConnection() method of DriverManager to obtain a JDBC connection. getConnection also takes Database URL, user name and password as arguments.

The driver is a contract between a Java program and the data store. It enables to write standardized code to query database and abstracts its implementation and how the call is physically made to the database.

DriverManager is a factory class that is responsible for maintaining the single instance of each registered driver. DriverManager eliminates having multiple instances of the same driver as it is Singleton.

The DriverManager acts as a registry and provides lookup mechanism by taking the connection URL and finding the suitable driver for it. When a driver understands the URL, DriverManager passes the URL to it to create the database connection.

From the JDBC connection object, the class DatabaseMetaData that describes the database can be retrieved by calling getMetaData method.

JDBC statements helps to issue sql queries to the database using JDBC connection. createStatement method from connection object can be called to create a JDBC statement. Once created the SQL query can be passed to it and executing using different methods such as execute, executeUpdate and executeQuery.

execute method can be used with any type of SQL statements and it returns a boolean. A true indicates that the execute method returned a result set object which can be retrieved using getResultSet method. false indicates that the query returned an int value or void. execute method can run both select and insert/update statements.

executeQuery method execute statements that returns a result set by fetching some data from the database. It executes only select statements.

executeUpdate method execute sql statements that insert/update/delete data at the database. This method return int value representing number of records affected; Returns 0 if the query returns nothing. The method accepts only non-select statements.

JDBC PreparedStatement object stores the precompiled SQL statement that can be used efficiently to execute the query multiple times.

The Prepared Statement may be parametrized. It also provides a bunch of setter methods (setInt, setString and so on) to set the IN parameters for the query and it is compatible with specified sql types as input parameter. As an example, for INTEGER sql datatype use setInt method.

PreparedStatement pstmt = con.prepareStatement("UPDATE DEPARTMENT
                                     SET EMP_COUNT = ? WHERE ID = ?");
   pstmt.setBigDecimal(1, 1500);
   pstmt.setInt(2, 100);

PreparedStatement is precompiled so it eliminates the overhead of compiling again when the query is executed. PreparedStatement also automatically escapes special character and eliminates sql injection threat.

Use the setNull method to bind null to the parameter. The setNull method accepts two parameter, index and the sql type as arguments.

PreparedStatement ps = conn.prepareStatement(sqlQuery);
ps.setNull(5, java.sql.Types.INTEGER);

PreparedStatement prevents SQL injection threats as it automatically escapes the special characters.

Precompiles and caches the SQL statement for faster execution and to reuse the same SQL statement in batch.

PreparedStatement provides clear separation between the query code and the parameter values that improves readability.

PreparedStatement provides convenient way to transform Java object types to SQL Data types to pass input parameters.

• Registering the JDBC driver class,
• Creating connection using DriverManager,
• Creating statementd,
• Executing queries,
• and closing connection.

The java.sql package has the JDBC API core interface and classes.

Core interfaces include Connection, Statement, DatabaseMetaData, PreparedStatement, CallableStatement, ResultSet, ResultSetMetaData and so on.

Core classes include DriverManager, Types, SQLException and many more.

There are 3 types.

• Statement,
• PreparedStatement,
• and CallableStatement.

The Connection interface manages the database transactions by providing commit, rollback methods.

There are 2 layers.

• JDBC API that bridges application to JDBC Manager,
• and JDBC Driver API that connects JDBC connection with the driver.

The Complete JDBC architecture implements bridge design pattern, an abstract concept that decouples abstraction from implementation so that both can vary independently.

Other design patterns also involved in the JDBC API. DriverManager.getConnection Implements static factory method pattern, connection and statement implements Transactional pattern and proxy pattern, ResultSet iterator and data mapper pattern.

Yes. The connection pool implementations are threadsafe.

Using Callable statement interface, the stored procedure and the functions can be executed.

CallableStatement myStatement = conn.prepareCall ("{call myDbStoredProc (?,?)}");

JDBC ResultSet object is similar to a table of data representing a database result set, which is generated by executing a statement that queries the database.

ResultSet object maintains a cursor that points to the current row, initially points to the first row. Calling next() on the result set moves the cursor to next row and returns false when the last row is already reached.

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed.

A ResultSet is readable only and the cursor moves only forward by default. It is also possible to create scrollable and updateable ResultSet. We can use ResultSet getter methods with column name or index starting from 1 to retrieve the column data.

Based on the cursor scroll behavior, there are 3 types.

ResultSet.TYPE_FORWARD_ONLY, the default type where cursor can only move forward in the result set.

ResultSet.TYPE_SCROLL_INSENSITIVE cursor can move forward and backward, and the resultset is insensitive to changes made by others to the database after the result set was created.

ResultSet.TYPE_SCROLL_SENSITIVE, the cursor can move In both direction, and the result set is sensitive to changes made by others to the database after the result set has been created.

Based on the concurrency there are 2 types of ResultSet object.

ResultSet.CONCUR_READ_ONLY: The result set is read only, this is the default concurrency type.

ResultSet.CONCUR_UPDATABLE: We can use ResultSet update method to update the row data.

To minimize the round trips to the database, pre fetching gets multiple rows of data each time data is fetched and the extra row data is stored in client-side buffers for later access by the client. The number of rows to prefetch can be set using setRowPrefetch method.

The external changes made at the data store are not reflected while the result is open in TYPE_SCROLL_INSENSITIVE, where as TYPE_SCROLL_SENSITIVE does.

Not all databases or JDBC Drivers support all ResultSet types or concurrency. DatabaseMetaData.supportsResultSetType checks if the specified ResultSet type supported and the DatabaseMetaData.supportsResultSetConcurrency finds if the specified concurrency level is supported.

Class.forName method returns java.lang.Class object.

Yes. Multiple threads can share a connection object however the oracle JDBC driver API methods are synchronized, other threads need to hold while one thread is executing.

By invoking setAutoCommit method on a connection object with false as argument, we can manually handle transactions.

Implement connection pooling.

Reuse prepared statement for similar queries so that the DB will reuse the previous query plan for faster performance.

Always close the PreparedStatement when done. With Java 7, use try-with-resources for auto closing.

try (PreparedStatement pstmt = conn.prepareStatement(sqlSt);
      ResultSet r = pstmt.executeQuery();) {
    // do some logic with ResultSet 
}

The isolation levels are,

• TRANSACTION_NONE,
• TRANSACTION_READ_COMMITTED,
• TRANSACTION_READ_UNCOMMITTED,
• TRANSACTION_REPEATABLE_READ,
• TRANSACTION_SERIALIZABLE.

The Connection interface maintains the session with the database and facilitates transaction management. It has factory methods that return the instances of Statement, PreparedStatement, CallableStatement, and DatabaseMetaData.

The ResultSet object represents a row or record in a database table. It can be used to fetch and change the cursor pointer and retrieves the data from the database.

Using PreparedStatement interface, we can store and retrieve images.

The setBinaryStream() method of PreparedStatement sets binary information into the parameterIndex.

PreparedStatement ps=con.prepareStatement("insert into images values(?,?)");  
ps.setInt(1,400); //Image index  
  
FileInputStream fis=new FileInputStream("d:\\myImage.jpg");  
ps.setBinaryStream(2,fis,fis.available());  
int i=ps.executeUpdate();  

JDBC API provides Batch Processing feature through which we can execute bulk of queries in one go for a database.

JDBC API supports batch processing through Statement and PreparedStatement addBatch() and executeBatch() methods.

Batch Processing is faster than executing one statement at a time as the number of database calls are less.

setMaxRows(int i) method limits the number of rows that the database returns from the query. The same can be done in SQL query itself, for example in MySQL, we can use the LIMIT clause to set the maximum number of rows returned by the query.

setFetchSize issues the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects generated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.

There are 2 types of locking in JDBC by which we can handle multiple users trying to update the same record.

Optimistic locking locks the record only when update happens. Optimistic locking does not use exclusive locks when reading or selecting the record.

Pessimistic Locking locks the record as soon as it selects the row to update. This locking strategy guarantees the changes are made safely and consistently.

No, it doesn't.

RowSet extends ResultSet and adds JDBC API support to the Java Bean component model. RowSet can be connected or disconnected.

Disconnected RowSet can be serialized while ResultSet cannot be serialized.

A connected RowSet always keeps connection with database, while a disconnected RowSet connects to database, get the data and then close the connection.

Disconnected RowSet are Serializable while connected are not.

Java provides 5 types of RowSet.

• JdbcRowSet,
• JoinRowSet,
• CachedRowSet,
• FilteredRowSet
• and WebRowSet.

JdbcRowSet is connected RowSet while the rest is disconnected row sets.

The JdbcRowSet implementation is a wrapper for ResultSet object and has following advantages over ResultSet.

• Uses ResultSet object as a JavaBeans component.
• A JdbcRowSet can be used as a JavaBeans component, thus it can be created and configured at design time and executed at run time.
• used to make a ResultSet object scrollable and updatable.

SQLWarning is a subclass of SQLException that holds database access warnings. Warnings do not stop the execution of a specific application, as exceptions do.

A warning may be retrieved on the Connection object, the Statement object, PreparedStatement and CallableStatement objects, or on the ResultSet using getWarnings method.

SQLWarning warning = stmt.getWarnings();

   while (warning != null)
   {
       System.out.println("Message: " + warning.getMessage());
       System.out.println("SQLState: " + warning.getSQLState());
       System.out.println("Vendor error code: " + warning.getErrorCode());
       warning = warning.getNextWarning();
   }

"No suitable driver" may occur during a call to the DriverManager.getConnection method due to the following reasons:

• Failed to load the appropriate JDBC drivers before calling the getConnection method.
• Due to invalid JDBC URL, not being recognized by JDBC driver.
• One or more the shared libraries needed by the bridge cannot be loaded.

• Load the DB specific JDBC driver that communicates with the database.
• Open the connection to the database, for sending SQL statements and getting results.
• Create JDBC Statement object containing SQL query.
• Execute statement which returns the result set. ResultSet contains the tuples of DB table as a result of SQL query.
• Process the result set.
• Close the connection.

java.sql.Blob is used when extraction of the data is performed. Blob does not extract any data from the database until we trigger a query to the database.

byte array is used for writing/inserting data to the database.