Using the Struct Type
This section describes how to use the struct type of a JDBC driver.
Prerequisites for code running:
- Add the gaussdbjdbc.jar package as required. For example, if you use an IDE to run code, you need to add the gaussdbjdbc.jar package to the local IDE.
- An A-compatible database is connected, and the database version must be 503.0 or later.
Example 1: The JDBC driver uses the record type in the kernel.
// Writing the username and password used for authentication directly into code may cause security risks. It is recommended that you store them in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security. // In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (variable names are subject to the actual situation). // Change the values of $ip, $port, and database. import com.huawei.gaussdb.jdbc.jdbc.StructDescriptor; import com.huawei.gaussdb.jdbc.jdbc.GaussStruct; import java.sql.*; public class StructTest1 { // Establish a database connection in non-encrypted mode. public static Connection getConnection() { String username = System.getenv("EXAMPLE_USERNAME_ENV"); String passwd = System.getenv("EXAMPLE_PASSWORD_ENV"); String driver = "com.huawei.gaussdb.jdbc.Driver"; String sourceURL = "jdbc:gaussdb://$ip:$port/database?enableGaussArrayAndStruct=true"; Connection conn = null; try { // Load the database driver. Class.forName(driver); } catch (Exception e) { e.printStackTrace(); return null; } try { // Create a database connection. conn = DriverManager.getConnection(sourceURL, username, passwd); System.out.println("Connection succeed!"); } catch (Exception e) { e.printStackTrace(); return null; } return conn; } /** * Create a frontend database object. * * @param conn conn * @throws SQLException An exception occurred while executing the statement */ public static void prepareTestObject(Connection conn) throws SQLException { Statement stmt = conn.createStatement(); // To output parameters, enable the proc_outparam_override parameter for the database. stmt.execute("set behavior_compat_options = 'proc_outparam_override'"); // Create a test object. stmt.execute("create or replace package test_pkg is\n" + " type test_rec is record(col1 int, col2 varchar(50));\n" + " function test_func return test_rec;\n" + " procedure test_proc(v1 in test_rec, v2 out test_rec);\n" + "end test_pkg;"); stmt.execute("create or replace package body test_pkg is\n" + " function test_func return test_rec is\n" + " v test_rec;" + " begin\n" + " v.col1 := 123;\n" + " v.col2 := 'abc';\n" + " return v;\n" + " end;\n" + " procedure test_proc(v1 in test_rec, v2 out test_rec) is\n" + " begin\n" + " v2.col1 := v1.col1 + 1;\n" + " v2.col2 := v1.col2 || 'd';\n" + " end;\n" + "end test_pkg;"); } /** * Clear the database object. * * @param conn conn * @throws SQLException if an exception occurred while executing the statement */ public static void cleanTestObject(Connection conn) throws SQLException { Statement stmt = conn.createStatement(); stmt.execute("drop package test_pkg"); } /** * Use struct object APIs. * * @param struct instance of Struct * @throws SQLException if used Struct failed */ public static void testStruct(Struct struct) throws SQLException { // Traverse and print elements. Object[] attributes = struct.getAttributes(); for (Object attribute : attributes) { System.out.println(attribute); } } /** * Construct a struct object and use related APIs (non-standard). * * @param conn conn * @throws SQLException if create Struct failed */ public static void testConstructStruct1(Connection conn) throws SQLException { System.out.println("=========== testConstructStruct1 ==========="); String typeName = "test_pkg.test_rec"; StructDescriptor typeDesc = StructDescriptor.getDescriptor(typeName, conn); GaussStruct struct = new GaussStruct(typeDesc, new Object[]{666, "aaabbbccc"}); testStruct(struct); } /** * Construct a struct object and use related APIs (standard). * * @param conn conn * @throws SQLException if create Struct failed */ public static void testConstructStruct2(Connection conn) throws SQLException { System.out.println("=========== testConstructStruct2 ==========="); String typeName = "test_pkg.test_rec"; Struct struct = conn.createStruct(typeName, new Object[]{666, "aaabbbccc"}); testStruct(struct); } /** * Obtain the struct object returned by the function. * * @param conn conn * @throws SQLException if an exception occurred */ public static void testReturnStructParam(Connection conn) throws SQLException { System.out.println("=========== testReturnStructParam ==========="); // Run the stored procedure, obtain the struct object returned by the function, and execute struct APIs. PreparedStatement stmt = conn.prepareStatement("select test_pkg.test_func()"); ResultSet rs = stmt.executeQuery(); rs.next(); Struct Struct = (Struct) rs.getObject(1); testStruct(Struct); } /** * Construct a struct object and input and output parameters. * * @param conn conn * @throws SQLException if an exception occurred */ public static void testInputOutputStructParam(Connection conn) throws SQLException { System.out.println("=========== testInputOutputStructParam ==========="); String typeName = "test_pkg.test_rec"; StructDescriptor typeDesc = StructDescriptor.getDescriptor(typeName, conn); GaussStruct inStruct = new GaussStruct(typeDesc, new Object[]{123, "abc"}); // Run a stored procedure. CallableStatement cstmt = conn.prepareCall("{call test_pkg.test_proc(?, ?)}"); cstmt.setObject(1, inStruct); cstmt.registerOutParameter(2, Types.STRUCT, typeName); cstmt.execute(); // Obtain the output parameters and execute struct APIs. Struct outStruct = (Struct) cstmt.getObject(2); testStruct(outStruct); } /** * Main program. Call static methods one by one. * * @param args args */ public static void main(String[] args) throws SQLException { // Create a database connection. Connection conn = getConnection(); // Create a frontend test object. prepareTestObject(conn); // Method 1 for constructing a struct object testConstructStruct1(conn); // Method 2 for constructing a struct object testConstructStruct2(conn); // Return the struct. testReturnStructParam(conn); // Input and output struct parameters. testInputOutputStructParam(conn); // Delete the test object. cleanTestObject(conn); } }
The execution result of the preceding example is as follows:
=========== testConstructStruct1 =========== 666 aaabbbccc =========== testConstructStruct2 =========== 666 aaabbbccc =========== testReturnStructParam =========== 123 abc =========== testInputOutputStructParam =========== 124 abcd
Example 2: Struct object APIs are used.
// Writing the username and password used for authentication directly into code may cause security risks. It is recommended that you store them in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security. // In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (variable names are subject to the actual situation). // Change the values of $ip, $port, and database. import com.huawei.gaussdb.jdbc.jdbc.GaussStruct; import com.huawei.gaussdb.jdbc.jdbc.StructDescriptor; import java.sql.*; public class StructTest2 { // Establish a database connection in non-encrypted mode. public static Connection getConnection() { String username = System.getenv("EXAMPLE_USERNAME_ENV"); String passwd = System.getenv("EXAMPLE_PASSWORD_ENV"); String driver = "com.huawei.gaussdb.jdbc.Driver"; String sourceURL = "jdbc:gaussdb://$ip:$port/database?enableGaussArrayAndStruct=true"; Connection conn = null; try { // Load the database driver. Class.forName(driver); } catch (Exception e) { e.printStackTrace(); return null; } try { // Create a database connection. conn = DriverManager.getConnection(sourceURL, username, passwd); } catch (Exception e) { e.printStackTrace(); return null; } return conn; } /** * Create a frontend database object. * * @param conn conn * @throws SQLException An exception occurred while executing the statement */ public static void prepareTestObject(Connection conn) throws SQLException { Statement stmt = conn.createStatement(); stmt.execute("create or replace package test_pkg is\n" + " type test_rec is record(col1 int, col2 varchar(50));\n" + "end test_pkg;"); } /** * Clear the database object. * * @param conn conn * @throws SQLException if an exception occurred while executing the statement */ public static void cleanTestObject(Connection conn) throws SQLException { Statement stmt = conn.createStatement(); stmt.execute("drop package test_pkg"); } /** * Main program. Call static methods one by one. * * @param args args */ public static void main(String[] args) throws SQLException { // Create a database connection. Connection conn = getConnection(); // Create a frontend test object. prepareTestObject(conn); String typeName = conn.getSchema() + ".test_pkg.test_rec"; // Obtain the type descriptor of the test_array type. StructDescriptor desc = StructDescriptor.getDescriptor(typeName, conn); // Create a GaussStruct object based on the type descriptor and element data. GaussStruct struct = new GaussStruct(desc, new Object[]{123, "abc"}); // Obtain the record type descriptor. desc = struct.getDescriptor(); // Use type descriptor APIs (non-standard). // Print whether the type name is $currentSchema.test_pkg.test_rec. System.out.println(typeName.equals(desc.getSQLTypeName())); // Print whether sqlType of the type is Types.STRUCT. System.out.println(desc.getSQLType() == Types.STRUCT); // Use a struct API. // Obtain and traverse the struct elements. Object[] attributes = struct.getAttributes(); for (Object attribute : attributes) { System.out.println(attribute); } // Delete the test object. cleanTestObject(conn); } }
The execution result of the preceding example is as follows:
true true 123 abc
Example 3: An empty element of the string type is converted to null when it is passed to the database.
// Writing the username and password used for authentication directly into code may cause security risks. It is recommended that you store them in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security. // In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (variable names are subject to the actual situation). // Change the values of $ip, $port, and database. import java.sql.*; public class StructTest3 { // Establish a database connection in non-encrypted mode. public static Connection getConnection() { String username = System.getenv("EXAMPLE_USERNAME_ENV"); String passwd = System.getenv("EXAMPLE_PASSWORD_ENV"); String driver = "com.huawei.gaussdb.jdbc.Driver"; String sourceURL = "jdbc:gaussdb://$ip:$port/database?enableGaussArrayAndStruct=true"; Connection conn = null; try { // Load the database driver. Class.forName(driver); } catch (Exception e) { e.printStackTrace(); return null; } try { // Create a database connection. conn = DriverManager.getConnection(sourceURL, username, passwd); } catch (Exception e) { e.printStackTrace(); return null; } return conn; } /** * Create a frontend database object. * * @param conn conn * @throws SQLException An exception occurred while executing the statement */ public static void prepareTestObject(Connection conn) throws SQLException { Statement stmt = conn.createStatement(); stmt.execute("create table test_tab(c1 varchar(50))"); stmt.execute("create or replace package test_pkg is\n" + " type test_rec is record(col1 varchar(50), col2 varchar(50), col3 varchar(50));\n" + " procedure test_proc(v1 in test_rec);\n" + "end test_pkg;"); stmt.execute("create or replace package body test_pkg is\n" + " procedure test_proc(v1 in test_rec) is\n" + " begin\n" + " insert into test_tab values(v1.col1);\n" + " insert into test_tab values(v1.col2);\n" + " insert into test_tab values(v1.col3);\n" + " end;\n" + "end test_pkg;"); } /** * Clear the database object. * * @param conn conn * @throws SQLException if an exception occurred while executing the statement */ public static void cleanTestObject(Connection conn) throws SQLException { Statement stmt = conn.createStatement(); stmt.execute("drop package test_pkg"); stmt.execute("drop table test_tab"); } /** * Transfer a struct with empty elements to the database. * * @param conn conn * @throws SQLException if an exception occurred while executing the statement */ public static void inParamWithEmptyElement(Connection conn) throws SQLException { String typeName = "test_pkg.test_rec"; // Create a struct object. Struct struct = conn.createStruct(typeName, new Object[]{"", "", null}); // Traverse and print elements. for (Object attribute : struct.getAttributes()) { if (attribute == null) { System.out.println("attribute is null"); } else { if (((String) attribute).isEmpty()) { System.out.println("attribute is empty"); } } } // Input parameters and run the stored procedure. CallableStatement cstmt = conn.prepareCall("{call test_pkg.test_proc(?)}"); cstmt.setObject(1, struct, Types.STRUCT); cstmt.execute(); // Query results. PreparedStatement stmt = conn.prepareStatement("select * from test_tab"); ResultSet rs = stmt.executeQuery(); while (rs.next()) { String s = rs.getString(1); if (s == null) { System.out.println("s is null"); } else { if (s.isEmpty()) { System.out.println("s is empty"); } } } } /** * Main program. Call static methods one by one. * * @param args args */ public static void main(String[] args) throws SQLException { // Create a database connection. Connection conn = getConnection(); // Create a frontend test object. prepareTestObject(conn); // Transfer a struct with empty elements to the database as input parameters. inParamWithEmptyElement(conn); // Delete the test object. cleanTestObject(conn); } }
The execution result of the preceding example is as follows:
attribute is empty attribute is empty attribute is null s is null s is null s is null

