Monday, March 30, 2009

Java : Simple Example of PreparedStatement

Prepared statements are the ability to set up a statement once, and then execute it many times with different parameters. They are designed to replace building ad hoc query strings, and do so in a more secure and efficient manner. A typical prepared statement would look something like:

SELECT * FROM Employee WHERE emp_id = ?

The '?' is what is a called a placeholder. When you execute the above query, you would need to supply the value for it, which would replace the '?' in the query bove.

Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement object to increase efficiency.

//code for

import java.sql.*;
public class PreparedStatementDemo
public static void main(String[] args) throws Exception
Connection con;

PreparedStatement ps;

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//Loading Drivers

con=DriverManager.getConnection("jdbc:odbc:oracledsn","scott","tiger");//Making Connection

ps=con.prepareStatement("SELECT * FROM Employee where emp_id=?");//this is prepare statement
//observe the '?'

ps.setInt(1,11);//we can also use setString("String"); for checking String

ResultSet rset=ps.executeQuery();//Execute the Prepared Statement

while( through the ResultSet
System.out.println(rset.getInt(1)+" "+rset.getString(2));

ps.close();//Closeing Connection and PreparedStatement