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.
String url = "jdbc:lindorm:table:url=http://ld-bp17j28j2y7pm****-proxy-lindorm-pub.lindorm.rds.aliyuncs.com:30060";
Properties properties = new Properties();
properties.put("user", "root");
properties.put("password", "test");
properties.put("database", "default");
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 |
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 . 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:
String tableName = "sql_table_" + new Random().nextInt(1000);
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);
}
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);
ps.addBatch();
}
int[] ret = ps.executeBatch();
System.out.println(Arrays.toString(ret));
}
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);
}
}
String deleteSql = "delete from " + tableName + " where id=?";
try (PreparedStatement ps = connection.prepareStatement(deleteSql)) {
ps.setString(1, "aa1");
ps.executeUpdate();
}
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);
}
}
String dropTable = "drop table " + tableName;
try (Statement stmt = connection.createStatement()) {
stmt.execute(dropTable);
}
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.
Reference
For more information about the SQL syntax supported by LindormTable, see LindormTable SQL.