JAVA工程启动时自动创建数据库、数据表

一，需求

很多时候，我们会有这样的需求：

1. 系统首次部署时，自动创建数据库、表
2. 执行单元测试时，数据库、表维持初始化状态方便测试。

二，准备

本文对这种需求的实现做了可行性验证。

注意：这边需要配置具有建库建表权限的数据库用户名、密码
数据库配置文件 jdbc.properties

jdbc.driver   = com.mysql.jdbc.Driver
jdbc.password = root
jdbc.url      = jdbc:mysql://127.0.0.1:3306/jdbc-dbutils?useUnicode=true&characterEncoding=utf-8
jdbc.username = root

三，初始化脚本

待执行初始化sql 脚本 sql/init.sql ，可以多个

/*
Navicat MySQL Data Transfer

Source Server         : mysql
Source Server Version : 50559
Source Host           : localhost:3306
Source Database       : dbutils

Target Server Type    : MYSQL
Target Server Version : 50559
File Encoding         : 65001

Date: 2018-11-02 22:48:33
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'name_525', '48');
INSERT INTO `user` VALUES ('2', 'name_367', '33');
INSERT INTO `user` VALUES ('3', 'name_630', '23');
INSERT INTO `user` VALUES ('4', 'name_230', '34');
INSERT INTO `user` VALUES ('5', 'name_750', '50');
INSERT INTO `user` VALUES ('6', 'name_762', '26');
INSERT INTO `user` VALUES ('7', 'name_433', '38');
INSERT INTO `user` VALUES ('8', 'name_742', '44');
INSERT INTO `user` VALUES ('9', 'name_960', '37');

四，核心代码

1. pom.xml

这里只用到了Spring的JdbcTemplate ，故使用了只有单个jar包的 spring的早期版本。
pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.fly</groupId>
	<artifactId>jdbc</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>jdbc</name>
	<url>http://maven.apache.org</url>
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring</artifactId>
			<version>2.5.6</version>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.38</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>1.7.25</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>1.7.25</version>
		</dependency>
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>3.5</version>
		</dependency>
		<dependency>
			<groupId>commons-logging</groupId>
			<artifactId>commons-logging</artifactId>
			<version>1.2</version>
		</dependency>
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.5</version>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.3.2</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

2. 核心代码

import java.io.IOException;
import java.io.InputStream;
import java.util.ResourceBundle;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.Assert;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

/**
 * 
 * 数据库初始化
 * 
 * @author 00fly
 * @version [版本号, 2018年11月11日]
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
public class DataBaseInit
{
    private static final Logger logger = LoggerFactory.getLogger(DataBaseInit.class);
    
    private DataBaseInit()
    {
        super();
    }
    
    /**
     * 使用数据库脚本进行数据初始化-JdbcTemplate实现
     * 
     * @param sqlPathArr
     * @throws IOException
     * @see [类、类#方法、类#成员]
     */
    public static void initUseSQL(String... sqlPathArr)
        throws IOException
    {
        Assert.notEmpty(sqlPathArr, "SQLPathArr array length must be greater than 0");
        // 建库用临时DataSource
        MysqlDataSource dataSource = new MysqlDataSource();
        ResourceBundle config = ResourceBundle.getBundle("jdbc");
        String jdbcUrl = StringUtils.substringBeforeLast(config.getString("jdbc.url"), "/");
        dataSource.setUrl(jdbcUrl);
        dataSource.setUser(config.getString("jdbc.username"));
        dataSource.setPassword(config.getString("jdbc.password"));
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        logger.info("JdbcTemplate = {}", jdbcTemplate);
        
        // 取数据库名
        String dataBase = StringUtils.substringAfterLast(config.getString("jdbc.url"), "/");
        dataBase = dataBase.contains("?") ? StringUtils.substringBefore(dataBase, "?") : dataBase;
        logger.info("★★★★ jdbcUrl = {}, dataBase = {}", jdbcUrl, dataBase);
        
        // 按需建库
        jdbcTemplate.execute(String.format("CREATE DATABASE IF NOT EXISTS `%s` DEFAULT CHARACTER SET UTF8", dataBase));
        
        // 正式库刷表
        dataSource.setUrl(config.getString("jdbc.url") + "&allowMultiQueries=true");
        jdbcTemplate = new JdbcTemplate(dataSource);
        logger.info("JdbcTemplate = {}", jdbcTemplate);
        for (String sqlPath : sqlPathArr)
        {
            try (InputStream inputStream = DataBaseInit.class.getResourceAsStream(sqlPath))
            {
                String sqlText = IOUtils.toString(inputStream, "utf-8");
                logger.info("SQL = {}", sqlText);
                if (dataSource.getUrl().contains("allowMultiQueries=true"))
                {
                    logger.info("开始执行当前的初始化语句块");
                    jdbcTemplate.execute(sqlText);
                }
                else
                {
                    logger.info("开始分割执行当前的初始化语句块");
                    Arrays.stream(sqlText.split(";")).filter(StringUtils::isNotBlank).forEach(jdbcTemplate::execute);
                }
            }
            logger.info("★★★★ initTable success!!");
        }
    }

3. 数据库自动创建2种方式

1.）修改jdbc.url参数支持数据库创建(推荐)

在jdbc.url参数后面添加&createDatabaseIfNotExist=true
注意：数据库配置文件 jdbc.properties里的 jdbc.username一定要有建库权限, 否则会报权限不足。

2.）调用DataBaseInit.initWithSql

            ResourceBundle config = ResourceBundle.getBundle("jdbc");
            String jdbcUrl = config.getString("jdbc.url");
            if (jdbcUrl.contains("createDatabaseIfNotExist=true"))
            {
                // 初始化dataSource、JdbcTemplate
                log.info("#### createDatabaseIfNotExist=true ####");
                MysqlDataSource dataSource = new MysqlDataSource();
                dataSource.setUrl(jdbcUrl);
                dataSource.setUser(config.getString("jdbc.username"));
                dataSource.setPassword(config.getString("jdbc.password"));
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                
                // 执行DDL、DML
                Resource resource1 = new ClassPathResource("/sql/init.sql");
                String sqlText = IOUtils.toString(resource1.getInputStream(), "utf-8");
                Arrays.stream(sqlText.split(";")).filter(StringUtils::isNotBlank).forEach(jdbcTemplate::execute);
                
                // 间接验证
                List<Map<String, Object>> list = jdbcTemplate.queryForList("show tables");
                log.info("JdbcTemplate.queryForList = {}", list);
            }
            else
            {
                DataBaseInit.initWithSql("/sql/init.sql");
            }

五，完整代码

完整的项目代码请参考：https://gitee.com/00fly/java-code-frame/tree/master/jdbc

姊妹篇： SpringBoot工程启动时自动创建数据库、数据表

有任何问题和建议，都可以向我提问讨论,大家一起进步，谢谢!

–over–