Site Search:

Submit queries and read results from the database including creating statements, returning result sets, iterating through the results, and properly closing result sets, statements, and connections

Back>




The following program demonstrates how to connect to a database using DriverManager, then create a Statement from the connection, finally get the ResultSet or updateCount by calling executeQuery, executeUpdate and execute.

Notice calling Connection, Statement, ResultSet related methods throws SQLException. The try-with-resources block makes sure the Connection, Statement and ResultSet in the program are always closed.

  • Closing a Connection also closes the Statement and ResultSet.
  • Closing a statement also closes the ResultSet.
There are 3 ways to execute a sql statement.
  1. When you run sql SELECT, the preferred way is to call stmt.executeQuery, which returns a ResultSet.
  2. When you run sql DELETE, INSERT, UPDATE, the preferred way is to call stmt.executeUpdate, which returns an int indicating how many rows are effected.
  3. stmt.execute can run SELECT, DELETE, INSERT and UPDATE, it returns a boolean indicating the whether or not a ResultSet can be accessed. You then call stmt.getResultSet or stmt.getUpdateCount to further process the result.
You call while(rs.next()){...} to integrate through the ResultSet.  Note the column index is started from 1, the column can be referred either by name or column index.



package derby;
import java.sql.*;
public class DerbyDb {
    
    /*
     * Download and unzip latest Derby http://db.apache.org/derby/derby_downloads.html
     * Set classpath to point to derby.jar
     * javac -cp "/Users/homenetwork/Downloads/db-derby-10.13.1.1-lib/lib/derby.jar:." DerbyDb.java
     * java -cp "/Users/homenetwork/Downloads/db-derby-10.13.1.1-lib/lib/derby.jar:." DerbyDb
     */
    static final String URL = "jdbc:derby:social;create=true;";
    public static void main(String...args) throws SQLException {
        try(Connection conn = DriverManager.getConnection(URL);
                Statement stmt = conn.createStatement();){
            //stmt.executeUpdate("Drop table people");
            int r = stmt.executeUpdate("create table people ("
                    + "name VARCHAR(255),"
                    + "age INTEGER,"
                    + "emailAddress VARCHAR(255),"
                    + "gender varchar(255))");
            System.out.println(r);
            
            r = stmt.executeUpdate("insert into people values ('Ted', 16, 'ted@email.com', 'MALE')");
            System.out.println(r);
            r = stmt.executeUpdate("insert into people values ('Tedy', 16, 'tedy@email.com', 'MALE')");
            System.out.println(r);
            ResultSet rs = stmt.executeQuery("select * from people");
            
            while(rs.next()) {
                System.out.println("name = " + rs.getString(1));
                int age = rs.getInt(2);
                System.out.println(age);
                String email = (String)rs.getObject("emailAddress");
                System.out.println(email);
                String gender = rs.getString("gender");
                System.out.println(gender);
            }
            System.out.println();
            sql(stmt, "insert into people values ('Min', 36, 'min@email.com', 'MALE')");
            sql(stmt, "select * from people");
            stmt.executeUpdate("Drop table people");
        }
    }
    
    static void sql(Statement stmt, String sql) throws SQLException {
        boolean isResultSet = stmt.execute(sql);
        if(isResultSet) {
            ResultSet rs = stmt.getResultSet();
            while(rs.next()) {
                System.out.println("name = " + rs.getString(1));
                int age = rs.getInt(2);
                System.out.println(age);
                String email = (String)rs.getObject("emailAddress");
                System.out.println(email);
                String gender = rs.getString("gender");
                System.out.println(gender);
            }
            rs.close(); //closing Statement also closes ResultSet
        } else {
            int r = stmt.getUpdateCount();
            System.out.println(r);
        }
    }

}



Besides rs.getInt, rs.getString and rs.getObject, there are other methods: rs.getBoolean, getDate, getDouble, getLong, getTime and getTimeStamp. Please check the java documents for their corresponding usage.

Improved version




Finally,

  • OCPJP test DriverManager, which is not a preferred way in workplace. DataSource is preferred way. Most of time, DAO layer is managed with frameworks like ibatis, mybais, Hibernate, JPA etc.
  • OCPJP test Statement, which is not a preferred way in workplace. PreparedStatement is preferred way.