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关联的嵌套结果映射
  1. 创建两个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 @   文采杰出  阅读(7)  评论(0)  编辑  收藏  举报
相关博文：
· Spring框架集成Mybatis
· mybatis中@Mapper使用介绍
· Mybatis学习
· Mybatis学习
· Mybatis学习总结
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛（1）
点击右上角即可分享
微信分享提示