Java PreparedStatement

2022-09-06 10:30:58 浏览数 (1)

大家好，又见面了，我是你们的朋友全栈君。

  • Java PreparedStatement is just like a Statement and it’s part of the Java JDBC Framework. Java PreparedStatement就像一个Statement，它是Java JDBC Framework的一部分。
  • It is used to perform crud operations with Database. 它用于对数据库执行分类操作。
  • PreparedStatement extends the Statement interface. PreparedStatement扩展了Statement接口。
  • PreparedStatement is considered as more secure as it supports parameterized queries. PreparedStatement also prevents SQL Injection attacks. PreparedStatement支持参数​​化查询，因此被认为更安全。 PreparedStatement还可以防止SQL注入攻击。
  • We can obtain an instance of PreparedStatement by calling the prepareStatement(String query) method of Connection as shown below. 我们可以通过调用Connection的prepareStatement（String query）方法来获得PreparedStatement的实例，如下所示。
代码语言：javascript复制
// Method :public PreparedStatement prepareStatement(String query)throws SQLException{}// Usage :Connection con = DriverManager.getConnection ("jdbc:mysql://localhost:3306/customerdb", "root", "root");PreparedStatement ps = con.prepareStatement("select id, firstname, lastname, email, birthdate from tblcustomer");

PreparedStatement的优势 (PreparedStatement Advantages)

  • PreparedStatement can be used for both parameterized query and normal query. PreparedStatement可以用于参数化查询和普通查询。
  • Query Performance of PreparedStatement is better than that of the Statement. PreparedStatement的查询性能优于语句的查询性能。
  • An instance of PreparedStatement can be reused to execute the same query with different parameters. 可以重新使用PreparedStatement实例以执行具有不同参数的同一查询。
  • PreparedStatement saves application from SQL Injection attacks. PreparedStatement使应用程序免受SQL Injection攻击。

Java PreparedStatement Hierarchy Java PreparedStatement层次结构

PreparedStatement方法 (PreparedStatement Methods)

We can divide the methods into different categories.

我们可以将方法分为不同的类别。

1.执行查询 (1. Executing Query)

  • ResultSet executeQuery(): This method is used perform read operation using PreparedStatement object. It returns an instance of ResultSet is used to get data. ResultSet executeQuery（） ：此方法用于通过PreparedStatement对象执行读取操作。 它返回一个ResultSet的实例，用于获取数据。
  • int executeUpdate(): This method is used to execute insert, delete and update queries. It will return an integer value indicating numbers database row affected by the query. int executeUpdate（） ：此方法用于执行插入，删除和更新查询。 它将返回一个整数值，指示受查询影响的数字数据库行。

2.将参数值传递给查询 (2. Passing Parameter values to Query)

All of the below methods have 2 arguments. 1st argument is Parameter Index and 2nd argument is the Value of Parameter.

以下所有方法都有2个参数。 第一个参数是参数索引，第二个参数是参数值。

  1. void setInt(int parameterIndex, int value): This method sets the Integer value to the specified parameter index. void setInt（int parameterIndex，int value） ：此方法将Integer值设置为指定的参数索引。
  2. void setShort(int parameterIndex, short value): This method sets the short value to the specified parameter index. void setShort（int parameterIndex，short value） ：此方法将short值设置为指定的参数索引。
  3. void setLong(int parameterIndex, long value): This method sets the Long value to the specified parameter index. setLong（int parameterIndex，long value） ：此方法将Long值设置为指定的参数索引。
  4. void setFloat(int parameterIndex, float value): This method sets the Float value to the specified parameter index. void setFloat（int parameterIndex，float value） ：此方法将Float值设置为指定的参数索引。
  5. void setDouble(int parameterIndex, double value): This method sets the Double value to the specified parameter index. void setDouble（int parameterIndex，double value） ：此方法将Double值设置为指定的参数索引。
  6. void setBigDecimal(int parameterIndex, BigDecimal value): This method sets the BigDecimal value to the specified parameter index. void setBigDecimal（int parameterIndex，BigDecimal value） ：此方法将BigDecimal值设置为指定的参数索引。
  7. void setString(int parameterIndex, String value): This method sets the String value to the specified parameter index. setString（int parameterIndex，String value） ：此方法将String值设置为指定的参数索引。
  8. void setDate(int parameterIndex, Date value): This method sets the Date value to the specified parameter index. setDate（int parameterIndex，Date value） ：此方法将Date值设置为指定的参数索引。

