Using Prepared Statements
来源:互联网 发布:广州易娱网络 编辑:程序博客网 时间:2024/06/06 00:47
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 advantageto this is that in most cases, this SQL statement is sent to the DBMSright 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 usedfor SQL statements with no parameters, you probably use them most oftenfor SQL statements that take parameters. The advantage of using SQLstatements that take parameters is that you can use the same statementand supply it with different values each time you execute it. Examplesof 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.
setXXXUsing 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);
setXXXThe first argument given to a setXXX
method indicates which question mark placeholder is to be set, and thesecond argument indicates the value to which it is to be set. The nextexample sets the second placeholder parameter to the string "Colombian
":
updateSales.setString(2, "Colombian");
setXXXAfter 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 thesimple 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 SQLstatement with input parameters. If you will be updating often, on theother 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 statementafter resetting the value of one of its parameters and leaving theother 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")
Using a Loop to Set Values
You can often make coding easier by using a for
loop or a while
loop to set values for input parameters.
The code fragment that follows demonstrates using a for
loop to set values for parameters in the PreparedStatement
object updateSales
. The array salesForWeek
holds the weekly sales amounts. These sales amounts correspond to the coffee names listed in the array coffees
, so that the first amount in salesForWeek
(175) applies to the first coffee name in coffees
("Colombian
"), the second amount in salesForWeek
(150) applies to the second coffee name in coffees
("French_Roast
"), and so on. This code fragment demonstrates updating the SALES
column for all the coffees in the table COFFEES
:
PreparedStatement updateSales;
String updateString = "update COFFEES " +
"set SALES = ? where COF_NAME like ?";
updateSales = con.prepareStatement(updateString);
int [] salesForWeek = {175, 150, 60, 155, 90};
String [] coffees = {"Colombian", "French_Roast", "Espresso",
"Colombian_Decaf", "French_Roast_Decaf"};
int len = coffees.length;
for(int i = 0; i < len; i++) {
updateSales.setInt(1, salesForWeek[i]);
updateSales.setString(2, coffees[i]);
updateSales.executeUpdate();
}
When the proprietor wants to update the sales amounts for the nextweek, he can use this same code as a template. All he has to do isenter the new sales amounts in the proper order in the array salesForWeek
. The coffee names in the array coffees
remain constant, so they do not need to be changed. (In a realapplication, the values would probably be input from the user ratherthan from an initialized Java array.)
Return Values for the executeUpdate
Method
Whereas executeQuery
returns a ResultSet
object containing the results of the query sent to the DBMS, the return value for executeUpdate
is an int
that indicates how many rows of a table were updated. For instance, the following code shows the return value of executeUpdate
being assigned to the variable n
:updateSales.setInt(1, 50);
updateSales.setString(2, "Espresso");
int n = updateSales.executeUpdate();
// n = 1 because one row had a change in it
The table COFFEES
was updated by having the value 50
replace the value in the column SALES
in the row for Espresso
. That update affected one row in the table, so n
is equal to 1
.
When the method executeUpdate
is used to execute a DDL statement, such as in creating a table, it returns the int
0
. Consequently, in the following code fragment, which executes the DDL statement used to create the table COFFEES
, n
is assigned a value of 0
:
int n = executeUpdate(createTableCoffees); // n = 0
Note that when the return value for executeUpdate
is 0
, it can mean one of two things:
- the statement executed was an update statement that affected zero rows
- the statement executed was a DDL statement.
- Using Prepared Statements
- Using Prepared Statements
- Using ext/mysqli: Part I - Overview and Prepared Statements
- Prepared Statements insert使用
- mysql Prepared Statements
- MySQL中的Prepared Statements
- MySQL Prepared Statements 使用示例
- SQL预处理语句(Prepared Statements)
- SQL预处理语句(Prepared Statements)
- SQL预处理语句(Prepared Statements)
- SQlite数据库的C编程接口(三) 预处理语句(Prepared Statements) ——《Using SQlite》读书笔记 .
- SQlite数据库的C编程接口(三) 预处理语句(Prepared Statements) ——《Using SQlite》读书笔记 .
- SQlite数据库的C编程接口(三) 预处理语句(Prepared Statements) ——《Using SQlite》读书笔记 .
- SQlite数据库的C编程接口(三) 预处理语句(Prepared Statements) ——《Using SQlite》读书笔记 .
- mysql Prepared Statements 原理介绍与优点
- 12.3.3.17 Using statements
- Re-factoring some internals of prepared statements in 5.7
- mysql sql-syntax-prepared-statements 变量做表名&& 字符函数
- Tomcat在Linux操作系统下安装的方法
- Linux下JDK, Tomcat, MySQL的配置
- Linux下Jdk与Tomcat的安装方法
- 关于B/S和C/S架构的探析
- WinAPI: GetRegionData
- Using Prepared Statements
- dwr例子6调用返回List、Set或者Map的java方法
- 推荐100首值得听的英文歌【ZT】
- 我也谈谈“理工大学杀人案”,达尔闻的进化论真伟大
- 常见数据库Sql Server,Oracle和MySQL的分页语句
- 实现无刷新三级联动菜单[vs2005与ajaxpro]
- 安装xml数据库Xindice1.1
- 自定义空间
- 运行一个 DOS 命令,并等待其结束