Applied JDBC 
Peter Komisar  _©   Conestoga College  latest version 5.8 / 2010

JDBC Java SQL Mapping

In our cursory coverage of SQL we did not talk about
data types. SQL has many more data types then Java.
The mapping between the two worlds may vary depending
on the direction in which the conversion is taking place,
whether Java to SQL or SQL to Java.  Furthermore there
are variations when object types are mapped between
SQL and Java. The following charts show how the get /
set( ) methods of the Java API translate between the
different types.

The following link is a useful general reference for Java
JDBC and SQL. The resource supplies detail on all
aspects of JDBC and mapping between SQL and Java.

http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/GettingStartedTOC.fm.html

What we are leaving out for now

For simplicity we have left out the types CLOB, BLOB
Array, Struct, DISTINCT and JAVA_OBJECT which can
be visited later

The following tables show Java type conversion to JDBC
types.

JDBC Types Mapped to Java Types
 

Java Types Mapped to JDBC Types

This table shows the reverse of the above mapping. It's
shorter as there are fewer Java types. Where Java types
map to more than one SQL type, both are shown in the
same row. 

Java String type doesn't map to CHAR but instead to
VARCHAR or LONGVARCHAR. Also the BigDecimal
type doesn't map to SQL type DECIMAL but only to the
NUMERIC type.

The mapping for String will normally be VARCHAR but
will turn into LONGVARCHAR if the given value exceeds
the driver's limit on VARCHAR values. The same is true
for byte[ ] and VARBINARY and LONGVARBINARY values.
 
 
The following tables show Java type conversion to JDBC
types.

JDBC Types Mapped to Java Object Types

There is a slightly different mapping from JDBC types to
Java object types for the get / setObject methods. Notice
in the following table instead of TINYINT and SMALLINT
mapping to the corresponding wrapper class for byte and
short, they both map to the Integer wrapper class.

// small JDBC Integer types promote to Integer Java type
 
 

Java Object Types Mapped to JDBC Types Types

Again we have fewer mappings going from Java wrapper
classes to SQL types. For instance the Integer wrapper
type mapping only to the SQL INTEGER type.
 

Note, the mapping for String will normally be VARCHAR
but will turn into LONGVARCHAR if the given value exceeds
the driver's limit on VARCHAR values. The case is similar
for byte[] and VARBINARY and LONGVARBINARY values.

ResultSet Getter Methods

To extract the results that are of different types, ResultSet
has many getter methods. Below are listed just some of
them.

Sampling of ResultSet Getter Methods

• getBigDecimal( )
• getBlob( )
  
• getBoolean( )
• getByte( )
  
• getClob( )
  
• getCurrency( )
  
• getDate( )
• getDouble( )
  
• getFloat( )
  
• getInt( )
  
• getLong( )
  
• getObject( )
  
• getShort( )
  
• getTime( )
  

// primitives plus some of the other common types

Vendor Differences in How JDBC Types are Converted

An unfortunate fact of life, there are variations in how
different database vendors translate JDBC types to their
native database types. The following tables are lifted
from the reference noted above.

Tables from 'Mapping SQL and Java Types', The Sun site
// for reference only
 
http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/mapping.html#table1

.

// For an example, read the following on how Java Handles CLOBs

Quote from CLOB API 

"An SQL CLOB is a built-in type that stores a Character Large Object
as a column value in a row of a database table. By default drivers
implement a Clob object using an SQL locator(CLOB), which means
that a Clob object contains a logical pointer to the SQL CLOB data
rather than the data itself."

The JDBC API

We see there are vendor variations in how types are
translated between Java and SQL Databases. These
differences need to be accounted for if you are porting
applications across different vendor's databases.

// use types appropriate for a given database

Beyond the minor variations though, JDBC or Java
Database Connectivity still offers a single API to handle
database activity in a uniform and database independent
way.

The objective of JDBC has been to allow Java application
to meet a corporation's database requirements despite the
fact that the company has it's information deployed across
several different database platforms.

// JDBC facilitates database mngt. across different database platforms

JDBC Packages

The classic JDBC API resides in the java.sql package
and extensions are provided in the javax.mysql package.

SQL packages

• java.mysql

• javax.mysql

More recently, the javax.sql package was added ( as of JDK
1.4 ) and is primarily focused on server-side JDBC functions.
Added features include 'DataSource' support, which is the
J2EE model that simplifies JDBC driver and connection
management. As well, Connection pooling support is added
and greatly improves JDBC performance. The new package
also adds support for distributed transactions. Also added is
the RowSet interface. "A rowset can be used as a JavaBeans
component in a visual Bean development environment"
                                                                                    - JDK docs.

New Features in javax.mysql Package

• DataSource and RowSet Interfaces

• Connection Pooling Support

• Distributed Transaction Support

A JDBC application can access any of a number databases.
The details of the connectivity are managed by the JDBC
driver. The driver layer handles all aspects of connecting to
the given vendor-specific or custom database.

A vendor / developer provides implementations for the following
four key interfaces of JDBC. This is the 'in order of use' view.

JDBC Interfaces Listed in Order They are Used in Code

• Driver 

• Connection

• Statement

• ResultSet

