JDBC : Java DataBase Connectivity - Java advanced (OCP)

JDBC is a database neutral Java API in the java.sql package that allows connectivity and operations on databases. JDBC drivers provide database specific implementations of the API (for MySql, PostgreSQL, DB2… as examples). Driver libraries are made available to the application class loader via class or module path.

This article is part of a series on advanced Java concepts, also corresponding to topics to study for upgrading from level I certification to level II certification (OCP level). The new Oracle Java certification framework (as from Java 11) only offers one certification covering both levels, so you’ll want to go through the level I (“OCA”) topics as well.

Connecting to the database

The database driver to use is selected using JDBC connection String:

jdbc:<provider>:(<driver type>:)<connection details>

Examples:

jdbc:oracle:thin:@<host>:<port>:<db name>

jdbc:postgresql://<host>:port/dbName

jdbc:mysql://<host>:port/dbName

JDBC classes

• Connection interface: create a session with a specific database, create statements
• DriverManager class: manage db drivers, create connection objects
• Statement interface: hold basic SQL statements
• PreparedStatement interface: hold precompiled SQL statements
• CallableStatement interface: allow calls to stored procedures or functions
• ResultSet interface: hold a set of records returned by a query (a query is a “select” statement)

They all implement AutoCloseable interface, thus have to be explicitly closed in finally block or used in a try-with-resources.

Their methods can throw SQLException, which outputs database specific error codes and SQL status information, in addition to usual Java exceptions details, thanks to the database driver.

try (Connection c = DriverManager.getConnection(connectionString);
     PreparedStatement s = c.prepareStatement(aStatement);
     ResultSet rs = s.executeQuery();) {
 ...
} catch (SQLException e) {
  // additional information methods available for DB errors
  e.getSQLState();
  e.getErrorCode();
}

If you use the class try and close manually the resources, the closure order matters. The order of closure is the reverse order of opening the resources (thus in this example, ResultSet, then PreparedStatement and Connection). Closing the connection first wouldn’t throw an error, but the database will not clean up and other resources will stay open.

Statement

Statement is not “parameter aware”. It holds a string where parameters are concatenated, which causes a risk of SQL injections. It has to be parsed and recompiled before every execution.

Statement statement = c.createStatement();
statement.executeQuery(theQuery);
ResultSet results = statement.getResultSet();
statement.executeUpdate(theStatement);
int rowCount = statement.getUpdateCount(); // get number of rows updated
boolean isQuery = statement.execute(theStatement); // returns true if statement is a query (select)

PreparedStatement

Prepared statements don’t use string concatenation and are parameters aware.

The character “?” is used in the statement each time a parameter is expected.

Gotcha! Parameters positions are indexed from 1, not 0! (That’s the case for all similar indexes in JDBC)

PreparedStatement ps = connection.prepareStatement("select id from products where price > ? and discount = ?");
ps.setInt(position, value);
ps.setObject(position, value, Types.INTEGER);

CallableStatement

Callable statements allow to call stored procedures and functions in the database.

// CALLING A PROCEDURE (no result returned)

String functionCall = "{call theStoredProcedure(?,?)}"; // for example, an insertion
CallableStatement stmt=con.prepareCall(functionCall);  
stmt.setInt(1, theIntValue);  
stmt.setString(2,theString);  
stmt.execute();  

// CALLING A FUNCTION (returns a result)

String functionCall = "?={ call theStoredFunction(?)}";
CallableStatement cs = connection.prepareCall(functionCall);
cs.registerOutParameter(1, Types.VARCHAR); // first index will hold the response
cs.setObject(2, myDate, Types.DATE); // can also be written: cs.setDate(2, myDate);
cs.execute();
String response = cs.getObject(1, Types.VARCHAR); // can also be written: cs.getString(1);

ResultSet

Iterate over a ResultSet:

resultSet.next() // returns true if a next record exists and moves the pointer to it; false if no more records

resultSet.getObject(position, type);
resultSet.getObject(name, type);
resultSet.getString(position);
resultSet.getInt(name);
// position and name refer to column's

Transactions

By default, JDBC handles transactions in auto-commit mode.

Commit occurs automatically when statement processing is successful.

connection.setAutoCommit(false); // change behavior
connection.getAutoCommit(); // returns true if mode is auto-commit

SavePoint sp = connection.setSavePoint();
connection.rollback(sp); // to a specified savepoint
connection.commit();
connection.rollback(); // whole transaction (uncommitted)

Gotcha! Explicit connection closure causes commit of current transaction even if the mode is not auto-commit.

Database meta data

Get information on database:

DatabaseMetaData db = connection.getMetaData();
// String:
db.getDatabaseProductName();
db.getProductVersion();
db.getSQLKeywords(); // Retrieves a comma-separated list of all of this database's SQL keywords that are NOT also SQL:2003 keywords
// boolean:
db.supportsOuterJoins();
db.supportsSavepoints();

Get result set meta data:

ResultSetMetaData rs = resultSet.getMetaData();
rs.getColumnCount();
rs.getColumnName(index);
rs.getColmunType(index);

---

Check other OCP topics related articles and subscribe to receive new posts in your mailbox.