Four Main JDBC Classes

 

Introduction.  When making use of the JDBC - Java Database Connectivity a developer generally makes use of four main classes.
  • java.sql.DriverManager
    • loads the JDBC driver and manages the connection to the database
  • java.sql.Connection
    • connects to the database
  • java.sql.Statement
    • manages an SQL statement for a connection
  • java.sql.ResultSet
    • allows access to the results of an executed statement

A typical sequence of steps involved in using the JDBC are

  1. a driver is loaded
  2. a connection is made from the driver
  3. a statement is made based on the connection
  4. an SQL statement is executed using the statement
  5. a resultset is returned based on the execution of the SQL statement

A sincere effort to illustrate this sort of process is contained in the following rather disjointed code segments.

While this is a rather abstract presentation, we will see these classes implemented in many different ways during the remainder of the course.

Loading the JDBC driver for ODBC - Open Database Connectivity databases.

 

//  load the JDBC driver

Class.forname("jdbc.odbc.JdbcOdbcDriver");

 

Now the developer needs to describe the path for the connection, or URL - Uniform Resource Locator to the database.  On Windows machines this can be done with a DSN - Data Source Name connection. 

Then the developer needs to establish the connection.

 

//  the URL string

String  url = "jdbc:odbc:DSNname";

 

//  connect to the URL and return a connection object

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

 

The two blank sets of quotes refer to particular Java utilities information.  Generally these will be filled in with a username and password associated with the database on a server.

The following table presents several of the most important java.sql.DriverManager class methods.  We will use the first method but not many others.

 

java.sql.DriverManager
Method Description
getConnection(String url, java.util.Properties info) Attempts to establish a connection to the given url.
getDriver(String url) Attempts to get a driver that understands the given url.
getDrivers( ) Returns an enumeration of all the currently loaded drivers.
setLoginTimeout( int seconds) Sets the maximum time that all drivers must wait when attempting to login to a database.

 

Now we need to develop the SQL statement based on the connection.  We will also execute the SQL statement in this section of code.

 

//  create the string for the SQL statement

String  queryString = "select * from tableName";

 

//  create the java statement for the SQL

Statement stat = con.createStatement( );

 

//  execute the SQL command based on the connection

ResultSet rs = stat.executeQuery( queryString );

 

Now we will give a brief survey of some of the more important methods in the java.sql.Connection class.

 

java.sql.Connection
Method Description
createStatement( ) Creates and returns a new Statement object.
preparedStatement(String sql) Pre-compiles an SQL statement and stores it for later reference in the preparedStatement object.
prepareCall(String sql) Creates an object for calling a stored procedure.
NativeSQL(String sql) Converts JDBC SQL into SQL for the appropriate database system.
setAutoCommit( boolean autoCommit) Allows the default autoCommit mode to be changed.
GetAutoCommit( ) Retrieves the current state of the  autoCommit mode.
commit( ) Commits all changes made since the previous commit/rollback and releases any database locks held by the connection.
rollback( ) Rolls back any changes made since the previous commit/rollback and releases any database locks held by the connection.
close( ) Causes an immediate release of a connection's database and JDBC resources.
isclosed( ) Returns the status of a connection

 

 

Now we will give a brief survey of some of the more important methods in the java.sql.Statement class.

 

java.sql.Statement
Method Description
executeStatement(String sql) Executes an SQL statement and returns a resultSet.
executeUpdate(String sql) Executes an SQL insert, update or delete statement and returns the row count or 0 if the statements return nothing.
close( ) Releases a statements JDBC resources and database.
setCursorName(String name) Defines the SQL cursor name that will be used by subsequent Statement execute methods.
execute(String sql) Executes an SQL statement that may return multiple results.
getResultSet( ) Returns the current result as a ResultSet.
getUpdateCount( ) Returns the current result of the update.
getMoreResults( ) Moves to the  next SQL statement's result.

 

Now we need to move through the ResultSet.  Unfortunately, we can only move forward at this point in time until I find out more about the most recent versions of the JDBC.

We define a boolean variable to act as the condition for continuing to move through the ResultSet.

We make use of a very simple print statement to display the contents of a particular column given its name is Column_.

 

//  define the boolean variable based on the ability to
//  move to a next record in the ResultSet

//  loop through the ResultSet

while ( rs.next( ) )

{

System.out.println("Column_ : " + rs.getInt( "Column_") );

}

 

Now we present some of the more important methods invoked within the java.sql.ResultSet class.  It should be obvious that the getWhatever( ) methods exist for every data type.

 

 

java.sql.ResultSet
Method Description
next( ) Moves to the next row in the ResultSet.
close( ) Closes the ResultSet.
getString( int columnIndex) Returns the value of the columnIndex as a String.
getBoolean( int columnIndex) Returns the value of the columnIndex as a Boolean.
getByte( int columnIndex) Returns the value of the columnIndex as a Byte.
getShort( int columnIndex) Returns the value of the columnIndex as a Short integer.
getInt( int columnIndex) Returns the value of the columnIndex as a Short integer.
getLong( int columnIndex) Returns the value of the columnIndex as a Long integer.
getFloat( int columnIndex) Returns the value of the columnIndex as a Float.
getDouble( int columnIndex) Returns the value of the columnIndex as a Double.
getDate( int columnIndex) Returns the value of the columnIndex as a Date.
getTime( int columnIndex) Returns the value of the columnIndex as a Time.