Mybatis
1.简介
1.1 mybatis
MyBatis 是一款优秀的持久层框架,它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息,将接口和 Java 的 POJOs(Plain Ordinary Java Object,普通的 Java对象)映射成数据库中的记录。
获取
- maven
- github
1.2 持久层
持久化:将程序数据在持久状态和瞬时状态间转换的机制。通俗的讲,就是瞬时数据(比如内存中的数据,是不能永久保存的)持久化为持久数据(比如持久化至数据库中,能够长久保存)。
持久层:完成持久化工作的代码块
2.第一个程序
2.1 搭建环境
搭建数据库
create database `mybatis`;
use mybatis;
create table user(
id int(20) not null primary key,
name varchar(30) default null,
pwd varchar(30) default null
)engine=innodb default charset=utf8;
insert into user values(1,'name','pwd');
新建项目(IDEA)
- 新建maven项目
- 删除 src 目录
- 导入依赖
2.2 新建模块(基于上述项目, maven)
-
配置mybatis
新建/src/resources/
mybatis-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://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> <mappers> <mapper resource="cqu/ma/dao/UserMapper.xml"/> </mappers> </configuration>
-
编写工具类 (获取 SqlSession 对象)
package cqu.ma.utils; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; public class MybatisUtil { private static SqlSessionFactory sqlSessionFactory; static{ try { //使用 mybatis 第一步:获取 sqlSessionsFactory 对象 String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } } //通过 sqlSessionFactory 获取 sqlSession 对象 //SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句 public static SqlSession getSqlSession(){ return sqlSessionFactory.openSession(); } }
2.3 编写代码
-
pojo.User (简单类对于数据库 user表)
package cqu.ma.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{" +this.id +"," +this.name +"," +this.pwd +"}"; } }
-
dao.UserDao (dao 接口)
package cqu.ma.dao; import cqu.ma.pojo.User; import java.util.List; public interface UserDao { public List<User> getUserList(); }
-
dao/
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="cqu.ma.dao.UserDao"> <!--id=方法名--> <select id="getUserList" resultType="cqu.ma.pojo.User"> select * from mybatis.user </select> </mapper>
2.4 测试
注意:
-
mybatis-config.xml 中需要注册 对应的mapper.xml
-
maven约定大于配置,可能写的配置文件无法导出
解决:在 pom.xml 中添加<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>
测试代码
package cqu.ma.dao;
import cqu.ma.pojo.User;
import cqu.ma.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserDaoTest {
@Test
public void test(){
//1.获取 sqlSession
SqlSession sqlSession = MybatisUtil.getSqlSession();
//2.执行 sql
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> userList = userDao.getUserList();
for(User user:userList){
System.out.println(user);
}
//3.关闭
sqlSession.close();
}
}
2.5 总结
- mybatis-config.xml 核心配置文件
- 由该配置文件创建一个 SqlSessionFactory 对象
- 由SqlSessionFactory 对象 获取 SqlSession 对象
- dao接口类,mapper.xml 代替 dao接口实现类,并在 mabatis-config.xml中注册该 mapper.xml
- 测试
3. CRUD
dao/mapper
package cqu.ma.dao;
import cqu.ma.pojo.User;
import java.util.List;
public interface UserMapper {
public List<User> getUserList();
public User getUserById(int id);
public int addUser(User user);
public int updateUser(User user);
public int deleteUser(int id);
}
mapper.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="cqu.ma.dao.UserMapper">
<!-- id=方法名 -->
<select id="getUserList" resultType="cqu.ma.pojo.User">
select * from mybatis.user
</select>
<select id="getUserById" parameterType="int" resultType="cqu.ma.pojo.User">
select * from mybatis.user where id = #{id}
</select>
<!--对象中的属性可以直接取出来-->
<insert id="addUser" parameterType="cqu.ma.pojo.User">
insert into mybatis.user(id,name,pwd) values(#{id},#{name},#{pwd})
</insert>
<update id="updateUser" parameterType="cqu.ma.pojo.User">
update mybatis.user set name=#{name}, pwd=#{pwd} where id=#{id}
</update>
<delete id="deleteUser" parameterType="int">
delete from mybatis.user where id=#{id}
</delete>
</mapper>
test
package cqu.ma.dao;
import cqu.ma.pojo.User;
import cqu.ma.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserDaoTest {
@Test
public void test(){
//1.获取 sqlSession
SqlSession sqlSession = MybatisUtil.getSqlSession();
//2.执行 sql
//方式1:getMapper
UserMapper userDao = sqlSession.getMapper(UserMapper.class);
List<User> userList = userDao.getUserList();
//方式2:
//List<User> userList = sqlSession.selectList("cqu.ma.dao.UserDao.getUserList");
for(User user:userList){
System.out.println(user);
}
//3.关闭
sqlSession.close();
}
@Test
public void getUserById(){
SqlSession session = MybatisUtil.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.getUserById(1);
System.out.println(user);
session.close();
}
@Test
public void addUser(){
//增删改需要提交事务
SqlSession session = MybatisUtil.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
int n = mapper.addUser(new User(3,"用户3","333"));
System.out.println(n);
//提交事务
session.commit();
session.close();
}
@Test
public void updateUser(){
SqlSession session = MybatisUtil.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
int n = mapper.updateUser(new User(3,"新用户3","11111"));
System.out.println(n);
session.commit();
session.close();
}
@Test
public void deleteUser(){
SqlSession session = MybatisUtil.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
int n = mapper.deleteUser(3);
System.out.println(n);
session.commit();
session.close();
}
}
模糊查询
select * from mybatis.user where name like "%"#{value}"%"
4.配置解析
4.1 核心配置文件
- mybatis-config.xml
- MyBatis 的配置文件包含了会深深影响 MyBatis 行为的设置和属性信息。 配置文档的顶层结构如下:
- configuration(配置)
- properties(属性)
- settings(设置)
- typeAliases(类型别名)
- typeHandlers(类型处理器)
- objectFactory(对象工厂)
- plugins(插件)
- environments(环境配置)
- environment(环境变量)
- transactionManager(事务管理器)
- dataSource(数据源)
- environment(环境变量)
- databaseIdProvider(数据库厂商标识)
- mappers(映射器)
- configuration(配置)
4.2 environments
MyBatis 可以配置成适应多种环境,这种机制有助于将 SQL 映射应用于多种数据库之中, 现实情况下有多种理由需要这么做。例如,开发、测试和生产环境需要有不同的配置;或者想在具有相同 Schema 的多个生产数据库中使用相同的 SQL 映射。还有许多类似的使用场景。
不过要记住:尽管可以配置多个环境,但每个 SqlSessionFactory 实例只能选择一种环境。
4.3 properties
这些属性可以在外部进行配置,并可以进行动态替换。你既可以在典型的 Java 属性文件中配置这些属性,也可以在 properties 元素的子元素中设置。
4.4 类型别名
<typeAliases>
<typeAlias type="cqu.ma.pojo.User" alias="User"></typeAlias>
</typeAliases>
已有的
别名 | 映射的类型 |
---|---|
_byte | byte |
_long | long |
_short | short |
_int | int |
_integer | int |
_double | double |
_float | float |
_boolean | boolean |
string | String |
byte | Byte |
long | Long |
short | Short |
int | Integer |
integer | Integer |
double | Double |
float | Float |
boolean | Boolean |
date | Date |
decimal | BigDecimal |
bigdecimal | BigDecimal |
object | Object |
map | Map |
hashmap | HashMap |
list | List |
arraylist | ArrayList |
collection | Collection |
iterator | Iterator |
5.resultMap
解决java类中属性名与数据库表中字段名不一致问题
<?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="cqu.ma.dao.UserMapper">
<!--id 名字-->
<resultMap id="UserMap" type="User">
<!--column=数据库表中字段名,property=实体类中属性名-->
<result column="pwd" property="password"></result>
</resultMap>
<!-- id=方法名 -->
<select id="getUserList" resultMap="UserMap">
select * from mybatis.user
</select>
<select id="getUserById" parameterType="int" resultType="cqu.ma.pojo.User">
select * from mybatis.user where id = #{id}
</select>
</mapper>
6.日志
6.1 日志工厂
settings
logImpl | 指定 MyBatis 所用日志的具体实现,未指定时将自动查找。 | SLF4J | LOG4J | LOG4J2 | JDK_LOGGING | COMMONS_LOGGING | STDOUT_LOGGING | NO_LOGGING |
---|---|---|
STDOUT_LOGGING 标准日志工厂
6.2 Log4j
Log4j是Apache的一个开源项目,通过使用Log4j,我们可以控制日志信息输送的目的地是控制台、文件、GUI组件,甚至是套接口服务器、NT的事件记录器、UNIX Syslog守护进程等
-
导入
<!-- https://mvnrepository.com/artifact/log4j/log4j --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency>
-
log4j.properties
#将等级为DEBUG的日志信息输出到console和file log4j.rootLogger=DEBUG,console,file #控制台 console log4j.appender.console=org.apache.log4j.ConsoleAppender log4j.appender.console.Target=System.out log4j.appender.console.Threshold=DEBUG log4j.appender.console.layout=org.apache.log4j.PatternLayout log4j.appender.console.layout.ConversionPattern=[%c]-%m%n #文件 file log4j.appender.file=org.apache.log4j.RollingFileAppender log4j.appender.file.File=./log/ma.log log4j.appender.file.MaxFileSize=10mb log4j.appender.file.Threshold=DEBUG log4j.appender.file.layout=org.apache.log4j.PatternLayout log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n #日志输出级别 log4j.logger.org.mybatis=DEBUG log4j.logger.java.sql=DEBUG log4j.logger.java.sql.Statement=DEBUG log4j.logger.java.slq.ResultSet=DEBUG log4j.logger.java.sql.PrepareStatement=DEBUG
-
mybatis-config.xml
<settings> <!-- <setting name="logImpl" value="STDOUT_LOGGING"/>--> <setting name="logImpl" value="LOG4J"/> </settings>
代码中使用Log4j
package cqu.ma;
import org.apache.log4j.Logger;
import org.junit.Test;
public class Log4jTest {
//参数为当前类 class 对象
static Logger logger = Logger.getLogger(Log4jTest.class);
@Test
public void test(){
logger.info("info: test");
logger.debug("debug:test");
logger.error("error:test");
}
}
7.分页
select * from `user` limit startIndex,pageSize
8.使用注解开发
适用于简单语句,不用编写 mapper.xml
package cqu.ma.dao;
import cqu.ma.pojo.User;
import org.apache.ibatis.annotations.Select;
import java.util.List;
import java.util.Map;
public interface UserMapper {
@Select("select * from mybatis.user")
public List<User> getUserList();
}
mybatis-config.xml
<mappers>
<!-- <mapper resource="cqu/ma/dao/UserMapper.xml"/>-->
<!--绑定接口-->
<mapper class="cqu.ma.dao.UserMapper"></mapper>
</mappers>
9.Lombok
- IDEA 安装 lombok (IDEA2021 默认已安装)
- 导入lombok依赖
- 在实体类上加注解
@Getter and @Setter
@FieldNameConstants
@ToString
@EqualsAndHashCode
@AllArgsConstructor, @RequiredArgsConstructor and @NoArgsConstructor
@Log, @Log4j, @Log4j2, @Slf4j, @XSlf4j, @CommonsLog, @JBossLog, @Flogger, @CustomLog
@Data
@Builder
@SuperBuilder
@Singular
@Delegate
@Value
@Accessors
@Wither
@With
@SneakyThrows
@val
@var
experimental @var
@UtilityClass
@Data : 无参构造,getter,setter,toString,hashCode,equals
@AllArgsConstructor() //显示定义有参构造,若需要无参构造仍需要显示定义
@NoArgsConstructor()
10.多对一
package cqu.ma.pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private Teacher teacher;
}
package cqu.ma.pojo;
import lombok.Data;
@Data
public class Teacher {
private int id;
private String name;
}
按照查询嵌套处理
<?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="cqu.ma.dao.StudentMapper">
<resultMap id="StudentMap" type="cqu.ma.pojo.Student">
<result column="id" property="id"></result>
<result column="name" property="name"></result>
<!--复杂属性
对象 : association
集合 : collection
-->
<association property="teacher" column="tid" javaType="cqu.ma.pojo.Teacher" select="getTeacher"></association>
</resultMap>
<select id="getStudents" resultMap="StudentMap">
select * from student
</select>
<select id="getTeacher" resultType="cqu.ma.pojo.Teacher">
select * from teacher where id=#{id}
</select>
</mapper>
按照结果嵌套处理
<select id="getStudents2" resultMap="StudentMap2">
select s.id sid, s.name sname, t.id tid, t.name tname
from student s, teacher t
where s.tid=t.id;
</select>
<resultMap id="StudentMap2" type="cqu.ma.pojo.Student">
<result property="id" column="sid"></result>
<result property="name" column="sname"></result>
<association property="teacher" javaType="cqu.ma.pojo.Teacher">
<result property="id" column="tid"></result>
<result property="name" column="tname"></result>
</association>
</resultMap>
11.一对多
package cqu.ma.pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private int tid;
}
package cqu.ma.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
private List<Student> students;
}
<?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="cqu.ma.dao.TeacherMapper">
<!--按结果嵌套-->
<select id="getTeacher" resultMap="TeacherMap">
select s.id sid, s.name sname, t.name tname, t.id tid
from teacher t, student s
where s.tid=t.id and t.id=#{tid}
</select>
<resultMap id="TeacherMap" type="cqu.ma.pojo.Teacher">
<result property="id" column="tid"></result>
<result property="name" column="tname"></result>
<!--复杂属性-->
<collection property="students" ofType="cqu.ma.pojo.Student">
<result property="id" column="sid"></result>
<result property="name" column="sname"></result>
<result property="tid" column="tid"></result>
</collection>
</resultMap>
<!--按照查询嵌套-->
<select id="getTeacher2" resultMap="TeacherMap2">
select * from teacher where id=#{tid}
</select>
<resultMap id="TeacherMap2" type="cqu.ma.pojo.Teacher">
<result property="id" column="id"></result>
<collection property="students" column="id" javaType="ArrayList" ofType="cqu.ma.pojo.Student" select="getStudentsByTid"></collection>
</resultMap>
<select id="getStudentsByTid" resultType="cqu.ma.pojo.Student">
select * from student where tid=#{tid}
</select>
</mapper>
小结
- association 多对一
- collection 一对多
- javaType 指定实体类中属性类型
- ofType 指定泛型约束类型(如集合范型参数)
12.动态sql
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事,但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言,MyBatis 显著地提升了这一特性的易用性。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器,你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中,需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
IF
package cqu.ma.dao;
import cqu.ma.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
public int addBlog(Blog blog);
public List<Blog> getBlogsIF(Map map);
}
<?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="cqu.ma.dao.BlogMapper">
<insert id="addBlog" parameterType="cqu.ma.pojo.Blog">
insert into blog(id,title,author,create_time,views)
values(#{id},#{title},#{author},#{createTime},#{views})
</insert>
<select id="getBlogsIF" parameterType="map" resultType="cqu.ma.pojo.Blog">
select * from blog where 1=1
<if test="title != null">
and title=#{title}
</if>
<if test="author != null">
and author=#{author}
</if>
</select>
</mapper>
@Test
public void getBlogsIF(){
SqlSession session = MybatisUtil.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String,String> map = new HashMap<>();
// map.put("title","t1");
map.put("author","a1");
List<Blog> blogs = mapper.getBlogsIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
choose
类似 switch case
<select id="getBlogsChoose" parameterType="map" resultType="cqu.ma.pojo.Blog">
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, set)
where
select * from blog
<where>
<if test="title!=null">
title=#{title}
</if>
<if test="author!=null">
and author=#{author}
</if>
</where>
<!-- where 标签 处理条件 -->
set
<update id="updateBlogSET" parameterType="map">
update blog
<set>
<if test="title!=null">
title=#{title},
</if>
<if test="author!=null">
author=#{author}
</if>
</set>
where id=#{id}
</update>
foreach
<select id="getBlogsForeach" parameterType="map" resultType="cqu.ma.pojo.Blog">
select * from blog
<where>
<!--collection 集合名字 item 取出每个元素 命名w-->
<foreach collection="views" item="view" open="(" close=")" separator="or">
views=#{view}
</foreach>
</where>
</select>
@Test
public void getBlogsForeach(){
SqlSession session = MybatisUtil.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Map<String,Object> map = new HashMap<>();
List<Integer> views = new ArrayList<>();
views.add(0);
views.add(9);
map.put("views",views);
List<Blog> blogs = mapper.getBlogsForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
sql片段
<sql id="if-title-author">
0 <if test="title != null">
and title=#{title}
</if>
<if test="author != null">
and author=#{author}
</if>
</sql>
<select id="getBlogsIF" parameterType="map" resultType="cqu.ma.pojo.Blog">
select * from blog where 1=1
<include refid="if-title-author"></include>
</select>