JDBC

What is JDBC ?

  • 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.

What are the two major components of JDBC ?

One implementation interface for database manufacturers, the other implementation interface for application and applet writers.

What is JDBC Driver interface ?

The JDBC Driver interface provides vendor-specific implementations of the abstract classes provided by the JDBC API.

Each vendors driver must provide implementations of the java.sql.Connection, Statement, PreparedStatement, CallableStatement, ResultSet and Driver.

What are the common tasks of JDBC ?

  • Create an instance of a JDBC driver or load JDBC drivers through jdbc.drivers
  • Register a driver
  • Specify a database
  • Open a database connection
  • Submit a query
  • Receive results

What packages are used by JDBC ?

There are 8 packages:

  • java.sql.Driver
  • Connection
  • Statement
  • PreparedStatement
  • CallableStatement
  • ResultSet
  • ResultSetMetaData
  • DatabaseMetaData.

What are the flow statements of JDBC ?

A URL string –> getConnection–> DriverManager–> Driver–> Connection–> Statement–> executeQuery–> ResultSet.

What are the steps involved in establishing a connection ?

This involves two steps: (1) loading the driver and (2) making the connection.

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:

For example,

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:

For example,

Class.forName("jdbc.DriverXYZ");

What Class.forName will do while loading drivers ?

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 a DBMS.

How can you make the connection ?

In establishing a connection is to have the appropriate driver connect to the DBMS. The following line of code illustrates the general idea:

For example,

String url = "jdbc:odbc:Fred";
Connection con = DriverManager.getConnection(url, "Fernanda", "J8");

How can you create JDBC statements ?

A Statement object is what sends your SQL statement to the DBMS. You simply create a Statement object and then execute it, 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. E.g. 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();

How can you retrieve data from the ResultSet ?

First JDBC returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. The following code demonstrates declaring the ResultSet object rs.
E.g.

ResultSet rs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

Second:

String s = rs.getString("COF_NAME");
The method getString is invoked on the ResultSet object rs , so getString will retrieve (get) the value stored in the column COF_NAME in the current row of rs.

What are the different types of Statements ?

1.Statement (use createStatement method) 2. Prepared Statement (Use prepareStatement method) and 3. Callable Statement (Use prepareCall)

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.
E.g.

PreparedStatement updateSales = con.prepareStatement("UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");

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;
E.g.

CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();

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
E.g.

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();
	}
}

How to Make Updates to Updatable Result Sets ?

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.
E.g.

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");

How many types of JDBC drivers available ?

There are 4 types of JDBC drivers available.
Type 1 : JDBC-ODBC bridge
It is used for local connection.
ex) 32bit ODBC in windows
Type 2 : Native API connection driver
It is connected by Native Module of dependent form of H/W like .dll or .so.
ex) OCI driver for local connection to Oracle
Type 3 : Network connection driver
Type 4 : Database Protocol driver
It is independent from H/W because this driver is %100 java.
ex) thin driver for local/global connection to Oracle
*** Type1, type 2, and type 3 are usually used.

Who implements the ResultSet’s next() methiod in JDBC ?

ResultSet is an interface, It doesn’t have any method implementation.

All databases support common operations of executing a query, traverse through the resultset, reading each column in a result, getting a connection, closing a connection etc.

So these standard operations are made as interface methods by Java. Therefore, the vendors who provide database drivers, implement this interface and hence they have to provide their own implementation for each method.

These drivers are present in the form of jars which you have to add to your project classpath to be able to communicate with that vendor specific database  connection like sqljdbc, odbc etc.

Why do we need to load JDBC driver class dynamically ?

The statically loading of the driver class or something like new com.mysql.jdbc.Driver(); will lead to a static link to this class as it will be part of your classes import statements. So as soon as you load this class and the com.mysql.jdbc.Driver class is not available it will crash.

Whereas the dynamically loading of driver class or something like Class.forName(“com.mysql.jdbc.Driver”).newInstance(); will try to load the class at runtime. It will also fail with a classnotfound but you could catch it and try to load another driver or show a nice message to the user.