Lindorm:Use Java JDBC APIs to develop applications

How it works

1. Add the MySQL JDBC driver dependency to your project.

2. Initialize the driver and establish a connection using a JDBC URL that targets LindormTable's MySQL-compatible endpoint.

3. Use standard JDBC APIs with Lindorm SQL to create tables, write data, query, and delete records.

Prerequisites

Before you begin, make sure you have:

• The MySQL compatibility feature enabled on your Lindorm instance. See Enable the MySQL compatibility feature

• JDK 1.8 or later installed

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

Step 1: Add the MySQL JDBC driver

For a Maven project, add the following dependency to the dependencies section in pom.xml:

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.3.0</version>
</dependency>

For a Gradle project, add the following to your build.gradle:

implementation 'com.mysql:mysql-connector-j:8.3.0'

Step 2: Connect to LindormTable

Initialize the MySQL JDBC driver and establish a connection:

Class.forName("com.mysql.cj.jdbc.Driver");

// The database username from the Lindorm console.
String username = "root";
// The database password from the Lindorm console.
String password = "root";
// Specify the database to connect to. If omitted, the default database is used.
String database = "default";
// The LindormTable endpoint for MySQL. Port 33060 is fixed and must not be changed.
// Replace the hostname with your actual MySQL-compatible endpoint.
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);

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

Connection parameters

Connection settings

All connection settings in the URL are required. The following table explains each setting:

Step 3: Run SQL operations

After connecting, use Lindorm SQL to create tables, insert, query, and delete data:

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 in a batch. Use INSERT — in Lindorm SQL, INSERT has UPSERT semantics.
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);
        ps.addBatch();
    }
    int[] ret = ps.executeBatch();
    System.out.println(Arrays.toString(ret));
}

// 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()) {
        String id = rs.getString(1);
        String name = rs.getString(2);
        System.out.println("id=" + id);
        System.out.println("name=" + name);
    }
}

// Delete data.
String deleteSql = "DELETE FROM " + tableName + " WHERE id = ?";
try (PreparedStatement ps = connection.prepareStatement(deleteSql)) {
    ps.setString(1, "aa1");
    ps.executeUpdate();
}

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

For the full Lindorm SQL syntax reference, see SQL reference.

Usage notes

INSERT and UPSERT semantics

In Lindorm SQL, INSERT has the same semantics as UPSERT. The MySQL JDBC client optimizes INSERT statements, so use INSERT for all data writes.

Batch write sizing

Batch writes reduce remote procedure call (RPC) overhead and let the server process multiple rows per request, increasing throughput compared to single-row writes. However, batches that are too large can cause out-of-memory (OOM) errors or full garbage collection (Full GC) pauses, which degrades service stability. Keep batchSize between 50 and 100 rows. To increase overall write throughput, increase write concurrency rather than batch size.

What's next

• SQL reference — full Lindorm SQL syntax

• View endpoints — locate your MySQL-compatible endpoint

• Configure a whitelist — manage IP access control