在小项目中，给用户部署环境比较烦人， 数据库创建、导入能省则省。 设计初衷：
1.用户安装数据库后系统自动创建数据库。
2.数据库自动导入。
3.数据库创建完成后，数据库连接池保持可用。
A.先来熟悉一下springboot 中datasource.schema配置:
# 数据库配置 #spring.datasource.driver-class-name=org.sqlite.JDBC ##jdbc:mysql://172.16.102.85:3306/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两种数据库
package cn.sigutech.DataBase.config;
import cn.sigutech.DataBase.Mysql.*;
import cn.sigutech.DataBase.MysqlRstData;
import cn.sigutech.DataBase.Utils.PropertiesUtils;
import cn.sigutech.utils.Utils;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.dbcp.BasicDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@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 ;
}
}
}
接口就不写， 直接实现代码
package cn.sigutech.DataBase.Mysql; import cn.sigutech.DataBase.MysqlRstData; import com.alibaba.fastjson.JSONObject; 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 ; } }
package cn.sigutech.DataBase.Mysql; import cn.sigutech.DataBase.MysqlRstData; import cn.sigutech.utils.ExceptionUtil; import cn.sigutech.utils.FileUtil; import org.apache.commons.io.FileUtils; import org.apache.commons.lang.StringUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.File; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; 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<String> 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工具代码，其它简单工具代码自己整理
package cn.sigutech.DataBase.Utils; import com.alibaba.fastjson.JSONObject; import org.apache.commons.dbcp.BasicDataSource; import org.apache.commons.io.FileUtils; import org.springframework.util.StringUtils; import java.io.File; import java.io.IOException; import java.util.ArrayList; import java.util.List; import java.util.Map; /** * 读取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<String> 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
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- websocket -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-websocket</artifactId>
</dependency>
<!--通用操作组件开始-->
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>
<!--能用操作组件结束-->
<!--数据库开始-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
<version>RELEASE</version>
</dependency>
<!-- Mysql 驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.29</version>
</dependency>
<!-- 日志组件开始 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.16</version>
</dependency>
<!-- 日志组件结束 -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<!-- alj JSON -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.37</version>
</dependency>
<!-- alj JSON END -->