PostgreSQL PREPARE语句与JDBC中PreparedStatement语句关系_postgres jdbc preparedstatement-优快云博客

本文链接：https://blog.youkuaiyun.com/zhu4674548/article/details/82920122

1. PostgreSQL(PG) PREPARE 语句

qingping=> \h prepare
Command:     PREPARE
Description: prepare a statement for execution
Syntax:
PREPARE name [ ( data_type [, ...] ) ] AS statement

qingping=> prepare test_prepare as select id, name, salary from tt where id = $1;
PREPARE
qingping=> execute test_prepare(1);
 id | name  |  salary  
----+-------+----------
  1 | David | 15000.00
(1 row)


qingping=> select * from pg_prepared_statements ;
     name     |                               statement                                |         prepare_time         | 
parameter_types | from_sql 
--------------+------------------------------------------------------------------------+------------------------------+-
----------------+----------
 test_prepare | prepare test_prepare as select id, name, salary from tt where id = $1; | 2018-10-01 12:01:09.13746-04 | 
{integer}       | t
(1 row)

1.1描述

PREPARE创建一个预备语句。预备语句是一种服务器端对象，它可以被用来优化性能。当PREPARE语句被执行时，指定的语句会被解析、分析并且重写。当后续发出一个EXECUTE命令时，该预备语句会被规划并且执行。这种工作的划分避免了重复性的解析分析工作，不过允许执行计划依赖所提供的特定参数值。

预备语句可以接受参数：在执行时会被替换到语句中的值。在创建预备语句时，可以用位置引用参数，如$1、$2等。也可以选择性地指定参数数据类型的一个列表。当一个参数的数据类型没有被指定或者被声明为unknown时，其类型会从该参数被使用的环境中推知（如果可能）。在执行该语句时，在EXECUTE语句中为这些参数指定实际值。更多有关于此的信息可参考EXECUTE。

预备语句只在当前数据库会话期间存在。当会话结束时，预备语句会消失，因此在重新使用之前必须重新建立它。这也意味着一个预备语句不能被多个数据库客户端同时使用。不过，每一个客户端可以创建它们自己的预备语句来使用。预备语句可以用DEALLOCATE命令手工清除。

当一个会话要执行大量类似语句时，预备语句可能会有最大性能优势。如果该语句很复杂（难于规划或重写），例如，如果查询涉及很多表的连接或者要求应用多个规则，性能差异将会特别明显。如果语句相对比较容易规划和重写，但是执行起来开销相对较大，那么预备语句的性能优势就不那么显著了。

1.2参数

name
给这个特定预备语句的任意名称。它在一个会话中必须唯一并且后续将被用来执行或者清除一个之前准备好的语句。

data_type
预备语句一个参数的数据类型。如果一个特定参数的数据类型没有被指定或者被指定为unknown，将从该参数被使用的环境中推得。要在预备语句本身中引用参数，可以使用 $1、$2等。

statement
任何SELECT、INSERT、UPDATE、DELETE或者VALUES语句。

1.3注解

对每一组提供的EXECUTE值，预备语句可以使用通用计划而不是重新做计划。对于没有参数的预备语句马上就会这样做，否则只有五次或者更多次执行产生的计划的估计代价平均值（包括规划开销）比通用计划的代价估计更昂贵时才会这样做。一旦选中一个通用计划，在该预备语句剩余的生存时间内都将使用它。使用在重复值很多的列中很少出现的EXECUTE值可以产生比通用计划更加廉价的定制计划（即使加上规划开销），这样通用计划将不会被使用。

2. JAVA中的PreparedStatement语句

2.1 Example

[********@localhost Java]$ cat ConnectPostgres.java
/*
 * Connect to PostgreSQL by JDBC
 * Use preprared statement and execute the query
 */
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
  
public class ConnectPostgres {  
    private static String driver = "org.postgresql.Driver";
    private static String url ="jdbc:postgresql://192.168.56.26:5432/qingping";
    private static String user = "qingping";
    private static String password = "Gauss@123";

    static {
        try {
            //Load the driver
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.err.println("Load PostgreSQL Driver failed");
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection con = null;
        try {
            con = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (con != null) {
            // Success!
            System.out.println("getConnection succeed");
        }
        else {
            System.out.println("getConnection failed");
        }
        return con;
    }

    public static void main(String[] args) {
        try {
            Connection conn = getConnection();
            System.out.println("Sleep 60 seconds");
            Thread.sleep(60000);
            PreparedStatement pstmt = conn.prepareStatement("SELECT id, name, salary FROM tt where id = ?"); 
            if (pstmt == null)
                System.out.println("Generate PreparedStatement failed");
            int i = 0;
            while (i < 5) {
                pstmt.setInt(1, i);
                ResultSet rest = pstmt.executeQuery();
                while (rest.next())
                    System.out.println("result data: " + rest.getInt(1) + ", " + rest.getString(2));
                i++;
            }
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2.2 抓取JDBC执行过程中数据包

P...9.SELECT id, name, salary FROM tt where id = $1.......B......................D....P.E...	.....S....1....2....T...K..id...................name................h..salary...................C...
SELECT 0.Z....IP...9.SELECT id, name, salary FROM tt where id = $1.......B......................D....P.E...	.....S....1....2....T...K..id...................name................h..salary...................D... ......1....David....15000.00C...
SELECT 1.Z....IP...9.SELECT id, name, salary FROM tt where id = $1.......B......................D....P.E...	.....S....1....2....T...K..id...................name................h..salary...................D... ......2....Peter....25000.00C...
SELECT 1.Z....IP...9.SELECT id, name, salary FROM tt where id = $1.......B......................D....P.E...	.....S....1....2....T...K..id...................name................h..salary...................C...
SELECT 0.Z....IP...<S_1.SELECT id, name, salary FROM tt where id = $1.......B.....S_1.................D....P.E...	.....S....1....2....T...K..id...................name................h..salary...................C...
SELECT 0.Z....IX....

通过数据包可以看出JDBC中PreparedStatement是通过调用数据库中PREPARRE语句来实现的，PREPARE语句仅在当前会话(Session)生效，断开后需要重新创建，PREPARE语句可以减少词法／语法／重写等工作，当执行时，直接获取缓存的执行计划，交给执行器执行，这样就大大减少了在前端解析优化花费的时间。