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

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

　　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("
");
//        content.append("jdbc.url="  ).append(url);
//        content.append("
");
//        content.append("jdbc.user=").append(username);
//        content.append("
");
//        content.append("jdbc.password=").append(password);
//        content.append("
");
//        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(",
)Engine=", "
)Engine="));
                System.out.println(SQL);
                SQL = new StringBuilder();
            } else {
                SQL.append('
');
            }
        }
        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 -->