介绍Mybatis
Mybatis开发流程
- 引入MyBatis依赖
- 创建核心配置文件
- 创建实体(Entity)类
- 创建Mapper映射文件
- 初始化SessionFactory
- 利用SqlSession对象操作数据
Mybatis环境配置
mybatis-config.xml
- Mybatis采用XML格式配置数据库环境信息
- Mybatis环境配置标签< environment>
- environment包含数据库驱动、URL、用户名与密码
- 创建maven项目,引入mybatis依赖
maven中央仓库在国外apache服务器,有时候下载比较困难,我们可以在pom.xml中增加< repositories>标签将下载服务器指向国内阿里云镜像,可以极大的提高下载速度
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.zl</groupId>
<artifactId>mybatis</artifactId>
<version>1.0-SNAPSHOT</version>
<repositories>
<!-- 阿里云镜像-->
<repository>
<id>aliyun</id>
<name>aliyun</name>
<url>https://maven.aliyun.com/repository/public</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
</dependency>
<!--MySQL JDBC驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
</dependencies>
</project>
- 在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="prd">
<!--配置环境,不同的环境不同的id名字-->
<environment id="prd">
<!-- 采用JDBC方式对数据库事务进行commit/rollback -->
<transactionManager type="JDBC"></transactionManager>
<!--采用连接池方式管理数据库连接-->
<!--<dataSource type="POOLED">-->
<dataSource type="com.imooc.mybatis.datasource.C3P0DataSourceFactory">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/zl_cms?useUnicode=true&characterEncoding=UTF-8"/>
<property name="user" value="root"/>
<property name="password" value="root"/>
<property name="initialPoolSize" value="5"/>
<property name="maxPoolSize" value="20"/>
<property name="minPoolSize" value="5"/>
<!--...-->
</dataSource>
</environment>
<environment id="prd">
<!-- 采用JDBC方式对数据库事务进行commit/rollback -->
<transactionManager type="JDBC"></transactionManager>
<!--采用连接池方式管理数据库连接-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
</configuration>
SqlSessionFactory
- SqlSessionFactory是Mybatis的核心对象
- 用于初始化Mybatis,创建SqlSession对象
- 保证SqlSessionFactory在应用中全局唯一
平时使用时SqlSessionFactory本质上就是加载配置文件来完成Mybatis框架的初始化工作。除此之外,数据库表的增删改查由这个Factory创建的SqlSession对象完成。
SqlSession
- SqlSession是Mybatis操作数据库的核心对象
- SqlSession使用JDBC方式与数据库交互
- SqlSession对象提供了数据表CRUD对应方法
在test目录下创建MybatisTestor测试类
package com.zl.mybatis;
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 org.junit.Test;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
//JUNIT单元测试类
public class MybatisTestor {
/**
* 初始化SqlSessionFactory
*/
@Test
public void testSqlSessionFactory() throws IOException {
//利用Reader加载classpath下的mybatis-config.xml核心配置文件
Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
System.out.println("SessionFactory加载成功");
SqlSession sqlSession = null;
try{
//创建SqlSession对象,SqlSession是JDBC的扩展类,用于与数据库交互
sqlSession = sqlSessionFactory.openSession();
//创建数据库连接(测试用)
Connection connection = sqlSession.getConnection();
System.out.println(connection);
}catch (Exception e){
e.printStackTrace();
}finally {
if (sqlSession != null){
//如果type="POOLED",代表使用连接池,close则是将连接回收到连接池中
//如果type="UNPOOLED",代表直连,不使用连接池,close则会调用Connection.close()方法关闭连接
sqlSession.close();
}
}
}
}
初始化工具类MybatisUtils
package com.zl.mybatis.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.Reader;
/**
* MyBatisUtils工具类,创建全局唯一的SqlSessionFactory对象
*/
public class MybatisUtils {
//利用static(静态)属于类不属于对象,且全局唯一
private static SqlSessionFactory sqlSessionFactory = null;
//利用静态块在初始化类时实例化sqlSessionFactory
static {
Reader reader = null;
try {
reader = Resources.getResourceAsReader("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
throw new ExceptionInInitializerError(e);
}
}
/**
* openSession 创建一个新的SqlSession对象
* @return SqlSession对象
*/
public static SqlSession openSession(){
return sqlSessionFactory.openSession();
}
/**
* 释放一个有效的SqlSession对象
* @param session 准备释放SqlSession对象
*/
public static void closeSession(SqlSession session){
if(session != null){
session.close();
}
}
}
MybatisTestor测试类中测试MybatisUtils
@Test
public void testMyBatisUtils() throws Exception {
SqlSession sqlSession = null;
try{
sqlSession = MybatisUtils.openSession();
Connection connection = sqlSession.getConnection();
System.out.println(connection);
}catch (Exception e){
throw e;
}finally {
MybatisUtils.closeSession(sqlSession);
}
}
运行控制台输出如下说明Mybatis建立数据库连接成功
MybatisUtils中配置文件名错误再次运行测试方法报错
reader = Resources.getResourceAsReader("mybatis-config1.xml");
MyBatis数据查询
MyBatis数据查询步骤
- 创建实体类(Entity)
- 创建Mapper XML
- 编写< select>SQL标签
- 开启驼峰命名映射
- 新增< mapper>
- SqlSession执行select语句
1.创建实体类RepositoryStock
package com.zl.mybatis.entity;
import lombok.Data;
@Data
public class RepositoryStock {
private String materialCode;
private String materialName;
private String specs;
private Integer unit;
private String material;
private String standard;
private Integer amount;
/**锁库数量*/
private Integer lockAmount;
/**在用数量*/
private Integer inuseAmount;
private String categoryCode;
private String remarks;
private Integer isUse;
private Integer isDel;
private String tBranchCode;
private String companyCode;
private Integer initialNum;
}
2.创建Mapper.xml,并编写< select>标签
<?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">
<!--命名空间-->
<mapper namespace="stock">
<select id="selectAll" resultType="com.zl.mybatis.entity.RepositoryStock">
SELECT * FROM repository_stock ORDER BY id DESC LIMIT 10
</select>
</mapper>
3.在mybatis配置文件最下方添加mapper映射
<mappers>
<mapper resource="mapper/RepositoryStockMapper.xml"></mapper>
</mappers>
4.在MybatisTestor中添加测试方法
@Test
public void testSelectAll() throws Exception {
SqlSession session = null;
try{
session = MybatisUtils.openSession();
List<RepositoryStock> list = session.selectList("stock.selectAll");
for (RepositoryStock s : list){
System.out.println(s.getSpecs());
}
}catch (Exception e){
throw e;
}finally {
MybatisUtils.closeSession(session);
}
}
运行发现部分字段值为空,由于表中字段命名方式为下划线连接,实体类属性为驼峰式命名
解决办法就是在mybatis配置文件mybatis-config.xml中配置驼峰命名转换
<settings>
<!-- goods_id ==> goodsId 驼峰命名转换 -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
再次运行测试程序,问题已解决
SQL传参
传递单个参数
RepositoryStockMapper.xml中添加sql
要接收的参数类型属性为parameterType,基本类型的包装类型都可以。
要接收的参数配置为#{ }
<select id="selectById" parameterType="Integer" resultType="com.zl.mybatis.entity.RepositoryStock">
SELECT * FROM repository_stock WHERE id= #{value}
</select>
MybtisTestor中添加测试方法
@Test
public void testSelectById() throws Exception{
SqlSession session = null;
try{
session = MybatisUtils.openSession();
RepositoryStock stock = session.selectOne("stock.selectById",20); //参数2为要传入sql的参数
System.out.println(stock.getMaterialName());
}catch (Exception e){
throw e;
}finally {
MybatisUtils.closeSession(session);
}
}
传递多个数值
RepositoryStockMapper.xml中添加sql,用Map类型接收参数
<select id="selectByAmountRange" parameterType="java.util.Map" resultType="com.zl.mybatis.entity.RepositoryStock">
SELECT * FROM repository_stock WHERE amount BETWEEN #{min} AND #{max} ORDER BY amount limit 0,#{limit}
</select>
MybtisTestor中添加测试方法
@Test
public void testSelectByAmountRange() throws Exception {
SqlSession session = null;
try{
session = MybatisUtils.openSession();
Map param = new HashMap();
param.put("min", 100);
param.put("max", 500);
param.put("limit", 10);
List<RepositoryStock> list = session.selectList("selectByAmountRange", param); //不加namespace时要保证sql id是全局唯一的
for (RepositoryStock s: list) {
System.out.println(s.getMaterialName()+":"+s.getAmount());
}
}catch (Exception e){
throw e;
}
finally {
MybatisUtils.closeSession(session);
}
}
运行结果
需要注意的是在向sql传入多个数据的时候,map中每一个key都要在sql中使用上,xml中定义的参数在map中都要存在
获取多表关联查询结果
RepositoryStockMapper.xml中添加sql,查询结果映射为Map类型
<select id="selectStockMap" resultType="java.util.Map">
SELECT repository_stock.*,repository_category.category_name
FROM repository_stock
LEFT JOIN repository_category ON repository_category.category_code=repository_stock.category_code
</select>
MybtisTestor中添加测试方法
@Test
public void testSelectStockMap() throws Exception {
SqlSession session = null;
try{
session = MybatisUtils.openSession();
List<Map> list = session.selectList("stock.selectStockMap");
for (Map map: list) {
System.out.println(map);
}
}catch (Exception e){
throw e;
}
finally {
MybatisUtils.closeSession(session);
}
}
运行结果
Map中key值排序是按key的hashcode排序,为了保持与查询结果一致把查询结果映射为LinkedHashMap,再看运行结果
<select id="selectStockMap" resultType="java.util.LinkedHashMap">
利用LinkedHashMap保存多表关联结果,
MyBatis会将每一条记录包装为LinkedHashMap对象
key是字段名 value是字段对应的值 , 字段类型根据表结构进行自动判断
优点: 易于扩展,易于使用
缺点: 太过灵活,无法进行编译时检查
ResultMap结果映射
- ResultMap可以将查询结果映射为复杂类型的Java对象
- ResultMap适用于Java对象保存多表关联结果
- ResultMap支持对象关联查询等高级特性
创建StockDTO 类
package com.zl.mybatis.dto;
import com.zl.mybatis.entity.RepositoryStock;
@Data
public class StockDTO {
private RepositoryStock stock = new RepositoryStock();
private String categoryName;
private String orgName;
RepositoryStockMapper.xml中添加映射
<resultMap id="rmStock" type="com.zl.mybatis.dto.StockDTO">
<!--设置主键字段与属性映射-->
<id property="stock.id" column="id"></id>
<!--设置非主键字段与属性映射-->
<result property="stock.materialCode" column="material_code"></result>
<result property="stock.materialName" column="material_name"></result>
<result property="stock.specs" column="specs"></result>
<result property="stock.unit" column="unit"></result>
<result property="stock.material" column="material"></result>
<result property="stock.standard" column="standard"></result>
<result property="stock.amount" column="amount"></result>
<result property="stock.lockAmount" column="lock_amount"></result>
<result property="stock.inuseAmount" column="inuse_amount"></result>
<result property="stock.categoryCode" column="category_code"></result>
<result property="stock.remarks" column="remarks"></result>
<result property="categoryName" column="category_name"></result>
<result property="orgName" column="org_name"></result>
</resultMap>
<select id="selectStockDTO" resultMap="rmStock">
SELECT repository_stock.*,repository_category.category_name,organization.`name` AS org_name
FROM repository_stock
LEFT JOIN repository_category ON repository_category.category_code=repository_stock.category_code
LEFT JOIN organization ON organization.branch_code=repository_stock.t_branch_code
</select>
MybatisTestor中添加测试方法
@Test
public void testSelectStockDTO() throws Exception{
SqlSession session = null;
try{
session = MybatisUtils.openSession();
List<StockDTO> list = session.selectList("stock.selectStockDTO");
for (StockDTO dto: list) {
System.out.println(dto.getStock().getMaterialName());
}
}catch (Exception e){
throw e;
}
finally {
MybatisUtils.closeSession(session);
}
}
运行结果
如果StockDTO中还需要repository_category中的更多字段
@Data
public class StockDTO {
private RepositoryStock stock = new RepositoryStock();
private RepositoryCategory category = new RepositoryCategory();
// private String categoryName;
private String orgName;
}
RepositoryStockMapper.xml中配置resultMap
<resultMap id="rmStock" type="com.zl.mybatis.dto.StockDTO">
<!--设置主键字段与属性映射-->
<id property="stock.id" column="id"></id>
<!--设置非主键字段与属性映射-->
<result property="stock.materialCode" column="material_code"></result>
<result property="stock.materialName" column="material_name"></result>
<result property="stock.specs" column="specs"></result>
<result property="stock.unit" column="unit"></result>
<result property="stock.material" column="material"></result>
<result property="stock.standard" column="standard"></result>
<result property="stock.amount" column="amount"></result>
<result property="stock.lockAmount" column="lock_amount"></result>
<result property="stock.inuseAmount" column="inuse_amount"></result>
<result property="stock.categoryCode" column="category_code"></result>
<result property="stock.remarks" column="remarks"></result>
<result property="category.categoryCode" column="category_code"></result>
<result property="category.categoryName" column="category_name"></result>
<result property="category.pCategoryCode" column="p_category_code"></result>
<result property="orgName" column="org_name"></result>
</resultMap>
Mybatis数据插入操作
MyBatis写操作包含三种
- 插入 - < insert>
- 更新 - < update>
- 删除 - < delete>
id定义sql的id号,parameterType指向要传入的原始的数据是什么。
当前这个例子parameterType指向了RepositoryStock这个实体,这就意味着我们在java程序中要首先创建一个RepositoryStock对象,并为其赋值,这样才能在程序执行时将RepositoryStock中的数据带入到insert into新增语句中,而value设置值的部分则使用 #{属性名} 进行指代。insert update delete不需要书写resultType或resultMap
insert
<insert id="insert" parameterType="com.zl.mybatis.entity.RepositoryStock">
INSERT INTO repository_stock(material_code,material_name,specs,unit,material,standard,amount)
VALUES (#{materialCode},#{materialName},#{specs},#{unit},#{material},#{standard},#{amount})
<!-- 执行sql之后自动执行以下sql返回插入数据的主键
order="AFTER代表insert语句执行完之后执行下面这条sql;BEFORE代表执行insert语句之前执行下面这条语句"-->
<selectKey resultType="Integer" keyProperty="id" order="AFTER">
select last_insert_id() <!-- 用于获取当前连接最后产生的id号,范围是在当前连接最后产生的id,所以没有并发性的问题-->
</selectKey>
</insert>
@Test
public void testInsert() throws Exception{
SqlSession session = null;
try{
session = MybatisUtils.openSession();
RepositoryStock stock = new RepositoryStock();
stock.setMaterialCode("0000000000001");
stock.setMaterialName("测试物资");
stock.setSpecs("测试规格");
stock.setUnit(1);
stock.setMaterial("混合");
stock.setAmount(10);
//insert()方法返回值代表本次成功插入的记录总数
int num = session.insert("stock.insert",stock);
session.commit(); //提交事务数据
}catch (Exception e){
throw e;
}
finally {
MybatisUtils.closeSession(session);
}
}
selectKey与useGeneratedKeys的区别
useGeneratedKeys —— 默认false,不会自动获取主键
keyProperty —— 哪个属性对应主键
keyColumn —— 表中字段名
<insert id="insert" parameterType="com.zl.mybatis.entity.RepositoryStock"
useGeneratedKeys="true"
keyProperty="id"
keyColumn="id" >
INSERT INTO repository_stock(material_code,material_name,specs,unit,material,standard,amount)
VALUES (#{materialCode},#{materialName},#{specs},#{unit},#{material},#{standard},#{amount})
</insert>
二者区别
显示与隐式
- selectKey标签需要明确编写获取最新主键的SQL语句
- useGeneratedKeys属性会自动根据驱动生成对应SQL语句
应用场景不同 - selectKey 适用于所有的关系型数据库
- useGeneratedKeys 只支持"自增主键"类型的数据库
总结
- selectKey标签是通用方案,适用于所有数据库,但编写麻烦,每种数据库用的sql语句不同
- useGeneratedKeys 只支持"自增主键"类型的数据库,使用简单
在Oracle中selectKey的用法
<insert parameterType="com.zl.mybatis.entity.RepositoryStock">
INSERT INTO SQL语句
<selectKey resultType="Integer" order="BEFORE" keyProperty="id">
SELECT seq_stock.nextval as id from dual
</selectKey>
</insert>
MyBatis更新与删除操作
更新 - < update>
<update id="update" parameterType="com.zl.mybatis.entity.RepositoryStock">
UPDATE repository_stock
SET material_code = #{materialCode}, material_name = #{materialName}, specs = #{specs}, unit = #{unit},
material = #{material}, standard = #{standard}, amount = #{amount}
WHERE id = #{id}
</update>
@Test
public void testUpdate() throws Exception{
SqlSession session = null;
try{
session = MybatisUtils.openSession();
RepositoryStock stock = session.selectOne("stock.selectById",3343);
stock.setMaterialName("测试更新物资名称");
int num = session.update("stock.update",stock);
session.commit(); //提交事务数据
}catch (Exception e){
if(session != null){
session.rollback(); //回滚事务
}
throw e;
}
finally {
MybatisUtils.closeSession(session);
}
}
删除 - < delete>
<delete id="delete" parameterType="Integer">
DELETE FROM repository_stock WHERE id = #{value}
</delete>
@Test
public void testDelete() throws Exception{
SqlSession session = null;
try{
session = MybatisUtils.openSession();
int num = session.delete("stock.delete",3344);
session.commit(); //提交事务数据
}catch (Exception e){
if(session != null){
session.rollback(); //回滚事务
}
throw e;
}
finally {
MybatisUtils.closeSession(session);
}
}
MyBatis预防SQL注入攻击
SQL代码:
SELECT * FROM employee_info WHERE name = ‘” + name + ”’”;
正常情况:
name:张三 -> SELECT * FROM employee_info WHERE name = ‘张三’;
SQL注入攻击:
name: ’ or 1=1 or 1=’
SELECT * FROM employee_info WHERE name = ‘’ or 1=1 or 1=‘’
SQL注入是指攻击者利用SQL漏洞,绕过系统约束,越权获取数据的攻击方式
- ${}文本替换,未经任何处理对SQL文本替换
- #{}预编译传值,使用预编译传值可以预防SQL注入
MybatisTestor中创建测试方法
/**
* 预防SQL注入
*/
@Test
public void testSelectByMaterialName() throws Exception {
SqlSession session = null;
try{
session = MybatisUtils.openSession();
Map param = new HashMap();
/*
${}原文传值
SELECT * FROM repository_stock
WHERE material_name = '' or 1 =1 or material_name = '公制球头内六角扳手'
*/
/*
#{}预编译
SELECT * FROM repository_stock
WHERE material_name = "'' or 1 =1 or material_name = '公制球头内六角扳手'"
*/
param.put("materialName","'' or 1 =1 or material_name = '公制球头内六角扳手'");
List<RepositoryStock> list = session.selectList("stock.selectByMaterialName",param);
for(RepositoryStock s : list){
System.out.println(s.getMaterialName() + ":" + s.getAmount());
}
}catch (Exception e){
if(session != null){
session.rollback(); //回滚事务
}
throw e;
}
finally {
MybatisUtils.closeSession(session);
}
}
RepositoryStockMapper.xml中使用 ${} 传参数
<select id="selectByMaterialName" parameterType="java.util.Map" resultType="com.zl.mybatis.entity.RepositoryStock">
SELECT * FROM repository_stock WHERE material_name = ${materialName}
</select>
运行结果
RepositoryStockMapper.xml中使用 #{} 传参数
<select id="selectByMaterialName" parameterType="java.util.Map" resultType="com.zl.mybatis.entity.RepositoryStock">
SELECT * FROM repository_stock WHERE material_name = #{materialName}
</select>
运行结果
${}一般应用于根据前端输入的条件不同来选择不同的字段进行排序,这个时候就需要用到原文传值来动态变化SQL了。
param.put("order"," ORDER BY id DESC");
<select id="selectByMaterialName" parameterType="java.util.Map" resultType="com.zl.mybatis.entity.RepositoryStock">
SELECT * FROM repository_stock WHERE material_name = #{materialName}
${order}
</select>
$ {}所产生的SQL子句绝对不能是外界输入的