1:创建Connection连接
在properties配置文件中加入需要复制数据库的链接信息,即模板库
tlcad.platform.db.manager.sourceDburl=jdbc:mysql://**:30365/ykxt_template_init?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC
tlcad.platform.db.manager.username=***
tlcad.platform.db.manager.password=***
package com.tlcad.platform.dbmanager.api.util;
import com.tlcad.platform.core.utils.SnowflakeIdWorker;
import com.tlcad.platform.properties.$;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* @ClassName CoonectionFactoryUtil
* @Description 获取连接
* @Author wangcc
* @Date 2022/4/8 12:07
* @Version 1.0
**/
public class CoonectionFactoryUtil {
public volatile static Connection coonection = null;
public volatile static Connection newCoonection = null;
public static final String url = $.get("tlcad.platform.db.manager.sourceDburl");
public static final String userName = $.get("tlcad.platform.db.manager.username");
public static final String password = $.get("tlcad.platform.db.manager.password");
private CoonectionFactoryUtil(){}
/**
* 从配置文件中读取源db信息
* @return
*/
public static Connection getCoonection(){
if(coonection == null){
try {
synchronized (Connection.class){
//无需自动创建
coonection = DriverManager.getConnection(url, userName, password);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return coonection;
}
/**
* 动态传入db
* @param dburl
* @param dbusername
* @param dbpwd
* @return
*/
public static Connection getCoonection(String dburl,String dbusername,String dbpwd){
return coonection;
}
public static Connection getCoonection(String dbName,String sourceDbname){
try {
synchronized (Connection.class){
String url1 = url.replace(sourceDbname, dbName);
newCoonection = DriverManager.getConnection(url1, userName, password);
}
} catch (SQLException e) {
e.printStackTrace();
}
return newCoonection;
}
}
2:根据创建的数据库连接生成一个新的数据库
package com.tlcad.platform.dbmanager.api;
import com.tlcad.platform.dbmanager.api.model.Database;
import java.util.List;
/**
* 数据连接
*/
public interface MgDbConnect {
/**
* 添加一个数据库
* @param database
* @return
*/
public MgDatabase add(Database database);
/**
* 删除一个数据库
* @param name
* @return
*/
public boolean del(String name);
/**
* 创建一个新的数据库
* @param dbName
* @return
*/
public MgDatabase created(String dbName);
/**
* 获取当前数据库连接
* @return
*/
public String getDataBaseName();
}
package com.tlcad.platform.dbmanager.api.impl.mysql;
import com.tlcad.platform.dbmanager.api.MgDatabase;
import com.tlcad.platform.dbmanager.api.MgDbConnect;
import com.tlcad.platform.dbmanager.api.model.Database;
import com.tlcad.platform.dbmanager.api.util.CoonectionFactoryUtil;
import org.springframework.stereotype.Service;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
@Service
public class DbConnectMysql implements MgDbConnect {
private Connection conn = null;
@Override
public List<String> getDbNames() {
conn = CoonectionFactoryUtil.getCoonection();
List<String> dbs = new ArrayList<>();
try {
DatabaseMetaData metaData = conn.getMetaData();
ResultSet rs = metaData.getCatalogs();
while (rs.next()) {
dbs.add(rs.getString("TABLE_CAT"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return dbs;
}
@Override
public MgDatabase add(Database database) {
return null;
}
@Override
public boolean del(String name) {
Statement smt = null;
try {
smt = CoonectionFactoryUtil.getCoonection(name, this.getDataBaseName()).createStatement();
smt.executeUpdate("DROP TABLE IF EXISTS " + name + ";");
} catch (SQLException e) {
e.printStackTrace();
}
return true;
}
@Override
public MgDatabase created(String dbName) {
try {
Statement stat = conn.createStatement();
//创建数据库dbName
stat.executeUpdate("create database " + dbName);
//先获取现有连接数据库的所有表
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
@Override
public String getDataBaseName() {
conn = CoonectionFactoryUtil.getCoonection();
try {
return conn.getCatalog();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
3:复制数据库
package com.tlcad.platform.dbmanager.api;
import com.tlcad.platform.dbmanager.api.model.Database;
import com.tlcad.platform.dbmanager.api.model.Table;
import com.tlcad.platform.dbmanager.api.model.Tableddl;
import java.util.List;
/**
* 数据库操作基类
*/
public interface MgDatabase {
/**
* 获取所有表信息
*
* @return
*/
public List<Table> getTablesInfo();
/**
* 添加一个表
* @param table
* @return
*/
public MgTable add(Table table);
/**
* 批量添加表
* @param table
* @return
*/
public boolean add(List<Table> table);
/**
* 获取建表语句
* @param tableName
* @return
*/
public Tableddl getTableddl(String tableName);
/**
* 将一个老库的所有表和数据复制到另一个新的数据库
* @param targetDbName 目标数据库,新库
* @param sourceDbName 来源数据库,老库
* @return
*/
public boolean copyDataBase(String targetDbName,String sourceDbName);
}
package com.tlcad.platform.dbmanager.api.impl.mysql;
import com.tlcad.platform.dbmanager.api.MgDatabase;
import com.tlcad.platform.dbmanager.api.MgDbConnect;
import com.tlcad.platform.dbmanager.api.MgTable;
import com.tlcad.platform.dbmanager.api.model.Table;
import com.tlcad.platform.dbmanager.api.model.Tableddl;
import com.tlcad.platform.dbmanager.api.util.CoonectionFactoryUtil;
import com.tlcad.platform.dbmanager.api.util.SqlFormatUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
@Service
public class DatabaseMysql implements MgDatabase {
private Connection con;
private String dbName;
@Autowired
private MgDbConnect mgDbConnect;
@Override
public List<String> getTables() {
Statement stat = null;
List<String> tables = new ArrayList<>();
try {
con = CoonectionFactoryUtil.getCoonection();
stat = con.createStatement();
//查询数据
ResultSet result = stat.executeQuery(" select table_name tableName \n" +
"from information_schema.tables\n" +
"where table_schema = (select database())\n" +
"order by create_time desc");
while (result.next())
{
tables.add(result.getString("tableName"));
}
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
return tables;
}
@Override
public List<Table> getTablesInfo() {
Statement stat = null;
List<Table> tables = new ArrayList<>();
try {
con = CoonectionFactoryUtil.getCoonection();
stat = con.createStatement();
//查询数据
ResultSet result = stat.executeQuery(" select table_name tableName, \n" +
" engine, \n" +
" table_comment tableComment, \n" +
" table_collation tableCollation, \n" +
" create_time createTime \n" +
"from information_schema.tables\n" +
"where table_schema = (select database())\n" +
"order by create_time desc");
while (result.next())
{
Table table = new Table();
table.setTableName(result.getString("tableName"));
table.setCreateTime(result.getString("createTime"));
table.setEngine(result.getString("engine"));
table.setTableCollation(result.getString("tableCollation"));
table.setTableComment(result.getString("tableComment"));
tables.add(table);
}
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
return tables;
}
@Override
public Tableddl getTableddl(String tableName) {
Statement stat = null;
Tableddl tableddl = new Tableddl();
try {
con = CoonectionFactoryUtil.getCoonection();
stat = con.createStatement();
//查询建表语句
ResultSet result = stat.executeQuery(" SHOW CREATE TABLE " + tableName);
while (result.next())
{
tableddl.setTableName(result.getString("Table"));
tableddl.setDdl(result.getString("Create Table"));
}
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
return tableddl;
}
@Override
public boolean copyDataBase(String targetDbName, String sourceDbName) {
//获取老数据库中所有表
List<Table> tables = this.getTablesInfo();
//创建一个新的数据库
mgDbConnect.created(targetDbName);
//打开创建的数据库
try {
Statement stat = CoonectionFactoryUtil.getCoonection(targetDbName,sourceDbName).createStatement();
if(tables.size() > 0){
for (Table table : tables) {
//按照表分别获取建表语句
Tableddl tableddl = this.getTableddl(table.getTableName());
//创建表
stat.executeUpdate(tableddl.getDdl());
//创建表数据
stat.executeUpdate(SqlFormatUtil.getInsertSql(tableddl.getTableName(), sourceDbName));
}
}
return true;
} catch (SQLException e) {
e.printStackTrace();
}finally {
mgDbConnect.del(targetDbName);
}
return false;
}
}
package com.tlcad.platform.dbmanager.api.model;
import java.util.List;
import java.util.Map;
/**
* @ClassName Tableddl
* @Description TODO
* @Author wangcc
* @Date 2022/4/8 14:18
* @Version 1.0
**/
public class Tableddl {
private String tableName;
private String ddl;
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getDdl() {
return ddl;
}
public void setDdl(String ddl) {
this.ddl = ddl;
}
}
package com.tlcad.platform.dbmanager.api.model;
public class Table {
//表名称
private String tableName;
//默认殷勤
private String engine;
//表备注
private String tableComment;
//字符集
private String tableCollation;
//创建时间
private String createTime;
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getEngine() {
return engine;
}
public void setEngine(String engine) {
this.engine = engine;
}
public String getTableComment() {
return tableComment;
}
public void setTableComment(String tableComment) {
this.tableComment = tableComment;
}
public String getTableCollation() {
return tableCollation;
}
public void setTableCollation(String tableCollation) {
this.tableCollation = tableCollation;
}
public String getCreateTime() {
return createTime;
}
public void setCreateTime(String createTime) {
this.createTime = createTime;
}
}
4:执行方法
@Autowired
private MgDatabase mgDatabase;
@Autowired
private MgDbConnect mgDbConnect;
public void test() {
dataBaseName = "ykxt_123456";
boolean b = mgDatabase.copyDataBase(dataBaseName,mgDbConnect.getDataBaseName());
}
package com.tlcad.platform.dbmanager.api.util;
/**
* @ClassName SqlFormatUtil
* @Description 格式化sql
* @Author wangcc
* @Date 2022/4/8 14:31
* @Version 1.0
**/
public class SqlFormatUtil {
/**
* 根据表名格式化插入表数据sql
* @param tableName
* @return
*/
public static String getInsertSql(String tableName,String sourceDb){
String sql = "insert into "+tableName+" select * from "+sourceDb+"."+tableName+";";
return sql;
}
}