更新时间:2024-05-07 GMT+08:00

执行SQL语句

执行普通SQL语句

应用程序通过执行SQL语句来操作数据库的数据(不用传递参数的语句),需要按以下步骤执行。

支持对XML类型数据进行SELECT、UPDATE、INSERT、DELETE等操作。

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

    1
    2
    3
    4
    5
    6
    // 认证用的用户名和密码直接写到代码中有很大的安全风险,建议在配置文件或者环境变量中存放(密码应密文存放,使用时解密),确保安全。
    // 本示例以用户名和密码保存在环境变量中为例,运行本示例前请先在本地环境中设置环境变量(环境变量名称请根据自身情况进行设置)EXAMPLE_USERNAME_ENV和EXAMPLE_PASSWORD_ENV。
    String userName = System.getenv("EXAMPLE_USERNAME_ENV");
    String password = System.getenv("EXAMPLE_PASSWORD_ENV");
    Connection conn = DriverManager.getConnection("url",userName,password);
    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));");
    
    • 数据库中收到的一次执行请求(不在事务块中),如果含有多条语句,将会被打包成一个事务,事务块中不支持vacuum操作。如果其中有一个语句失败,那么整个请求都将会被回滚。
    • 使用Statement执行多语句时应以“;”作为各语句间的分隔符,存储过程、函数、匿名块不支持多语句执行。当preferQueryMode=simple,语句执行不进行解析逻辑,此场景下无法使用";"作为多语句间的分隔符。
    • “/”可用作创建单个存储过程、函数、匿名块、包体的结束符。当preferQueryMode=simple,语句执行不进行解析逻辑,此场景下无法使用"/"作为结束符。
    • 在prepareThreshold=1时,因为preferQueryMode默认模式不对statement进行缓存淘汰,所以statement执行的每条SQL都会缓存语句,可能导致内存膨胀。需要调整preferQueryMode=extendedCacheEverything,对statement进行缓存淘汰。

  3. 关闭语句对象。

    1
    stmt.close();
    

执行预编译SQL语句

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

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

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

  2. 调用PreparedStatement的setShort设置参数。

    1
    pstmt.setShort(1, (short)2);
    

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

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

    1
    int rowcount = pstmt.executeUpdate();
    

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

    1
    pstmt.close();
    

调用存储过程

GaussDB支持通过JDBC直接调用事先创建的存储过程,步骤如下:

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

    1
    2
    3
    4
    5
    6
    // 认证用的用户名和密码直接写到代码中有很大的安全风险,建议在配置文件或者环境变量中存放(密码应密文存放,使用时解密),确保安全。
    // 本示例以用户名和密码保存在环境变量中为例,运行本示例前请先在本地环境中设置环境变量(环境变量名称请根据自身情况进行设置)EXAMPLE_USERNAME_ENV和EXAMPLE_PASSWORD_ENV。
    String userName = System.getenv("EXAMPLE_USERNAME_ENV");
    String password = System.getenv("EXAMPLE_PASSWORD_ENV");
    Connection myConn = DriverManager.getConnection("url",userName,password);
    CallableStatement cstmt = myConn.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方法调用。

    1
    cstmt.execute();
    

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

    1
    int out = cstmt.getInt(4);  //获取out参数
    

    示例:

     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 in out integer
    )
    as
    begin
        psv_inout := psv_in1 + psv_in2 + psv_inout;
    end;
    /
    

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

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

Oracle兼容模式启用重载时,调用存储过程

打开参数behavior_compat_options='proc_outparam_override'后,JDBC调用事先创建的存储过程,步骤如下:

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

    1
    2
    3
    4
    5
    6
    // 认证用的用户名和密码直接写到代码中有很大的安全风险,建议在配置文件或者环境变量中存放(密码应密文存放,使用时解密),确保安全。
    // 本示例以用户名和密码保存在环境变量中为例,运行本示例前请先在本地环境中设置环境变量(环境变量名称请根据自身情况进行设置)EXAMPLE_USERNAME_ENV和EXAMPLE_PASSWORD_ENV。
    String userName = System.getenv("EXAMPLE_USERNAME_ENV");
    String password = System.getenv("EXAMPLE_PASSWORD_ENV");
    Connection conn = DriverManager.getConnection("url",userName,password);
    CallableStatement cs = conn.prepareCall("{ CALL TEST_PROC(?,?,?) }");
    

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

    1
    2
    3
    4
    PGobject pGobject = new PGobject();
    pGobject.setType("public.compfoo"); // 设置复合类型名,格式为“schema.typename”。
    pGobject.setValue("(1,demo)"); // 绑定复合类型值,格式为“(value1,value2)”。
    cs.setObject(1, pGobject);
    

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

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

  4. 调用CallableStatement的execute方法调用。

    1
    cs.execute();
    

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

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

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

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

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

示例:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
// 在数据库创建复合数据类型。
CREATE TYPE compfoo AS (f1 int, f3 text);
// 在数据库中已创建了如下存储过程,它带有out参数
create or replace procedure test_proc
(
    psv_in in compfoo,
    psv_out out compfoo,
    table_out out compfoo_table
)
as
begin
    psv_out := psv_in;
    table_out:=compfoo_table();
    table_out.extend(2);
    table_out(1):=psv_out;
    table_out(2):=psv_out;
end;
/
// 在数据库中创建table类型
create type compfoo_table is table of compfoo;

执行批处理

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

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

    1
    2
    3
    4
    5
    6
    // 认证用的用户名和密码直接写到代码中有很大的安全风险,建议在配置文件或者环境变量中存放(密码应密文存放,使用时解密),确保安全。
    // 本示例以用户名和密码保存在环境变量中为例,运行本示例前请先在本地环境中设置环境变量(环境变量名称请根据自身情况进行设置)EXAMPLE_USERNAME_ENV和EXAMPLE_PASSWORD_ENV。
    String userName = System.getenv("EXAMPLE_USERNAME_ENV");
    String password = System.getenv("EXAMPLE_PASSWORD_ENV");
    Connection conn = DriverManager.getConnection("url",userName,password);
    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO customer_t1 VALUES (?)");
    

  2. 针对每条数据都要调用setShort设置参数,以及调用addBatch确认该条设置完毕。

    1
    2
    pstmt.setShort(1, (short)2);
    pstmt.addBatch();
    

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

    1
    int[] rowcount = pstmt.executeBatch();
    

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

    1
    pstmt.close();
    

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