java 新建数据库表_Java创建数据库新建表及初始化表

方法一

package com.crt.openapi;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.io.InputStreamReader;

import java.nio.charset.Charset;

import java.sql.Connection;

import java.sql.Statement;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.jdbc.ScriptRunner;

public class CreateDB2 {

public static void main(String[] args) throws Exception

{

Class.forName("com.mysql.jdbc.Driver");

//一开始必须填一个已经存在的数据库

String url = "jdbc:mysql://127.0.0.1:3306/ecsb_dev?characterEncoding=utf-8&autoReconnect=true";

//遇到一个乱码的问题,是因为连接串中有这两个参数allowMultiQueries=true&useUnicode=true&,耽搁了一天时间,最终找到是这个问题

// String url = "jdbc:mysql://127.0.0.1:3306/ecsb_dev?allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8&autoReconnect=true";

Connection conn = DriverManager.getConnection(url, "root", "123456");

Statement stat = conn.createStatement();

String dataBaseName="ecsb_tenant9999";

//创建数据库hello

stat.executeUpdate("CREATE DATABASE IF NOT EXISTS "+dataBaseName+" default charset utf8 COLLATE utf8_general_ci; ");

//打开创建的数据库

stat.close();

conn.close();

url=url.replace("ecsb_dev", dataBaseName);

conn = DriverManager.getConnection(url, "root", "123456");

stat = conn.createStatement();

/* //创建表test

// stat.executeUpdate("create table test(id int, name varchar(80))");

//添加数据

stat.executeUpdate("insert into test values(1, ‘张三‘)");

stat.executeUpdate("insert into test values(2, ‘李四‘)");*/

ScriptRunner runner = new ScriptRunner(conn);

runner.setAutoCommit(false);

runner.setErrorLogWriter(null);

runner.setLogWriter(null);

// runner.runScript(new InputStreamReader(new FileInputStream("D:\\work_code\\version2\\ecsb-parent\\ecsb-springboot\\src\\main\\resources\\db.sql"),"gbk"));

Resources.setCharset(Charset.forName("UTF-8"));

/*InputStreamReader reader = new InputStreamReader(Resources.getResourceAsStream("db.sql"), "UTF-8");

runner.runScript(reader);*/

runner.runScript(Resources.getResourceAsReader("db.sql"));

//查询数据

ResultSet result = stat.executeQuery("select * from api_token");

while (result.next())

{

System.out.println(result.getInt("id") + " " + result.getString("creater"));

}

//关闭数据库

result.close();

stat.close();

conn.close();

}

}

方法二

导入ant包

org.apache.ant

ant

1.10.7

package com.crt.openapi;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.io.File;

import java.io.InputStreamReader;

import java.nio.charset.Charset;

import java.sql.Connection;

import java.sql.Statement;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.jdbc.SQL;

import org.apache.ibatis.jdbc.ScriptRunner;

import org.apache.tools.ant.Project;

import org.apache.tools.ant.taskdefs.SQLExec;

public class CreateDB {

public static void main(String[] args) throws Exception

{

Class.forName("com.mysql.jdbc.Driver");

//一开始必须填一个已经存在的数据库

String url = "jdbc:mysql://127.0.0.1:3306/ecsb_dev?characterEncoding=utf-8&autoReconnect=true";

//遇到一个乱码的问题,是因为连接串中有这两个参数allowMultiQueries=true&useUnicode=true&,耽搁了一天时间,最终找到是这个问题

// String url = "jdbc:mysql://127.0.0.1:3306/ecsb_dev?allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8&autoReconnect=true";

Connection conn = DriverManager.getConnection(url, "root", "123456");

Statement stat = conn.createStatement();

String dataBaseName="ecsb_tenant1999";

//创建数据库hello

stat.executeUpdate("CREATE DATABASE IF NOT EXISTS "+dataBaseName+" default charset utf8 COLLATE utf8_general_ci; ");

//打开创建的数据库

stat.close();

conn.close();

url=url.replace("ecsb_dev", dataBaseName);

conn = DriverManager.getConnection(url, "root", "123456");

stat = conn.createStatement();

SQLExec sqlExec = new SQLExec();

sqlExec.setDriver("com.mysql.jdbc.Driver");

sqlExec.setUrl(url);

sqlExec.setUserid("root");

sqlExec.setPassword("[email protected]");

//要执行的脚本

sqlExec.setSrc(new File("D:\\work_code\\version2\\ecsb-parent\\ecsb-springboot\\src\\main\\resources\\db.sql"));

sqlExec.setPrint(true); //设置是否输出

sqlExec.setEncoding("utf8");

sqlExec.setProject(new Project());

sqlExec.execute();

/* new SQL(){

public SQL SELECT(String columns) {};

return "";

}*/

/* //创建表test

// stat.executeUpdate("create table test(id int, name varchar(80))");

//添加数据

stat.executeUpdate("insert into test values(1, ‘张三‘)");

stat.executeUpdate("insert into test values(2, ‘李四‘)");*/

/*ScriptRunner runner = new ScriptRunner(conn);

runner.setAutoCommit(true);

runner.setErrorLogWriter(null);

runner.setLogWriter(null);

// runner.runScript(new InputStreamReader(new FileInputStream("D:\\work_code\\version2\\ecsb-parent\\ecsb-springboot\\src\\main\\resources\\db.sql"),"gbk"));

InputStreamReader reader = new InputStreamReader(Resources.getResourceAsStream("db.sql"), "UTF-8");

Resources.setCharset(Charset.forName("UTF8"));

//runner.runScript(Resources.getResourceAsReader("db.sql"));

runner.runScript(reader);*/

//查询数据

ResultSet result = stat.executeQuery("select * from api_token");

while (result.next())

{

System.out.println(result.getInt("id") + " " + result.getString("creater"));

}

//关闭数据库

result.close();

stat.close();

//runner.closeConnection();

conn.close();

}

}

原文：https://www.cnblogs.com/hikoukay/p/11718723.html