Using PreparedStatement in Java
by Grace | December 16, 2008 in Java, Sql | No Comments
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
}
