1 MyBatis 的事务控制
在 MyBatis 中事务不是自动提交的主要原因是从连接池取出连接,会调connection.setAutocommit(false)
方法,使得在默认情况下,需要手动提交事务。设置自动事务提交的方法:创建 SqlSession 对象时使用sqlSessionFactory.openSession(true)
方法实现事务的自动提交。
2 MyBatis 的动态 SQL 语句
2.1 <if> 标签
2.1.1 持久层 Dao 接口
List<User> findByUser(User user);
2.1.2 映射配置
<select id="findByUser" resultType="user" parameterType="user">
SELECT * from user USER WHERE 1 = 1
<!-- test属性中写对象属性名-->
<if test="username != null and username != '' ">
AND username LIKE #{username}
</if>
<if test="address != null">
AND address like #{address}
</if>
</select>
2.1.3 测试
@Test
public void testFindByUser(){
User u = new User();
u.setUsername("%甲%");
u.setAddress("%北");
List<User> users = userDao.findByUser(u);
for (User user : users) {
System.out.println(user);
}
}
返回结果
User{id=41, username='甲', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}
其中返回结果显示的 SQL 语句为SELECT * from user USER WHERE 1 = 1 AND username LIKE ? AND address like ?
2.2 <where> 标签
2.2.1 映射配置
<sql id="defaultSql">
SELECT * FROM USER
</sql>
<select id="findByUser" resultType="user" parameterType="user">
<include refid="defaultSql"></include>
<where>
<if test="username != null and username != ''" >
and username like #{username}
</if>
<if test="address != null">
and address like #{address}
</if>
</where>
</select>
2.2. 2 测试
@Test
public void testFindByUser(){
User u = new User();
u.setUsername("%甲%");
u.setAddress("%北");
List<User> users = userDao.findByUser(u);
for (User user : users) {
System.out.println(user);
}
}
返回结果
User{id=41, username='甲', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}
返回结果中的 SQL 语句
SELECT * FROM USER WHERE username like ? and address like ?
2.3 <foreach> 标签
查询需要传入多个用户信息时,需要封装参数,进行参数传递。例如:SELECT * FROM USERS WHERE username LIKE '%张%' AND id IN (10,89,16)
2.3.1 QueryIdVo 封装类
public class QueryIdVo implements Serializable {
private List<Integer> ids;
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
}
2.3.2 持久层 Dao 接口
List<User> findInIds(QueryIdVo vo);
2.3.3 映射配置
<select id="findInIds" resultType="user" parameterType="cn.pine.domain.QueryIdVo">
<include refid="defaultSql"></include>
<where>
<if test="ids != null and ids.size() > 0">
<foreach collection="ids" open="id in (" close=")" item="uid" separator=",">
#{uid}
</foreach>
</if>
</where>
</select>
2.3.4 测试
@Test
public void testFindByInIds(){
QueryIdVo vo = new QueryIdVo();
List<Integer> ids = new ArrayList<Integer>();
ids.add(41);
ids.add(45);
ids.add(42);
vo.setIds(ids);
List<User> users = userDao.findInIds(vo);
for (User user : users) {
System.out.println(user);
}
}
返回结果
User{id=41, username='王一', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}
User{id=42, username='张三', birthday=Fri Mar 02 15:09:37 CST 2018, sex='女', address='上海'}
User{id=45, username='李四', birthday=Sun Mar 04 12:04:06 CST 2018, sex='男', address='深圳'}
返回的SQL语句
SELECT * FROM USER WHERE id in ( ? , ? , ? )
3 MyBatis 多表查询
3.1 多对一查询
以用户(User)和账户(Account)为模型,一个用户可以拥有多个账户,从账户信息出发关联查询用户信息为多对一查询,但是本质上是一对一查询,因为一个账户只能对应一个用户。而从用户出发,查新用户下的账户信息为一对多查询,因为一个用户对应多个账户。
3.1.1 以账户关联查询用户方式一
3.1.1.1 账户信息实体类
//省略getter,setter和toString方法
public class Account implements Serializable {
private Integer id;
private Integer uid;
private Double money;
}
3.1.1.2 AccountUser 类
定义 AccountUser 类来封装关联查询结果,要求包含账户信息与用户信息。
//省略getter和setter方法
public class AccoutUser extends Account implements Serializable {
private String username;
private String address;
@Override
public String toString() {
return super.toString() + "AccoutUser{" +
"username='" + username + '\'' +
", address='" + address + '\'' +
'}';
}
}
3.1.1.3 账户的持久层 Dao 接口
public interface IAccountDao {
List<AccoutUser> findAll();
}
3.1.1.4 AccountDao.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.pine.dao.IAccountDao">
<select id="findAll" resultType="cn.pine.domain.AccoutUser">
SELECT a.*,u.username,u.address FROM account a, user u WHERE a.uid = u.id;
</select>
</mapper>
3.1.1.6 测试
Acoount表:
id | uid | money |
---|---|---|
1 | 46 | 1000 |
2 | 45 | 1000 |
3 | 46 | 2000 |
user表:
id | username | birthday | sex | address |
---|---|---|---|---|
41 | 王一 | 27/2/2018 17:47:08 | 男 | 北京 |
42 | 张三 | 2/3/2018 15:09:37 | 女 | 上海 |
43 | 张五 | 4/3/2018 11:34:34 | 女 | 广州 |
45 | 李四 | 4/3/2018 12:04:06 | 男 | 深圳 |
46 | 赵六 | 7/3/2018 17:37:26 | 男 | 杭州 |
48 | 钱七 | 8/3/2018 11:44:00 | 女 | 成都 |
@Test
public void testFindAll(){
List<AccoutUser> accoutUsers = accountDao.findAll();
for (AccoutUser accoutUser : accoutUsers) {
System.out.println(accoutUser);
}
}
返回结果
Account{id=1, uid=46, money=1000.0}AccoutUser{username='赵六', address='杭州'}
Account{id=2, uid=45, money=1000.0}AccoutUser{username='李四', address='深圳'}
Account{id=3, uid=46, money=2000.0}AccoutUser{username='赵六', address='杭州'}
3.1.2 以账户关联查询用户方式二
通过在 Account 类中加入 User 属性,Dao 接口方法返回值修改为List<Acoount>
。
最后在映射配置中通过 resultMap
建立对应关系,如下:
<resultMap type="account" id="accountMap">
<id column="aid" property="id"/>
<result column="uid" property="uid"/>
<result column="money" property="money"/>
<!-- 它是用于指定从表方的引用实体属性的 -->
<association property="user" javaType="user">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="findAll" resultMap="accountMap">
select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid =u.id;
</select>
3.2 一对多查询
查询用户信息及其关联的账户信息,为一对多查询。(使用做外链接,因为可能有的用户没有账户信息)
3.2.1 User 类中加入List<Account>
属性
//省略了getter,setter和toString方法
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
private List<Account> accounts;
}
3.2.2 用户持久层 Dao 接口加入查询方法
List<User> findAllUserAccount();
3.2.3 映射配置
<resultMap id="userMap" type="user">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="address" property="address"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
<!-- collection 定义账户信息
property 关联查询User对象的属性
ofType 指定List中的对象类型-->
<collection property="accounts" ofType="cn.pine.domain.Account">
<id column="aid" property="id"/>
<result column="uid" property="uid"/>
<result column="money" property="money"/>
</collection>
</resultMap>
<select id="findAllUserAccount" resultMap="userMap">
SELECT u.*, a.id as aid, a.uid, a.money FROM USER u LEFT OUTER JOIN account a on u.id=a.uid
</select>
3.2.4 测试
@Test
public void testFindAllUserAccount(){
List<User> users = userDao.findAllUserAccount();
for (User user : users) {
System.out.println("------每个用户的内容----------");
System.out.println(user);
System.out.println(user.getAccounts());
}
}
}
返回结果
------每个用户的内容----------
User{id=41, username='王一', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}
[]
------每个用户的内容----------
User{id=42, username='张三', birthday=Fri Mar 02 15:09:37 CST 2018, sex='女', address='上海'}
[]
------每个用户的内容----------
User{id=43, username='张五', birthday=Sun Mar 04 11:34:34 CST 2018, sex='女', address='广州'}
[]
------每个用户的内容----------
User{id=45, username='李四', birthday=Sun Mar 04 12:04:06 CST 2018, sex='男', address='深圳'}
[Account{id=2, uid=45, money=1000.0}]
------每个用户的内容----------
User{id=46, username='赵六', birthday=Wed Mar 07 17:37:26 CST 2018, sex='男', address='杭州'}
[Account{id=1, uid=46, money=1000.0}, Account{id=3, uid=46, money=2000.0}]
------每个用户的内容----------
User{id=48, username='钱七', birthday=Thu Mar 08 11:44:00 CST 2018, sex='女', address='成都'}
[]
3.3 多对多查询
3.3.1 用户与角色的关系模型
一个角色(Role)可以对应多个用户,一个用户(User)可对应多个角色,形成多对多关系。
通过查询角色关联查询所需要的 SQL 语句:
SELECT
r.*,u.id uid,
u.username username,
u.birthday birthday,
u.sex sex,
u.address address
FROM
ROLE r
INNER JOIN
USER_ROLE ur
ON ( r.id = ur.rid)
INNER JOIN
USER u
ON (ur.uid = u.id);
4.1.2 角色实体类
//省略getter,setter,toString方法
public class Role implements Serializable {
private Integer roleId;
private String roleName;
private String roleDesc;
private List<User> users;
}
4.1.3 角色持久层 Dao 接口
public interface IRoleDao {
List<Role> findAll();
}
4.1.4 映射配置
<resultMap id="roleMap" type="cn.pine.domain.Role">
<id column="rid" property="roleId"/>
<result column="role_name" property="roleName"/>
<result column="role_desc" property="roleDesc"/>
<collection property="users" ofType="cn.pine.domain.User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="address" property="address"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
</collection>
</resultMap>
<select id="findAll" resultMap="roleMap">
SELECT u.*, r.id as rid, r.role_name, r.role_desc from role r LEFT outer JOIN user_role ur on r.id = ur.rid left outer join user u on u.id=ur.uid
</select>
4.1.5 测试
//获得的动态代理对象应为session.getMapper(IRoleDao.class)
@Test
public void testFindAll() {
List<Role> roles = roleDao.findAll();
for (Role role : roles) {
System.out.println("======每个角色的信息========");
System.out.println(role);
System.out.println(role.getUsers());
}
}
返回结果
======每个角色的信息========
Role{roleId=1, roleName='院长', roleDesc='管理整个学院', users=[User{id=41, username='王一', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}, User{id=45, username='李四', birthday=Sun Mar 04 12:04:06 CST 2018, sex='男', address='深圳'}]}
[User{id=41, username='王一', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}, User{id=45, username='李四', birthday=Sun Mar 04 12:04:06 CST 2018, sex='男', address='深圳'}]
======每个角色的信息========
Role{roleId=2, roleName='总裁', roleDesc='管理整个公司', users=[User{id=41, username='王一', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}]}
[User{id=41, username='王一', birthday=Tue Feb 27 17:47:08 CST 2018, sex='男', address='北京'}]
======每个角色的信息========
Role{roleId=3, roleName='校长', roleDesc='管理整个学校', users=[]}
[]