mybatis

#MAven中使用原始mybatis

1.新建，maven项目，引入依赖

    <!--导入依赖-->
    <dependencies>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.3</version>
        </dependency>
        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
        </dependency>
    </dependencies>

2.编写 mybatis—config.xml （拿来即用）

里面主要配置了mysql连接信息以及加载扫描映射文件

<?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>
    <!--数据源环境-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <!--加载映射文件-->
<!--    <mappers>-->
<!--        <mapper resource="com\mapper\UserMapper.xml"></mapper>-->
<!--    </mappers>-->
</configuration>

3.编写mybatisutils （拿来即用）

读取你的mybatis-config。xml并获取SqlSession

package com.derking.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;

public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;


    static {
        InputStream is = null;
        try {
            is = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 获得SqlSession
     */
    public static SqlSession getSqlSession() throws IOException {
        return sqlSessionFactory.openSession();
    }

    /**
     * 提交释放资源
     */
    public static void commitAndClose(SqlSession sqlSession) {
        if (sqlSession != null) {
            sqlSession.commit();
            sqlSession.close();
        }
    }

    /**
     * 回滚释放资源
     */
    public static void rollbackAndClose(SqlSession sqlSession) {
        if (sqlSession != null) {
            sqlSession.rollback();
            sqlSession.close();
        }
    }
}

4.编写xmlsql语句 （具体业务）

命名空间指定好接口类 sql语句指定好方法名

<?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.derking.mapper.UserMapper">
    <select id="getUserList" resultType="com.derking.pojo.User">
        select * from mybatis
    </select>
</mapper>

5.测试

通过工具类拿到sqlsession sqlsession通过接口文件拿到sql语句 sql语句执行方法

    @Test
    public void test() throws IOException {
        /*第一步获得sqlsession对象*/
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.getUserList();
        userList.forEach(System.out::println);

        sqlSession.close();
    }

6.容易出现的问题

找不到 mybatis——config。xml

        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>

找不到数据库连接池

把mysql-conntion-java 版本换高点

7.自己有的问题

我的xml映射文件只能放在java包下，放在recausese报找不到。xml文件

8.笔记

resultType : Sql语句执行的返回值； parameterType : 参数类型；

9.生命周期和作用域

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2SJGmu19-1651582357237)(img.png)]

10.结果集映射

如果数据库里的字段和java实体类名字不一样 有两种解决办法

1.as 起别名

<select id="getUserById" resultType="com.kuang.pojo.User">
    select id,name,pwd as password from USER where id = #{id}
</select>

2.配置resultMap结果及映射

<!--结果集映射-->
<resultMap id="UserMap" type="User">
    <!--column数据库中的字段，property实体类中的属性-->
    <result column="id" property="id"></result>
    <result column="name" property="name"></result>
    <result column="pwd" property="password"></result>
</resultMap>

<select id="getUserList" resultMap="UserMap">
    select * from USER
</select>

11.配置LOG4J

1.引入依赖

        <!--log4j-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

2.配置log4j.properties

#将等级为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/rzp.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.sq1.PreparedStatement=DEBUG

3.在mybatis_config.xml中引用

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

12.分页查询

RowBounds实现

1.接口

/*RowBounds分页*/
    List<User> getUserByRowBounds();

2.xml SQL语句

    <!--RowBounds分页查询用户信息-->
    <select id="getUserByRowBounds" resultType="com.derking.pojo.User">
        select * from user
    </select>

3.测试

    /*RowBounds实现分页查询*/
    @Test
    public void test2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        /*RowBoundS实现*/
        RowBounds rowBounds = new RowBounds(0,2);
        /*通过java代码层面实现分页*/
        List<User> list = sqlSession.selectList("com.derking.mapper.UserMapper.getUserByRowBounds", null, rowBounds);
        list.forEach(System.out::println);
        sqlSession.close();
    }
}

