java执行mysql脚本执行_JAVA 执行MYSQL脚本（创建数据库，建表等）

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

CREATE DATABASE /*!32312 IF NOT EXISTS*/`@@@dbName@@@` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `@@@dbName@@@`;

DROP TABLE IF EXISTS `tb_abc`;

CREATE TABLE `tb_abc` (

`id` varchar(36) NOT NULL,

`days` int(11) DEFAULT NULL,

`last_update_user` varchar(50) DEFAULT NULL,

`last_update_time` datetime DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySqlSuperDao.java

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import java.util.Properties;

public class MySqlSuperDao implements ISuperDao {

/**

* 创建数据库(读SQL脚本)

*

* @author HeCheng

*/

@SuppressWarnings("finally")

public boolean createMisDB(String name, String year) throws DbException,

DaoException, SQLException {

Connection conn = null;

Statement stmt = null;

boolean success = false;

// 创建数据库名

String dbName = name + "_" + year;

try {

List sqlList = new ArrayList();

try {

InputStream sqlFileIn = MySqlSuperDao.class

.getResourceAsStream("/MySql/createDB.sql");

// 将SQL脚本产生为list

StringBuffer sqlSb = new StringBuffer();

byte[] buff = new byte[1024];

int byteRead = 0;

while ((byteRead = sqlFileIn.read(buff)) != -1) {

sqlSb.append(new String(buff, 0, byteRead, "utf-8"));

}

String[] sqlArr = sqlSb.toString().split(

"(;\\s*\\r\\n)|(;\\s*\\n)");

// 替换数据库名

int replace = 0;

for (int i = 0; i < sqlArr.length; i++) {

if (replace == 2) {

break;

}

if (sqlArr[i].indexOf("@@@dbName@@@") > -1) {

sqlArr[i] = sqlArr[i].replace("@@@dbName@@@", dbName);

replace++;

}

}

// 将数组转成LIST并且过滤LOCKTABLE 和注释

for (int i = 0; i < sqlArr.length; i++) {

String sql = sqlArr[i].replaceAll("--.*", "").trim();

if (!sql.equals("") && sql.indexOf("LOCK TABLES") != 0

&& sql.indexOf("UNLOCK TABLES") != 0

&& sql.indexOf("/*") != 0) {

sqlList.add(sql);

}

}

} catch (Exception e) {

System.out.println("error");

return false;

}

// 创建数据库链接并执行SQL脚本

conn = this.getConnection();

stmt = null;

conn.setAutoCommit(false);

stmt = conn.createStatement();

for (String sql : sqlList) {

stmt.addBatch(sql);

}

stmt.executeBatch();

success = true;

} catch (Exception e) {

// 如果报错，则删除D

conn = null;

conn = this.getConnection();

stmt = null;

conn.setAutoCommit(false);

stmt = conn.createStatement();

stmt.execute("drop database " + dbName);

success = false;

} finally {

if (stmt != null) {

stmt.close();

}

if (conn != null) {

conn.close();

}

return success;

}

}

/**

* 获取数据库链接

*

* @author HeCheng

* @time 2011-02-26 10:48:24

* @return

* @throws SQLException

* @throws IOException

* @throws ClassNotFoundException

*/

private Connection getConnection() throws SQLException, IOException,

ClassNotFoundException {

Connection con = null;

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

Properties properties = new Properties();

properties.load(MySqlSuperDao.class

.getResourceAsStream("/MySql/DB.properties"));

String url = properties.getProperty("url");

String u = properties.getProperty("u");

String p = properties.getProperty("p");

con = DriverManager.getConnection("jdbc:mysql://" + url + "", u, p);

return con;

}

}

分享到：

2011-02-26 10:52

浏览 5005

评论