Mybatis mapper.xml接口开发复习

作者: 名字是乱打的 | 来源:发表于2020-03-18 13:40 被阅读0次

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>