mybatis增删改查crud
这mybatis算是我学框架以来学的最顺利最舒畅的一部分了，一天就给基础视频全部搞定了。吃大亏了，之前应该先好好的把spring mvc和mybatis部分学完了再去学sprngboot的，这样就不会那么吃力了，前两天单独一个拦截器就搞了三天。
先记一下cmd启动mysql命令，两星期之前学的感觉快忘记了。cmd管理员运行，先net start mysql 然后mysql -u root -p 再输入密码。
然后出现了插入中文报错问题，我想永久解决这个问题去看了一下my.ini文件发现明明都已经改了怎么还是这个样子，算了我也不想搞了真的解决不完这么多问题。然后到了晚上又恢复正常了。。。这idea真是时不时犯病！
看弹幕说这里是面试题先记下来再说：
项目springboot-mybatis-crud
从上往下的顺序：
package com.itheima.mapper;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.time.LocalDate;
import java.time.LocalDateTime;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
private Integer id;
private String username;
private String password;
private String name;
private Short gender;
private String image;
private Short job;
private LocalDate entrydate;
private Integer deptId;
private LocalDateTime createTime;
private LocalDateTime updateTime;
}
package com.itheima.mapper;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.annotations.Param;
import java.time.LocalDate;
import java.util.List;
@Mapper
public interface EmpMapper {
//根据ID删除数据
@Delete("delete from emp where id = #{id}")
public void delete(Integer id);
//public int delete(Integer id);
//新增员工
@Options(useGeneratedKeys = true, keyProperty = "id")
@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);
//更新员工
@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);
//方案三: 开启mybatis的驼峰命名自动映射开关 --- a_cloumn ------> aColumn
//根据ID查询员工
@Select("select * from emp where id = #{id}")
public Emp getById(Integer id);
//方案一: 给字段起别名, 让别名与实体类属性一致
//@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);
//方案二: 通过@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);
//条件查询员工
//方式一
//@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);
//方式二
// @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);
//动态条件查询
public List<Emp> list(String name, Short gender, LocalDate begin , LocalDate end);
//动态更新员工
public void update2(@Param("emp") Emp emp);
//批量删除员工
public void deleteByIds(List<Integer> ids);
}
package com.itheima;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class SpringbootMybatisCrudApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootMybatisCrudApplication.class, args);
}
}
<?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">
<sql id="commonSelect">
select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time
from emp
</sql>
<!-- 动态更新员工-->
<update id="update2">
update emp
# 使用set标签可以将所有的字段进行包裹
<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>
<!--resultType: 单条记录封装的类型-->
<select id="list" resultType="com.itheima.pojo.Emp">
<include refid="commonSelect"/>
<where>
<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>
<!--批量删除员工 (18,19,20)-->
<!--
collection: 遍历的集合
item: 遍历出来的元素
separator: 分隔符
open: 遍历开始前拼接的SQL片段
close: 遍历结束后拼接的SQL片段
-->
<delete id="deleteByIds">
delete from emp where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
# 这个表每一次遍历出来的元素
#{id}
</foreach>
</delete>
</mapper>
package com.itheima;
import com.itheima.mapper.Emp;
import com.itheima.mapper.EmpMapper;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import javax.annotation.Resource;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.List;
@SpringBootTest
class SpringbootMybatisCrudApplicationTests {
@Resource
// @Autowired//将这个接口类型的对象注入进来 不知道为什么每次都爆红
private EmpMapper empMapper;
@Test
public void testDelet() {
empMapper.delete(17);
//返回值为0说明已经删除改行 为1 说明没有删除
// int delete = empMapper.delete(17);
// System.out.println(delete);
}
//以下都是调用EmpMapper中的方法来传递值 再将返回的emp对象输出来 封装到emp员工对象当中
//新增员工
@Test
public void testInsert() {
//构造员工对象
Emp emp = new Emp();
emp.setUsername("Tom6");
emp.setName("汤姆6");
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);
System.out.println(emp.getId());
}
// //更新员工
//@Test
// public void testUpdate() {
//
// Emp emp = new Emp();
// emp.setUsername("Tom3");
// emp.setName("汤姆3");
// emp.setImage("1.jpg");
// emp.setGender((short) 1);
// emp.setJob((short) 1);
// emp.setEntrydate(LocalDate.of(2003, 13, 13));
//// emp.setCreateTime(LocalDateTime.now());
// emp.setUpdateTime(LocalDateTime.now());//更新
// emp.setDeptId(3);
//
// //执行更新操作
// empMapper.update(emp);
// }
// //根据id查询员工
// @Test
// public void testGetById(){
// empMapper.getById(20);
// }
// 根据条件查询员工
@Test
public void testList() {
//对应EmpMapper的public List<Emp> list(String name, Short gender, LocalDate begin,LocalDate end);
//放到集合中
List<Emp> empList = empMapper.list("张", (short) 1, LocalDate.of(2010, 1, 1), LocalDate.of(2020, 1, 1));
System.out.println(empList);
}
// @Test
// public void testL(){
// //对应EmpMapper的public List<Emp> list(String name, Short gender, LocalDate begin,LocalDate end);
// //放到集合中
// List<Emp> empList = empMapper.list("张",null,null,null);
// System.out.println(empList);
// }
//动态更新员工 - 更新ID为18的员工 username 更新为 Tom111, name更新为 汤姆111, gender更新为2
@Test
public void testUpdate2() {
//构造员工对象
Emp emp = new Emp();
emp.setId(19);
emp.setUsername("Tom222333");
// emp.setName("汤姆222");
// emp.setGender((short)1);
// emp.setUpdateTime(LocalDateTime.now());
//执行更新员工操作
empMapper.update2(emp);
}
//批量删除员工
@Test
public void testDeleteByIds() {
List<Integer> ids = Arrays.asList(13, 14, 15);
empMapper.deleteByIds(ids);
}
}
