Jdbc

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.

Connection

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();
ds.setServerName("...");
ds.setDatabaseName("...");
 
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.

Transactions

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

SQLException

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.

RowSet

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.

SQLXML

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.

Arrays

Mysql does not support sql array data type.

insert into REGIONS values(
'Northwest',
'{"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