Result Set

The rows that satisfy the conditions of a query are called the result set. A user can access the data in a result set one row at a time, and a cursor (pointer) provides the means to do that.


There are two ways to acquire a connection

  1. sing DriverManager
  2. Using a DataSource (preferred)

How to use a data source:

Sysadmin first creates the ds and registers it with a JNDI service.

com.dbaccess.BasicDataSource ds = new com.dbaccess.BasicDataSource();
Context ctx = new InitialContext();
ctx.bind("jdbc/billingDB", ds);
Then a programmer can use it:
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/billingDB");
Connection con = ds.getConnection(username,pass);

By using a datasource programmer no longer have to hard code database specific information and they can change any time by sysadmin.

Connection Pool

A connection pool is a cache of database connection objects. We can deploy a pooled data source into a JNDI (depending if our driver supports it), then ds.getConnection() gets a connection from the pool.

To work with a connection pool first we need to deploy a ConnectionPoolDataSource object and then deploy a DataSource object implemented to work with it.

Distributed Datasource

We can also deploy a distributed or transactional datasource into a JNDI (i.e involving more than one database)

In this environment a transaction manager controls when a distributed transaction begins and when it is committed or rolled back; therefore, application code should never call the methods Connection.commit or Connection.rollback, etc.


Tx can be used to group statements together for execution as a unit and also to preserve the integrity of the data in a table. 

  • first step to allow two or more statements to be grouped into a transaction is to disable the auto-commit mode.
  • last step it to call commit

To avoid conflicts during a transaction, a DBMS uses locks, mechanisms for blocking access by others to the data that is being accessed by the transaction. (Note that in auto-commit mode, where each statement is a transaction, locks are held for only one statement.) After a lock is set, it remains in force until the transaction is committed or rolled back. For example, a DBMS could lock a row of a table until updates to it have been committed. The effect of this lock would be to prevent a user from getting a dirty read.

A non-repeatable read occurs when transaction A retrieves a row, transaction B subsequently updates the row, and transaction A later retrieves the same row again. Transaction A retrieves the same row twice but sees different data.

A phantom read occurs when transaction A retrieves a set of rows satisfying a given condition, transaction B subsequently inserts or updates a row such that the row now meets the condition in transaction A, and transaction A later repeats the conditional retrieval. Transaction A now sees an additional row. This row is referred to as a phantom.

A dirty read is reading a value before it is made permanent. Accessing an updated value that has not been committed is considered a dirty read because it is possible for that value to be rolled back to its previous value.

Isolation Level Transactions Dirty Reads Non-Repeatable Reads Phantom Reads
TRANSACTION_NONE Not supported Not applicable Not applicable Not applicable
TRANSACTION_READ_COMMITTED Supported Prevented Allowed Allowed
TRANSACTION_READ_UNCOMMITTED Supported Allowed Allowed Allowed
TRANSACTION_REPEATABLE_READ Supported Prevented Prevented Allowed
TRANSACTION_SERIALIZABLE Supported Prevented Prevented Prevented


Has a reference to a chain of exceptions. If more than one error occurs, the exceptions are referenced through this chain. Retrieve these exceptions by calling the method SQLException.getNextException on the exception that was thrown.


A JDBC RowSet object holds tabular data in a way that makes it more flexible and easier to use than a result set.

A RowSet acts as a JavaBean and can have properties.

For all RowSet objects we can do:

  1. A cursor movement
  2. The update, insertion, or deletion of a row
  3. A change to the entire RowSet contents

These interfaces extend RowSet:

JdbcRowSet: A JdbcRowSet object is an enhanced ResultSet object. It maintains a connection to its data source, just as a ResultSet object does. The big difference is that it has a set of properties and a listener notification mechanism that make it a JavaBeans component.

CachedRowSet: A CachedRowSet object is special in that it can operate without being connected to its data source, that is, it is a disconnected RowSet object.

WebRowSet: A WebRowSet object is very special because in addition to offering all of the capabilities of a CachedRowSet object, it can write itself as an XML document and can also read that XML document to convert itself back to a WebRowSet object.

JoinRowSet: A JoinRowSet implementation lets you create a SQL JOIN between RowSet objects when they are not connected to a data source. This is important because it saves the overhead of having to create one or more connections.

FilteredRowSet: A FilteredRowSet object lets you cut down the number of rows that are visible in a RowSet object so that you can work with only the data that is relevant to what you are doing.


This interface can be used to save/retrieve xml to/from database. mysql and javadb currently do not support SQLXML but we can use longtext instead for mysql.


Mysql does not support sql array data type.

insert into REGIONS values(
'{"93101", "97201", "99210"}');

java.sql.Array can handle arrays in db.
java.sql.Array aArray = con.createArrayOf("VARCHAR", someJavaArray);

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License