更新时间:2025-03-13 GMT+08:00

执行SQL语句

本小节中,执行普通SQL语句创建customer_t1表,执行预处理插入语句批量插入数据,执行预处理更新语句更新数据,同时演示创建和调用存储过程

执行普通SQL语句

应用程序通过执行SQL语句来操作数据库,支持对XML类型数据进行SELECT、UPDATE、INSERT、DELETE等操作。

前置条件是已经连接数据库,连接对象为conn。执行普通SQL语句,创建customer_t1表的步骤如下:

  1. 调用Connection接口的createStatement方法创建语句对象stmt。

    1
    Statement stmt = conn.createStatement();
    

  2. 调用Statement接口的executeUpdate方法执行SQL语句。

    1
    int rc = stmt.executeUpdate("CREATE TABLE customer_t1(c_customer_sk INTEGER, c_customer_name VARCHAR(32));");
    

  3. 调用Statement接口的close方法关闭语句对象stmt。

    1
    stmt.close();
    

  • 数据库中收到的一次执行请求(不在事务块中),如果含有多条语句,将会被打包成一个事务,事务块中不支持vacuum操作,如果其中有一个语句失败,那么整个请求都将会被回滚。
  • 使用Statement执行多语句时应以“;”作为各语句间的分隔符,存储过程、函数和匿名块不支持多语句执行。当preferQueryMode=simple,语句执行不进行解析逻辑,此场景下无法使用“;”作为多语句间的分隔符。
  • “/”可用作创建单个存储过程、函数、匿名块、包体的结束符。当preferQueryMode=simple,语句执行不进行解析逻辑,此场景下无法使用“/”作为结束符。
  • 由于JDBC会对prepareStatement中的SQL语句进行缓存,可能导致内存膨胀,如果JVM内存较小,建议调整preparedStatementCacheSizeMiB或者preparedStatementCacheQueries。

执行预处理插入语句

用一条预处理语句处理多条相似的数据,数据库只创建一次执行计划,节省了语句的编译和优化时间。

前置条件是执行以上的普通SQL语句,已经创建customer_t1表。执行预处理语句,批量插入数据的步骤如下:

  1. 调用Connection接口的prepareStatement方法创建预处理语句对象pst。

    1
    PreparedStatement pst = conn.prepareStatement("INSERT INTO customer_t1 VALUES (?,?)");
    

  2. 针对每条数据要调用对应接口设置参数,以及调用addBatch将SQL语句添加到批处理中。

    1
    2
    3
    4
    5
    for (int i = 0; i < 3; i++) {
       pst.setInt(1, i);
       pst.setString(2, "data " + i);
       pst.addBatch();
    }
    

  3. 调用PreparedStatement接口的executeBatch方法执行批处理。

    1
    pst.executeBatch();
    

  4. 调用PreparedStatement接口的close方法关闭预处理语句对象pst。

    1
    pst.close();
    

在实际的批处理过程中,通常不终止批处理程序的执行,否则会降低数据库的性能。因此在批处理程序时,应该关闭自动提交功能,每几行提交一次。关闭自动提交功能的语句为:

conn.setAutoCommit(false);

执行预处理更新语句

预编译语句是只编译和优化一次,可以通过设置不同的参数值多次使用。由于已经预先编译好,后续使用会减少执行时间。因此,如果多次执行一条语句,请选择使用预编译语句。

前置条件是执行以上的预处理语句,customer_t1表已经批量插入数据。执行预编译SQL语句对数据进行更新的步骤如下:

  1. 调用Connection接口的prepareStatement方法创建预编译语句对象pstmt。

    1
    PreparedStatement pstmt = conn.prepareStatement("UPDATE customer_t1 SET c_customer_name = ? WHERE c_customer_sk = 1");
    

  2. 调用PreparedStatement接口的setString方法设置参数。

    1
    pstmt.setString(1, "new Data");
    

  3. 调用PreparedStatement接口的executeUpdate方法执行预编译SQL语句。

    1
    int rowcount = pstmt.executeUpdate();
    

  4. 调用PreparedStatement接口的close方法关闭预编译语句对象pstmt。

    1
    pstmt.close();
    

