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

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+"表创建成功";
}

}
posted @ 2021-03-31 11:31  dark_one  阅读(349)  评论(0编辑  收藏  举报