Using PreparedStatement

22 December 2007

SQL statements are executed on a database tables using Statement object. If the sql statement takes parameter, then it is a wise decision to use PreparedStatement as they are more flexible and makes coding easier.

Creating SQL statements that take parameters provides flexible statements to work with. We can use the same statement and supply it with different values each time we execute it.

We use place holders (question marks) in the query, which are supplied values to make queries flexible. Values are supplied using setXx(…) methods for example:

void setDouble(int parameterIndex, double x)
void setFloat(int parameterIndex, float x)
void setInt(int parameterIndex, int x)
void setLong(int parameterIndex, long x)
…

One has to specify the index as well while setting values. If we miss a parameter or supply an extra parameter, then org.postgresql.util.PSQLException is thrown.

Time for an example. I will connect to Postgres database and will display the contents of a table. Then I will use PreparedStatement statement to update a record and for confirmation, I will again display the contents of the table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
 
 
public class Db {
 
	static String dbname = "testdb";
	static String dbuser = "postgres";
	static String dbpass = "postgres";
	static String dbhost = "localhost";
	static String dbport = "5432";
	static String dbtable = "languages";
 
	public static void main(String[] args) throws Exception {
		Connection conn;
 
		Class.forName("org.postgresql.Driver").newInstance();
		String url = "jdbc:postgresql://" + dbhost + ":" + dbport + "/" + dbname;
		System.out.println("getConnection: url="+url);
		conn = DriverManager.getConnection(url, dbuser, dbpass);
		Statement s = conn.createStatement();
		String query = "select * from programming";
		ResultSet rs;
 
		System.out.println("Before update.");
		rs = s.executeQuery(query);
		while(rs.next())
		{
			System.out.print(rs.getString("name"));
			System.out.println(" - " + rs.getString("comments"));
		}
 
		PreparedStatement updateSales = conn.prepareStatement("UPDATE programming 
                SET comments = ? WHERE name LIKE ? ");
		updateSales.setString(1, "Mustang - Added"); 
		updateSales.setString(2, "Java"); 
                updateSales.executeUpdate();
 
               	System.out.println("After update.");
		rs = s.executeQuery(query);
		while(rs.next())
		{
			System.out.print(rs.getString("name"));
			System.out.println(" - " + rs.getString("comments"));
		}
 
 
 
 
		}
 
}

Output:

getConnection: url=jdbc:postgresql://localhost:5432/testdb
Before update.
C++ - Borland
VB - Microsoft
Java - Mustang
Aater update.
C++ - Borland
VB - Microsoft
Java - Mustang – Added

Lets use PreparedStatement with simple select queries.

PreparedStatement ps = conn.prepareStatement("select * from programming 
                                 where name LIKE ?");
ps.setString(1, "Java");
 
		ResultSet rs = ps.executeQuery();
		while(rs.next())
		{
			System.out.print(rs.getString("name"));
			System.out.println(" - " + rs.getString("comments"));
		}