SpringBoot，Mybatis项目开发经验

SpringBoot，Mybatis项目开发经验

1. 数据库建表尽量建有ID，有ID删除会非常方便，没有ID需要多条件查询才能删除。例如批量删除：

//ID 'where id in idList'就可以轻松实现
<delete id="batchDelete" parameterType="java.util.List">
  delete from holiday
   where 1>2
   or id in
   <foreach collection="list" item="item" open="(" separator="," close=")">
     #{item}
   </foreach>
</delete>

//没有ID 需要匹配所有变量，写起来就比较麻烦了
<delete id="batchDelete" parameterType="java.util.List">
  delete from userinfo_of_run where
  <foreach collection="list" item="item" separator="or">
    userinfo_id = #{item.userinfoId,jdbcType=INTEGER}
    and num_run_id = #{item.numRunId,jdbcType=INTEGER}
    and startdate = #{item.startdate,jdbcType=TIMESTAMP}
    and enddate = #{item.enddate,jdbcType=TIMESTAMP}
  </foreach>
</delete>

2. 批量新增删除输入collection参数定义：

//1个参数时collection使用list、map等作为参数
<delete id="batchDelete" parameterType="java.util.List">
  delete from holiday
   where 1>2
   or id in
   <foreach collection="list" item="item" open="(" separator="," close=")">
     #{item}
   </foreach>
</delete>

