Lindorm:Use a JDBC driver to connect to and send SQL requests to LindormSearch

Prerequisites

Before you begin, make sure you have:

• LindormSearch activated for your Lindorm instance. For more information, see Activate LindormSearch

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

• The SQL endpoint of the LindormSearch cluster. For more information, see View endpoints

Add the Maven dependency

Add the following dependency to the pom.xml file of your project:

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

Create a collection

Create a collection on the Cluster Management page in the Lindorm console before running the sample code. For more information, see Manage collections.

Connection parameters

Use the following parameters to connect to LindormSearch:

Sample code

The following example connects to LindormSearch and runs a sequence of DDL and DML operations: create a table, upsert rows, query results, delete rows, and drop the table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class SearchSqlDemo {
    public static void main(String[] args) {
        Connection pconn = null;
        Statement stmt = null;
        try {
            // Load the LindormSearch JDBC driver.
            Class.forName("com.aliyun.lindorm.search.client.Driver");

            // Set the SQL endpoint of your LindormSearch cluster.
            String url = "jdbc:lindorm:search:url=http://ld-xxxx:30070";

            Properties props = new Properties();
            props.put("user", "testuser");
            props.put("password", "password");

            // Establish a connection.
            pconn = DriverManager.getConnection(url, props);

            // Create a statement.
            stmt = pconn.createStatement();

            // Create a table.
            stmt.execute("create table if not exists test(c1 int, c2 varchar, primary key(c1))");

            // Write a single row.
            stmt.execute("upsert into test(c1,c2) values(1,'Shenzhen')");
            stmt.execute("upsert into test(c1,c2) values(2,'Shanghai')");

            // Write multiple rows.
            stmt.execute("upsert into test(c1,c2) values(3,'Beijing'),(4,'Guangzhou'),(5,'Hangzhou')");

            // Query all rows.
            ResultSet rs = stmt.executeQuery("select * from test order by c1");
            System.out.println("#####before delete:");
            while (rs.next()) {
                System.out.println("c1=" + rs.getInt("c1") + ",c2=" + rs.getString("c2"));
            }

            // Delete rows where c1 > 3.
            stmt.execute("delete from test where c1 >3");

            // Query remaining rows.
            rs = stmt.executeQuery("select * from test order by c1");
            System.out.println("#####after delete:");
            while (rs.next()) {
                System.out.println("c1=" + rs.getInt("c1") + ",c2=" + rs.getString("c2"));
            }

            // Drop the table.
            stmt.execute("drop table if exists test");
        } catch (Throwable e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (pconn != null) {
                    pconn.close();
                }
            } catch (Throwable e) {
                e.printStackTrace();
            }
        }
    }
}

Use a connection pool to manage connections. A connection pool avoids the overhead of establishing and closing a connection for each operation, which improves query performance.