View RSS Feed

JDBC

Working with JDBC in Java

Rate this Entry
by , 04-04-2012 at 06:32 AM (3543 Views)
Java Database Connectivity (JDBC) is a technology that allows Java applications working with relational database management systems (RDBMS) such as MySQL, Microsoft SQL Server, Oracle, PostgreSQL, … via Structured Query Language (SQL) statement. The JDBC API is included in JDK, so any Java application can communicate with various database systems without requiring additional installation, as long as a JDBC driver is provided for the targeted database system.

By the end of this article, you will know basic concepts of JDBC, how it works and how to do some commonly used database operations in Java applications.


Overview of JDBC


At a glance, JDBC is a set of APIs that allows developers to establish connection with a database system, execute CRUD (create, read, update and delete) operations, iterate over results set and close the connection.


Key features

  • Metadata support: JDBC provides full access to metadata of the underlying database system.
  • No installation: JDBC requires only a suitable driver library for the target database system without any special installation.
  • URL-based database connection identification: a database connection is identified by a URL-based pattern which makes code more portable and easier to maintain.
  • Java platform integration: JDBC is an integral part of the Java platform (for both standard edition and enterprise edition), thus developers can write portable database driven applications.


Benefits

  • Work with almost database systems: as any popular database system provides JDBC-compliant driver, Java applications can work with various database systems and easily switch to another one without changing code.
  • Database development made easy: The JDBC API is simple to use and make database development easier by hiding many complexity of data access tasks.
  • Zero configuration: Java programs do not require any configuration to work with JDBC.



JDBC API

The latest version of JDBC API is 4.1 (comes Java SE 7). The API contains two packages:

  • java.sql: provides core API.
  • javax.sql: provides additional API for server side database capabilities.



API specification for both the packages is available in the Java SE API documentation.
Home page of JDBC technology can be found at: http://www.oracle.com/technetwork/ja...ew-141217.html


JDBC drivers

A JDBC driver is a library written in Java that allows the JDBC API communicating with the underlying database system. Database vendors implement and provide JDBC drivers for their database system.

Developers need to have the JDBC driver library present in the classpath in order to test/run Java applications with a database system, though it is not needed to compile source code because the source code is dealing with the JDBC API only.

To search for a specific JDBC driver for your database system, go to: http://devapp.sun.com/product/jdbc/drivers
Oracle also issues a list of vendors that built endorsed JDBC drivers, the list can be found at: http://www.oracle.com/technetwork/ja...ex-136695.html

The code example in this article is supposed to be tested with MySQL database. You can download JDBC driver for MySQL at: http://www.mysql.com/downloads/connector/j/

Extract the zip distribution and you will see a JAR file called something like mysql-connector-java-5.1.18-bin.jar, add this jar file to your project’s classpath.


Establish a connection

This is the first step to communicate with any database system. To make a connection, you have to specify:

  • Driver class name: fully qualified name of the driver class, for example: com.mysql.jdbc.Driver is the name of driver class for MySQL.
  • Database URL: to identify server name, port number and database name. For example, the following URL:

    jdbc:mysql://localhost:3306/mysql

    identifies a MySQL database name “mysql”, server name is localhost, port number is 3306.
  • Database user (user name and password): is the user account which has permission to work with the database.



The following code establishes a connection to a MySQL database called “mysql”:

Java Code: Make a database connection in JDBC
        String driverClassName = "com.mysql.jdbc.Driver";
        String databaseURL = "jdbc:mysql://localhost:3306/mysql";
        String userName = "root";
        String password = "password";
       
        try {
            Class.forName(driverClassName);
            Connection conn = DriverManager.getConnection(databaseURL, userName, password);
            System.out.println("Connected to database at " + databaseURL);
            conn.close();
        } catch (ClassNotFoundException cfe) {
            System.out.println("Database driver class could not be found");
            cfe.printStackTrace();
        } catch (SQLException sqlex) {
            System.out.println("SQL ERROR");
            sqlex.printStackTrace();
        }
Note that the driver class name and database URL are different on different database system, so consult documentation provided by database vendor to know the exact pattern.

A Connection object is returned once the database is successfully connected. Any subsequent database operations have to be made within the context of this connection.


Working with Statement and PreparedStatement

Java applications talk with a database server by sending SQL statements. JDBC API provides two interfaces that represent two kinds of statements: Statement and PreparedStatement.

  • Statement: is used to execute static SQL statements which do not have parameters.
  • PreparedStatement: is for executing parameterized SQL statements. This interface extends from the Statement interface.



Both the interfaces define various methods for executing SQL statements. Here is to name the most commonly used methods:

  • boolean execute(String sql): executes a single SQL statement, return true if the statement returns multiple results; return false if there is no results or update count. This method is typically used when you don’t care about the return, for example, executing a stored procedure.

  • int executeUpdate(String sql): executes a SQL statement that does not return a result, it returns the number of rows affected instead. This method is used to execute UPDATE, INSERT, or DELETE statements.

  • ResultSet executeQuery(String sql): executes a SQL statement which returns results in a ResultSet object. This method is used to execute SELECT statement and to get the returned result.



For example, the following code executes a DELETE statement using executeUpdate() method:

Java Code: Execute a static SQL statement
        try {
            Statement statement = conn.createStatement();
            String sql = "DELETE FROM timesheet WHERE id=4";
            int rowDeleted = statement.executeUpdate(sql);
            System.out.println("Number of rows deleted: " + rowDeleted);
            statement.close();
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
        }
Note that the statement should be closed after the query is executed.

For executing INSERT or UPDATE statements, it is common to use the PreparedStatement for easily manipulating the parameters. A parameterized INSERT statement looks like this:

INSERT INTO timesheet (name, job_type, start_date, end_date) VALUES (?, ?, ?, ?)

Values of parameters are put in question marks. The PreparedStatement interface provides a number of methods to replace the question marks with actual values:

setXXX(int parameterIndex, XXX value)

where XXX represents actual data type (Byte, Date, Int, String…) and parameterIndex represents the index of the parameter (remember the index is 1-based, not 0-based, meaning the first parameter is at index 1, and so on…).

For example, the following code executes an INSERT statement using the PreparedStatement interface:

Java Code: Execute a dynamic SQL statement
        try {
            String sqlInsert = "INSERT INTO timesheet (name, job_type, start_date, end_date) VALUES (?, ?, ?, ?)";
            PreparedStatement statement = conn.prepareStatement(sqlInsert);
           
            statement.setString(1, "Peter Smith");
            statement.setString(2, "Java developer");
            statement.setDate(3, java.sql.Date.valueOf("2012-02-15"));
            statement.setDate(4, java.sql.Date.valueOf("2012-03-31"));
           
           
            int rowInserted = statement.executeUpdate();
            System.out.println("Number of rows inserted: " + rowInserted);
            statement.close();
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
        }

There is a couple of difference compared with using the static Statement interface:

  • The parameterized SQL statement is passed to the preparedStatement() method to create a PreparedStatement object.
  • The no-argument executeUpdate() is called to execute the query.



Working with ResultSet

The executeQuery() method is used to execute SELECT statement which may return one or more rows from the database. The data can be accessed by a ResultSet object which provides various methods for iterating over the result and extracting data for a specific row. A ResultSet object maintains a cursor that points to the current row in the result set.

  • Navigation methods: return true if there is a row available, false if there is no row.

    • first(): move the cursor to the first row.
    • next(): move the cursor to the next row.
    • previous(): move the cursor the previous row.
    • last(): move the cursor to the last row.

  • Data extraction methods: are used to get value of a specific column in the current row.


  • XXX getXXX(int columnIndex): retrieves value of a column at the specified index.
  • XXX getXXX(String columnLabel): get value of a column by its name.

Where XXX represents data type, for example:


byte getByte(int columnIndex)

String getString(String columnLabel)


For example, the following code executes a SELECT statement and iterate over the result set:

Java Code: Loop through rows in database using ResultSet
        try {
            String sqlSelect = "SELECT * FROM timesheet";
            Statement statement = conn.createStatement();
           
            ResultSet result = statement.executeQuery(sqlSelect);
           
            int rowCount = 1;
           
            while (result.next()) {
                String name = result.getString("name");
                String jobType = result.getString("job_type");
                Date startDate = result.getDate("start_date");
                Date endDate = result.getDate("end_date");
               
                System.out.println(String.format("Row %d: %s - %s - %s - %s",
                        rowCount, name, jobType, startDate, endDate));
                rowCount++;
            }
           
            statement.close();
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
        }

Working with transaction

Transaction helps to ensure data integrity, meaning all changes are made or nothing is made to the database system within a single transaction, to prevent partially updated data in case of exceptions or network failure…JDBC makes working with transaction easily. The Connection interface defines some methods for working with transaction:

  • setAutoCommit(boolean autoCommit): Enables/disables auto-commit mode. By default, auto-commit is enabled. To work with transaction you should disable auto-commit.
  • commit(): makes all changes permanently to the database system, should be called when auto-commit is disabled.
  • rollback(): reverts all changes made in the current transaction, all modified values are reverted back to their previous values.



For example, the following code illustrates using transaction:

Java Code: Using transaction in JDBC
        try {
            // disable auto-commit to work in transaction
            conn.setAutoCommit(false);
           
            String sqlInsert = "INSERT INTO timesheet (name, job_type, start_date, end_date) VALUES (?, ?, ?, ?)";
            PreparedStatement statement = conn.prepareStatement(sqlInsert);
           
            statement.setString(1, "Bill John");
            statement.setString(2, "Manager");
            statement.setDate(3, java.sql.Date.valueOf("2011-01-10"));
            statement.setDate(4, java.sql.Date.valueOf("2012-02-01"));
           
            statement.executeUpdate();
           
            String sqlUpdate = "UPDATE timesheet SET job_type = ? WHERE id=?";
            statement = conn.prepareStatement(sqlUpdate);
            statement.setString(1, "Manager");
            statement.setInt(2, 2);
            statement.executeUpdate();
           
            String sqlDelete = "DELETE FROM timesheet WHERE id=6";
            statement.executeUpdate(sqlDelete);           
                       
            // makes changes permanently as a transaction
            conn.commit();
           
        } catch (SQLException sqlex) {
            try {
                // reverts to previous state
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                // enable auto-commit (back to default)
                conn.setAutoCommit(true);           
            } catch (SQLException ex) {
                ex.printStackTrace();
            }           
        }
As you can notice in the above code, the transaction involves executing three statements: INSERT, UPDATE and DELETE. The changes are rolled back in case any exception occurred in the middle of the execution of the three statements. The changes are only committed when all the three statements executed successfully.


Close connection

And finally, remember to close the connection to disconnect from the database system correctly:

Java Code: Close database connection
        try {
            conn.close();
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();           
        }

Submit "Working with JDBC in Java" to Facebook Submit "Working with JDBC in Java" to Digg Submit "Working with JDBC in Java" to del.icio.us Submit "Working with JDBC in Java" to StumbleUpon Submit "Working with JDBC in Java" to Google

Comments