//多个参数时collection使用入参参数名作为参数
<insert id="batchSave" useGeneratedKeys="true" keyProperty="id" >
  insert ignore into role_menu(menu_id, role_id)
  values
  <foreach collection="menuIds" item="mid" separator=",">
    (#{mid}, #{roleId})
  </foreach>
</insert>

3.新增有自增ID使用useGeneratedKeys为true,keyProperty为自增ID，parameterType为参数类型

<insert id="saveRole" useGeneratedKeys="true" keyProperty="roleId" parameterType="com.jlink.entity.Role">
	INSERT INTO
	role(name, code, create_time, is_delete, is_active)
	VALUES
	(#{name},#{code},#{createTime}, #{isDelete}, #{isActive})
</insert>

查询resultType为查询类型，parameterType为输入类型，多输入参数可以省略。

<select id="queryRoleById" resultType="com.jlink.entity.Role" parameterType="Integer">
	SELECT role_id, name, code, create_time, modify_time, is_delete, is_active
	FROM role
	where
	role_id = #{roleId}
</select>

4.日期请求和返回使用JsonFormat定义格式，默认为时间戳

public class UserinfoOfRun {
    private Integer userinfoId;

    private Integer numRunId;

    @JsonFormat(pattern = "yyyy-MM-dd",timezone="GMT+8")
    private Date startdate;

    @JsonFormat(pattern = "yyyy-MM-dd",timezone="GMT+8")
    private Date enddate;

    private Integer isNotOfRun;

    private Integer orderRun;
}

//maven配置
<!-- Json-->
 <dependency>
     <groupId>com.fasterxml.jackson.core</groupId>
     <artifactId>jackson-core</artifactId>
     <version>2.9.5</version>
 </dependency>
 <dependency>
     <groupId>com.fasterxml.jackson.core</groupId>
     <artifactId>jackson-databind</artifactId>
     <version>2.9.5</version>
 </dependency>
 <dependency>
     <groupId>com.fasterxml.jackson.core</groupId>
     <artifactId>jackson-annotations</artifactId>
     <version>2.9.5</version>
 </dependency>
 <dependency>
     <groupId>com.fasterxml.jackson.module</groupId>
     <artifactId>jackson-module-jaxb-annotations</artifactId>
     <version>2.9.5</version>
 </dependency>

5. 解析EXCEL使用poi

//读取excel
private Workbook readExcel(String filePath){
    Workbook wb = null;
    if(filePath==null){
        return null;
    }
    String extString = filePath.substring(filePath.lastIndexOf("."));
    InputStream is = null;
    try {
        is = new FileInputStream(filePath);
        if(".xls".equals(extString)){
            return wb = new HSSFWorkbook(is);
        }else if(".xlsx".equals(extString)){
            return wb = new XSSFWorkbook(is);
        }else{
            return wb = null;
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return wb;
}

//表格内容类型有多种，NUMERIC、STRING、FORMULA等
private Object getCellFormatValue(Cell cell){
    Object cellValue = null;
    if(cell!=null){
//            System.out.println(cell+","+cell.getCellType());
        //判断cell类型
        switch(cell.getCellType()){
            case NUMERIC:{
                //cell.setCellType(STRING);
                //cellValue = importByExcelForDate(cell.getStringCellValue());
                cellValue = cell.getNumericCellValue();
                break;
            }
            case FORMULA:{
                //判断cell是否为日期格式
                if(DateUtil.isCellDateFormatted(cell)){
                    //转换为日期格式YYYY-mm-dd
                    cellValue = cell.getDateCellValue();
                }else{
                    //数字
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
            case STRING:{
                cellValue = cell.getRichStringCellValue().getString();
                break;
            }
            default:
                cellValue = "";
        }
    }else{
        cellValue = "";
    }
    return cellValue;
}

但意外地发现检测‘2001/5/9’为‘NUMERIC’,getNumericCellValue得到的是自1900年的天数，需要转化为Date

private String importByExcelForDate(String value) {//value就是它的天数
    String currentCellValue = "";
    if(value != null && !value.equals("")){
        Calendar calendar = new GregorianCalendar(1900,0,-1);
        Date d = calendar.getTime();
        Date dd = DateUtils.addDays(d,Integer.valueOf(value));
        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
        currentCellValue = formater.format(dd);
    }
    return currentCellValue;
}

整个代码如下：

public boolean saveExcel(File file) throws ParseException {
    Workbook wb =null;
    Sheet sheet = null;
    Row row = null;
    List<Map<String,String>> list = null;
    String cellData = null;
    String columns[] = {"DEPT_NO","BADGENUMBER","SSN","NAME","GENDER","HIREDDAY","CardNo","LeaveDate"};
    wb = readExcel(file.getAbsolutePath());
    if(wb != null){
        //用来存放表中数据
        list = new ArrayList<Map<String,String>>();
        //获取第一个sheet
        sheet = wb.getSheetAt(0);
        //获取最大行数
        int rownum = sheet.getPhysicalNumberOfRows();
        //获取第一行
        row = sheet.getRow(0);
        //获取最大列数
        int colnum = row.getPhysicalNumberOfCells();
        for (int i = 1; i<rownum; i++) {
            Map<String,String> map = new LinkedHashMap<String,String>();
            row = sheet.getRow(i);
            if(row !=null){
                for (int j=0;j<colnum;j++){
                    cellData = (String) getCellFormatValue(row.getCell(j));
                    map.put(columns[j], cellData);
                }
            }else{
                break;
            }
            list.add(map);
        }
    }

    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    List<Userinfo> userinfos = new LinkedList<>();
    //遍历解析出来的list
    for (Map<String,String> map : list) {
        Userinfo userinfo = new Userinfo();
        for (Map.Entry<String,String> entry : map.entrySet()) {
//                    System.out.print(entry.getKey()+":"+entry.getValue()+",");
            switch(entry.getKey()){
                case "DEPT_NO":
                    userinfo.setDeptNo(entry.getValue());
                    break;
                case "BADGENUMBER":
                    userinfo.setBadgenumber(entry.getValue());
                    break;
                case "SSN":
                    userinfo.setSsn(entry.getValue());
                    break;
                case "NAME":
                    userinfo.setName(entry.getValue());
                    break;
                case "GENDER":
                    userinfo.setGender(entry.getValue());
                    break;
                case "HIREDDAY":
                    if(!StringUtils.isBlank(entry.getValue())){
                        userinfo.setHiredday(dateFormat.parse(entry.getValue()));
                    }
                    break;
                case "CardNo":
                    userinfo.setCardno(entry.getValue());
                    break;
                case "LeaveDate":
                    if(!StringUtils.isBlank(entry.getValue())){
                        userinfo.setHiredday(dateFormat.parse(entry.getValue()));
                    }
                    break;

                default:
                    System.out.println(entry.getKey());

            }
        }
        userinfos.add(userinfo);
//                System.out.println();
    }
//        List<Userinfo> userinfos = new LinkedList<>();
    return batchSave(userinfos);
}

Maven配置POI:

 <!-- 引入poi，解析workbook视图 -->
 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>4.0.0</version>
 </dependency>
 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml</artifactId>
     <version>4.0.0</version>
 </dependency>

SpringBoot，Mybatis项目开发流程

1. 建数据表，Mybatis生成xml和dao文件，修改文件。
2. Controller构思请求参数，结果参数，需要新请求、结果对象时新建放入DTO（Data Transfer Object）中；
3. Mybatis实现sql查询，时间长了会发现很多其实关键都是写sql语句，因为sql是对数据最快的操作方式；
4. 定义dao层对应的方法；
5. 定义service对应的接口和方法；
6. 使用postman进行测试；
7. 填写API文档；
8. 查增删改，先做查询接口，得到结果可以用于添加接口测试，然后做删除，删除添加的数据，再做修改，因为修改不是必要的，查增删一般是必要的。
9. 在测试过程中发现有没有考虑到的情况需要再次构思逻辑，修改代码。
10. 尽量将逻辑代码再service中实现，controller只负责请求参数、返回参数的处理。