全部產品
Search
文件中心

Lindorm:基於Java ORM架構MyBatis的應用開發

更新時間:Jul 06, 2024

Lindorm寬表引擎提供了一系列基於MySQL協議的串連方式,涉及多種語言和多種架構,其中包括Java對象關係映射(ORM)架構MyBatis。MyBatis架構實現了SQL與代碼的解耦,使資料管理變得更加靈活和方便。如果您習慣使用MyBatis架構進行資料開發,或想要對SQL語句進行統一管理和最佳化,推薦您通過Java ORM架構MyBatis串連和使用Lindorm寬表引擎。

前提條件

  • 已開通MySQL協議相容功能。如何開通,請參見開通MySQL協議相容功能

  • 已安裝Java環境,要求安裝JDK 1.8及以上版本。

  • 已將用戶端IP添加至白名單,具體操作請參見設定白名單

操作步驟

  1. 添加MyBatis和MySQL JDBC Driver依賴。以Maven專案為例,在pom.xml檔案的dependencies中添加以下依賴項:

    <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>
  2. resources目錄下,建立mybatis-config.xml基礎設定檔,用於儲存Lindorm寬表引擎相關串連資訊。

    <?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>

    參數說明

    參數

    說明

    url

    MySQL協議的Java JDBC串連地址及需要串連的資料庫。格式為jdbc:mysql://<MySQL相容地址>/<資料庫名>。預設串連default資料庫。

    如何擷取MySQL相容地址,請參見查看串連地址

    重要
    • 如果應用部署在ECS執行個體,建議您通過專用網路訪問Lindorm執行個體,可獲得更高的安全性和更低的網路延遲。

    • 如果應用部署在本地,在通過公網串連Lindorm執行個體前,需在控制台開通公網地址。開通方式:在控制台選擇資料庫連接 > 寬表引擎,在寬表引擎頁簽單擊開通公網地址

    • 通過專用網路訪問Lindorm執行個體,url中請填寫MySQL相容地址對應的專用網路地址。通過公網訪問Lindorm執行個體,url中請填寫MySQL相容地址對應的公網地址。

    username

    如果您忘記使用者密碼,可以通過Lindorm寬表引擎的叢集管理系統修改密碼。具體操作,請參見修改使用者密碼

    password

  3. 建立對象類。

    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. 建立MyBatis架構的Mapper,用於定義SQL語句與業務代碼的映射關係。

    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. 編寫業務代碼。

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

完整樣本

完整範例程式碼,請參見mybatis-demo.tar

執行成功後,將返回以下結果:

[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}]