Mysql存储过程动态建表
DELIMITER $
CREATE PROCEDURE test_create_table (
IN dept_emp_test VARCHAR (30) # 表名
)
BEGIN
# 利用concat拼接字符串函数,将复制表的sql赋值到变量test_create_table
# sql 语句里面的dept_emp 是作为被复制的表
set @test_create_table = concat('CREATE TABLE',dept_emp_test,"(ID varchar(64) primary key)"," SELECT * FROM dept_emp WHERE 1=2;");
# 预处理建表
PREPARE test_create_table FROM @test_create_table;
# 执行sql
EXECUTE test_create_table;
END $
@Scheduled(cron = " 0 */1 * * * ?")
public void testInsert() {
String curName = Thread.currentThread().getName() ;
System.out.println("当前时间:"+ LocalDateTime.now(ZoneOffset.of("+8")).format(df)+" 开始插入数据 === 线程名: "+curName);
Connection connection = JDBCUtils.getConnection();
PreparedStatement statement=null;
System.out.println("开始插入数据");
Long startTime = System.currentTimeMillis();
try {
String sql="INSERT INTO dept_emp(emp_no, dept_no,from_date,to_date) VALUES (?,?,?,?)";
statement=connection.prepareStatement(sql);
//批量操作必须关闭自动提交
connection.setAutoCommit(false);
for (int i = 1; i <=100000 ; i++) {
statement.setInt(1, i);
statement.setString(2, "d00" + i);
statement.setDate(3, new Date(System.currentTimeMillis()) );
statement.setDate(4, new Date(System.currentTimeMillis()));
statement.addBatch();//把数据放入缓存区
// if(i%10000==0){
// //刷新缓存区
// statement.executeBatch();
// statement.clearBatch();//清除之前的数据
// }
}
//刷新缓存区
statement.executeBatch();
statement.close();
connection.commit();//执行完后,手动提交事务
connection.setAutoCommit(true);//在把自动提交打开
Long endTime = System.currentTimeMillis();
System.out.println("插入完毕,用时:" + (endTime - startTime));
} catch (Exception e) {
e.printStackTrace();
}finally {
//关闭资源,倒关
JDBCUtils.release(connection, statement, null);
}
}
// @Scheduled(cron = "0 58 23 L * ? ")
@Scheduled(cron = " 0 */5 * * * ?")
public void testCreate() {
System.out.println("开始复制表");
String curName = Thread.currentThread().getName() ;
System.out.println("当前时间:"+ LocalDateTime.now(ZoneOffset.of("+8")).format(df)+" 数据库 复制 ========== 线程名: "+curName);
Connection connection = JDBCUtils.getConnection();
PreparedStatement statement=null;
Long startTime = System.currentTimeMillis();
DateTimeFormatter sdf = DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss");
String s= LocalDateTime.now(ZoneOffset.of("+8")).format(sdf);
s="dept_emp_"+s;
String newTableName="dept_emp_copy";
/**
* 调用存储过程建表
*/
String sql = "{call test_create_table(?)}";
Connection conn = null;
CallableStatement call = null;
PreparedStatement ps =null;
try {
//得到一个数据库连接
conn = JDBCUtils.getConnection();
//通过连接创建出statement
call = conn.prepareCall(sql);
call.setString(1, newTableName);
call.execute();
/**
* 此语句给原表改名字 我们根据年月给原表来命名
*/
String sql1 = " rename table dept_emp to "+ s;
ps= conn.prepareStatement(sql1);
ps.execute();
/**
* 此语句将复制的表改成原表的名字
*/
Thread.sleep(1000);
String sql2 ="rename table "+newTableName +" to dept_emp";
ps= conn.prepareStatement(sql2);
ps.execute();
Long endTime = System.currentTimeMillis();
System.out.println("复制完毕,用时:" + (endTime - startTime));
} catch (Exception e) {
e.printStackTrace();
}finally{
//关闭连接,释放资源
JDBCUtils.release(conn, ps, null);
}
}