INTRODUCTION TO JDBC

What is JDBC?

JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

The JDBC library includes APIs for each of the tasks commonly associated with database usage:

• Making a connection to a database
• Creating SQL or MySQL statements
• Executing that SQL or MySQL queries in the database
• Viewing & Modifying the resulting records

Fundamentally, JDBC is a specification that provides a complete set of interfaces that allows for portable access to an underlying database. Java can be used to write different types of executables, such as:

• Java Applications
• Java Applets
• Java Servlets
• Java ServerPages (JSPs)
• Enterprise JavaBeans (EJBs)

All of these different executables are able to use a JDBC driver to access a database and take advantage of the stored data.
JDBC provides the same capabilities as ODBC, allowing Java programs to contain database-independent code.

Establishing A Connection

The first thing to do, of course, is to install Java, JDBC and the DBMS on your working machines. Since we want to interface with an Oracle database, we would need a driver for this specific database as well. Fortunately, we have a responsible administrator who has already done all this for us on the Leland machines.

As we said earlier, before a database can be accessed, a connection must be opened between our program(client) and the database(server). This involves two steps:

• Load the vendor specific driver :Why would we need this step? To ensure portability and code reuse, the API was designed to be as independent of the version or the vendor of a database as possible. Since different DBMS's have different behavior, we need to tell the driver manager which DBMS we wish to use, so that it can invoke the correct driver.

                    An Oracle driver is loaded using the following code snippet:

                    Class.forName("oracle.jdbc.driver.OracleDriver")

• Make the connection : Once the driver is loaded and ready for a connection to be made, you may create an instance of a Connection object using:

      Connection con = DriverManager.getConnection(  "jdbc:oracle:thin:@dbaprod1:1544:SHR1_PRD",        username, passwd);

Okay, lets see what this jargon is. The first string is the URL for the database including the protocol (jdbc), the vendor (oracle), the driver (thin), the server (dbaprod1), the port number (1521), and a server instance(SHR1_PRD). The username and passwd are your username and password, the same as you would enter into SQLPLUS to access your account.

That's it! The connection returned in the last step is an open connection which we will use to pass SQL statements to the database. In this code snippet, con is an open connection, and we will use it below. 

Note:The values mentioned above are valid for our (Leland) environment. They would have different values in other environments.

Creating JDBC Statements

A JDBC Statement object is used to send your SQL statements to the DBMS, and should not to be confused with an SQL statement. A JDBC Statement object is associated with an open connection, and not any single SQL Statement. You can think of a JDBC Statement object as a channel sitting on a connection, and passing one or more of your SQL statements (which you ask it to execute) to the DBMS.

An active connection is needed to create a Statement object. The following code snippet, using our Connection object con, does it for you:

    Statement stmt = con.createStatement() ;

At this point, a Statement object exists, but it does not have an SQL statement to pass on to the DBMS. We learn how to do that in a following section.

Creating JDBC PreparedStatement

Sometimes, it is more convenient or more efficient to use a PreparedStatement object for sending SQL statements to the DBMS. The main feature which distinguishes it from its superclass Statement, is that unlike Statement, it is given an SQL statement right when it is created. This SQL statement is then sent to the DBMS right away, where it is compiled. Thus, in effect, a PreparedStatement is associated as a channel with a connection and a compiled SQL statement.

The advantage offered is that if you need to use the same, or similar query with different parameters multiple times, the statement can be compiled and optimized by the DBMS just once. Contrast this with a use of a normalStatement where each use of the same SQL statement requires a compilation all over again.

PreparedStatements are also created with a Connection method. The following snippet shows how to create a parameterized SQL statement with three input parameters:

      PreparedStatement prepareUpdatePrice = con.prepareStatement(

                "UPDATE Sells SET price = ? WHERE bar = ? AND beer = ?");

Before we can execute a Prepared Statement, we need to supply values for the parameters. This can be done by calling one of the setXXX methods defined in the class Prepared Statement. Most often used methods are setInt, setFloat, setDouble, setString etc. You can set these values before each execution of   the prepared statement.

Continuing the above example, we would write:

   prepareUpdatePrice.setInt (1, 3);

   prepareUpdatePrice.setString (2, "Bar Of Foo");

   prepareUpdatePrice.setString (3, "BudLite");

Executing CREATE/INSERT/UPDATE Statements
 Executing SQL statements in JDBC varies depending on the ``intention'' of the SQL statement. DDL (data definition language) statements such as table creation and table alteration statements, as well as statements to update the table contents, are all executed using the method executeUpdate. Notice that these commands change the state of the database, hence the name of the method contains ``Update''.
The following snippet has examples of executeUpdate statements.

   Statement stmt = con.createStatement ();
   stmt.executeUpdate ("CREATE TABLE Sells” +
      "(bar VARCHAR2 (40), beer VARCHAR2 (40), price REAL)" );
   stmt.executeUpdate ("INSERT INTO Sells " +
      "VALUES ('Bar Of Foo', 'BudLite', 2.00)" );
   String sqlString = "CREATE TABLE Bars " +
      "(name VARCHAR2 (40), address VARCHAR2 (80), license INT)" ;
   stmt.executeUpdate (sqlString);

Since the SQL statement will not quite fit on one line on the page, we have split it into two strings concatenated by a plus sign(+) so that it will compile. Pay special attention to the space following "INSERT INTO Sells" to separate it in the resulting string from "VALUES". Note also that we are reusing the   same Statement object rather than having to create a new one.

When executeUpdate is used to call DDL statements, the return value is always zero, while data modification statement executions will return a value greater than or equal to zero, which is the number of tuples affected in the relation.

While working with a Prepared Statement, we would execute such a statement by first plugging in the values of the parameters (as seen above), and then invoking the executeUpdate on it.
      int n = prepareUpdatePrice.executeUpdate() ;

Executing SELECT Statements
As opposed to the previous section statements, a query is expected to return a set of tuples as the result, and not change the state of the database. Not surprisingly, there is a corresponding method called executeQuery, which returns its results as a ResultSet object:

   String bar, beer;
   Float price;

   ResultSet rs = stmt.executeQuery ("SELECT * FROM Sells");
   While (rs.next() ) {
      Bar = rs.getString ("bar");
      Beer = rs.getString ("beer");
      Price = rs.getFloat ("price");
      System.out.println (bar + “sells " + beer + " for " + price + " Dollars.");
   }

The bag of tuples resulting from the query are contained in the variable rs which is an instance of ResultSet. A set is of not much use to us unless we can access each row and the attributes in each row. The ResultSetprovides a cursor to us, which can be used to access each row in turn. The cursor is initially set just before the first row. Each invocation of the method next causes it to move to the next row, if one exists and return true, or return false if there is no remaining row.

We can use the getXXX method of the appropriate type to retrieve the attributes of a row. In the previous example, we used getString and getFloat methods to access the column values. Notice that we provided the name of the column whose value is desired as a parameter to the method. Also note that the VARCHAR2 type bar, beer have been converted to Java String, and the REAL to Java float.

Equivalently, we could have specified the column number instead of the column name, with the same result. Thus the relevant statements would be:

      bar = rs.getString(1);
      price = rs.getFloat(3);
      beer = rs.getString(2);

While working with a Prepared Statement, we would execute a query by first plugging in the values of the parameters, and then invoking the execute Query on it.

                  ResultSet rs = prepareUpdatePrice.executeQuery () ;