Java JDBC

JDBC

JDBC is the Java API for accessing relational databases. It consists of the interfaces and classes based from the package java.sql. There are only a few actual classes involved: For the most part, JDBC offers a set of standard interfaces which are implemented by the driver classes specific to the DBMS (DataBase Management System). Keep in mind that the term database usually means DBMS (which can manage more than one database).

Linking JDBC to a DBMS is effected by the driver JAR (Java ARchive) which provides classes which implement these (and other) interfaces of JDBC:
Array, Blob, CallableStatement, Clob, Connection, Driver, PreparedStatement, Ref, ResultSet, ResultSetMetaData, SQLData, SQLInput, SQLOutput, Statement, Struct

Data Types

A DBMS maintains its own set of data types. For example, these are some of the MySQL types:
SQL Type#bytesDescription
CHAR(N)Nfixed length string up to 255 bytes
VARCHAR(N)string length + (1 or 2) variable length string up to ≈ 65,000 bytes
TINYTEXTstring length + 1string up to ≈ 255 bytes
TEXTstring length + 2string up to 65,000 types
MEDIUMTEXTstring length + 3string up to ≈ 16 million bytes
LONGTEXTstring length + 4string up to ≈ 4.2 billion bytes
TINYINT10 to 255 or -127 to 128
SMALLINT20 to ≈ 65,000 (or neg/pos split)
MEDIUMINT30 to ≈ 16 million (or neg/pos split)
INT40 to ≈ 4 billion (or neg/pos split)
BIGINT80 to ≈ 18*1018 (or neg/pos split)
FLOAT46-digit precision
REAL (DOUBLE)816-digit precision
DECIMAL(N,D)length + (1 or 2)arbitrary precision
DATE3year-month-day
TIME3hour-min-sec
DATETIME8DATE and TIME combined
TIMESTAMP4# seconds since 1970
BINARY, VARBINARY like "*CHAR" types, but binary
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB like "*TEXT" types, but binary
ENUM('value1','value2',...)1 or 2column holds one of the specific string values
SET('value1','value2',...)1, 2, 3, 4 or 8column holds one or more of the specific string values
In contrast, the SQLite DBMS, a file-based system has only a few non-null types:
INTEGER, REAL, TEXT, BLOB
SQLite supports a type affinity whereby common SQL types listed above, such as VARCHAR can be used as well. There are issues to deal with, such as the fact that MySQL VARCHAR is case-insensitive whereas the VARCHAR for SQLite, which maps to TEXT, by default is case senstive.

Mapping to Java Types

Regradless of the DBMS, JDBC uses the data through its own types and the user must be aware of the correspondence with the types of the DBMS. Here are some examples for MySQL:
MySQL TypeJava Type
CHAR, VARCHAR, LONGVARCHARString
TEXTString
TINYINTbyte
SMALLINTshort
INT (INTEGER)int
BIGINTlong
NUMERIC, DECIMALjava.math.BigDecimal
REALfloat
FLOAT, DOUBLEdouble
BITboolean
BINARY, VARBINARY, LONGVARBINARY   byte[]
BLOB, MEDIUMBLOB, LARGEBLOB   byte[]
DATEjava.sql.Date
TIMEjava.sql.Time
DATETIME, TIMESTAMPjava.sql.Timestamp
Note, of course, that this correspondence is imperfect for VARCHAR due to its case-insensitive nature.

JDBC Usage

JDBC usages follow the same pattern:

Driver and Connection

Historically, the first JDBC drivers were JDBC-ODBC bridge drivers allowing Java to connect to a variety of databases through Microsoft's ODBC (Open Database Connectivity), a C-based API. Java supports this though a built-in driver class sun.jdbc.odbc.JdbcOdbcDriver.

For a long time now, MySQL has used its own pure-Java driver called the MySQL Connector/J driver:
com.mysql.jdbc.Driver
SQLite drivers for Java seem to be less fixed in stone. We use Zentus driver driver:
org.sqlite.JDBC
Loading the driver is usually done by calling the constructor implicitly via the call:
Class.forName("Some_JDBC_Driver");
This is equivalent to using the constructor in the more normal sense as
new Some_JDBC_Driver();
The former version has the advantage in that the driver does not have to be identified when the code is compiled. With the driver loaded, the key starting step is to obtain the connection through a call to the DriverManager.getConnection function as follows:
Connection cx = DriverManager.getConnection( url, user, password );
The url string is a specific designator of the data source for the desired DBMS which follows this pattern:
url = "jdbc:subprotocol:subname";
For example: The user and password are needed for MySQL, but not for SQLite, in which case they are simply disregarded if present.

Since JDK 1.6, the MySQL driver is automatically loaded when the connection is made, assuming that it the driver is accessible in the CLASSPATH. In contrast, the SQLite driver loading must be made explicit.

