Mybatis

新建maven工程,在resources中新建config.xml配置文件

<?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"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.1.41:3306/test?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="*********"/>
            </dataSource>
        </environment>
    </environments>
<!--  每一个Mapper.xml都需要在Mybatis核心文件中注册  -->
    <mappers>
        <mapper resource="com/kuang/dao/UserMapper.xml"/>
    </mappers>
</configuration>

根据数据表创建pojo对象

package com.kuang.pojo;

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

    /*public User() {
    }*/

    public User(int id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    /*public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }*/

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

创建SqlSession

package com.kuang.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 javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;


    static {
        {
            try {
                String resource = "mybatis-config.xml";
                InputStream inputStream = Resources.getResourceAsStream(resource);
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    public  SqlSession getSqlSession(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        return sqlSession;
    }
}

创建UserMapper接口

package com.kuang.dao;

import com.kuang.pojo.User;
import org.apache.ibatis.annotations.MapKey;
import java.util.List;
import java.util.Map;

public interface UserMapper {
    //查询全部用户
    List<User> getUserList();
    //根据ID查询用户
    User getUserById(int id);
    //以map方式查询用户
    User getUserById2(Map<String,Object> map);

    //插入一个用户
    int addUser(User user);
    //以map方式插入用户
    int addUser2(Map<String,Object> map);
    //修改用户
    int updateUser(User user);
    //删除用户
    int deleteUser(int id);
}

创建UserMapper.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.kuang.dao.UserMapper">

    <select id="getUserList" resultType="com.kuang.pojo.User">
        select * from test.user;
    </select>

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

    <select id="getUserById2" parameterType="map" resultType="com.kuang.pojo.User" >
        select * from test.user where id = #{id} and name=#{name}
    </select>

    <insert id="addUser" parameterType="com.kuang.pojo.User">
        insert into test.user(id,name,pwd) values(#{id},#{name},#{pwd});
    </insert>
<!--    以map方式插入用户-->
    <insert id="addUser2" parameterType="hashmap">
        insert into test.user(id,name,pwd) values(#{userid},#{name},#{password});
    </insert>


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

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

测试:

package com.kuang.dao;

import com.kuang.pojo.User;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

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

public class UserMapperTest {
    @Test
    public void test(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        //方式一,getMapper
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.getUserList();
        for (User user : userList) {
            System.out.println(user);
        }

        //方式二
      /*  List<User> userList1 = sqlSession.selectList("getUserList");
        for (User user : userList1) {
            System.out.println(user);
        }*/
        //关闭sqlSession
        sqlSession.close();

    }

    @Test
    public void getUserById(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.getUserById(1);
        System.out.println(user);
        sqlSession.close();
    }

    @Test
    public void getUserById2(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Object> map = new HashMap<>();
        map.put("id",5);
        map.put("name","单依纯");
        User user2 = mapper.getUserById2(map);
        System.out.println(user2);
        sqlSession.close();
    }

    @Test
    public void addUser(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.addUser(new User(3,"哈哈","123"));
        //提交事务
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void updateUser(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUser(new User(3,"章子怡","123123"));
        sqlSession.commit();
        sqlSession.close();
    }

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

    @Test
    public void addUser2(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Object> map = new HashMap<>();
        map.put("userid",6);
        map.put("name","李冰冰");
        map.put("password",123);

        mapper.addUser2(map);
        sqlSession.commit();
        sqlSession.close();
    }
}

模糊查询
在UserMapper中添加

//模糊查询
    List<User> getUserLike(String value);

在UserMapper.xml中实现

<select id="getUserLike" resultType="com.kuang.pojo.User">
        select * from test.user where name like #{value}
</select>

测试:

@Test
    public void getUserLike(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.getUserLike("%范%");
        for (User user : userList) {
            System.out.println(user);
        }
    }

也就是在select * from test.user where name like "%"#{value}"%"添加了模糊查询,在测试执行的时候不用再添加%了,List<User> userList = mapper.getUserLike("范");,在测试执行代码时添加%安全.

在mybatis-config.xml引入外部配置文件,在mybatis-config.xml中有层级顺序,如图所示:

properties标签需要放在标签中第一个

在mybatis-config.xml中给实体类起别名,它仅用于 *.xml配置，意在降低冗余的全限定类名书写

<typeAliases>
        <typeAlias type="com.kuang.pojo.User" alias="User"></typeAlias>
    </typeAliases>

这样在UserMapper.xml中的resultType填写之前定义的别名.

    <select id="getUserList" resultType="User">
        select * from test.user;
    </select>

映射器（mappers）
在mybatis-config.xml配置文件中添加映射器,告诉 MyBatis 到哪里去找到这些语句
方式一:

方式二:使用映射器接口实现类的完全限定类名

方式三:

<!-- 将包内的映射器接口实现全部注册为映射器 -->
    <mappers>
<!--        <mapper resource="com/kuang/dao/UserMapper.xml"/>-->
        <package name="com.kuang.dao"/>
    </mappers>

其中注意点和方式二相同

resultMap结果映射
显式使用外部的 resultMap，解决列名不匹配的另外一种方式,当pojo对象中的字段和列中字段不一致时,

通过结果集映射

    <!--结果集映射-->
    <resultMap id="UserMap" type="User">
<!--        <result column="id" property="id"/>-->
<!--        <result column="name" property="name"/>-->
        <result column="pwd" property="password"/>
    </resultMap>
    <select id="getUserById" resultMap="UserMap" >
        select * from test.user where id = #{id}
    </select>

Mybatis日志

在配置文件mybatis-config.xml中添加设置,其中STDOUT_LOGGING为控制台标准输出.
使用log4j
<setting name="logImpl" value="LOG4J"/>
在resource下添加log4j.properties

log4j.rootLogger = debug,console,file
#debug日志输出到控制台
log4j.appender.console=org.apache.log4j.ConsoleAppender
#日志格式
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.conversionPattern=[%-10p]%r %c%t%d{yyyy-MM-dd HH:mm:ss:SSS} %m%n


#appender到文件
log4j.appender.file=org.apache.log4j.FileAppender
#文件格式
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.conversionPattern=[%-10p]%r %c%t%d{ } %m%n
log4j.appender.file.file=D:/Feibao/log4j.log 
log4j.appender.file.encoding=UTF-8

#RollingFileAppender
log4j.appender.rollingFile=org.apache.log4j.RollingFileAppender
log4j.appender.rollingFile.layout=org.apache.log4j.PatternLayout
log4j.appender.rollingFile.layout.conversionPattern=[%-10p]%r %c%t%d{yyyy-MM-dd HH:mm:ss} %m%n
log4j.appender.rollingFile.file=D:/Feibao/log4j.log
log4j.appender.rollingFile.encoding=UTF-8
log4j.appender.rollingFile.maxFileSize=1MB
log4j.appender.rollingFile.maxBackupIndex=5

#DailyRollingFileAppender,用来创建每天滚动的日志文件
log4j.appender.dailyRollingFile=org.apache.log4j.DailyRollingFileAppender
#PatternLayout 允许你使用转换模式（conversion pattern）来定义日志输出的格式。
log4j.appender.dailyRollingFile.layout=org.apache.log4j.PatternLayout
#[%-10p]左对齐并占用至少 10 个字符的宽度。
#%r: 输出自应用启动到输出该日志信息耗费的毫秒数。
#%c: 输出日志事件所属的 logger 的名字。
#%t: 输出产生该日志事件的线程名。
#%d{yyyy-MM-dd HH:mm:ss}: 输出日志事件的日期或时间，日期或时间转换格式由花括号内的内容指定。
#%m: 输出日志消息。
#%n: 输出一个平台相关的行分隔符（如 Unix 系统是 \n，Windows 系统是 \r\n）。
log4j.appender.dailyRollingFile.layout.conversionPattern=[%-10p]%r %c%t%d{yyyy-MM-dd HH:mm:ss} %m%n
log4j.appender.dailyRollingFile.file=D:/Feibao/log4j.log
#日志文件的字符编码为 UTF-8。
log4j.appender.dailyRollingFile.encoding=UTF-8
#DatePattern='.'yyyy-MM-dd 表示日志文件每天滚动一次，并且在文件名中插入日期，用 . 分隔。
log4j.appender.dailyRollingFile.datePattern = '.'yyyy-MM-dd HH-mm-ss

可以另外参考https://baike.baidu.com/item/log4j/480673?fr=ge_ala

Mybatis实现分页

UserMapper接口

List<User> getUserByLimit(Map<String,Integer> map);

UserMapper.xml

<!--    分页查询-->
    <select id="getUserByLimit" parameterType="map" resultType="User">
        select * from test.user limit ${startIndex},#{pageSize};
    </select>

测试

public class UserDaoTest {
    @Test
    public void test(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Integer> map = new HashMap<>();
        map.put("startIndex",0);
        map.put("pageSize",2);
        List<User> userList = mapper.getUserByLimit(map);
        for (User user : userList) {
            System.out.println(user);
        }
        sqlSession.close();
    }
}

通过RowBounds实现分页,不使用sql关键字limit
在UserMapper接口中定义方法List<User> getUserByRowBounds();
在UserMapper.xml中实现List<User> getUserByRowBounds();
测试:

@Test
    public void getUserByRowBounds(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        RowBounds rowBounds = new RowBounds(1, 2);
        List<Object> userList = sqlSession.selectList("com.kuang.dao.UserMapper.getUserByRowBounds", null, rowBounds);
        for (Object user : userList) {
            System.out.println(user);
        }
        sqlSession.close();

    }

使用注解,复杂sql语句还是用xml方式.

最后测试

public class UserMapperDu {
    @Test
    public void test(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.getUsers();
        for (User user : users) {
            System.out.println(user);
        }
        sqlSession.close();
    }
}

使用自动提交事务

在MybatisUtils.java中修改

字符串替换

默认情况下，使用 #{} 参数语法时，MyBatis 会创建 PreparedStatement 参数占位符，并通过占位符安全地设置参数（就像使用 ? 一样）。
当 SQL 语句中的元数据（如表名或列名）是动态生成的时候，字符串替换将会非常有用。举个例子，如果你想 select 一个表任意一列的数据时，不需要这样写：

@Select("select * from user where id = #{id}")
User findById(@Param("id") long id);

@Select("select * from user where name = #{name}")
User findByName(@Param("name") String name);

@Select("select * from user where email = #{email}")
User findByEmail(@Param("email") String email);
// 其它的 "findByXxx" 方法

而是可以只写这样一个方法：

@Select("select * from user where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);

 其中${column}会被直接替换，而 #{value} 会使用 ? 预处理。 这样，就能完成同样的任务,其中${column}和@Param("column")值要相同,都是column, String后面的的column可以自定义名称; #{value}和 @Param("value")值要相同,都是value,String后面的vale可以自定义名称.

User userOfId1 = userMapper.findByColumn("id", "1");或者返回int参数的字符串表示形式。User userOfId1 = userMapper.findByColumn("id", String.valueOf(1));
User userOfNameKid = userMapper.findByColumn("name", "kid");
User userOfEmail = userMapper.findByColumn("email", "noone@nowhere.com");

练习在UserMapper.xml中添加增删改查并使用注解

public interface UserMapper {

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

/*    @Select("select * from user where id =#{id1}")
    User getUserById(@Param("id1") int id);*/


    @Select("select * from user where ${column} = #{value}")
    User getUserById(@Param("column") String column1, @Param("value") String value1);

    @Insert("insert into test.user(id,name,pwd) value (#{id},#{name},#{password})")
    int addUser(User user);

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

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

测试:

public class UserMapperDu {
    @Test
    public void test(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.getUsers();
        for (User user : users) {
            System.out.println(user);
        }
        sqlSession.close();
    }

     @Test
    public void test2(){
         SqlSession sqlSession = new MybatisUtils().getSqlSession();
         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//         User userById = mapper.getUserById(1);
         User userById = mapper.getUserById("id", String.valueOf(1));
         System.out.println(userById);
         sqlSession.close();
     }

     @Test
    public void insertTest(){
         SqlSession sqlSession = new MybatisUtils().getSqlSession();
         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
         mapper.addUser(new User(8,"张靓颖","123"));
         sqlSession.close();

     }

     @Test
    public void updateTest(){
         SqlSession sqlSession = new MybatisUtils().getSqlSession();
         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
         mapper.updateUser(new User(5,"周笔畅","123"));
         sqlSession.close();
     }

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

多对一 association

建老师表
建学生表

CREATE TABLE `student2` (
    `id` INT(10) NOT NULL,
    `name` VARCHAR(30) DEFAULT NULL,
    `tid` INT(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
);

StudentMapper.xml
association – 一个复杂类型的关联；许多结果将包装成这种类型
嵌套结果映射 – 关联可以是 resultMap 元素，或是对其它结果映射的引用
嵌套查询处理:
首先编辑StudentMapper接口

public interface StudentMapper {
    //查询所有的学生信息,以及对应的老师的信息
    public List<Student> getStudent();
//    public List<Student> getStudent2();
}

然后配置StudentMapper.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.kuang.dao.StudentMapper">


    <select id="getStudent" resultMap="StudentTeacher">
        select * from test.student2;
    </select>
    <resultMap id="StudentTeacher" type="Student">
<!--下面的Id和result可以注释,因为上面用的select *-->
 <!--        <id property="id" column="id"/>-->
<!--        <result property="name" column="name"></result>-->
        <!--复杂的属性,单独处理
        对象用association; 集合用collection-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="Teacher">
        select * from test.teacher where id = #{id}
    </select>
</mapper>

Mybatis关联的嵌套结果映射

创建两个pojo类

import lombok.Data;

//多对一
@Data
public class Student {
    private int id;
    private String name;
    //学生关联一个老师
    private Teacher teacher;
}
---
public class Teacher {
    private int id;
    private String name;

    public Teacher(int id, String name) {
        this.id = id;
        this.name = name;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }

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

    public void setName(String name) {
        this.name = name;
    }
}

配置Mapper接口

public interface StudentMapper {
    //查询所有的学生信息,以及对应的老师的信息
//    public List<Student> getStudent();
    public List<Student> getStudent2();
}

在StudentMapper.xml文件中定义一个来指定如何将查询结果映射到这些Java对象

    <resultMap id="StudentTeacherResultMap" type="Student">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <id property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

    <select id="getStudent2" resultMap="StudentTeacherResultMap">
        SELECT
            s.id AS sid,
            s.name AS sname,
            t.id AS tid,
            t.name AS tname
        FROM
            test.student2 s
                INNER JOIN
            test.teacher t ON s.tid = t.id
    </select>

在这个中：
和标签用于映射Student对象的属性。
标签用于处理Student对象中的teacher属性，它是一个Teacher类型的对象。
在标签内部，再次使用和标签来映射Teacher对象的属性。最后一个<select>查询来使用这个<resultMa>

简单查询遇到的问题
pojo:

import lombok.Data;
@Data
public class Student {
    private int id;
    private String name;
    //一个学生对一个老师
    private int tid;
}

public class Teacher {
    private int id;
    private String name;
    //一个老师拥有多个学生
    private List<Student> student;



    public Teacher(int id, String name, List<Student> student) {
        this.id = id;
        this.name = name;
        this.student = student;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + "\"" +
                ", student=" + student +
                '}';
    }



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

    public void setName(String name) {
        this.name = name;
    }

    public void setStudent(List<Student> student) {
        this.student = student;
    }


    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public List<Student> getStudent() {
        return student;
    }

    public Teacher() {
    }
}
//Teacher已经要加无参构造,否则报错:
//Caused by: java.lang.IndexOutOfBoundsException: Index: 2, Size: 2

配置TeacherMapper接口

public interface TeacherMapper {
    @Select("select * from test.teacher")
    List<Teacher> getTeacher();
}

测试:

public class MyTest {
    @Test
    public void test(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> teachers = mapper.getTeacher();
        System.out.println(teachers);
    }
}

一对多

先按照结果嵌套查询:

pojo类用的是上面的
编辑TeacherMapper接口

  //获取指定老师下的所有学生及老师的信息(方法1)
    Teacher getTeacher(@Param("tid") int id);

编辑TeacherMapper.xml

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

    <!--结果嵌套查询-->
    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid, s.name sname, t.name tname, t.id tid
        from student2 s, teacher t where s.tid= t.id and t.id =#{tid}
    </select>


    <resultMap id="TeacherStudent" type="Teacher">
        <id property="id" column="tid"/>
        <result property="name" column="tname"/>
<!--        集合用collection-->
<!--        ofType” 属性，它用来将 JavaBean（或字段）属性的类型和集合存储的类型区分开来。student是一个LIst集合,所以用ofType-->
        <collection property="student" ofType="com.kuang.pojo.Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
</mapper>

测试:

    @Test
    public void testTeacher(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
    }

使用第二种方法嵌套查询
编辑TeacherMapper接口

//获取指定老师下的所有学生及老师的信息(方法2)
    Teacher getTeacher2(@Param("tid") int id);

TeacherMapper.xml增加内容:

<select id="getTeacher2" resultMap="TeacherStudent2">
        select * from test.teacher where id = #{tid}
    </select>

    <resultMap id="TeacherStudent2" type="Teacher">
        <result property="id" column="id"/>
        <collection property="student"  column="id" ofType="Student" select="getStudentByTeacherId"/>
    </resultMap>
    <select id = "getStudentByTeacherId" resultType="Student">
--          #{id}里的值可以自定义
        select * from test.student2 where tid = #{id}
    </select>

测试:

@Test
    public void testTeacher2(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher2(1);
        System.out.println(teacher);
    }

动态sql

新建测试表
编写实体类:
补充:
数据库中的日期字段为create_time,实体类中的属性为private Date createTime;,所以需要是否开启驼峰命名自动映射，即从经典数据库列名 A_COLUMN 映射到经典 Java 属性名 aColumn。
在mybatis-config.xml中添加

public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;

    public Blog(String id, String title, String author, Date createTime, int views) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.createTime = createTime;
        this.views = views;
    }

    public Blog() {

    }

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

    public void setTitle(String title) {
        this.title = title;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public void setViews(int views) {
        this.views = views;
    }

    @Override
    public String toString() {
        return "Blog{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", author='" + author + '\'' +
                ", createTime=" + createTime +
                ", views=" + views +
                '}';
    }
}

创建BlogMapper接口

public interface BlogMapper {
    //插入数据
    int addBlog(Blog blog);

    //查询博客
    List<Blog> queryBlogIf(Map map);
}

创建BlogMapper.xml

<mapper namespace="com.kuang.dao.BlogMapper">

    <insert id="addBlog" parameterType="blog">
        insert into test.blog(id,title,author,create_time,views )
        values (#{id},#{title},#{author},#{createTime},#{views});
    </insert>
</mapper>

测试:

    @Test
    public void addInitBlog(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId(IdUtils.getId());
        blog.setTitle("Mybatis如此简单");
        blog.setAuthor("狂神");
        blog.setCreateTime(new Date());
        blog.setViews(9999);;
        mapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("Java如此简单");
        mapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("Spring如此简单");
        mapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("微服务如此简单");
        mapper.addBlog(blog);

        sqlSession.close();
    }

where标签

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

    <select id="queryBlogIf" resultType="blog" parameterType="map">
        select * from test.blog 
    <where>
        <if test = "title != null">
            and title = #{title}
        </if>
        <if test="author !=null">
            and author = #{author}
        </if>
    </where>
    </select>
*  <trim prefix="WHERE" prefixOverrides="AND |OR ">可以实现和<where>标签同样的功能
```xml
    <select id="queryBlogIf" resultType="blog" parameterType="map">
        select * from test.blog 
    <<trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test = "title != null">
            and title = #{title}
        </if>
        <if test="author !=null">
            and author = #{author}
        </if>
    </trim>
    </select>

trim 元素经常与 if 元素结合使用，以根据条件动态地构建SQL语句。prefix="WHERE" 表示如果 trim 元素内部有任何内容（即至少有一个 if 条件为真），则在整个 trim 元素的内容前添加 WHERE 关键字。prefixOverrides="AND |OR " 表示如果 trim 元素的内容以 AND 或 OR 开头，则这些关键字会被去除。

##### choose、when、otherwise+where
MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。
```xml
<mapper namespace="com.kuang.dao.BlogMapper">
 <select id="queryBlogChoose" resultType="Blog">
        select * from test.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>
</mapper>

传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形。若两者都没有就按views查,如果views也没有传参,那查询为空.查看日志返回局域为select * from test.blog WHERE views = ?.

set

用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列

在Blogmapper.xml中新增更新语句

<!--    更新博客-->
    <update id="updateBlog">
        update test.blog
        <set>
            <if test="title !=null">
                title = #{title}
            </if>
            <if test ="author != null">
                author = #{author}
            </if>
        </set>
        where Id = #{id}
    </update>

如果在更新时,设置了title那么set会自动添加到更新语句中,如果没有设置title那么set元素会自动隐藏,也就是说set 元素会动态地在行首插入 SET 关键字.

测试

    @Test
    public void Update(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap<>();
        map.put("author","狂神");
        map.put("id","2035c436585f41719d9570fd1a916dc0");
//        map.put("title","Java如此简单");
        mapper.updateBlog(map);
    }

sql片段

Foreach

你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。在大多数情况下，你可能不需要在中使用index，特别是当你只关心集合中的值时。

在BlogMapper中添加接口

    //查询1-3号记录的博客
    List<Blog> queryBlogForeach(Map map);

在BlogMapper.xml中实现接口(实现的sql语句为:SELECT * FROM test.blog where Id in (1,2,3))

    <select id="queryBlogForeach" resultType="com.kuang.pojo.Blog">
        select * from test.blog
<where>
            id in
                <foreach collection="ids" item="id"  open="(" separator="," close=")">
                    #{id}
                </foreach>
</where>
    </select>

测试:

@Test
    public void Foreach(){
        SqlSession sqlSession = new MybatisUtils().getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap<>();
        ArrayList<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        map.put("ids",ids);
        List<Blog> blogs = mapper.queryBlogForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
    }

缓存

默认情况下，只启用了本地的会话缓存(也就是SqlSession)，它仅仅对一个会话中的数据进行缓存。当关闭sqlSession.close();本地(也就是一级缓存)也就失效了.
在sqlSession内可以手动清理缓存:sqlSession.clearCache();
二级缓存

要启用全局的二级缓存，只需要在你的 SQL 映射文件中添加一行：
如果使用注解,那么在*Mapper接口中使用@CacheNamespace,即可使用默认二级缓存
在 Java 中，序列化是将对象的状态转换为可以存储或传输的形式的过程。当对象实现了 Serializable 接口时，Java 运行时环境（JRE）可以自动将其状态写入一个持久的存储区，比如文件或网络。同样地，也可以从存储区中恢复该对象的状态。要确保 com.kuang.pojo.User 类实现了 Serializable 接口
public class User implements Serializable
只有当sqlSession关闭或提交事务后,二级缓存才生效.
测试:

    public void test2(){
         SqlSession sqlSession = new MybatisUtils().getSqlSession();
         SqlSession sqlSession2 = new MybatisUtils().getSqlSession();


         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
         UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);

         User userById = mapper.getUserById("id", String.valueOf(1));
         System.out.println(userById);
//         关闭sqlSession后再次查询就会从二级缓存中读取
         sqlSession.close();
         System.out.println("===");
         
         User userById2 = mapper2.getUserById("id", String.valueOf(1));
         System.out.println(userById2);
         sqlSession.close();
     }

posted @ 2024-05-16 01:06 文采杰出阅读(3) 评论(0) 编辑收藏举报

刷新页面返回顶部

文采杰出