1. 首页
  2. 文章中心
  3. JavaEE

【三】Mybatis

113 阅读 0 评论
我是靠谱客的博主 安静抽屉，这篇文章主要介绍【三】Mybatis，现在分享给大家，希望可以做个参考。

Table of Contents

 

一、动态 SQL 语句

1、标签：

2、标签：

3、标签

二、多表查询

1、一对多（用户与账户是一对多的关系）

一、动态 SQL 语句

1、<if>标签：

我们根据实体类的不同取值,使用不同的 SQL 语句来进行查询。比如在 id 如果不为空时可以根据 id 查询,如果 username 不为空时还要加入用户名作为条件

持久层 Dao 映射配置：

复制代码
1
2
3
4
5
6
7
8
9
<select id="findByUser" resultType="user" parameterType="user">
select * from user where 1=1
<if test="username!=null and username != '' ">
and username like #{username}
</if>
<if test="address != null">
and address like #{address}
</if>
</select>

2、<where>标签：

为了简化上面 where 1=1 的条件拼装,我们可以采用<where>标签来简化开发

持久层 Dao 映射配置：

复制代码
1
2
3
4
5
6
7
8
9
10
11
<select id="findUserByCondition" resultType="user" parameterType="user">
select * from user
<where>
<if test="username != null">
and username like #{username}
</if>
<if test="sex != null">
and sex = #{sex}
</if>
</where>
</select>

3、<foreach>标签

传入多个 id 查询多个用户信息

持久层 Dao 映射配置(QueryVo中有ids列表)：

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<select id="findUserInIds" resultType="user" parameterType="QueryVo">
select * from user
<where>
<if test="ids != null and ids.size()>0">
<foreach collection="ids" open="and id in (" close=")" item="uid" separator=",">
#{uid}
<--与item值相同-->
</foreach>
</if>
</where>
</select>
解释：
<foreach>标签用于遍历集合,它的属性:
collection:代表要遍历的集合元素,注意编写时不要写#{}
open:代表语句的开始部分
close:代表结束部分
item:代表遍历集合的每个元素,生成的变量名
sperator:代表分隔符

二、多表查询

1、一对多（用户与账户是一对多的关系）

复制代码
1
2
从表添加主表的一个对象

  account实体类

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
package com.zmz.domain;
public class Account {
private Integer id;
private Integer uid;
private Double money;
// 对于一对一的关系：添加主表的一个对象
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", uid=" + uid +
", money=" + money +
'}';
}
}
复制代码
1
2
IAccountDao映射文件:IAccountDao.xml
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?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.zmz.dao.IAccountDao">
<resultMap id="accountResultMap" type="account">
<id column="aid" property="id"/>
<result column="uid" property="uid"/>
<result column="money" property="money"/>
<!--关联的用户对象-->
<association property="user" column="uid" javaType="user">
<id property="id" column="id"/>
<result column="username" property="username"/>
<result column="address" property="address"/>
<result column="sex" property="sex"/>
<result column="birthday" property="birthday"/>
</association>
</resultMap>
<select id="findAllAccount" resultType="com.zmz.domain.AccountUser">
select a.*, u.username, u.address from account a left outer join user u on a.uid=u.id
</select>
<select id="findAllAccountUser" resultMap="accountResultMap">
select u.*,a.id as aid,a.uid,a.money from account a , user u where u.id = a.uid;
</select>
</mapper>
复制代码
1
主表实体应该包含从表实体的集合引用

  user实体类

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
package com.zmz.domain;
import java.util.Date;
import java.util.List;
public class User {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
//一对多关系映射：主表实体应该包含从表实体的集合引用
private List<Account> accounts;
public List<Account> getAccounts() {
return accounts;
}
public void setAccounts(List<Account> accounts) {
this.accounts = accounts;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + ''' +
", address='" + address + ''' +
", sex='" + sex + ''' +
", birthday=" + birthday +
'}';
}
}

 IUserDao接口的映射文件:IUserDao.xml

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?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.zmz.dao.IUserDao">
<!--定义user的resultMap-->
<resultMap id="userAccountMap" type="user">
<!--主键 property为类属性，column为表字段-->
<id property="id" column="id"/>
<!--非主键字段-->
<result property="username" column="username"/>
<result property="address" column="address"/>
<result property="sex" column="sex"/>
<result property="birthday" column="birthday"/>
<!--配置user对象中account集合的映射-->
<!--collection:用于建立一对多中集合属性的对应关系ofType:用于指定集合元素的数据类型-->
<collection property="accounts" ofType="account">
<id column="id" property="id"/>
<result property="uid" column="uid"/>
<result property="money" column="money"/>
</collection>
</resultMap>
<select id="findAll" resultMap="userAccountMap">
select * from user u left outer join account a on u.id = a.uid
</select>
</mapper>

注意：映射文件一定要配置 resultMap 

2、一对多（用户与账户是一对多的关系）

用户和角色是多对多的关系

多对多关系需要在各自的JavaBean中添加对方对象的列表

User对象

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
package com.itheima.domain;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
/**
* @author 黑马程序员
* @Company http://www.ithiema.com
*/
public class User implements Serializable {
private Integer id;
private String username;
private String address;
private String sex;
private Date birthday;
//多对多的关系映射：一个用户可以具备多个角色
private List<Role> roles;
public List<Role> getRoles() {
return roles;
}
public void setRoles(List<Role> roles) {
this.roles = roles;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + ''' +
", address='" + address + ''' +
", sex='" + sex + ''' +
", birthday=" + birthday +
'}';
}
}

