Create cross-platform database-driven applications with JDBC

by Contributor Melonfire | Jan 29, 2007 8:04:00 PM

Tags: Databases, Java, Contributor Melonfire, CITY_TEMP, CITY_TEMP value, JDBC, RDBMS

Takeaway: The Java Database Connectivity (JDBC) API offers a unified interface to different databases, providing a series of generic functions that are internally translated into native function calls. This makes it extremely easy to create database-driven applications that work across different RDBMS types. This introduction shows you how to use JDBC methods to perform basic database operations.

This article is also available as a TechRepublic download, which includes the code samples in a more manageable text file format.

One of Java most powerful APIs is the Java Database Connectivity (JDBC) interface. This module offers a unified interface to different databases, providing a series of generic functions that are internally translated into native function calls. This makes it extremely easy to create database-driven applications that work across different RDBMS types, and in fact to switch from one database architecture to another without significantly affecting your application code.

In this tutorial, I'll give you an introduction to JDBC, showing you how to use JDBC methods to perform basic database operations, including:

• Retrieving and processing resultsets
• Inserting new records and modifying existing ones, and
• Obtaining metadata about installed databases and tables

Note: This tutorial assumes that your Java development environment contains correctly-configured JDBC libraries. The SQL code in this tutorial has been tested on the Oracle RDBMS, and you may need to modify this for other database systems and datatypes.

Setting up the example table

In this step, you'll create an example table that will be used in subsequent examples, and also set access permissions and driver information to enable connectivity between your Java application and your RDBMS.

To begin, create an example table for your SQL queries, which holds weather information for various cities around the globe. If you're using Oracle, the following table creation code will work:

CREATE TABLE CITY_TEMP1 (ID NUMBER, CITY VARCHAR2(30), TEMP NUMBER(4,2), CONSTRAINT ID_PK PRIMARY KEY (ID))

Alternatively, if your tastes swing more towards open-source RDBMSs like MySQL, you can use the following SQL code to generate this table:

CREATE TABLE CITY_TEMP (ID TINYINT NOT NULL, CITY VARCHAR( 40 ) NOT NULL, TEMP FLOAT NOT NULL, PRIMARY KEY ( `ID` ))

Once the table has been created, populate it with some data, as follows:

INSERT INTO CITY_TEMP VALUES (1, 'LONDON', 8.8);
INSERT INTO CITY_TEMP VALUES (2, 'MUMBAI', 22.0);
INSERT INTO CITY_TEMP VALUES (3, 'NEW YORK', 2.5);
INSERT INTO CITY_TEMP VALUES (4, 'PARIS', 12.6);
INSERT INTO CITY_TEMP VALUES (5, 'SYDNEY', 25.3);
INSERT INTO CITY_TEMP VALUES (6, 'TOKYO', 5.7);
INSERT INTO CITY_TEMP VALUES (7, 'DEHLI', 36.2);
INSERT INTO CITY_TEMP VALUES (8, 'BERLIN', 10.1);
INSERT INTO CITY_TEMP VALUES (9, 'BAGHDAD', 34.4);
INSERT INTO CITY_TEMP VALUES (10, 'RANGOON', 23.9);

Here's what the end result should look like:

+----+----------+------+
| ID | CITY     | TEMP |
+----+----------+------+
|  1 | LONDON   |  8.8 |
|  2 | MUMBAI   |   22 |
|  3 | NEW YORK |  2.5 |
|  4 | PARIS    | 12.6 |
|  5 | SYDNEY   | 25.3 |
|  6 | TOKYO    |  5.7 |
|  7 | DEHLI    | 36.2 |
|  8 | BERLIN   | 10.1 |
|  9 | BAGHDAD  | 34.4 |
| 10 | RANGOON  | 23.9 |
+----+----------+------+

Connecting to the database

In order to write JDBC code for any RDBMS, it is necessary to first obtain and install a driver class for that RDBMS. Drivers are available for most well-known RDBMSs: for example, you can obtain an Oracle JDBC driver or a MySQL driver. Obtain the driver for your particular database, and ensure that it's available in your Java CLASSPATH.

Next, configure access to the RDBMS, via a configuration file that contains information on the server host name, log-in user name, and password. This file should be named Connection.properties, and might be formatted like this:

driverName=the driver name

url=url for connection

userName=user name

password=password

Thus, for Oracle, you might have:

driverName=oracle.jdbc.driver.OracleDriver

url=jdbc:oracle:thin:@host:1521:db1

userName=user

password=pass

And for MySQL, you might have:

driverName=com.mysql.jdbc.Driver

url=jdbc:mysql:///db1

userName=user

password=pass

Retrieving and processing query results

With all these pieces in place, let's get started with some code. We'll begin with something very basic: executing a SELECT * query and processing the query results. Let's begin by writing some code (Listing A) to create and return a connection to the database.

Listing A

package jdbc;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.PropertyResourceBundle;
import java.util.ResourceBundle;

/**
 * This class will load the driver and connect to database based on values present
 * in the property file. It will have a method to create a connection object and
 * return it.
 *
 */
public class DatabaseConnector
{
    /** This variable will hold the value for driver name.*/
    String driverName = null;
   
    /**
     * This variable will hold the value for a database url of the form
     * jdbc:subprotocol:subname
     */
    String url = null;

    /**
     * This variable will hold the value for the database user on whose behalf
     * the connection is being made
     */
    String userName = null;

   /**
    * This variable will hold the value for the user's password 
    */
    String password = null;

    /**
     * The constructor reads the property file and populates variables
     */
    public DatabaseConnector()
    {
        try
        {
            InputStream is = new FileInputStream("Connection.properties");
            ResourceBundle bundle = new PropertyResourceBundle(is);
            driverName = (String) bundle.getObject("driverName");
            url = (String) bundle.getObject("url");
            userName = (String) bundle.getObject("userName");
            password = (String) bundle.getObject("password");
        }
        catch (FileNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }
   
    /**
     * This method will create a connection and return it.
     * @return a connection to the URL
     */
    public Connection getConnection()
    {
        Connection conn = null;
       
        try
        {
            // Loading the driver
            Class.forName(driverName);
           
            // Creating connection
            conn = DriverManager.getConnection(url, userName, password);
        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
       
        return conn;
    }
}

In this class, the getConnection() method is used to create a common Connection object, using the access parameters and driver specified in the Connection.properties configuration file. If there's a problem with the connection -- for example, if the user name or password is incorrect -- the code above will generate an exception. Before using this class, remember to modify the code above to reflect the real path to the configuration file on your system.

Next up, is a class that executes a SELECT query and processes the results. (Listing B)

Listing B

package jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * This class will read and print the contents of a table
 *
 */
public class DataSelector
{
    public static void main(String[] args)
    {
        DataSelector ds = new DataSelector();
        ds.select();
    }
   
    /**
     * This method will select all rows and print them to the console.
     *
     */
    private void select()
    {
        int id = 0;
        float temp = 0.0F;
        String cityName = null;
       
        String query = "SELECT ID, CITY, TEMP FROM CITY_TEMP";
       
        try
        {
            // Creating statement and executing the query
            Connection conn = new DatabaseConnector().getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
           
            while(rs.next())
            {
                id = rs.getInt(1);
                cityName = rs.getString(2);
                temp = rs.getFloat(3);
               
                System.out.println(id + ", " + cityName + ", " + temp);
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}

This class first uses the DatabaseConnector class to open a connection to the database. It then creates a Statement object, which exposes an executeQuery() method. A simple SELECT query is then passed to this executeQuery() method, which returns a Resultset object representing the results of the query. A while loop, combined with the object's next() method, is then used to iterate over the result set, printing the values of each field. Note that the getString(), getInt() and getFloat() methods are used to access different datatypes of the result set, with the field index passed to each as argument.

Tip: You can replace the field index with the field name to obtain equivalent results. For example, in the previous listing, the method call getFloat("temp") would be equivalent to the call getFloat(3).

Here's the output of the previous listing:

1, LONDON, 8.8
2, MUMBAI, 22.0
3, NEW YORK, 2.5
4, PARIS, 12.6
5, SYDNEY, 25.3
6, TOKYO, 5.7
7, DEHLI, 36.2
8, BERLIN, 10.1
9, BAGHDAD, 34.4
10, RANGOON, 23.9

Adding new records

The Statement object you saw in the previous listing also exposes an executeUpdate() method, which comes in particularly handy for adding new records (or updating existing ones). To see it in action, consider Listing C, which defines a new insert() method to execute an INSERT query and add a new record to the table.

Listing C

package jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * This class will insert a row in the table and print a message
 * if successful
 *
 */
public class DataInserter
{
    public static void main(String[] args)
    {
        DataInserter ds = new DataInserter();
        int result = ds.insert();

        if(result != 0)
        {
            System.out.println(result + " row(s) inserted successfully.");
        }
        else
        {
            System.out.println("Data could not be inserted.");
        }
       
    }
   
    /**
     * This method will insert a row and return an integer value
     * specifying the number of rows inserted. 
     * @return no of rows inserted
     */
    private int insert()
    {
        int id = 13;
        int result = 0;
        float temp = 4.5F;
        String cityName = "CALCUTTA";
       
        String query = "INSERT INTO CITY_TEMP VALUES ("+ id + ", '" +
                        cityName + "', "+ temp+")";
        try
        {
            // Creating statement and executing the query
            Connection conn = new DatabaseConnector().getConnection();
            Statement stmt = conn.createStatement();
            result = stmt.executeUpdate(query);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
       
        return result;
    }
}

The internals of the insert() method are fairly self-explanatory: a number of variables, representing the new data to be added, are initialized and interpolated into a query string. A new Connection and Statement object is created, and the Statement object's executeUpdate() method is used to process the query string. A message is then printed to the console, indicating whether the addition was successful and the number of rows updated. If an error occurs -- for example, try altering the record ID to one that's already in use -- an exception and stack trace will be generated.

Updating and deleting existing records

Updating or deleting existing records follows a similar process: initialize a Statement object and then pass the object's executeUpdate() method an appropriate UPDATE or DELETE query. Listing D is an example.

Listing D

package jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * This class will update the table and a print a message if it
 * was successfully updated.
 *
 */
public class DataModifier
{
    public static void main(String[] args)
    {
        DataModifier ds = new DataModifier();
        int result = ds.update();
        if(result != 0)
        {
            System.out.println(result + " row(s) updated successfully.");
        }
        else
        {
            System.out.println("Data could not be updated.");
        }
    }
   
    /**
     * This method will update table and return an integer value
     * specifying the number of rows updated. 
     *
     * @return no of rows updated
     */
    private int update()
    {
        int id = 20;
        int result = 0;
        float temp = 32.9F;
       
        String query = "UPDATE CITY_TEMP SET TEMP=" + temp + " WHERE ID = "+ id;
        try
        {
            // creating statement and executing the query
            Connection conn = new DatabaseConnector().getConnection();
            Statement stmt = conn.createStatement();
            result = stmt.executeUpdate(query);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
       
        return result;
    }
}

Here, the executeUpdate() method will update the table and return an integer value specifying the number of rows updated. One point of interest here: because it's possible to write an UPDATE or DELETE query that is successful without actually altering the table -- for example, an UPDATE query that references an non-existent record ID -- your code should check this return value (as the listing above does) and return an appropriate message.

Obtaining database and table information

JDBC also lets you obtain meta-information on the databases and tables in an RDBMS. This includes basic information, such as table names and types, as well as more advanced information on table data types, keys, field names and constraints. The key to this information is the DatabaseMetaData object, which is illustrated in Listing E.

Listing E

package jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * This class provides information related to the database. It shows
 * how the Java API can be used to retrieve database information.
 */
public class DatabaseUtil
{
    public static void main(String[] args)
    {
        DatabaseUtil dbUtil = new DatabaseUtil();
        dbUtil.getDatebaseInfo();
    }
   
    /**
     * This method gets information related to the database.
     * It returns the following output
     * Schema Name
     *      Table Name (number of rows)
     *
     */
    private void getDatebaseInfo()
    {
        try
        {
            // Getting DatabaseMetaData instance
            DatabaseConnector dc = new DatabaseConnector();
            Connection conn = dc.getConnection();
            DatabaseMetaData dmd = conn.getMetaData();

            String tableName = null;
            String schemaName = null;
            String query = "SELECT COUNT(*) FROM ";
           
            // Retrieving the schema names
            ResultSet rsOfSchemas = dmd.getSchemas();
            Statement stmt = conn.createStatement();
           
            while(rsOfSchemas.next())
            {
                schemaName=rsOfSchemas.getString("TABLE_SCHEM");

                System.out.println(schemaName);
                // Retrieving the table names
                ResultSet rsOfTables = dmd.getTables(null,schemaName,null,new String[]{"TABLE"});
               
                while(rsOfTables.next())
                {
                    tableName = rsOfTables.getString("TABLE_NAME");
                    // Retrieving the number of rows in a table
                    ResultSet rsOfNoOfRows = stmt.executeQuery(query + tableName);
                   
                    while(rsOfNoOfRows.next())
                    {
                        int nofOfRows = rsOfNoOfRows.getInt(1);
                        System.out.println("\t" + tableName + "(" + nofOfRows +")");
                    }
                }
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}

This class makes use of two methods exposed by the DatabaseMetaData object: getSchemas(), which returns information on available databases or schemas, and getTables(), which returns a list of table names within each database. Both methods return Resultset objects, which can be processed using a standard while() loop. The utility class above also attempts to return some information on the number of records in each table, by executing a SELECT COUNT(*) query on each of the tables returned by getTables().

Listing F is an example of the output:

Listing F

USR1
      EMP(7)
      DEPT(2)
USR2
      TEMP(1)
      T1(0)
USR3
USR5
      CITY_TEMP(11)
      XENON(43)

The DatabaseMetaData object also exposes a number of other useful methods: getColumns() for field information; getColumnPrivileges() for field access rights; getDefaultTransactionIsolation() for the transaction isolation level; getIndexInfo() for information on a table's indices; getProcedures() for a list of available stored procedures; getUserName() for the name of the currently logged-in user; and many more. Read more about available methods on the Sun Web site.

Note: Keep in mind that different database systems have different capabilities, and so it's quite possible that some of these methods may not work on your particular RDBMS. Look in your RDBMS' driver documentation for information about which methods are supported.

And that's about it for this tutorial. The class templates above will save you some time the next time you sit down to write a database-driven Java application. Happy coding!

Print/View all Posts Comments on this article

JSPvaliantsin1983@...  | 05/09/07