执行SQL语句

执行普通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调用该存储过程，返回的游标将不可用。
• 存储过程不能和普通SQL在同一条语句中执行。
• 存储过程中inout类型参数必需注册出参。

• 很多数据库类如Connection、Statement和ResultSet都有close()方法，在使用完对象后应把它们关闭。Connection对象的关闭将间接关闭所有与它关联的Statement对象，Statement对象的关闭将间接关闭ResultSet对象。
• 一些JDBC驱动程序提供命名参数的方法来设置参数。命名参数的方法允许根据名称而不是顺序来设置参数，若参数有默认值，则可以不用指定参数值就可以使用此参数的默认值。即使存储过程中参数的顺序发生了变更，也不必修改应用程序。目前GaussDB数据库的JDBC驱动程序不支持此方法。
• GaussDB数据库不支持带有输出参数的函数，也不支持存储过程和函数参数默认值。
• conn.prepareCall("{? = CALL TESTPROC(?,?,?)}")，执行存储过程绑定参数时，可以按照占位符的顺序绑定参数，注册第一个参数为出参。也可以按照存储过程中的参数顺序绑定参数，注册第四个参数为出参，上述用例为此场景，注册第四个参数为出参。

创建和调用存储过程（入参为复合数据类型）

以下用例展示A兼容模式下，入参为复合数据类型的存储过程创建和调用情况。前置条件是数据库建连完成、连接对象为conn。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

// 在数据库创建复合数据类型。
CREATE TYPE compfoo AS (f1 int, f3 text);
// 在数据库中创建table类型。
create type compfoo_table is table of compfoo;
// 在数据库中创建如下存储过程，它带有out参数。
create or replace procedure test_proc
(
    psv_in in compfoo,
    table_in in compfoo_table,
    psv_out out compfoo,
    table_out out compfoo_table
)
as
begin
    psv_out := psv_in;
    table_out:=compfoo_table();
    table_out.extend(table_in.count);
    for i in 1..table_in.count loop
         table_out(i):=table_in(i);
    end loop;
end;
/

调用存储过程test_proc如下：

1. 设置参数behavior_compat_options='proc_outparam_override'后，调用Connection的prepareCall方法创建调用语句对象cs。

   1 2 3

   Statement statement = conn.createStatement(); statement.execute("set behavior_compat_options='proc_outparam_override'"); CallableStatement cs = conn.prepareCall("{ CALL TEST_PROC(?,?,?,?) }");

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

   1 2 3 4 5 6 7 8

   PGobject pGobject = new PGobject(); pGobject.setType("public.compfoo"); // 设置复合类型名，格式为“schema.typename”。 pGobject.setValue("(1,demo)"); // 绑定复合类型值，格式为“(value1,value2)”。 cs.setObject(1, pGobject); pGobject = new PGobject(); pGobject.setType("public.compfoo_table"); // 设置Table类型名，格式为"schema.typename"。 pGobject.setValue("{\"(10,demo10)\",\"(11,demo111)\"}"); //绑定Table类型值，格式为"{\"(value1,value2)\",\"(value1,value2)\",...}"。 cs.setObject(2, pGobject);

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

   1 2 3 4

   // 注册out类型的参数，类型为复合类型,格式为“schema.typename”。 cs.registerOutParameter(3, Types.STRUCT, "public.compfoo"); // 注册out类型的参数，类型为Table类型,格式为“schema.typename”。 cs.registerOutParameter(4, Types.ARRAY, "public.compfoo_table");

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

   1	cs.execute();

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

   1 2 3 4 5 6 7 8 9 10 11 12

   // 返回结构是自定义类型。 PGobject result = (PGobject)cs.getObject(3); // 获取out参数 result.getValue(); // 获取复合类型字符串形式值。 result.getArrayValue(); //获取复合类型数组形式值，以复合数据类型字段顺序排序。 result.getStruct(); //获取复合类型子类型名，按创建顺序排序。 result.getAttributes(); //返回自定义类型每列组成类型的对象，对于array类型和table类型返回的是PgArray,对于自定义类型，封装的是PGobject，对于其他类型数据存储方式为字符串类型。 // 返回结果是Table类型。 PgArray pgArray = (PgArray) cs.getObject(4); ResultSet rs = pgArray.getResultSet(); while (rs.next()) { rs.getObject(2);// table类型每行的数据构建成的对象。 }

   

   如果出参的table类型组成为自定义类型，例如create type compfoo_table is table of compfoo，此时接收到的返回对象为PgArray，在通过rs.getObject(2)遍历获取到的组成对象也为PgArray，此时无法获取到组成它的compfoo类型对应的每列数据，需要通过getPGobject()获取到PgObject再操作获取。

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

   1	cs.close();

• A兼容模式开启参数后，调用存储过程必须使用{call proc_name(?,?,?)}形式调用，调用函数必须使用{? = call func_name(?,?)}形式调用（等号左侧的“？”为函数返回值的占位符，用于注册函数返回值）。
• 参数behavior_compat_options='proc_outparam_override'变更后，业务需要重新建立连接，否则无法正确调用存储过程和函数。
• 函数和存储过程中包含复合类型时，参数的绑定与注册需要使用schema.typename形式。