java.sql package - IBM DB2

Java.sql package defines the classes and interfaces required for the JDBC program to access the relation data stored in a database. These APIs can be used to connect to the relational database and manipulate the data (insert, update, delete, and so on) stored in tabular form using the SQL standard. The interfaces defined in this package are implemented by the driver specific classes and the definition can differ from vendor to vendor.

Getting a connection
A connection to a database can be obtained using the DriverManager class of the java.sql package.Before getting connection, the driver specific classes must be loaded and registered to the DriverManager. Any number of drivers can be loaded and registered with the Driver Manager. You can use the code shown below to load a driver specific class.

Loading the Type 4 driver classes

The forName method take a string argument whose value is the name of the package which implements the interfaces defined in java.sql package. The connection to a database can be obtained by calling the getConnection method of DriverManager class. This method takes a string value (URL) as an input, which gives the information required to connect to the database. A typical URL format for Type 4 driver is:

The code below returns the connection as Connection class object. Loading the Type 4 driver and getting connection to the database

In the case where multiple drivers are loaded, DriverManager is responsible for making use of the appropriate driver to make a connection.

Manipulating data
After getting the connection, data can be selected, inserted, updated, or deleted from the relational tables using SQL statements. JDBC driver implements two interfaces Statement and PreparedStatement for this purpose. An object of any of these classes is required for running an SQL statement.

An object of Statement (or class implementing the Statement interface) can be used to execute the SQL statement which does not contain parameter markers. An object can be created from the Connection object using createStatement method.
Any number of statements can be created for a particular connection object. Statement interface defines executeQuery and executeUpdate methods to execute a query statement. The executeQuery method is used when the result set is expected (for example, for the SELECT statement) as output of the query.

Alternatively, executeUpdate method is used for updating the database contents (for example, INSERT, UPDATE, and DELETE statements). The executeQuery method returns the ResultSet object, which represents a set of rows returned by the SELECT query. This ResultSet object can be used to fetch the result row by row. executeUpdate returns an integer value, which indicates the number of rows updated, inserted, or deleted from the database based on the type of SQL statement.

SELECT using Statement object
below example contains a code snippet for the method getProducts from the application code. The method select the product names from the CUSTOMER table and stores them in the String array.

SELECT using Statement object

The Statement object also provides execute methods to execute any type of query. The execute method is useful when the decision on SQL statement type is taken at runtime. It returns true if the result of the SQL statement is a ResultSet object (for example, for SELECT statement) and false if the result is update count or there is no result. Based on the return value, getResultSet and getUpdateCount methods can be used to get the resultSet object or the update count.

SELECT using Statement object’s execute method

UPDATE using Statement object
Below it give the code snippet to update a value in the table using the Statement object.

UPDATE using Statement object

An INSERT or DELETE statement can be executed using the Statement object in the same fashion.

An object of PreparedStatement (or a class implementing the PreparedStatement interface) can be used to run the queries, which can contain parameter markers. A PreparedStatement object can be created using the prepareStatement method of Connection object. PreparedStatement extends the Statement interface.

If the SQL statement contains parameter markers, the values for these parameter markers need to be set before executing the statement. Value can be set using setXXX methods of PreparedStatement object where XXX denoted the data type of the parameter marker. setXXX methods are also called setter methods.

The following are the examples of setXXX methods:

  • setInt
  • setString
  • setDouble
  • setBytes
  • setClob
  • setBlob

After setting the parameter values, the SQL statement can be executed using any of the executeQuery, executeUpdate, or execute method based on the SQL type.

SELECT using PreparedStatement object
below it gives the code snippet for the method isCustomer from the application code. The method passes the value of the customer id to the method, which is used to pass the value to the SELECT query. The method returns null if the customer id does not exist.

SELECT using PreparedStatement object

INSERT using PreparedStatement
This example shows how to insert the value in the table using the PreparedStatement object.

INSERT using the PreparedStatement object

Tip: Trying to run a query which updates the content of the database with the executeQuery method will result in an exception. Similarly, trying to run a select query using the executeUpdate method will give an exception. Use the execute method whenever you are unsure about the query at compile time (that is, the query is generated at runtime using user’s input) and check for the Boolean value returned by the method to decide the result.

An object of the Callable Statement interface (or the class implementing the Callable Statement interface) can be used to call the stored procedure. The Callable Statement interface extends the Prepared Statement interface. An object of Callable Statement can be created using the prepare Call method of Connection object.

The parameter for a CallableStatement can be of three types:

  • IN
  • OUT

The value for IN and INOUT parameters must be set before executing the Callable Statement. In the same way, OUT and INOUT parameters should be registered to the database before executing the statement. The Callable Statement can be executed using execute, executeQuery, and executeUpdate methods. The usage of these three methods is:

  • execute: Use this method when multiple result sets are expected as an output.
  • executeQuery: Use this method when a single result set is expected.
  • executeUpdate: Use this method when no result set is expected.

Below example gives the code snippet from the createPorder method of the application code. The method calls a stored procedure named createOrder. The stored procedure code is given in the class CreateOrder. The stored procedure has two IN parameters and one OUT parameter. The stored procedure should be registered before using it.

Calling a stored procedure using CallableStatement object

The executeQuery method of Statement, PreparedStatement, and CallableStatement returns the ResultSet object as an output of the query. The ResultSet object maintains a cursor to the current row of the result set of a query.

This cursor can be advanced to the next row by using the next method of this object. The cursor by default can only be moved forward and is read-only. The columns value for the current row can be fetched by calling the getXXX method of the ResultSet object where XXX denotes the data type of the column. The getXXX takes the column number or name for the current row as an input.

