mybatis学习笔记
1、第一个mybatis
导入依赖
//mysql依赖
<dependency> <groupId>com.mysql</groupId> <artifactId>mysql-connector-j</artifactId> <version>8.0.31</version> </dependency>
//mybatis依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.11</version>
</dependency>
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>
<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&useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--映射xml,可映射多个文件-->
<mappers>
<mapper resource="com/xx/dao/userMapper.xml"/>
</mappers>
</configuration>
创建工具类
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();
}
创建实体类
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添加
<!--在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
这些属性可以在外部进行配置，并可以进行动态替换。
<properties resource="jdbc.properties"/>
<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类起别名）
//type为POJO的完整类名，alias为别名
<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>
//冗长问题
//name为POJO的包名
//默认起的别名为首字母小写
<typeAliases> <package name="com.xx.pojo"/> </typeAliases>
settting
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>//日志
<setting name="mapUnderscoreToCamelCase" value="true"/>//开启驼峰命名
//开启一级缓存
<setting name="cacheEnabled" value="true"/>//默认开启，增加可读性
<cache/>
</settings>
typeHandlers(此标签必须放置environments前和typeAliases后)
environments
<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(此标签用于配置映射文件)
<!--方式一-->
<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
int addStu2(Map<String,Object> map);
<insert id="addStu2" parameterType="map">
insert into dt_stu(id,studentName) values(#{sid},#{sname});
</insert>
@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
<select id="getLikeStudent" resultType="Student" parameterType="String">
select * from dt_stu where studentName like "%"#{ssname}"%"
</select>
List<Student> stulist= mapper.getLikeStudent("%李%");
<select id="getLikeStudent" resultType="Student" parameterType="String">
select * from dt_stu where studentName concat ('%',#{value},'%')
</select>
resultMap
<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>
配置日志
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
配置log4j
导入依赖
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
新建log4j.properties文件
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修改日志文件类型
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
简单实用log4j
反射当前类
static Logger logger=Logger.getLogger(mybatisTest.class);
logger.info("用户充值成功");
logger.debug("发生了错误");
logger.error("用户强制充值");
LIMIT
接口类
List<Student> getStudentByLimit(Map<String,Object> map);
mapper.xml
<select id="getStudentByLimit" resultMap="studentMap" resultType="map">
select * from dt_stu limit #{startIndex},#{pageSize}
</select>
@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分页
接口类
List<Student> getStudentRowBounds();
mapper.xml
<select id="getStudentRowBounds" resultMap="studentMap">
select * from dt_stu
</select>
@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绑定接口
<mappers>
<mapper class="com.xx.mapper.studentMapper"/>
</mappers>
设置autoCommit
sqlSessionFactory.openSession(true);//设置自动提交
//方法存在多个参数，需添加@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依赖
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.26</version>
<scope>provided</scope>
</dependency>
（3）LomBok参数
多对一查询
按照查询处理（子查询）
javaType指定属性类型
ofType集合中的泛型信息
<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>
按照结果嵌套处理
<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>
<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>
<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>
动态查询
where、if
<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>
choose (when, otherwise)//相当于switch语句)
<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>
trim
如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：
<trim prefix="WHERE" prefixOverrides="AND |OR ">prefixOverrides属性(前缀) ... </trim>
<trim prefix="SET" suffixOverrides=","> //suffixOverrides属性（后缀）
...
</trim>
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>
foreach
<!--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");
objects.add("test2");
map.put("authors",objects);
List<Blog> blogList = mapper.getBlog3(map);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.close();
}
获取随机的id
public static String getId()
{
return UUID.randomUUID().toString().replaceAll("-","");
}
SQL片段（处理多代码放一个地方）
<select id="getBlog2" resultType="Blog" parameterType="map">
...
<include refid="xx"></include>
</select>
<sql id="xx">
...
</sql>
<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有效
缓存失效的情况
增删改语句会刷新缓存。 查询不一样的对象 手动清理缓存//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/
浙公网安备 33010602011771号