1
【Mybatis】-数据库如何实现数据封装(以及增、更、查)
source link: https://blog.51cto.com/u_15858858/10246025
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
【Mybatis】-数据库如何实现数据封装(以及增、更、查)
精选 原创//新增
@Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time)" +
"value (#{username},#{name},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})")
public void insert(Emp emp);
@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(2024,1,1));
emp.setCreateTime(LocalDateTime.now());
emp.setUpdateTime(LocalDateTime.now());
emp.setDeptId(1);
empMapper.insert(emp);
}
描述:在数据添加成功后,需要获取插入数据库数据的主键。如︰添加套餐数据时,还需要维护套餐菜品关系表数据。
@Test
public void testInsert(){
Emp emp = new Emp();
emp.setUsername("TOM2");
emp.setName("汤姆2");
emp.setImage("1.jpg");
emp.setGender((short)1);
emp.setJob((short)1);
emp.setEntrydate(LocalDate.of(2024,1,1));
emp.setCreateTime(LocalDateTime.now());
emp.setUpdateTime(LocalDateTime.now());
emp.setDeptId(1);
empMapper.insert(emp);
System.out.println(emp.getId());
}
我们并没有拿到该主键值
@Options(useGeneratedKeys = true,keyProperty = "id")
//更新
@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(22);
emp.setUsername("TOM66");
emp.setName("汤姆66");
emp.setImage("1.jpg");
emp.setGender((short)1);
emp.setJob((short)1);
emp.setEntrydate(LocalDate.of(2024,1,1));
emp.setUpdateTime(LocalDateTime.now());
emp.setDeptId(1);
empMapper.update(emp);
}
查询(根据ID查询)
@Select("select * from emp where id = #{id}")
public Emp getById(Integer id);
@Test
public void testGetById(){
Emp emp = empMapper.getById(22);
System.out.println(emp);
}
但是我们会发现后面的几个都为null,这是因为mybatis对于数据封装的规则
实体类属性名和数据库表查询返回的字段名一致,mybatis会自动封装。
如果实体类属性名和数据库表查询返回的字段名不一致,不能自动封装。
方法一:给字段起别名
//给字段起别名
@Select("select id, username, password, name, gender, image, job, entrydate," +
" dept_id deptId, create_time createTime, update_time updateTime from emp where id=#{}")
public Emp getById(Integer id);
方法二:手动映射封装
//手动映射封装
@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);
方法三(推荐)开启mybatis的驼峰命名自动映射开关
#开启mybatis的驼峰命名自动映射开关
mybatis.configuration.map-underscore-to-camel-case=true
//条件查询员工信息
@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);
因为在引号里面所以这里只能用$,但是这就是造成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")
public List<Emp> list(String name, Short gender, LocalDate begin,LocalDate end);
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK