SpringBootCRUD

1. 首先在application.yml配置文件中 添加数据库连接四要素
   

2.创建实体类 Book

点击查看代码

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Book {
    private Long id;
    private String bname;
    private String author;
    private BigDecimal salePrice;
    private BigDecimal costPrice;
    private Long classify;

}

3. 创建BookMapper接口 并在类的上面添加@Mapper 注解 将类交给Spring容器
   

除此之外,还可以通过在启动类上添加 MapperScan 注解 扫描包下的所有mapper,并放入容器中

4. 通过@Autowired注解 进行依赖注入 在容器中找到BookMapper 并将它的实例 注入到 bookMapper 对象中 创建测试
   ` @Autowired
   private BookMapper bookMapper;

   @Test
   void test1() {
   List books = bookMapper.selAll();
   books.forEach(System.out::println);
   }`

5.在BookMapper接口中 创建方法 并通过@select注解 编写sql语句 通过@Results以及@Result 设置映射规则 将数据库中的字段映射到java对象的字段中

点击查看代码

@Mapper
public interface BookMapper {
    @Select("select * from t_books")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "author", column = "author"),
            @Result(property = "price", column = "price"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<Book> selAll();
}

测试结果:

6.继续添加 save方法,用于添加

点击查看代码

@Insert(" insert into t_books(bname,author,salePrice,costPrice,classify) values(#{bname},#{author},#{salePrice},#{costPrice},#{classify})")
    int save(Book book);

创建测试:

点击查看代码

 @Test
     void test2() {
         Book book = new Book();
         book.setBname("test");
         book.setAuthor("test");
         book.setSalePrice(new BigDecimal("0"));
         book.setCostPrice(new BigDecimal("0"));
         book.setClassify(1L);
         int save = bookMapper.save(book);
         if(save>0){
             System.out.println("保存成功");
         }else{
             System.out.println("保存失败");
         }

     }

测试结果: 

7.批量删除

点击查看代码

 @DeleteProvider(type = BookProvider.class,method = "batchDelete")
    int batchDelete(Long[] ids);

    class BookProvider {
        public String batchDelete(Long[] ids) {
            StringBuffer sql = new StringBuffer("delete from t_books where id in (");
            for (Long id : ids) {
                sql.append(id + ",");
            }
            String substring = sql.substring(0, sql.length() - 1);
            substring += ")";
            return substring;
        }
    }

说明: 通过@DeleteProvider 注解 实现此功能,创建内部类 BookProvider,在内部类中通过StringBuffer处理字符串,写具体的sql语句

8.创建测试:

点击查看代码

@Test
     void test6() {
        Long[] ids = {19L,20L};
        int delete= bookMapper.batchDelete(ids);
        if(delete>0){
            System.out.println("删除成功");
        }else{
            System.out.println("删除失败");
        }
     }

测试结果:

9.局部修改

点击查看代码

@UpdateProvider(type = BookProvider.class,method = "patch")
    int patch(Book book);
public String patch(Book book) {
            StringBuffer sql = new StringBuffer("update t_books set ");
            if (book.getBname() != null) {
                sql.append("bname=#{bname},");
            }
            if (book.getAuthor() != null) {
                sql.append("author=#{author},");
            }
            if (book.getSalePrice() != null) {
                sql.append("salePrice=#{salePrice},");
            }
            if (book.getCostPrice() != null) {
                sql.append("costPrice=#{costPrice},");
            }
            if (book.getClassify() != null) {
                sql.append("classify=#{classify},");
            }
            String substring = sql.substring(0, sql.length() - 1);
            substring += " where id=#{id}";
            return substring;
        }

创建测试:

点击查看代码

@Test
     void test7() {
         Book book= new Book();
         book.setId(21L);
         book.setSalePrice(new BigDecimal("100"));
         int update = bookMapper.patch(book);
         if(update>0){
             System.out.println("修改成功");
         }else{
             System.out.println("修改失败");
         }
     }

测试结果:

10. 条件查询

点击查看代码

@SelectProvider(type = SQLProvider.class,method = "selByCondition")
    List<Employee> selByCondition(EmployeeQO qo);


public String selByCondition(EmployeeQO qo){
            StringBuffer sql = new StringBuffer("select * from emp where 1=1 ");
            if(!StringUtils.isEmpty(qo.getEname())){
                sql.append(" and ename like concat('%',#{ename},'%')");
            }
            if(!StringUtils.isEmpty(qo.getSalStart())){
                sql.append(" and sal >= #{salStart}");
            }
            if(!StringUtils.isEmpty(qo.getSalEnd())){
                sql.append(" and sal <= #{salEnd}");
            }
            return sql.toString();
        }

11. 创建测试

点击查看代码

//条件查询
     @Test
     void test8() {
         BookQO qo= new BookQO("波特");
         List<Book> books = bookMapper.selByCondition(qo);
         books.forEach(System.out::println);
     }

测试结果:

12.分页查询 首先引入依赖

点击查看代码

//分页查询
     @Test
     void test9() {
         PageHelper.startPage(1,3);
         // 没有分页信息的条件查询
         List<Book> list = bookMapper.selAll();
         //分页参数
         PageInfo<Book> pageInfo = new PageInfo<>(list);
         //获取切分后的小集合
         List<Book> pageInfoList = pageInfo.getList();
         pageInfoList.forEach(System.out::println);
     }

测试结果: