JDBC TUTORIAL


Feature from JDBC1.0                                      Features From JDBC2.0
 
 
 
1.Establishing a connection 2.Creating, Inserting , Updating...
3.Using Prepared Statement 4.Return value of executeUpdate method
5.Using Transaction 6.Transaction Isolation Level
7.Stored Procedures 8.Retrieving Exceptions and Warnings
Establishing a Connection: Creating, Inserting , Updating, Deleting and Querying a table: Using Prepared Statement
Concept:

Prepared statemets are sent to the DBMS for precompilation at the time of creation. So in effect a prepared statement object points to a precompiled statement which could be exceuted over and over again without the overhead of compilation by the DBMS each time. The statement information of the prepared statement are passed as a string argument at the time of instantiation.

Prepared statements can take in zero or more arguments. This allows for execution of the same statement with different arguments.

Creating a PreparedStatement:

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

Setting values for the input arguments and executing a prepared statement:

ps.setInt(1,12);
ps.setString(2,'Columbian');
ps.executeUpdate();

The values set for the parameters can be cleared by calling the clearParameters() method.

Return value of executeUpdate method
The return value of executeUpdate method is an int.
The int value returned by executeUpdate method used to delete or update a table returns indicates the number of rows the statement affected.
If the executeUpdate statement was used to execute a DDL statement like CREATE TABLE, it returns 0.

So when an executeUpdate method returns 0 it could mean one of the two things: (a) The statement executed was a DML statement and it affected no rows of the table. (b) The statement executed was a DDL statement.

Using Transaction
By default a new open Connection has the auto-commit mode enabled. This means the result of a statement is commited to the DBMS as soon as it is completed. A statement is said to be completed when all of its result set data and update counts has been retrieved.
If a number of statements are to be executed as a unit, then the auto-commit mode need to be disabled. This is done as :
con.setAutoCommit(false).
This will allows a number of statements to be committed as a single whole when the con.commit() method is called. All statements exceuted since the previous call to the commit method, are included in the transaction.

If something goes wrong within a transaction in one of the statements, calling the con.rollback() method will revert all changes done within the transaction. The rollback method is generally called from within a try/catch block of an SQLException that surrounds the statements of the transaction.

Transaction Isolation Level
Transaction Isolation Level defines the degree of concurrency to data access by multiple users. It determines how locks are set to rows and tables of the DBMS by a transaction so as to limit access to data by other transactions.
Stored Procedures
A Stored Procedure is a group of SQL statements that form a logical unit and performs a definite task. Stored procedures are stored with the database server and could be excecuted by calling from an application code. A stored procedure can have input and output parameters.

Creating a Stored Procedure:

The syntax for creating stored procedures has wide variation across DBMS. The following presents a sample code:

String createProcedure = "create procedure SHOW_SUPPLIERS " +
                                   "as " +
                                   "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
                                   "from SUPPLIERS, COFFEES " +
                                   "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
                                   "order by SUP_NAME";

stmt.executeUpdate(createProcedure);

The stored procedure will be compiled and srored as a database object. It could be now called like a method.

Calling a Stored Procedure:

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

If the stored procedure contained a single DDL OR UPDATE/DELETE statement, then executeUpdate method could be used.

If the stored procedue contained more than one SQL statement it will produce more than one result set, more than one update count or some combination of result set and update counts. In such a case the execute method should be used.

Stored procedure can have IN, OUT and INOUT parameters.

Retrieving Exceptions and Warnings
 
Exception while loading the driver:

    try {
                  Class.forName("myDriverClassName");
          } catch(java.lang.ClassNotFoundException e) {
                  System.err.print("ClassNotFoundException: ");
                  System.err.println(e.getMessage());
          }

SQLException

The SQLException.getMessage method returns the error message corresponding to the SQLException.
The SQLException.getSQLState method return X/OPEN standard error code correpsonding to the SQLException.
The SQLException.getErrorCode methos return the DBMS specific code for the error.
The SQLException.getNextException method return the next SQLException in the chain.

   try {
                  // Code that could generate an exception goes here.
                  // If an exception is generated, the catch block below
                  // will print out information about it.
          } catch(SQLException ex) {
                  System.out.println("\n--- SQLException caught ---\n");
                  while (ex != null) {
                          System.out.println("Message:   "
                                             + ex.getMessage ());
                          System.out.println("SQLState:  "
                                             + ex.getSQLState ());
                          System.out.println("ErrorCode: "
                                             + ex.getErrorCode ());
                          ex = ex.getNextException();
                          System.out.println("");
                  }
         }

SQLWarning

The methods used are just the same as in SQLException.
getNextWarning is used to get the next warning in the chain.

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. If getWarnings returns a warning, you can call the SQLWarning method
getNextWarning on it to get any additional warnings. Executing a statement automatically clears the
warnings from a previous statement, so they do not build up. This means, however, that if you want to
retrieve warnings reported on a statement, you must do so before you execute another statement.
 

          Statement stmt = con.createStatement();
          ResultSet rs = stmt.executeQuery("select COF_NAME from COFFEES");
          while (rs.next()) {
                  String coffeeName = rs.getString("COF_NAME");
                  System.out.println("Coffees available at the Coffee Break:  ");
                  System.out.println("    " + coffeeName);
                  SQLWarning warning = stmt.getWarnings();
                  if (warning != null) {
                          System.out.println("\n---Warning---\n");
                          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());
                                  System.out.println("");
                                  warning = warning.getNextWarning();
                          }//while
                }//if

                  SQLWarning warn = rs.getWarnings();
                  if (warn != null) {
                          System.out.println("\n---Warning---\n");
                          while (warn != null) {
                                  System.out.println("Message: "
                                                     + warn.getMessage());
                                  System.out.println("SQLState: "
                                                     + warn.getSQLState());
                                  System.out.print("Vendor error code: ");
                                  System.out.println(warn.getErrorCode());
                                  System.out.println("");
                                  warn = warn.getNextWarning();
                          }//while
                  }//if
          }//while

The most common SQLWarning is DataTruncationWarning which has an SQLState of  01004.


New Features From JDBC2.0
 
 
1.Creating a Scrollbale Result Set 2.Methods to move the cursor
3.Using Scrollable Result Set for Updation 4.Inserting a row:
5.Deleting a Row: 6.Seeing the Latest Rows
7.Batch Updates 8.Exceptions with batch updates
8.SQL3 DATATYPES
Scrollable Result Set
Feature:
Allows a result set's cursor to move backward and forward. It also allows to position the cursor on a paticular row and also to check the position of the cursor.

Creating a Scrollbale Result Set:

Statement stmt  = con.createStatement(int ResultSetType, int ResultSetUpdatibility)
ResultSet rs = stmt.executeQuery("SELECT * FROM COFFEES");

In the above createStatement:

ResultSetType : defines the scrollabilty of the result set. It could be any one of the 3 constansts:
ResultSet.TYPE_FORWARD_ONLY : The cursor can go forward only. This is the default type of the result set created by not specifying an argument in the createStatement method as in API1.0
ResultSet.TYPE_SCROLL_INSENSITIVE: Creates a scrollable result set that does not reflect changes to it while it is open
ResultSet.TYPE_SCROLL_SENSITIVE: Creates a scrollable result set that reflect changes to it while it is open
ResultSetUpdatibility: defines whether the result set is read only or could be used for updating also. It could take one of the following 2 constants:
ResultSet.CONCUR_READ_ONLY: The result set can be used for reading rows only. This is the defalut type as in createStatement of API1.0
ResultSet.CONCUR_UPDATABLE: The result set could be used for updating rows in the underlying table.
Methods to move the cursor in a Scrollable Result set

next : Moves the cursor one row forward. Please note that a new ResultSet has its cursor pointed to the row before first, so access the first row the next method need to be called necessarily.
previous: Moves teh cursor backward one row.

Both next and previous returns false when they fall out of the ends of the result set.

