JavaWeb学习_Mybatis_编写SQL

注解

删除

mapper方法:

// 根据ID删除数据
@Delete("delete from emp where id = ${id}")
public int delete(Integer id);

注意事项:

如果mapper接口方法形参只有一个普通类型的参数, #{…}里面的属性名可以随便写, 如: #{id}, #{value}.

Test:

@Autowired
private EmpMapper empMapper;

@Test
public void testDelete()
{
    int num = empMapper.delete(17);
    System.out.println(num);
}

预编译SQL

优势:

• 性能更高
• 更安全(防止SQL注入)

日志输出

application.properties

#指定mybatis输出日志的位置, 输出控制台
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

参数占位符

• #{...}: 执行SQL时, 会将#{...}替换为?, 生成预编译SQL, 会自动设置参数值, 参数传递都使用#{...}
• ${...}: 拼接SQL, 直接将参数拼接在SQL语句中, 存在SQL注入问题, 在对表名和列表进行动态设置时使用

新增

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})")
public void insert(Emp emp);

Test方法:

@Test
public void testInsert()
{
    // 构造员工对象
    Emp emp = new Emp();
    emp.setUsername("Tom");
    emp.setName("汤姆");
    emp.setImage("1.jpg");
    emp.setGender((short)1);
    emp.setJob((short)1);
    emp.setEntrydate(LocalDate.of(2000, 1, 1));
    emp.setCreateTime(LocalDateTime.now());
    emp.setUpdateTime(LocalDateTime.now());
    emp.setDeptId(1);

    // 新增员工
    empMapper.insert(emp);
}

主键返回

// 将生成的主键值, 赋值给emp对象的id属性
@Options(keyProperty = "id", useGeneratedKeys = true) 
@Insert("...")
public void insert(Emp emp);

更新

mapper方法:

@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}")
public void update(Emp emp);

Test方法:

@Test
public void testUpdate()
{
    // 构造员工对象
    Emp emp = new Emp();
    emp.setId(18);
    emp.setUsername("Tom1");
    emp.setName("汤姆");
    emp.setImage("1.jpg");
    emp.setGender((short)1);
    emp.setJob((short)1);
    emp.setEntrydate(LocalDate.of(2000, 1, 1));
    emp.setUpdateTime(LocalDateTime.now());
    emp.setDeptId(1);

    // 更新员工
    empMapper.update(emp);
}

查询

根据ID查询

mapper方法:

// 方案3: 开启mybatis的驼峰命名自动映射开关
// 根据ID查询员工
@Select("select * from emp where id = #{id}")
public Emp getById(Integer id);

// 方案1: 给字段起别名, 让别名与实体类属性一致
// @Select("select id, username, password, name, gender, image, job, entrydate, " +
//         "dept_id deptID, create_time createTime, update_time updateTime from emp where id = #{id}")
// public Emp getById(Integer id);

// 方案2: 通过@Results, @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}")
// public Emp getById(Integer id);

application.properties:

# 关键字: camel
# 开启mybatis的驼峰命名自动映射开关 a_column -> aColumn
mybatis.configuration.map-underscore-to-camel-case=true

Test:

@Test
public void testGetById()
{
    Emp emp = empMapper.getById(18);
    System.out.println(emp);
}

条件查询

mapper方法:

// 条件查询员工
// 性能低, 不安全, 存在SQL注入问题
// @Select("select * from emp where name like '%${name}%' and gender = #{gender} and " +
//         "entrydate between #{begin} and #{end} order by update_time desc")
// concat()函数
@Select("select * from emp where name like concat('%', #{name}, '%') and gender = #{gender} and " +
        "entrydate between #{begin} and #{end} order by update_time desc")
public List<Emp> list(String name, Short gender, LocalDate begin, LocalDate end);

Test方法:

@Test
public void testList()
{
    List<Emp> empList = empMapper.list("张", (short)1,
        LocalDate.of(2010, 1, 1),
        LocalDate.of(2020, 1, 1)
    );
    System.out.println(empList);
}

参数名说明:
在SpringBoot的1.x版本/单独使用mybatis, 需要添加@Param("...")注解来指定参数名, 因为编译不保留mapper方法的形参.(2.x内置了编译插件)

XML映射文件

规范:

• XML映射文件的名称与Mapper接口名称一致，并且将XML映射文件和Mapper接口放置在相同包下(同包同名).
• XML映射文件的namespace属性为Mapper接口全限定名一致.
• XML映射文件中sql语句的id与Mapper接口中的方法名一致，并保持返回类型一致.

xml配置:

<?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="com.itheima.mapper.EmpMapper">
                      <!-- 单条记录封装的类型 -->
    <select id="list" resultType="com.itheima.pojo.Emp">
        select * from emp
        where name like concat('%', #{name}, '%')
            and gender = #{gender} and
            entrydate between #{begin} and #{end}
        order by update_time desc
    </select>
</mapper>

动态SQL

if&where&set标签

<mapper namespace="com.itheima.mapper.EmpMapper">

    <!-- 单条记录封装的类型 -->
    <select id="list" resultType="com.itheima.pojo.Emp">
        select * from emp
        <where>
            -- 1. 动态生成where
            -- 2. 动态去除'and'和'or'
            <if test="name != null">
                name like concat('%', #{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>

    <update id="update">
        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="updateTime != null">update_time = #{updateTime}</if>
            where id = #{id}
        </set>
    </update>
</mapper>

foreach标签

<delete id="deleteByIds">
    /*
        collection: 遍历的集合
        item: 遍历出来的元素
        separator: 分隔符
        open: 遍历开始前拼接的SQL片段
        close: 遍历结束后平解的SQL片段
    */
    delete from emp where id in
    <foreach collection="Ids" item="Id" separator="," open="(" close=")">
        #{Id}
    </foreach>
</delete>

sql&include标签

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

<include refid="commonSelect"></include>

来源

黑马程序员. JavaWeb开发教程