Mybatis 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.zyh.mybatis.mapper.UserMapper">
</mapper>
一 Mybatis单表动态sql
.普通CRUD
<!--查询所有-->
<select id="getUserList" resultType="com.zyh.mybatis.pojo.User">
select * from user
</select>
<!--插入一个用户-->
<insert id="insertUser" parameterType="com.zyh.mybatis.pojo.User">
insert into user(username,address,sex,birthday) values (#{username},#{address},#{sex},#{birthday})
</insert>
<!--更新用户-->
<update id="updataUser" parameterType="com.zyh.mybatis.pojo.User">
update user
<set>
<if test="username!=null">
username=#{username},
</if>
<if test="birthday!=null">
birthday=#{birthday},
</if>
<if test="sex!=null">
sex=#{sex},
</if>
<if test="address!=null">
address=#{address},
</if>
</set>
where id=#{id}
</update>
<!--根据id删除用户-->
<delete id="deleteUserById" parameterType="integer">
delete from user where id=#{id}
</delete>
<!--根据id查询用户-->
<select id="findUserById" parameterType="integer" resultType="user">
select * from user where id =#{id}
</select>
<!--查询用户数量-->
<select id="findCountUsers" parameterType="integer" resultType="integer">
select count(*) from user
</select>
2 .模糊查询
<!--根据名字模糊查询用户-->
<select id="findUserByName" parameterType="string" resultType="user">
select * from user where username like #{name}
</select>
<!--根据名字模糊查询用户方法二 固定占位符-->
<select id="findUserByNameMethods2" parameterType="string" resultType="user">
select * from user where username like '${value}'
</select>
3.bean中属性名和数据库列名不同
方法1.起别名
<!--查询所有-->
<select id="getUserList" resultType="com.zyh.mybatis.pojo.User">
select id as userId, username as userName, address as userAddress,
sex as userSex,birthday as userBirthday from user;
</select>
方法2.配置resultmap 键值对应关系
<!-- 配置 查询结果的列名和实体类的属性名的对应关系 -->
<resultMap id="userMap" type="uSeR">
<!-- 主键字段的对应 -->
<id property="userId" column="id"></id>
<!--非主键字段的对应-->
<result property="userName" column="username"></result>
<result property="userAddress" column="address"></result>
<result property="userSex" column="sex"></result>
<result property="userBirthday" column="birthday"></result>
</resultMap>
<!-- 查询所有 -->
<select id="findAll" resultMap="userMap">
select * from user;
</select>
动态sql练习
where if
<!--根据传入的条件进行查询-->
<select id="selectByCondition" parameterType="user" resultType="user">
select * from user
<where>
<if test="username!=null">
username=#{username}
</if>
<if test="birthday!=null">
and birthday=#{birthday}
</if>
<if test="sex!=null">
and sex=#{sex}
</if>
<if test="address!=null">
and address=#{address}
</if>
</where>
</select>
foreach
查询指定区间id的用户名字
<select id="selectByIdList" parameterType="list" resultType="String">
select username from user
<where>
<if test="list!=null and list.size()>0">
<foreach collection="list" open="id in (" close=")" separator="," item="item" index="index">
#{item}
</foreach>
</if>
</where>
</select>
sql拼接
<sql id="getAllUser">
select * from user
</sql>
<!--查询所有-->
<select id="getUserList" resultType="com.zyh.mybatis.pojo.User">
<include refid="getAllUser"/> <!--嵌入sql-->
</select>
二 Mybatis多表
表之间的关系
- 1对1
一个用户有一个名字 - 1对多
一个用户可以有很多外号 - 多对1
多个外号可能属于同一个人,其属于一个特殊的一对一,因为每个外号其实都对应着一个人 - 多对多
多个用户可以有多个名字,也可以有多个外号
多表动态sql
以用户和账户为例
我们要查询账户以及其对应的使用者的信息,我们一般常用的方式是在从表中建立主表对象,这样如果我们想联动的查询信息,只要将主表的信息封装进去即可,
本次案例中即 account账户中建立用户user对象
本案例表中要求外键..互为外键,用户表为主表....注意
多对一 : 查询每个账户对应的用户信息
account.java
package com.zyh.mybatis.pojo;
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;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", uid=" + uid +
", money=" + money +
'}';
}
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;
}
}
AccountMapper.java
public interface AccountMapper {
List<Account> getAccountUser();
}
AccountMapper.xml
<mapper namespace="com.zyh.mybatis.mapper.AccountMapper">
<!--封装账户和用户部分信息-->
<resultMap id="accountUser" type="account">
<id property="id" column="id"></id>
<result property="money" column="money"></result>
<!--一对一映射封装user-->
<association property="user" javaType="user">
<id property="id" column="uid"></id> <!--这里两个id会重复,我在sql中做了别名-->
<result property="username" column="username"></result>
<result property="address" column="address"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
</association>
</resultMap>
<!--查询所有账户信息以及用户信息-->
<select id="getAccountUser" resultMap="accountUser">
select a.id,a.money,a.uid,
u.id as uid, u.username,u.address,u.sex,u.birthday
from account a,user u where a.uid=u.id;
</select>
</mapper>
test.java
@Test
public void getAccountList() {
List<Account> accountUserList = accountMapper.getAccountUser();
for (Account au:accountUserList){
System.out.print(au.toString()+" ");
System.out.println(au.getUser());
}
}
一对多关系映射：主表实体应该包含从表实体的集合引用
一对多 : 查询每个用户对应的账户信息
User.java
public class User implements Serializable {
private Integer id;
private String username;
private String address;
private String sex;
private Date birthday;
private List<Account> accountList;
--set--get--tostring--省略----
UserMapper.java
public interface UserMapper {
public List<User> getUserAndAccount();
}
UserMapper.xml
注意:由于我们嵌入的是list集合,因此在resultmap中也是用的<collection>属性,其中ofType属性表示的是集合中存储的元素类型
另外:这里的sql语句,由于我们要查询所有用户的账户,有的用户没有账户我们一样要显示,因此这里用的是左外连接
<!--配置映射结果集-->
<resultMap id="UserWithAccount" type="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<result property="birthday" column="birthday"></result>
<collection property="accountList" ofType="account"> <!--ofType集合中元素的类型-->
<id property="id" column="accountid"></id>
<result property="uid" column="uid"/>
<result property="money" column="money"/>
</collection>
</resultMap>
<!--查询所有用户以及其名下账户-->
<select id="getUserAndAccount" resultMap="UserWithAccount">
select u.*,a.id as accountid,a.uid,a.money from
`user` u LEFT OUTER JOIN account a on a.uid=u.id;
</select>
多对多
设计:
以用户的角色为例
建立表role角色表,user表,以及以roleid和userid为外键的中间表role_user
javapojo里两个bean互相存储对方的list集合
示例1.查询每个用户拥有的角色
User.java
public class User implements Serializable {
private Integer id;
private String username;
private String address;
private String sex;
private Date birthday;
//多对多的关系映射：一个用户可以具备多个角色
private List<Role> roles;
set...get...
UserMapper.java
public interface UserMapper {
List<User> getUsersRolers();
}
UserMapper.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="com.zyh.mybatis.mapper.UserMapper">
<resultMap id="usersWithRoles" type="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<result property="birthday" column="birthday"></result>
<collection property="roles" ofType="role">
<id property="roleId" column="roleId"></id>
<result property="roleName" column="roleName"/>
<result property="roleDesc" column="roleDesc"/>
</collection>
</resultMap>
<select id="getUsersRolers" resultMap="usersWithRoles">
SELECT u.*,r.ID AS roleId,r.ROLE_DESC as roleDesc,r.ROLE_NAME AS roleName
from user u LEFT OUTER JOIN user_role ur ON u.id=ur.UID
LEFT OUTER JOIN role r on ur.RID=r.ID;
</select>
</mapper>
test
@Test
public void getUsersRolers() {
List<User> userList = userMapper.getUsersRolers();
System.out.println("");
for (User user : userList) {
System.out.println(user.toString());
for (Role role:user.getRoles()){
System.out.println(role.toString());
}
System.out.println("______________________________");
}
}
查询结果
查询每个角色下的用户
Role.java
public class Role implements Serializable {
private Integer roleId;
private String roleName;
private String roleDesc;
//多对多的关系映射：一个角色可以赋予多个用户
private List<User> users;
set...get
RoleMapper.java
<?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.zyh.mybatis.mapper.RoleMapper">
<resultMap id="roleAndUser" type="role">
<id property="roleId" column="roleid"></id>
<result property="roleName" column="roleName"/>
<result property="roleDesc" column="roleDesc"/>
<collection property="users" ofType="user">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<result property="birthday" column="birthday"></result>
</collection>
</resultMap>
<select id="getAllRole" resultMap="roleAndUser">
select ID as id,ROLE_DESC AS roleDesc,ROLE_NAME AS roleName from role
</select>
<select id="getRoleWithUser" resultMap="roleAndUser">
SELECT r.ID AS roleid,r.ROLE_NAME as roleName,r.ROLE_DESC as roleDesc,u.*
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>
test.java
@Test
public void getRoleWithUser() {
List<Role> roleList = roleMapper.getRoleWithUser();
System.out.println("");
for (Role role : roleList) {
System.out.println(role.toString());
for (User user:role.getUsers()){
System.out.println(user.toString());
}
System.out.println("______________________________");
}
}
网友评论