View RSS Feed

My Java Tips

Using PreparedStatement

Rate this Entry
by , 11-12-2011 at 06:36 PM (2183 Views)
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:
Java Code:
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.

Java Code:
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.

Java Code:
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"));
		}
Its your turn now to practice it. Try to use PreparedStatement in your applications to make coding simpler and flexible.

Submit "Using PreparedStatement" to Facebook Submit "Using PreparedStatement" to Digg Submit "Using PreparedStatement" to del.icio.us Submit "Using PreparedStatement" to StumbleUpon Submit "Using PreparedStatement" to Google

Categories
JDBC

Comments