Mybatis 多表间查询之 多对一
0. 描述:
关于是使用MyBatis多表之间的查询操作，案例中表t_category 是描述书的分类，表t_Book是现有书表，两个表之间通过cid字段建立连接，本次通过两种方式来实现 多对一的实现，关联和分步来实现，实际开发中是分布来实现的。
1. 表
CREATE TABLE `t_category` (
`cid` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`cname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`desc` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`cid`) USING BTREE,
UNIQUE INDEX `cname`(`cname`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `t_category` VALUES ('1', '程序设计', '程序设计分类');
INSERT INTO `t_category` VALUES ('3', '图形 图像 多媒体', '图形图像多媒体');
INSERT INTO `t_category` VALUES ('4', '操作系统/系统开发', '操作系统/系统开发');
INSERT INTO `t_category` VALUES ('458795C27E7346A8A5F1B942319297E0', '系统开发', '系统开发分类');
INSERT INTO `t_category` VALUES ('5', '数据库', '数据库');
INSERT INTO `t_category` VALUES ('57DE3C2DDA784B81844029A28217698C', 'Dreamweaver', 'Dreamweaver分类');
INSERT INTO `t_category` VALUES ('5F79D0D246AD4216AC04E9C5FAB3199E', 'Java Javascript', 'Java Javascript分类');
INSERT INTO `t_category` VALUES ('6', '网络与数据通讯', '网络与数据通讯!');
INSERT INTO `t_category` VALUES ('65830AB237EF428BAE9B7ADC78A8D1F6', 'Unix', 'Unix分类');
INSERT INTO `t_category` VALUES ('922E6E2DB04143D39C9DDB26365B3EE8', 'C C++ VC VC++', 'C C++ VC VC++分类');
CREATE TABLE `t_book` (
`bid` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`bname` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`author` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`price` decimal(8, 2) DEFAULT NULL,
`press` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`cid` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (`bid`) USING BTREE,
INDEX `FK_t_book_t_category`(`cid`) USING BTREE,
CONSTRAINT `FK_t_book_t_category` FOREIGN KEY (`cid`) REFERENCES `t_category` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `t_book` VALUES ('52B0EDFF966E4A058BDA5B18EEC698C4', '亮剑Java Web项目开发案例导航(含DVD光盘1张)', '朱雪琴', 69.00, '电子工业出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('5315DA60D24042889400AD4C93A37501', 'Spring 3.x企业应用开发实战(含CD光盘1张)', '陈雄华', 90.00, '电子工业出版社', '1');
INSERT INTO `t_book` VALUES ('56B1B7D8CD8740B098677C7216A673C4', '疯狂 Java 程序员的基本修养（《疯狂Java讲义》最佳拍档，扫清知识死角，夯实基本功）', '李刚', 59.00, '电子工业出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('57B6FF1B89C843C38BA39C717FA557D6', '了不起的Node.js: 将JavaScript进行到底（Web开发首选实时 跨多服务器 高并发）', 'Guillermo Rauch', 79.00, '电子工业出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('5C68141786B84A4CB8929A2415040739', 'JavaScript高级程序设计(第3版)(JavaScript技术名著，国内JavasScript第一书，销量超过8万册)', 'Nicholas C. Zakas', 99.00, '人民邮电出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('5EDB981339C342ED8DB17D5A198D50DC', 'Java程序性能优化', '葛一鸣', 59.00, '清华大学出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('A3D464D1D1344ED5983920B472826730', 'Java Web开发详解：XML+DTD+XML Schema+XSLT+Servlet 3 0+JSP 2 2深入剖析与实例应用(含CD光盘1张)', '孙鑫', 119.00, '电子工业出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('A46A0F48A4F649AE9008B38EA48FAEBA', 'Java编程全能词典(含DVD光盘2张)', '明日科技', 98.00, '电子工业出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('D0E69F85ACAB4C15BB40966E5AA545F1', 'Java并发编程实战（第16届Jolt大奖提名图书，Java并发编程必读佳作', 'Brian Goetz', 69.00, '机械工业出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('D2ABA8B06C524632846F27C34568F3CE', 'Java 经典实例', '达尔文', 98.00, '中国电力出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('D8723405BA054C13B52357B8F6AEEC24', '深入理解Java虚拟机：JVM高级特性与最佳实践（第2版）', '周志明', 79.00, '机械工业出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('DC36FD53A1514312A0A9ADD53A583886', 'JavaScript异步编程：设计快速响应的网络应用【掌握JavaScript异步编程必杀技，让代码更具响应度！ 】', 'Trevor Burnham ', 32.00, '人民邮电出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('DCB64DF0084E486EBF173F729A3A630A', 'Java设计模式(第2版)', 'Steven John Metsker', 75.00, '电子工业出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('DCB64DF0084E486EBF173F798A3A630A', 'C++ Primer中文版（第5版）', '(美)李普曼 等', 128.00, '电子工业出版社', '922E6E2DB04143D39C9DDB26365B3EE8');
INSERT INTO `t_book` VALUES ('DEE7BDC7E0E343149E3C3601D2658171', '疯狂HTML 5/CSS 3/JavaScript讲义(含CD光盘1张)', '李刚', 69.00, '电子工业出版社', '1');
INSERT INTO `t_book` VALUES ('DF4E74EEE89B43229BB8212F0B858C38', '精通Hibernate：Java对象持久化技术详解（第2版）(含光盘1张)', '孙卫琴', 75.00, '电子工业出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('EA695342393C4BE48B831FA5E6B0E5C4', '编写可维护的JavaScript《JavaScript高级程序设计》作者Nicholas Zakas最新力作，构建编码风格手册，帮助开发团队从“游击队”走向“正规军”）', 'Nicholas C. Zakas', 55.00, '人民邮电出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('F0E34313BF304CCEBF198BD4E05307B8', 'jQuery Cookbook中文版（jQuery之父鼎力推荐，社区数十位专家倾情力作', 'jQuery社区专家组', 69.00, '人民邮电出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('F6162799E913423EA5CB57BEC65AB1E9', 'JUnit实战(第2版)', '塔凯文', 79.00, '人民邮电出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('F693239BC3B3444C8538ABE7411BB38E', 'Java Web典型模块与项目实战大全（配光盘）', '常建功', 99.50, '清华大学出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('F78C94641DB4475BBA1E72A07DF9B3AE', 'JAVA面向对象编程', '孙卫琴 ', 65.80, '电子工业出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('FC232CD9B6E6411BBBB1A5B781D2C3C9', 'Java与模式(含盘)（超多实例和习题，详解设计原则与设计模式）', '阎宏', 88.00, '电子工业出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('FEC3740CF30E442A94021911A25EF0D7', 'Spring攻略(第2版)(Spring攻略(第2版))', 'Gary Mak Josh Long Daniel Rubio', 128.00, '人民邮电出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
INSERT INTO `t_book` VALUES ('FFABBED1E5254BC0B2726EC4ED8ACCDA', '深入理解OSGi：Equinox原理、应用与最佳实践（《深入理解Java虚拟机》作者新作！全面解读最新OSGi R5.0规范，深入讲解OSGi原理和服务，以及Equinox框架的用法和原理）', '周志明', 79.00, '机械工业出版社', '5F79D0D246AD4216AC04E9C5FAB3199E');
2. 项目搭建
1. 导入pom.xml
依赖的jar 表的插件
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
<configuration>
<source>11</source>
<target>11</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
</plugins>
</build>
</project>
2.Mybatis-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<!--SQL日志-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<environments default="development">
<environment id="development">
<!--事务管理器-->
<transactionManager type="JDBC"/>
<!--数据源-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/kdb2"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!-- 映射代码存放区 -->
</configuration>
3. Mybatis 连接工具类
public class MyBatisUtil {
private static final String resource = "mybatis-config.xml";
private static SqlSessionFactory sqlSessionFactory;
static {
try {
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
// ThreadLocal是一个和线程相关的容器，只要是在同一个线程中，获取到的必定是同一个对象
private static ThreadLocal<SqlSession> TL = new ThreadLocal<>();
public static SqlSession openSession() {
SqlSession sqlSession = TL.get();
// 当前线程第一次调用该方法
if (sqlSession == null) {
sqlSession = sqlSessionFactory.openSession();
TL.set(sqlSession);
}
return sqlSession;
}
public static void release(SqlSession sqlSession) {
if (sqlSession != null) {
sqlSession.close();
}
}
}
4. log4j 配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
<param name="Encoding" value="UTF-8" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L) \n" />
</layout>
</appender>
<logger name="java.sql">
<level value="debug" />
</logger>
<logger name="org.apache.ibatis">
<level value="info" />
</logger>
<root>
<level value="debug" />
<appender-ref ref="STDOUT" />
</root>
</log4j:configuration>
5.domain
public class Book {
private String bid;
private String bname;
private String author;
private Double price;
private String press;
private String cid;
// get/set method and toString
}
public class Category {
private String cid;
private String cname;
private String desc;
// get/set method and toString
}
1.关联查询的方式实现
1. 实体类 Book.java
2. 主配置文件 Mybatis-config.xml
</environments>
<mappers>
<mapper resource="cn/ccut/mapper/BookDao.xml" ></mapper>
</mappers>
3. 接口 IBookDao.java
public interface IBookDao {
List<Book> selectQuery();
}
4. BookDao.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">
<!--namespace的值和接口全限定名一致的 -->
<mapper namespace="cn.ccut.dao.IBookDao">
<resultMap id="books" type="cn.ccut.domain.Book">
<id property="bid" column="bid"/>
<result property="bname" column="bname"/>
<result property="author" column="author"/>
<result property="price" column="price"/>
<result property="press" column="press"/>
<result property="cid" column="cid"/>
<!-- property 和数据库表中的属性一样，column book表中关联字段 -->
<association property="category" column="cid" javaType="cn.ccut.domain.Category">
<id property="cid" column="tcid"/>
<result property="cname" column="tcname"></result>
<result column="desc" property="desc"></result>
</association>
</resultMap>
<select id="selectQuery" resultMap="books">
select t1.cid tcid,t1.cname tcname,t1.desc,t.* from t_book t join t_category t1 on t.cid=t1.cid and t.cid="5F79D0D246AD4216AC04E9C5FAB3199E"
</select>
</mapper>
5. 测试
public class testS {
@Test
public void fun(){
IBookDao mapper = MyBatisUtil.openSession().getMapper(IBookDao.class);
System.out.println( mapper.selectQuery());
}
}
正确数据:
[Book{bid='52B0EDFF966E4A058BDA5B18EEC698C4', bname='亮剑Java Web项目开发案例导航(含DVD光盘1张)', author='朱雪琴', price=69.0, press='电子工业出版社', cid='5F79D0D246AD4216AC04E9C5FAB3199E', category=Category{cid='5F79D0D246AD4216AC04E9C5FAB3199E', cname='Java Javascript', desc='Java Javascript分类'}
2. 分步查询
1.domain 不变
2. BookDao.xml
<!--抽取-->
<mapper namespace="cn.ccut.dao.IBookDao">
<resultMap id="books01" type="cn.ccut.domain.Book">
<id property="bid" column="bid"/>
<result property="bname" column="bname"/>
<result property="author" column="author"/>
<result property="price" column="price"/>
<result property="press" column="press"/>
<result property="cid" column="cid"/>
</resultMap>
<resultMap id="books" extends="books01" type="cn.ccut.domain.Book">
<association property="category" column="cid" javaType="cn.ccut.domain.Category" select="typeQuery">
</association>
</resultMap>
<select id="selectQuery" resultMap="books">
select * from t_book
</select>
<select id="typeQuery" resultType="cn.ccut.domain.Category">
select * from t_category where cid=#{cid11111}
</select>
</mapper>
最简化版本
<?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="cn.ccut.dao.IBookDao">
<resultMap id="books" type="cn.ccut.domain.Book">
<association property="category" column="cid" javaType="cn.ccut.domain.Category" select="categoryQuery"/>
</resultMap>
<select id="selectQuery" resultMap="books">
select * from t_book
</select>
<select id="categoryQuery" resultType="cn.ccut.domain.Category">
select * from t_category where cid=#{cid11111}
</select>
</mapper>
3. 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="cn.ccut.dao.IBookDao">
<resultMap id="books01" type="cn.ccut.domain.Book">
<id property="bid" column="bid"/>
<result property="bname" column="bname"/>
<result property="author" column="author"/>
<result property="price" column="price"/>
<result property="press" column="press"/>
<result property="cid" column="cid"/>
</resultMap>
<resultMap id="books" extends="books01" type="cn.ccut.domain.Book">
<association property="category" column="cid" javaType="cn.ccut.domain.Category">
<id property="cid" column="tcid"/>
<result property="cname" column="tcname"></result>
<result column="desc" property="desc"></result>
</association>
</resultMap>
<select id="selectQuery" resultMap="books">
select t1.cid tcid,t1.cname tcname,t1.desc,t.* from t_book t join t_category t1 on t.cid=t1.cid and t.cid="5F79D0D246AD4216AC04E9C5FAB3199E"
</select>
</mapper>
不停的思考，就会不停的进步
