Use the MyBatis framework to connect to LindormTable databases - Lindorm

You can connect to LindormTable over MySQL by using multiple programming languages and frameworks, including MyBatis that is an ORM framework for Java. The MyBatis framework decouples SQL from business code, which achieves more flexible and convenient data management. If you are accustomed to using the MyBatis framework for development or want to manage and optimize SQL statements in a centralized manner, we recommend that you use the MyBatis framework to connect to and use LindormTable.

Prerequisites

The MySQL compatibility feature is enabled for the instance. For more information, see Enable the MySQL compatibility feature.
Java Development Kit (JDK) V1.8 or later is installed.
The IP address of your client is added to the whitelist of your Lindorm instance. For more information, see Configure whitelists.

Procedure

Add the dependencies of MyBatis and MySQL JDBC Driver. For example, you can add the following dependencies to the pom.xml file in your Maven project:

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.14</version>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.0.33</version>
</dependency>

Create a configuration file named mybatis-config.xml in the resources directory to save the basic information used to connect to LindormTable.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://ld-uf6k8yqb741t3****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com:33060/default"/>
                <property name="username" value="root"/>
                <property name="password" value="test"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper class="org.example.UserMapper"/>
    </mappers>
</configuration>

Parameters

Parameter	Description
url	The LindormTable endpoint for MySQL and the database to which you want to connect. The value of the parameter is in the following format: `jdbc:mysql://<LindormTable endpoint for MySQL>/<Database name>`. By default, your client is connected to a database named default. For more information about how to obtain the LindormTable endpoint for MySQL, see View endpoints. Important If your application is deployed on an Elastic Compute Service (ECS) instance, we recommend that you use a VPC to connect to the Lindorm instance for higher security and lower latency. If your application is deployed on a local server and needs to connect to the Lindorm instance over the Internet, you can perform the following steps to enable the Internet endpoint for the instance in the Lindorm console: In the left-side navigation pane, select Database Connections > Wide Table Engine. On the Wide Table Engine tab, click Enable Public Endpoint. If you use a VPC to access the Lindorm instance, specify the LindormTable VPC endpoint for MySQL in the value of url. If you use the Internet to access the Lindorm instance, specify the LindormTable Internet endpoint for MySQL in the value of url.
username	The user name and password used to connect to LindormTable. If you forget your password, you can change the password in the cluster management system of LindormTable. For more information, see Manage users.
password

Create an object class.

package org.example;

import java.nio.charset.StandardCharsets;
import java.sql.Date;
import java.sql.Timestamp;

public class User {
  private int userId;
  private String userName;
  private double height;
  private long score;
  private Timestamp createTime;
  private Date birthday;

  private byte[] digest;

  public User(int userId, String userName, double height, long score,
      Timestamp createTime, Date birthday, byte[] digest) {
    this.userId = userId;
    this.userName = userName;
    this.height = height;
    this.score = score;
    this.createTime = createTime;
    this.birthday = birthday;
    this.digest = digest;
  }

  public int getUserId() {
    return userId;
  }

  public void setUserId(int userId) {
    this.userId = userId;
  }

  public String getUserName() {
    return userName;
  }

  public void setUserName(String userName) {
    this.userName = userName;
  }

  public double getHeight() {
    return height;
  }

  public void setHeight(double height) {
    this.height = height;
  }

  public long getScore() {
    return score;
  }

  public void setScore(long score) {
    this.score = score;
  }

  public Timestamp getCreateTime() {
    return createTime;
  }

  public void setCreateTime(Timestamp createTime) {
    this.createTime = createTime;
  }

  public Date getBirthday() {
    return birthday;
  }

  public void setBirthday(Date birthday) {
    this.birthday = birthday;
  }

  public byte[] getDigest() {
    return digest;
  }

  public void setDigest(byte[] digest) {
    this.digest = digest;
  }

  @Override
  public String toString() {
    return "User{" + "userId=" + userId + ", userName='" + userName + '\'' + ", height=" + height + ", score=" + score + ", createTime=" + createTime + ", birthday=" + birthday + ", digest=" + new String(digest, StandardCharsets.UTF_8) + '}';
  }
}

Create a mapper to define the mapping relationship between SQL statements and business code.

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

package org.example;

import java.util.List;

public interface UserMapper {

  @Update("create table if not exists demo_user(`id` INT, `name` VARCHAR, `height` DOUBLE, `score` BIGINT, `createtime` TIMESTAMP, `birthday` DATE, digest VARBINARY,primary key(id))")
  void createUserTable();

  @Update("drop table if exists demo_user")
  void dropUserTable();

  @Insert("upsert into demo_user(`id`,`name`,`height`,`score`,`createtime`,`birthday`,`digest`) values(#{userId},#{userName},#{height},#{score},#{createTime},#{birthday},#{digest})")
  int upsertUser(User user);

  @Delete("delete from demo_user where `id` = #{userId}")
  int deleteUser(@Param("userId") int userId);

  @Select("select * from demo_user where `id` = #{userId}")
  User selectOneUser(@Param("userId") int userId);

  @Select("select * from demo_user")
  List<User> selectAllUser();
}

Write your business code.

package org.example;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.Date;
import java.sql.Timestamp;

public class MybatisDemo {
  public static void main(String[] args) throws Exception {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(
        inputStream);
    try (SqlSession session = sqlSessionFactory.openSession()) {
      UserMapper mapper = session.getMapper(UserMapper.class);

      //create user table
      mapper.createUserTable();

      //select all users
      System.out.println(mapper.selectAllUser());

      User user1 = new User(1, "zhangsan", 1.8, 100,
          new Timestamp(System.currentTimeMillis()),
          Date.valueOf("1995-03-02"),
          "hello".getBytes(StandardCharsets.UTF_8));
      User user2 = new User(2, "lisi", 1.7, 90,
          new Timestamp(System.currentTimeMillis()),
          Date.valueOf("1996-08-02"),
          "world".getBytes(StandardCharsets.UTF_8));

      //insert user1 and user2
      mapper.upsertUser(user1);
      mapper.upsertUser(user2);

      //select all users
      System.out.println(mapper.selectAllUser());
      //select user1
      System.out.println(mapper.selectOneUser(1));

      //delete user1
      mapper.deleteUser(1);
      System.out.println(mapper.selectAllUser());

      //update user2's score to 99
      user2.setScore(99);
      mapper.upsertUser(user2);
      System.out.println(mapper.selectAllUser());

      //drop user table
      mapper.dropUserTable();
    }
  }
}

Sample code

The complete sample code is included in mybatis-demo.tar.

After the sample code is run, the following result is returned:

[User{userId=1, userName='zhangsan', height=1.8, score=100, createTime=2023-12-02 09:39:17.63, birthday=1995-03-02, digest=hello}, User{userId=2, userName='lisi', height=1.7, score=90, createTime=2023-12-02 09:39:17.63, birthday=1996-08-02, digest=world}]
User{userId=1, userName='zhangsan', height=1.8, score=100, createTime=2023-12-02 09:39:17.63, birthday=1995-03-02, digest=hello}
[User{userId=2, userName='lisi', height=1.7, score=90, createTime=2023-12-02 09:39:17.63, birthday=1996-08-02, digest=world}]
[User{userId=2, userName='lisi', height=1.7, score=99, createTime=2023-12-02 09:39:17.63, birthday=1996-08-02, digest=world}]