Lindorm:Develop applications using the Java ORM framework MyBatis

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

1. Add the MyBatis and MySQL JDBC Driver dependencies. For example, for a Maven project, add the following dependencies to the dependencies block in the pom.xml file:

   <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.3.0</version>
   </dependency>

2. In the resources folder, create a mybatis-config.xml configuration file to store the connection information for 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-bp1g0p8i3265l****-proxy-lindorm-pub.lindorm.aliyuncs.com:33060/default?sslMode=disabled&amp;allowPublicKeyRetrieval=true&amp;useServerPrepStmts=true&amp;useLocalSessionState=true&amp;rewriteBatchedStatements=true&amp;cachePrepStmts=true&amp;prepStmtCacheSize=100&amp;prepStmtCacheSqlLimit=50000000"/>
                   <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 JDBC connection address for the MySQL protocol. The format is jdbc:mysql://<MySQL-compatible endpoint>/<Database name>?<Connection configuration>. If you do not specify a database name, the client connects to the default database. To obtain the MySQL-compatible Endpoint, see View endpoints. The connection configuration can improve performance. Specify all configuration items. For more information, see Connection configuration. Important If your application is deployed on an Elastic Compute Service (ECS) instance, access the Lindorm instance over a virtual private cloud (VPC) for higher security and lower network latency. If your application is deployed locally, enable a public endpoint in the console before you connect to the Lindorm instance over the Internet. To do this, in the console, choose Database Connection > Wide Table Engine. On the Wide Table Engine tab, click Enable Public Endpoint. To access the Lindorm instance over a VPC, set the url parameter to the VPC address of the MySQL-compatible endpoint. To access the Lindorm instance over the Internet, set the url parameter to the Public address of the MySQL-compatible endpoint.
   username	If you forget the user password, you can change it in the cluster management system of LindormTable. For more information, see Change a user password.
   password

3. 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) + '}';
     }
   }

4. Create a MyBatis mapper to define the mapping 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();
   }

5. Write the 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();
       }
     }
   }

Complete example

For the complete sample code, see mybatis-demo.zip.

After the code is successfully executed, 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}]

FAQ

### Cause: java.lang.ClassCastException: class org.apache.ibatis.executor.ExecutionPlaceholder cannot be cast to class java.util.List