一、Mybatis多表查询
1.一对一关系
概述:
一对一其实没什么好说的,因为这是比较容易理解的就选择一个账户只有一个用户即,一个account只有一个user来做案例
接口:
package com.ps.dao;
import com.ps.pojo.Account;
import java.util.List;
public interface AccountDao {
List<Account> findAllAccount();
}
接口映射配置:
<?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="com.ps.dao.AccountDao">
<resultMap id="allTable" type="account">
<id column="accountID" property="accountID"></id>
<result column="uid" property="uid"></result>
<result column="money" property="money"></result>
<!--association用来封装1对1关联查询的数据-->
<!--
property:设置实体类的属性
javaType:设置属性的类型
-->
<association property="user" javaType="user">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</association>
</resultMap>
<!-- 这里其实只要配置好一对一的映射关系即可 -->
<select id="findAllAccount" resultMap="allTable">
<!-- 通过多表连接查询获得数据,然后根据配置好的映射关系把数据存进去 -->
select * from account a inner join user u on a.uid = u.id;
</select>
</mapper>
实体类:
package com.ps.pojo;
import java.util.List;
public class User {
private int id;
private String username;
private String birthday;
private char sex;
private String address;
}
package com.ps.pojo;
public class Account {
private int accountID;
private int uid;
private double money;
private User user;
}
测试代码:
package com.ps.dao;
import com.ps.pojo.Account;
import com.ps.pojo.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class MybatisTest {
private static SqlSession session = null;
private static SqlSessionFactory build = null;
@BeforeClass
public static void init() throws Exception {
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
build = builder.build(in);
}
@Before
public void getSqlSession() {
//如果在这里设置为true,mybatis就会自动提交事务
session = build.openSession();
}
@Test
public void demo10() throws Exception {
AccountDao mapper = session.getMapper(AccountDao.class);
List<Account> list = mapper.findAllAccount();
for (Account account : list) {
System.out.println(account);
}
}
@After
public void close() throws Exception {
if (session != null) {
session.close();
}
}
}
2.一对多关系
概述:
一对多关系可以其实更好理解了,如果你理解了上面的例子,那么反过来呢?一个用户可以注册多个账号,就像你可以有多个QQ号一样。
实体类:
package com.ps.pojo;
import java.util.List;
public class User {
private int id;
private String username;
private String birthday;
private char sex;
private String address;
//存放账号信息的集合
private List<Account> AccountList;
}
package com.ps.pojo;
public class Account {
private int accountID;
private int uid;
private double money;
}
接口映射文件:
<?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="com.ps.dao.UserMapper">
<!-- 为了安全起见,把所有映射关系都配置好,避免获取不到的情况 -->
<resultMap id="findUserAccount" type="user">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
<!-- collection就是用来映射集合的标签 -->
<!--封装Account表数据
collection标签的作用:用来封装一对多关联查询的数据
property:要映射的实体类属性名
javaType:要映射的属性的类型
ofType:集合元素的类型
-->
<collection property="AccountList" javaType="list" ofType="account">
<id column="accountID" property="accountID"></id>
<result column="uid" property="uid"></result>
<result column="money" property="money"></result>
</collection>
</resultMap>
<select id="findAllUserAndAccount" resultMap="findUserAccount">
<!-- 通过多表连接查询获得数据,然后根据配置好的映射关系把数据存进去 -->
select * from user u LEFT join account ac on u.id=ac.uid
</select>
</mapper>
测试代码:
@Test
public void demo11() throws Exception {
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> list = mapper.findAllUserAndAccount();
for (User account : list) {
System.out.println(account);
}
}
3.多对多关系
概述:
多对多的关系,其实就是两个1对多的关系来组成的,亦或者说是由一个中间表或关系表来帮助构建的。
就比如玩游戏由很多个角色,你可以同时有很多个角色,这个角色也可以有很多人在玩。
多对多其实和一对多的差别不是很大,因为在显示的时候只能以某一个为主,一对多表示的是多个账户不会有相同的,也就是意味着账户不可共享。
实体类代码:
package com.ps.pojo;
import java.util.List;
public class User {
private int id;
private String username;
private String birthday;
private char sex;
private String address;
}
package com.ps.pojo;
import java.util.List;
public class Role {
private int rid;
private String rname;
private String rDesc;
//存放用户的集合,如果需要查看用户对角色,只要在用户表加一个集合,然后稍微修改以下sql语句即可
private List<User> userList;
}
接口:
package com.ps.dao;
import com.ps.pojo.Role;
import java.util.List;
public interface RoleDao {
List<Role> findAll();
}
接口映射文件:
<?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="com.ps.dao.RoleDao">
<resultMap id="RoleAndUser" type="role">
<id column="rid" property="rid"></id>
<result column="rname" property="rname"></result>
<result column="rDesc" property="rDesc"></result>
<collection property="userList" javaType="list" ofType="user">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="RoleAndUser">
SELECT u.*,r.* FROM role r LEFT JOIN relationship rs ON r.rid = rs.rid
LEFT JOIN USER u ON u.id = rs.uid
</select>
</mapper>
测试代码:
@Test
public void demo12() throws Exception {
RoleDao mapper = session.getMapper(RoleDao.class);
List<Role> list = mapper.findAll();
for (Role role : list) {
System.out.println(role);
}
}
二、Mybatis延迟加载
概述:
在需要用到数据的时候才加载,不需要用的时候不加载,也被叫做懒加载;而我们常用的加载方式也被称作及时加载
优点:
先从单表查询,需要时再从关联表去关联查询,大大提高数据库性能,因为查询单表要比关联查询多张表速度要快。
缺点:
因为只有当需要用到数据时,才会进行数据库查询,这样在大批量数据查询时,因为查询也要消耗时间,所以可能造成用户等待时间变长,造成用户体验下降。
1.一对一延迟加载
开启延迟加载:
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
</settings>
配置接口映射文件:
<?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="com.ps.dao.AccountDao">
<resultMap id="accountMap" type="account">
<id column="accountID" property="accountID"></id>
<result column="uid" property="uid"></result>
<result column="money" property="money"></result>
<!--用来封装1对1关联查询的数据-->
<!--
property:设置实体类的属性
javaType:设置属性的类型
-->
<!--封装User表数据:使用延迟加载策略
association标签的作用:用来设置一对一查询
select属性:设置延迟加载数据时要执行的SQL语句,格式:namespace+"."+id
column属性:设置字段名,该字段的值会作为参数传递给select属性关联的方法
-->
<association property="user" javaType="user" column="uid"
select="com.ps.dao.UserMapper.findUserById">
</association>
</resultMap>
<select id="findUser" resultMap="accountMap">
select * from account
</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="com.ps.dao.UserMapper">
<select id="findUserById" resultType="user">
select * from user where id=#{id}
</select>
</mapper>
测试代码:
package com.ps.dao;
import com.ps.pojo.Account;
import com.ps.pojo.Role;
import com.ps.pojo.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class LazyTest {
private static SqlSession session = null;
private static SqlSessionFactory build = null;
@BeforeClass
public static void init() throws Exception {
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
build = builder.build(in);
}
@Before
public void getSqlSession() {
//如果在这里设置为true,mybatis就会自动提交事务
session = build.openSession();
}
@After
public void close() throws Exception {
if (session != null) {
session.close();
}
}
@Test
public void testO2O() {
AccountDao mapper = session.getMapper(AccountDao.class);
List<Account> list = mapper.findUser();
for (Account account : list) {
//这条语句没有调用user所以不会触发查询user的代码
System.out.println(account.getMoney());
//这里用到了user,所以会自动加载
System.out.println(account.getUser());
}
}
}
2.一对多延迟加载
接口映射配置:
<?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="com.ps.dao.AccountDao">
<select id="findAccountByID" resultType="account" parameterType="int">
select * from account where uid=#{uid}
</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="com.ps.dao.UserMapper">
<resultMap id="FUAA" type="user">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
<!--2. 封装Account表数据
collection标签的作用:用来封装一对多关联查询的数据
property:要映射的实体类属性名
javaType:要映射的属性的类型
ofType:集合元素的类型
-->
<collection property="AccountList" javaType="list" ofType="account" column="id"
select="com.ps.dao.AccountDao.findAccountByID">
</collection>
</resultMap>
<select id="findUserAndAccount" resultMap="FUAA">
select * from user
</select>
</mapper>
测试代码:
package com.ps.dao;
import com.alibaba.druid.support.json.JSONUtils;
import com.ps.pojo.Account;
import com.ps.pojo.Role;
import com.ps.pojo.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class LazyTest {
private static SqlSession session = null;
private static SqlSessionFactory build = null;
@BeforeClass
public static void init() throws Exception {
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
build = builder.build(in);
}
@Before
public void getSqlSession() {
//如果在这里设置为true,mybatis就会自动提交事务
session = build.openSession();
}
@After
public void close() throws Exception {
if (session != null) {
session.close();
}
}
@Test
public void testO2N() {
UserMapper mapper = session.getMapper(UserMapper.class);
List<User> userList = mapper.findUserAndAccount();
for (User user : userList) {
System.out.println(user.toString());
System.out.println(user.getUsername());
}
}
}
注意:
这里的一对多其实和之前的一对多的实体类是一样的,其实你两个都有也可以,也是两边互相有地方存彼此的索引,但是这样的话就必须全部配置赋值了,不然的话非常容易导致null值;
3.注解实现一对一、一对多延迟加载
概述:
其实写了这么多会发现,其实xml配置和注解配置没有什么区别,大部分都很相同,对于每个标签就是各种注解,把xml配置中的东西各个赋值给注解,所以接下来我就将只给出注解
一对一:
@Select("select * from account")
@Results({
@Result(column = "accountID",property = "accountID",id =true),
@Result(column = "uid",property = "uid"),
@Result(column = "money",property = "money"),
@Result(one = @One(select = "com.ps.dao.UserMapper.findUserById")
,column = "uid",property = "user"),
})
List<Account> findAccount();
一对多:
@Select("select * from user")
@Results({
@Result(column = "id",property = "id",id =true),
@Result(column = "username",property = "username"),
@Result(column = "birthday",property = "birthday"),
@Result(column = "sex",property = "sex"),
@Result(column = "address",property = "address"),
@Result(one = @One(select = "com.ps.dao.AccountDao.findAccountByID")
,column = "id",property = "AccountList")
})
List<User> findUserAndAccount();
前两篇文章看这里: