最近要把生产数据库的部分基础配置数据同步到新的测试环境用于测试使用，但由于生产环境网络和测试环境不通，DBA又不给支持，那么只能自己动手丰衣足食了。原本MySQL安装目录下可以使用mysqldump命令来对mysql数据库进行转储或者备份，它通过连接MySQL数据库，将需要转储的数据查询出来，再将查询结果转换成对应的Insert语句。通过这种方式，可以对单个库，多个库，或者多张表进行转储。但是有一个前提是我们需要知道数据库的安装目录或者能够使用这个命令来操作。但是现在我们没有，于是唯一的办法就是自己来加工了。
在网上发现有人干这事了，但是bug又有点多，于是花了点时间重新改了下，代码很简单，发出来方便以后用，后面可能还得经常干这事儿！
@Slf4j
public class DbDataDumpSqlUtil {
private static final String select="SELECT * FROM";//查询sql
private static final String insert="INSERT INTO";//插入sql
private static String values="VALUES";//values关键字
/**
* 导出数据库表
* @param tables
* @throws SQLException
*/
public static void dumpSql(String driver,String url,String userName,String password,String[] tables,String filePath) {
//创建查询语句Map<tableName,selectSql>
Map<String,String> sqlMap = createSQL(tables);
//执行sql并拼装
Connection conn = null;
Statement statement = null;
List<String> insertList = null ;
try {
//连接数据库
conn = connectSQL(driver, url, userName, password);
statement = conn.createStatement();
insertList = getColumnNameAndColumeValue(statement, sqlMap);
} catch (Exception e) {
log.error("DbDataDumpSqlUtil遇到错误:" ,e);
} finally {
if (statement != null) {
try {
statement.close();
} catch (Exception e) {
log.error("DbDataDumpSqlUtil遇到错误:" ,e);
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
log.error("DbDataDumpSqlUtil遇到错误:" ,e);
}
}
}
if(!CollectionUtils.isEmpty(insertList)) {
createFile(insertList, filePath);//创建文件
}
}
/**
* 拼装查询语句
* @return 返回select集合
*/
public static Map<String,String> createSQL(String[] tables) {
Map<String,String> listSQL=new HashMap(tables.length);
for(String tableName:tables){
StringBuffer sb=new StringBuffer();
sb.append(select).append(" ").append(tableName);
listSQL.put(tableName,sb.toString());
}
return listSQL;
}
/**
* 连接数据库 创建statement对象
* @param driver
* @param url
* @param UserName
* @param Password
*/
private static Connection connectSQL(String driver,String url,String UserName,String Password){
try{
Class.forName(driver).newInstance();
Connection conn = DriverManager.getConnection(url, UserName, Password);
return conn;
}catch(Exception e){
log.error("DbDataDumpSqlUtil遇到错误:" ,e);
}
return null;
}
/**
* 获取列名和列值
* @param statement
* @param sqlMap
* @return
* @throws SQLException
*/
public static List<String> getColumnNameAndColumeValue(Statement statement, Map<String,String> sqlMap) throws Exception {
List<String> insertList = new ArrayList<>();
ResultSet resultSet;
if (sqlMap.size() > 0) {
for (Map.Entry<String, String> entry : sqlMap.entrySet()) {
//执行sql
resultSet = statement.executeQuery(entry.getValue());
try {
getColumnNameAndColumeValue(resultSet, entry.getKey(), insertList);
}catch(Exception e){
log.error("DbDataDumpSqlUtil遇到错误:" ,e);
}finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (Exception e) {
log.error("DbDataDumpSqlUtil遇到错误:" ,e);
}
}
}
//插入空行
insertList.add("");
}
}
return insertList;
}
private static void getColumnNameAndColumeValue(ResultSet resultSet, String tableName, List<String> insertList) throws Exception{
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
while (resultSet.next()) {
StringBuffer columnName = new StringBuffer();
StringBuffer columnValue = new StringBuffer();
for (int i = 1; i <= columnCount; i++) {
String value = null;
if (resultSet.getString(i) != null) {
value = resultSet.getString(i).trim();
}
if (i == 1) {
columnName.append(rsmd.getColumnName(i));
} else {
columnName.append("," + rsmd.getColumnName(i));
columnValue.append(",");
}
if (value != null) {
if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i)
|| Types.LONGVARCHAR == rsmd.getColumnType(i)) {
columnValue.append("'").append(value.replace("'","\\'")).append("'");
} else if (Types.SMALLINT == rsmd.getColumnType(i) || Types.INTEGER == rsmd.getColumnType(i)
|| Types.BIGINT == rsmd.getColumnType(i) || Types.FLOAT == rsmd.getColumnType(i)
|| Types.DOUBLE == rsmd.getColumnType(i) || Types.NUMERIC == rsmd.getColumnType(i)
|| Types.DECIMAL == rsmd.getColumnType(i)) {
columnValue.append(value);
} else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i)
|| Types.TIMESTAMP == rsmd.getColumnType(i)) {
columnValue.append("'").append(value).append("'");
} else {
columnValue.append(value);
}
} else {
columnValue.append("null");
}
}
insertSQL(columnName, columnValue, tableName, insertList);
}
}
/**
* 创建insertsql.txt并导出数据
*/
public static File createFile(List<String> insertList,String filePath) {
File file=new File(filePath);
if(!file.exists()){
try {
file.createNewFile();
} catch (IOException e) {
log.error("创建文件失败:" ,e);
}
}
FileWriter fw=null;
BufferedWriter bw=null;
try {
fw = new FileWriter(file);
bw = new BufferedWriter(fw);
if(insertList.size()>0){
for(int i=0;i<insertList.size();i++){
bw.append(insertList.get(i));
bw.append("\n");
}
}
} catch (IOException e) {
log.error("DbDataDumpSqlUtil遇到错误:" ,e);
}finally{
try {
bw.close();
fw.close();
} catch (IOException e) {
log.error("DbDataDumpSqlUtil遇到错误:" ,e);
}
}
return file;
}
/**
* 拼装insertsql 放到全局list里面
* @param columnName
* @param columnValue
* @param tableName
* @param insertList
* @return
*/
private static List<String> insertSQL(StringBuffer columnName, StringBuffer columnValue, String tableName ,List<String> insertList ) {
StringBuffer insertSQL=new StringBuffer();
insertSQL.append(insert).append(" ")
.append(tableName).append("(").append(columnName.toString())
.append(") ").append(values).append(" (").append(columnValue.toString()).append(");");
insertList.add(insertSQL.toString());
//插入空行
insertList.add("");
return insertList;
}
}
这里我们写一个main方法测试下，测试代码如下 ：
@SpringBootTest
public class DbDataDumpSqlUtilTest {
/**
* 导出数据库表
*/
@Test
public void dumpSqlTest() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://10.222.16.87:8154/chaos_settlement?useUnicode=true&characterEncoding=utf-8&sessionVariables=sql_mode='NO_ENGINE_SUBSTITUTION'";
String userName = "alm_user";
String password = "alm_user";
String filePath="E://insertSQL.sql";//绝对路径 导出数据的文件
String[] tables= {
"base_customer"
, "base_dict"
};
DbDataDumpSqlUtil.dumpSql(driver,url,userName,password,tables,filePath);
}
}
最终导出的文件效果就是这样了：
INSERT INTO st_customer(id,customer_invoice,customer_code,customer_name,taxpayer_code,taxpayer_type,delete_flag,creator,editor,created_time,modified_time,yn_flag) VALUES (64,'test',null,null,'test','test',0,'admin',null,'2021-11-01 09:47:57.0','2021-11-01 09:47:56.0','1');
INSERT INTO st_customer(id,customer_invoice,customer_code,customer_name,taxpayer_code,taxpayer_type,delete_flag,creator,editor,created_time,modified_time,yn_flag) VALUES (65,'111',null,null,'111','111',0,'admin',null,'2021-11-02 17:13:23.0','2021-11-02 17:13:22.0','1');
INSERT INTO st_dict(id,dict_code,dict_name,dict_value,dict_desc,delete_flag,parent_id,is_parent,remark,extend1,extend2,extend3,extend4,yn_flag,creator,editor,created_time,modified_time) VALUES (25,'AdjuStatusEnum','调整状态',null,null,1,'-1','1',null,null,null,null,null,'1','system','admin','2021-09-28 18:32:04.0','2021-10-25 18:06:18.0');
INSERT INTO st_dict(id,dict_code,dict_name,dict_value,dict_desc,delete_flag,parent_id,is_parent,remark,extend1,extend2,extend3,extend4,yn_flag,creator,editor,created_time,modified_time) VALUES (26,'AdjuStatusEnum','调整状态','1','未调整',1,'25','0',null,null,null,null,null,'0','system','admin','2021-09-28 18:33:51.0','2021-10-25 18:06:18.0');
当然了如果你使用连接池或者动态数据源，其实我们只需要拿到一个Connection，即可按照上面的方法调用就好，放在Controller的接口里，就是这样：
public class BizConfigController {
//临时文件目录
@Value("${file.temp_path}")
String bathPath;
@Autowired
SqlSessionFactory sqlSessionFactory;
@PostMapping("/dumpBaseSql")
public void dumpBaseSql(@RequestParam String tableNames, HttpServletResponse response) {
String[] tableNameArray = tableNames.split(",");
Map<String, String> sqlMap = DbDataDumpSqlUtil.createSQL(tableNameArray);
//动态数据源
//DynamicDataSourceContextHolder.push(Constants.DB_SLAVE);
SqlSession sqlSession = sqlSessionFactory.openSession();
Connection conn = sqlSession.getConnection();
List<String> insertList = new ArrayList<>();
try {
Statement statement = conn.createStatement();
insertList = DbDataDumpSqlUtil.getColumnNameAndColumeValue(statement, sqlMap);
} catch (Exception e) {
log.error("Dump数据到Sql出现异常！",e);
} finally {
if (sqlSession != null) {
sqlSession.close();
}
}
String filePath= bathPath + DateUtils.currentDate().getTime()+".sql";
File file = DbDataDumpSqlUtil.createFile(insertList,filePath);
HttpUtils.writeResponse(file, response);
//删除文件
FileUtils.deleteQuietly(file);
}
}
到这里就都搞定了，以后又可以愉快的玩耍了。