Java Web 学习08

更新：

@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
public void testUpdate(){
    Emp emp = new Emp();
    emp.setId(18);
    emp.setUsername("lucy");
    emp.setName("路西");
    emp.setImage("1.rpg");
    emp.setGender((short)1);
    emp.setJob((short)1);
    emp.setEntrydate(LocalDate.of(2020,01,01));
    emp.setUpdateTime(LocalDateTime.now());
    emp.setDeptId(1);
    empMapper.update(emp);
}

查询：

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

@Test
public void testSelect(){
    Emp select = empMapper.select(20);
    System.out.println(select);
}

解决方法1：驼峰命名 （起别名） 

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

方案2：使用注解

@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 select(Integer id);

方案3：开启mybatis驼峰自动映射开关（推荐）

在配置文件中设置

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

条件查询：

例：

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

@Select("select * from emp where name like '%${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);

#{}不能卸载''里面所以使用'%${name}%'   效率低，不安全

解决方法：使用concat拼接字符串 

concat('%',#{name},'%')

XML映射文件：

 

EmpMapper.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.example.de.mapper.EmpMapper">

    <select id="list" resultType="com.example.de.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>