No.5 MybatisGenerator+手写sql+定时任务调用更新

概述

采用Mybatis CodeGenerator，来自动将mysql中表内容，关联到spring boot中。目前只发现单表操作的方法，多表关联要手写sql。所以下面呈现了如何结合。
我有一个模拟数据表dtableship，里面有每个时刻的船舶数据模拟，要定时调用更新到动态船舶表中tableship。所以下面呈现了如何做 定时任务调用更新。

CodeGenerator类中调用： genCode(“tableship”);
会自动产生：

model	dao	service	impl	web
Tableship	TableshipMapper	TableshipService	TableshipServiceImpl	TableshipController

在TableshipController中已经存在简单的CURD了，但是复杂的写法，可以用service中方法来实现单表的调用。

   /**
     * 按条件查询  select from tableship WHERE fieldName=value，返回单个数据
     * @param fieldName  代表字段名（Model中的成员变量，Tableship）
     * @param value      必须是唯一的（返回单个）
     * @return
     * @throws TooManyResultsException
     */
    @PostMapping("/findBy")
    public Result findBy(@RequestParam String fieldName, @RequestParam Object value) throws TooManyResultsException {
        Tableship tableship = tableshipService.findBy(fieldName, value);
        return ResultGenerator.genSuccessResult(tableship);
    }


    /**
     * 按条件查询
     * @param fieldName  代表字段名（Model中的成员变量，Tableship）
     * @param value      可以不是唯一的（返回多个List）
     */
    @PostMapping("/findByCondition")
    public Result findByCondition(@RequestParam String fieldName, @RequestParam Object value) {

        Condition condition = new Condition(Tableship.class);
        Example.Criteria criteria = condition.createCriteria();
        criteria.orEqualTo(fieldName, value);

        List<Tableship> list = tableshipService.findByCondition(condition);
        return ResultGenerator.genSuccessResult(list);
    }

    /**
     * 按2个条件查询  (例如：航道划分=10010，时间=2019-06-27 16:50:50）
     *      * sql: select * from tableship where fieldName=value and fieldName1=value2
     * @param fieldName     字段名1 fieldName=channelDivisionId ,  value=10010
     * @param value
     * @param fieldName1    字段名2 fieldName1=datatime, value2=2019-06-27 16:50:50
     * @param value1
     * @return
     */
    @PostMapping("/findByCondition2")
    public Result findByCondition2(@RequestParam String fieldName, @RequestParam Object value,
                                   @RequestParam String fieldName1, @RequestParam Object value1) {

        Condition condition = new Condition(Tableship.class);
        Example.Criteria criteria = condition.createCriteria();
        criteria.andEqualTo(fieldName, value);
        criteria.andEqualTo(fieldName1, value1);
        List<Tableship> list = tableshipService.findByCondition(condition);
        return ResultGenerator.genSuccessResult(list);
    }


    /**
     * 按条件查询(例如：航道划分=10010，uuid在1~200之间）
     * sql: select * from tableship where fieldName=value and fieldName1 between value1 and value2
     * @param fieldName       fieldName=value ->  channelDivisionId=10010
     * @param value                 fieldName=channelDivisionId ,  value=10010
     * @param fieldName1      fieldName1 between value1 and value2   ->  uuid between 1 and 20
     * @param value1                fieldName1=uuid , value1=1, value2=200
     * @param value2
     * @return                查询到的所有船记录 List
     */
    @PostMapping("/findByConditionBetween")
    public Result findByConditionBetween(@RequestParam String fieldName, @RequestParam Object value,
                                         @RequestParam String fieldName1, @RequestParam Object value1,@RequestParam Object value2) {

        Condition condition = new Condition(Tableship.class);
        Example.Criteria criteria = condition.createCriteria();
        criteria.orEqualTo(fieldName, value);
        criteria.andBetween(fieldName1, value1,value2);
        List<Tableship> list = tableshipService.findByCondition(condition);
        return ResultGenerator.genSuccessResult(list);
    }

    /**
     * 条件查询，where fieldName between value1 and value2
     * @param fieldName 代表字段名（Model中的成员变量，Tableship）
     * @param value1    在1 和 2 之间
     * @param value2
     * @return          List
     */
    @PostMapping("/findByBetween")
    public Result findByBetween(@RequestParam String fieldName, @RequestParam Object value1,@RequestParam Object value2) {

        Condition condition = new Condition(Tableship.class);
        Example.Criteria criteria = condition.createCriteria();
        criteria.andBetween(fieldName,value1,value2);

        List<Tableship> list = tableshipService.findByCondition(condition);
        return ResultGenerator.genSuccessResult(list);
    }