Note: The parameterIndex value starts from 1 and all of these methods throw SQLException.

注意 ：parameterIndex值从1开始，所有这些方法都抛出SQLException 。

Java PreparedStatement示例 (Java PreparedStatement Example)

We will be using the MySQL database to demonstrate the usage of PreparedSatement. Use below DB scripts to create database, tables and sample data.

我们将使用MySQL数据库来演示PreparedSatement的用法。 使用下面的DB脚本创建数据库，表和示例数据。

代码语言：javascript复制
create database customerdb;

use customerdb;

create table tblcustomer(
    id integer AUTO_INCREMENT primary key,
    firstname varchar(32),
    lastname varchar(32),
    email varchar(32),
    birthdate datetime
);

insert into tblcustomer (id,firstname,lastname,email,birthdate) values(1,'Ricky','Smith','ricky@google.com','2001-12-10');

Database Connection Information:

数据库连接信息：

Name of MySql Database: customerdb IP: localhost Port: 3306 username: root password: root

MySql数据库名称：customerdb IP：本地主机 端口：3306 用户名：root 密码：root

Maven Dependency:

Maven依赖关系 ：

代码语言：javascript复制
<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.48</version>
    </dependency>
</dependencies>

1.使用PreparedStatement获取数据 (1. Get data using PreparedStatement)

In this case, we will fetch the row having specified id from tblcustomer. The Query will return a single row.

在这种情况下，我们将从tblcustomer获取具有指定id的行。 查询将返回单行。

代码语言：javascript复制
package com.journaldev.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class PreparedStatementDemo {
    public static void main(String[] args) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int customerId = 1;
        String query = "select id, firstname, lastname, email,     birthdate from tblcustomer where id = ?";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/customerdb", "root", "root");
            ps = con.prepareStatement(query);
            ps.setInt(1, customerId);
            rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println("Id:"   rs.getInt(1));
                System.out.println("First Name:"   rs.getString(2));
                System.out.println("Last Name:"   rs.getString("lastname"));
                System.out.println("Email:"   rs.getString("email"));
                System.out.println("BirthDate:"   rs.getDate("birthdate"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            rs.close();
            ps.close();
            con.close();
        }
    }
}

Understanding the execution steps:

了解执行步骤：

Step 1: Loading JDBC Driver.

步骤1：加载JDBC驱动程序。

Class.forName(“com.mysql.jdbc.Driver”) loads jdbc driver into memory.

类。 forName （ “ com.mysql.jdbc.Driver” ）将jdbc驱动程序加载到内存中。

Step 2: Now we need to obtain the Connection object. The following line will do it.

步骤2 ：现在我们需要获取Connection对象。 下一行将执行此操作。

DriverManager.getConnection(“<<JDBC Url>>”, “<<Db username>>”, “<<db password>>”);

DriverManager.getConnection（ “ << JDBC Url >>” ， “ << Db用户名>>” ， “ << db密码>>” ）;

Step 3: We can obtain instance of PreparedStatement from Connection object. We need to also specify query that we want to execute. e.g.

步骤3：我们可以从Connection对象获取PreparedStatement的实例。 我们还需要指定要执行的查询。 例如

PreparedSatement ps = con.prepareStatement(<<Query>>);

PreparedSatement ps = con.prepareStatement（ << Query >> ）;

PreparedStatement also supports parameterized query.

PreparedStatement还支持参数化查询。

‘?’ is the parameter in the query. The value of this parameter needs to be provided before executing the Query.

‘？’ 是查询中的参数。 在执行查询之前，需要提供此参数的值。

Step 4: Providing Values of query Parameters. There is only one parameter in the above example i.e. id of type integer.

