什么是 MyBatis ?
Apahce的⼀个开源项⽬ ⼀款优秀的持久层框架,它⽀持⾃定义 SQL、存储过程以及⾼级映射 免除了⼏乎所有的
JDBC 代码以及设置参数和获取结果集的⼯作 通过简单的 XML 或注解来配置和映射 Java对象 到 数据库中的记录
官⽅地址:https://mybatis.org/mybatis-3/
新建⼀个测试的maven项⽬ 依赖地址
https://mvnrepository.com/artifact/org.mybatis/mybatis/3.5.4
核⼼流程:
https://mybatis.org/mybatis-3/zh/getting-started.html
每个基于 MyBatis的应⽤都是以⼀个 SqlSessionFactory 的实例为核⼼ SqlSessionFactory 的实例可以通过
SqlSessionFactoryBuilder 获得 SqlSessionFactoryBuilder 可以从 XML
配置⽂件或⼀个预先配置的 Configuration 实例来构 建出 SqlSessionFactory 实例 ⼯⼚设计模式⾥⾯
需要获取SqlSession ,⾥⾯提供了在数据库执⾏ SQL 命令所需的所有⽅ 法
Mybaits3.X项目代码实现:
一.pom.xml添加maven依赖
<dependencies>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<!-- 使⽤JDBC链接mysql的驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
</dependencies>
二.配置全局配置文件: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://127.0.0.1:3306/xdclass?useUnicode=true&characterEncoding=utf-8&useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="xdclass.net"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/VideoMapper.xml"/>
</mappers>
</configuration>
三.mappers中的配置文件 videoMapper.xml
mappers中可以放很多xml文件,负责各类接口,这里只用了一个videoMapper.xml
namespace: 名称空间,一般需要保持全局唯一, 最好是和dao层的java接口一致, 可以映射 sql语句
到对应的方法名称和参数、返回类型mybatis是使用接口动态代理
<?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层的java接口一致,
可以映射 sql语句 到对应的方法名称和参数、返回类型
mybatis是使用接口动态代理
-->
<mapper namespace="net.xdclass.online_class.dao.VideoMapper">
<!--
statement sql
id: 当前mapper下需要唯一
resultType : sql查询结果集的封装
-->
<select id="selectById" parameterType="java.lang.Integer" resultType="net.xdclass.online_class.domain.Video">
select * from video where id = #{video_id,jdbcType=INTEGER}
</select>
<select id="selectListByXML" resultType="net.xdclass.online_class.domain.Video">
select * from video
</select>
<select id="selectByPointAndTitleLike" resultType="net.xdclass.online_class.domain.Video">
select * from video where point=#{point,jdbcType=DOUBLE} and title like concat('%', #{title,jdbcType=VARCHAR},'%')
</select>
</mapper>
四.namespace="net.xdclass.online_class.dao.VideoMapper"对应的java文件
@Param(“video_id”) 一个参数可以不用别名 如果多个参数一定要用Param别名
package net.xdclass.online_class.dao;
import net.xdclass.online_class.domain.Video;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface VideoMapper {
/**
* 根据视频id查找视频对象
* @param videoId
* @return
*/
Video selectById(@Param("video_id") int videoId);
//Video selectById(int videoId);
/**
* 查询全部视频列表
* @return
*/
List<Video> selectListByXML();
/**
* 查询全部视频列表
* @return
*/
@Select("select * from video")
List<Video> selectList();
/**
* 根据评分和标题模糊查询
* @param point
* @param title
* @return
*/
List<Video> selectByPointAndTitleLike(@Param("point") double point, @Param("title") String title);
}
ps:如果接口是很简单的一个sql语句,那么注解是比较方便的一种方法,就不用xml对接了
获取参数中的值
注意 :取java对象的某个值,属性名⼤⼩写要⼀致#{value} : 推荐使⽤, 是java的名称
${value} : 不推荐使⽤,存在sql注⼊⻛险
五.主函数启动类:SqlSessionDemo
package net.xdclass.online_class;
import net.xdclass.online_class.dao.VideoMapper;
import net.xdclass.online_class.domain.Video;
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;
import java.util.List;
public class SqlSessionDemo {
public static void main(String [] args) throws IOException {
String resouce = "config/mybatis-config.xml";
//读取配置文件
InputStream inputStream = Resources.getResourceAsStream(resouce);
//构建Session工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取Session
try(SqlSession sqlSession = sqlSessionFactory.openSession()){
VideoMapper videoMapper = sqlSession.getMapper(VideoMapper.class);
Video video = videoMapper.selectById(44);
// System.out.println(video.toString());
//通过注解
List<Video> videoList = videoMapper.selectList();
//List<Video> videoList = videoMapper.selectListByXML();
//System.out.println(videoList.toString());
//多参数查询
List<Video> videoList2 = videoMapper.selectByPointAndTitleLike(8.7,"HTML");
System.out.println(videoList2.toString());
}
}
}
控制台运行结果:
(这里用到了控制台调试debug打印,可见下文调试之控制台打印sql方法)
知识点一:Mybatis的开发调试之控制台打印sql
步骤一:pom.xml加入
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.5</version>
</dependency>
</dependencies>
步骤二:加入log4j.properties配置文件
log4j.rootLogger=ERROR, stdout
log4j.logger.net.xdclass=DEBUG
#trace内容会打印更多
#log4j.logger.net.xdclass=TRACE
#细化到打印某个mapper
#log4j.logger.net.xdclass.online_class.dao.VideoMapper.selectById=TRACE
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
知识点二:Mybatis参数别名的查询使用
默认参数查询
单个参数,可以使⽤别名,也可以使⽤默认的名称,默认名称的话可以随意
使⽤参数别名
模糊查询
mysql⾃带函数使⽤
<select id="selectByPointAndTitleLike"
resultType="net.xdclass.online_class.domain.Video">
select * from video where point=#{point} and title like concat('%', #
{title},'%')
</select>
知识点三:Mybatis配置驼峰字段到java类
数据库字段是下划线,java属性是驼峰,怎么查询映射上去?
⽅法⼀: select cover_img as coverImg from video
// 多字段怎么办
<!--下划线⾃动映射驼峰字段-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
加在全局配置文件中即可,像cover_img就可以访问这些字段了
知识点四:Mybatis的插入语句和批量插入
videoMapper.xml:
<insert id="add" parameterType="net.xdclass.online_class.domain.Video" useGeneratedKeys="true" keyProperty="id" keyColumn="id" >
INSERT INTO `video` ( `title`, `summary`, `cover_img`, `price`, `create_time`, `point`)
VALUES
(#{title,jdbcType=VARCHAR},#{summary,jdbcType=VARCHAR},#{coverImg,jdbcType=VARCHAR},#{price,jdbcType=INTEGER},
#{createTime,jdbcType=TIMESTAMP},#{point,jdbcType=DOUBLE});
</insert>
<!--批量插入-->
<insert id="addBatch" parameterType="net.xdclass.online_class.domain.Video" useGeneratedKeys="true" keyProperty="id" keyColumn="id" >
INSERT INTO `video` ( `title`, `summary`, `cover_img`, `price`, `create_time`, `point`)
VALUES
<foreach collection="list" item="video" separator=",">
(#{video.title,jdbcType=VARCHAR},#{video.summary,jdbcType=VARCHAR},#{video.coverImg,jdbcType=VARCHAR},
#{video.price,jdbcType=INTEGER},
#{video.createTime,jdbcType=TIMESTAMP},#{video.point,jdbcType=DOUBLE})
</foreach>
</insert>
videoMapper.java
/**
* 批量插入
* @param list
* @return
*/
int addBatch(List<Video> list);
启动类:
//新增一条记录
Video video1 = new Video();
video1.setTitle("小滴课堂面试专题900道1111");
video1.setCoverImg("xdclass.net/aaa.png111");
video1.setPoint(9.41);
video1.setCreateTime(new Date());
video1.setPrice(9911);
video1.setSummary("这个是面试专题概要11");
//新增一条记录
Video video2 = new Video();
video2.setTitle("小滴课堂面试专题900道2");
video2.setCoverImg("xdclass.net/aaa.png2");
video2.setPoint(9.2);
video2.setCreateTime(new Date());
video2.setPrice(9922);
video2.setSummary("这个是面试专题概要22");
List<Video> list = new ArrayList<>();
list.add(video1);
list.add(video2);
int rows = videoMapper.addBatch(list);
System.out.println(rows);
System.out.println(list.toString());
知识点五:Mybatis更新语句
<update id="updateVideo" parameterType="net.xdclass.online_class.domain.Video">
update video
set## 标题
title = #{title,jdbcType=VARCHAR},
summary = #{summary,jdbcType=VARCHAR},
cover_img = #{coverImg,jdbcType=VARCHAR},
price = #{price,jdbcType=INTEGER},
create_time = #{createTime,jdbcType=TIMESTAMP},
point = #{point,jdbcType=DOUBLE}
where
id = #{id}
</update>
<update id="updateVideoSelective" parameterType="net.xdclass.online_class.domain.Video">
update video
<trim prefix="set" suffixOverrides=",">
<if test="title != null "> title = #{title,jdbcType=VARCHAR},</if>
<if test="summary != null "> summary = #{summary,jdbcType=VARCHAR},</if>
<if test="coverImg != null "> cover_img = #{coverImg,jdbcType=VARCHAR},</if>
<if test="price != 0 "> price = #{price,jdbcType=INTEGER},</if>
<if test="createTime !=null "> create_time = #{createTime,jdbcType=TIMESTAMP},</if>
<!--一定要看pojo类里面的是基本数据类型,还是包装数据类型-->
<if test="point != null "> point = #{point,jdbcType=DOUBLE},</if>
</trim>
where
id = #{id}
</update>
知识点六:Mybatis删除语句
<delete id="deleteByCreateTimeAndPrice" parameterType="java.util.Map">
delete from video where create_time <![CDATA[ > ]]> #{createTime} and price <![CDATA[ >= ]]> #{price}
</delete>
Map<String,Object> map = new HashMap<>();
map.put("createTime","2021-01-11 09:33:20");
map.put("price",9000);
int rows = videoMapper.deleteByCreateTimeAndPrice(map);
System.out.println(rows);
ps:转义字符
由于MyBatis的sql写在XML⾥⾯, 有些sql的语法符号和xml⾥⾯的冲突
⼤于等于 <![CDATA[ >= ]]>
⼩于等于 <![CDATA[ <= ]]>