现在我们不适用这些CodeGenerator自带的方法了，用自己手写sql
首先写好sql语句，在mysql中测试准确性。
写入.xml中

1. TableshipMapper.xml

<?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.company.project.dao.TableshipMapper">
  <resultMap id="BaseResultMap" type="com.company.project.model.Tableship">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="mmsi" jdbcType="VARCHAR" property="mmsi" />
    <result column="Channel_division_ID" jdbcType="VARCHAR" property="channelDivisionId" />
    <result column="longitude" jdbcType="DECIMAL" property="longitude" />
    <result column="latitude" jdbcType="DECIMAL" property="latitude" />
    <result column="datetime" jdbcType="TIMESTAMP" property="datetime" />
    <result column="heading" jdbcType="VARCHAR" property="heading" />
    <result column="speed" jdbcType="VARCHAR" property="speed" />
    <result column="shipclass" jdbcType="VARCHAR" property="shipclass" />
    <result column="state" jdbcType="VARCHAR" property="state" />
    <result column="draught" jdbcType="VARCHAR" property="draught" />
    <result column="length" jdbcType="VARCHAR" property="length" />
    <result column="width" jdbcType="VARCHAR" property="width" />
    <result column="callsign" jdbcType="VARCHAR" property="callsign" />
  </resultMap>
  