步骤4：提供查询参数的值。 上面的示例中只有一个参数，即整数类型的id。

int customerId = 1; ps.setInt(1, customerId); setInt(<<Parameter Number>>,<<Parameter Value>) method has 2 argument. In the above example, ‘1’ is parameter number and variable customerId is the value of Parameter.

int customerId = 1; ps.setInt（1，customerId）; setInt（<<参数编号>>，<<参数值>）方法具有2个参数。 在上面的示例中，“ 1”是参数编号，变量customerId是Parameter的值。

Step 5: Executing Query.

步骤5：执行查询。

executeQuery() method of PreparedStatement is used to execute the select query. It will return the instance of ResultSet. If your query if for insert, update or delete purpose then you can use executeUpdate().

PreparedStatement的executeQuery（）方法用于执行选择查询。 它将返回ResultSet的实例。 如果查询用于插入，更新或删除，则可以使用executeUpdate（） 。

Step 6: Iterating ResultSet. next() method of ResultSet is used to obtain query output.

步骤6：迭代ResultSet。 ResultSet的next（）方法用于获取查询输出。

Step 7: Closing Resources: It’s one of the important steps. Many developers forget to close resources like ResultSet, PreparedStatement, and Connection. It will result in Resource leakage which can bring down your application.

步骤7：关闭资源：这是重要的步骤之一。 许多开发人员忘记关闭诸如ResultSet，PreparedStatement和Connection之类的资源。 这将导致资源泄漏，这可能会使您的应用程序崩溃。

Output of Program:

程序输出：

代码语言：javascript复制
Id:1
First Name:Ricky
Last Name:Smith
Email:ricky@google.com
BirthDate:2001-12-1

2.使用PreparedStatement插入操作 (2. Insert Operation using PreparedStatement)

In this example, we will use PreparedStatement to perform insert operation in tblcustomer table.

在此示例中，我们将使用PreparedStatement在tblcustomer表中执行插入操作。

代码语言：javascript复制
package com.journaldev.examples;

import java.sql.*;
import java.text.SimpleDateFormat;

public class PrepareStatementInsertDemo {
    public static void main(String[] args) throws Exception {
        {
            Connection con = null;
            PreparedStatement ps = null;
            ResultSet rs = null;
            String firstname = "matthew";
            String lastname = "wade";
            String email = "matthew@java.com";
            Date birthdate = new Date(new SimpleDateFormat("YYYY-MM-DD").parse("2000-12-12").getTime());
            String query = "insert into tblcustomer (id,firstname,lastname,email,birthdate) values(default,?,?,?,?)";
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/customerdb", "root", "root");
                ps = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, firstname);
                ps.setString(2, lastname);
                ps.setString(3, email);
                ps.setDate(4, birthdate);
                int row = ps.executeUpdate();
                System.out.println("No. of Rows inserted:"   row);
                rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    System.out.println("Id of new Customer:"   rs.getInt(1));
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                rs.close();
                ps.close();
                con.close();
            }
        }
    }
}

In this example, while creating an instance of PreparedStatement, we have passed 2 arguments. 1st is the query itself and 2nd is “Statement.RETURN_GENERATED_KEYS“, which will help us to get the primary key value of the new row.

在此示例中，在创建PreparedStatement实例时，我们传递了2个参数。 第一个是查询本身， 第二个是“语句。 RETURN_GENERATED_KEYS ”，这将帮助我们获取新行的主键值。

The below code is used to provide parameters for Insert Query.

以下代码用于为插入查询提供参数。

代码语言：javascript复制
ps.setString(1, firstname);
ps.setString(2, lastname);
ps.setString(3, email);
ps.setDate(4, birthdate);

As stated in the previous program, executeUpdate() method is used to perform the insert operation. It will return the number of rows affected by our query.

如先前程序中所述，executeUpdate（）方法用于执行插入操作。 它将返回受我们的查询影响的行数。

Output of Program:

程序输出：

代码语言：javascript复制
No. of Rows inserted:1
Id of new Customer:2

If you go to DB and execute a select query then you will see the below result.

如果转到DB并执行选择查询，则将看到以下结果。

