MyBatis多表查詢的操作方法注解和XML實現(xiàn)SQL語句
1.準備工作
1.1創(chuàng)建數(shù)據(jù)庫
(1)創(chuàng)建數(shù)據(jù)庫:
CREATE DATABASE mybatis_test DEFAULT CHARACTER SET utf8mb4;
(2)使用數(shù)據(jù)庫
-- 使?數(shù)據(jù)數(shù)據(jù) USE mybatis_test;
1.2 創(chuàng)建用戶表和實體類
創(chuàng)建用戶表
-- 創(chuàng)建表[??表] 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 創(chuàng)建文章表和實體類
上?建了?張??表, 我們再來建?張?章表, 進?多表關聯(lián)查詢.
?章表的uid, 對應??表的id.
創(chuàng)建文章表:
-- 創(chuàng)建?章表 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';
插入數(shù)據(jù)
-- 插?測試數(shù)據(jù) 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
文件中配置:
# 數(shù)據(jù)庫連接配置 spring: datasource: # MySQL在遠程服務器上 url: jdbc:mysql://x.x.x.x:3306/mybatis_test?characterEncoding=utf8&useSSL=false username: root #MySQL賬號 password: root #MySQL密碼 driver-class-name: com.mysql.cj.jdbc.Driver mybatis: configuration: # 配置打印 MyBatis?志 log-impl: org.apache.ibatis.logging.stdout.StdOutImpl map-underscore-to-camel-case: true #配置駝峰?動轉換 # 配置 mybatis xml 的?件路徑,在 resources/mapper 創(chuàng)建所有表的 xml ?件 mapper-locations: classpath:mapper/**Mapper.xml
2.多表查詢
2.1 需求: 根據(jù)uid查詢書的作者等相關信息
2.1.1 注解實現(xiàn)
SQL命令:
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 =1;
根據(jù)查詢的結果在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 { @Autowired private ArticleInfoMapper articleInfoMapper; @Test void queryArticleAndUser() { articleInfoMapper.queryArticleAndUser(1); } }
運行結果:
如果名稱不?致的, 采?Results, 或者別名的?式解決, 和單表查詢?樣 Mybatis 不分單表還是多表, 主要就是三部分: SQL, 映射關系和實體類
2.1.2 XML實現(xiàn)
SQL命令:
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 =1;
根據(jù)查詢的結果在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.gender from articleinfo a left join user_info b on a.uid=b.id where a.id = #{id} </select> </mapper>
測試代碼:
@Slf4j @SpringBootTest class ArticleInfoXMLMapperTest { @Autowired private ArticleInfoXMLMapper articleInfoXMLMapper; @Test void queryArticleAndUser() { articleInfoXMLMapper.queryArticleAndUser(1); } }
運行結果:
如果名稱不?致的, 采?ResultMap, 或者別名的?式解決, 和單表查詢?樣 Mybatis 不分單表還是多表, 主要就是三部分: SQL, 映射關系和實體類
2.2 需求: 根據(jù)user_in的id查詢作者創(chuàng)作的書相關信息
2.1.1 注解實現(xiàn)
SQL命令:
SELECT b.username, b.age, b.gender, a.id, a.title, a.content, a.uid FROM articleinfo a RIGTH JOIN user_info b ON b.uid = a.id WHERE b.id =1;
根據(jù)查詢的結果在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 { @Autowired private ArticleInfoMapper articleInfoMapper; @Test void queryArticleAndUser() { articleInfoMapper.queryArticleAndUser(1); } }
運行結果:
如果名稱不?致的, 采?Results, 或者別名的?式解決, 和單表查詢?樣 Mybatis 不分單表還是多表, 主要就是三部分: SQL, 映射關系和實體類
2.1.2 XML實現(xiàn)
SQL命令:
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 a.id =1;
根據(jù)查詢的結果在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.uid from articleinfo a right join user_info b on a.uid=b.id where b.id = #{id} </select> </mapper>
測試代碼:
@Slf4j @SpringBootTest class ArticleInfoXMLMapperTest { @Autowired private ArticleInfoXMLMapper articleInfoXMLMapper; @Test void queryArticleAndUser() { articleInfoXMLMapper.queryArticleAndUser(1); } }
運行結果:
如果名稱不?致的, 采?ResultMap, 或者別名的?式解決, 和單表查詢?樣 Mybatis 不分單表還是多表, 主要就是三部分: SQL, 映射關系和實體類
到此這篇關于MyBatis多表查詢的操作方法注解和XML實現(xiàn)SQL語句的文章就介紹到這了,更多相關MyBatis多表查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
Java并發(fā)LinkedBlockingQueue源碼分析
這篇文章主要為大家介紹了Java并發(fā)LinkedBlockingQueue源碼分析,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪2023-02-02springboot整合日志處理Logback的實現(xiàn)示例
Logback是由log4j創(chuàng)始人設計的又一個開源日志組件,本文主要介紹了springboot整合日志處理Logback,文中通過示例代碼介紹的非常詳細,需要的朋友們下面隨著小編來一起學習學習吧2024-01-01詳解SpringMVC組件之HandlerMapping(一)
這篇文章主要介紹了詳解SpringMVC組件之HandlerMapping(一),HandlerMapping組件是Spring?MVC核心組件,用來根據(jù)請求的request查找對應的Handler,在Spring?MVC中,有各式各樣的Web請求,每個請求都需要一個對應的Handler來處理,需要的朋友可以參考下2023-08-08IDEA?報Plugin'maven-resources-plugin:'not?found?
如果在使用?IDEA?時遇到?"Plugin?'maven-resources-plugin:'?not?found"?錯誤,可能是由于?Maven?倉庫中未找到所需的?Maven?插件,近小編給大家分享幾種解決方法,感興趣的朋友跟隨小編一起看看吧2023-07-07