Once all the rows are fetched, the next method returns an exception. By default, the cursor cannot be moved backward and updated. Cursors can be moved backward or moved directly to a specific row by defining the scrollability of the ResultSet object. Similarly, the cursor can be updated by defining the updateability of the ResultSet object.

The following three properties for the ResultSet can be set while creating the Statement object:

  • resultSetType
  • resultSetConcurrency
  • resultSetHoldability

While resultSetType defines the scrollability of the cursor, resultSetConcurrency defines the updatabilty of the cursor. resultSetHoldability indicates that the result set will remain open even after the statement is closed. The createStatement and prepareStatement supporting all of these properties have the following syntax:

All three properties are optional. Example 5-10 defines a Statement object with a scrollable and updatable ResultSet. It fetched the ResultSet row by row and update the status whenever the record with value 5003 is found. It prints all the records after the update is done.

Scrollable and updatable Result set

The supported methods for the scrollable cursor are:

  • first()
  • last()
  • next()
  • previous()
  • absolute()
  • relative()
  • afterLast()
  • beforeLast()

Tip: Each ResultSet object is associated with a Statement, PreparedStatement, or Callable Statement object. The sensitivity or the updatability cannot be defined at the resultSet level. It should be defined while creating the statement object itself, which means ResultSet objects created for SQL statements using the same statement object will have the same properties. So once the ResultSet object is created, it is not possible to change the properties.

With batch updates, a group of updates can be done to the database at the same time instead of updating one by one. The Statement object provides the following methods for batch updates:

  • addBatch method can be used to add the SQL statements in the batch.
  • executeBatch method will execute all the batch statements at the same time.
  • executeBatch method will return the number of rows affected by the batch update.

Example shows how to do the batch updates using the Statement object.

Batch update with Statement object

PreparedStatement and CallableStatement can also be used to make the batch updates. This can be done by running the same PreparedStatement or CallableStatement with different parameter values.

Below Example gives the code snippet from the application code from the method createCart. The method takes an HashMap object which has the key-value pair of product name and the quantity as an input and inserts the PID and QUANTITY values using a batch update into a table. The table is also created in the same method. The name of the table is generated by appending the CID value to the word cart.

Batch update with PreparedStatement object

Tip: To execute a batch of statements, all the statements in a single batch should be batch compatible. Batch compatible statements fall in two categories:

  1. Statements not having parameter markers or host expressions.
  2. Different instances (with different parameter values) of the same
  3. PreparedStatement if the statement has the parameter marker.

All the statement in category 1 are batch compatible. In the same way, all the statements in category 2 are batch compatible, but none of the statements in category 1 are batch compatible with the statements in category 2.

The java.sql package provides three interfaces to access the metadata at the database level, parameter level, and result set level. The driver class implements these interfaces.

DatabaseMetaData object gives the information of the database as a whole. The object can be created by calling the method get Meta Data of Connection object. The Database MetaData object provides a lot of methods to get the database information. Some of them are explained below:

  • Driver information
  • getDriverName and getDriverVersion methods give the details of the driver in use.
  • Index and primary key information
  • getIndexInfo gives the details of the indexes in the database. Similarly, the getPrimaryKeys method gives the primary key’s details.
  • Information of database object name’s length
  • getMaxCursorNameLength, getMaxProcedureNameLength,
    getMaxSchemaNameLength, getMaxStatementLength,
    getMaxTableNameLength and so on give the maximum length allowed for the different database objects.
  • Information regarding the database objects
  • getProcedures, getSchemas, getTables, getUserName, getTableTypes, and so on methods give the information regarding the database object.
  • Information regarding database object size
  • Methods are provided to get the information regarding the size of the object such as maximum number of connections allowed, maximum number of columns in a table, and so on. Examples of these kinds of methods are:
    – getMaxConnection
    – getMaxColumnInTable
    – getMaxColumnInOrderBy
    – getMaxColumnInIndex
    – getMaxColumnInSelect and so on
  • Support functions
  • There are a lot of support functions defined that can be used to check if any of the functionality is supported in this driver or not. Some examples are:
    – supportBatchUpdates
    – supportConvert
    – supportColumnAliasing
    – supportGroupBy
    – supportGroupByUnrelated
    – supportExpressionInOrderBy
    – supportFullOuterJoins and so on

This interface is included in JDBC universal driver and is not supported for Type 2 drivers. This interface contains the methods to retrieve the information regarding the parameter’s markers in a PreparedStatement object.The ParameterMetaData object can be created by calling the getParameterMetaData method of PrepareStatement object. This interface has the following methods:

  • getParameterCount
  • This method returns the number of parameters in PrepareStatement.
  • getParameterType
  • This method returns the SQL data type of the parameter marker. It takes an integer value, which indicates the position of the parameter marker in the Prepared Statement.
  • getParameterMode
  • This method gives the information regarding the type of the parameter: IN, INOUT, or OUT.
  • isNullable
  • This method returns true if the parameter is nullable; otherwise, it returns false.
  • getPrecision and getScale
  • These methods return the precision and the scale of the parameter marker if the parameter type is decimal.

This interface provides the methods to get the ResultSet information. This information is specially needed to retrieve the data when the application does not know about the column types in the ResultSet. The object of ResultSetMetaData can be created by calling the getResultSetMetaData method on the ResultSet object. This interface has the following important methods:

  • getColumnCount
  • This method returns the number of columns in the ResultSet object.
  • getColumnName
  • This method returns the name of the column in the ResultSet object.
  • getColumnType
  • This method returns the data type of the column.
  • getTableName
  • This method returns the name of the underlying table for the ResultSet.
  • isNullable
  • This method returns true if the ResultSet column can have a null value.
  • isReadOnly
  • This method will return true if the column is read-only

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

IBM DB2 Topics