prepareStatement设置绑定参数后,最终会构建成一个B报文或U报文,在下一步执行SQL语句时发给服务端。但是B报文或U报文有最大长度限制(不能超过1023MB),如果一次绑定数据过大,可能因报文过长导致异常。因此prepareStatement设置绑定参数时需要注意评估和控制绑定数据的大小,避免出现超出报文上限要求的现象。

创建和调用存储过程

GaussDB支持通过JDBC调用存储过程,前置条件是数据库建连完成、连接对象为conn。

创建存储过程testproc如下:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
//在数据库中创建如下存储过程,它带有out参数
CREATE or REPLACE procedure testproc 
(
    psv_in1 in integer,
    psv_in2 in integer,
    psv_inout inout integer
)
AS
BEGIN
    psv_inout := psv_in1 + psv_in2 + psv_inout;
END;
/

调用存储过程testproc如下:

  1. 调用Connection的prepareCall方法创建调用语句对象cstmt。

    1
    CallableStatement cstmt = conn.prepareCall("{? = CALL testproc(?,?,?)}");
    

  2. 调用CallableStatement的setInt方法设置参数。

    1
    2
    3
    cstmt.setInt(2, 50);
    cstmt.setInt(1, 20);
    cstmt.setInt(3, 90);
    

  3. 调用CallableStatement的registerOutParameter方法注册输出参数。

    1
    cstmt.registerOutParameter(4, Types.INTEGER);  //注册out类型的参数,类型为整型。
    

  4. 调用CallableStatement的execute方法执行SQL语句。

    1
    cstmt.execute();
    

  5. 调用CallableStatement的getInt方法获取out输出参数。

    1
    int out = cstmt.getInt(4);
    

  6. 调用CallableStatement的close方法关闭调用语句对象cstmt。

    1
    cstmt.close();
    

  • 一些JDBC驱动程序提供命名参数的方法来设置参数。命名参数的方法允许根据名称而不是顺序来设置参数,若参数有默认值,则可以不用指定参数值就可以使用此参数的默认值。即使存储过程中参数的顺序发生了变更,也不必修改应用程序。目前GaussDB数据库的JDBC驱动程序不支持此方法。
  • GaussDB数据库不支持带有输出参数的函数,也不支持存储过程和函数参数默认值。
  • 使用conn.prepareCall("{? = CALL testproc(?,?,?)}")执行存储过程绑定参数时,可以按照占位符的顺序绑定参数,注册第一个参数为出参。也可以按照存储过程中的参数顺序绑定参数,注册第四个参数为出参,上述用例为此场景,注册第四个参数为出参。
  • 当游标作为存储过程的返回值时,如果使用JDBC调用该存储过程,返回的游标将不可用。
  • 存储过程不能和普通SQL在同一条语句中执行。
  • 存储过程中inout类型参数必须注册出参。

在语句中添加单分片执行语法

前置条件是已经连接数据库,连接对象为conn,创建test表并插入数据。

  1. 通过调用Connection对象的setClientInfo(String name,String value)方法来设置nodeName参数。

    conn.setClientInfo("nodeName","datanode1");

  2. 执行SQL语句,其中包括使用Statement对象的executeQuery(String sql)和execute(String sql)以及PreparedStatement对象的executeQuery()和execute()方法。

    PreparedStatement pstm = conn.prepareStatement("SELECT * FROM test");
    pstm.execute();
    pstm.executeQuery();
    Statement stmt=conn.createStatement();
    stmt.execute("SELECT * FROM test");
    stmt.executeQuery("SELECT * FROM test");

  3. 关闭参数,将参数值设置为空字符串。

    conn.setClientInfo("nodeName","");
    • 该功能基于内核单分片执行功能进行适配,所以使用前请确认使用的数据库内核是否支持单分片执行。
    • 开启nodeName参数,执行SQL语句后需要手动关闭参数,否则会对其他查询语句的执行产生影响。
    • 一旦参数开启,当前连接所有的语句都会到指定的DN上去执行。
    • 参数开启后PreparedStatement对象的缓存机制会受到影响,已经缓存的语句会被清空,之后执行的带单分片查询的语句都不再缓存,直到参数关闭后才恢复缓存功能。
    • 参数为连接级参数,所以在同一时间只有一个参数会生效,无法通过此接口做到同一时间两条语句到不同的分片上去执行。