MyBatis 基础与使用

1 创建

勾选依赖 : MyBaits Framework , MySql Driver;
resource/application.properties ; 添加数据库信息

1.1 代码提示

选中 ‘select * from user’ 右键->显示上下文操作->语言注入设置->选择 Mysql即可, 写代码有提示; 库名和表名一样时表名要全, user.user 只写一个user不行
在 idea 中连接数据库后, 表名和字段名也会有提示;

1.2 连接池

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->  
<dependency>  
    <groupId>com.alibaba</groupId>  
    <artifactId>druid</artifactId>  
    <version>1.2.24</version>  
</dependency>

spring.application.name=SpringBootMyBatis  
#驱动类名称  
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver  
#数据库连接的url  
spring.datasource.url=jdbc:mysql://localhost:3306/user  
#连接数据库的用户名  
spring.datasource.username=root  
#连接数据库的密码  
spring.datasource.password=1234  

spring.datasource.type = com.alibaba.druid.pool.DruidDataSource

2 lombok

框架: 编译时自动生成 getter setter toString…

3 预编译

3.1 sql注入

@Delete("delete from emp where id = #{id}")  // 这个是站位, 不会sql注入问题
int delete(Integer id);//返回操作影响的数据


@Delete("delete from emp where id = ${id}")   // 这个是拼接, 有sql注入问题
int delete(Integer id);//返回操作影响的数据

4 基础操作

4.1 命名

数据库中可以使用下划线, 实体类中使用驼峰命名法;

4.2 删除

Mapper 标记
接口

@Autowired  
private EmpMapper empMapper;  

@Test  
public void testDelete(){  
    System.out.println(empMapper.delete(16));  
}

4.3 插入

接口类前面要Mapper注解

@Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time) "+  
"values (#{username},#{name},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})")  
void insert(Emp emp);


public void testInsert(){  
    Emp emp = new Emp();  
    emp.setUsername("Tom1");  
    emp.setName("Tom");  
    emp.setGender((short)1);  
    emp.setImage("1.img");  
    emp.setJob((short)2);  
    emp.setEntrydate(LocalDate.of(2006,3,4));  
    emp.setDeptId(1);  
    emp.setCreateTime(LocalDateTime.now());  
    emp.setUpdateTime(LocalDateTime.now());  

    empMapper.insert(emp);  
}

4.3.1 插入返回主键

dishMapper.insert(dish);  

// xml文件中设置属性回显, useGeneratedKeys="true" keyProperty="id" 返回的值赋值为传入insert的参数dish的id, 再获取属性  
Long dishId = dish.getId();

4.4 修改

@Update("update emp set username = #{username},name = #{name},gender = #{gender},image = #{image}, job = #{job}, entrydate = #{entrydate},dept_id = #{deptId}, update_time = #{updateTime} where id = #{id};")  
void update(Emp emp);


@Test  
public void testUpdate(){  
    Emp emp = new Emp();  
    emp.setId(18);  
    emp.setUsername("update_test");  
    emp.setName("Tom");  
    emp.setGender((short)1);  
    emp.setImage("1.img");  
    emp.setJob((short)2);  
    emp.setEntrydate(LocalDate.of(2006,3,4));  
    emp.setDeptId(1);  
    emp.setUpdateTime(LocalDateTime.now());  

    empMapper.update(emp);  
}

4.5 查询

查询时 名称一致才能自动封装

@Test  
public void testSelect(){  
    Emp emp = empMapper.selectById(1);  
    System.out.println(emp);  
}

@Select("select * from emp where id= #{id}")  
Emp selectById(Integer id);

4.5.1 解决名称不一致

4.5.1.1 sql起别名

在sql语句中给返回字段起别名

@Select("select id, username, password, name, gender, image, job, entrydate, dept_id deptId, create_time createTime, update_time updateTime from emp where id= #{id}")  
Emp selectById(Integer id);

4.5.1.2 Result

