创建生成周报excel表(三）编写Dao层

悠悠 2022-12-02 00:46 135阅读 0赞

在写完了 工具类PoiUtils 后 需要编写业务层 和 控制层来实现后端的整个场景

目前涉及到的东西有

1、数据库及其连接工具（myssql + mybatis），

2、开发框架 springboot 。

目前想实现的业务场景是：

通过 控制层收到的部门名称进行导出数据库中对应的工作内容。

流程:

# 1、创建mapper 层 #

实现对本周工作内容的 CRUD，以及下周工作计划的CRUD。

第一步是数据库创建：

在创建的webproject 数据库中新建了两张表  project（计划表）  summary（工作表）

-- ----------------------------
    -- Table structure for project
    -- ----------------------------
    DROP TABLE IF EXISTS `project`;
    CREATE TABLE `project` (
      `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
      `plan` varchar(255) NOT NULL,
      `plan_result` varchar(300) NOT NULL,
      `name` varchar(10) NOT NULL,
      `department` varchar(10) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of project
    -- ----------------------------
    
    -- ----------------------------
    -- Table structure for summary
    -- ----------------------------
    DROP TABLE IF EXISTS `summary`;
    CREATE TABLE `summary` (
      `id` int(3) unsigned NOT NULL AUTO_INCREMENT,
      `outline` varchar(255) NOT NULL,
      `details` varchar(1000) NOT NULL,
      `department` varchar(10) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

第二步是 springboot 链接数据库的配置文件 (我添加了一个驼峰命名法的配置文件，用于后续创建 实体类的时候无法识别）

server:
      port: 10002 #9001 作为消费者  10001开始作为生产者
      tomcat:
        uri-encoding: UTF-8
    
    
    spring:
      application:
        name: poiproject
    
      datasource:
         url: jdbc:mysql://xxx.xxxx.xxx.xxx:33306/webproject?characterEncoding=utf-8&useSSL=false # 不用ssl
         username: root
         password: PASSWORD
         driver-class-name: com.mysql.jdbc.Driver
    
    
      freemarker:
        allow-request-override: false
        cache: true
        charset: UTF-8
        check-template-location: true
        content-type: text/html
        expose-request-attributes: false
        expose-session-attributes: false
        expose-spring-macro-helpers: false
        suffix: .html
        template-loader-path: classpath:/templates/
    
    #设置驼峰命名法
    mybatis:
      configuration:
        map-underscore-to-camel-case: true

第三步是 创建实体类：Project 类 和 Summary类 作为用于存放 本周工作内容 和 下周工作计划的实体。

Project 类：

public class Project implements Serializable {
    
        private Integer id;
    
        private String plan;
    
        private String planResult;
    
        private String name;
    
        private String department;
    
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getPlan() {
            return plan;
        }
    
        public void setPlan(String plan) {
            this.plan = plan;
        }
    
        public String getPlanResult() {
            return planResult;
        }
    
        public void setPlanResult(String planResult) {
            this.planResult = planResult;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getDepartment() {
            return department;
        }
    
        public void setDepartment(String department) {
            this.department = department;
        }
    
        @Override
        public String toString() {
            return "Project{" +
                    "id=" + id +
                    ", plan='" + plan + '\'' +
                    ", planResult='" + planResult + '\'' +
                    ", name='" + name + '\'' +
                    ", department='" + department + '\'' +
                    '}';
        }
    }

Summary类

public class Summary implements Serializable {
    
        private int id;
    
        private String  outline;
    
        private String details;
    
        private String department;
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getOutline() {
            return outline;
        }
    
        public void setOutline(String outline) {
            this.outline = outline;
        }
    
        public String getDetails() {
            return details;
        }
    
        public void setDetails(String details) {
            this.details = details;
        }
    
        public String getDepartment() {
            return department;
        }
    
        public void setDepartment(String department) {
            this.department = department;
        }
    
    
        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            Summary summary = (Summary) o;
            return id == summary.id &&
                    Objects.equals(outline, summary.outline) &&
                    Objects.equals(details, summary.details) &&
                    Objects.equals(department, summary.department);
        }
    
        @Override
        public int hashCode() {
    
            return Objects.hash(id, outline, details, department);
        }
    }

3、创建Mapper层

SummaryMapper 用于对数据库 summary表的CRUD

@Mapper
    @Component
    public interface SummaryMapper {
    
        /*分页查询*/
        @Select("SELECT id, outline,details ,department FROM summary LIMIT ${page},${limit}")
        LinkedList<Summary> getSummaryListPage(@Param("page") int page, @Param("limit") int limit);
    
        /*根据部门查询 工作内容*/
        @Select("SELECT id, outline,details,department FROM summary WHERE department like '%${department}%'")
        LinkedList<Summary> getSummaryByDepartment(@Param("department") String department);
    
        /*根据 id 和部门查询一条工作内容*/
        @Select("SELECT id, outline,details ,department FROM summary WHERE id = '${id}' AND department = '${department}'")
        Summary getSummary(@Param("id") int id, @Param("department") String department);
    
        /*插入*/
        @Insert("INSERT INTO summary (outline,details,department) VALUES ('${outline}','${details}','${department}')")
        int insertSummary(@Param("outline") String outline, @Param("details") String details, @Param("department") String department);
    
        /*删除*/
        @Delete("DELETE FROM summary WHERE id = '${id}'AND department='${department}'")
        int deleteSummary(@Param("id") int id, @Param("department") String department);
    
        /*删除所有*/
        @Delete("DELETE FROM summary WHERE department like'%${department}%'")
        int deleteBySummary( @Param("department") String department);
    
        /*修改*/
        @Update("UPDATE summary SET outline='${outline}',details='${details}',department='${department}' WHERE id='${id}' ")
        int updateSummary(@Param("id") int id, @Param("outline") String outline, @Param("details") String details, @Param("department") String department);
    
    }

ProjectMapper 用于对数据库 Project 表进行 CRUD

@Mapper
    @Component
    public interface ProjectMapper {
    
        /*分页查询*/
        @Select("SELECT id, plan,plan_result,name,department FROM project LIMIT ${page},${limit}")
        LinkedList<Project> getProjectListPage(@Param("page") int page, @Param("limit") int limit);
    
        /*根据部门查询 计划内容*/
        @Select("SELECT id, plan,plan_result,name,department FROM project WHERE department like '%${department}%'")
        LinkedList<Project> getProjectByDepartment(@Param("department") String department);
    
        /*查询单个 部门内容*/
        @Select("SELECT id, plan,plan_result ,department FROM project WHERE id = '${id}' AND department = '${department}'")
        Project selectProject(@Param("id") int id, @Param("department") String department);
    
        /*插入*/
        @Insert("INSERT INTO project (plan,plan_result,name,department) VALUES ('${plan}','${planResult}','${name}','${department}')")
        int insertProject(@Param("plan") String plan, @Param("planResult") String planResult, @Param("name") String name,@Param("department")String department);
    
        /*删除*/
        @Delete("DELETE FROM project WHERE id = '${id}'AND department='${department}'")
        int deleteProject(@Param("id") int id, @Param("department") String department);
    
        /*删除所有*/
        @Delete("DELETE FROM project WHERE department like'%${department}%'")
        int deleteByProject( @Param("department") String department);
    
        /*更新*/
        @Update("UPDATE project SET plan='${plan}',plan_result='${planResult}',name='${name}',department='${department}' WHERE id='${id}' ")
        int updateProject(@Param("id") int id, @Param("plan") String plan, @Param("planResult") String planResult, @Param("name") String name,@Param("department")String department);
    
    }

目前mapper层搭建已完成。

自用