IUserDao接口的映射文件IUserDao.xml

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<?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.itheima.dao.IUserDao">
<!-- 定义User的resultMap-->
<resultMap id="userMap" type="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="address" column="address"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
<!-- 配置角色集合的映射 -->
<collection property="roles" ofType="role">
<id property="roleId" column="rid"></id>
<result property="roleName" column="role_name"></result>
<result property="roleDesc" column="role_desc"></result>
</collection>
</resultMap>
<!-- 查询所有 -->
<select id="findAll" resultMap="userMap">
select u.*,r.id as rid,r.role_name,r.role_desc from user u
left outer join user_role ur
on u.id = ur.uid
left outer join role r on r.id = ur.rid
</select>
<!-- 根据id查询用户 -->
<select id="findById" parameterType="INT" resultType="user">
select * from user where id = #{uid}
</select>
</mapper>

Role对象

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package com.itheima.domain;
import java.io.Serializable;
import java.util.List;
/**
* @author 黑马程序员
* @Company http://www.ithiema.com
*/
public class Role implements Serializable {
private Integer roleId;
private String roleName;
private String roleDesc;
//多对多的关系映射：一个角色可以赋予多个用户
private List<User> users;
public List<User> getUsers() {
return users;
}
public void setUsers(List<User> users) {
this.users = users;
}
public Integer getRoleId() {
return roleId;
}
public void setRoleId(Integer roleId) {
this.roleId = roleId;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
@Override
public String toString() {
return "Role{" +
"roleId=" + roleId +
", roleName='" + roleName + ''' +
", roleDesc='" + roleDesc + ''' +
'}';
}
}

IRoleDao接口的映射文件：IRoleDao.xml

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?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.itheima.dao.IRoleDao">
<!--定义role表的ResultMap-->
<resultMap id="roleMap" type="role">
<id property="roleId" column="rid"></id>
<result property="roleName" column="role_name"></result>
<result property="roleDesc" column="role_desc"></result>
<collection property="users" ofType="user">
<id column="id" property="id"></id>
<result column="username" property="username"></result>
<result column="address" property="address"></result>
<result column="sex" property="sex"></result>
<result column="birthday" property="birthday"></result>
</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>
</mapper>

 

最后

以上就是安静抽屉最近收集整理的关于【三】Mybatis的全部内容，更多相关【三】Mybatis内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(75)

相关文章

在mybatis中#{}和${}的区别，使用场景及sql注入转载：https://blog.csdn.net/ideality_hunter/article/details/80623526sql注入，一个例子让你知道什么是sql注入
在mybatis中#{}和${}的区别，使用场景及sql注入转载：https://blog.csdn.net/ideality_hunter/article/details/80623526sql注入，一个例子让你知道什么是sql注入
Mybatis实现动态SQL
Mybatis实现动态SQL
MyBatis中#{}和${}1 #{}和${}的区别2 示例
MyBatis中#{}和${}1 #{}和${}的区别2 示例
MyBatis中防止 sql注入及#{ }和${ }的区别
MyBatis中防止 sql注入及#{ }和${ }的区别
【三】Mybatis
【三】Mybatis
mybatis中的sql语句中#{}和${}的区别
mybatis中的sql语句中#{}和${}的区别
SpringMVC 学习笔记(五) 基于RESTful的CRUD
SpringMVC 学习笔记(五) 基于RESTful的CRUD
使用js实现简单地对表格添加行和删除行
使用js实现简单地对表格添加行和删除行

评论列表共有 0 条评论

立即
投稿
返回
顶部