The driver implementation is normally accessed by a user
through the DriverManager class or through the newer
DataSource interface. The DriverManager implementation
is responsible for loading, unloading and making driver
connections. The Connection, Statement and ResultSet
interfaces are all implemented by the driver developer as
part of the JDBC driver for the database management
system.

// The DriverManager is responsible for loading, unloading
// and making driver connections.

Another way to think of these interfaces is the Driver
interfacer is a low-level connector, while the Connection
Statement and ResultSet are all application layer
interfaces.

Layer View of the JDBC Interfaces

Driver

The Driver Interface

Every JDBC driver must implement the Driver interface.
The Driver interface describes six methods including the
essential connect( ) method. This method does the actual
connecting to the URL that represents the database. This
method is not normally called directly.

The connect( ) method can be accessed directly to 'hardwire'
to a particular driver. This will eliminate the DriverManager
iterating through it's list of drivers. Usually connect( ) is
called indirectly through the method getConnection( ) of
the DriverManager class.

The Driver Interface Methods

• acceptsURL(String url) // determines if driver can connect to URL.

• connect(String url, Properties info) // attempts to connect to URL.

• getMajorVersion() // driver's major version number.

• getMinorVersion() // driver's minor version number

• getPropertyInfo(String url, Properties info) // driver property info

• jdbcCompliant( ) // whether  driver is a JDBC Compliant_TM driver.

DriverManager Behaviour

If a resource is found matching the database described
in the URL, a TCP/IP connection is attempted. If this
connection attempt is successful, the method returns a
Connection object. (The driver vendor is responsible for
creating an implementation of the Connection interface
that is returned by the connect( ) method. The connect( )
method also has a form that takes a Properties object
that stores a user name and password. The method
throws an SQLException if the URL is valid but the
TCP/IP connection fails.

The Full Signature of the connect( ) Method

public Connection connect ( String url, Properties info )
throws SQLException
 

The JDBC URL   // URL to the database

Every JDBC application must provide at least one driver
implementation. Driver's locate and access the particular
database files using a special JDBC version or a URL. 

The JDBC URL follows the WWW standard URL syntax
as follows:

The JDBC URL

jdbc:< sub-protocol >:<sub-name >

where

• sub-protocol is the driver type usually the vendor's name

• sub-name is the network-encoded database name.

The sub-name will contain any network locating information
needed such as the host domain name or IP address, the
port to use, the database file name, a user_name and a
password.

Optionally, network indirection, can be used to hide away
the connection information. In this case the sub-protocol
field will provide a naming service which in turns resolves
a connection to a database based on having been supplied
some kind of alias.   // may involve aliasing
 

JDBC URL Examples

The first example shows a JDBC driver for a MySQL
database system located on the local machine for an
unspecified database file in the DB directory inside the
data directory inside the MySQL directory. You can create
a URL something like this to do local development using
MySQL on your personal machine.

Example 1     

String url =  "jdbc:mysql:///mysql/data/DB/";

The next example describes an Oracle driver and a local
database called services.

Example 2

jdbc:oracle:services

The third example describes a server named bugserver
in the bug domain, on port 9999 and a database called
'bugparade'.

Example 3 

jdbc:msql://bugserver.bug:9999/bugparade

The last example describes a naming service, nameService
and a name alias which will take care of resolving the
connection to the database without exposing details
about the machine hosting the database.

Example 4 

jdbc:nameService:alias
 

The DriverManager Class

The DriverManager class is simpler to use then the newer
DataSource interface and remains widely used 

The DriverManager class is the traditional utility class
allowing a user to manage a set of database drivers.
It's methods can be used to register and de-register
drivers and to make connections to databases.

DriverManager also supplies several methods to log
database activities.

DriverManager maintains a list of drivers which take
care of registering themselves with the class using
the static registerDriver( ) method. The driver vendor
includes a section of static code in the driver
implementation, (called a static initializer ) that takes
care of instantiating the class and registering it with
the DriverManager.

Example    

static {  // driver developer instaniate the driver
             // passes instance to DriverManager.registerDriver( )
           }

It is for this reason, you do not normally instantiate the
driver and register it yourself as this would be redundant.

// why you don't need to instantiate the Driver

The DriverManager can load the class template that is
used to instatiate the driver in one of two ways, either
using the Class.forName( ) method or by adding the
class name to the java.lang.System. property called
"jdbc.drivers".

Class.forName( )

A program can also explicitly load drivers as needed via
a call the forName( ) method of class 'Class'. This is the
recommended way of loading the driver class into the
Java environment. This call coordinates with the section
of static code we mentioned above that will create and
register the driver object with DriverManager's list of
drivers.

Example   

Class.forName("com.mysql.jdbc.Driver");
 

Using the java.lang.System Property "jdbc.driver".

The other way the driver class can be made available
is to specify the driver as a "jdbc.drivers" system property.
This takes the form of a colon separated list as is shown
in the following examples. This method depends on the
System property not changing.

If there is any doubt about the underlying system's
information, the Class.forName( ) approach is probably
a better route to take. The following examples from the
JDK docs show how drivers are specified via a Java
property assignment.

Example 1

 jdbc.drivers=foo.bah.Driver:wombat.sql.Driver:bad.taste.ourDriver

Example 2

jdbc.drivers=oracle.sql.Driver:weblogic.sql.Driver:CloudScape.sqlDriver

// driver list is colon separated
 

Getting Connected with DriverManager

Once a driver is loaded and registered it becomes available
for establishing a connection to the database. A main function
of the DriverManager class is to return a Connection object
via it's getConnection( ) method. When getConnection( ) is
called, the DriverManager will attempt to locate a suitable
driver from among it's list of those loaded at initialization
and those loaded explicitly.

DriverManager has three versions of the getConnection( )
method. One is based on a String holding the database
URL. A second version takes a String describing a URL
and a Properties object holding user name and password.
A third version that allows the user name and password
to be explicitly specified along with the database url.
 

The getConnection( ) Method Signatures 

    public static synchronized Connection
    getConnection ( String url  ) throws SQLException

    public static synchronized Connection
    getConnection ( String url, Properties info )
     throws SQLException

    public static synchronized Connection
    getConnection( String url,String user, String password )
     throws SQLException
 

Following is an example of the getConnection( ) method
being used to return a Connection object.

Example

Connection con =
DriverManager.getConnection( url , user_properties );
 

The DataSource Interface

The DataSource interface which was introduced in JDBC 2.0
and is the suggested replacement for the DriverManager class.
A DataSource object is a flexible container that can represent
a variety of data sources whether a complex association of
databases or a single file that may not even represent a
relational database model. 

The DataSource interface is  important for use in the Enterprise
edition of Java, J2EE. This is because the interface works easily
with other architectures used in J2EE such as JNDI, mentioned
below.

Though they have similar methods and work in a similar fashion,
DataSource is preferred over DriverManger for a couple of
reasons. A DataSource object has properties that identify and
describe the data source. A DataSource object works with the
'Java Naming and Directory Interface' which runs separately
from the application that uses it.


Benefits of Using DataSource

• uses JNDI

• has descriptive properties

• facilitates connection pooling & distributed transactions

// DataSource works with JNDI used in J2EE

JNDI allows a named reference to be associated with the
driver's hard code name. The JDBC application then can
pass this alias into the program allowing JNDI to match
the proper given name to the JDBC driver.

// allows decoupling of the driver from it's position in an architecture

JNDI makes applications more portable and easier to
maintain. Changes can be made to the data source and
the JDBC application does not have to be changed.
The DataSource object can also take advantage of
sophisticated features that are important to the enterprise,
like connection pooling and distributed transactions.

Java Native Directory Interface
added ref: http://java.sun.com/products/jndi/overview.html

JNDI can store and retrieve any type of Java object. The
API supplies methods for doing standard directory functions,
such as searching for objects using their attributes.

Somewhat like JDBC own architecture, JNDI enables
applications to access different, and possibly multiple,
naming and directory services using a common API.
JNDI allows taking advantage of a variety of naming
and directory services, such as LDAP, NDS, DNS, NIS
and other legacy systems.

JNDI provides a directory service that enables services
and resources to be accessed in a uniform way. The
resource might be a file service or a messaging service
or in the case of JDBC the resource may be a data
source. The 'JDBC API Tutorial and Reference' supplies
the following example to show how a DataSource object
may have it's properties set and then registered with the
JNDI naming service.

Example       // from 'JDBC API Tutorial & Reference'

VendorDataSource vds = new VendorDataSource( );
// a vendor's implementation of the DataSource interface
vds.setServerName("my_database_server");
vds.setDatabaseName("my_database");
vds.setDescription("the data source for inventory and personnel");

Context ctx = new InitialContext( );
ctx.bind("jdbc/AcmeDB",vds);

The VendorDataSource in the above example would be
a vendor supplied version of the DataSource interface.
Context and InitialContext( ) are defined in the JNDI API.
The bind( ) method associates the String literal with the
DataSource object. This stores the name and object in
the JNDI database for ongoing referencing.

Following is a summary of the DataSource Interface.

The DataSource Interface

public interface DataSource
    {
    Connection getConnection( );
    Connection getConnection(String username, String password);
    int getLoginTimeout( );
    PrintWriter getLogWriter( );
    void setLoginTimeout( int seconds );
    void setLogWriter(PrintWriter out);
    }

Wrapping a Classic JDBC Connection in a DataSource Object

The following code wraps conventional JDBC connection
code as a DataSource object through the implementation
of the DataSource interface.

Sample Code that Wraps a Traditional JDBC
Connection in a DataSource Object

package data;

import javax.sql.*;
import java.sql.*;
import java.io.*;

public class TownDataSource implements DataSource{

/* Properties */

private String description=

" Only the description property is implemented. This DataSource is a " +
"wrapper for a standard DriverManager JDBC connection to a MySQL database. " +
"It is implemented to facilitate connection pooling and future use of JNDI " +
"naming services. ";
 

private Connection connection;
String username;
String password;

/**  No args constructor supplying a ixed database access */

public TownDataSource( ){
     username = "exampleName";
     password = "examplePassword";

  try{
     Class.forName("com.mysql.jdbc.Driver");
     connection = 
     DriverManager.getConnection("jdbc:mysql://localhost/databaseName",username,password);
     }

   catch(ClassNotFoundException cnf){
      System.out.println("Class Not Found Exception: Driver not found");
      }

   catch(SQLException sqle){
      System.out.println("SQL Exception");
      }
  }  // constructor ends



public Connection getConnection()throws java.sql.SQLException{
      return this.connection;
      }

public Connection getConnection(String username, String password)throws java.sql.SQLException{
      return DriverManager.getConnection("jdbc:mysql://localhost/earth",username,password);
      }

public int getLoginTimeout()throws java.sql.SQLException{
      return DriverManager.getLoginTimeout();
      }
 
public PrintWriter getLogWriter( ) throws java.sql.SQLException{
      return DriverManager.getLogWriter( );
      }
 
public void setLoginTimeout(int seconds)throws java.sql.SQLException{
      DriverManager.setLoginTimeout(seconds);
      }

public void setLogWriter(PrintWriter out)throws java.sql.SQLException{
      DriverManager.setLogWriter(out);
      }


// JavaBean style support for description property read-only

public String getDescription(){
  return description;
  }
public static void main(String[]args){ new TownDataSource();}
} // class ends

DataSource Supports Connection Pooling

// DataSources with the javax.sql package can support these features

Connection pooling is an important technique used in
multi-threaded situations where a connection object is
reused rather than being created anew for every new
connection. Maintaining a pool of reusable connections
preserves computing resources and improves performance.

DataSource Supports  Distributed Transactions

Distributed transactions are complex transactions which
may be made up of several connections to several databases.
In order to keep track of whether all the separate parts of
the transaction have been successful individual transaction
managers can vote to commit the transaction. If one branch
does not agree to commit then all the branches of the
transaction will be rolled back.

The DataSource object in conjunction with other classes in
the javax.sql package are able to participate in the use of
these sophisticated features while the DriverManager cannot.
 

The Application Layer
// Connection, Statement & ResultSet which work over the Driver Layer

The Driver layer which we have covered in looking at the
details of the DriverManager class involves the 'plumbing'
that works underneath the 'Application Layer'. The Application
layer describes the use of the three interface implementations,
Connection, Statement and ResultSet used by users to
access databases.

The Connection object is obtained from the JDBC driver
implementation via the DriverManager or DataSource
getConnection( ) method. The Connection object is used
in turn to return a Statement object using the Connection
createStatement( ) method. The Statement object provides
the methods used to send SQL commands to a database.
If the SQL statement sent is a SELECT query, the result
is returned encapsulated in a ResultSet object. The ResultSet
instance in turn provides methods to access the returned
result set data.

Connection

The Connection Interface

The Connection interface embodies a 'session', really
a connection with a database. DriverManager's method
getConnection( ) returns the vendor's implementation of
this interface. The Connection interface provides about
two dozen methods. The ones used most frequently are
used to return different types of Statement objects.

The createStatement( ) method returns a generic Statement
object. Six variations of the prepareStatement( ) method
returns the more specialized PreparedStatement object.
The prepareCall( ) method comes in three variations and
returns another specialized statement object called the
CallableStatement. These last types of object allow for
the use of SQL-styled prepared statements and stored
procedures.

Methods used to Return Different Types of
Statement Objects

 Statement createStatement( )  throws SQLException
 // three variations

 PreparedStatement prepareStatement (String sql)  throws SQLException
 // six variations

 CallableStatement  prepareCall (String sql) throws SQLException
 // three variations

The Connection interfaces also defines the methods,
setAutoCommit( ), commit( )  and rollback( ) which are
used to control SQL transactions. Auto-commit is set
to true by default which voids the rollback mechanism.

Setting auto-commit to false using the setAutoCommit( )
method will force an explicit commit to be executed via
the commit( ) method. Following is the signatures of
these three methods.

Signatures of methods used to manipulate SQL transactions

  void setAutoCommit  (boolean autoCommit) throws SQLException

  void commit ( ) throws SQLException

  void rollback( ) throws SQLException
 

The getMetaData( ) method returns a DatabaseMetaData
object which carries information about the database being
queried. Returning the DatabaseMetaData object is also
an important means for testing which features are supported
by any given JDBC driver.

Signatures of the getMetaData( ) method

DatabaseMetaData getMetaData( ) throws SQLException

The following code gets Driver and Database information
first from methods of the DriverManager class and after
from a Connection object. Driver info is available via the
DriverManager even when not connected to a database.
The DatabaseMetaData though requires a connection.

// this was run on a machine with MySQL
// was installed the output is listed below.

Code Accessing Driver and DatabaseMetaData Objects

import java.sql.*;
import java.util.*;

class DriverInfo{
public static void main(String[]args){
Driver driver=null;
       try{
         Class.forName("com.mysql.jdbc.Driver");
          Enumeration enu = DriverManager.getDrivers( );
         while( enu.hasMoreElements() ){
             driver=(Driver)enu.nextElement();
          break;
             }
// breaking out of enumeration on first driver located which on this machine is the mysql driver

     System.out.println();    
    System.out.println("Driver Version: " +
                              driver.getMajorVersion() + ":" +
                              driver.getMinorVersion( ));

   System.out.println("JDBC Compliant: " + driver.jdbcCompliant());
   
// get DatabaseMetaData
   
    Connection connect = DriverManager.getConnection
                                     ("jdbc:mysql://localhost/earth","numero","987654321");
    DatabaseMetaData datadata = connect.getMetaData();
    System.out.println(datadata.getDriverName());  
    System.out.println("Database Product Name: " + datadata.getDatabaseProductName());
        System.out.println("Database Product Version: " + datadata.getDatabaseProductVersion());
    System.out.println(); 
        }

      // catching everthing with generic Exception class
  
      catch(Exception e){
          System.out.println(e);
          }
     } 
} 

OUTPUT

[peter@localhost Examples]$ java DriverInfo

Driver Version: 3:1
JDBC Compliant: false
MySQL-AB JDBC Driver
Database Product Name: MySQL
Database Product Version: 5.0.21-standard

Statement

The Statement Interface

A Statement object is used to send SQL queries to a
database and retrieve the results. The Statement object
can be used to send different types of SQL statements
including UPDATE, INSERT, DELETE or SELECT as
well as CREATE or DROP statements. Following the
more common of Statement's over two dozen methods
are discussed.

executeQuery( ) & executeUpdate

The executeQuery( ) method is used to execute SELECT
statements. Select statements return a result set. The
result set is returned encapsulated in a ResultSet object.
For queries that do not return a result set, the executeUpdate( )
method is frequently used.  This includes SQL statements
like CREATE, UPDATE and INSERT. (The Data Definition
Language ( DDL) commands describe this category of
command.)

Signatures of the executeQuery & executeUpdate Methods

ResultSet executeQuery( String sql ) throws SQLException

int executeUpdate( String sql ) throws SQLException

// executeUpdate returns an int that is a row count for INSERT,
// UPDATE or DELETE statements, or 0 for SQL statements that
// return nothing

execute( ) & getResultSet( )

A more general approach to executing SQL is to use
the execute( ) method that can execute any kind of SQL
statement. The execute( ) method may return several
ResultSet objects. The boolean value that is returned
by the method indicates if there are more result sets to
be processed.

When a result set is returned the method, getResultSet( )
can be used to return the ResultSet object. The
getUpdateCount( ) and getMoreResults( ) methods
are used to get more update counts and to test if
there are more result sets available to be processed.

Signatures of the execute and Related Methods

boolean execute(String sql)

ResultSet getResultSet ( ) throws SQLException

int getUpdateCount ( ) throws SQLException

boolean getMoreResults ( ) throws SQLException 

The PreparedStatement Interface

The 'Statement' inteface is the parent of 'PreparedStatement'
which in turn is extended by 'CallableStatement'. These
extensions are supplied to accomodate the use of SQL
prepared statements and stored procedures. The following
diagram shows how the interfaces are related.
 

The Statement Hierarchy
 

 Statement Interface
           |
      PreparedStatement Interface
                  |
             CallableStatement Interface  

PreparedStatement extends Statement and is useful
for statements that will be used repeatedly with minor
variations Different setType( ) methods are used to fill
parameter values marked by question marks in the
prepared statement. Parameters are indexed, 1 to n.

The PreparedStatement interface defines a number of
setType( ) methods, one for each Java primitive type.
These are used to set the corresponding SQL type in
the locations marked by question marks. The Java to
SQL mapping follows the scheme described in the
tables provided at the beginning of the note.

The following example from the JDK  documentation
shows how the question marks are used in conjunction
with the indices and values supplied to the set methods.
Notice the index follows the SQL style of starting at one
rather than the offset value, zero.

Example of a PreparedStatement Object Being Used

PreparedStatement pstmt = con.prepareStatement
("UPDATE EMPLOYEES  SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00);
pstmt.setInt(2, 110592);

// SQL index starts at 1 rather than the offset 0

This is not a complete discussion of using the prepared
statement in Java but it will get us to first base.

The CallableStatement Interface

CallableStatement is an extension of PreparedStatement.
The CallableStatement interface is used to execute SQL
stored procedures. Stored procedures are also referred to
as 'persistent stored modules'. They are program routines
stored in the database which are invoked by SQL. A good
trend from the Java developer's point of view, many Database
Management Systems now support the creation of stored
procedures written in Java.

// stored procedures a.k.a. persistent stored modules

MySQL 5.x supports stored procedures. The company has
their own comment on the merits of stored procedures.

MySQL's opinion on Stored Procedures

"Stored routines can provide improved performance because
less information needs to be sent between the server and
the client. The trade-off is that this does increase the load
on the database server because more of the work is done
on the server side and less is done on the client (application)
side. Consider this if many client machines (such as Web
servers) are serviced by only one or a few database servers."     
   
                                                        -  MySQL Manual

Stored Procedures

Calling Database Functions       // for reference

Most databases have a set of functions that can be
called to construct expressions. For instance to call a
function, the escape sequence would be used with the
'fn' keyword as is shown in the following example.

Example

ResultSet rs = statement.executeQuery("{ \fn LOG( " + a_float + " ) }");
 

Passing Parameters to Stored Procedures

In the above example we saw the use of curly braces as
a special form of escape used for stored procedures.
 

An executeBatch( ) method Example

Curly braces are also used to call custom designed stored
procedures using JDBC. (See table above.) Notice in the
following code excerpt from the 'JDBC API Tutorial &
Reference', the curly brace system is used with the call
command to call the stored procedure updatePrices. The
addBatch( ) and executeBatch( ) method is inherited from
the Statement interface.

Code Sample Example

CallableStatement cstmt = con.prepareCall
                                    ( "{ call updatePrices( ?, ? )}");

scstmt.setString( 1,"Columbian");
cstmt.setFloat( 2, 8.49f );
cstmt.addBatch( );

cstmt.setString(1,"Columbian");
cstmt.setFloat(2,9.49f);
cstmt.addBatch( );

int [ ] updateCounts = cstmt.executeBatch( );

Creating a Stored Procedure

Storing a procedure which doesn't provide for specifying
arguments handling return types is easy to follow as is
shown in the following example taken from the JDBC
tutorial authored by Maydene Fisher and available for
view at the Sun site. 

http://java.sun.com/docs/books/tutorial/jdbc/index.html

Code Sample from the JDBC Tutorial by Maydene Fisher

// note syntax between databases may vary

" The following SQL statement creates a stored procedure.
( This is Maydene Fisher's example except with round function
braces added as appropriate. They ended up being needed
in the databases we were using. )

          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

The following code puts the SQL statement into a string and
assigns it to the variable createProcedure , which we will
use later:

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

The following code fragment uses the Connection object
'con' to create a Statement object, which is used to send
the SQL statement creating the stored procedure to the
database:

Statement stmt = con.createStatement();
stmt.executeUpdate(createProcedure);

The procedure SHOW_SUPPLIERS will be compiled and
stored in the database as a database object that can be
called, similar to the way you would call a method.  "

The actual code to call the query is as follows.

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

*
// depending on the database this form may vary  ("{ call SHOW_SUPPLIERS }"); 
// was the form in the original reference, and round braces have been added  _ed
 

IN OUT & INOUT Variables         //  for reference

SQL uses IN OUT and INOUT variable types in conjunction
with stored procedures.. The IN variables are like Java
arguments and can be set use setType( ) methods.
OUT parameters carry the values that return from stored
procedures. They are are like the return values of Java
methods. They are retrieved from inside a Java program
using getType( ) methods.

// INOUT values are like Java objects that are passed into and
// returned from a method carrying values

INOUT values carry in and out of a stored procedure. You
might conceptualize the INOUT type as Java class objects
whose variables can carry values into and out of a method.
To process INOUT variables set and getType( ) methods
are used along with the method registerOutParameter( ).
The get/setType( ) methods access reference requested
data in each column by row position (1 to n) or by name.
 

ResultSet

The ResultSet Interface  // classic version 

The values that are returned from the database via a
JDBC execution of an SQL statement come encapsulated
in a ResultSet object. The classic ResultSet object can
be thought of as a table of rows and columns with a cursor.

The cursor is initially set above the first row. When next( )
is called on the ResultSet object the first row is referenced.
The next( ) method returns a boolean which indicates if
there are any more rows in the table. Frequently the result
set will be inspected row by row in a while loop based on
the next( ) methods return value.

while( result.next( ) ){
      for ( int i = 1; i < numberColumns; i ++ ){
            System.out.print(rs.getString( i ) + "   " );
            }
     }

// note only the getString( ) method is shown here. There are a large
// number of get and set methods for getting different Java types

JDBC 2.0 ResultSet

The ResultSet interface defines methods for accessing
data tables generated by a Statement execution. With
the release of JDBC 2.0 the method set of the ResultSet
interface has been enhanced to allow flexible access of
tables that are returned from queries. The tables  are fully
indexed and accessible by row number or column name.
The interface originally had over 40 accessing methods.
It now has 140 methods!

// ResultSet has gone from just over 40 to 140 methods

The getMetaData( ) method returns a ResultSetMetaData
object that can be queried for information about the result
set. The following three methods may throw SQLExceptions
as vendors have the option of caching the results on the
remote side creating the possibility of a error in communication.
 

The next() , getMetaData( ) & close( )  Method Signatures

• boolean next ( ) thows SQLException

• ResultSetMetaData getMetaData( ) throws SQLException

• void close( )

The ResultSetMetaData Interface

The ResultSetMetaData object provides information
'about' the result set tables that have been returned. Of
it's twenty plus methods the getColumnCount( ) method is
one of the most commonly used to get the number of
columns in the result set. The following example is again
taken from the JDK documentation.

Example // from the JDK documentation

     ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
     ResultSetMetaData rsmd = rs.getMetaData( );
     int numberOfColumns = rsmd.getColumnCount( );
     boolean b = rsmd.isSearchable(1);

Self Test                               Self Test With Answers

1) Regarding mapping of types which of the following statements is not correct?

a) The many SQL types will often map to the same Java type.
b) Both Java primitives and object types can be mapped to SQL types.
c) Java wrapper classes are used extensively in mapping object type to SQL types.
d) Because SQL has more data types then Java Object class types are used
    to make up the difference.
 

