MyBatis学习小结

MyBatis

步骤一

建立实体类

public class User {
    private int id;
    private  String name;
    private String pwd;

编写对应的get、set方法 、有参构造和无参构造、toString方法。
建立UserMapper接口

public interface UserMapper {
    // 查询全部用户
    List<User> getUserList();

    // 根据id的查询用户
    User getUserById(int id);

    // 增加用户insert
    int addUser(User user);

    // 修改用户
    int updateUser(User user);

    // 删除用户
    int deleteUser(int id);

**引入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核心配置文件-->
<configuration>
        <!--引入外部配置文件-->
        <properties resource="db.properties"/>

    <!--给实体类取别名-->
    <typeAliases>
        <package name="com.jie.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
<mappers>
    <mapper resource="com/jie/dao/UserMapper.xml"/>
    <!--<mapper class="com.jie.dao.UserMapper"/>-->
    <!--<package name="com.jie.dao"/>-->
</mappers>

</configuration>

编写UserMap.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">

<!--namespace 绑定一个对应的Dao/Mapper接口-->
<mapper namespace="com.jie.dao.UserMapper">

    <select id="getUserList" resultType="user">
    select * from mybatis.user
    </select>
    <select id="getUserById" parameterType="int" resultType="user">
        select * from mybatis.user where id = #{id}
          </select>
    <!-- 对象中的属性，可以直接取出来-->
     <insert id="addUser" parameterType="User">
         insert into mybatis.user (id , name , pwd) values (#{id},#{name},#{pwd});
     </insert>
     <update id="updateUser" parameterType="User">
         UPDATE  mybatis.user set name =#{name}, pwd=#{pwd}  WHERE  id = #{id};
     </update>
     <delete id="deleteUser" parameterType="int">
         delete FROM  mybatis.user where id = #{id}
     </delete>

编写MyBatis工具类

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            // 使用MyBatis第一步：获取sqlSessionFactory对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
             sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        return  sqlSessionFactory.openSession();
    }

}

测试类

 @Test
    public void test(){
        // 第一步： 获得SqlSession 对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //方式一： 执行SQL
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.getUserList();

        for (User user : userList) {
            System.out.println(user);
        }


        //关闭SqlSession
        sqlSession.close();

结果集映射

当字段名不一样时

 <!--结果集映射-->
    <resultMap id="UserMap" type="User">
        <!--字段名一样可以不写-->
        <!--<result column="id" property="id"/>-->
        <!--<result column="name" property="name"/>-->
        <result column="pwd" property="password"/>

    </resultMap>

开启日志

log4j
在MyBatis核心配置文件中开启log4j日志
默认是STDOUT_LOGGING

<settings>
        <!--<setting name="logImpl" value="STDOUT_LOGGING"/>-->
        <setting name="logImpl" value="LOG4J"/>
    </settings>

编写log4j.properties 对log4j进行配置

最后在测试类中加入日志

static Logger logger = Logger.getLogger(UserMapperTest.class);

    @Test
    public void getUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user = mapper.getUserById(1);
        System.out.println(user);

        sqlSession.close();
    }

    @Test
    public void testlog4j(){
       logger.info("info:进入了log4j");
       logger.debug("debug:进入了log4j");
       logger.error("error:进入了log4j");
    }
      

使用注解

在UserMapper接口中使用注解
省去了xml文件，但是功能不如使用xml文件强大

    @Select("select * from user")
    List<User> getUsers();

    // 方法存在多个参数，所有的参数前面必须加上@Param("id")注解
    @Select("select * from user where id= #{id}")
    User getUserById(@Param("id") int id);

    @Insert("insert into user (id , name, pwd) values (#{id},#{name},#{pwd})")
    int addUser(User user);

    @Update("update user set name=#{name},pwd=#{pwd} where id=#{id}")
    int updateUser(User user);

    @Delete("delete from user where id=#{uid}")
    int deleteUser(@Param("uid") int id);

嵌套查询和子查询

查询所有的学生信息，以及老师的信息

准备阶段：1.建立Teacher和Student实体类
2.编写两个实体类的Mapper接口
3.编写MyBatis核心配置文件，绑定接口
4.建立对应接口的xml文件
5.创建工具类
5.测试

方式一：子查询
只有步骤四和上面不同，所以这里只写步骤四的代码

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jie.dao.StudentMapper">

  <select id="getStudent" resultMap="StudentTeacher">
      SELECT * from student
  </select>
    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性我们需要单独处理 对象：association  集合：collection-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{tid}
    </select>

</mapper>

嵌套查询

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jie.dao.StudentMapper">

        <select id="getStudent2" resultMap="StudentTeacher">
            SELECT s.id sid, s.name sname,t.name tname
            FROM student s,teacher t
            WHERE s.tid = t.id;
        </select>
    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" column="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>



</mapper>

测试类

    @Test
    public  void testStudent(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

        List<Student> studentList = mapper.getStudent();
        for (Student student : studentList) {
            System.out.println(student);
        }


        sqlSession.close();
    }

注意点
返回一个对象时使用:association

 <association property="teacher" column="Teacher">
            <result property="name" column="tname"/>
        </association>
        

返回一个集合时使用:collection

  <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>

动态SQL

if、where、set用法
if：可以使用 if 进行判断，对SQL语句进行拼接
set：在update语句中代替set使用，可以自动去掉最后一个 author = #{author}, 中的逗号。

   <update id="updateBlog" parameterType="map">
        update blog
        <set>
            <if test="title != null">
                title=#{title},
            </if>
            <if test="author !=null">
                 author = #{author},
            </if>
        </set>
        where id = #{id}
    </update>
           

where：用法与set大致相同，在需要使用where时加上，可以自动去掉where后面的and
when:判断与if差不多不同点在于后面接otherwise
otherwise：否则，如果when条件不成立，则otherwise成立
choose：when和otherwise都需要在choose标签内

  <select id="queryBlogChoose" resultType="blog" parameterType="map">
        select * FROM blog
        <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
               AND author = #{author}
            </when>
            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
        </where>
    </select>

动态SQL片段：把可以重复使用的SQL语句拿出来，建立动态SQL片段。

<!--动态SQL片段-->
    <sql id="if-title-author">
        <if test="title != null">
            title=#{title}
        </if>
        <if test="author !=null">
            and author = #{author}
        </if>
    </sql>
    <select id="queryBlogIf" parameterType="map" resultType="blog">
        select * FROM blog
        <where>
          <include refid="if-title-author"/>
        </where>
    </select>

一级缓存和二级缓存

一级缓存：一级缓存为默认开启的缓存，只能储存上一次查询的数据，下一次查询后刷新。
二级缓存：FIFO先进先出。

 <!--开启二级缓存-->
    <cache type="org.mybatis.caches.ehcache.EhcacheCache"/>

配置二级缓存ehcache.xml

<?xml version="1.0" encoding="UTF-8"?>
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd"
         updateCheck="false">

    <diskStore path="./tmpdir/Tmp_EhCache"/>

    <defaultCache
            eternal="false"
            maxElementsInMemory="10000"
            overflowToDisk="false"
            diskPersistent="false"
            timeToIdleSeconds="1800"
            timeToLiveSeconds="259200"
            memoryStoreEvictionPolicy="LRU"/>

    <cache
            name="cloud_user"
            eternal="false"
            maxElementsInMemory="5000"
            overflowToDisk="false"
            diskPersistent="false"
            timeToIdleSeconds="1800"
            timeToLiveSeconds="1800"
            memoryStoreEvictionPolicy="LRU"/>
</ehcache>

小知识
数据的查询路径为：二级缓存 - - -》一级缓存 - - -》数据库