Lindorm:Application development using the Java JDBC interface

Prerequisites

Before you begin, ensure that you have:

• JDK 1.8 or later installed

• Your client IP address added to the Lindorm whitelist. See Set a whitelist

Limitations

This topic applies only to Lindorm in wide table mode. Lindorm Serverless is not supported.

Add the Maven dependency

For a Maven project, add the following to the dependencies section of your pom.xml:

<dependency>
  <groupId>com.aliyun.lindorm</groupId>
  <artifactId>lindorm-all-client</artifactId>
  <version>2.2.1.3</version>
</dependency>

Connect to LindormTable

Use DriverManager.getConnection() to establish a connection.

// Lindorm Wide Table SQL endpoint — obtain from the Lindorm console.
// See: View endpoints
String url = "jdbc:lindorm:table:url=http://<your-endpoint>:30060";

Properties properties = new Properties();
// The database username obtained from the Lindorm console.
properties.put("user", "root");
// The database password obtained from the Lindorm console.
properties.put("password", "test");
// The database to connect to. Defaults to "default" if not specified.
properties.put("database", "default");

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

Run CRUD operations

The following example covers the full create, read, update, and delete (CRUD) cycle. The complete runnable sample is in the com.aliyun.lindorm.sql.demo.BasicDemo class — download the sample code.

String tableName = "sql_table_" + new Random().nextInt(1000);

// Create a table.
// The CREATE TABLE statement creates a wide table by default.
try (Statement statement = connection.createStatement()) {
    String sql = "create table if not exists " + tableName
        + "(id VARCHAR, name VARCHAR, primary key(id))";
    statement.executeUpdate(sql);
}

// Insert data using batch upsert.
// Keep individual batches to hundreds of rows — large batches degrade performance.
String upsertSql = "upsert into " + tableName + "(id, name) values(?, ?)";
try (PreparedStatement ps = connection.prepareStatement(upsertSql)) {
    for (int i = 0; i < 100; i++) {
        ps.setString(1, "aa" + i);
        ps.setString(2, "bb" + i);
        ps.addBatch();
    }
    ps.executeBatch();
}

// Query data.
String querySql = "select * from " + tableName + " where id=?";
try (PreparedStatement ps = connection.prepareStatement(querySql)) {
    ps.setString(1, "aa1");
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        System.out.println("id=" + rs.getString(1));
        System.out.println("name=" + rs.getString(2));
    }
}

// Delete a row.
String deleteSql = "delete from " + tableName + " where id=?";
try (PreparedStatement ps = connection.prepareStatement(deleteSql)) {
    ps.setString(1, "aa1");
    ps.executeUpdate();
}

// Update a row.
// LindormTable UPDATE supports only single-row updates, not batch updates.
// The WHERE clause must include the full primary key.
String updateSql = "update " + tableName + " set name=? where id=?";
try (PreparedStatement ps = connection.prepareStatement(updateSql)) {
    ps.setString(1, "bb2update");
    ps.setString(2, "aa2");
    ps.executeUpdate();
}

// Drop the table.
try (Statement stmt = connection.createStatement()) {
    stmt.execute("drop table " + tableName);
}

// Always close the connection after use to prevent connection leaks.
connection.close();

Use LindormTable with Java frameworks

If you prefer to use a higher-level Java framework, refer to the following examples:

• Druid connection pool: Druid access example

• Spring Framework: Spring access example

• MyBatis: MyBatis access example

• Hibernate: Hibernate access example

FAQ

Why does UPDATE require the full primary key in the WHERE clause?

LindormTable UPDATE supports only single-row updates. The WHERE clause must specify the full primary key. For multi-column primary keys, include every key column in the WHERE clause.

What SQL syntax does LindormTable support?

See the SQL syntax manual for the full reference.

References

For information about the syntax of LindormTable SQL, see SQL Syntax Manual.