Victor Chen

PreparedStatements are used in Java to set up SQL statements. They can be used to insert a set of data to the database or retrieve a set of data from the database.  You can visit the Java API here:  http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html

This is how I’ve used the prepared statement to insert data to the database:

First, import the necessary package:

import java.sql.*;

Create the PreparedStatement object:

PreparedStatement pstmt = null;

Create a  String for your SQL statement. For example:

String insert = "INSERT INTO MYTABLE (COL1, COL2, COL3) VALUES (?, ?, ?)";

The question marks represent values being passed into the statement.

Create a Connection to the database. Usually the URL, username and password are stored in a config file but for simplicity, the following can be done:

Connection con = DriverManager.getConnection("<URL>", "<username>", "<password>");

The URL is something like “jdbc:odbc:<db>”. Username is the schema name, and password is the password.

Then, in a try/catch block,

try (
  pstmt = con.prepareStatement(insert);
  pstmt.setString(1, valCol1);  //valCol1 is the String value being inserted for COL1 in the table MYTABLE
  pstmt.setString(2, valCo2);
  pstmt.setString(3, valCo3);
  pstmt.executeQuery();  //executeQuery() is the method used to run the SQL statement
  con.commit();  //commit() will commit the data into MYTABLE
} catch (SQLException e){
  error("Error inserting into db " + e.getMessage());
}

To retrieve data from the db is similar:

This time, you will need to create a ResultSet object to store the retrieved data:

ResultSet rs = null;

This is an example:

public String[] getData(Connection con, String col3) {
  String select = "SELECT COL1, COL2 FROM MYTABLE WHERE COL3 = ?";
  PreparedStatement ps = null;
  ResultSet rs = null;
  List<String> dataArray = new ArrayList<String>(); //This will contain the data from the db
  try{
    ps = con.prepareStatement(select);
    ps.setString(1,  col3); // col3 is the value being set in the SQL statement
    rs = ps.executeQuery(); //this will execute the query and store the result in rs
    while(rs.next()) {
      dataArray.add(rs.getString(1));
    }
  } catch(SQLException e) {
    error("Unable to get data " , e);
  }
  ps.close()  //close the prepared statement
  con.close()  //close the connection
}