mybatis动态sql和缓存

搭建环境和前面的基本一样，主要是配置mapper 接口，mapperXml文件，mybaits核心配置文件，测试类有不同的地方

1.mapper接口

package mapper;

import pojo.User;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public interface UserMapper {
    public List<User> getUserList();
    public List<User> getUserList2();
    public List<User> getUserLisIf(HashMap<String,Object> map);
    
}

 

2.mapperXml文件

<?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="mapper.UserMapper">
<!-- 在UserMapper中开启缓存   -->
    <cache eviction="FIFO"
           flushInterval="60"
           size="500"
           readOnly="true"></cache>
    <!--查询    -->
    <!--本来这里的resultType要写全限定类名，但是在mybaits-config.xml文件中配置了别名user后，这里可以直接写user-->
    <select id="getUserList" resultType="user">
        select * from user
    </select>

    <!--使用resultmap 解决实体类和数据库字段不一致的问题，这里我故意把实体类的email 改为userEmail   -->
    <resultMap id="userMap" type="user">
        <!-- column是数据库中的字段名，  property是实体类的属性     -->
        <result column="email" property="userEmail"></result>
    </resultMap>
    <select id="getUserList2" resultMap="userMap">
        select * from user
    </select>

<!--    使用动态sql if 标签-->
<!--    <select id="getUserLisIf" parameterType="map" resultType="user">-->
<!--        select * from user where 1=1-->
<!--        <if test="userId!=null">-->
<!--            and userId=#{userId}-->
<!--        </if>-->

<!--        <if test="userName!=null">-->
<!--            and userName=#{userName}-->
<!--        </if>-->
<!--    </select>-->

<!--    &lt;!&ndash;    使用动态sql where标签会自动去掉没必要的and,比如 userId成立的情况，会自动去掉userName前面的and&ndash;&gt;-->
    <select id="getUserLisIf" parameterType="map" resultType="user">
        select * from user
        <where>
            <if test="userId!=null">
                 userId=#{userId}
            </if>

            <if test="userName!=null">
                and userName=#{userName}
            </if>
        </where>
    </select>

<!-- 还有一个是update 的set 标签，<set></set> -->

</mapper>

 

3.mybatis核心配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 配置日志文件   -->
    <settings>
<!--        <setting name="logImpl" value="STDOUT_LOGGING"/>-->
        <setting name="logImpl" value="log4j"/>
<!--    开启驼峰命名    -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!--     开启缓存-->
        <setting name="cacheEnabled" value="true"/>
    </settings>
    


<!--    <properties resource="db.properties"/>-->
    <!--别名    -->
    <typeAliases>
<!--        &lt;!&ndash;设置User类的别名为user        &ndash;&gt;-->
<!--        <typeAlias type="pojo.User" alias="user"></typeAlias>-->
<!--        设置pojo包下的所有实体类的别名为类名的首字母小写  -->
        <package name="pojo"/>
    </typeAliases>

    <environments default="develoment">
        <environment id="develoment">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://47.112.227.192:3306/spring?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false&amp;serverTimezone=GMT%2B8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <!--每一个mapper 都要在mybatis 的核心配置文件中注册    -->
    <mappers >
<!--        用resource映射的话就用全路径-->
        <mapper resource="mapper/userMapper.xml"></mapper>
<!--        <mapper resource="mapper/studentMapper.xml"></mapper>-->
<!--    用class 映射的话，要用接口的类的全限定类名    -->
<!--        <mapper class="mapper.StudentMapper"></mapper>-->
<!--        <mapper resource="mapper/teacherMapper.xml"></mapper>-->
    </mappers>
</configuration>

 

4.测试类

package test;

import commonUntil.MybatisUntil;
import mapper.UserMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.User;

import java.util.HashMap;
import java.util.List;

public class Mytest {
    @Test
    public void getUserList(){
        SqlSession sqlSession= MybatisUntil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.getUserList();
        System.out.println("userList:"+userList);
        sqlSession.close();
        //当这里没有为UserMapper设置二级缓存的时候，两次查询都会去连接数据库查数据
        //当在userMapper.xml里面设置了缓存后，第二次查询就不走数据库了，直接缓存中去获取

        SqlSession sqlSession2= MybatisUntil.getSqlSession();
        UserMapper userMapper2 = sqlSession2.getMapper(UserMapper.class);
        List<User> userList2 = userMapper2.getUserList();
        System.out.println("userList2:"+userList2);
        sqlSession.close();
    }


    @Test
    public void getUserLisIf(){
        SqlSession sqlSession= MybatisUntil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Object> map = new HashMap<String, Object>();
        //这里根据条件动态加入条件
//        map.put("userId",20);
        map.put("userName","四皇凯多");
        List<User> userList = userMapper.getUserLisIf(map);
//      由于mybatis 有一级缓存的原因，在一个sqlSession会话内再次去拿同样的数据会去一级缓存拿
        List<User> userList2 = userMapper.getUserLisIf(map);
        System.out.println("userList:"+userList);
        System.out.println("userList2:"+userList2);
        sqlSession.close();
    }


}