PreparedStatement
In this chapter you will learn:
What is PreparedStatement
The PreparedStatement interface extends
the Statement interface.
PreparedStatement can accept arguments dynamically.
When your SQL query is parameterized,
you should use a PreparedStatement object.
A PreparedStatement object enables you to
pass input parameters to the SQL statement
before sending it to the database server for execution.
PreparedStatement pstmt = null;/* jav a 2 s . c o m*/
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
. . .
}
catch (SQLException e) {
. . .
}
finally {
. . .
}
The following code is a runnable and showing how to
use PreparedStatement.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
/*from ja va 2 s . c o m*/
public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
conn.setAutoCommit(false);
Statement st = conn.createStatement();
PreparedStatement pstmt = conn.prepareStatement("create table survey (id int, name VARCHAR(30) );");
pstmt.executeUpdate();
String INSERT_RECORD = "insert into survey(id) values(?)";
pstmt = conn.prepareStatement(INSERT_RECORD);
pstmt.setString(1, "1");
pstmt.executeUpdate();
ResultSet rs = st.executeQuery("SELECT * FROM survey");
outputResultSet(rs);
pstmt.setString(1, "2");
pstmt.executeUpdate();
rs = st.executeQuery("SELECT * FROM survey");
outputResultSet(rs);
rs.close();
st.close();
conn.close();
}
private static void outputResultSet(ResultSet rs) throws Exception {
ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();
for (int i = 1; i < numberOfColumns + 1; i++) {
String columnName = rsMetaData.getColumnName(i);
System.out.print(columnName + " ");
}
System.out.println();
System.out.println("----------------------");
while (rs.next()) {
for (int i = 1; i < numberOfColumns + 1; i++) {
System.out.print(rs.getString(i) + " ");
}
System.out.println();
}
}
private static Connection getConnection() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
String url = "jdbc:hsqldb:mem:data/tutorial";
return DriverManager.getConnection(url, "sa", "");
}
}
The code above generates the following result.
Parameter marker
All parameters in JDBC are represented by the ?.
? is called the parameter marker.
Every parameter must have a value before executing the SQL statement.
We can use setXXX() methods to bind values to the parameters,
Each parameter marker is referred to by its ordinal position starting from 1, which is different from the starting value for an Java array.
Closing PreparedStatement Obeject
It is important to close the PreparedStatement
object in order to save system resource.
Closing Connection object will cause to close the PreparedStatement object,
but we should always explicitly close the PreparedStatement object.
PreparedStatement pstmt = null;/*from j ava 2 s . c o m*/
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
. . .
}
catch (SQLException e) {
. . .
}
finally {
pstmt.close();
}
Next chapter...
What you will learn in the next chapter: