mybatis学习笔记

1、第一个mybatis

导入依赖

?
1
2
3
4
5
//mysql依赖<br><dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <version>8.0.31</version>
 </dependency><br>//mybatis依赖<br><dependency><br>　　<groupId>org.mybatis</groupId><br>　　<artifactId>mybatis</artifactId><br>　　<version>3.5.11</version><br></dependency>

mybatis-config.xml

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://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://localhost:3306/db_stu?useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments><br><!--映射xml,可映射多个文件-->
    <mappers>
        <mapper resource="com/xx/dao/userMapper.xml"/>
    </mappers>
</configuration>

创建工具类

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
private static SqlSessionFactory sqlSessionFactory;
   static {
       try {
           String resource = "mybatis-config.xml";
           InputStream inputStream = Resources.getResourceAsStream(resource);
           sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
       } catch (IOException e) {
           throw new RuntimeException(e);
       }
   }
   public static SqlSession getSqlSession()
   {
        return  sqlSessionFactory.openSession();
   }

创建实体类

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
private int id;
    private String StudentName;
    private String gender;
    private int age;
 
    public Student() {
    }
 
    public Student(int id, String studentName, String gender, int age) {
        this.id = id;
        StudentName = studentName;
        this.gender = gender;
        this.age = age;
    }
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getStudentName() {
        return StudentName;
    }
 
    public void setStudentName(String studentName) {
        StudentName = studentName;
    }
 
    public String getGender() {
        return gender;
    }
 
    public void setGender(String gender) {
        this.gender = gender;
    }
 
    public int getAge() {
        return age;
    }
 
    public void setAge(int age) {
        this.age = age;
    }
 
    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", StudentName='" + StudentName + '\'' +
                ", gender='" + gender + '\'' +
                ", age=" + age +
                '}';
    }

创建接口

package com.xx.dao;
import com.xx.pojo.Student;
import java.util.List;

public interface studentMapper {
    //查询全部学生
    List<Student> getAllStudents();
    //根据学号查找学生
    Student getStudentById(int id);
    //添加学生
    int addStudent(Student stu);
    //修改信息
    int updateStudent(Student stu);
    //删除学生信息
    int deleteStudent(int id);
}