2) In a Java url the subname may contain all but one of the following. Which does
not belong.

a) domain name or IP address
b) database file name
c) driver maker
d) a user_name & password.
 

3) Which of the following is not correct regarding the connect( ) method?

a) The connect( ) method is usually called indirectly in a JDBC program.
b) The connect( ) method throws an SQLException if the url is valid but the
     connection fails.
c) connect( ) is one of several methods in DriverManager used to connect to
    databases.
d) Network indirection, can be used to hide away the connection information.
 

4) DataSource is preferred over DriverManager

    a) as it lets an application use JNDI to locate databases
    b) offers support for complex transactions
    c) replaces getConnection( ) with the easier to use getDataSource( ) method
    d) permits use of pooled connections

5) Which of the following statements is not correct?

 a) The driver class can be supplied to the application using the java.lang.System
    property "jdbc.driver".
    b) The static getClass( ) method of class Class can also be used to supply the
    Driver implementation.
    c) The driver implementation uses a static initializer to instantiate and register the
    driver with the DriverManager class.
    d) DriverManager will attempt to locate a suitable driver from among it's list of those
    loaded at initialization and those loaded explicitly.
 

6) Which of the following statements is not correct?

a) Connection methods are used to return different types of Statement objects
b) The executeUpdate( ) is used for SQL updates while all other SQL commands
     should use executeQuery( ).
