小宇工作日志

动态查询并创建数据表

逻辑层代码

public interface MysqlOperateService {
    //判断表是否存在
    public int existTable(String tableName);

    //删除表
    public int dropTable(@Param("tableName") String tableName);

    //创建表
    public int creatNewTable(@Param("tableName") String tableName);
}

 Impl层代码

@Service
public class MysqlOperateServiceImpl implements MysqlOperateService {
    @Autowired
    private MysqlOperateMapper mysqlOperateMapper;
    @Override
    public int existTable(String tableName) {
        return mysqlOperateMapper.existTable(tableName);
    }

    @Override
    public int dropTable(String tableName) {
        return mysqlOperateMapper.dropTable(tableName);
    }

    @Override
    public int creatNewTable(String tableName) {
        return mysqlOperateMapper.creatNewTable(tableName
        );
    }
}

Mapper

public interface MysqlOperateMapper {

    /**
     * 判断表是否存在
     *
     * @param tableName 需要查询的表名
     * @return 结果
     */
    public int existTable(String tableName);

    /**
     * 删除表
     *
     * @param tableName 需要删除的表名
     * @return 结果
     */
    public int dropTable(@Param("tableName") String tableName);

    /**
     * 删除表
     *
     * @param tableName 创建表
     * @return 结果
     */
    public int creatNewTable(@Param("tableName") String tableName);
}

Mapper.xml

<mapper namespace="com.seefar.system.mapper.MysqlOperateMapper">

    <select id="existTable" parameterType="String" resultType="Integer">
        select count(*)
        from information_schema.TABLES
        where table_schema = 'tj2021'
          and table_name = #{tableName}
    </select>
    <update id="creatNewTable" parameterType="String">
        CREATE TABLE `${tableName}`
        (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id)
        )
    </update>
    <update id="dropTable">
        DROP TABLE IF EXISTS ${tableName}
    </update>
</mapper>

定时任务模块

注：1.要写注解@Component

     2.访问的字段为方法名+具体方法名

@Component("mysqlCheck")
public class MySqlCheck {

    @Autowired
    private MysqlOperateService mysqlOperateService;


    /**
     * 实现动态检查并创建表
     */

    public void MysqlOperate() {
        SimpleDateFormat time = new SimpleDateFormat("yyyy-MM-dd");
        String date = time.format(new Date());
        String tableName = "z_process_" + date;
        System.out.println(tableName);
        int a = mysqlOperateService.existTable(tableName);
        if (a > 0) {
            System.out.println("表存在");
        } else {
            mysqlOperateService.creatNewTable(tableName);
        }
    }

    public void MysqlSelect() {
        LocalDate nowDay = LocalDate.now();
        LocalDate beforeDay = nowDay.minusDays(30);
        long numOfDays = ChronoUnit.DAYS.between(beforeDay, nowDay);
        List<LocalDate> listOfDates1 = Stream.iterate(beforeDay, date -> date.plusDays(1))
                .limit(numOfDays)
                .collect(Collectors.toList());
        listOfDates1.forEach(item -> {
            DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
//            System.out.println(dateTimeFormatter.format(item));
                String tableName = "z_process_" + dateTimeFormatter.format(item);
                System.out.println(tableName);
                int a = mysqlOperateService.existTable(tableName);
                if (a>0){
                    System.out.println("表存在");
                }
                else{
//                    mysqlOperateService.creatNewTable(tableName);
                }



        });
    }
}

如何获取当前日期及如何给获得的日期设置格式

  SimpleDateFormat time = new SimpleDateFormat("yyyy-MM-dd");
        String date = time.format(new Date());