java 获取mysql 表创建的sql语句_java初始化数据库读取sql配置文件建表

在src/main/resources下sql文件夹建立一个init.sql文件，放入建表语句：

CREATE TABLE {DB}.`AGENT_STATUS` (

`DEVICE_NUM` varchar(100) NOT NULL,

`START_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 　　　　　　　　　　CURRENT_TIMESTAMP,

`STATUS` int(11) NOT NULL,

`DURATION` int(11) DEFAULT NULL,

`END_TIME` timestamp NULL DEFAULT NULL,

`CALL_TYPE` int(11) DEFAULT NULL,

`SESSION_ID` varchar(255) DEFAULT NULL,

`SOURCE` varchar(255) DEFAULT NULL,

`CUSTOM_NUM` varchar(100) DEFAULT NULL,

`MEDIA_TYPE` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

CREATE TABLE {DB}.`ASSOCIATE_DATA` (

`SESSIONID` varchar(255) NOT NULL,

`DEVICE_NUM` varchar(255) DEFAULT NULL,

`AGENT_NAME` varchar(255) DEFAULT NULL,

`SKILL_GROUP_ID` int(11) DEFAULT NULL,

`SKILL_GROUP_NAME` varchar(255) DEFAULT NULL,

`TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`SOURCE` varchar(255) DEFAULT NULL,

`CALL_TYPE` int(11) DEFAULT NULL,

`AGENT_ID` varchar(36) DEFAULT NULL,

PRIMARY KEY (`SESSIONID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

读取.sql工具类ReportFormSqlUtil，代码如下：

package com.channelsoft.col.colweb.util;

import java.io.InputStream; import java.util.ArrayList; import java.util.List;

import org.slf4j.Logger; import org.slf4j.LoggerFactory;

public class ReportFormSqlUtil {

private static Logger log = LoggerFactory.getLogger(ConfigUtil.class);

public static List loadSql()throws Exception {

InputStream sqlFileIn = null;   List sqlList = new ArrayList();

try {

sqlFileIn = ReportFormSqlUtil.class.getResourceAsStream("/sql/reportform.sql");  //配置文件路径

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"));

}

sqlFileIn.close();

// Windows 下换行是 \r\n, Linux 下是 \n

String[] sqlArr = sqlSb.toString().split("(;\\s*\\r\\n)|(;\\s*\\n)");

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

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

if (!sql.equals("")) {     sqlList.add(sql);    }

}

return sqlList;

} catch (Exception ex) {

log.error("读取报表sql建表文件[reportform.sql]失败.", ex);

throw new Exception(ex.getMessage());

}

}

}

初始化数据库建表：

//给用户建立报表数据库

List listsql = ReportFormSqlUtil.loadSql();//读取.sql建表语句

String crsql = "CREATE DATABASE col_user_"+ user.getId() + " DEFAULT CHARACTER SET utf8";//建库语句

try {

//建库

jdbcTemplate.update(crsql);

for(int i = 0;i < listsql.size();i++){

//建表语句

String sqll = listsql.get(i).replaceFirst("\\{DB\\}","col_user_"+user.getId());

System.out.println(sqll);

jdbcTemplate.update(sqll);

}

} catch (Exception e) {

logger.error("初始化用户报表数据库错误", e);

throw e;

}