PreparedStatement Example in Java
By: Tamil Selvan in JDBC Tutorials on 2007-10-12
Sometimes it is more convenient to use a
object for sending SQL statements to the database. This special type of
statement is derived from the more general class,
you already know.
If you want to execute a
Statement object many times, it
normally reduces execution time to use a
The main feature of a
PreparedStatement object is that, unlike
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
object contains not just an SQL statement, but an SQL statement that has been
precompiled. This means that when the
executed, the DBMS can just run the
statement without having to compile it first.
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
Statement objects, you create
objects with a
Connection method. Using our open connection
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 ?");
updateSales now contains the SQL statement,
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
object. You do this by calling one of the
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
so on. In general, there is a
setXXX method for each
primitive type declared in the Java programming language.
setXXX Using the
from the previous example, the following line of code sets the first question
mark placeholder to a Java
int with a value of 75:
setXXX The first argument given to a
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 "
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
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():
executeUpdate was used to execute both the
stmt and the
Notice, however, that no argument is supplied to
when it is used to execute
updateSales. This is true because
already contains the SQL statement to be executed.
Looking at these examples, you might wonder why you would choose to use a
object with parameters instead of just a simple statement, since the simple
statement involves fewer steps. If you were going to update the
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
especially in situations where you can use a
for loop or
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
called. Using the
the following code fragment illustrates reusing a prepared statement after
resetting the value of one of its parameters and leaving the other one the
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")
This policy contains information about your privacy. By posting, you are declaring that you understand this policy:
- Your name, rating, website address, town, country, state and comment will be publicly displayed if entered.
- Aside from the data entered into these form fields, other stored data about your comment will include:
- Your IP address (not displayed)
- The time/date of your submission (displayed)
- Your email address will not be shared. It is collected for only two reasons:
- Administrative purposes, should a need to contact you arise.
- To inform you of new comments, should you subscribe to receive notifications.
- A cookie may be set on your computer. This is used to remember your inputs. It will expire by itself.
This policy is subject to change at any time and without notice.
These terms and conditions contain rules about posting comments. By submitting a comment, you are declaring that you agree with these rules:
- Although the administrator will attempt to moderate comments, it is impossible for every comment to have been moderated at any given time.
- You acknowledge that all comments express the views and opinions of the original author and not those of the administrator.
- You agree not to post any material which is knowingly false, obscene, hateful, threatening, harassing or invasive of a person's privacy.
- The administrator has the right to edit, move or remove any comment for any reason and without notice.
Failure to comply with these rules may result in being banned from submitting further comments.
These terms and conditions are subject to change at any time and without notice.
- Data Science
- React Native
- Cloud Computing
- Java Beans
- Mac OS X
- Office 365
- Tech Reviews
Data Access Technologies in Java
JDBC and Tomcat context settings
TEXT datatype SPLIT in MSSQL - to solve the 8000 limit set by varchar
What is Referential Integrity in databases?
Handling CSV in Stored Procedures
java.lang.NoClassDefFoundError and java.lang.NoSuchMethodError
Calling a Stored Procedure from JDBC in Java
setSavepoint and releaseSavepoint Example in Java
Result Sets, Cursors and Transactions in SQL
Stored Procedures example in SQL
Using the DriverManager Class vs Using a DataSource Object for a connection