afterLast: Move the cursor to the position after the last row in the result set.
beforeFirst: Move the cursor to the position before the first row in the result set.
first: Move the sursor to the first row.
last: Move the cusrsor to the last row.
absolute(int): Move the cursor to the specified row number of the result set. If a positive number is provided, move from the beginning else from the last row. So absolute(1) moves to the first row and absolute(-1) to the last row.
relative(int): Move the cursor relative to the current cursor position. A postive value moves forward while a negative values moves in the backward direction.
getrow(): returns the number of the row where thr cursor is currently positioned.

isFirst() : returns true if the cursor is at the first row
isLast(): returns true if the cursor is at the last row
isBeforeFirst() : returns true if the cursor is placed before first row
isAfterLast() : returns true if the cusror is placed after last row
 

Using Scrollable Result Set for Updation

First we create an updateable result set. It need not necessarily be a scrollable one though it is convenient if we want to move to different rows for updating or insert new rows etc:

Connection con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName");
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                               ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

/* Update a row: (Say the last row) */

uprs.last();
uprs.updateFloat("PRICE", 778.99);
uprs.updateRow();

To cancel the update the cancelRowUpdates method can be called before calling the updateRow method.
It cancels all updates on the row.
For a cursor the row number starts from 1.

Inserting a row:

          Connection con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName");
          Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                               ResultSet.CONCUR_UPDATABLE);
          ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");
          uprs.moveToInsertRow();
          uprs.updateString("COF_NAME", "Kona");
          uprs.updateInt("SUP_ID", 150);
          uprs.updateFloat("PRICE", 10.99);
          uprs.updateInt("SALES", 0);
          uprs.updateInt("TOTAL", 0);
          uprs.insertRow();

The insertRow puts the new row both into the result set as well as the database.
Once the insert has been done you are ready to insert a new row. The cursor is still at the insert row.

After the row has been inserted all cursor movement methods can be called and also another method moveToCurrentRow. This takes the cursor back to the row where the cursor was there prior to moving to the insert row. Also the previous, relative, next etc are relative to this prior current position.

Deleting a Row:

Move the cursor to the row you want to delete and then call deleteRow method:

uprs.absolute(4);
uprs.deleteRow();

Depending on the JDBC driver the deleted row will no longer be there in the Result set or a Hole will be left in its place. In the latter case the row numbers of the result set does not change.
 

Seeing the Latest Rows
Calls the refreshRow() method to see the latest rows of the query. This is a costly operation esp if the query returns more than 1 row.
 