代码语言：javascript复制
mysql> use customerdb;

Database changed

mysql> select * from tblcustomer;
 ---- ----------- ---------- ------------------ --------------------- 
| id | firstname | lastname | email            | birthdate           |
 ---- ----------- ---------- ------------------ --------------------- 
|  1 | Ricky     | Smith    | ricky@google.com | 2001-12-10 00:00:00 |
|  2 | matthew   | wade     | matthew@java.com | 1999-12-26 00:00:00 |
 ---- ----------- ---------- ------------------ --------------------- 
2 rows in set (0.00 sec)

3.使用PreparedStatement更新操作 (3. Update Operation using PreparedStatement)

Now we will perform the update operation. We will update the first name and last name of the customer having email “matthew@java.com”. This row was inserted in the previous example.

现在，我们将执行更新操作。 我们将更新电子邮件为“ matthew@java.com”的客户的名字和姓氏。 在上一个示例中插入了该行。

代码语言：javascript复制
package com.journaldev.examples;

import java.sql.*;

public class PrepareStatementUpdateDemo {
    public static void main(String[] args) throws Exception {
        {
            Connection con = null;
            PreparedStatement ps = null;
            String email = "matthew@java.com";
            String newFirstname = "john";
            String newLastname = "smith";
            String query = "update tblcustomer set firstname = ?,lastname =? where email = ?";
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/customerdb", "root", "root");
                ps = con.prepareStatement(query);
                ps.setString(1, newFirstname);
                ps.setString(2, newLastname);
                ps.setString(3, email);
                int row = ps.executeUpdate();
                System.out.println("No. of Rows Updated:"   row);
                if (row == 1) {
                    String selectQuery = "select id,firstname,lastname,email,birthdate from tblcustomer where email=?";
                    try (PreparedStatement selStatement = con.prepareStatement(selectQuery);
                    ) {
                        selStatement.setString(1, email);
                        ResultSet rs = selStatement.executeQuery();
                        if (rs.next()) {
                            System.out.println("Id:"   rs.getInt(1));
                            System.out.println("First Name:"   rs.getString(2));
                            System.out.println("Last Name:"   rs.getString("lastname"));
                            System.out.println("Email:"   rs.getString("email"));
                            System.out.println("BirthDate:"   rs.getDate("birthdate"));
                        }
                        rs.close();
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                ps.close();
                con.close();
            }
        }
    }
}

Understanding the program:

了解程序：

In the above example, we have 3 parameters in the query. 1st is the new first name, 2nd is new last name and 3rd is the email of the customer.

在上面的示例中，我们在查询中有3个参数。 第一个是新的名字， 第二个是新的名字， 第三个是客户的电子邮件。

The below line of code provides value of this parameter to PreparedStatement.

下面的代码行将此参数的值提供给PreparedStatement。

代码语言：javascript复制
ps.setString(1, newFirstname);
ps.setString(2, newLastname);
ps.setString(3, email);

executeUpdate() method is used to execute update query. It will return the number of rows updated by the query.

executeUpdate（）方法用于执行更新查询。 它将返回查询更新的行数。

Output of Program:

程序输出：

代码语言：javascript复制
No. of Rows Updated:1
Id:2
First Name:john
Last Name:smith
Email:matthew@java.com
BirthDate:1999-12-26

You can check the update in the database using the SQL query.

您可以使用SQL查询在数据库中检查更新。

代码语言：javascript复制
mysql> select * from tblcustomer;
 ---- ----------- ---------- ------------------ --------------------- 
| id | firstname | lastname | email            | birthdate           |
 ---- ----------- ---------- ------------------ --------------------- 
|  1 | Ricky     | Smith    | ricky@google.com | 2001-12-10 00:00:00 |
|  2 | john      | smith    | matthew@java.com | 1999-12-26 00:00:00 |
 ---- ----------- ---------- ------------------ --------------------- 
2 rows in set (0.00 sec)

4.使用PreparedStatement删除操作 (4. Delete Operation using PreparedStatement)

Now we will delete customer record having email “matthew@java.com”.

现在，我们将删除电子邮件为“ matthew@java.com”的客户记录。

代码语言：javascript复制
package com.journaldev.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class PrepareStatementDeleteDemo {
    public static void main(String[] args) throws Exception {
        {
            Connection con = null;
            PreparedStatement ps = null;
            String email = "matthew@java.com";
            String query = "delete from tblcustomer where email = ?";
            try {
                Class.forName("com.mysql.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/customerdb", "root", "root");
                ps = con.prepareStatement(query);
                ps.setString(1, email);
                int row = ps.executeUpdate();
                System.out.println("No. of Rows Deleted:"   row);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                ps.close();
                con.close();
            }
        }
    }
}

PreparedStatement中的批处理方法 (Batch Methods in PreparedStatement)

