目的
解决原生方式中硬编码的问题
简化后期执行SQL
Mapper代理的规则
1、定义与SQL映射文件同名的Mapper接口，并且将Mapper接口和SQL映射文件放置在同一目录下
2、设置SQL映射文件的namespace属性为Mapper接口全限定名
3、在Mapper接口中定义方法，方法名就是SQL映射文件中sql语句的id，并保证参数类型和返回值类型一致
4、编码
-
通过SqlSession的getMapper方法获取，Mapper接口代理对象
-
调用对应方法完成sql执行
Mybatis核心配置文件详解
- 类型别名（typeAliases）
以下标签在数据库连接信息配置文件下写入
<typeAliases>
<package name = "映射实体类的路径，路径写到包即可"> 别名为类名，不区分大小写
</typeAliases>
注意：配置各个标签时，需要遵循先后顺序
数据库字段名 和 实体类的属性名不一致解决方法
<!--数据库表的字段名称 和 实体类的属性名称不一样则不能自动封装数据 以下是解决方式之一，还有SQL片段的方式不好用这里就不介绍了-->
<resultMap id="map" type="brand"> <!--id是唯一标识 type是实体类类型，这里我取了别名，正常要传实体类的相对路径 -->
<result column="brand_name" property="brandName"/><!--参数一是数据库的字段名 参数二是实体类的属性名 -->
<result column="company_name" property="companyName"/>
</resultMap>
<select id="selectAll" resultMap="map"> <!--用resultMap替换 resultSetType-->
select * from tb_brand;
</select>
参数占位符和SQL语句中的特殊字符处理
1、#{}：执行SQL时，会将#{}占位符替换为？，将来自动设置参数值
2、${}：拼接SQL，存在SQL注入问题
3、使用时机
- 参数传递，都使用#{}
- 如果要对表名、列名进行动态设置，只能使用${}进行SQL拼接 4、parameterType：用于设置参数类型，该参数可以省略
5、SQL语句中特殊字符处理
- 转义字符
<![CDATA[内容]]>
SQL语句设置多个参数的3个方式
1、散装参数：需要使用@Param("SQL中的参数占位符")
2、实体类封装参数：只需要保证SQL中参数名和实体类属性名对应上，即可设置成功
3、Map集合：只需要保证SQL中的参数名和Map集合的键名称对应上，即可设置成功
MyBatis基础标签
<if></if>
<if test="status!=null" >当status不等于null的时候下面的SQL就会执行
status = #{id}
</if>
--------------------
<where></where> where标签也是判断标签
假设用户的请求只需要执行第二条SQL，普通的where，SQL语句就会变成这样 select * from tb_brand where and company_name like ? 这里可以发现多了一个and从而出现SQL语法错误
使用where标签则不会，where标签会对SQL进行判断，自动删除SQL里多余的一些东西
<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>
----------------------
<choose></choose> 相当于switch-> <when></when> 相当于case-> <otherwise></otherwise>相当于default 但是我们一般不用<otherwise></otherwise>标签，因为我们使用<where>标签进行包裹，用户请求没有进入<when>标签时 <where>标签自动删除where的判断
----------------
<set></set> 如果对列的部分数据进行修改每条修改的语句后面可能会多出一个逗号，导致产生SQL语法错误<set>标签会对多余的逗号进行删除
添加主键返回
注意：openSession() 默认开启事务 对数据数据进行增删改操作后需要手动提交事务 或者将openSession（true）将其设置为true
批量删除
案例
配置文件
- logback.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<!--
CONSOLE ：表示当前的日志信息是可以输出到控制台的。
-->
<appender name="Console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>[%level] %blue(%d{HH:mm:ss.SSS}) %cyan([%thread]) %boldGreen(%logger{15}) - %msg %n</pattern>
</encoder>
</appender>
<logger name="com.itheima" level="DEBUG" additivity="false">
<appender-ref ref="Console"/>
</logger>
<!--
level:用来设置打印级别，大小写无关：TRACE, DEBUG, INFO, WARN, ERROR, ALL 和 OFF
， 默认debug
<root>可以包含零个或多个<appender-ref>元素，标识这个输出位置将会被本日志级别控制。
-->
<root level="INFO">
<appender-ref ref="Console"/>
</root>
</configuration>
- 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>
<!--为映射文件路径取别名 -->
<typeAliases>
<package name="com.itheima.pojo"/> <!--实体类路径，写到包即可-->
</typeAliases>
<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:///db1?useSSL=false&characterEncoding=utf-8"/>
<property name="username" value="root"/>
<property name="password" value="1234"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--加载SQL的映射文件-->
<!-- <mapper resource="com/itheima/mapper/brandMapper.xml"/>-->
<!--包扫描方式加载sql映射文件-->
<package name="com.itheima.mapper"/>
</mappers>
</configuration>
- 映射文件
<?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:名称空间 -->
<mapper namespace="com.itheima.mapper.brandMapper">
<!-- 数据库表的字段名称 和 实体类的属性名称不一样则不能自动封装数据 以下是解决方式-->
<resultMap id="map" type="brand"> <!--id是唯一标识 type是实体类类型，这里我取了别名，正常要传实体类的相对路径 -->
<result column="brand_name" property="brandName"/><!--参数一是数据库的字段名 参数二是实体类的属性名 -->
<result column="company_name" property="companyName"/>
</resultMap>
<!--查询所有数据 -->
<select id="selectAll" resultMap="map"> <!--用resultMap替换 resultSetType-->
select * from tb_brand;
</select>
<!--根据Id查询数据 -->
<select id="selectDetails" resultMap="map">
select * from tb_brand where id = #{id}; <!--#{}是占位符可以防止SQL注入 ${}不能防止SQL注入-->
</select>
<!--多条件查询 -->
<select id="selectMulti" resultMap="map">
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="selectSingle" resultMap="map">
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>
<!--修改数据 -->
<update id="modify">
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>
<!--删除单个数据 -->
<delete id="deleteById">
delete from tb_brand where id = #{id};
</delete>
<!--删除多个数据 -->
<delete id="deleteByIds">
delete from tb_brand where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>
</mapper>
测试类和Mapper接口以及实体类
- Mapper接口
package com.itheima.mapper;
import com.itheima.pojo.Brand;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface brandMapper {
List<Brand> selectAll();
Brand selectDetails(int id);
//List<Brand> selectMulti(@Param("status")int status,@Param("companyName")String companyName,@Param("brandName")String brandName);
//List<Brand> selectMulti(Brand brand);
List<Brand> selectMulti(Map map);
List<Brand> selectSingle(Brand brand);
// void add(@Param("brandName")String brandName,@Param("companyName")String companyName,@Param("ordered")int ordered,@Param("description")String description,@Param("status")int status);
void add(Brand brand);
void modify(Brand brand);
void deleteById(int id);
void deleteByIds(@Param("ids") int[] ids);
}
- 实体类
package com.itheima.pojo;
public class Brand {
// id 主键
private Integer id;
// 品牌名称
private String brandName;
// 公司名称
private String companyName;
// 排序字段
private Integer ordered;
// 产品描述
private String description;
// 状态 0：禁用 1：启用
private Integer status;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getBrandName() {
return brandName;
}
public void setBrandName(String brandName) {
this.brandName = brandName;
}
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public Integer getOrdered() {
return ordered;
}
public void setOrdered(Integer ordered) {
this.ordered = ordered;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
@Override
public String toString() {
return "Brand{" +
"id=" + id +
", brandName='" + brandName + ''' +
", companyName='" + companyName + ''' +
", ordered=" + ordered +
", description='" + description + ''' +
", status=" + status +
'}';
}
}
- 测试类
package com.itheima;
import com.itheima.mapper.brandMapper;
import com.itheima.pojo.Brand;
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.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MapperTest {
@Test
public void selectAllTest() throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
brandMapper mapper = sqlSession.getMapper(brandMapper.class);
List<Brand> brands = mapper.selectAll();
System.out.println(brands);
sqlSession.close();
}
@Test
public void selectDetailsTest() throws Exception {
int id = 1;
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
brandMapper mapper = sqlSession.getMapper(brandMapper.class);
Brand brand = mapper.selectDetails(id);
System.out.println(brand);
sqlSession.close();
}
@Test
public void selectMultiTest() throws Exception {
int status = 1;
String brandName = "华为";
String companyName = "华为";
brandName = "%" + brandName + "%";
companyName = "%" + companyName + "%";
// Brand brand = new Brand();
// brand.setStatus(status);
// brand.setCompanyName(companyName);
// brand.setBrandName(brandName);
Map map = new HashMap();
map.put("status",status);
map.put("brandName",brandName);
map.put("companyName",companyName);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
brandMapper mapper = sqlSession.getMapper(brandMapper.class);
List<Brand> brands = mapper.selectMulti(map);
System.out.println(brands);
sqlSession.close();
}
@Test
public void selectSingleTest() throws Exception {
int status = 1;
String brandName = "华为";
String companyName = "华为";
brandName = "%" + brandName + "%";
companyName = "%" + companyName + "%";
Brand brand = new Brand();
//brand.setStatus(status);
brand.setCompanyName(companyName);
//brand.setBrandName(brandName);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
brandMapper mapper = sqlSession.getMapper(brandMapper.class);
List<Brand> brands = mapper.selectSingle(brand);
System.out.println(brands);
sqlSession.close();
}
@Test
public void addTest() throws Exception {
String brandName = "波导";
String companyName = "波导手机";
int ordered = 100;
String description = "波导手机，手机中的战斗机";
int status = 1;
Brand brand = new Brand();
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setOrdered(ordered);
brand.setDescription(description);
brand.setStatus(status);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
brandMapper mapper = sqlSession.getMapper(brandMapper.class);
mapper.add(brand);
System.out.println(brand.getId());
sqlSession.commit();//对数据库表进行增删改，需要手动回滚事务，或者openSession（设置为true）
sqlSession.close();
}
@Test
public void modifyTest() throws Exception {
int id = 4;
String brandName = "波导";
String companyName = "波导";
int ordered = 100;
String description = "手机中的战斗机";
int status = 0;
Brand brand = new Brand();
//brand.setBrandName(brandName);
// brand.setCompanyName(companyName);
// brand.setOrdered(ordered);
// brand.setDescription(description);
brand.setStatus(status);
brand.setId(id);
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
brandMapper mapper = sqlSession.getMapper(brandMapper.class);
mapper.modify(brand);
sqlSession.commit();//对数据库表进行增删改，需要手动回滚事务，或者openSession（设置为true）
sqlSession.close();
}
@Test
public void deleteByIdTest() throws Exception {
int id = 6;
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
brandMapper mapper = sqlSession.getMapper(brandMapper.class);
mapper.deleteById(id);
sqlSession.commit();//对数据库表进行增删改，需要手动回滚事务，或者openSession（设置为true）
sqlSession.close();
}
@Test
public void deleteByIdsTest() throws Exception {
int[] id = {4,5};
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
brandMapper mapper = sqlSession.getMapper(brandMapper.class);
mapper.deleteByIds(id);
sqlSession.commit();//对数据库表进行增删改，需要手动回滚事务，或者openSession（设置为true）
sqlSession.close();
}
}
