文章目录
一.MyBatis介绍
1.什么是MyBatis?
- MyBatis是优秀的持久层框架
- MyBatis使用xml将sql与程序解耦,便于维护
- MyBatis学习简单,执行高效,是JDBC的延伸
2.MyBatis开发流程
- 引入MyBatis依赖
- 创建核心配置文件
- 创建实体(Entity)类
- 创建Mapper映射文件
- 初始化SessionFactory
- 利用Sqlsession对象操作数据
二.MyBatis环境配置
1.创建mybatis-config.xml文件
- MyBatis采用xml格式配置数据库环境信息
- MyBatis环境配置标签
- environment包含数据库驱动、URL、用户名与密码
<?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="dev">
<environment id="dev">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/atguigu"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mappers/emp.xml"/>
</mappers>
</configuration>
三.SqlSessionFactory
1.SqlSessionFactory是什么
- SqlSessionFactory是MyBatis的核心对象
- 用于初始化MyBatis,创建SqlSession对象
- 保证SqlSessionFactory在应用中全局唯一
2.SqlSession是什么
- SqlSession是MyBatis操作数据库的核心对象
- SqlSession使用JDBC方式与数据库交互
- SqlSession对象提供了数据表CRUD对应方法
3.例:
package com.imooc.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;
public class MyBatisTest {
@Test
public void testSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
//利用Reader加载mybatis-config.xml核心配置文件
Reader reader = Resources.getResourceAsReader(resource);
//初始化SqlSessionFactory对象,同时解析mybatis-config.xml文件
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = null;
try {
//创建SqlSession对象,SqlSession是JDBC的扩展类,用于数据库交互
session = build.openSession();
//创建数据库连接对象,测试用
Connection connection = session.getConnection();
System.out.println(connection);
}catch (Exception e){
e.printStackTrace();
}finally {
if (session != null) {
session.close();
}
}
}
}
四.初始化工具类MyBatisUtils
package com.imooc.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;
public class MyBatisUtils {
//利用static(静态)属于类不属于对象,且全局唯一
private static SqlSessionFactory sqlSessionFactory;
//利用静态块在初始化类时实例化sqlSessionFactory
static {
Reader reader = null;
String resource = "mybatis-config.xml";
//利用Reader加载mybatis-config.xml核心配置文件
try {
reader = Resources.getResourceAsReader(resource);
//初始化SqlSessionFactory对象,同时解析mybatis-config.xml文件
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
/**
* 创建一个新的SqlSession对象
* @return
*/
public static SqlSession openSession() {
return sqlSessionFactory.openSession();
}
/**
* 释放一个有效的SqlSession对象
* @param session
*/
public static void closeSession(SqlSession session) {
if (session != null) {
session.close();
}
}
}
测试:
@Test
public void testMyBatisUtils(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
Connection connection = sqlSession.getConnection();
System.out.println(connection);
}catch (Exception e){
throw e;
}finally {
if (sqlSession != null) {
MyBatisUtils.closeSession(sqlSession);
}
}
}
五.MyBatisUtils数据查询
1.MyBatisUtils数据查询步骤
- 创建实体类(Entity)
- 创建Mapper XML
- 编写SQL标签
- 开启驼峰命名映射
- 新增
- SqlSession执行select语句
2.例:
package com.imooc.entity;
public class Emp {
private Integer empId;
private String empName;
private Integer empAge;
private Float empSalary;
public Integer getEmpId() {
return empId;
}
public void setEmpId(Integer empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public Integer getEmpAge() {
return empAge;
}
public void setEmpAge(Integer empAge) {
this.empAge = empAge;
}
public Float getEmpSalary() {
return empSalary;
}
public void setEmpSalary(Float empSalary) {
this.empSalary = empSalary;
}
}
<?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="emp">
<select id="selectAll" resultType="com.imooc.entity.Emp">
select * from t_emp order by emp_id desc limit 3
</select>
</mapper>
<?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>
<settings>
<!-- 驼峰命名转换-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/atguigu"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mappers/emp.xml"/>
</mappers>
</configuration>
@Test
public void testSelectAll(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
List<Emp> list = sqlSession.selectList("emp.selectAll");
for (Emp emp : list) {
System.out.println(emp.getEmpAge());
}
}catch (Exception e){
throw e;
}finally {
if (sqlSession != null) {
MyBatisUtils.closeSession(sqlSession);
}
}
}
六.SQL传参
<?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="emp">
<select id="selectAll" resultType="com.imooc.entity.Emp">
select * from t_emp order by emp_id desc limit 3
</select>
<!-- 多参数查询-->
<select id="selectById" parameterType="Integer" resultType="com.imooc.entity.Emp">
select * from t_emp where emp_id =#{value}
</select>
<!-- 多个参数查询-->
<select id="selectByIdRange" parameterType="java.util.Map" resultType="com.imooc.entity.Emp">
select * from t_emp where emp_age between #{min} and #{max} ORDER BY emp_age limit 0,#{limit}
</select>
</mapper>
@Test
public void testSelectById(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
Emp emp = sqlSession.selectOne("emp.selectById", 1);
System.out.println(emp.getEmpAge());
}catch (Exception e){
throw e;
}finally {
if (sqlSession != null) {
MyBatisUtils.closeSession(sqlSession);
}
}
}
@Test
public void testSelectByIdRange(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
Map map = new HashMap();
map.put("min", 18);
map.put("max", 30);
map.put("limit", 2);
List<Emp> list = sqlSession.selectList("emp.selectByIdRange", map);
for (Emp emp : list) {
System.out.println(emp.getEmpAge());
}
}catch (Exception e){
throw e;
}finally {
if (sqlSession != null) {
MyBatisUtils.closeSession(sqlSession);
}
}
}
七.获取多表关联查询结果
<!-- 多表关联查询-->
<select id="selectEmpMap" parameterType="java.util.Map" resultType="java.util.LinkedHashMap">
SELECT e.*,c.name FROM `t_emp` e LEFT JOIN category c on e.category_id=c.id
</select>
@Test
public void testSelectEmpMap(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
List<Map> list = sqlSession.selectList("emp.selectEmpMap");
for (Map map : list) {
System.out.println(map);
}
}catch (Exception e){
throw e;
}finally {
if (sqlSession != null) {
MyBatisUtils.closeSession(sqlSession);
}
}
}
八.ResultMap结果映射
- ResultMap可以将结果映射为复杂类型的Java对象
- ResultMap适用于Java对象保存多表关联结果
- ResultMap支持对象关联查询等高级特性
package com.imooc.mybatis.dto;
import com.imooc.entity.Emp;
public class EmpDTO {
private Emp emp = new Emp();
private String categoryName;
public Emp getEmp() {
return emp;
}
public void setEmp(Emp emp) {
this.emp = emp;
}
public String getCategoryName() {
return categoryName;
}
public void setCategoryName(String categoryName) {
this.categoryName = categoryName;
}
}
XML:
<!-- 多表关联查询结果映射-->
<resultMap id="rmEmp" type="com.imooc.mybatis.dto.EmpDTO">
<!-- 设置主键字段与属性映射-->
<id property="emp.empId" column="emp_id"></id>
<!-- 设置非主键字段与属性映射-->
<result property="emp.empName" column="emp_name"></result>
<result property="emp.empSalary" column="emp_salary"></result>
<result property="emp.empAge" column="emp_age"></result>
<result property="emp.categoryId" column="category_id"></result>
<result property="categoryName" column="category_name"></result>
</resultMap>
<select id="selectEmpDTO" resultMap="rmEmp">
SELECT e.*,c.name FROM `t_emp` e LEFT JOIN category c on e.category_id=c.id
</select>
测试:
@Test
public void testSelectEmpDTO(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
List<EmpDTO> list = sqlSession.selectList("emp.selectEmpDTO");
for (EmpDTO dto : list) {
System.out.println(dto.getEmp().getEmpName());
}
}catch (Exception e){
throw e;
}finally {
if (sqlSession != null) {
MyBatisUtils.closeSession(sqlSession);
}
}
}
九.MyBatis数据写入
1.insert新增数据
xml:
<insert id="insert" parameterType="com.imooc.entity.Emp">
insert into t_emp(emp_name,emp_salary,emp_age,category_id) values
(#{empName},#{empSalary},#{empAge},#{categoryId})
<!-- 插入数据成功后进行主键回添-->
<selectKey resultType="Integer" keyProperty="empId" order="AFTER">
<!-- 在当前连接执行last_insert_id 不会有并发问题 -->
select last_insert_id()
</selectKey>
</insert>
测试:
//测试插入数据
@Test
public void testInsert(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
Emp emp = new Emp();
emp.setEmpAge(18);
emp.setEmpName("test");
emp.setCategoryId(3);
emp.setEmpSalary(50000f);
int num = sqlSession.insert("emp.insert", emp);
sqlSession.commit(); //提交事务
System.out.println(emp.getEmpId());
}catch (Exception e){
if (sqlSession != null) {
sqlSession.rollback();//回滚事务
}
throw e;
}finally {
if (sqlSession != null) {
MyBatisUtils.closeSession(sqlSession);
}
}
}
2.selectKey与useGeneratedKeys的区别
二者区别:显示与隐示
- selectKey需要明确编写获取最新主键的sql语句(select last_insert_id())
- useGeneratedKeys属性会自动根据驱动生成对应sql语句
二者区别:应用场景不同
- selectKey适用于所有的关系型数据库
- useGeneratedKeys只支持自增主键类型的数据库(比如Oracle就不支持)
总结:
- selectKey标签是通用方案,适用于所有数据库,但编写麻烦
- useGeneratedKeys只支持自增主键类型的数据库,使用简单
3.update更新数据
<update id="update" parameterType="com.imooc.entity.Emp">
update t_emp set emp_name = #{empName} ,emp_age = #{empAge}
where emp_id = #{empId}
</update>
//测试更新数据
@Test
public void testUpdate(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
Emp emp = sqlSession.selectOne("emp.selectById",20008);
emp.setEmpAge(999);
emp.setEmpName("李四");
int num = sqlSession.update("emp.update",emp);
System.out.println(num);
sqlSession.commit(); //提交事务
}catch (Exception e){
if (sqlSession != null) {
sqlSession.rollback();//回滚事务
}
throw e;
}finally {
if (sqlSession != null) {
MyBatisUtils.closeSession(sqlSession);
}
}
}
4.delete 删除数据
<delete id="delete" parameterType="Integer">
delete from t_emp where emp_id = #{value}
</delete>
//测试删除数据
@Test
public void testDelete(){
SqlSession sqlSession = null;
try {
sqlSession = MyBatisUtils.openSession();
int num = sqlSession.delete("emp.delete",20009);
sqlSession.commit(); //提交事务
}catch (Exception e){
if (sqlSession != null) {
sqlSession.rollback();//回滚事务
}
throw e;
}finally {
if (sqlSession != null) {
MyBatisUtils.closeSession(sqlSession);
}
}
}
十.预防SQL注入攻击
SQL注入是指攻击者利用sql漏洞,绕过系统约束,越权操作数据的攻击方式
MyBatis两种传值方式
- ${}文本替换,未经任何处理对SQL文本替换
- #{}预编译传值,可以有效预防SQL注入