  • void addBatch(): This method used to adds sets of the parameter to this PreparedStatement object’s batch to update multiple rows. void addBatch（） ：此方法用于将参数集添加到此PreparedStatement对象的批处理中以更新多行。
  • int[] executeBatch(): This method executes all the SQL queries from PreparedStatement object’s batch and returns the array of update counts. This method throws a BatchUpdateException if it fails to execute and JDBC driver may or may not continue to process the remaining batch. int [] executeBatch（） ：此方法从PreparedStatement对象的批处理中执行所有SQL查询，并返回更新计数数组。 如果此方法无法执行，并且JDBC驱动程序可能会也可能不会继续处理剩余的批处理，则将抛出BatchUpdateException。

使用PreparedStatement的批量/批量操作 (Batch/Bulk Operation using PreparedStatement)

代码语言：javascript复制
package com.journaldev.examples;import java.sql.*;import java.text.SimpleDateFormat;public class PrepareStatementBatchDemo {    public static void main(String[] args) throws Exception {        {            Connection con = null;            PreparedStatement ps = null;            ResultSet rs = null;            SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-DD");            String query = "insert into tblcustomer (id,firstname,lastname,email,birthdate) values(default,?,?,?,?)";            try {                Class.forName("com.mysql.jdbc.Driver");                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/customerdb", "root", "root");                ps = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);                // 1st Insert                ps.setString(1, "Ross");                ps.setString(2, "Southee");                ps.setString(3, "ross@java.com");                ps.setDate(4, new Date(sdf.parse("2000-12-12").getTime()));                ps.addBatch();                // 2nd Insert                ps.setString(1, "Mayank");                ps.setString(2, "Kohli");                ps.setString(3, "mayank@java.com");                ps.setDate(4, new Date(sdf.parse("2005-12-12").getTime()));                ps.addBatch();                // 3rd Insert                ps.setString(1, "Tom");                ps.setString(2, "Patel");                ps.setString(3, "tom@java.com");                ps.setDate(4, new Date(sdf.parse("1995-12-12").getTime()));                ps.addBatch();                // Execution                int[] rows = ps.executeBatch();                for (int row : rows) {                    System.out.println("No. of Rows inserted:"   row);                }                rs = ps.getGeneratedKeys();                while (rs.next()) {                    System.out.println("Id of new Customer:"   rs.getInt(1));                }            } catch (Exception e) {                e.printStackTrace();            } finally {                rs.close();                ps.close();                con.close();            }        }    }}

In the above example, we have inserted 3 records of customers in one batch. It is more effective to insert multiple rows in batch instead of single-row. The addBatch() method adds data in a batch. The executeBatch() executes all the queries in the batch.

在上面的示例中，我们分批插入了3个客户记录。 批量插入多行而不是单行更为有效。 addBatch（）方法可批量添加数据。 executeBatch（）执行批处理中的所有查询。

Output:

输出：

代码语言：javascript复制
No. of Rows inserted:1
No. of Rows inserted:1
No. of Rows inserted:1
Id of new Customer:10
Id of new Customer:11
Id of new Customer:12

this link. 链接下载完整的Java项目。

Reference: Java doc

参考 ： Java文档

翻译自: https://www.journaldev.com/37814/java-preparedstatement

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/134720.html原文链接：https://javaforall.cn

linux 批量计算 java sql 数据库

0 人点赞