Connect to the DBMS

In the case of MySQL and other DBMSs, you can connect to it without specifying the database:
String url = "jdbc:mysql://database-server";
Connection cx = DriverManager.getConnection( url, user, password );
For example, you can do "root-like" actions by connecting as root with the above url:
Connection cx = DriverManager.getConnection( url, "root", "root-password" );

Sample Program

Here is a basic program which illustrates JDBC usage for both MySQL and SQLite. Create a dedicated NetBeans project JDBCTest and drop this code into the auto-generated main file:
   jdbctest.JDBCTest   Show Hide
package jdbctest;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
 
public class JDBCTest {
 
  static Connection getDBConnection1() throws SQLException {
    String host     = "localhost";
    String database = "test";
    String user     = "guest";
    String password = "";
 
    String url = String.format("jdbc:mysql://%s/%s", host, database);
 
    System.out.println("Connect to " + url);
    Connection cx = DriverManager.getConnection(url, user, password);
    return cx;
  }
 
  static Connection getDBConnection2() 
      throws SQLException, ClassNotFoundException 
  {
    String database = "database.sqlite";
 
    String url = String.format("jdbc:sqlite:%s", database);
 
    System.out.println("Connect to " + url);
    Class.forName("org.sqlite.JDBC");
    Connection cx = DriverManager.getConnection(url);
    return cx;
  }
 
  public static void main(String[] args) {
    try {
      Connection cx = getDBConnection1();    // mysql
//      Connection cx = getDBConnection2();    // sqlite
 
      System.out.println("Connection OK\n");
 
      // executeUpdate examples
      String sql;
      Statement stmt = cx.createStatement();
 
      sql = "drop table if exists things";
      System.out.println(sql);
      stmt.executeUpdate(sql);
 
      sql = "create table things (item varchar(10), qty int)";
      System.out.println(sql);
      stmt.executeUpdate(sql);
 
      sql = "insert into things values ('pencil',40), ('book', 5), ('pen',12)";
      System.out.println(sql);
      stmt.executeUpdate(sql);
 
      // executeQuery examples
      ResultSet rs;
 
      sql = "select * from things order by item";
      System.out.println("\n----- " + sql);
      rs = stmt.executeQuery(sql);
      while (rs.next()) {
        String item = rs.getString("item");
        int qty = rs.getInt("qty");
        System.out.format("record: %s,%s\n", item, qty);
      }
 
      sql = "select * from things where qty > 10";
      System.out.println("\n----- " + sql);
      rs = stmt.executeQuery(sql);
      while (rs.next()) {
        String item = rs.getString("item");
        int qty = rs.getInt("qty");
        System.out.format("record: %s,%s\n", item, qty);
      }
 
      // PreparedStatement example
      PreparedStatement prepStmt
          = cx.prepareStatement("select qty from things where item=?");
 
      Scanner keyboard = new Scanner(System.in);
 
      System.out.print("\nitem to search for: ");
      String item = keyboard.nextLine().trim();
 
      prepStmt.setString(1, item);
      System.out.println("\n----- " + prepStmt);
      rs = prepStmt.executeQuery();
      while (rs.next()) {
        int qty = rs.getInt("qty");
        System.out.format("quantity: %s\n", qty);
      }
 
    }
    catch (Exception ex) {
      System.out.println("FAILED: " + ex);
    }
  }
}
select
To use this in NetBeans:
  1. Create a New Java Application with the name JDBCTest. In the auto-generated Main class, replace the code with what is given below.
  2. For MySQL, right-click on Libraries and select Add Library. From the choices, select MySQL JDBC Driver and click the Add Library button.
  3. Also for MySQL, the program use the "standard" test database installed and accessible by the guest user with empty password. The check to make using the mysql command-line client is whether this gets you in with empty password:
    mysql -u guest test -p
    
  4. For SQLite, download the SQLite driver from the Computer Science site: Extract the JAR file from this archive and store it in some dedicated Java JAR library folder. Some suggestions:
    Documents\Jars     (Windows)
    ~/Library/Jars/    (Mac & Linux)
    
The test runs are based on the choice of database to use, which is determined by the outcome of getting the connection:
    public static void main(String[] args) {
    try {
      Connection cx = getDBConnection1();    // mysql
//      Connection cx = getDBConnection2();    // sqlite
which are either:
  static Connection getDBConnection1() throws SQLException {
    String host     = "localhost";
    String database = "test";
    String user     = "guest";
    String password = "";
 
    String url = String.format("jdbc:mysql://%s/%s", host, database);
 
    System.out.println("Connect to " + url);
    Connection cx = DriverManager.getConnection(url, user, password);
    return cx;
  }
or
  static Connection getDBConnection2() 
      throws SQLException, ClassNotFoundException 
  {
    String database = "database.sqlite";
 
    String url = String.format("jdbc:sqlite:%s", database);
 
    System.out.println("Connect to " + url);
    Class.forName("org.sqlite.JDBC");
    Connection cx = DriverManager.getConnection(url);
    return cx;
  }

SQL statement

Once we have established the connection, cx, there are three kinds of ways of to obtain an SQL statement from the connection: The Statement is first created from the connection:
Statement st = cx.createStatement();
A String sql_op representing a syntactically correct SQL command, can be executed with st. The type of execution used depends on whether there are any expected results. The section of the program which illustrates the executeUpdate usage is:
      // executeUpdate examples
      String sql;
      Statement stmt = cx.createStatement();
 
      sql = "drop table if exists things";
      System.out.println(sql);
      stmt.executeUpdate(sql);
 
      sql = "create table things (item varchar(10), qty int)";
      System.out.println(sql);
      stmt.executeUpdate(sql);
 
      sql = "insert into things values ('pencil',40), ('book', 5), ('pen',12)";
      System.out.println(sql);
      stmt.executeUpdate(sql);

Result Set Usage

When the executeQuery command is used (typically for a SELECT query), a ResultSet is returned. The results of such a query are specific columns from rows which matched the query. Typically we want to read these rows sequentially and process the information. This is done in a fairly standard manner:
while (rs.next()) {
   // get data from columns in the row "rs"
}
We can retrieve a specific column from rs by:
<type> info = rs.get<type>( <column name> );
or
<type> info = rs.get<type>( <column index> );
where <type> matches the standard Java types like String, int, Timestamp, etc. For example, assuming a title field in the record,
String title = rs.getString( "title" );
The call
Object info = rs.getObject ( .. );
gives a type-independent way to retrieve the column data (in which case a field with the SQL INT type will probably return a java.lang.Long object).

If a single row is expected, say, in the search based on a key value, the while loop format can be replaced by something like this:
if (! rs.next() ) {
  throw new Exception("something unexpected");
}
<type> info = rs.get<type>( <column name> );
The section of the program which illustrates the executeQuery and ResultSet usage is:
      // executeQuery examples
      ResultSet rs;
 
      sql = "select * from things order by item";
      System.out.println("\n----- " + sql);
      rs = stmt.executeQuery(sql);
      while (rs.next()) {
        String item = rs.getString("item");
        int qty = rs.getInt("qty");
        System.out.format("record: %s,%s\n", item, qty);
      }
 
      sql = "select * from things where qty > 10";
      System.out.println("\n----- " + sql);
      rs = stmt.executeQuery(sql);
      while (rs.next()) {
        String item = rs.getString("item");
        int qty = rs.getInt("qty");
        System.out.format("record: %s,%s\n", item, qty);
      }

Prepared Statements

The PreparedStatement provides insertion points into an SQL statement for data. This is an important feature because it sanitizes the data by escaping control characters. It can also be useful to make multiple calls with different data more efficient.
PreparedStatement st = cx.prepareStatement( sql_op );
The String sql_op contains zero or more "?" characters which represent unspecified values. The values are specified by Java statements:
st.set<type>(pos, value);
For example,
st.setString(pos, value);
specifies the value of the specified type to be placed in position pos (starting from 1) of the statement. After setting the values of all postions we can call either one of the (parameterless):
st.executeUpdate(); or st.executeQuery();
This section of the program illustrates the usage of the PreparedStatement.
      // PreparedStatement example
      PreparedStatement prepStmt
          = cx.prepareStatement("select qty from things where item=?");
 
      Scanner keyboard = new Scanner(System.in);
 
      System.out.print("\nitem to search for: ");
      String item = keyboard.nextLine().trim();
 
      prepStmt.setString(1, item);
      System.out.println("\n----- " + prepStmt);
      rs = prepStmt.executeQuery();
      while (rs.next()) {
        int qty = rs.getInt("qty");
        System.out.format("quantity: %s\n", qty);
      }
When you run it, try item inputs like these:
pen
computer
john's book
An important thing to note about PreparedStatement usage is that values introduced through "?" insertions are automatically escaped correctly. Furthermore, a PreparedStatement can be reused for different inputs. In particular, you should generally prefer the PreparedStatement over the regular Statement in complex SQL constructions.

Closing the connection.

This is done with the statement:
cx.close();
This is sometimes necessary to finalize a database transaction in certain situations where we can hold off completion of the changes until the program completes. In our examples which are interactive, we usually want the database operations to take place immediately. In this case, it is neither desirable nor necessary to explicitly close the connection.


© Robert M. Kline