java 项目启动创建数据库执行初始化脚本

2023-10-10 00:56| 来源: 网络整理| 查看: 265

在小项目中，给用户部署环境比较烦人，数据库创建、导入能省则省。设计初衷：

　　1.用户安装数据库后系统自动创建数据库。

　　2.数据库自动导入。

　　3.数据库创建完成后，数据库连接池保持可用。

A.先来熟悉一下springboot 中datasource.schema配置:

# 数据库配置 #spring.datasource.driver-class-name=org.sqlite.JDBC ##jdbc:mysql://ip:port/version_manage?characterEncoding=utf8&useSSL=false #spring.datasource.url=jdbc:sqlite:Server/sqlite/sql.db #spring.datasource.username= #spring.datasource.password= #spring.datasource.initialization-mode= always ##spring.datasource.schema= classpath:sql/schema.sql #启动执行脚本

这个配置是项目启动后自动执行schema.sql ，增量数据变动比较好，不适合项目初始化用。

B. springboot中自己编写实现过程，具体逻辑实现为：

　　加载Datasource之前检查数据库DB1是否可用。

　　　　如果DB1不存在则判断数据库连接下是否有mysql数据库

　　　　　　存在mysql库就连接mysql,用mysql库新建系统需要的数据库DB1

　　　　　　　　创建完数据库DB1后执行init.sql ，初始化数据库中的表。

　　　　　　　　　　全部完成后初始化DataSource bean

　　DB1的配置文件jdbc.properties ：有需求的话可以添加一个操作界面，项目启动后让用户自己页面操作后写入文件

dbtype=mysql ip=127.0.0.1 port=3306 dbname=DB1 username=root password=123456 init=init.sql

