Lindorm:Use Java JDBC APIs to develop applications

Prerequisites

• The MySQL compatibility feature is enabled for the instance. For more information, see Enable the MySQL compatibility feature.

• Java Development Kit (JDK) V1.8 or later is installed.

• The IP address of your client is added to the whitelist of your Lindorm instance. For more information, see Configure whitelists.

Procedure

1. Add the dependencies of the JDBC driver for MySQL. For example, you can add the following dependencies to the pom.xml file in your Maven project:

     <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>8.0.33</version>
     </dependency>

   Important

   • We recommend that you use the JDBC driver for MySQL of version 8.0 or later to connect to LindormTable.

   • To establish a connection by using the JDBC driver for MySQL, you must manually add the package of the driver (mysql-connector-java-x.x.x.jar) to CLASSPATH. Otherwise, the connection cannot be established.

2. Initialize a JDBC for MySQL client and establish a connection between the client and data.

   Class.forName("com.mysql.jdbc.Driver");
   
   // The database username that is obtained from the Lindorm console. 
   String username = "root";
   // The database password that is obtained from the Lindorm console. 
   String password = "root";
   // You can specify the database to which you want to connect when you establish the connection. If no database is specified, your client is connected to a database named default. 
   String database = "default";
   // Specify the LindormTable endpoint for MySQL. 33060 is the port number for MySQL. 
   String url = "jdbc:mysql://ld-uf6k8yqb741t3****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com:33060/" + database + "?sslMode=disabled&allowPublicKeyRetrieval=true&useServerPrepStmts=true&useLocalSessionState=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=100&prepStmtCacheSqlLimit=50000000";
   Properties properties = new Properties();
   properties.put("user", username);
   properties.put("password", password);
   
   // Obtain the established connection.
   Connection connection = DriverManager.getConnection(url, properties);

   Parameters

   Parameter	Description
   url	The URL used by the JDBC for MySQL client to connect to LindormTable. The URL is in the following format: jdbc:mysql://<LindormTable endpoint for MySQL>/" + database + "?<Connection settings> For more information about how to obtain the LindormTable endpoint for MySQL, see View endpoints. For more information about the connection settings, see Connection settings. Important If your application is deployed on an ECS instance, we recommend that you use a VPC to connect to the Lindorm instance to ensure higher security and lower network latency. If your application is deployed on a local server and needs to connect to the Lindorm instance over the Internet, you must enable the Internet endpoint for the instance in the Lindorm console. For more information, see View the endpoints of LindormTable. If you use a VPC to access the Lindorm instance, specify the LindormTable VPC endpoint for MySQL in the value of url. If you use the Internet to access the Lindorm instance, specify the LindormTable Internet endpoint for MySQL in the value of url.
   database	The name of the database to which you want to connect. By default, your client is connected to a database named default.
   username	The username and password used to connect to LindormTable. If you forget your password, you can change the password in the cluster management system of LindormTable. For more information, see Manage users.
   password

   Connection settings

   Parameter	Example	Description
   sslMode	disabled	Specifies whether to use SSL to establish encrypted connections. We recommend that you set this parameter to disabled. In this case, SSL is not used when a connection is established, which improves system performance.
   allowPublicKeyRetrieval	true	Specifies whether to retrieve the RSA public key from the server during user authentication. We recommend that you set this parameter to true.
   useServerPrepStmts	true	Specifies whether to use preprocessed statements on the server. We recommend that you set this parameter to true. In this case, Databases can use the built-in SQL preprocessing capability to improve query performance and reduce the risk of SQL injection.
   useLocalSessionState	true	Specifies whether to use the local session status to reduce the frequency of data transmission between the client and database server. We recommend that you set this parameter to true.
   rewriteBatchedStatements	true	Specifies whether to rewrite batch statements. We recommend that you set this parameter to true. In this case, the write performance of applications that use preprocessed statements and specify parameters in batches.
   cachePrepStmts	true	Specifies whether preprocessed statements can be cached. We recommend that you set this parameter to true to reduce the frequency of creating preprocessed statements. In this case, the query performance of statements that are frequently executed can be improved.
   prepStmtCacheSize	100	Specifies the number of preprocessed statements that can be cached. This parameter is available only when the cachePrepStmts parameter is set to true.
   prepStmtCacheSqlLimit	500000	Specifies the maximum length of the preprocessed statements that can be cached. SQL statements that exceed the specified length are not cached. If your business is performance-sensitive, we recommend that you set this parameter to a larger value. Note If you specify a large value for prepStmtCacheSqlLimit, more memory resources are consumed. Configure this parameter based on your actual requirements.

3. After the connection is established, use SQL syntaxes to connect to and use LindormTable. The following example shows how to query data updated in the latest time range in an index:

   String tableName = "sql_table_" + new Random().nextInt(1000);
   // Create a table.
   try (Statement statement = connection.createStatement()) {
     String sql = "create table if not exists " + tableName + "(id VARCHAR, name VARCHAR, primary key(id))";
     int ret = statement.executeUpdate(sql);
     System.out.println(ret);
   }
   
   // Insert data to the table.
   String upsertSql = "insert into " + tableName + "(id,name) values(?,?)";
   try (PreparedStatement ps = connection.prepareStatement(upsertSql)) {
     int batchSize = 100;
     for (int i = 0; i < batchSize; i++) {
       ps.setString(1, "aa" + i);
       ps.setString(2, "bb" + i);
       // Add the write operation to a batch.
       ps.addBatch();
     }
     int[] ret = ps.executeBatch();
     System.out.println(Arrays.toString(ret));
   }
   
   // Query data in the table.
   String querySql = "select * from " + tableName + " where id=?";
   try (PreparedStatement ps = connection.prepareStatement(querySql)) {
     ps.setString(1, "aa1");
     ResultSet rs = ps.executeQuery();
     while (rs.next()) {
       String id = rs.getString(1);
       String name = rs.getString(2);
       System.out.println("id=" + id);
       System.out.println("name=" + name);
     }
   }
   
   // Delete data from the table.
   String deleteSql = "delete from " + tableName + " where id=?";
   try (PreparedStatement ps = connection.prepareStatement(deleteSql)) {
     ps.setString(1, "aa1");
     ps.executeUpdate();
   }
   
   // Close the connection. Make sure the connection is closed after an operation is complete. Otherwise, connection leaks occur. 
   connection.close();

   For more information about how to use the SQL syntaxes supported by LindormTable, see Lindorm SQL syntax.

   Note

   • In the preceding sample code, batchSize indicates the number of data records that can be inserted to a table in a batch. We recommend that you set this parameter to a value that is not larger than 65535/Number of columns. The optimal value of this parameter can be calculated by using the following formula: Math.round(65535/Number of columns). For example, the sample table in the preceding code contains two columns. Therefore, the recommended maximum value of batchsize is 32767.5 (65535/2), which can be rounded down to 32767.

   • In Lindorm SQL, the INSERT syntax is equivalent to the UPSERT syntax. However, the JDBC for MySQL client is optimized for the INSERT syntax. Therefore, we recommend that you use the INSERT syntax to write data.