@Results({  
        @Result(column = "dept_id", property = "deptId"),  
        @Result(column = "create_time", property = "createTime"),  
        @Result(column = "update_time", property = "updateTime")  
})  
@Select("select * from emp where id= #{id}")  
Emp selectById(Integer id);

4.5.1.3 MyBatis 自动映射

a_column –> aColumn 自动;映射

mybatis.configuration.map-underscore-to-camel-case=true

4.5.2 拼接查询 concat

@Select("select * from emp where name like '%${name}%' and gender = #{gender} and entrydate " +  
        "between #{begin} and #{end} order by update_time desc;")  
List<Emp> selectBy(String name, Short gender, LocalDate begin, LocalDate end);

# { } 不能出现在 '' 内, 改成 $

存在 sql 注入问题, 且性能低.

concat 函数

@Select("select * from emp where name like concat('%',#{name},'%') and gender = #{gender} and entrydate " +  
        "between #{begin} and #{end} order by update_time desc;")  
List<Emp> selectBy(String name, Short gender, LocalDate begin, LocalDate end);

5 Xml

xml 文件中 <> 要转义字符 > <

resultType 是单条数据的实体类型, 函数返回的是list 但是单条数据是Emp 类型, 因次是 org.hzl.pojo.Emp

<mapper namespace="org.hzl.mapper.EmpMapper">  
    <select id="selectBy" resultType="org.hzl.pojo.Emp">  
        select * from emp where name like '%${name}%' and gender = #{gender} and entrydate between #{begin} and #{end} order by update_time desc;    </select>  
</mapper>

// mapper 中不用写注解
List<Emp> selectBy(String name, Short gender, LocalDate begin, LocalDate end);

MyBatisX 插件

6 动态sql

6.1 if

<mapper namespace="org.hzl.mapper.EmpMapper">  
    <select id="selectBy" resultType="org.hzl.pojo.Emp">  
        select *        from emp        where        <if test="name!=null">  
            name like '%${name}%'        </if>  
        <if test="gender!=null">  
            and gender = #{gender}        </if>  
        <if test="begin!=null and end!=null">  
            and entrydate between #{begin} and #{end}        </if>  

        order by update_time desc;    </select>  
</mapper>

List<Emp> list = empMapper.selectBy("张", null, null, null);  
System.out.println(list);

6.1.1 and 拼接问题

<mapper namespace="org.hzl.mapper.EmpMapper">  
    <select id="selectBy" resultType="org.hzl.pojo.Emp">  
        select *        from emp        where        <if test="name!=null">  
            name like '%${name}%'        </if>  
        <if test="gender!=null">  
            and gender = #{gender}        </if>  
        <if test="begin!=null and end!=null">  
            and entrydate between #{begin} and #{end}        </if>  

        order by update_time desc;    </select>  
</mapper>

List<Emp> list = empMapper.selectBy(null, (short) 1, null, null);  
System.out.println(list);

name 字段为null, 但下面的 and gender 多了个 and.
当条件都不满足时, where 也多了.

使用 <where> </where> 标签包裹起来, 自动去除 and

<mapper namespace="org.hzl.mapper.EmpMapper">  
    <select id="selectBy" resultType="org.hzl.pojo.Emp">  
        select *        from emp        <where>  
            <if test="name!=null">  
                name like '%${name}%'            </if>  
            <if test="gender!=null">  
                and gender = #{gender}            </if>  
            <if test="begin!=null and end!=null">  
                and entrydate between #{begin} and #{end}            </if>  

        </where>  


        order by update_time desc;    </select>  
</mapper>

<where> 实现了 and or where 的去除

6.1.2 案例不完全更新

需求: 有传递值就更新, 否则不更新该字段;

<update id="updateNotAll">  
    update emp    set    <if test="username!=null">username = #{username},</if>  
    <if test="name!=null">name = #{name},</if>  
    <if test="gender!=null">gender = #{gender},</if>  
    <if test="image!=null">image = #{image},</if>  
    <if test="job!=null">job = #{job},</if>  
    <if test="entrydate!=null">entrydate = #{entrydate},</if>  
    <if test="deptId!=null">dept_id = #{deptId},</if>  
    <if test="updateTime!=null">update_time = #{updateTime}</if>  
    where id = #{id}</update>

