package cn.net.leadu.backupbase.utils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import java.sql.*;
import java.util.Calendar;
@Repository
public class CommonTableInsert {
    private static final Logger log = LoggerFactory.getLogger(CommonTableInsert.class);
    private String driver="com.mysql.jdbc.Driver";
    private String url="jdbc:mysql://10.10.1.86:3306/taimeng?useUnicode=true&characterEncoding=UTF-8";
    private String userName="vms";
    private String password="567uyT%^&";


    public String  createtable() throws SQLException, ClassNotFoundException{
        Calendar calendar = Calendar.getInstance();
        calendar.add(calendar.DATE, 2);
        String dateStr = DateUtils.getStrDate(calendar.getTime(), DateUtils.simpleDateFormat_14);
        String tablename="LOG_"+dateStr.substring(0,8);
        //连接数据库
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, userName, password);
        Statement stat = conn.createStatement();
        //检索元数据对象
        DatabaseMetaData metaData = conn.getMetaData();
        //创建对象
        String[] types = {"TABLE"};
        //获取数据库表名
        ResultSet tables = metaData.getTables("taimeng", "vms",tablename, types);
        // 判断表是否存在，如果存在则什么都不做，否则创建表
        if(tables.next()){
            log.info(tablename+"表已存在");
            return tablename+"表已存在";
        }
        else{
            // 先判断是否纯在表名，有则先删除表在创建表
//            stat.executeUpdate("DROP TABLE IF EXISTS sys_admin_divisions;CREATE TABLE sys_admin_divisions("
            //创建行政区划表
            stat.executeUpdate("CREATE TABLE `"+tablename+"`  (\n" +
                    "  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,\n" +
                    "  `source` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,\n" +
                    "  `url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,\n" +
                    "  `user_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,\n" +
                    "  `user_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,\n" +
                    "  `request_info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,\n" +
                    "  `response_info` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,\n" +
                    "  `request_time` datetime(0) DEFAULT NULL,\n" +
                    "  `remark` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,\n" +
                    "  PRIMARY KEY (`id`) USING BTREE\n" +
                    ")"
            );
            log.info(tablename+"表创建成功");
        }
        // 释放资源
        stat.close();
        conn.close();
        return tablename+"表创建成功";
    }

}

自动创建数据库中的表结构