自动创建数据库/表

自动创建数据库/表

方法一

 public static void main(String[] args) throws SQLException, IOException {
        String username = " ";
        String password = "";
        String host = "";
        String exportDatabaseName = "";
        //创建数据库
        Connection connection = DriverManager.getConnection("jdbc:mysql://"+host+":3306?serverTimezone=UTC", username, password);
        Statement statement = connection.createStatement();
        statement.executeUpdate("create database if not exists "+exportDatabaseName+" default character set utf8mb4 COLLATE utf8mb4_general_ci");
        statement.close();
        connection.close();
        //执行sql文件
        File directory = new File("");
        String rootCanonicalPath = directory.getCanonicalPath();
        String exportPath = rootCanonicalPath + "/src/main/resources/sql/**.sql";
        String command = new String("cmd /k mysql"+" -h"+host+" -u"+username+" -p"+password+" "+exportDatabaseName+" <"+exportPath);
        //执行命令行
        Runtime runtime = Runtime.getRuntime();
        try {
            //cmd /k在执行命令后不关掉命令行窗口  cmd /c在执行完命令行后关掉命令行窗口   \\表示转译符也可使用/替代，linux使用/
            Process process = runtime.exec(command);
            log.info("创建数据库成功");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

方法二

public static void main(String[] args) throws SQLException, IOException {
//创建数据库
        String username = "";
        String password = "";
        String host = "";
        String driver = "";
        String exportDatabaseName = "";
        //创建数据库
        Connection connection = DriverManager.getConnection("jdbc:mysql://"+host+":3306?serverTimezone=UTC", username, password);
        Statement statement = connection.createStatement();
        statement.executeUpdate("create database if not exists "+exportDatabaseName+" default character set utf8mb4 COLLATE utf8mb4_general_ci");
        statement.close();
        connection.close();
        File directory = new File("");
        String rootCanonicalPath = directory.getCanonicalPath();
        String exportPath = rootCanonicalPath + "/src/main/resources/sql/mall.sql";
        executeSql("jdbc:mysql://"+host+":3306/"+exportDatabaseName+"?serverTimezone=Asia/Shanghai&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false", username, password, driver,exportPath);
    }

    static void executeSql(String url, String userName, String userPassword, String driver, String sql) throws FileNotFoundException {
        //sql执行输出流
        StringWriter succeedWriter = new StringWriter();
        PrintWriter succeedOut = new PrintWriter(succeedWriter);
        StringWriter errorWriter = new StringWriter();
        PrintWriter errorOut = new PrintWriter(errorWriter);
        Connection conn = null;
        ScriptRunner runner;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, userName, userPassword);
        } catch (Exception e) {
            log.error("数据库连接失败");
            try {
                conn.close();
            } catch (Exception ee) {
                log.error(url + "关闭连接错误!");
            }
            return;
        }
        runner = new ScriptRunner(conn);
        //设置字符集,不然中文乱码插入错误
        Resources.setCharset(Charset.forName("UTF-8"));
        runner.setAutoCommit(false);
        runner.setSendFullScript(true);
        //设置日志
        runner.setLogWriter(succeedOut);
        runner.setErrorLogWriter(errorOut);
        //遇到错误停止
        runner.setStopOnError(true);
//        Reader read = new StringReader(sql);
        Reader read = new FileReader(new File(sql));
        try {
            runner.runScript(read);
        } catch (Exception e) {
            e.printStackTrace();
            return;
        } finally {
            try {
                runner.closeConnection();
                conn.close();
            } catch (Exception e) {
                log.error(url + "关闭连接错误!");
            }
        }
        log.info("执行成功");
    }

方法三

public static void main(String[] args) throws SQLException {
        //创建数据库
        Connection connection = DriverManager.getConnection("jdbc:mysql://***:3306?serverTimezone=UTC", "***", "***");
        Statement statement = connection.createStatement();
        statement.executeUpdate("create database if not exists `***` default character set utf8mb4 COLLATE utf8mb4_general_ci");
        statement.close();
        connection.close();
        execute();
    }


    private static void execute() {
        String connectUrl = "jdbc:mysql://***:3306/***?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true";
        Connection connection = null;
        try {
            Class.forName("***");
            connection = DriverManager.getConnection(connectUrl, "***", "***");
            initTable(connection);
        } catch (SQLException | ClassNotFoundException | IOException e) {
            // 异常处理

        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    // 异常处理
                }
            }
        }
    }

    private static void initTable(Connection connection) throws IOException {
        // sql 脚本
        ClassPathResource classPathResource = new ClassPathResource("sql/mall.sql");
        InputStream inputStream = classPathResource.getInputStream();
        InputStreamReader inputStreamReader = new InputStreamReader(inputStream);
        ScriptRunner scriptRunner = new ScriptRunner(connection);
        // 必须设置该参数为true，否则无法执行begin……end;
        scriptRunner.setSendFullScript(true);
        scriptRunner.runScript(inputStreamReader);
    }

大佬勿喷，欢迎提意见建议评论！！！！