SSM[mybaits] 复杂查询

发表于 | 分类于

动态sql语句

  • <where> 和 <if>
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    <select id="findByCond" resultType="com.gphper.domain.User" parameterType="com.gphper.domain.User">
        select * from user
        <where>
            <if test="name != null">
                and name = #{name}
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
        </where>
    </select>
    [IUserDao.java]
    1
    List<User> findByCond(User user);
    [测试方法]
    1
    2
    3
    4
    5
    6
    7
    8
    9
    public void findByCond(){
        User u = new User();
        u.setName("cjp");
        u.setGender("男");
        List<User> users = userDao.findByCond(u);
        for(User user:users){
            System.out.println(user);
        }
    }

根据传入的user对象的值是否为空添加sql查询条件

  • <foreach> 在in条件中的使用
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    <select id="findIn" resultType="com.gphper.domain.User" parameterType="com.gphper.domain.QueryVo">
        select * from user
        <where>
            <if test="ids != null and ids.size()>0">
                <foreach collection="ids" open="and id in (" close=")" item="id" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>
    [QueryVo]
    1
    2
    3
    4
    5
    6
    7
    8
    private List<Integer> ids;
    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }
    [IUserDao]
    1
    List<User> findIn(QueryVo vo);
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    @Test
    public void findIn(){
        QueryVo vo = new QueryVo();
        List<Integer> l = new ArrayList<>();
        l.add(1);
        l.add(3);
        vo.setIds(l);

        List<User> users = userDao.findIn(vo);
        for(User user:users){
            System.out.println(user);
        }
    }
  • <sql> 和 <include>
    抽离sql语句使用的标签

多表查询

  • 一对一关联查询

    [account.java]
    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
    package com.gphper.domain;

    public class Account{
        private int id;
        private int uid;
        private double money;

        public User getUser() {
            return user;
        }

        public void setUser(User user) {
            this.user = user;
        }

        private User user;
        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public int getUid() {
            return uid;
        }

        public void setUid(int 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 +
                    ", user=" + user +
                    '}';
        }
    }
    [IAccountDao.java]
    1
    List<Account> findAll();
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    <resultMap id="account" type="com.gphper.domain.Account">
        <id property="id" column="aid"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <association property="user" column="uid" javaType="com.gphper.domain.User">
            <id property="id" column="id"></id>
            <result property="name" column="name"></result>
        </association>
    </resultMap>

    <!--  配置查询所有  -->
    <select id="findAll" resultMap="account">
        select a.id as aid,a.money,a.uid,b.* from account a,user b where a.uid = b.id;
    </select>

  • 一对多关联查询

    [user.java]
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    List<Account> accounts;

     public List<Account> getAccounts() {
        return accounts;
    }

    public void setAccounts(List<Account> accounts) {
        this.accounts = accounts;
    }
    ```java [IUserDao.java]
    List<User> findAndAccount();
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    <resultMap id="userAccountMap" type="com.gphper.domain.User">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <collection property="accounts" ofType="com.gphper.domain.Account">
            <id property="id" column="aid"></id>
            <result property="money" column="money"></result>
        </collection>
    </resultMap>
    <select id="findAndAccount" resultMap="userAccountMap">
        select * from user u left join account a on u.id = a.uid
    </select>

  • 多对多
    框架在写法上和一对多没有毛区别

延迟加载

  • 开启配置
    lazyLoadingEnabled aggressiveLazyLoading
  • 一对一
    修改 association 标签就可以，只有不取user的值，就不会执行查询语句
    1
    2
    3
    4
    5
    6
    7
    8
    <resultMap id="account" type="com.gphper.domain.Account">
        <id property="id" column="aid"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <association property="user" column="uid" select="com.gphper.dao.IUserDAo.findById">

        </association>
    </resultMap>
  • 一对多
    修改 collection 标签，也是使用 select 属性，同一对一写法

缓存

  • 一级缓存 SqlSession 缓存

  • 二级缓存 SqlSessionFactory 缓存 【需要在 MysqlMapConf.xml和相应的映射文件中 单独设置】

  • 一级缓存存的是对象，二级缓存存的是数据不是对象

  • 注解开发注意点

  1. 不能有接口对应的xml映射文件

  2. <resultMap> 标签对应 @Results注解

    1
    2
    3
    4
    5
    6
    <resultMap id="userMap" type="user">
        <!--  先对应主键  -->
        <id property="id" column="id"></id>
        <!--  再对应非主键  -->
        <result property="userName" column="username"></result>
    </resultMap>
    1
    2
    3
    4
    @Results(id = "userMap",value = {
        @Result(id = true,column = "id",property = "id"),
        @Result(column = "username",property = "userName")
    })

    使用时将 resultMap="userMap" 属性改成 @ResultMap("userMap")

  3. 多对一写法

    1
    2
    3
    4
    5
    6
    7
    8
    <resultMap id="account" type="com.gphper.domain.Account">
        <id property="id" column="aid"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <association property="user" column="uid" select="com.gphper.dao.IUserDAo.findById">

        </association>
    </resultMap>
    1
    2
    3
    4
    5
    6
    @Results(id = "account",value = {
            @Result(id = true,column = "aid",property = "id"),
            @Result(column = "uid",property = "uid"),
            @Result(column = "money",property = "money"),
            @Result(column = "uid",property = "user",one = @One(select = "com.gphper.dao.IUserDAo.findById",fetchType = FetchType.EAGER))
    })

  4. 一对多
    将多对一中的 one 换成 many