实现

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xx.dao.studentMapper">
    <select id="getAllStudents" resultType="com.xx.pojo.Student" >
        select * from db_stu.student
    </select>
    <select id="getStudentById" resultType="com.xx.pojo.Student" parameterType="int">
        select * from db_stu.student where id =#{id}
    </select>
    <insert id="addStudent" parameterType="com.xx.pojo.Student">
        insert into db_stu.student(id,StudentName,gender,age) values(#{id},#{StudentName},#{gender},#{age})
    </insert>
    <update id="updateStudent" parameterType="com.xx.pojo.Student">
        update db_stu.student set StudentName=#{StudentName},gender=#{gender},age=#{age} where id=#{id};
    </update>
    <delete id="deleteStudent" parameterType="int">
        delete from db_stu.student where id =#{id};
    </delete>
</mapper>

测试实例

@Test
public void addStudent()
{
    SqlSession sqlSession=mybatisUtil.getSqlSession();
    studentMapper stuMapper=sqlSession.getMapper(studentMapper.class);
   int res= stuMapper.addStudent(new Student(6,"无敌战胜","神",999));
   if(res>0)
   {
       System.out.println("插入成功");
       sqlSession.commit();
   }
    sqlSession.close();
}
@Test
public void queryAllStudents()
{
    SqlSession sqlSession=mybatisUtil.getSqlSession();
    studentMapper stuMapper=sqlSession.getMapper(studentMapper.class);
    List<Student> students=stuMapper.getAllStudents();
    for (Student student : students) {
        System.out.println(student);
    }
    sqlSession.close();
}
@Test
public void queryStudent()
{
    SqlSession sqlSession=mybatisUtil.getSqlSession();
    studentMapper stuMapper=sqlSession.getMapper(studentMapper.class);
    Student stu=stuMapper.getStudentById(2);
    System.out.println(stu);
    sqlSession.close();
}
@Test
public void updateStudnet()
{
    SqlSession sqlSession=mybatisUtil.getSqlSession();
    studentMapper mapper = sqlSession.getMapper(studentMapper.class);
    int res=mapper.updateStudent(new Student(6,"xxxiaoxing","男",19));
    if(res>0)
    {
        System.out.println("修改成功");
        sqlSession.commit();
    }
    sqlSession.close();
}
@Test
public void deleteStudent()
{
    SqlSession sqlSession=mybatisUtil.getSqlSession();
    studentMapper mapper = sqlSession.getMapper(studentMapper.class);
    int res=mapper.deleteStudent(6);
    if(res>0)
    {
        System.out.println("删除成功");
        sqlSession.commit();
    }
    sqlSession.close();
}

注意:增删改需提交事务(SqlSession.commit())//否则操作无效

maven资源过滤问题 

在pom.xml添加

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<!--在build中配置resources，来防止我们资源导出失败的问题-->
<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>

mybatis-config.xml讲解

 

properties

这些属性可以在外部进行配置，并可以进行动态替换。

?
1
<properties resource="jdbc.properties"/>
?
1
2
3
4
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>

typeAliases（为Java的POJO类起别名）

?
1
2
3
4
5
//type为POJO的完整类名，alias为别名<br><typeAliases>
        <typeAlias type="com.xx.pojo.Student" alias="Student"/>
        <typeAlias type="com.xx.pojo.Class" alias="Class"/>
        <typeAlias type="com.xx.pojo.Teacher" alias="Teacher"/>
</typeAliases>
?
1
2
3
//冗长问题<br>//name为POJO的包名<br>//默认起的别名为首字母小写<br><typeAliases>
      <package name="com.xx.pojo"/>
</typeAliases>

settting

?
1
2
3
<settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>//日志
        <setting name="mapUnderscoreToCamelCase" value="true"/>//开启驼峰命名<br> 　　　　//开启一级缓存<br> 　　　　<setting name="cacheEnabled" value="true"/>//默认开启，增加可读性<br><cache/><br><em id="__mceDel"></settings></em>

typeHandlers(此标签必须放置environments前和typeAliases后)

environments

?
1
2
3
4
5
6
7
8
9
10
11
<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="url"/>
                <property name="username" value="name"/>
                <property name="password" value="pwd"/>
            </dataSource>
        </environment>
</environments>

mappers(此标签用于配置映射文件)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<!--方式一-->
<mappers>
        <mapper resource="com/xx/dao/studentMapper.xml"/>
</mappers>
<!--方式二 不常见-->
<!--使用url引用本地文件>
<mappers>
        <mapper url="file://D:/com/xx/dao/student.xml"/>
</mappers>
<!--方式三-->
<!--使用class引入接口类-->
<!--接口名称与映射文件名称一致、接口与映射文件在同一个包中、映射文件mapper标签的命名空间的值为全限定类名-->
 <mappers>
        <mapper class="com.xx.dao.studentMapper"/>
</mappers>
<!--方式四-->
 <!--使用包名引入-->
  <!--DAO的实现类采用mapper动态代理实现、其他三个条件与类名引入条件一致-->
 <mappers>
       <package name="com.xx.dao"/>
 </mappers>

map

?
1
int addStu2(Map<String,Object> map);
?
1
2
3
<insert id="addStu2" parameterType="map">
        insert into dt_stu(id,studentName) values(#{sid},#{sname});
</insert>
?
1
2
3
4
5
6
7
8
9
10
11
12
@Test
public void addStu2()
{
        SqlSession sqlSession=mybatisUtil.getSqlSession();
        studentMapper mapper = sqlSession.getMapper(studentMapper.class);
        Map<String, Object> map = new HashMap<>();
        map.put("sid",10);
        map.put("sname","xx");
        mapper.addStu2(map);
        sqlSession.commit();
        sqlSession.close();
}

模糊查询（注：防sql注入）wh 

?
1
2
3
<select id="getLikeStudent" resultType="Student" parameterType="String">
        select * from dt_stu where studentName like "%"#{ssname}"%"
</select>
?
1
List<Student> stulist= mapper.getLikeStudent("%李%");
?
1
2
3
<select id="getLikeStudent" resultType="Student" parameterType="String">
        select * from dt_stu where studentName concat ('%',#{value},'%')
</select>

resultMap

?
1
2
3
4
5
6
7
8
<resultMap id="userMap" type="user">
        <result column="id" property="id"/>
        <result column="name" property="userName"/>
        <result column="age" property="age"/>
</resultMap>
<select id="getAllUser" resultMap="userMap">
        select * from user
</select>

配置日志

?
1
2
3
<settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

配置log4j

导入依赖

?
1
2
3
4
5
<dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
</dependency>

新建log4j.properties文件

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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.ImmediateFlush=true
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%p][%d{yyyy-MM-dd HH:mm:ss}][%c]%m%n
 
#设置文件输出
log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=log/log.text
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 HH:mm:ss}][%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

mybits修改日志文件类型

?
1
2
3
<settings>
        <setting name="logImpl" value="LOG4J"/>
</settings>

简单实用log4j

反射当前类

?
1
static  Logger logger=Logger.getLogger(mybatisTest.class);<br>logger.info("用户充值成功");<br>logger.debug("发生了错误");<br><em id="__mceDel"><em id="__mceDel"><em id="__mceDel">logger.error("用户强制充值");</em></em></em>

 LIMIT

接口类

?
1
List<Student> getStudentByLimit(Map<String,Object> map);

mapper.xml

?
1
2
3
<select id="getStudentByLimit" resultMap="studentMap" resultType="map">
        select * from dt_stu limit #{startIndex},#{pageSize}
</select>
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Test
    public void getStudentByLimit()
    {
        SqlSession sqlSession = mybatisUtil.getSqlSession();
        studentMapper mapper = sqlSession.getMapper(studentMapper.class);
        Map<String, Object> map = new HashMap<>();
        map.put("startIndex",0);
        map.put("pageSize",2);
        List<Student> studentByLimit = mapper.getStudentByLimit(map);
        for (Student student : studentByLimit) {
            System.out.println(student);
        }
        sqlSession.close();
    }

　　

RowBounds分页

接口类

?
1
List<Student> getStudentRowBounds();

mapper.xml

?
1
2
3
<select id="getStudentRowBounds" resultMap="studentMap">
        select * from dt_stu
</select>
?
1
2
3
4
5
6
7
8
9
10
11
@Test
    public void getStudentRowBounds()
    {
        SqlSession sqlSession = mybatisUtil.getSqlSession();
        RowBounds rowBounds = new RowBounds(0,2);
        List<Student>  students= sqlSession.selectList("com.xx.mapper.studentMapper.getStudentRowBounds",null,rowBounds);
        for (Student student : students) {
            System.out.println(student);
        }
        sqlSession.close();
    }

注解使用mybatis

@Select("select * from dt_stu")
List<Student> getAllStudents();

//方法存在多个参数，需添加@Param
@Select("select * from dt_stu where id = #{id} ")
Student getUserById(@Param("id") int id);

@Insert("insert into dt_stu(id,studentName,gender,age) values(#{id},#{studentName},#{gender},#{age})")
int addStudent(Student stu);
@Update("update dt_stu set studentName=#{studentName},gender=#{gender},age=#{age} where id=#{id}")
int updateStudent(Student student);
@Delete("delete from dt_stu where id = #{id}")
int deleteStu(@Param("id") int id);

mybatis-config.xml绑定接口

?
1
2
3
<mappers>
       <mapper class="com.xx.mapper.studentMapper"/>
 </mappers>

设置autoCommit

?
1
sqlSessionFactory.openSession(true);//设置自动提交
?
1
2
3
//方法存在多个参数，需添加@Param
@Select("select * from dt_stu where id = #{id} and studentName=#{name}")
Student getUserById(@Param("id") int id,@Param("name") String name);@Param("id")要与#{id}的名字相同

@Parma()注解

多个基本类型加上，应用类型不需要添加，单个基本类型可忽略

Lombok插件（注解添加getter，setter，toString方法）

1.使用

（1）下载插件

 (2)导入maven依赖

?
1
2
3
4
5
6
<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.26</version>
            <scope>provided</scope>
</dependency>

（3）LomBok参数

 多对一查询

按照查询处理（子查询）

javaType指定属性类型

ofType集合中的泛型信息

?
1
2
3
4
5
6
7
8
9
10
11
<resultMap id="stuMap" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="teacher" javaType="Teacher" column="tid"  select="getTeacher"/>
    </resultMap>
    <select id="getAllStudent" resultMap="stuMap">
        select * from student
    </select>
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id =#{id}
 </select>

 按照结果嵌套处理

?
1
2
3
4
5
6
7
8
9
10
11
12
<select id="getAllStudent2" resultMap="stuMap2">
 select s.id as sid,s.name sname,t.id tid,t.name tname
 where s.tid=t.id
</select>
    <resultMap id="stuMap2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
</resultMap>

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="getTeacher" resultMap="TeacherStu">
        select t.id tid,t.name tname,s.id sid,s.name sname
        from student s,teacher t
        where s.tid=t.id
    </select>
    <resultMap id="TeacherStu" 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>
?
1
2
3
4
5
6
7
8
9
10
<select id="getTeacher2" resultMap="TeacherStu2">
        select * from teacher where id =#{tid}
    </select>
    <resultMap id="TeacherStu2" type="Teacher">
        <result property="id" column="id"/>
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudent" column="id"/>
    </resultMap>
    <select id="getStudent" resultType="Student">
        select * from student where tid=#{tid}
</select>

动态查询

?
1
where、if
?
1
2
3
4
5
6
7
8
9
10
11
<select id="queryBlogIF" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <if test="title!=''">
                title=#{title}
            </if>
            <if test="author!=''">
                and author =#{author}
            </if>
        </where>
</select>
?
1
choose (when, otherwise)//相当于switch语句)
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<select id="queryBlogChoose" resultType="Blog" parameterType="map">
        select * from blog
        <where>
            <choose>
                <when test="title!=null">
                    title=#{title}
                </when>
                <when test="author!=null">
                    author=#{author}
                </when>
                <otherwise>
                    views=#{views}
                </otherwise>
            </choose>
        </where>
</select>
?
1
trim如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：
?
1
2
3
4
5
<trim prefix="WHERE" prefixOverrides="AND |OR ">prefixOverrides属性(前缀)
  ...
</trim><trim prefix="SET" suffixOverrides=","> //suffixOverrides属性（后缀）
  ...
</trim>
?
1
set、if
<update id="updateBlog" parameterType="map">
    update blog
    <set>
        <if test="title!=null">title=#{title},</if>
        <if test="author!=null">author=#{author},</if>
        <if test="createTime!=null">create_time=#{createTime},</if>
        <if test="views!=null">views=#{views}</if>
        where id =#{id}
    </set>
</update>
?
1
foreach
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<!--collectoin为集合 映射值 item的值与#{author}对应-->
<select id="getBlog3" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <foreach collection="authors" item="author" open="(author=" separator="or"  close=")" >
                #{author}
            </foreach>
        </where>
</select>
 
@Test
public  void getBlog3()
{
    SqlSession sqlSession = mybatisUtil.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map<String, Object> map = new HashMap<>();
    List<String> objects = new ArrayList<>();
    objects.add("test1");<br>    objects.add("test2");
    map.put("authors",objects);
    List<Blog> blogList = mapper.getBlog3(map);
    for (Blog blog : blogList) {
        System.out.println(blog);
    }
    sqlSession.close();
}

获取随机的id

?
1
2
3
4
public static String getId()
{
        return UUID.randomUUID().toString().replaceAll("-","");
}

SQL片段（处理多代码放一个地方）

?
1
2
3
4
5
6
7
<select id="getBlog2" resultType="Blog" parameterType="map">
        ...
        <include refid="xx"></include>
    </select>
<sql id="xx">
        ...
</sql>
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<sql id="id_title_authorValue">
        <if test="id!=null">
            id=#{id}
        </if>
        <if test="title!=null">
            and title=#{title}
        </if>
        <if test="author!=null">
           and author=#{author}
        </if>
    </sql>
<select id="getBlog2" resultType="Blog" parameterType="map">
        select * from blog
        <where>
            <include refid="id_title_authorValue"></include>//使用include标签引入sql、refig="" 为sql标签里的id
        </where>
</select>

最好基于单表来定义SQL片段

sql片段不要存在where标签

 

cache(一级缓存默认开启)同个mapper有效

缓存失效的情况

?
1
2
3
增删改语句会刷新缓存。
查询不一样的对象
手动清理缓存//sqlSession.clearCache();

二级缓存（会话提交或关闭的时候才会提交到二级缓存）

启用二级缓存

<setting name="cacheEnabled" value="true"/>//默认开启，增加可读性
<cache/>
//增加属性
<cache
  eviction="FIFO"
  flushInterval="60000"
  size="512"
  readOnly="true"/>
配置创建了一个 FIFO 缓存，每隔 60 秒刷新，最多可以存储结果对象或列表的 512 个引用，而且返回的对象被认为是只读的
sql语句中的  useCache="true"使用缓存
  • LRU – 最近最少使用：移除最长时间不被使用的对象。
  • FIFO – 先进先出：按对象进入缓存的顺序来移除它们。
  • SOFT – 软引用：基于垃圾回收器状态和软引用规则移除对象。
  • WEAK – 弱引用：更积极地基于垃圾收集器状态和弱引用规则移除对象。

mybatis-config.xml完整配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties"/>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/><!--开启驼峰命名格式-->
        <setting name="cacheEnabled" value="true"/>
    </settings>
    <typeAliases>
      <package name="com.xx.pojo"/>
    </typeAliases>
        <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.xx.mapper"/>
    </mappers>
</configuration>

　　

pageHelper

https://pagehelper.github.io/

 

 

posted @   xiaoxing~  阅读(75)  评论(0)  编辑  收藏  举报
相关博文：
·  SSM框架整合
·  Servlet学习笔记
·  Mybatis学习
·  MyBatis基础
·  MyBatis自学笔记（狂神说）
阅读排行：
· 地球OL攻略 —— 某应届生求职总结
· 周边上新：园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源！
· 提示词工程——AI应用必不可少的技术
· .NET周刊【3月第1期 2025-03-02】
点击右上角即可分享
微信分享提示
SQL AI 助手