Batch Updates
With the JDBC 2.0 API, Statement, PreparedStatement, and CallableStatement objects have the ability to maintain a list of commands that can be submitted together as a batch. They are created with an associated list, which is initially empty. You can add SQL commands to this list with the method addBatch, and you can empty the list with the method clearBatch. You send all of the commands in the list to the database with the method executeBatch.
An Example:

          con.setAutoCommit(false);
          Statement stmt = con.createStatement();
          stmt.addBatch("INSERT INTO COFFEES" +
                        "VALUES('Amaretto', 49, 9.99, 0, 0)");
          stmt.addBatch("INSERT INTO COFFEES" +
                        "VALUES('Hazelnut', 49, 9.99, 0, 0)");
          stmt.addBatch("INSERT INTO COFFEES" +
                        "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
          stmt.addBatch("INSERT INTO COFFEES" +
                        "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");
          int [] updateCounts = stmt.executeBatch();

The executeBatch method returns an array of update counts for each of the statement in the Batch.

Exceptions with batch updates

Any statement in the batch can throw SQLException. Also executeBatch will throw SQLException if the batch has a statement that returns a result set. This is because executeBatch is supposed to return an array of update counts and trying to make a result set of this array of int will result to throwing the SQLException. Not all statements in the batch might succeed. The array length of the update counts array will tell the number of sucessful operations.

If no SQLException was thrown, you know that there were no access problems and that all of the
commands produce update counts. If one of the commands cannot be executed for some other reason, the
method executeBatch will throw a BatchUpdateException . In addition to the information that all
exceptions have, this exception contains an array of the update counts for the commands that executed
successfully before the exception was thrown. Because the update counts are in the same order as the
commands that produced them, you can tell how many commands were successful and which commands
they are.

BatchUpdateException is derived from SQLException . This means that you can use all of the methods
available to an SQLException object with it. The following code fragment prints the SQLException
information and the update counts contained in a BatchUpdateException object. Because
getUpdateCounts returns an array of int , it uses a for loop to print each of the update counts.
 

          try {
                  // make some updates
          } catch(BatchUpdateException b) {
                  System.err.println("SQLException: " + b.getMessage());
                  System.err.println("SQLState:  " + b.getSQLState());
                  System.err.println("Message:  " + b.getMessage());
                  System.err.println("Vendor:  " + b.getErrorCode());
                  System.err.print("Update counts:  ");
                  int [] updateCounts = b.getUpdateCounts();
                  for (int i = 0; i < updateCounts.length; i++) {
                          System.err.print(updateCounts[i] + "   ");
                  }
          }


SQL3 DATATYPES
 
New SQL3 Datatypes
 
BLOB: Binary Large Object Used to store very large amounts of data as raw bytes
CLOB: Character Large Object Used to store very large amounts of data as Characters
ARRAY A number od values of the same data type
UDT: User Defined Data Types Structured types and Distinct types
Mapping of SQL3 Datatypes with new Data Types of Java
 
SQL3 DataType Java Data Type
BLOB Blob
CLOB Clob
ARRAY Array
Structured Data Type (UDT) Struct
REF instance Ref

Using the SQL3 Datatypes

The ResultSet.getXXX and CallableStatement.getXXX methods are used to access values of SQL3 datatypes. PreparedStatement.setXXX is used to store them and updateXXX to update them.
 
 
SQL3 Type getXXX Mehod setXXX method updateXXX method
BLOB getBlob setBlob updateBlob
CLOB getClob setClob updateClob
ARRAY getArray setArray updateArray
Structured type getObject setObject updateObject
REF getRef setRef updateRef

Concepts of Blob, Clob and Array Objects

Any instance of Blob, Clob and Array classes are logical pointer to the SQL BLOB, CLOB and ARRAY objects in the database server. The instances can be used to manipulate the actual objects without bringing in them in. This feature can improve performance as BLOB, CLOB and ARRAY objects can be very large.

There are methods in the Blob, Clob and the Array classes to actually bring in the SQL objects to the client (also called materializing). Once materialized, all the java programming features can be used on them. Say the ARRAY object can be materialized to an array and the elements of the array can be manipulated like that of a normal java array.

Struct and Distinct Type

The SQL Structured type and the distinct type are the 2 user defined data types that could be defined using the SQL CREATE TYPE statement.

Struct Type:

Here is an example of defining an SQL Structured data type:

CREATE TYPE  PLANE_POINT
(
        X  FLOAT,
        Y FLOAT
)

A Struct object is used to represent an SQL Structured type. It is different from Clob, or Blob Array as a Struct object contains  values for each of the attributes in the SQL structured type and is not just a logical pointer to the object in the database.

Example:
Suppose that a PLANE_POINT object is stored in column POINTS of table PRICES .

          ResultSet rs = stmt.executeQuery(
              "SELECT POINTS FROM PRICES  WHERE PRICE > 3000.00");
          while (rs.next()) {
                  Struct point = (Struct)rs.getObject("POINTS");
                  // do something with point
          }

If the PLANE_POINT object retrieved has an X value of 3 and a Y value of -5, the Struct object point will contain the values 3 and -5.

Distinct Type:
Here is  an example of creating a distinct type:

CREATE TYPE MONEY AS NUMERIC(10, 2)

This definition creates the new type called MONEY , which is a number of type NUMERIC that is always base10 with two digits after the decimal point. MONEY is now a datatype in the schema in which it was defined, and you can store instances of MONEY in a table that has a column of type MONEY.

An SQL distinct type is mapped to the type in the Java programming language to which its underlying type
would be mapped. For example, NUMERIC maps to java.math.BigDecimal , so the type MONEY maps
to java.math.BigDecimal . To retrieve a MONEY object, you use ResultSet.getBigDecimal or
CallableStatement.getBigDecimal ; to store a MONEY object, you use   PreparedStatement.setBigDecimal.