- The type name is case-sensitive and cannot contain decimal points.
- The type name does not support synonyms.
- The type name can be in the Schema.Package.Type, Package.Type, Schema.Type, or Type format. If no schema name is transferred, the current schema is used by default. If the schema or package name contains a decimal point, it must be enclosed in double quotation marks.
- The element supports the following basic types: int2, int4, int8, float4, float8, numeric, bool, bpchar, varchar, nvarchar2, name, text, timestamp, timestamptz, time, timetz, clob, bytea, and blob. Possible aliases of the preceding types include smallint, int, integer, bigint, number, float, boolean, char, and varchar2.
- The element supports the following user-defined types: array, collection, and record.
- After JDBC enables the URL parameter enableGaussArrayAndStruct, the output parameters and return values of the collection or array type can be obtained as struct objects. To output parameters, the GUC parameter behavior_compat_options needs to be set to 'proc_outparam_override' for the database.
- JDBC relies on the database to verify type modifiers. For example:
- The element precision cannot be converted. For example, for the record(c1 numeric(3, 1), c2 numeric(3, 1)) type, if the value of the input column element is 2.55 when a struct is constructed, JDBC will not convert it to 2.6.
- The element length cannot be verified. For example, for the record(c1 varchar(5), c2 varchar(5)) type, if the length of the input string is greater than 5 when the struct is constructed, JDBC will not report an error.
- NOT NULL verification is not supported. For example, for the record(c1 int not null, c2 int) type, if the first column contains null when the struct is constructed, JDBC will not report an error.
- When a struct object passed to the database contains empty elements, the database converts them to null. For example, an empty element of the string type is converted to null when being passed to the database. Similarly, if the length of a string stored in an element of the CLOB type is 0, the element is converted to null when being passed to the database. In addition, if the length of a binary array stored in an element of the BLOB or BYTEA type is 0, the element is converted to null when being passed to the database.
You are advised not to store empty elements in the constructed struct object because empty elements are converted to null when they are passed to the database. For example, an empty element of the string type is converted to null. For details, see Example 3.
When the struct object sent from the database to the client contains empty elements, the client also converts these elements to null.
- For details about struct APIs, see the description of standard APIs supported by GaussStruct objects.
- StructDescriptor is a non-standard API. Its getSQLType API returns the fixed value java.sql.Types.STRUCT.
For details about the type name returned by the getSQLTypeName API of StructDescriptor, see Data Type Mapping.
The following table lists standard struct APIs.
Method |
Return Type |
Throws |
Supported or Not |
---|---|---|---|
getSQLTypeName() |
String |
SQLException |
Supported |
getAttributes() |
Object[] |
SQLException |
Supported |
getAttributes(java.util.Map<String,Class<?>> map) |
Object[] |
SQLException |
Not supported |

- The object type obtained by the getAttribute API is Object[]. For details about the type of each element in the returned array, see the Java Variable Type column in Data Type Mapping.
For example, for the record(c1 int, c2 varchar(5)) type, the first element in Object[] returned by getAttribute is null or an integer, and the second element is null or a string.
Special notes: For an element of the int2 or smallint type, the element type is changed to short when the element is accessed.
- For details about the type names returned by the getSQLTypeName API, see Data Type Mapping. The mapping rules for the type names of the collection, array, and record types are as follows:
- If a type is defined in a package, its name is generally in the Schema.Package.Type format.
If any of schema, package, and type names does not comply with the rules, the type name is in the "Schema"."Package"."Type" format.
- Only digits, letters, and underscores (_) are allowed.
- The first character cannot be a digit.
- If a type is defined in a schema, its name is generally in the Schema.Type format.
If any of schema and type names do not comply with the rules, the type name is in the "Schema"."Type" format.
- Only digits, letters, and underscores (_) are allowed.
- The first character cannot be a digit.
- If a type is defined in a package, its name is generally in the Schema.Package.Type format.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot