多环境数据库结构与数据同步,java实现,编写sql文件形式_＋二_java 数据库结构同步

大大的周 02-07 2161

多环境数据库结构与数据同步,编写sql文件形式

总监：咳咳咳…，小王啊，最近有个需求啊，咱们这测试环境的数据库结构进行了更改，开发环境环境怎么办呢？这开新环境还会有些原始数据和建表，怎么办呢? 线上怎么搞呢？

我：咱们可以用Navicat呀，直接同步过去就好了呀！

总监：我觉得这样不好，线上环境的数据库我是不打算直接连接的。

我：😳那…

总监：你想想办法，看看怎么搞，给你一天时间，搞个demo给我。

我：😅额…

好吧，既然任务已经下发了，就只能想想怎么搞定它了。

开始发散思维…

没想法，百度吧！

好像没啥针对的解决方案啊！那我只能自己撸代码？

继续发散思维…

还是不行，那还是踏踏实实看看怎么实现吧！

先规划一下怎么实现这个功能。既然是要同步数据库中的数据，那么一定要在程序启动的时候去执行这一段代码。今后要是有变更的话，是要支持版本不断的累加的，也就是支持后续的版本号。要区分不同的环境，也就是需要有一个环境开关，控制不同的环境是否开启版本迭代。该sql文件中要是有不成功的sql，要终止sql执行并进行回滚，那么需要开启事务。

好啦，思路有啦！那就想一个实现代码的步骤。因为是jar包，无法进行直接读取我们写的.sql文件。sql文件项目中是写到resources/sql文件夹的。那么需要进行解压到当前工作目录下的/temporary文件夹下面，用于读取编写的.sql文件。获取到.sql文件列表后，判定当前数据库中是否存在约定的_version表。没有则创建，并根据版本号挨个执行.sql文件。有则查看数据库中当前执行到的.sql版本号，并执行后续版本的.sql文件。读取后删除解压的文件，该任务完成。