@Configuration主要业务逻辑,此处只处理简单的mysql，sqlite两种数据库 @Configuration public class MysqlConfig { static final Logger logger = LoggerFactory.getLogger(MysqlConfig.class); //数据库正常标志 public static boolean dbConnectFlag = false ; public static JSONObject mysqlPro ; static { String basePath = System.getProperty("user.dir") ; String dirName = basePath + "/conf/jdbc.properties"; mysqlPro = PropertiesUtils.parse(dirName); } @Bean public DataSource createDatesource() { if(mysqlPro.containsKey("dbtype") ){ if("mysql".equalsIgnoreCase(mysqlPro.get("dbtype").toString())){ if(!mysqlPro.containsKey("ip") || !mysqlPro.containsKey("dbname") ||!mysqlPro.containsKey("username") ||!mysqlPro.containsKey("password") ){ return null ; } IDBcontrolled mc = createIDBcontrolled(); MysqlRstData mysqlRstData = mc.testConn(mysqlPro) ;　　　　　　　　　　//数据库存在，不存在dbname的库 if( mysqlRstData.getCode() .equals(MysqlRstData.ResultCode.NO_DB)){ if(!mysqlPro.containsKey("init")){ return null ; } mysqlRstData =mc.createDB(mysqlPro); if( mysqlRstData.getCode() .equals(MysqlRstData.ResultCode.SUCCESS) ){ mysqlRstData = mc.importDB(mysqlPro); } } if( !mysqlRstData.getCode() .equals(MysqlRstData.ResultCode.SUCCESS)){ return null ; } BasicDataSource dataSource = new BasicDataSource(); dataSource.setUrl( String.format(MysqlDBUtil.baseurl,mysqlPro.getString("ip"), mysqlPro.getString("port"),mysqlPro.getString("dbname")) ); dataSource.setDriverClassName(MysqlDBUtil.driver); dataSource.setUsername(mysqlPro.getString("username") ); dataSource.setPassword(mysqlPro.getString("password") ); dataSource.setRemoveAbandoned(false); dataSource.setInitialSize(Utils.getInt(mysqlPro.getString("initialSize"), 5));//初始化的连接数 dataSource.setMaxActive(Utils.getInt(mysqlPro.getString("maxActive"), 10));//最大连接数量 dataSource.setMaxIdle(Utils.getInt(mysqlPro.getString("maxIdle"), 10));//最大空闲数 dataSource.setMinIdle(Utils.getInt(mysqlPro.getString("minIdle"), 5));//最小空闲 dataSource.setMaxWait(Utils.getInt(mysqlPro.getString("maxWait"), 3000)); dataSource.setRemoveAbandoned(Utils.getBoolean(mysqlPro.getString("removeAbandoned"))); dataSource.setRemoveAbandonedTimeout(Utils.getInt(mysqlPro.getString("removeAbandonedTimeout"), 180)); dataSource.setValidationQuery(mysqlPro.getString("validationQuery")); dataSource.setTestOnBorrow(Utils.getBoolean(mysqlPro.getString("testOnBorrow"), true)); dataSource.setLogAbandoned(Utils.getBoolean(mysqlPro.getString("logAbandoned"), true)); this.dbConnectFlag = true ; return dataSource; } } return null ; } 　　　//识别数据库，各数据库特殊类型字段转换函数不一样，返回各数据库特殊字段处理类！　 @Bean("IDbColumnConvert") public IDbColumnConvert createIDbColumnConvert(){ if(mysqlPro.containsKey("dbtype") ) { if ("mysql".equalsIgnoreCase(mysqlPro.get("dbtype").toString())) { return new MysqlDbColumnConvert(); } else { return null ; } }else{ return null ; } } 　　//不同数据库不同的实现代码 public IDBcontrolled createIDBcontrolled(){ if(mysqlPro.containsKey("dbtype") ) { if ("mysql".equalsIgnoreCase(mysqlPro.get("dbtype").toString())) { return new MysqlController(); } else { return null ; } }else{ return null ; } } }

　接口就不写，直接实现代码

public class MysqlController implements IDBcontrolled{ /** * 测试db是否可用 * @param database * @return */ public MysqlRstData testConn(JSONObject database ) { String ip = database.getString("ip"); String port = database.getString("port"); String dbname = database.getString("dbname"); String username = database.getString("username"); String password = database.getString("password"); MysqlRstData mysqlRstData = MysqlDBUtil.testMysqlConn(ip,port,dbname,username,password); return mysqlRstData ; } /** * 根据参数新建数据库，并脚本导入 * @param database * @return */ public MysqlRstData createConnect(JSONObject database ) { String ip = database.getString("ip"); String port = database.getString("port"); String dbname = database.getString("dbname"); String username = database.getString("username"); String password = database.getString("password"); MysqlRstData mysqlRstData = MysqlDBUtil.testMysqlConn(ip,port,dbname,username,password); if(mysqlRstData.getCode() .equals(MysqlRstData.ResultCode.NO_DB)){ mysqlRstData = createDB(database); if( mysqlRstData.getCode() .equals(MysqlRstData.ResultCode.SUCCESS) ){ mysqlRstData = importDB(database); } } return mysqlRstData ; } /** * 新建数据库 * @param database * @return */ public MysqlRstData createDB(JSONObject database) { String ip = database.getString("ip"); String port = database.getString("port"); String dbname = database.getString("dbname"); String username = database.getString("username"); String password = database.getString("password"); MysqlRstData mysqlRstData = MysqlDBUtil.creatDB(ip,port,dbname,username,password); return mysqlRstData ; } /** * 导入数据 * @param database * @return */ public MysqlRstData importDB(JSONObject database) { String ip = database.getString("ip"); String port = database.getString("port"); String dbname = database.getString("dbname"); String username = database.getString("username"); String password = database.getString("password"); String init = database.getString("init"); MysqlRstData mysqlRstData = MysqlDBUtil.importDB(ip,port,dbname,username,password ,init); return mysqlRstData ; } }

public class MysqlDBUtil { static final Logger logger = LoggerFactory.getLogger(MysqlDBUtil.class); static String mysqlDriver = "com.mysql.jdbc.Driver"; public static String baseurl = "jdbc:mysql://%s:%s/%s?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true"; public static String driver ="com.mysql.jdbc.Driver"; /** * 测试数据库是否可以连接 * @param ip * @param port * @param dbname * @param username * @param password * @return */ public static MysqlRstData testMysqlConn(String ip, String port , String dbname, String username, String password ) { String url = String.format(baseurl,ip,port,dbname); Connection conn = getMysqlConn( url, username, password ); if (conn != null) { try { conn.close(); } catch (SQLException e) { logger.error(ExceptionUtil.getMessage((Exception) e) ); } return new MysqlRstData(MysqlRstData.ResultCode.SUCCESS, "") ; }else { String newUrl = String.format(baseurl,ip,port,"mysql"); Connection newConn = getMysqlConn( newUrl, username, password ); if (newConn != null) { try { newConn.close(); } catch (SQLException e) { logger.error(ExceptionUtil.getMessage((Exception) e) ); } return new MysqlRstData(MysqlRstData.ResultCode.NO_DB, "未找到名称为【" +dbname+"】的数据库"); }else { return new MysqlRstData(MysqlRstData.ResultCode.NO_CONNECT, "后台无法连接到ip为：【"+ip+"】，port为：【"+port+"】的数据库"); } } } /** * 获取数据库连接，调用完成后需要关闭 * @param ip * @param port * @param dbname * @param username * @param password * @return */ public static Connection getMysqlConn(String ip,String port , String dbname,String username,String password ) { String url = String.format(baseurl,ip,port,dbname); Connection conn = getMysqlConn( url, username, password ); return conn; } /** * 获取数据库连接，调用完成后需要关闭 * @param url * @param username * @param password * @return */ public static Connection getMysqlConn(String url,String username,String password ) { Connection conn = null; Connection newConn = null; try { Class.forName(mysqlDriver); } catch (ClassNotFoundException e) { logger.error(ExceptionUtil.getMessage((Exception) e) ); return null; } try { conn = DriverManager.getConnection(url, username, password); if (conn != null) { return conn ; }else{ return null; } } catch (SQLException e ) { if (e.getLocalizedMessage().contains("Unknown database")) { logger.error(ExceptionUtil.getMessage((Exception) e) ); //连接成功 : 数据库不存在，将自动创建 return null ; } else { logger.error(ExceptionUtil.getMessage((Exception) e) ); //连接错误 return null ; } } } /** * 登录mysql后，建立目标数据库 * @param ip * @param port * @param dbname * @param username * @param password * @return */ public static MysqlRstData creatDB(String ip, String port , String dbname, String username, String password) { String databaseSql = "create database " + dbname + " default character set utf8 COLLATE utf8_general_ci "; Connection conn = null; MysqlRstData mysqlRstData = new MysqlRstData( ); try { conn = getMysqlConn(ip, port, "mysql", username, password); Statement smt = conn.createStatement(); smt.executeUpdate(databaseSql); return mysqlRstData ; }catch (SQLException e){ logger.error(ExceptionUtil.getMessage((Exception) e) ); mysqlRstData.setCode(MysqlRstData.ResultCode.CREATE_ERROR); mysqlRstData.setMsg(e.toString()); return mysqlRstData ; }finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { logger.error(ExceptionUtil.getMessage((Exception) e) ); e.printStackTrace(); } } } } /** * 将mysql 配置文件写到jdbc.properties中 * @param ip * @param port * @param dbname * @param username * @param password * @return */ // public static void writeJDBCProperties(String ip, String port , String dbname, String username, String password) { // String url = String.format(baseurl,ip,port,dbname) ; // String basePath = System.getProperty("user.dir") ; // String dirName = basePath + "/conf/"; // FileUtil.pkgDirName(dirName); // StringBuffer content = new StringBuffer(); // content.append("jdbc.driver=").append("com.mysql.jdbc.Driver"); // content.append("\r\n"); // content.append("jdbc.url=" ).append(url); // content.append("\r\n"); // content.append("jdbc.user=").append(username); // content.append("\r\n"); // content.append("jdbc.password=").append(password); // content.append("\r\n"); // FileUtil.fileWriter(dirName , "jdbc.properties" , content ); // // } /** * 获取数据库脚本文件 * @return * @throws IOException */ public static String[] getDbInitScript(String init) throws IOException { // File script = ResourceUtils.getFile("classpath:sql/init.sql"); String basePath = System.getProperty("user.dir") ; String fileName = basePath +"/" + "/conf/" + init ; File script = new File(fileName); List LS = FileUtils.readLines(script, "utf-8"); List SQLS = new ArrayList(); StringBuilder SQL = new StringBuilder(); boolean ignoreTerms = false; for (Object L : LS) { String L2 = L.toString().trim(); // NOTE double 字段也不支持 boolean H2Unsupported = L2.startsWith("fulltext"); // Ignore comments and line of blank if (StringUtils.isEmpty(L2) || L2.startsWith("--") || H2Unsupported) { continue; } if (L2.startsWith("/*") || L2.endsWith("*/")) { ignoreTerms = L2.startsWith("/*"); continue; } else if (ignoreTerms) { continue; } SQL.append(L2); if (L2.endsWith(";")) { // SQL ends SQLS.add(SQL.toString().replace(",\n)Engine=", "\n)Engine=")); System.out.println(SQL); SQL = new StringBuilder(); } else { SQL.append('\n'); } } return SQLS.toArray(new String[0]); } /** * 导入数据库 * @param ip * @param port * @param dbname * @param username * @param password * @param init * @return */ public static MysqlRstData importDB(String ip, String port , String dbname, String username, String password ,String init) { //写配置文件 Connection conn = null ; Statement stmt = null; MysqlRstData mysqlRstData = new MysqlRstData(); try { conn = getMysqlConn(ip, port, dbname, username, password); stmt = conn.createStatement(); for (String sql : getDbInitScript(init)) { try { logger.info(sql); stmt.execute(sql); }catch (Exception e){ logger.error( ExceptionUtil.getMessage( e )); } } }catch (SQLException e){ logger.error( ExceptionUtil.getMessage( e )); mysqlRstData.setCode(MysqlRstData.ResultCode.IMPORT_ERROR); mysqlRstData.setMsg(e.toString()); return mysqlRstData; } catch (IOException e) { logger.error( ExceptionUtil.getMessage( e )); mysqlRstData.setCode(MysqlRstData.ResultCode.INITFILE_ERROR); mysqlRstData.setMsg(e.toString()); return mysqlRstData; }finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } return mysqlRstData; } }

读取Properties工具代码，其它简单工具代码自己整理

/** * 读取Properties文件类容 */ public class PropertiesUtils{ public static JSONObject parse(String filePath){ File file = new File(filePath); JSONObject jsonObject = new JSONObject(); if (file.exists()) { List LS = null; try { LS = FileUtils.readLines(file, "utf-8"); List SQLS = new ArrayList(); StringBuilder SQL = new StringBuilder(); boolean ignoreTerms = false; for (Object L : LS) { String param = L.toString().trim(); if(StringUtils.isEmpty(param)){ continue; } String key =param.substring(0,param .indexOf("=")) ; String value =param.substring(param .indexOf("=")+ 1) ; jsonObject.put(key ,value); } } catch ( Exception e) { e.printStackTrace(); } } return jsonObject; } }

pom.xml

org.springframework.boot spring-boot-starter-web org.springframework.boot spring-boot-starter-websocket commons-lang commons-lang 2.6 org.springframework.boot spring-boot-starter-jdbc commons-dbcp commons-dbcp 1.4 org.apache.commons commons-pool2 RELEASE mysql mysql-connector-java 5.1.29 log4j log4j 1.2.16 commons-io commons-io 2.4 com.alibaba fastjson 1.2.37

【本文地址】

公司简介

联系我们

java 项目启动创建数据库 执行初始化脚本

java 项目启动创建数据库执行初始化脚本