On this page

Configuring the JDBC Driver
Executing SQL Statements
Example of calling the isValid method to refresh the cache
Decrypting the Encrypted Equality Ciphertext
Precompiling an Encrypted Table
Batch Processing on Encrypted Tables

Show all

Help Center/ GaussDB/ Feature Guide(Centralized_V2.0-8.x)/ Setting Encrypted Equality Query/ Using JDBC to Operate an Encrypted Database

Using JDBC to Operate an Encrypted Database

Updated on 2024-08-20 GMT+08:00

View PDF

Configuring the JDBC Driver

Obtain the JDBC driver package. For details about how to obtain and use the JDBC driver, see "Application Development Guide > Development Based on JDBC" and "Application Development Guide > Compatibility Reference > JDBC Compatibility Package" in Developer Guide.
The encrypted database supports the gsjdbc4.jar, opengaussjdbc.jar, and gscejdbc.jar JDBC driver packages.
- gscejdbc.jar (currently, only EulerOS is supported): The main class name is com.huawei.gaussdb.jdbc.Driver, and the URL prefix of the database connection is jdbc:gaussdb. This driver package is recommended in encrypted scenarios. The Java code examples in this section use the gscejdbc.jar package by default.
- gaussdbjdbc.jar: The main class name is com.huawei.gaussdb.jdbc.Driver. The URL prefix of the database connection is jdbc:gaussdb. This driver package does not contain the dependent libraries related to encryption and decryption that need to be loaded to an encrypted database. You need to manually configure the LD_LIBRARY_PATH environment variable.
- gaussdbjdbc-JRE7.jar: The main class name is com.huawei.gaussdb.jdbc.Driver. The URL prefix of the database connection is jdbc:gaussdb. The gaussdbjdbc-JRE7.jar package is used in the JDK 1.7 environment. This driver package does not contain the dependent libraries related to encryption and decryption that need to be loaded to an encrypted database. You need to manually configure the LD_LIBRARY_PATH environment variable.
NOTE:
Other compatibility: The encrypted database also supports other compatible JDBC driver packages: gsjdbc4.jar and opengaussjdbc.jar.
- gsjdbc4.jar: The main class name is org.postgresql.Driver, and the URL prefix of the database connection is jdbc:postgresql.
- opengaussjdbc.jar: The main class name is com.huawei.opengauss.jdbc.Driver, and the URL prefix of the database connection is jdbc:opengauss.
Configure LD_LIBRARY_PATH.
Before using the JDBC driver package in encrypted scenarios, you need to set the environment variable LD_LIBRARY_PATH.
- When the gscejdbc.jar driver package is used, the dependent library required by the encrypted database in the gscejdbc.jar driver package is automatically copied to the path and loaded when connecting to the database with the encrypted database function enabled.
- When using gaussdbjdbc.jar, gaussdbjdbc-JRE7.jar, opengaussjdbc.jar, or gsjdbc4.jar, you need to decompress GaussDB-Kernel_Database version number_OS version number_64bit_libpq.tar.gz to a specified directory, and add the path of the lib folder to the LD_LIBRARY_PATH environment variable.
  CAUTION:
  
  To use the JDBC driver package in the full-encryption scenario, you must have the System.loadLibrary permission as well as the read and write permissions on files in the first-priority path of the environment variable LD_LIBRARY_PATH. You are advised to use an independent directory to store the full-encryption dependent library. If java.library.path is specified during execution, the value must be the same as the first-priority path of LD_LIBRARY_PATH.

When gscejdbc.jar is used, JVM that loads class files depends on the libstdc++ library of the system. If the encrypted database function is enabled, gscejdbc.jar automatically copies the dynamic libraries (including the libstdc++ library) on which the encrypted database depends to the LD_LIBRARY_PATH path set by the user. If the version of a dependent library does not match that of the existing system library, only the dependent library is deployed during the first running. After the dependent library is called again, it can be used normally.

Executing SQL Statements

Before running the SQL statements in this section, ensure that the preparation and configuration phases are complete.

This section uses a complete execution process as an example to describe how to use the encrypted database syntax, including three phases: DDL statement execution, DML statement execution, and cleanup.

For details about JDBC development operations that are the same as those in non-encrypted scenarios, see "Application Development Guide > Development Based on JDBC" in Developer Guide.

Connection parameters of an encrypted database

enable_ce: string type. If enable_ce is set to 0, the full encryption function is disabled. If enable_ce is set to 1, the basic capability of encrypted equality query is supported. If enable_ce is set to 3, the memory decryption emergency channel is supported based on the encrypted equality query capability.

// The following uses the gscejdbc.jar driver as an example. If other driver packages are used, you only need to change the driver class name and the URL prefix of the database connection.
// gsjdbc4.jar: The main class name is org.postgresql.Driver, and the URL prefix of the database connection is jdbc:postgresql.
// opengaussjdbc.jar: The main class name is com.huawei.opengauss.jdbc.Driver, and the URL prefix of the database connection is jdbc:opengauss.
// gscejdbc.jar: The main class name is com.huawei.gaussdb.jdbc.Driver, and the URL prefix of the database connection is jdbc:gaussdb.
// gaussdbjdbc.jar: The main class name is com.huawei.gaussdb.jdbc.Driver, and the URL prefix of the database connection is jdbc:gaussdb.
// gaussdbjdbc-JRE7.jar: The main class name is com.huawei.gaussdb.jdbc.Driver, and the URL prefix of the database connection is jdbc:gaussdb.