问题会多个逗号 ; 使用 <set> 标签解决;

<update id="updateNotAll">  
    update emp    <set>  
        <if test="username!=null">username = #{username},</if>  
        <if test="name!=null">name = #{name},</if>  
        <if test="gender!=null">gender = #{gender},</if>  
        <if test="image!=null">image = #{image},</if>  
        <if test="job!=null">job = #{job},</if>  
        <if test="entrydate!=null">entrydate = #{entrydate},</if>  
        <if test="deptId!=null">dept_id = #{deptId},</if>  
        <if test="updateTime!=null">update_time = #{updateTime}</if>  

    </set>  

    where id = #{id}</update>

6.1.3 总结逗号,where

6.2 foreach

<!--    item  遍历出来的;元素-->  
<!--    separator 分隔符-->  
<!--    open 遍历开始前面的拼接片段-->  
<!--    close 遍历结束后拼接的片段-->  
    <delete id="deleteByIds">  
        delete from emp where id in        <foreach collection="ids" item = "id" separator="," open = "(" close = ")">  
            #{id}        </foreach>  
    </delete>

6.3 sql 和 include

6.3.1 问题

代码复用性差, 若修改需修改多处.

6.3.2 sql

<sql id = "commonSelect">  
    select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time    from emp</sql>


<select id="selectBy" resultType="org.hzl.pojo.Emp">  

    <include refid="commonSelect"/>  
    <where>  
        <if test="name!=null">  
            name like '%${name}%'        </if>  
        <if test="gender!=null">  
            and gender = #{gender}        </if>  
        <if test="begin!=null and end!=null">  
            and entrydate between #{begin} and #{end}        </if>  

    </where>  


    order by update_time desc;</select>

插入返回主键

keyProperty 指定赋值给传入参数的哪个属性, 这里是赋值给 dish.id
```
dishMapper.insert(dish);
```

<insert id="insert" useGeneratedKeys="true" keyProperty="id">  
    insert into dish(name, category_id, price, image, description, status, create_time, update_time, create_user, update_user)    values (#{name},#{categoryId},#{price},#{image},#{description},#{status},#{createTime},#{updateTime},#{createUser},#{updateUser})</insert>

多表查询

<!--    select d.*,c.name categoryName from dish d left outer join category c on d.category_id = c.id;-->  
    <select id="pageQuery" resultType="com.sky.vo.DishVO">  
        select d.*,c.name categoryName from dish d left outer join category c on d.category_id = c.id        <where>  
            <if test="name!=null and name!=''">d.name like concat('%',#{name},'%')</if>  
            <if test="categoryId!=null">and d.categoryId=#{categoryId}</if>  
            <if test="status!=null">and d.status = #{status}</if>  
        </where>  
        order by d.create_time desc        </select>

6.4 总结

编写 xml 文件时 , 先在console中写好 sql 语句, 在替换标签.
编写xml 文件使 , String类型的要判断是否为空

6.5 坑

update 逗号问题, 检查sql 语句 每对后面逗号

MyBatis 基础与使用

1 创建

1.1 代码提示

1.2 连接池

2 lombok

3 预编译

3.1 sql注入

4 基础操作

4.1 命名

4.2 删除

4.3 插入

4.3.1 插入返回主键

4.4 修改

4.5 查询

4.5.1 解决名称不一致

4.5.1.1 sql起别名

4.5.1.2 Result

4.5.1.3 MyBatis 自动映射

4.5.2 拼接查询 concat

5 Xml

6 动态sql

6.1 if

6.1.1 and 拼接问题

6.1.2 案例 不完全更新

6.1.3 总结 逗号,where

6.2 foreach

6.3 sql 和 include

6.3.1 问题

6.3.2 sql

插入返回主键

多表查询

6.4 总结

6.5 坑

6.1.2 案例不完全更新

6.1.3 总结逗号,where