什么是MyBatis?
MyBatis 是一款优秀的持久层框架,用于简化 JDBC 开发
官网:MyBatis官网
持久层: 负责将数据到保存到数据库的那一层代码
JavaEE三层架构:表现层、业务层、持久层
MyBatis快速入门
- 创建数据库, 添加数据
- 创建模块, 在maven的配置文件(pom.xml)中导入坐标
<?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.itheima</groupId>
<artifactId>MybatisDemo</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<!--mybatis 依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<!--mysql 驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<!--junit 单元测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>test</scope>
</dependency>
<!-- 添加slf4j日志api -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.20</version>
</dependency>
<!-- 添加logback-classic依赖 -->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
<!-- 添加logback-core依赖 -->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-core</artifactId>
<version>1.2.3</version>
</dependency>
</dependencies>
</project>
- 编写MyBatis核心配置文件 --> 替换连接信息 解决硬编码问题
<?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.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<!-- 指定映射文件 -->
<mappers>
<!--加载sql映射-->
<mapper resource="UserMapper.xml"/>
</mappers>
</configuration>
- 编写SQL映射文件 --> 统一管理sql语句, 解决硬编码问题
<?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="test">
<select id="selectAll" resultType="com.itheima.pojo.User">
select *
from tb_user;
</select>
</mapper>
5.编码
5.1 定义POJO类
5.2 加载核心配置文件 获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
5.3 执行sql语句
5.4 释放资源
pojo类取别名
在MyBatis的核心配置文件中扫描pojo类
<typeAliases>
<package name="com.itheima.pojo"/>
</typeAliases>
MyBatis核心配置文件结构
Mapper代理开发
1.定义与SQL映射文件同名的Mapper接口, 并把Mapper接口和SQl配置文件放在同一个目录下
如下图所示: 编译后会自己跑到同一个文件夹里
2.设置SQL映射文件的namespace属性为Mapper接口的全限定名
<mapper namespace="com.itheima.mapper.UserMapper">
3.在 Mapper 接口中定义方法,方法名就是SQL映射文件中sql语句的id,并保持参数类型和返回值类型一致
List<User> selectAll();
(selectAll()就是sql语句的id)
4.通过 SqlSession 的 getMapper 方法获取 Mapper接囗的代理对象, 调用对应方法完成sql的执行
*tips:如果Mapper接口名称和SQL映射文件名称相同,并在同一目录下,则可以使用包扫描的方式简
化SQL映射文件的加载*
<package name="com.itheima.mapper"/>
查询结果映射
- 对不一样的列名起别名, 让别名和实体POJO类的属性名一样
<mapper namespace="com.itheima.mapper.BrandMapper">
<select id="selectAll" resultType="brand">
select id, brand_name as brandName, company_name as companyName, ordered, description, status
from tb_brand;
</select>
</mapper>
2.使用resultMap
2.1 在sql映射文件中添加resultMap
<resultMap id="brandResultMap" type="brand">
<result column="brand_name" property="brandName"/>
<result column="company_name" property="companyName"/>
</resultMap>
2.2 在查询语句中把resultType属性替换成resultMap属性
<select id="selectAll" resultMap="brandResultMap">
select *
from tb_brand;
</select>
tips: parameterType可以设置参数类型, 但是该参数可以省略(写在resultMap前)
sql语句的参数占位符
(1) #{} : 执行SQL时, 会将#{}占位符替换为?, 将来自动设置参数值 可避免SQL注入
(2) ${} : 拼SQL, 存在SQL注入问题
使用时机: 参数传递必须用#{}; 如果要对表名, 列名进行动态设置只能使用${}进行sql拼接
SQL语句中特殊字符处理:
- 转义字符
- <![CDATA[内容]]>
条件查询
散装参数
Brand selectByCondition(@Param("status") int status, @Param("CompanyName") String CompanyName, @Param("BrandName") String BrandName);
//@Param("SQL语句中指定的占位符名字")
通过POJO对象查询
Brand selectByCondition(Brand brand);
//要保证brand对象的属性和SQL语句中的占位符对的上
通过Map集合查询
Brand selectByCondition(Map map);
//要保证hashMap的键对的上SQL语句中的占位符
多条件-动态条件查询
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
<where>
<if test="status != null">
status = #{status}
</if>
<if test="companyName != null and companyName != ''">
and company_name like #{companyName}
</if>
<if test="brandName != null and brandName != '' ">
and brand_name like #{brandName}
</if>
</where>
</select>
单条件动态查询
<select id="selectByConditionSingle" resultMap="brandResultMap">
select * from tb_brand
<where>
<choose>
<when test="status != null">
status = #{status}
</when>
<when test="companyName != null and companyName != '' ">
company_name like #{companyName}
</when>
<when test="brandName != null and brandName != '' ">
brand_name like #{brandName}
</when>
</choose>
</where>
</select>
添加
<insert id="add" useGeneratedKeys="true" keyProperty="id">
insert into tb_brand(brand_name, company_name, ordered, description, status)
VALUES (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status})
</insert>
useGeneratedKeys="true" keyProperty="id"可以在添加完成后返回主键值, keyProperty 要设置成POJO类中对应的主键属性名
//设置完后传入的POJO对象会自动得到主键id值
System.out.println(brand.getId());
修改动态字段
<update id="update">
update tb_brand
<set>
<if test="brandName != null and brandName !='' ">
brand_name = #{brandName},
</if>
<if test="companyName != null and companyName !='' ">
company_name = #{companyName},
</if>
<if test="ordered != null">
ordered = #{ordered},
</if>
<if test="description != null and description !='' ">
description = #{description},
</if>
<if test="status != null">
status = #{status}
</if>
</set>
where id = #{id};
</update>
根据ID删除一个数据
<!--根据id删除一行数据-->
<delete id="deleteSingle">
delete from tb_brand
where id = #{id};
</delete>
根据ID批量删除
<delete id="deleteByIdGroup">
delete from tb_brand
where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
<!--参数依次是: 数组名(@Param(别名)), 数组中的元素, 分隔符, 遍历开始时拼接的字符, 遍历结束后拼接的字符-->
#{id}
</foreach>
</delete>
要在接口处给ids数组起个别名
int deleteByIdGroup(@Param("ids")int[] ids);
因为MyBatis会自动把数组参数变成Map集合
MyBatis底层参数传递
使用注解书写简单SQL语句
复杂语句用xml, 简单语句用注解
//使用注解书写sql语句(这段代码直接写在接口里就可以了)
@Select("select * from tb_user where id = #{id};")
User selectById(int id);