正常实现

    <!--分页查询用户信息-->
    <select id="getUserByLimit" parameterType="map" resultType="com.derking.pojo.User">
        select * from user limit #{startIndex},#{pageSize};
    </select>

        /*分页查询用户*/
        Map<String, Integer> map = new HashMap<String,Integer>();
        map.put("startIndex",0);
        map.put("pageSize",3);
        List<User> user = mapper.getUserByLimit(map);
        user.forEach(System.out::println);

13.使用注解开发

1.注解在接口上实现

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

2.在核心配置文件中绑定接口即可

<mappers>
    <mapper class="com.derking.mapper.UserMapper"/>
</mappers>

14.一对多 多对一

一对多

 <!--按照查询嵌套处理-->
    <!--查询所有学生以及他的老师-->
    <!--思路： 查询所有学生信息  根据查询出来的学生的tid 寻找特定的 老师（子查询）-->
    <select id="getStudent" resultMap="StudentsTeacher">
        select * from student
    </select>
    <resultMap id="StudentsTeacher" type="com.derking.pojo.Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性我们需要单独出来 对象：association  集合：collection-->
        <association property="teacher" column="tid" javaType="com.derking.pojo.Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="com.derking.pojo.Teacher">
        select * from teacher where id = #{id}
    </select>

    <!--按照结果嵌套处理  把连表查询的内容查出来 因为数据返回值对应不上 所以结果用Map进行封装-->
    <select id="getStudent2" resultMap="StudentsTeacher2">
        select s.id sid , s.name sname , t.name tname from student s,teacher t where s.tid = t.id
    </select>
    <resultMap id="StudentsTeacher2" type="com.derking.pojo.Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="com.derking.pojo.Teacher">
            <result property="name" column="tname"></result>
        </association>
    </resultMap>

多对一

    <!--一对多子查询-->
    <select id="getTeachersStudent2" resultMap="TeachersStudent2">
        select * from teacher where id = #{tid}
    </select>
    <resultMap id="TeachersStudent2" type="com.derking.pojo.Teacher">
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
    </resultMap>
    <select id="getStudentByTeacherId" resultType="com.derking.pojo.Student">
        select * from student where tid = #{tid}
    </select>

    <!--一对多   按结果嵌套查询-->
    <select id="getTeachersStudent" resultMap="TeacherStudent">
        select s.id sid, s.name sname, s.tid stid, t.id tid, t.name tname
        from student s,teacher t
        where s.tid = t.id and t.id = #{tid}
    </select>
    <resultMap id="TeacherStudent" type="com.derking.pojo.Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="com.derking.pojo.Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="stid"/>
        </collection>
    </resultMap>

15.动态sql

    <!--/*动态sql之if 查询所有*/-->
    <select id="queryBlogIF" parameterType="map" resultType="Blog">
        SELECT * FROM blog
        <where>
            <if test="title != null">
                AND title = #{title}
            </if>
            <if test="author != null">
                AND author = #{author}
            </if>
        </where>
    </select>
    
    <!--动态sql之choose 查询素有-->
    <select id="queryBlogChoose" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <choose>
                <when test="author != null">
                    author = #{author}
                </when>
                <when test="title != null">
                    and title = #{title}
                </when>
                <otherwise>
                    and views = #{views}
                </otherwise>
            </choose>
        </where>
    </select>

    <!--动态sql之set 更细博客-->
    <update id="updateBlog" parameterType="Blog">
        update blog
        <set>
            <if test="title != null">
                title = #{title}
            </if>
            <if test="author != null">
                author = #{author}
            </if>
        </set>
        where id = #{id} 
    </update>

16.缓存

一级缓存：

默认开启的，也关闭不了，作用域为sqlsession级别。

二级缓存：

开启需要在mapper.xml中添加标签
作用域为 类(maapper.xml（namespace）)级别
当一级缓存（sqlsession）关闭掉，缓存会丢到二级缓存中

 <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id = #{id} 
</update>

### 16.缓存
#### 一级缓存：
默认开启的，也关闭不了，作用域为sqlsession级别。
#### 二级缓存：
开启需要在mapper.xml中添加<cache/>标签
作用域为 类(maapper.xml（namespace）)级别
当一级缓存（sqlsession）关闭掉，缓存会丢到二级缓存中