Mybatis

Mybatis

---

核心文件

config.xml

数据库配置

<?xml version="1.0" encoding="utf8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--    引入外部配置文件-->
    <properties resource="db.properties"/>

    <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>
    
    <!--每一个mapper要配置-->
    <mappers>
        <mapper resource="com/dyd/dao/UserMapper.xml"/>
    </mappers>
</configuration>

utils.java

用于产生SqlSessionFactory和session

package com.dyd.utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
//SqlSessionFactory
public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            //获取sqlSession
            String resource = "mybaties-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //用SqlSessionFactory创建实例
    public static SqlSession getSqlSession(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        return sqlSession;
    }

}

pom.xml

产生xml文件

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.46</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.2</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.10</version>
    </dependency>
</dependencies>
<build>
    <resources>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.properties</include>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
    </resources>
</build>

---

接口配置

接口

package com.dyd.dao;
import com.dyd.pojo.User;
import java.util.List;
public interface UserMapper {
    List<User> getUserList();
    User getUserById(int id);
    int addUser(User user);
    int updateUser(User user);
    int deleteUser(int id);
}

配置文件

<?xml version="1.0" encoding="utf8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dyd.dao.UserMapper">
    <select id="getUserList" resultType="com.dyd.pojo.User">
        select * from mybaties.user;
    </select>

    <select id="getUserById" parameterType="int" resultType="com.dyd.pojo.User">
        select * from mybaties.user where id = #{id};
    </select>

    <insert id="addUser" parameterType="com.dyd.pojo.User">
        insert into mybaties.user (id,name ,pwd) values(#{id},#{name},#{pwd});
    </insert>

    <update id="updateUser" parameterType="com.dyd.pojo.User">
        update mybaties.user set name=#{name},pwd=#{pwd} where id =#{id};
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from mybaties.user where id=#{id};
    </delete>
</mapper>

---

测试

package com.dyd.dao;
import com.dyd.pojo.User;
import com.dyd.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserDaoTest {

    @Test
    public void test(){
        //获取sqlsession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //执行sql
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.getUserList();
        //遍历
        for (User user : userList) {
            System.out.println(user);
        }
        //关闭sql
        sqlSession.close();
    }

    @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 addUser() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int res = mapper.addUser(new User(4, "ww", "123324"));
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void updateUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUser(new User(4,"dod","123098"));
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void deleteUser(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteUser(4);
        sqlSession.commit();
        sqlSession.close();
    }
}

---

ResultMap

处理数据库中的列和实体类的字段对应(可以省略一样的映射)

<!--    结果映射-->
    <resultMap id="UserMap" type="User">
    <!--    column数据库中的字段，property实体类中的属性-->
    <result column="id" property="id"></result>
    <result column="name" property="name"></result>
    <result column="pwd" property="pwd"></result>
    </resultMap>
    <select id="getUserList" resultMap="UserMap">
    select * from mybaties.user;
</select>

---

日志工厂

<!--    日志工厂-->
<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

---

Log4j

#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file

#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/logFile.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

---

分页

List<User> getUserByLimit(Map<String,Integer> map);
<select id="getUserByLimit" parameterType="map" resultMap="UserMap">
    select * from mybaties.user limit #{startIndex},#{pageSize}
</select>
@Test
public void getUserByLimit(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    HashMap<String, Integer> map = new HashMap<String, Integer>();
    map.put("startIndex",0);
    map.put("pageSize",2);
    List<User> userList = mapper.getUserByLimit(map);
    for (User user : userList) {
        System.out.println(user);
    }
    sqlSession.close();
}

---

注解开发

@Select("select * from user where id = #{id}")
List<User> getUserById(@Param("id") int id);
@Insert("insert into user(id,name,pwd) values (#{id},#{name},#{password})")
int addUser(User user);
@Delete("delete from user where id = #{uid}")
int deleteUser(@Param("uid") int uid);
配置文件：
<mappers>
    <mapper class="com.dyd.dao.UserMapper"></mapper>
</mappers>

---

Lombok

简化pojo代码。

---

多对一查询

先把tid查询出来然后根据resultmap来进行二次查询。

@Data
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

<select id="getStudent" 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" javaType="Teacher">
	    <result property="name" column="tname"/>
    </association>
</resultMap>

---

一对多查询

@Data
public class Teacher {
    private int id;
    private String name;
    private List<Student> students;
}

<select id="getTeacher" resultMap="TeacherStudent">
    SELECT s.id sid,s.name sname,t.name tname,t.id tid
    FROM teacher t,student s
    WHERE s.`tid`=t.`id`;
</select>
<resultMap id="TeacherStudent" type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <collection property="students" ofType="Student">
    	<result property="id" column="sid"/>
    	<result property="name" column="sname"/>
    	<result property="tid" column="tid"/>
    </collection>
</resultMap>

---

动态sql (IF)

<select id="queryBlogIF" parameterType="map" resultType="Blog">
    select * from mybatis.blog 
    <where>
    	<if test="title!=null">
        	and title=#{title}
		</if>
    	<if test="author!=null">
        	and author=#{author}
		</if>
    </where>
</select>

---

动态sql (set)

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

---

缓存

一级缓存默认在sqlsession中开启

二级缓存只需在xml接口文件中加入即可，或者：

<cache eviction="FIFO"
    flushInterval="60000"
    size="512"
    readOnly="true"/>

全局缓存：

<setting name="cacheEnabled" value="true"/>

---