Lindorm寬表引擎提供了一系列基於MySQL協議的串連方式,涉及多種語言和多種架構,其中包括Java對象關係映射(ORM)架構MyBatis。MyBatis架構實現了SQL與代碼的解耦,使資料管理變得更加靈活和方便。如果您習慣使用MyBatis架構進行資料開發,或想要對SQL語句進行統一管理和最佳化,推薦您通過Java ORM架構MyBatis串連和使用Lindorm寬表引擎。
前提條件
已開通MySQL協議相容功能。如何開通,請參見開通MySQL協議相容功能。
已安裝Java環境,要求安裝JDK 1.8及以上版本。
已將用戶端IP添加至白名單,具體操作請參見設定白名單。
操作步驟
添加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>
在
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
建立對象類。
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) + '}'; } }
建立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(); }
編寫業務代碼。
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}]