Java使用代码处理表创建、删除操作

需求：按照当前日期创建后一天的表，然后删除前一年的数据表

分析思路，使用定时任务，动态创建数据表，删除数据表

具体如下：

1、定时任务

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

/**
 * 定时任务处理数据控制器

 */
@Component("sqlDataTask")
public class DealDataTaksController {

    
    @Autowired
    private TestService testService;


    //每天晚上23:59:00点执行
    @Scheduled(cron = "0 59 23 * * ?")
    public void createTable() {
        testService.create2dreliTable();
        System.out.println("========创建表执行完毕======== ");
    }


    //删除前一年的数据 每天晚上0点执行
    @Scheduled(cron = "0 0 0 * * ?")
    public void deletePreviousYearsData() {
        testService.drop2dreliTable();
        System.out.println("========删除表执行完毕======== ");
    }


}

2、接口

import java.util.List;

public interface TestService {

    /**
     * 创建表接口
     */
    int create2dreliTable();

    /**
     * 删除表接口
     */
    int drop2dreliTable();

   
}

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;


@Service
public class TestServiceImpl implements TestService {
    @Autowired
    private TestMapper testMapper;


    /**
     * 创建表接口
     */
    @Transactional(rollbackFor = Exception.class)
    @Synchronized
    @Override
    public int create2dreliTable() {
        String tablePrefix = "test_2dreli_";
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        Calendar calendar1 = Calendar.getInstance();
        //明天
        calendar1.add(Calendar.DATE, +1);
        Date start = calendar1.getTime();
        String tomorrow= dateFormat.format(start);
        String tableName = tablePrefix + tomorrow;
        System.out.println("tableName = " + tableName);
        return testMapper.create2dreliTable(tableName);
    }

    /**
     * 删除表接口
     */
    @Transactional(rollbackFor = Exception.class)
    @Synchronized
    @Override
    public int drop2dreliTable() {
        String tablePrefix = "test_2dreli_";
        //定义日期格式
        SimpleDateFormat format2 = new SimpleDateFormat("yyyyMMdd");
        Calendar calendar = Calendar.getInstance();
        //前一年
        calendar.add(Calendar.YEAR, -1);
        Date start = calendar.getTime();
        String yearBefore = format2.format(start);
        String tableName = tablePrefix + yearBefore;
        return testMapper.drop2dreliTable(tableName);
    }


}

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;


@Mapper
public interface TestMapper {

    /**
     * 创建表接口
     * @param tableName 表名称
     */
    public int  create2dreliTable(@Param("tableName") String tableName);

/**
     * 批量记录
     * @return 结果
     */
    public int batchInsert2dreliData(@Param("tableName") String tableName, @Param("list") List<Test2dreli> test2drelis);

    /**
     * 删除表接口
     * @param tableName 表名称
     */
    public int  drop2dreliTable(@Param("tableName") String tableName);

}

<?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.test.dao.TestMapper">
    
    <!--创建表的 SQL 语句-->
    <update id="create2dreliTable" parameterType="java.lang.String">
        CREATE TABLE IF NOT EXISTS `${tableName}`
        (
                                                 `id` int(20) NOT NULL AUTO_INCREMENT,
                                                 `lon` double NULL DEFAULT NULL COMMENT '经度',
                                                 `lat` double NULL DEFAULT NULL COMMENT '纬度',
												 `remarks` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
                                                 PRIMARY KEY (`id`) USING BTREE
        ) ENGINE = InnoDB AUTO_INCREMENT = 135712 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    </update>

    <!--删除的 SQL 语句-->
    <update id="drop2dreliTable" parameterType="java.lang.String">
        DROP TABLE IF EXISTS ${tableName}
    </update>

	<!--  批量新增数据 <foreach item="item" index="index" collection="list" separator=","> -->
    <insert id="batchInsert2dreliData" parameterType="java.util.List">
        insert into ${tableName}
        ( lon, lat, remarks)
        values
        <foreach item="item" index="index" collection="list" separator=",">
            (#{item.lon}, #{item.lat},#{item.remarks})
        </foreach>
    </insert>

</mapper>

至此即可完成动态创建表和删除表！