Getting and Processing a ResultSet Using a Statement and a PreparedStatement - Java JDBC

Java examples for JDBC:ResultSet

  1. HOME
  2. Java
  3. JDBC
  4. ResultSet

Description

Getting and Processing a ResultSet Using a Statement and a PreparedStatement

Demo Code

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

public class Main {
  private static final SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");

  public static void main(String[] args) {
    Connection conn = null;//from  www.  j  a  va2s .  c  om
    try {
      conn = JDBCUtil.getConnection();

      displayPersonUsingStatement(conn, 101);
      displayPersonUsingStatement(conn, 102);

      displayPersonUsingPreparedStatement(conn, 101);
      displayPersonUsingPreparedStatement(conn, 102);

      JDBCUtil.commit(conn);
    } catch (SQLException e) {
      System.out.println(e.getMessage());
      JDBCUtil.rollback(conn);
    } finally {
      JDBCUtil.closeConnection(conn);
    }
  }

  public static void displayPersonUsingStatement(Connection conn,
      int inputPersonId) throws SQLException {
    String SQL = "select person_id, first_name, last_name, "
        + " gender, dob, income from person " + " where person_id = "
        + inputPersonId;

    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
          ResultSet.CONCUR_UPDATABLE);
      rs = stmt.executeQuery(SQL);
      printResultSet(rs);
    } finally {
      JDBCUtil.closeStatement(stmt);
    }
  }

  public static void displayPersonUsingPreparedStatement(Connection conn,
      int inputPersonId) throws SQLException {

    String SQL = "select person_id, first_name, last_name, "
        + " gender, dob, income from person " + " where person_id = ?";

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = conn.prepareStatement(SQL);

      pstmt.setInt(1, inputPersonId);
      rs = pstmt.executeQuery();
      printResultSet(rs);
    } finally {
      JDBCUtil.closeStatement(pstmt);
    }
  }

  public static void printResultSet(ResultSet rs) throws SQLException {
    while (rs.next()) {
      int personId = rs.getInt("person_id");
      String firstName = rs.getString("first_name");
      String lastName = rs.getString("last_name");
      String gender = rs.getString("gender");
      Date dob = rs.getDate("dob");
      boolean isDobNull = rs.wasNull();

      double income = rs.getDouble("income");
      boolean isIncomeNull = rs.wasNull();

      String formattedDob = null;
      if (!isDobNull) {
        formattedDob = formatDate(dob);
      }

      System.out.print("Person ID:" + personId);
      System.out.print(", First Name:" + firstName);
      System.out.print(", Last Name:" + lastName);
      System.out.print(", Gender:" + gender);

      if (isDobNull) {
        System.out.print(", DOB:null");
      } else {
        System.out.print(", DOB:" + formattedDob);
      }

      if (isIncomeNull) {
        System.out.println(", Income:null");
      } else {
        System.out.println(", Income:" + income);
      }
    }
  }

  public static String formatDate(Date dt) {
    if (dt == null) {
      return "";
    }

    String formattedDate = sdf.format(dt);
    return formattedDate;
  }
}

class JDBCUtil {
  public static Connection getConnection() throws SQLException {
    Driver derbyEmbeddedDriver = null;// new
                                      // org.apache.derby.jdbc.EmbeddedDriver();
    DriverManager.registerDriver(derbyEmbeddedDriver);

    String dbURL = "jdbc:derby:beginningJavaDB;create=true;";
    String userId = "root";
    String password = "password";

    Connection conn = DriverManager.getConnection(dbURL, userId, password);
    conn.setAutoCommit(false);
    return conn;
  }

  public static void closeConnection(Connection conn) {
    try {
      if (conn != null) {
        conn.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void closeStatement(Statement stmt) {
    try {
      if (stmt != null) {
        stmt.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void closeResultSet(ResultSet rs) {
    try {
      if (rs != null) {
        rs.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void commit(Connection conn) {
    try {
      if (conn != null) {
        conn.commit();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void rollback(Connection conn) {
    try {
      if (conn != null) {
        conn.rollback();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void main(String[] args) {
    Connection conn = null;
    try {
      conn = getConnection();
      System.out.println("Connetced to the database.");
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      closeConnection(conn);
    }
  }
}

Related Tutorials