JDBC Prepared Statements

Intro

A PreparedStatement forces a SQL statement to be set to the database immediately where it will be precompiled. This means that for subsequent calls to the SQL statement can just run the PreparedStatements' SQL statement without having to compile it with every call.
A PreparedStatement is most useful when using a SQL statement that accepts parameters that may change with every call. This short tutorial is going to demonstrate this technique using a MySQL database.

An Example Method

Below is a method that sends a PreparedStatement to the database with two parameters: "journalId" and "userId". Notice that it calls the getConnection() method from a class instance called dataUtil. We'll talk about this class in a second for completeness.

public ResultSet viewItem (int journalId, int userId) throws SQLException
{
	ResultSet rs = null;
	String sql = "SELECT journal_id, date_added, journal_text FROM journal
		WHERE journal_id = ? AND user_id = ?";
	Connection conn = dataUtil.getConnection();
	PreparedStatement preparedStatement = conn.prepareStatement(sql);
	preparedStatement.setInt(1, journalId);
	preparedStatement.setInt(2, userId);
	rs = preparedStatement.executeQuery();
	return rs;
}

The Connection

As I mentioned above, I wanted to show you how to use the getConnection method, cause after all, if you can't connect to a database you can't execute a PreparedStatement. It will return an open Connection object for our PreparedStatement to use. The SERVER and DATABASE constants you would replace with your server address and database name, and the USER and PASS constants would obviously be your database username and password. This method requires the MySQL Connector/J JDBC driver. You can grab this at http://mmmysql.sourceforge.net/.

public Connection getConnection()
{
	try
	{
		Class.forName("org.gjt.mm.mysql.Driver");
		Properties connProp = new Properties();
		connProp.put ("user", USER);
		connProp.put ("password", PASS);
		connProp.put ("useUnicode", "false");

		Connection con = DriverManager.getConnection("jdbc:mysql://" + SERVER
			+ "/" + DATABASE, connProp);
		return con;
	}
	catch (ClassNotFoundException cnf)
	{
		//out.println("Class not found " + cnf);
		return null;
	}
	catch (SQLException ex)
	{
		return null;
	}
}

The Statement

Notice that we first declare a variable to represent the SQL statement, and assign the SQL to it:

String sql = "SELECT journal_id, date_added, journal_text FROM journal
		WHERE journal_id = ? AND user_id = ?";

The values that we will be sending to the database are represented by "?". Next we call our getConnection() method to get our connection object and then define our PreparedStatement, and then append our parameters to the PreparedStatement with these lines.

preparedStatement.setInt(1, journalId);
preparedStatement.setInt(2, userId);

Notice that you should use the correct set method depending on the datatype of the field you are assigning the value to (see References below). All that's left to do is to execute the PreparedStatement and to return the resultset.

References

http://java.sun.com/j2se/1.3/docs/api/java/sql/PreparedStatement.html