<!--手写sql部分-->
  <delete id="deleteTableshipById">
        delete from tableship where id=#{id1}
    </delete>

  <update id="updateTableshipBetweenId" parameterType="com.company.project.model.Tableship">
    update tableship t1 inner join (select * from dtableship where id between #{id1} and #{id2}) t2 on t1.mmsi =t2.mmsi set t1.Channel_division_ID = t2.Channel_division_ID,t1.longitude=t2.longitude,t1.latitude=t2.latitude
    ,t1.heading=t2.heading,t1.speed=t2.speed;
    </update>
</mapper>

2. dao-TableshipMapper

把调用方法写上去，分配参数；
参数加 @Param(“id1”) ，很重要，否则报错，找不到参数。
引用：
异常：
org.apache.ibatis.binding.BindingException: Parameter ‘Id’ not found. Available parameters are [arg1, Id, param1, param2]
解决：
在XXXmapper.java 的方法参数中，添加注解@Param（），以让ibatis识别

int select(@Param(“Id”) Long Id);

作者：墨咖
来源：CSDN
原文：https://blog.csdn.net/qq_37335810/article/details/83745384
版权声明：本文为博主原创文章，转载请附上博文链接！

package com.company.project.dao;

import com.company.project.core.Mapper;
import com.company.project.model.Tableship;
import org.apache.ibatis.annotations.Param;

public interface TableshipMapper extends Mapper<Tableship> {

    Integer deleteTableshipById(Integer id1);

    Integer updateTableshipBetweenId(@Param("id1")Integer id1, @Param("id2")Integer id2);
}

3.TableshipService & ServiceImpl

Service中写入方法，参数

package com.company.project.service;
import com.company.project.model.Tableship;
import com.company.project.core.Service;


/**
 * Created by CodeGenerator on 2019/07/18.
 */
public interface TableshipService extends Service<Tableship> {

    Integer deleteTableshipById(Integer id1);

    Integer updateTableshipBetweenId(Integer id1,Integer id2);
}

Impl中重写方法Override；把Mapper 自动装配）@Resource

package com.company.project.service.impl;

import com.company.project.dao.TableshipMapper;
import com.company.project.model.Tableship;
import com.company.project.service.TableshipService;
import com.company.project.core.AbstractService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;


/**
 * Created by CodeGenerator on 2019/07/18.
 */
@Service
@Transactional
public class TableshipServiceImpl extends AbstractService<Tableship> implements TableshipService {
    @Resource
    private TableshipMapper tableshipMapper;

    @Override
    public Integer deleteTableshipById(Integer id1) {
        return tableshipMapper.deleteTableshipById(id1);
    }

    @Override
    public Integer updateTableshipBetweenId(Integer id1, Integer id2) {
        return tableshipMapper.updateTableshipBetweenId(id1,id2);
    }
}

4.TableshipController

写上方法和，url，参数，

package com.company.project.web;

import com.company.project.core.Result;
import com.company.project.core.ResultGenerator;
import com.company.project.model.Tableship;
import com.company.project.service.TableshipService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import tk.mybatis.mapper.entity.Condition;
import tk.mybatis.mapper.entity.Example;

import javax.annotation.Resource;
import java.util.List;

/**
* Created by CodeGenerator on 2019/07/18.
*/
@RestController
@RequestMapping("/tableship")
public class TableshipController {
    @Resource
    private TableshipService tableshipService;

    @PostMapping("/add")
    public Result add(Tableship tableship) {
        tableshipService.save(tableship);
        return ResultGenerator.genSuccessResult();
    }

    @PostMapping("/delete")
    public Result delete(@RequestParam Integer id) {
        tableshipService.deleteById(id);
        return ResultGenerator.genSuccessResult();
    }

    @PostMapping("/update")
    public Result update(Tableship tableship) {
        tableshipService.update(tableship);
        return ResultGenerator.genSuccessResult();
    }

    @PostMapping("/detail")
    public Result detail(@RequestParam Integer id) {
        Tableship tableship = tableshipService.findById(id);
        return ResultGenerator.genSuccessResult(tableship);
    }

    @PostMapping("/list")
    public Result list(@RequestParam(defaultValue = "0") Integer page, @RequestParam(defaultValue = "0") Integer size) {
        PageHelper.startPage(page, size);
        List<Tableship> list = tableshipService.findAll();
        PageInfo pageInfo = new PageInfo(list);
        return ResultGenerator.genSuccessResult(pageInfo);
    }

    @PostMapping("/findByBetween")
    public Result findByBetween(@RequestParam String fieldName, @RequestParam Object value1,@RequestParam Object value2) {

        Condition condition = new Condition(Tableship.class);
        Example.Criteria criteria = condition.createCriteria();
        criteria.andBetween(fieldName,value1,value2);

        List<Tableship> list = tableshipService.findByCondition(condition);
        return ResultGenerator.genSuccessResult(list);
    }

    @PostMapping("/deleteTableshipById")
    public Result deleteTableshipById(@RequestParam Integer id1) {
        tableshipService.deleteTableshipById(id1);
        return ResultGenerator.genSuccessResult();

    }

    @PostMapping("/updateTableshipBetweenId")
    public Result updateTableshipBetweenId(@RequestParam Integer id1, @RequestParam Integer id2) {
        tableshipService.updateTableshipBetweenId(id1, id2);
        return ResultGenerator.genSuccessResult();

    }


}

这样在http用 POST就能访问了：
POST-- http://192.168.2.144:8080/tableship/updateTableshipBetweenId
id1=6&id2=10

定时任务

用注解
@Component
@Configuration //1.主要用于标记配置类，兼备Component的效果。
@EnableScheduling // 2.开启定时任务

//3.添加定时任务
@Scheduled(cron = “0/5 * * * * ?”)//或直接指定时间间隔，例如：5秒//@Scheduled(fixedRate=5000)

package com.company.project.schedule;

import com.company.project.service.TableshipService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.time.LocalDateTime;


@Component
@Configuration      //1.主要用于标记配置类，兼备Component的效果。
@EnableScheduling   // 2.开启定时任务
public class ScheduleTableship {
   @Autowired
   private TableshipService tableshipService;

   int id1Initial=1;
   int id2Initial=5;

   int num=5;
   int amount=20;
   int id1=0;
   int id2=0;
   int i=0;

   //3.添加定时任务
   @Scheduled(cron = "0/5 * * * * ?")//或直接指定时间间隔，例如：5秒//@Scheduled(fixedRate=5000)
   private void configureTasks() {


       if (id2<amount){
           id1=id1Initial+num*i;
           id2=id2Initial+num*i;
           i++;
       }
       else {
           id1=id1Initial;
           id2=id2Initial;
           i=1;
       }
       System.out.println("DUserSchedule执行时间: " + LocalDateTime.now());
       System.out.println("id1: " + id1+", id2: " + id2 );
       tableshipService.updateTableshipBetweenId(id1,id2);
//        System.out.println(tableshipService.findAll());
   }
   }

这里实现了，定时 5s，从模拟表，读数据id1~id2的记录更新到动态表。