Lindorm:Application development based on LindormDataSource

Prerequisites

• You have enabled MySQL protocol compatibility.

• Java Development Kit (JDK) 1.8 or later is installed.

• You have added the client IP address to the Lindorm whitelist.

Connection parameter description

When accessing Lindorm through the MySQL protocol, if no connection configuration is specified in both the connection address and the Properties file, LindormDataSource will automatically fill in parameters to improve SQL access performance. Therefore, you only need to fill in the parameters in the following table. For more information, see Appendix.

JDBC application integration

1. Before connecting to the Lindorm wide table engine through LindormDataSource, you need to install the relevant dependencies.

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

   <dependency>
       <groupId>com.mysql</groupId>
       <artifactId>mysql-connector-j</artifactId>
       <version>8.3.0</version>
   </dependency>
   
   <dependency>
       <groupId>com.aliyun.lindorm</groupId>
       <artifactId>lindorm-sql-datasource</artifactId>
       <version>2.2.1.4</version>
   </dependency>

2. You can connect to the Lindorm wide table engine in the following two ways.

   • Establish a connection in your business code.

     import com.aliyun.lindorm.sql.client.datasource.LindormDataSource;
     import com.aliyun.lindorm.sql.client.datasource.LindormDataSourceConfig;
     import java.sql.Connection;
     import java.sql.PreparedStatement;
     import java.sql.ResultSet;
     
     public class test {
         public static void main(String[] args) throws Exception{
             LindormDataSourceConfig lindormDataSourceConfig = new LindormDataSourceConfig();
             lindormDataSourceConfig.setJdbcUrl("jdbc:mysql://ld-bp1mq0tdzbx1m****-proxy-lindorm-pub.lindorm.aliyuncs.com:33060/database");
             lindormDataSourceConfig.setUsername("r***");
             lindormDataSourceConfig.setPassword("p***");
             lindormDataSourceConfig.setMaximumPoolSize(30);
             LindormDataSource lindormDataSource = new LindormDataSource(lindormDataSourceConfig);
     
             try (Connection connection = lindormDataSource.getConnection()) {
                 String sql = "select * from " + tableName + " where id=?";
                 try (PreparedStatement ps = connection.prepareStatement(sql)) {
                     ps.setString(1, "001");
                     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);
                     }
                 }
             }
     
         }
     }

   • Establish a connection through a configuration file.

     1. Create a new lindorm.properties file in the src/main/resources directory of your Maven project, and add the following content to the file:

        jdbcUrl=jdbc:mysql://ld-bp1mq0tdzbx1m****-proxy-lindorm-pub.lindorm.aliyuncs.com:33060/default
        username=r***
        password=p***
        maximumPoolSize=30

     2. Add the following content to your business code.

        LindormDataSourceConfig config = new LindormDataSourceConfig("lindorm.properties");
        LindormDataSource lindormDataSource = new LindormDataSource(config);

Spring-Boot 2.x application integration

1. Before connecting to the Lindorm wide table engine through LindormDataSource, you need to install the relevant dependencies.

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

   <dependency>
       <groupId>com.mysql</groupId>
       <artifactId>mysql-connector-j</artifactId>
       <version>8.3.0</version>
   </dependency>
   
   <dependency>
       <groupId>com.aliyun.lindorm</groupId>
       <artifactId>lindorm-sql-datasource-springboot-starter</artifactId>
       <version>2.2.1.4</version>
   </dependency>

2. Add Spring configuration. Create a new application.yml file in the src/main/resources directory of your Maven project, and add the following content to the file.

   spring:
     datasource:
       lindorm:
         jdbc-url: jdbc:mysql://ld-bp167w8n1ab5p****-proxy-sql-lindorm.lindorm.rds.aliyuncs.com:33060/db1
         username: r***
         password: t***
         maximum-pool-size: 30

3. Write your business code.

   @Service
   public class DatabaseService {
   
     @Autowired
     private DataSource dataSource;
   
     public void createUser(User user) throws SQLException {
       String sql = "INSERT INTO users (name, age) VALUES (?, ?)";
   
       try (Connection conn = dataSource.getConnection();
           PreparedStatement ps = conn.prepareStatement(sql)) {
         ps.setString(1, user.getName());
         ps.setInt(2, user.getAge());
         ps.executeUpdate();
       }
     }
   }

Spring-Boot 3.x application integration

1. Before connecting to the Lindorm wide table engine through LindormDataSource, you need to install the relevant dependencies.

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

   <dependency>
       <groupId>com.mysql</groupId>
       <artifactId>mysql-connector-j</artifactId>
       <version>8.3.0</version>
   </dependency>
   
   <dependency>
       <groupId>com.aliyun.lindorm</groupId>
       <artifactId>lindorm-sql-datasource-springboot-3-starter</artifactId>
       <version>2.2.1.4</version>
   </dependency>

2. Add Spring configuration. Create a new application.yml file in the src/main/resources folder of the Maven project, and add the following content to the file.

   spring:
     datasource:
       lindorm:
         jdbc-url: jdbc:mysql://ld-bp167w8n1ab5p****-proxy-sql-lindorm.lindorm.rds.aliyuncs.com:33060/db1
         username: r***
         password: r***
         maximum-pool-size: 30

3. Write your business code.

   @Service
   public class DatabaseService {
   
     @Autowired
     private DataSource dataSource;
   
     public void createUser(User user) throws SQLException {
       String sql = "INSERT INTO users (name, age) VALUES (?, ?)";
   
       try (Connection conn = dataSource.getConnection();
           PreparedStatement ps = conn.prepareStatement(sql)) {
         ps.setString(1, user.getName());
         ps.setInt(2, user.getAge());
         ps.executeUpdate();
       }
     }
   }

MyBatis application integration

When using MyBatis, if it is based on the Spring-Boot framework, you can complete the MyBatis configuration in Spring-Boot. If you use MyBatis independently (without relying on Spring-Boot), follow these steps for configuration.

1. Before connecting to the Lindorm wide table engine through LindormDataSource, you need to install the relevant dependencies.

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

   <dependency>
       <groupId>com.mysql</groupId>
       <artifactId>mysql-connector-j</artifactId>
       <version>8.3.0</version>
   </dependency>
   
   <dependency>
       <groupId>com.aliyun.lindorm</groupId>
       <artifactId>lindorm-sql-datasource</artifactId>
       <version>2.2.1.4</version>
   </dependency>

2. You can connect to the Lindorm wide table engine in the following two ways.

   • Establish a connection in your business code.

     SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
     
     LindormDataSource dataSource = new LindormDataSource();
     dataSource.setJdbcUrl("jdbc:mysql://ld-bp1mq0tdzbx1m****-proxy-lindorm-pub.lindorm.aliyuncs.com:33060");
     dataSource.setUsername("r***");
     dataSource.setPassword("r***");
     sessionFactoryBean.setDataSource(dataSource);
     
     SqlSessionFactory sessionFactory = sessionFactoryBean.getObject();
     try (SqlSession session = sessionFactory.openSession(true)) {
         UserMapper mapper = session.getMapper(UserMapper.class);
         mapper.insert(user);
     }

   • Establish a connection through a configuration file.

     1. Define a DataSourceFactory that complies with MyBatis.

        package com.example.datasource;
        
        import com.aliyun.lindorm.sql.client.datasource.LindormDataSource;
        import com.aliyun.lindorm.sql.client.datasource.LindormDataSourceConfig;
        import org.apache.ibatis.datasource.DataSourceFactory;
        
        public class LindormDataSourceFactory implements DataSourceFactory {
          private Properties props;
        
          @Override
          public void setProperties(Properties props) {
            this.props = props;
          }
        
          @Override
          public DataSource getDataSource() {
            LindormDataSourceConfig config = new LindormDataSourceConfig(props);
            LindormDataSource dataSource = new LindormDataSource(config);
            return dataSource;
          }
        }
        

     2. Create a new src/main/resources file named mybatis-config.properties in the Maven project folder, and add the following content to the file:

        <?xml version="1.0" encoding="UTF-8" ?>
        <!DOCTYPE configuration
                PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
                "http://mybatis.org/dtd/mybatis-3-config.dtd">
        <configuration>
            <environments default="development">
                <environment id="development">
                    <transactionManager type="JDBC"/>
                    <dataSource type="com.example.datasource.LindormDataSourceFactory">
                        <property name="jdbcUrl"
                                  value="jdbc:mysql://ld-8vbn68478unu8****-proxy-sql-lindorm.lindorm.rds.aliyuncs.com:33060/lindorm_test"/>
                        <property name="username" value="r***"/>
                        <property name="password" value="t***"/>
                        <property name="maximumPoolSize" value="30"/>
                    </dataSource>
                </environment>
            </environments>
        
            <mappers>
                <mapper class="com.example.mapper.UserMapper"/>
            </mappers>
        </configuration>
        

     3. Add the following content to your business code.

          public static void main(String[] args) throws SQLException, IOException {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory =
                new SqlSessionFactoryBuilder().build(inputStream);
        
            UserService userService = new UserService(sqlSessionFactory);
            userService.run();
          }

Optional configuration items

Typically, you only need to configure maximumPoolSize (maximum connection pool size). Other configuration items do not require additional configuration.

Appendix

When accessing Lindorm through the MySQL protocol, if the following parameters are not specified in both the URL (connection address) and the Properties file, LindormDataSource will automatically fill them in to improve SQL access performance.

The default parameters that are automatically filled in are as follows:

    ("sslMode", "DISABLED");
    ("allowPublicKeyRetrieval", "true");
    ("useServerPrepStmts", "true");
    ("useLocalSessionState", "true");
    ("rewriteBatchedStatements", "true");
    ("cachePrepStmts", "true");
    ("prepStmtCacheSize", "300");
    ("prepStmtCacheSqlLimit", "50000000");

Therefore, when using LindormDataSource, you only need to fill in the JDBC connection URL for MySQL protocol (url) and specify the target database (database). You do not need to add connection configurations in the connection string. For example: jdbc:mysql://ld-uf6k8yqb741t3****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com:33060/default.