Use Java JDBC APIs to develop applications based on LindormTable - Lindorm

This topic describes how to use the JDBC APIs provided by Java to develop SQL-based LindormTable applications and provides examples.

Prerequisites

Java Development Kit (JDK) V1.8 or later is installed.
The IP address of your client is added to the whitelist of the Lindorm instance. For more information, see Configure a whitelist.

Limits

The procedure described in this topic is not applicable to Lindorm Serverless.

Procedure

Note

You can directly use the sample code provided in the following procedure, or download sample code from lindorm-sql-demo to your local computer for compiling and running. The sample code provided in this topic is included in the com.aliyun.lindorm.sql.demo.BasicDemo class.

Download a Lindorm client. For example, you can add the following dependencies to the pom.xml file in your Maven project: The following code provides an example on how to call Java API operations for LindormTable SQL to access Lindorm wide tables:
```
<dependency> 
  <groupId>com.aliyun.lindorm</groupId>  
  <artifactId>lindorm-all-client</artifactId>
  <version>2.2.1.3</version>
</dependency>
```

Initialize the Lindorm client and establish a connection between the Lindorm client and data.

// The endpoint that is required when you use SQL to connect to and use LindormTable. 
String url = "jdbc:lindorm:table:url=http://ld-bp17j28j2y7pm****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060";

// Configure connection parameters.
Properties properties = new Properties();

// The database username that is obtained from the Lindorm console. 
properties.put("user", "root");
// The database password that is obtained from the Lindorm console. 
properties.put("password", "test");
// You can specify the database that you want to connect when you establish the connection. If no database is specified, your client is connected to a database named default. 
properties.put("database", "default");

// Obtain the connection.
Connection connection = DriverManager.getConnection(url, properties);

Important

If a connection between the Lindorm client and server is idle for more than 10 minutes, the server closes the connection to improve resource utilization. If you use a connection after it is closed, the com.aliyun.lindorm.client.shaded.org.apache.calcite.avatica.http.ConnectionDisconnectedException error is returned. To fix the issue, establish a new connection.

The following table describes the parameters that you can configure.

Parameter	Example	Description
url	jdbc:lindorm:table:url=http://ld-bp17j28j2y7pm****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060	The endpoint that is required when you use SQL to connect to and use LindormTable. For more information about how to obtain the endpoint, see View endpoints. 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 can perform the following steps to enable the Internet endpoint for the instance in the Lindorm console: In the left-side navigation pane, select Database Connections > Wide Table Engine. On the Wide Table Engine tab, click Enable Public Endpoint.
user	root	If you forget your password, you can change the password in the cluster management system of LindormTable.
password	test
database	default	The name of the database to which you want to connect. By default, your client is connected to a database named default.

After the connection is established, call Java API operations for LindormTable SQL to access Lindorm wide tables. The following code provides an example on how to call Java API operations for LindormTable SQL to access Lindorm wide tables:

/* -------------- Examples of CRUD operations based on JDBC ----------------- */

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 = "upsert 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();
  }
  // Perform write operations in all batches. 
  // To ensure the performance and stability of your application, do not perform write operations in a large number of batches at the same time. 
  // When you use executeBatch() method to perform write operations in batches, we recommend that you specify up to hundreds of batches. 
  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();
}

// Update data in the table.
// The UPDATE statement in Lindorm SQL can update only a single row of data at a time and does not support batch data update.
// Therefore, you must specify all primary key columns in the WHERE clause.
String updateSql = "update " + tableName + " set name = ?  where id=?";
try (PreparedStatement ps = connection.prepareStatement(updateSql)) {
  ps.setString(1, "bb2update");
  ps.setString(2, "aa2");
  ps.executeUpdate();
}
String querySql1 = "select * from " + tableName + " where id=?";
try (PreparedStatement ps = connection.prepareStatement(querySql1)) {
  ps.setString(1, "aa2");
  ResultSet rs = ps.executeQuery();
  System.out.println("--------- update-----------");
  while (rs.next()) {
  String id = rs.getString(1);
  String name = rs.getString(2);
  System.out.println("id=" + id);
  System.out.println("name=" + name);
 }
}

// Delete a table.
String dropTable = "drop table " + tableName;
try (Statement stmt = connection.createStatement()) {
  stmt.execute(dropTable);
}

// Close the connection. Make sure the connection is closed after an operation is complete. Otherwise, connection leaks occur. 
connection.close();

Sample code for connecting to LindormTable by using different frameworks

To meet the requirements of a large number of users, this topic provides relevant links to the webpages that contain sample code that can be used to connect to LindormTable by using different Java frameworks.

If you want to use Druid to connect to LindormTable, see Sample code for a Druid-based connection.
If you want to use Spring to connect to LindormTable, see Sample code for a Spring-based connection.
If you want to use Mybatis to connect to LindormTable, see Sample code for a Mybatis-based connection.
If you want to use Hibernate to connect to LindormTable, see Sample code for a Hibernate-based connection.

Reference

For more information about the SQL syntax supported by LindormTable, see LindormTable SQL.