1.準備工作
1.1創建數據庫
（1）創建數據庫：
CREATE DATABASE mybatis_test DEFAULT CHARACTER SET utf8mb4;
（2）使用數據庫
-- 使?數據數據
USE mybatis_test;
1.2 創建用戶表和實體類
創建用戶表
-- 創建表[??表]CREATE TABLE `user_info` (`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,`username` VARCHAR ( 127 ) NOT NULL,`password` VARCHAR ( 127 ) NOT NULL,`age` TINYINT ( 4 ) NOT NULL,`gender` TINYINT ( 4 ) DEFAULT '0' COMMENT '1-男 2-? 0-默認',`phone` VARCHAR ( 15 ) DEFAULT NULL,`delete_flag` TINYINT ( 4 ) DEFAULT 0 COMMENT '0-正常, 1-刪除',`create_time` DATETIME DEFAULT now(),`update_time` DATETIME DEFAULT now() ON UPDATE now(),PRIMARY KEY ( `id` )
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4; 添加用戶信息
-- 添加??信息
INSERT INTO user_info( username, `password`, age, gender, phone )
VALUES ( 'admin', 'admin', 18, 1, '18612340001' );
INSERT INTO user_info( username, `password`, age, gender, phone )
VALUES ( 'zhangsan', 'zhangsan', 18, 1, '18612340002' );
INSERT INTO user_info( username, `password`, age, gender, phone )
VALUES ( 'lisi', 'lisi', 18, 1, '18612340003' );
INSERT INTO user_info( username, `password`, age, gender, phone )
VALUES ( 'wangwu', 'wangwu', 18, 1, '18612340004' );
實體類的屬性名與表中的字段名??對應
@Data
public class UserInfo {private Integer id;private String username;private String password;private Integer age;private Integer gender;private String phone;private Integer deleteFlag;private Date createTime;private Date updateTime;}1.3 創建文章表和實體類
上?建了?張??表, 我們再來建?張?章表, 進?多表關聯查詢.
?章表的uid, 對應??表的id.
創建文章表：
-- 創建?章表
DROP TABLE IF EXISTS articleinfo;
CREATE TABLE articleinfo (
id INT PRIMARY KEY auto_increment, title VARCHAR ( 100 ) NOT NULL,
content TEXT NOT NULL, uid INT NOT NULL,
delete_flag TINYINT ( 4 ) DEFAULT 0 COMMENT '0-正常, 1-刪除',
create_time DATETIME DEFAULT now(), update_time DATETIME DEFAULT now()
) DEFAULT charset 'utf8mb4';
插入數據
-- 插?測試數據
INSERT INTO articleinfo ( title, content, uid ) VALUES ( 'Java', 'Java正文', 1
);
INSERT INTO articleinfo ( title, content, uid ) VALUES ( 'MySQL', 'MySQL正文', 1
);
INSERT INTO articleinfo ( title, content, uid ) VALUES ( 'C', 'C正文', 2
);實體類的屬性名與表中的字段名??對應
@Data
public class ArticleInfo { private Integer id; private String title; private String content; private Integer uid; private Integer deleteFlag; private Date createTime; private Date updateTime;
}
1.4 配置文件
在Application.yml文件中配置：
# 數據庫連接配置
spring:datasource:# MySQL在遠程服務器上url: jdbc:mysql://x.x.x.x:3306/mybatis_test?characterEncoding=utf8&useSSL=falseusername: root #MySQL賬號password: root #MySQL密碼driver-class-name: com.mysql.cj.jdbc.Drivermybatis:configuration: # 配置打印 MyBatis?志log-impl: org.apache.ibatis.logging.stdout.StdOutImplmap-underscore-to-camel-case: true #配置駝峰?動轉換# 配置 mybatis xml 的?件路徑，在 resources/mapper 創建所有表的 xml ?件mapper-locations: classpath:mapper/**Mapper.xml2.多表查詢
2.1 需求: 根據uid查詢書的作者等相關信息
2.1.1 注解實現
SQL命令：
SELECTa.id,a.title,a.content,a.uid,b.username,b.age,b.gender
FROMarticleinfo aLEFT JOIN user_info b ON a.uid = b.id
WHEREa.id =1;
根據查詢的結果在ArticleInfo 類補充相關的屬性：
@Data
public class ArticleInfo {private Integer id;private String title;private String content;private Integer uid;private Integer deleteFlag;private Date createTime;private Date updateTime;// 補充??相關信息private String username;private Integer age;private Integer gender;}
ArticleInfoMapper接口：
@Mapper
public interface ArticleInfoMapper {@Select("select a.id,a.title,a.content,a.uid, b.username, b.age, b.gender " + // 注意最后的空格"from articleinfo a left join user_info b on a.uid=b.id " +"where a.id = #{id}")ArticleInfo queryArticleAndUser(Integer id);
}
測試代碼：
@Slf4j
@SpringBootTest
class ArticleInfoMapperTest {@Autowiredprivate ArticleInfoMapper articleInfoMapper;@Testvoid queryArticleAndUser() {articleInfoMapper.queryArticleAndUser(1);}
}
運行結果：
如果名稱不?致的, 采?Results, 或者別名的?式解決, 和單表查詢?樣 Mybatis 不分單表還是多表, 主要就是三部分: SQL, 映射關系和實體類
2.1.2 XML實現
SQL命令：
SELECTa.id,a.title,a.content,a.uid,b.username,b.age,b.gender
FROMarticleinfo aLEFT JOIN user_info b ON a.uid = b.id
WHEREa.id =1;
根據查詢的結果在ArticleInfo 類補充相關的屬性：
@Data
public class ArticleInfo {private Integer id;private String title;private String content;private Integer uid;private Integer deleteFlag;private Date createTime;private Date updateTime;// 補充??相關信息private String username;private Integer age;private Integer gender;}
ArticleInfoMapper接口：
@Mapper
public interface ArticleInfoXMLMapper {ArticleInfo queryArticleAndUser(Integer id);
}
ArticleInfoXMLMapper.xml文件：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.mybatis.mapper.ArticleInfoXMLMapper"><select id ="queryArticleAndUser" resultType="org.example.mybatis.model.ArticleInfo">select a.id,a.title,a.content,a.uid, b.username, b.age, b.genderfrom articleinfo a left join user_info b on a.uid=b.idwhere a.id = #{id}</select></mapper>
測試代碼：
@Slf4j
@SpringBootTest
class ArticleInfoXMLMapperTest {@Autowiredprivate ArticleInfoXMLMapper articleInfoXMLMapper;@Testvoid queryArticleAndUser() {articleInfoXMLMapper.queryArticleAndUser(1);}
}
運行結果：
如果名稱不?致的, 采?ResultMap, 或者別名的?式解決, 和單表查詢?樣 Mybatis 不分單表還是多表, 主要就是三部分: SQL, 映射關系和實體類
2.2 需求: 根據user_in的id查詢作者創作的書相關信息
2.1.1 注解實現
SQL命令：
SELECTb.username,b.age,b.gender,a.id,a.title,a.content,a.uid
FROMarticleinfo aRIGTH JOIN user_info b ON b.uid = a.id
WHEREb.id =1;
根據查詢的結果在ArticleInfo 類補充相關的屬性：
@Data
public class ArticleInfo {private Integer id;private String title;private String content;private Integer uid;private Integer deleteFlag;private Date createTime;private Date updateTime;// 補充??相關信息private String username;private Integer age;private Integer gender;}
ArticleInfoMapper接口：
@Mapper
public interface ArticleInfoMapper {@Select("select b.username, b.age,b.gender,a.id,a.title,a.content,a.uid " + // 注意最后的空格"from articleinfo a right join user_info b on a.uid=b.id " +"where b.id = #{id}")List<ArticleInfo> queryArticleAndUser(Integer id);
}
測試代碼：
@Slf4j
@SpringBootTest
class ArticleInfoMapperTest {@Autowiredprivate ArticleInfoMapper articleInfoMapper;@Testvoid queryArticleAndUser() {articleInfoMapper.queryArticleAndUser(1);}
}
運行結果：
如果名稱不?致的, 采?Results, 或者別名的?式解決, 和單表查詢?樣 Mybatis 不分單表還是多表, 主要就是三部分: SQL, 映射關系和實體類
2.1.2 XML實現
SQL命令：
SELECTb.username,b.age,b.gender,a.id,a.title,a.content,a.uid
FROMarticleinfo aRIGHT JOIN user_info b ON a.uid = b.id
WHEREa.id =1;
根據查詢的結果在ArticleInfo 類補充相關的屬性：
@Data
public class ArticleInfo {private Integer id;private String title;private String content;private Integer uid;private Integer deleteFlag;private Date createTime;private Date updateTime;// 補充??相關信息private String username;private Integer age;private Integer gender;}
ArticleInfoMapper接口：
@Mapper
public interface ArticleInfoXMLMapper {List<ArticleInfo> queryArticleAndUser(Integer id);
}
ArticleInfoXMLMapper.xml文件：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.mybatis.mapper.ArticleInfoXMLMapper"><select id ="queryArticleAndUser" resultType="org.example.mybatis.model.ArticleInfo">select b.username, b.age, b.gender,a.id,a.title,a.content,a.uidfrom articleinfo a right join user_info b on a.uid=b.idwhere b.id = #{id}</select></mapper>
測試代碼：
@Slf4j
@SpringBootTest
class ArticleInfoXMLMapperTest {@Autowiredprivate ArticleInfoXMLMapper articleInfoXMLMapper;@Testvoid queryArticleAndUser() {articleInfoXMLMapper.queryArticleAndUser(1);}
}
運行結果：
如果名稱不?致的, 采?ResultMap, 或者別名的?式解決, 和單表查詢?樣 Mybatis 不分單表還是多表, 主要就是三部分: SQL, 映射關系和實體類