public static void main(String[] args) {
    // Driver class.
    String driver = "com.huawei.gaussdb.jdbc.Driver";
    // Database connection descriptor. If enable_ce is set to 1, the encrypted equality query basic capability is supported.
    String sourceURL = "jdbc:gaussdb://127.0.0.1:8000/postgres?enable_ce=1";
    // Set the username and password in the environment variables USER and PASSWORD, respectively.
    String username = System.getenv("USER");
    String passwd = System.getenv("PASSWORD");
    Connection conn = null;
    try {
        // Load the driver.
        Class.forName(driver);
        // Create a connection.
        conn = DriverManager.getConnection(sourceURL, username, passwd);
        System.out.println("Connection succeed!");
        // Create a statement object.
        Statement stmt = conn.createStatement();
       // Set the parameters for accessing the master key.
        // Two methods are provided here. Select either of them.
        // Authentication mode 1: AK/SK authentication (For details about how to obtain parameters, including the project ID, AK, and SK, see the master key generation phase.)
        conn.setClientInfo("key_info", "keyType=huawei_kms, kmsProjectId={Project ID}, ak={AK}, sk={SK}");
        
        /* Example:
             conn.setClientInfo("key_info", "keyType=huawei_kms,kmsProjectId=0b59929e8100268a2f22c01429802728," + 
                 "ak=XMAUMJY******DFWLQW, sk=ga6rO8lx1Q4uB*********2gf80muIzUX,");
        */
        // Authentication mode 2: Account and password authentication (For details about how to obtain related parameters, including the IAM server address, IAM username, IAM user password, account name, and project, see the master key generation phase.)
        conn.setClientInfo("key_info", "keyType=huawei_kms," +
            "iamUrl={IAM server address}," +
            "iamUser={IAM username}," +
            "iamPassword={IAM user password}," +
            "iamDomain={Account name}," +
            "kmsProject={Project}");
        /* Example:
        conn.setClientInfo("key_info", "keyType=huawei_kms," +
                "iamUrl=https://iam.xxx.com/v3/auth/tokens," +
                "iamUser=test," +
                "iamPassword=*********," +
                "iamDomain=test_account," +
                "kmsProject=xxx");
        */

        // Define the client master key. cmk1 is the master key name, which can be customized.
        
        // For details about how to obtain the following parameters, including KMS server address and key ID, see the master key generation phase.
        int rc = stmt.executeUpdate("CREATE CLIENT MASTER KEY ImgCMK1 WITH ( KEY_STORE = huawei_kms , KEY_PATH = '{KMS server address}/{Key ID}', ALGORITHM = AES_256);");
        
        /* 
             Example of KEY_PATH: https://kms.cn-north-4.myhuaweicloud.com/v1.0/0b59929e8100268a2f22c01429802728/kms/9a262917-8b31-41af-a1e0-a53235f32de9
             Explanation: In the master key generation phase, the KMS has generated and stored the master key. Running this syntax only stores the master key information in the database for future access.
             Note: For details about the KEY_PATH format, see "SQL Reference > SQL Syntax > CREATE CLIENT MASTER KEY" in Developer Guide.
        */

        // Define a CEK.
        int rc2 = stmt.executeUpdate("CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM  = AES_256_GCM);");
        // Define an encrypted table.
        int rc3 = stmt.executeUpdate("CREATE TABLE creditcard_info (id_number int, name varchar(50) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC),credit_card varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC));");
        // Insert data.
        int rc4 = stmt.executeUpdate("INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393');");
        // Query the encrypted table.
        ResultSet rs = null;
        rs = stmt.executeQuery("select * from creditcard_info where name = 'joe';");
        // Delete the encrypted table.
        int rc5 = stmt.executeUpdate("DROP TABLE IF EXISTS creditcard_info;");
        // Delete a CEK.
        int rc6 = stmt.executeUpdate("DROP COLUMN ENCRYPTION KEY IF EXISTS ImgCEK1;");
        // Delete the CMK.
        int rc7 = stmt.executeUpdate("DROP CLIENT MASTER KEY IF EXISTS ImgCMK1;");
        // Close the statement object.
        stmt.close();
        // Close the connection.
        conn.close();
    } catch (Exception e) {
        e.printStackTrace();
        return;
    }
}

NOTE:

When JDBC is used to perform operations on an encrypted database, one database connection object corresponds to one thread. Otherwise, conflicts may occur due to thread changes.
When JDBC is used to perform operations on an encrypted database, different connections change the encrypted configuration data. The client calls the isValid method to ensure that the connections can hold the changed encrypted configuration data. In this case, the refreshClientEncryption parameter must be set to 1 (default value). In a scenario where a single client performs operations on encrypted data, the refreshClientEncryption parameter can be set to 0.

Example of calling the isValid method to refresh the cache

// Create a connection conn1.
Connection conn1 = DriverManager.getConnection("url","user","password");
// Create a CMK in another connection conn2.
...
// conn1 calls the isValid method to refresh the conn1 key cache.
try {
 if (!conn1.isValid(60)) {
  System.out.println("isValid Failed for connection 1");
 }
} catch (SQLException e) {
 e.printStackTrace();
        return null;
}

Decrypting the Encrypted Equality Ciphertext

A decryption API is added to the PgConnection class to decrypt the encrypted equality ciphertext of the fully-encrypted database. After decryption, the plaintext value is returned. The encrypted column corresponding to the ciphertext is found based on schema.table.column and the original data type is returned.

**Table 1** com.huawei.gaussdb.jdbc.jdbc.PgConnection function
Method	Return Type	Support JDBC 4 (Yes/No)
decryptData(String ciphertext, Integer len, String schema, String table, String column)	ClientLogicDecryptResult	Yes

Parameter description:

ciphertext
Ciphertext to be decrypted.
len
Ciphertext length. If the value is less than the actual ciphertext length, decryption fails.
schema
Name of the schema to which the encrypted column belongs.
table
Name of the table to which the encrypted column belongs.
column
Name of the column to which the encrypted column belongs.
NOTE:
Decryption is successful in the following scenarios, but is not recommended:
- The ciphertext length input parameter is longer than the actual ciphertext.
- schema.table.column points to another encrypted column. In this case, the original data type of the encrypted column is returned.

**Table 2** com.huawei.gaussdb.jdbc.jdbc.clientlogic.ClientLogicDecryptResult function
Method	Return Type	Description	Support JDBC 4 (Yes/No)
isFailed()	Boolean	Specifies whether the decryption fails. If the decryption fails, True is returned. Otherwise, False is returned.	Yes
getErrMsg()	String	Obtains error information.	Yes
getPlaintext()	String	Obtains the decrypted plaintext.	Yes
getPlaintextSize()	Integer	Obtains the length of the decrypted plaintext.	Yes
getOriginalType()	String	Obtains the original data type of the encrypted column.	Yes

// After the ciphertext is obtained through non-encrypted connection or logical decoding, this API can be used to decrypt the ciphertext.
import com.huawei.gaussdb.jdbc.jdbc.PgConnection;
import com.huawei.gaussdb.jdbc.jdbc.clientlogic.ClientLogicDecryptResult;

// conn is an encrypted connection.
// Call the decryptData method of PgConnection to decrypt the ciphertext, locate the encrypted column to which the ciphertext belongs based on the column name, and return the original data type.
ClientLogicDecryptResult decrypt_res = null;
decrypt_res = ((PgConnection)conn).decryptData(ciphertext, ciphertext.length(), schemaname_str,
        tablename_str, colname_str);
// Check whether the decryption of the returned result class is successful. If the decryption fails, obtain the error information. If the decryption is successful, obtain the plaintext, length, and original data type.
if (decrypt_res.isFailed()) {
    System.out.println(String.format("%s\n", decrypt_res.getErrMsg()));
} else {
    System.out.println(String.format("decrypted plaintext: %s size: %d type: %s\n", decrypt_res.getPlaintext(),
        decrypt_res.getPlaintextSize(), decrypt_res.getOriginalType()));
}

Precompiling an Encrypted Table

// Create a prepared statement object by calling the prepareStatement method in Connection.
PreparedStatement pstmt = con.prepareStatement("INSERT INTO creditcard_info VALUES (?, ?, ?);");
// Set parameters by triggering the setShort method in PreparedStatement.
pstmt.setInt(1, 2);
pstmt.setString(2, "joy");
pstmt.setString(3, "6219985678349800033");
// Execute the precompiled SQL statement by triggering the executeUpdate method in PreparedStatement.
int rowcount = pstmt.executeUpdate();
// Close the precompiled statement object by calling the close method in PreparedStatement.
pstmt.close();

Batch Processing on Encrypted Tables

// Create a prepared statement object by calling the prepareStatement method in Connection.
Connection conn = DriverManager.getConnection("url","user","password");
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO creditcard_info (id_number, name, credit_card) VALUES (?,?,?)");
// Call the setShort method for each piece of data, and call addBatch to confirm that the setting is complete.
int loopCount = 20;
 for (int i = 1; i < loopCount + 1; ++i) {
      pstmt.setInt(1, i);
      pstmt.setString(2, "Name " + i);
      pstmt.setString(3, "CreditCard " + i);
      // Add row to the batch.
      pstmt.addBatch();
}
// Execute batch processing by calling the executeBatch method in PreparedStatement.
int[] rowcount = pstmt.executeBatch();
// Close the precompiled statement object by calling the close method in PreparedStatement.
pstmt.close();

Parent topic: Setting Encrypted Equality Query

Previous topic: Using gsql to Operate an Encrypted Database

Next topic: Using Go Driver to Operate an Encrypted Database

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot