JDBC

In general, to process any SQL statement with JDBC, you follow these steps:

1. Establishing a connection: establish connection with data source you want to use. This is represented by connection object.
2. Create a statement: Statement is an interface that represents a SQL statement.
3 kinds of statement:
- Statement: used to implement simple SQL statements with no parameters.
- PreparedStatement: Used for precompiling SQL statement that might contain input parameters.
- CallableStatement: Used to execute stored procedures that may contain both input and output parameters.
3. Execute the query: Call execute method from Statement such as following:
- execute: returns true if first object that query returns is ResultSet object. Use this method if query could return one or more ResultSet object.
- executeQuery: returns one ResultSet object.
- executeUpdate: Returns an integer representing number of rows affected by SQL statement. Use this is you are using INSERT, UPDATE or DELETE SQL statements.
4. Process the ResultSet object: You access data in ResultSet object through a cursor. Note that this cursor is not a database cursor. Cursor is a pointer that points to one row of data in ResultSet object.
5. Close the connection: When you are finished with statement. call method Statement.close to immediately close resources it is using. When you close this method, its ResultSet objects are closed.

This page use the following method, CoffeeTables.viewTable to demonstrate the steps.
This method outputs the contents of the table COFFEES.

public static void viewTable(Connection con, String dbName)
    throws SQLException {

    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, " +
                   "SALES, TOTAL " +
                   "from " + dbName + ".COFFEES";
    try {
        stmt = con.createStatement();  // 2. Create statement.
        ResultSet rs = stmt.executeQuery(query); // 3. Executing Queries.
        while (rs.next()) { // 4. Processing ResultSet objects.
            String coffeeName = rs.getString("COF_NAME"); // 4. Processing ResultSet objects

            int supplierID = rs.getInt("SUP_ID"); // 4. Processing ResultSet objects

            float price = rs.getFloat("PRICE"); // 4. Processing ResultSet objects

            int sales = rs.getInt("SALES"); // 4. Processing ResultSet objects

            int total = rs.getInt("TOTAL"); // 4. Processing ResultSet objects

            System.out.println(coffeeName + "\t" + supplierID +
                               "\t" + price + "\t" + sales +
                               "\t" + total); // 4. Processing ResultSet objects

        }
    } catch (SQLException e ) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {      
        if (stmt != null) { stmt.close(); } //5. Closing connection
    }
}

Establishing connection:

public Connection getConnection() throws SQLException {

    Connection conn = null;
    Properties connectionProps = new Properties();
    connectionProps.put("user", this.userName);
    connectionProps.put("password", this.password);

    if (this.dbms.equals("mysql")) {
        conn = DriverManager.getConnection(
                   "jdbc:" + this.dbms + "://" +
                   this.serverName +
                   ":" + this.portNumber + "/",
                   connectionProps);
    } else if (this.dbms.equals("derby")) {
        conn = DriverManager.getConnection(
                   "jdbc:" + this.dbms + ":" +
                   this.dbName +
                   ";create=true",
                   connectionProps);
    }
    System.out.println("Connected to database");
    return conn;
}

Handling SQLExceptions:

Retreiving SQLExceptions:

public static void printSQLException(SQLException ex) {

    for (Throwable e : ex) {
        if (e instanceof SQLException) {
            if (ignoreSQLException(
                ((SQLException)e).
                getSQLState()) == false) {

                e.printStackTrace(System.err);
                System.err.println("SQLState: " +
                    ((SQLException)e).getSQLState());

                System.err.println("Error Code: " +
                    ((SQLException)e).getErrorCode());

                System.err.println("Message: " + e.getMessage());

                Throwable t = ex.getCause();
                while(t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

public static boolean ignoreSQLException(String sqlState) {

    if (sqlState == null) {
        System.out.println("The SQL state is not defined!");
        return false;
    }

    // X0Y32: Jar file already exists in schema
    if (sqlState.equalsIgnoreCase("X0Y32"))
        return true;

    // 42Y55: Table already exists in schema
    if (sqlState.equalsIgnoreCase("42Y55"))
        return true;

    return false;
}

Retrieving Warnings:

public static void getWarningsFromResultSet(ResultSet rs)
    throws SQLException {
    JDBCTutorialUtilities.printWarnings(rs.getWarnings());
}

public static void getWarningsFromStatement(Statement stmt)
    throws SQLException {
    JDBCTutorialUtilities.printWarnings(stmt.getWarnings());
}

public static void printWarnings(SQLWarning warning)
    throws SQLException {

    if (warning != null) {
        System.out.println("\n---Warning---\n");

    while (warning != null) {
        System.out.println("Message: " + warning.getMessage());
        System.out.println("SQLState: " + warning.getSQLState());
        System.out.print("Vendor error code: ");
        System.out.println(warning.getErrorCode());
        System.out.println("");
        warning = warning.getNextWarning();
    }
}

Creating Tables with JDBC API:

- public void createTable() throws SQLException {

    String createString =
        "create table " + dbName +
        ".SUPPLIERS " +
        "(SUP_ID integer NOT NULL, " +
        "SUP_NAME varchar(40) NOT NULL, " +
        "STREET varchar(40) NOT NULL, " +
        "CITY varchar(20) NOT NULL, " +
        "STATE char(2) NOT NULL, " +
        "ZIP char(5), " +
        "PRIMARY KEY (SUP_ID))";

    Statement stmt = null;
    try {
        stmt = con.createStatement();
        stmt.executeUpdate(createString);
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}

Populating Tables with JDBC API:

public void populateTable() throws SQLException {

    Statement stmt = null;
    try {
        stmt = con.createStatement();
        stmt.executeUpdate(
            "insert into " + dbName +
            ".SUPPLIERS " +
            "values(49, 'Superior Coffee', " +
            "'1 Party Place', " +
            "'Mendocino', 'CA', '95460')");

        stmt.executeUpdate(
            "insert into " + dbName +
            ".SUPPLIERS " +
            "values(101, 'Acme, Inc.', " +
            "'99 Market Street', " +
            "'Groundsville', 'CA', '95199')");

        stmt.executeUpdate(
            "insert into " + dbName +
            ".SUPPLIERS " +
            "values(150, " +
            "'The High Ground', " +
            "'100 Coffee Lane', " +
            "'Meadows', 'CA', '93966')");
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}

CoffeesTable.populateTable, inserts data into the table:



WebRowSet



A WebRowSet object has all the capabilities of a CachedRowSet object plus it can also do the following:

Write itself as an XML document
Read an XML document that describes a WebRowSet object






JoinRowSet: 



A JoinRowSet object has all the capabilities of a WebRowSet object (and therefore also those of a CachedRowSet object) plus it can also do the following:

Form the equivalent of a SQL JOIN without having to connect to a data source



FilteredRowSet:



A FilteredRowSet object likewise has all the capabilities of a WebRowSet object (and therefore also a CachedRowSet object) plus it can also do the following:

Apply filtering criteria so that only selected data is visible. This is equivalent to executing a query on a RowSet object without having to use a query language or connect to a data source.



All RowSet objects are derived from the ResultSet interface and therefore share its capabilities. What makes JDBC RowSet objects special is that they add these new capabilities:







Using JdbcRowSet objects:





Navigating JdbcRowSet Objects









A JdbcRowSet object can call the method next, and it can also call any of the other ResultSet cursor movement methods. For example, the following lines of code move the cursor to the fourth row in the jdbcRs object and then back to the third row:









jdbcRs.absolute(4);
jdbcRs.previous();









Updating Column values:




Assume that the Coffee Break owner wants to raise the price for a pound of Espresso coffee. If the owner knows that Espresso is in the third row of the jdbcRs object, the code for doing this might look like the following:

jdbcRs.absolute(3);
jdbcRs.updateFloat("PRICE", 10.99f);
jdbcRs.updateRow();



The code moves the cursor to the third row and changes the value for the column PRICE to 10.99, and then updates the database with the new price.



Inserting Rows:


jdbcRs.moveToInsertRow();
jdbcRs.updateString("COF_NAME", "HouseBlend");
jdbcRs.updateInt("SUP_ID", 49);
jdbcRs.updateFloat("PRICE", 7.99f);
jdbcRs.updateInt("SALES", 0);
jdbcRs.updateInt("TOTAL", 0);
jdbcRs.insertRow();

jdbcRs.moveToInsertRow();
jdbcRs.updateString("COF_NAME", "HouseDecaf");
jdbcRs.updateInt("SUP_ID", 49);
jdbcRs.updateFloat("PRICE", 8.99f);
jdbcRs.updateInt("SALES", 0);
jdbcRs.updateInt("TOTAL", 0);
jdbcRs.insertRow();

Deleting Rows:


As is true with updating data and inserting a new row, deleting a row is just the same for a JdbcRowSet object as for a ResultSet object. The owner wants to discontinue selling French Roast decaffeinated coffee, which is the last row in the jdbcRs object. In the following lines of code, the first line moves the cursor to the last row, and the second line deletes the last row from the jdbcRs object and from the database:





jdbcRs.last();
jdbcRs.deleteRow();









Creating JoinRowSet objects:



- 



A JoinRowSet object serves as the holder of a SQL JOIN. The following line of code shows to create a JoinRowSet object:





JoinRowSet jrs = new JoinRowSetImpl();

Adding RowSet objects:

String query =
    "SELECT COFFEES.COF_NAME " +
    "FROM COFFEES, SUPPLIERS " +
    "WHERE SUPPLIERS.SUP_NAME = Acme.Inc. " +
    "and " +
    "SUPPLIERS.SUP_ID = COFFEES.SUP_ID";

coffees = new CachedRowSetImpl();
coffees.setCommand("SELECT * FROM COFFEES");
coffees.setUsername(settings.userName);
coffees.setPassword(settings.password);
coffees.setUrl(settings.urlString);
coffees.execute();

suppliers = new CachedRowSetImpl();
suppliers.setCommand("SELECT * FROM SUPPLIERS");
suppliers.setUsername(settings.userName);
suppliers.setPassword(settings.password);
suppliers.setUrl(settings.urlString);
suppliers.execute(); 


Creating FilteredRowSet objects:

FilteredRowSet frs = new FilteredRowSetImpl();

frs.setCommand("SELECT * FROM COFFEE_HOUSES");
frs.setUsername(settings.userName);
frs.setPassword(settings.password);
frs.setUrl(settings.urlString);

frs.execute();


Testing FilteredRowSet:

public void testFilteredRowSet() {

    FilteredRowSet frs = null;
    StateFilter myStateFilter = new StateFilter(10000, 10999, 1);
    String[] cityArray = { "SF", "LA" };

    CityFilter myCityFilter = new CityFilter(cityArray, 2);

    try {
        frs = new FilteredRowSetImpl();

        frs.setCommand("SELECT * FROM COFFEE_HOUSES");
        frs.setUsername(settings.userName);
        frs.setPassword(settings.password);
        frs.setUrl(settings.urlString);
        frs.execute();

        System.out.println("\nBefore filter:");
        FilteredRowSetSample.viewTable(this.con);

        System.out.println("\nSetting state filter:");
        frs.beforeFirst();
        frs.setFilter(myStateFilter);
        this.viewFilteredRowSet(frs);

        System.out.println("\nSetting city filter:");
        frs.beforeFirst();
        frs.setFilter(myCityFilter);
        this.viewFilteredRowSet(frs);
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    }
}




Inserting or Updating rows:



frs.moveToInsertRow();
frs.updateInt("STORE_ID", 10101);
frs.updateString("CITY", "SF");
frs.updateLong("COF_SALES", 0);
frs.updateLong("MERCH_SALES", 0);
frs.updateLong("TOTAL_SALES", 0);
frs.insertRow();

frs.updateInt("STORE_ID", 33101);
frs.updateString("CITY", "SF");
frs.updateLong("COF_SALES", 0);
frs.updateLong("MERCH_SALES", 0);
frs.updateLong("TOTAL_SALES", 0);
frs.insertRow();
frs.moveToCurrentRow();



Retrieving and Accessing Array Values in ResultSet.

ResultSet rs = stmt.executeQuery(
    "SELECT region_name, zips FROM REGIONS");

while (rs.next()) {
    Array z = rs.getArray("ZIPS");
    String[] zips = (String[])z.getArray();
    for (int i = 0; i < zips.length; i++) {
        if (!ZipCode.isValid(zips[i])) {
            // ...
            // Code to display warning
        }
    }
}




Storing and updating Array Objects:




The following example sets the Array object northEastRegion (created in a previous example) as the second parameter to the PreparedStatement pstmt:

PreparedStatement pstmt = con.prepareStatement(
    "insert into REGIONS (region_name, zips) " + "VALUES (?, ?)");
pstmt.setString(1, "NorthEast");
pstmt.setArray(2, northEastRegion);
pstmt.executeUpdate();