JDBC Database Access Examples

This article provides a list of key examples which we would commonly encounter when implementing a Java application requiring database access. For purpose of these examples the assumption is that PostgreSQL database is already setup and necessary database driver is downloaded and added to the CLASSPATH. Refer to this article JDBC Quick Start for a quick introduction to get started.

Create Table using JDBC

Let us consider a simple car loan EMI table with 3 columns EMI Number, EMI Amount and Interest Amount. To create this table on a PostgreSQL database we would do the following. The Statement object is used for executing a static SQL statement.

public static void createTable(Connection connection) {
  try ( Statement stmt = connection.createStatement(); ) {
    String sql = "CREATE TABLE car_loan_emi (\r\n" + 
          "    emi_number      numeric(2) PRIMARY KEY,\r\n" + 
          "    emi_amount      integer,\r\n" + 
          "    interest_amount integer\r\n" + 
          ");";
    stmt.execute(sql);   
  } catch (SQLException e) {
    e.printStackTrace();
  } 
}

Insert records using JDBC

Let us consider the case where we need to populate our car loan EMI table with the values for 12 months. First let us consider the simple case of inserting a record in the table.

public static void insertSingle(Connection connection) {
  try ( Statement stmt = connection.createStatement(); ) {
    String sql = "INSERT INTO car_loan_emi values(1, '10000', '990');";
    stmt.execute(sql);   
  } catch (SQLException e) {
    e.printStackTrace();
  } 
}

Let us say we want to insert EMI values for 12 months into the car loan EMI table. In this case we can leverage PreparedStatement object for sending SQL queries to the database. The advantage of using PreparedStatement is that we use the same object and we supply it with different values when it is executed.

public static void insertMultiple(Connection connection) {
  String sql = "INSERT INTO car_loan_emi values(?, ?, ?);";
  try {
    connection.setAutoCommit(false);
  } catch (SQLException e2) {
    e2.printStackTrace();
  }
  try ( PreparedStatement stmt = connection.prepareStatement(sql); ) {
    for ( int month = 1; month <= 12; month++ ) {
      stmt.setInt(1, month);
      stmt.setInt(2, 10000);
      // ignore this formula
      int interest = (int)((100000 - ((month - 1) * 10000)) * 0.12);
      stmt.setInt(3, interest);
      stmt.executeUpdate();
    }
    connection.commit();
  } catch (SQLException e) {
    e.printStackTrace();
  } finally {
    try {
      connection.setAutoCommit(true);
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

Select records using JDBC

To select records from the car loan EMI table we use a ResultSet which is usually generated by querying the database with a Statement. The ResultSet object provides getter methods for retrieving the values.

public static void selectRecords(Connection connection) {
  try ( Statement stmt = connection.createStatement(); ) {
    ResultSet rs = stmt.executeQuery("SELECT * from car_loan_emi");
    while ( rs.next() ) {
      System.out.println(rs.getInt(1)); 
      System.out.println(rs.getInt(2));
      System.out.println(rs.getInt(3));
    }
  } catch (SQLException e) {
    e.printStackTrace();
  } 
}

Update records using JDBC

To update records in a table we can either execute an SQL statement (or) update using ResultSet. There are 3 types of ResultSet types.

1. TYPE_FORWARD_ONLY: The result set cannot be scrolled and the cursor can be moved forward only.
2. TYPE_SCROLL_INSENSITIVE: The result set can be scrolled and the cursor can be moved both forward and backward. The result set is insensitive to changes made in the underlying data source.
3. TYPE_SCROLL_SENSITIVE: The result set can be scrolled and the cursor can be moved both forward and backward. The result set reflects the changes made in the underlying data source.

We update the car loan EMI table for a specific month using SQL query.

public static void updateRows(Connection connection) {
  try ( Statement stmt = connection.createStatement(); ) {
    String sql = "UPDATE car_loan_emi SET emi_amount = 9000 WHERE emi_number = 6;"; 
    stmt.execute(sql);
  } catch (SQLException e) {
    e.printStackTrace();
  } 
}

We update the car loan EMI table for a specific month using the result set.

public static void updateRows(Connection connection) {
  try ( Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
              ResultSet.CONCUR_UPDATABLE); ) {
    String sql = "SELECT emi_number, emi_amount from car_loan_emi WHERE emi_number = 6;"; 
    ResultSet rs = stmt.executeQuery(sql);
    while ( rs.next() ) {
      int value = rs.getInt("emi_amount");    
      rs.updateInt("emi_amount", value - 1000);
      rs.updateRow();
    }
  } catch (SQLException e) {
    e.printStackTrace();
  } 
}