上代码： import cn.hutool.core.util.ZipUtil; import cn.hutool.db.Entity; import cn.hutool.db.ds.simple.SimpleDataSource; import cn.hutool.db.handler.EntityListHandler; import cn.hutool.db.sql.SqlExecutor; import lombok.extern.slf4j.Slf4j; import lombok.var; import org.springframework.beans.factory.InitializingBean; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Component; import javax.management.RuntimeErrorException; import javax.sql.DataSource; import java.io.*; import java.nio.charset.StandardCharsets; import java.sql.*; import java.util.*; import java.util.regex.Pattern; import static java.lang.Integer.parseInt; @Component("loadingSql") @Slf4j public class SynchronizingDbVersion implements InitializingBean { /** * 是否开启同步数据库的开关 * 这个位置是从nacos上注入的属性，可自行填写 */ private Boolean autoIteration; /** * 用户名 * 这个位置是从nacos上注入的属性，可自行填写 */ private String username; /** * 密码 * 这个位置是从nacos上注入的属性，可自行填写 */ private String password; /** * 注入命名空间 * 这个位置是从nacos上注入的属性，可自行填写 * dev local test等 */ private String nameSpace; /** * 定义版本号的正则匹配形式 */ private static Pattern pattern = Pattern.compile("^[1-9]\\d?(\\.(0|[1-9]\\d?)){2}$"); /** * 这个位置就是从配置文件中装配的配置类，在该代码中只是获取了数据库地址 * 例如：jdbc:postgresql://数据库ip:端口/数据库?currentSchema=模式名称&stringtype=unspecified&allowMultiQueries=true */ @Autowired HahaConfigDbUrlByDruid hahaConfigDbUrlByDruid; /** * 和上面一样，都是地址，只是有的环境用了druid，所以进行了分别获取 */ @Autowired HahaConfigDbUrl hahaConfigDbUrl; /** * 同步数据库版本 */ @Override public void afterPropertiesSet() { String unpackPath = null; Connection connection = null; if (autoIteration == null || !autoIteration) { log.info("<-------------未开启同步数据库版本的开关，不进行数据库的同步----------->"); return; } //数据库地址 String url; //本地环境的命名空间 String local = "local"; //兼容开发环境 if(local.equals(nameSpace)){ url = hahaConfigDbUrl.getUrl(); }else{ url = hahaConfigDbUrlByDruid.getUrl(); } try { log.info("<--------------------开始同步数据库版本信息---------------------->"); //获取当前的工作目录 String proFilePath = System.getProperty("user.dir"); //jar包所在的绝对路径 String absolutePath = this.getAbsolutePath(); //生成临时文件的路径 unpackPath = proFilePath + "temporary"; //解压文件 log.info("<---------------------------解压文件---------------------------->"); ZipUtil.unzip(absolutePath, unpackPath); //sql文件所在的路径 File file = new File(unpackPath + "/BOOT-INF/classes/sql"); File[] files = file.listFiles(); ArrayList<String> versionNumbers = new ArrayList<>(); HashMap<String, File> versionNumberFileMap = new HashMap<>(); if (files == null || files.length == 0) { log.info("<-------------------无版本更新,删除解压文件--------------------->"); FileUtilsDelete.delete(unpackPath); log.info("<-----------------------删除解压文件成功----------------------->"); return; } for (File fileItem : files) { //2.获取所有的文件名并进行排序，获取当前服务存储的数据库版本号，并挨个执行版本的sql文件 String name = getFileNameWithoutSuffix(fileItem.getName()); versionNumbers.add(name); versionNumberFileMap.put(name, fileItem); } //对版本号的列表进行排序 this.sortVersionArray(versionNumbers); //创建jdbc,这里不能用druid的连接，druid会拦截建表语句，以及.sql文件中的创建表/删除表的语句 DataSource ds = this.getDataSource(url); connection = ds.getConnection(); //判断有无_version表，如果没有，则进行创建 this.createTable(connection); //获取数据库当前版本 String versionDb = this.getVersionDb(connection); //判断并执行sql文件 this.assessAndExecute(versionDb,versionNumberFileMap,versionNumbers,connection); log.info("<-----------------------同步完成-------------------->"); FileUtilsDelete.delete(unpackPath); log.info("<-------------------删除临时解压文件成功------------->"); } catch (Exception e) { if (unpackPath != null) { FileUtilsDelete.delete(unpackPath); } log.error(e.getMessage()); throw new RuntimeErrorException(new Error("数据库同步失败")); }finally { if(connection != null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } /** * 判断并执行sql文件 */ private void assessAndExecute(String versionDb, HashMap<String, File> versionNumberFileMap, ArrayList<String> versionNumbers, Connection connection) { if (versionDb == null) { log.info("未获取到数据库中版本数据，依次执行sql文件"); //sql文件从头执行到尾 for (String versionNumber : versionNumbers ) { //版本号符合正则匹配的才进行执行 if(pattern.matcher(versionNumber).matches()){ log.info("执行{}版本文件",versionNumber); executeSqlAndUpdateVersion(versionNumberFileMap, versionNumber,connection); } } } else { log.info("获取到数据库中版本数据，依次执行sql文件"); //4.循环文件名称列表，直到数据库中存在的版本后进行更新 //当前数据库版本号 for (String versionNumber : versionNumbers ) { //版本号符合正则匹配的才进行执行 if (rule(versionNumber, versionDb) == 1) { if(pattern.matcher(versionNumber).matches()){ log.info("执行{}版本文件",versionNumber); executeSqlAndUpdateVersion(versionNumberFileMap, versionNumber,connection); } } } } } /** * 获取数据库中版本信息 * @param connection jdbc连接 */ private String getVersionDb(Connection connection) throws SQLException { List<Entity> query = SqlExecutor.query(connection, "select * from _version order by version desc limit 1", new EntityListHandler()); if(query !=null && query.size()>0){ return query.get(0).getStr("version"); }else { return null; } } /** * 获取dataSource */ private DataSource getDataSource(String url) { log.info("创建数据库连接"); return new SimpleDataSource(url,username,password); } /** * 获取该项目运行jar包的绝对路径 * * @return jar包的绝对路径 */ private String getAbsolutePath() { //获取当前类所在的绝对路径 file:/Users/Desktop/Server/service/admin/target/admin-1.0.0.jar!/BOOT-INF/lib/mybatis-1.0.0.jar!/ String locationPath = this.getClass().getProtectionDomain().getCodeSource().getLocation().getPath(); //获取服务jar包的绝对路径 String[] split = locationPath.split("!"); String str1 = split[0]; //jar包所在的绝对路径，去除file: return str1.substring(5); } /** * 创建表的方法 * * @param connection 数据库连接 */ private void createTable(Connection connection) throws SQLException { Statement stmt = connection.createStatement() ; log.info("查看是否有_version表"); String createTableSql = "CREATE TABLE \"_version\" (\"version\" VARCHAR (30) COLLATE \"pg_catalog\".\"default\" NOT NULL,\"created_at\" timestamptz (3) DEFAULT CURRENT_TIMESTAMP (3),CONSTRAINT \"_version_pkey\" PRIMARY KEY (\"version\"));"; try { stmt.execute("select version from _version limit 1"); }catch (Exception e){ log.info("创建_version表"); stmt.execute(createTableSql); } stmt.close(); } /** * 对版本号进行排序 */ private void sortVersionArray(ArrayList<String> versionArray) { versionArray.sort(this::rule); } /** * 比较版本号大小 * * @param version1 版本1 * @param version2 版本2 * @return 版本3 */ private Integer rule(String version1, String version2) { //去除'.'，将剩下的数字转换为数组 var arr1 = version1.split("\\."); var arr2 = version2.split("\\."); //取出两个数组中的最小程度 var minLen = Math.min(arr1.length, arr2.length); //最大长度 var maxLen = Math.max(arr1.length, arr2.length); //以最短的数组为基础进行遍历 for (int i = 0; i < minLen; i++) { //这里需要转换后才进行比较，否则会出现'10'<'7'的情况 if (parseInt(arr1[i]) > parseInt(arr2[i])) { //返回一个大于0的数，表示前者的index比后者的index大 return 1; } else if (parseInt(arr1[i]) < parseInt(arr2[i])) { //返回一个小于0的数，表示前者的index比后者的index小 return -1; } //因为不只进行一次计较，所以这里不对相等的两个数进行处理，否则有可能第一次比较就返回，不符合要求 //这个是为了区分'4.8'和'4.8.0'的情况 //在前面的比较都相同的情况下，则比较长度 //位数多的index大 if (i + 1 == minLen) { if (arr1.length > arr2.length) { return 1; } else { return -1; } } } return 0; } /** * 执行sql文件中的sql语句，并更新数据库的版本 */ private void executeSqlAndUpdateVersion(HashMap<String, File> versionNumberFileMap, String versionNumber,Connection connection) { Statement stmt = null; try { //开启事务 log.info("开启事务"); connection.setAutoCommit(false); stmt = connection.createStatement() ; log.info("读取文件"); File oneFile = versionNumberFileMap.get(versionNumber); //读取到的该行sql String sql = readFileByLines(oneFile.getPath()); //执行sql log.info("执行sql文件"); stmt.execute(sql); log.info("版本号为{}添加成功",versionNumber); //更新数据库的版本 log.info("添加数据库版本号为{}",versionNumber); String insertSql = "insert into _version(version) values(?)"; PreparedStatement preparedStatement = connection.prepareStatement(insertSql); preparedStatement.setString(1,versionNumber); preparedStatement.executeUpdate(); log.info("添加数据库版本号为{}成功",versionNumber); //提交事务 connection.commit(); log.info("提交事务"); }catch (Exception e){ log.error(e.getMessage()); try { //回滚事务 connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } throw new RuntimeException("执行sql文件出错"); }finally { try { if(stmt != null){ stmt.close(); } } catch (SQLException e) { e.printStackTrace(); } } } /** * 获取不带后缀的文件名 */ private String getFileNameWithoutSuffix(String fileName) { String fileNameIndexOf = fileName.substring(fileName.lastIndexOf(".")); int num = fileNameIndexOf.length(); return fileName.substring(0, fileName.length() - num); } /** * 以行为单位读取文件，常用于读面向行的格式化文件 */ private String readFileByLines(String filePath) throws Exception { StringBuffer str = new StringBuffer(); BufferedReader reader = null; try { reader = new BufferedReader(new InputStreamReader( new FileInputStream(filePath), StandardCharsets.UTF_8)); String tempString; // 一次读入一行，直到读入null为文件结束 while ((tempString = reader.readLine()) != null) { str = str.append("\n").append(tempString); } reader.close(); } catch (IOException e) { e.printStackTrace(); throw e; } finally { if (reader != null) { try { reader.close(); } catch (IOException ignored) { } } } return str.toString(); } }

该代码在llinux上是没有问题的，在win上的获取当前工作目录需要更改一下,可以自行更改如下代码获取。

String proFilePath = System.getProperty("user.dir");

FileUtilsDelete类

import java.io.File; public class FileUtilsDelete { /** * 删除文件，可以是文件或文件夹 * * @param fileName：要删除的文件名 * @return 删除成功返回true，否则返回false */ public static boolean delete(String fileName) { File file = new File(fileName); if (!file.exists()) { System.out.println("删除文件失败:" + fileName + "不存在！"); return false; } else { if (file.isFile()){ return deleteFile(fileName); } else{ return deleteDirectory(fileName); } } } /** * 删除单个文件 * * @param fileName：要删除的文件的文件名 * @return 单个文件删除成功返回true，否则返回false */ private static boolean deleteFile(String fileName) { File file = new File(fileName); // 如果文件路径所对应的文件存在，并且是一个文件，则直接删除 if (file.exists() && file.isFile()) { if (file.delete()) { System.out.println("删除单个文件" + fileName + "成功！"); return true; } else { System.out.println("删除单个文件" + fileName + "失败！"); return false; } } else { System.out.println("删除单个文件失败：" + fileName + "不存在！"); return false; } } /** * 删除目录及目录下的文件 * * @param dir：要删除的目录的文件路径 * @return 目录删除成功返回true，否则返回false */ private static boolean deleteDirectory(String dir) { // 如果dir不以文件分隔符结尾，自动添加文件分隔符 if (!dir.endsWith(File.separator)){ dir = dir + File.separator; } File dirFile = new File(dir); // 如果dir对应的文件不存在，或者不是一个目录，则退出 if ((!dirFile.exists()) || (!dirFile.isDirectory())) { System.out.println("删除目录失败：" + dir + "不存在！"); return false; } boolean flag = true; // 删除文件夹中的所有文件包括子目录 File[] files = dirFile.listFiles(); for (int i = 0; i < files.length; i++) { // 删除子文件 if (files[i].isFile()) { flag = deleteFile(files[i].getAbsolutePath()); if (!flag){ break; } } // 删除子目录 else if (files[i].isDirectory()) { flag = deleteDirectory(files[i].getAbsolutePath()); if (!flag){ break; } } } if (!flag) { System.out.println("删除目录失败！"); return false; } // 删除当前目录 if (dirFile.delete()) { System.out.println("删除目录" + dir + "成功！"); return true; } else { return false; } } } sql文件位置：

第二天

我：总监，完成了针对当前任务的代码。

总监：嗯！不错，小伙子，就是这个现在有点局限啊，有时间扩展一下，支持多种数据库的同步。等手里没有活了再弄吧。

我:额…

总监：下次有任务再找你，先去忙吧！

我：🏇

总监：回来，点个关注再走。