Lindorm:Application development based on LindormDataSource

Prerequisites

• Enable MySQL protocol compatibility.

• A Java environment with JDK 1.8 or a later version is required.

• Add your client IP address to the Lindorm whitelist.

Connection parameter description

When you access Lindorm using the MySQL protocol, LindormDataSource automatically fills in optimized settings to improve SQL access performance if no connection configuration is specified in the connection URL or the Properties file. Therefore, you only need to provide the parameters listed in the following table. For more information, see Appendix.

Integrate JDBC applications

1. Before you connect to the Lindorm wide table engine using LindormDataSource, you must install the required 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 one of 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 using a configuration file.

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

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

     2. Add the following code to your business logic:

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

Integrate Spring Boot 2.x applications

1. Before you connect to the Lindorm wide table engine using LindormDataSource, you must install the required 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 the Spring configuration. Create an application.yml file in the src/main/resources directory of your Maven project and add the following content:

   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();
       }
     }
   }

Integrate Spring Boot 3.x applications

1. Before you connect to the Lindorm wide table engine using LindormDataSource, you must install the required 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 the Spring configuration. Create an application.yml file in the src/main/resources directory of your Maven project and add the following content:

   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();
       }
     }
   }

Integrate MyBatis applications

When you use MyBatis with Spring Boot, you must configure MyBatis within Spring Boot. If you use MyBatis independently without Spring Boot, follow these steps.

1. Before you connect to the Lindorm wide table engine using LindormDataSource, you must install the required 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 one of 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 using a configuration file.

     1. Define a DataSourceFactory that is compatible 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 mybatis-config.properties file in the src/main/resources directory of your Maven project and add the following content:

        <?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 code to your business logic:

          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 need to configure only the maximumPoolSize configuration item, which specifies the maximum connection pool size. Other configuration items do not require additional configuration.

Appendix

When you access Lindorm using the MySQL protocol, LindormDataSource automatically fills in the following parameters to improve SQL access performance if they are not specified in the URL (endpoint) or the Properties file.

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 you use LindormDataSource, you need to provide only the JDBC connection URL for the MySQL protocol (the `url` parameter) and specify the target database (the `database` parameter). Do not add connection configuration to the connection string. For example, jdbc:mysql://ld-uf6k8yqb741t3****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com:33060/default.