c) Statement descendents are used to execute stored procedures.
d) Statement descendents are used to execute prepared statements.

Exercise 1

1) This exercise is designed to create an awareness of
what steps would need to be taken to create and call a
stored procedure from a Java program. It also introduces
you to MySQL and familiarizes you with connecting via
JDBC to another database.

Create a command line program that creates a stored
procedure in a database using the syntax described in
the note. Access the stored procedure from a Java program
and print the result to screen.

Use MySQL as your database or a database of your choice.

//  MySQL now supports stored procedures. It should be possible
// to store a procedure on MySQL and call that procedure from a
// JDBC program. 

   
Loading MySQL from File            Margaret Walsh

Entry of the data into the text file is quite easy but you have 
to be sure to use <tab> to separate the fields.  If there is a 
space in a line instead of a <tab>, SQL responds be entering 
NULL.  Same goes for an empty line except ALL fields will 
show in the table as NULL. You will see the results in the first 
table near the bottom of SQL.txt.  

SQL.txt contains step by step SQL commands used to build 
the database as well as building and filling the table within 
the database using a text file.  I have include what happens 
if there are spaces or empty lines in the INFILE. I have also 
included the command for mass removal of data and the 
reloading of the table with the edited FramesInv.txt file.


000007  'E102'  'LANCTOT'       'BLACK' 'RIMLON'        18
000008  'E1620' 'LANCTOT'       'GUNMETAL'      'RIMLON'        15
000009  'E4930' 'LANCTOT'       'MULTI' 'GRAFIC'        6
000010  'G101'  'BO OPTIK'      'BROWN' 'SAFARI'        19      
000011  'G104'  'BO OPTIK'      'BLACK' 'TIMEX' 4
000012  'G156'  'BO OPTIK'      'GREEN' 'AUSSIE'        9
000013  'G2507' 'BO OPTIK'      'BROWN' 'JUNGLE EYEWEAR'        15
000014  'H005'  'CENTENNIAL'    'DARK BLUE'     'KIKI'  '10'
    

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| framesinv          |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)


mysql> use FRAMESINV
Database changed

mysql> CREATE TABLE FramesInv
    -> (
    -> Invnum INTEGER, FrCode CHAR(12), FrMan VARCHAR(40),
    -> FrColor VARCHAR(15), FrStyle VARCHAR(20), FrInvAmt INTEGER
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> SHOW TABLES;
+---------------------+
| Tables_in_framesinv |
+---------------------+
| framesinv           |
+---------------------+
1 row in set (0.00 sec)

mysql> LOAD DATA LOCAL INFILE 'C:/FRAMESINV.TXT' INTO TABLE FramesInv;
Query OK, 9 rows affected, 10 warnings (0.02 sec)
Records: 9  Deleted: 0  Skipped: 0  Warnings: 10

mysql> SHOW TABLES;
+---------------------+
| Tables_in_framesinv |
+---------------------+
| framesinv           |
+---------------------+
1 row in set (0.01 sec)

// Caution: an empty line at the top of 
// the file will fill the fields with NULL 

mysql> SELECT * FROM FRAMESINV;

+--------+---------+--------------+-------------+------------------+----------+
| Invnum | FrCode  | FrMan        | FrColor     | FrStyle          | FrInvAmt |
+--------+---------+--------------+-------------+------------------+----------+
|      7 | 'E102'  | 'LANCTOT'    | 'BLACK'     | 'RIMLON'         |       18 |

|      8 | 'E1620' | 'LANCTOT'    | 'GUNMETAL'  | 'RIMLON'         |       15 |

|      9 | 'E4930' | 'LANCTOT'    | 'MULTI'     | 'GRAFIC'         |        6 |

|     10 | 'G101'  | 'BO OPTIK'   | 'BROWN'     | 'SAFARI'         |       19 |

|     11 | 'G104'  | 'BO OPTIK'   | 'BLACK'     | 'TIMEX'          |        4 |

|     12 | 'G156'  | 'BO OPTIK'   | 'GREEN'     | 'AUSSIE'         |        9 |

|     13 | 'G2507' | 'BO OPTIK'   | 'BROWN'     | 'JUNGLE EYEWEAR' |       15 |

|     14 | 'H005'  | 'CENTENNIAL' | 'DARK BLUE' | 'KIKI'           |        0 |

+--------+---------+--------------+-------------+------------------+----------+
8 rows in set (0.00 sec)
    

Code Possibilities To Assist in Solution

Following are some statements that might assist you
in your solution.

// Unlike the examples provided from the JDBC Tutorial,
// MySQL likes round braces in the create and call statements

/*1*/      String createProcedure = "create procedure ALL_CARS( ) " +
                                                       "select * FROM Cars" ;

/*2*/     Statement stmt = con.createStatement( );
              stmt.executeUpdate(createProcedure);
 

/*3*/    CallableStatement cs = con.prepareCall("{ call ALL_CARS( )}");
             ResultSet rs = cs.executeQuery( );

Practical To Enable MySQL Access from Java

Getting a JDBC driver for mySQL

In addition to the downloading MySQL and installing it the
corresponding driver needs to be downloaded as well.

Go to www.mysql.com and find the drivers and take the latest
stable release. In June of 2010  the Connector/J driver is in
version 5.1.12

In Windows, once you have the distribution jar you can unjar
it with the jar command or just the jar into WinZip. Select all
and extract to a directory. There will be a jar file. In the
driver I downloaded the driver jar was called

mysql-connector-java-5.1.12-bin.jar

Put this jar into the JDK home directory as follows. 

Example      

jdk1.6.0_04/jre/lib/ext  

The 'ext' extension directory is automatically in the JDK
classpath.

The classname to use for this driver is "com.mysql.jdbc.Driver".
(This would be the name you'd pass to Class.forName( ) ).

Example  Class.forName("com.mysql.jdbc.Driver");

To connect to the database, you need to use a JDBC URL
with the following format ([xxx] denotes optional url components):

JDBC URL Format

jdbc:mysql://[hostname][:port]/[dbname][?param1=value1][&param2=value2].....

The mysql database system's exe file the bin of the MySQL
directory but the data is stored in files representing databases
in the data directory. Using the above information this works
for a local installation. Leaving out the host and port defaults
to the loopback address of the local machine. I had created
the database DB earlier.

String url =  "jdbc:mysql:///mysql/data/DB/";

Just to hook up to the MySQL Server, you can use the following.

jdbc:mysql://localhost:3306/mysql

// assumes default port, 3306

optional

2) If you have time, create a GUI that has an area to create SQL
and buttons for submitting queries either that returning a result
set or query that does not return an result set.

Setting Up a New User

// this was applicable in Linux. Windows MySQL now has
// GUI for doing this stuff

Use the GRANT statement from MySQL's root user to create
user that is granted all privileges. This user would be useful
in creating a database and accessing it via user and password.

The following section is quoted from the MySQL 5.0 Manual.

"
First, use the mysql program to connect to the server as the
MySQL root user:

shell> mysql --user=root mysql

If you have assigned a password to the root account, you'll also
need to supply a --password or -p option for this mysql command
and also for those later in this section.

After connecting to the server as root, you can add new accounts.
The following statements use GRANT to set up four new accounts:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

"

Optional Exercise 2

If you are new to SQL, the following supplies additional information
and learning exercises.

Following is a quiz on the first half of the excellent JDBC tutorial by
Maydene Fisher located at the sun site at the following link. This
tutorial forms a chapter in the JDBC tutorial.

http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html

1. What line of code would you need to load the driver which translates Java
Database Connectivity commands into Microsoft's Open Database Connectivity
specification?

2.Write a line of code returning a Connection object named,bugCon,given the
url, jdbc:cyberbase:bugs, a password, sos, and a login namebugreporter543.

3.Given a connection object called, dCon, what line of code would return a
Statement object named, dState?

4.What single or pair of statements would create a 4 columns table, the first column
called RECORD ( sql type INT ) , the second column called DESCRIPTION
( sql type VARCHAR, maximum 16 characters ), the third column labelled
QUANTITY ( sql type INT ) and the forth column called PRICE (sql type
FLOAT),  given a statement object dState?

5.In the table created in Q.4, write jdbc code to insert 4 fictitious records. One
fictitious record example might have the values that appear in a table as follows:

100 ball  28  4.99.

6.What SQL statement would return the prices of the four record items?

7.Return the the result of SQL statement in Q.6  as a ResultSet object using
dState as the statement object and dResult as the name of the ResultSet object.

8.  getXXX( ) methods can access columns by _________ or_________
(Put the more efficient first).

9.  T/F getInt( ) can be used to retrieve sql INTEGER and BINARY types.

10. In the getXXX( ) methods table at the end of the JDBC Basics tutorial,
      a) what do the small x's refer to?   b) the large X's?

11.Write a String called newSale containing the SQL statements needed to update
the tutorial's COFFEES table in order to set SALES for Expresso to 150. Given
a Statement object called sT, write JDBC code to update COFFEES with the
new information in newSale.