PreparedStatement Example in Java

By: Tamil Selvan Emailed: 1674 times Printed: 2155 times    

Latest comments
By: rohit kumar - how this program is work
By: Kirti - Hi..thx for the hadoop in
By: Spijker - I have altered the code a
By: ali mohammed - why we use the java in ne
By: ali mohammed - why we use the java in ne
By: mizhelle - when I exported the data
By: raul - no output as well, i'm ge
By: Rajesh - thanx very much...
By: Suindu De - Suppose we are executing

Sometimes it is more convenient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement, that you already know.

If you want to execute a Statement object many times, it normally reduces execution time to use a PreparedStatement object instead.

The main feature of a PreparedStatement object is that, unlike a Statement object, it is given an SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.

Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. Examples of this are in the following sections.

Creating a PreparedStatement Object

As with Statement objects, you create PreparedStatement objects with a Connection method. Using our open connection con from previous examples, you might write code such as the following to create a PreparedStatement object that takes two input parameters:

PreparedStatement updateSales = con.prepareStatement(
    "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");

The variable updateSales now contains the SQL statement, "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?", which has also, in most cases, been sent to the DBMS and been precompiled.

Supplying Values for PreparedStatement Parameters

You need to supply values to be used in place of the question mark placeholders (if there are any) before you can execute a PreparedStatement object. You do this by calling one of the setXXX methods defined in the PreparedStatement class. If the value you want to substitute for a question mark is a Java int, you call the method setInt. If the value you want to substitute for a question mark is a Java String, you call the method setString, and so on. In general, there is a setXXX method for each primitive type declared in the Java programming language.

setXXX Using the PreparedStatement object updateSales from the previous example, the following line of code sets the first question mark placeholder to a Java int with a value of 75:

updateSales.setInt(1, 75);

setXXX The first argument given to a setXXX method indicates which question mark placeholder is to be set, and the second argument indicates the value to which it is to be set. The next example sets the second placeholder parameter to the string " Colombian":

updateSales.setString(2, "Colombian");

setXXX After these values have been set for its two input parameters, the SQL statement in updateSales is the equivalent to the SQL statement in the String object updateString that was used in the previous update example. Therefore, the following two code fragments accomplish the same thing:

Code Fragment 1:

String updateString = "UPDATE COFFEES SET SALES = 75 " + 
                      "WHERE COF_NAME LIKE 'Colombian'";
stmt.executeUpdate(updateString);

Code Fragment 2:

PreparedStatement updateSales = con.prepareStatement(
        "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75); 
updateSales.setString(2, "Colombian"); 
updateSales.executeUpdate():

The method executeUpdate was used to execute both the Statement stmt and the PreparedStatement updateSales. Notice, however, that no argument is supplied to executeUpdate when it is used to execute updateSales. This is true because updateSales already contains the SQL statement to be executed.

Looking at these examples, you might wonder why you would choose to use a PreparedStatement object with parameters instead of just a simple statement, since the simple statement involves fewer steps. If you were going to update the SALES column only once or twice, then there would be no need to use an SQL statement with input parameters. If you will be updating often, on the other hand, it might be much easier to use a PreparedStatement object, especially in situations where you can use a for loop or while loop to set a parameter to a succession of values. You will see an example of this later in this section.

Once a parameter has been set with a value, it retains that value until it is reset to another value, or the method clearParameters is called. Using the PreparedStatement object updateSales, the following code fragment illustrates reusing a prepared statement after resetting the value of one of its parameters and leaving the other one the same:

updateSales.setInt(1, 100); 
updateSales.setString(2, "French_Roast"); 
updateSales.executeUpdate(); 
// changes SALES column of French Roast row to 100
updateSales.setString(2, "Espresso");
updateSales.executeUpdate(); 
// changes SALES column of Espresso row to 100 (the first 
// parameter stayed 100, and the second parameter was reset
// to "Espresso")

JDBC Home | All JDBC Tutorials | Latest JDBC Tutorials

Sponsored Links

If this tutorial doesn't answer your question, or you have a specific question, just ask an expert here. Post your question to get a direct answer.



Bookmark and Share

Comments(1)


1. View Comment

Good example but hard to read. Every other word is on a different line, making readability hard for what is otherwise a good article.

View Tutorial          By: Eugene at 2012-03-28 12:19:44

Your name (required):


Your email(required, will not be shown to the public):


Your sites URL (optional):


Your comments:



More Tutorials by Tamil Selvan
Inner Class Example in Java
PreparedStatement Example in Java
UNIX read and write system calls sample program in C
Pointer Arrays and Pointers to Pointers in C
Relational and Logical Operators in C
Arithmetic Operators in C
strlen() sample program in C++
Sample program to draw a arc in J2ME
What Java Has Removed from C++
FileReader and FileWriter example program in Java

More Tutorials in JDBC
TEXT datatype SPLIT in MSSQL - to solve the 8000 limit set by varchar
Import TEXT to TABLE in MSSQL
What is Referential Integrity in databases?
Handling CSV in Stored Procedures
setSavepoint and releaseSavepoint Example in Java
Calling a Stored Procedure from JDBC in Java
java.lang.NoClassDefFoundError and java.lang.NoSuchMethodError
Creating Database Connection Pool in Tomcat 5.0 and Tomcat 5.5 for MySQL and Java
JDBC Basics and JDBC Components
SELECT Statements
WHERE Clauses in SQL
Joins example in SQL
Common SQL Commands
Result Sets, Cursors and Transactions in SQL
Stored Procedures example in SQL

More Latest News
Most Viewed Articles (in JDBC )
A simple JDBC application sample code
How connection pooling works in Java and JDBC
Using JDBC to extract data from a database and output to an XML document
PreparedStatement Example in Java
setSavepoint and releaseSavepoint Example in Java
Using JDBC to connect to MySQL from Java Program
Using the DriverManager Class vs Using a DataSource Object for a connection
What is the ACID principal?
Data Access Technologies in Java
Creating Database Connection Pool in Tomcat 5.0 and Tomcat 5.5 for MySQL and Java
What is JDBC?
JDBC Components
Using Transactions in JDBC
JDBC and Tomcat context settings
SELECT Statements
Most Emailed Articles (in JDBC)
Creating Database Connection Pool in Tomcat 5.0 and Tomcat 5.5 for MySQL and Java
Result Sets, Cursors and Transactions in SQL
Import TEXT to TABLE in MSSQL
Using JDBC to connect to MySQL from Java Program
A simple JDBC application sample code
Data Access Technologies in Java
Common SQL Commands
setSavepoint and releaseSavepoint Example in Java
JDBC Components
The Structure of JDBC
How connection pooling works in Java and JDBC
Using Transactions in JDBC
JDBC Basics and JDBC Components
WHERE Clauses in SQL
Getting Started with JDBC