Working With JDBC

Connecting to a database

In order to connect to a database, you need to perform some initialization first. Your JDBC driver has to be loaded by the Java Virtual Machine classloader, and your application needs to check to see that the driver was successfully loaded. We'll be using the ODBC bridge driver, but if your database vendor supplies a JDBC driver, feel free to use it instead.

// Attempt to load database driver

try
{

  // Load Sun's jdbc-odbc driver
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
}
catch (ClassNotFoundException cnfe) // driver not found
{
 System.err.println ("Unable to load database driver");
 System.err.println ("Details : " + cnfe);
 System.exit(0);
}

We try to load the JdbcOdbcDriver class, and then catch the ClassNotFoundException if it is thrown. This is important, because the application might be run on a non-Sun virtual machine that doesn't include the ODBC bridge, such as Microsoft's JVM. If this occurs, the driver won't be installed, and our application should exit gracefully.

Once our driver is loaded, we can connect to the database. We'll connect via the driver manager class, which selects the appropriate driver for the database we specify. In this case, we'll only be using an ODBC database, but in more complex applications, we might wish to use different drivers to connect to multiple databases. We identify our database through a JDBC URL.

A JDBC URL starts with "jdbc:" This indicates the protocol (JDBC). We also specify our database name in the URL. As an example, here's the URL for an ODBC datasource called 'demo'. Our final URL looks like this :

jdbc:odbc:demo

To connect to the database, we create a string representation of the database. We take the name of the datasource from the command line, and attempt to connect as user "dba", whose password is "sql".

// Create a URL that identifies database
String url = "jdbc:odbc:" + args[0];


// Now attempt to create a database connection
Connection db_connection = DriverManager.getConnection (url, "dba", "sql");

As you can see, connecting to a database doesn't take much code.

Executing database queries

In JDBC, we use a statement object to execute queries. A statement object is responsible for sending the SQL statement, and returning a set of results, if needed, from the query. Statement objects support two main types of statements - an update statement(executeUpdate) that is normally used for operations which don't generate a response, and a query statement(executeQuery) that returns data.

Once we have an instance of a statement object, you can call its executeUpdate and executeQuery methods. To illustrate the executeUpdate command, we'll create a table that stores information about employees. We'll keep things simple and limit it to name and employee ID.

Now that there's data in the table, we can execute queries. The response to a query will be returned by the executeQuery method as a ResultSet object. ResultSet objects store the last response to a query for a given statement object. Instances of ResultSet have methods following the pattern of getXX where XX is the name of a data type. Such data types include numbers (bytes, ints, shorts, longs, doubles, big-decimals), as well as strings, booleans, timestamps and binary data.

// Create a statement to send SQL
Statement db_statement = db_connection.createStatement();


// Create a simple table, which stores an employee ID and name
db_statement.executeUpdate("create table employee { int id, char(50) name };");

// Insert an employee, so the table contains data
db_statement.executeUpdate("insert into employee values (1, 'John Doe');");

// Commit changes
db_connection.commit();


// Execute query
ResultSet result = db_statement.executeQuery("select * from employee");

// While more rows exist, print them
while (result.next() )
{
// Use the getInt method to obtain emp. id
System.out.println ("ID : " + result.getInt("ID"));
// Use the getString method to obtain emp. name
System.out.println ("Name : " + result.getString("Name"));
System.out.println ();
}

More Posts on JDBC will be added here, note that System.out is just for demonstrating purpose in production best practice is to use Logger framework for logging.