Java创建数据库新建表及初始化表
2019-10-22 11:21 hikoukay 阅读(14474) 评论(0) 编辑 收藏 举报方法一
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包
<dependency> <groupId>org.apache.ant</groupId> <artifactId>ant</artifactId> <version>1.10.7</version> </dependency>
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("1qaz@WSX"); //要执行的脚本 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(); } }