package com.xxx.xxx.dao;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;
import com.ft.hsm.Util.ConstantValue;
import com.ft.hsm.Util.StringUtil;
/*
* 使用java以及jdbc执行sql脚本的工具示例代码
*/
public class SqlHelper {
private static Logger logger;
static {
logger = Logger.getLogger(ScreenDaoImpl.class);
PropertyConfigurator.configure(ConstantValue.PATH_SCREENSERVER_LOG4J_PROPERTIES);
}
public static boolean createDBFromSQLFile(String SQLPath, String SQLFileCharsetName, String dbFilePath,
int[] retRowsExpected) {
if (StringUtil.isEmpty(SQLPath) || StringUtil.isEmpty(SQLFileCharsetName) || StringUtil.isEmpty(dbFilePath)
|| 0 >= retRowsExpected.length) {
logger.error("参数错误");
return false;
}
// 检验是否己创建
File dbfile = new File(dbFilePath);
if (dbfile.exists() || dbfile.isDirectory()) {
logger.error(dbFilePath + "数据库文件己存在或存在同名文件夹");
return false;
}
// 读取SQL文件
String sql = getText(SQLPath, SQLFileCharsetName); // "UTF-8"
if (StringUtil.isEmpty(sql)) {
logger.error("读取SQL文件失败");
return false;
}
// 转换为SQL语句
List<String> sqlList = getSql(sql, SQLFileCharsetName);
for (int i = 0; i < sqlList.size(); i++) {
logger.info(i + ":" + sqlList.get(i));
}
boolean isSuccess = false;
try {
// 执行SQL语句
int[] rows = SqlHelper.execute(getConn(dbFilePath), sqlList);
logger.info("Row count Expected:" + Arrays.toString(retRowsExpected));
// 执行结果集鉴定
isSuccess = Arrays.equals(rows, retRowsExpected);
// 调试打印执行结果集
if (null == rows || rows.length != retRowsExpected.length) {
logger.error("返回结果与期望个数不符, rows.length=" + rows.length + ", retRowsExpected.length="
+ retRowsExpected.length);
} else {
for (int index = 0; index < rows.length; index++) {
logger.info("rows[" + index + "] return=" + rows[index] + ", expected=" + retRowsExpected[index]
+ ",sql=" + sqlList.get(index));
}
}
} catch (Exception e) {
e.printStackTrace();
}
return isSuccess;
}
private static Connection getConn(String dbFile) {
String driver = "org.sqlite.JDBC"; // "com.mysql.jdbc.Driver";
String url = "jdbc:sqlite:" + dbFile; // "数据库连接";
// String username = "账号";
// String password = "密码";
Connection conn = null;
try {
Class.forName(driver); //classLoader,加载对应驱动
conn = (Connection) DriverManager.getConnection(url/*, username, password*/);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static int[] execute(Connection conn, List<String> sqlList) throws Exception {
Statement stmt = null;
stmt = conn.createStatement();
for (String sql : sqlList) {
sql = sql.trim();
if (sql != null && !sql.trim().equals(""))
stmt.addBatch(sql);
}
int[] rows = stmt.executeBatch();
logger.info("Row count returned:" + Arrays.toString(rows));
conn.close();
return rows;
}
/*
* getText方法吧path路径里面的文件按行读数来放入一个大的String里面去
* 并在换行的时候加入\r\n
*/
public static String getText(String path, String SQLFileCharsetName) {
File file = new File(path);
if (!file.exists() || file.isDirectory()) {
logger.error(path + "文件不存在或存在同名文件夹");
return null;
}
StringBuilder sb = new StringBuilder();
try {
FileInputStream fis = new FileInputStream(path);
InputStreamReader isr = new InputStreamReader(fis, SQLFileCharsetName);
BufferedReader br = new BufferedReader(isr);
String temp = null;
temp = br.readLine();
while (temp != null) {
if (temp.length() >= 2) {
String str1 = temp.substring(0, 1);
String str2 = temp.substring(0, 2);
if (str1.equals("#") || str2.equals("--") || str2.equals("/*") || str2.equals("//")) {
temp = br.readLine();
continue;
}
sb.append(temp + "\r\n");
}
temp = br.readLine();
}
br.close();
} catch (Exception e) {
e.printStackTrace();
}
return sb.toString();
}
/*
* getSqlArray方法
* 从文件的sql字符串中分析出能够独立执行的sql语句并返回
*/
public static List<String> getSql(String sql, String SQLFileCharsetName) {
String s = sql;
s = tryDeleteBOM(SQLFileCharsetName, s);
s = s.replaceAll("\r\n", "\r");
s = s.replaceAll("\r\n", "\r");
s = s.replaceAll("\r", "\n");
List<String> ret = new ArrayList<String>();
String[] sqlarry = s.split(";"); //用;把所有的语句都分开成一个个单独的句子
sqlarry = filter(sqlarry);
ret = Arrays.asList(sqlarry);
return ret;
}
public static String[] filter(String[] ss) {
List<String> strs = new ArrayList<String>();
for (String s : ss) {
if (s != null && !s.equals("")) {
strs.add(s);
}
}
String[] result = new String[strs.size()];
for (int i = 0; i < strs.size(); i++) {
result[i] = strs.get(i).toString();
}
return result;
}
private static String tryDeleteBOM(String SQLFileCharsetName, String s) {
byte[] byteSQL = null;
logger.info("判断是否含有UTF-8的BOM头");
try {
byteSQL = s.getBytes(SQLFileCharsetName);
// 去掉UTF-8的BOM头
if (byteSQL[0] == (byte) 0xef && byteSQL[1] == (byte) 0xbb && byteSQL[2] == (byte) 0xbf) {
logger.info("含有UTF-8的BOM头");
logger.info("去掉UTF-8的BOM头前" + Arrays.toString(byteSQL));
s = new String(byteSQL, 3, byteSQL.length - 3, SQLFileCharsetName);
logger.info("去掉UTF-8的BOM头后为:" + Arrays.toString(s.getBytes(SQLFileCharsetName)));
} else {
logger.info("不含有UTF-8的BOM头");
}
} catch (UnsupportedEncodingException ex) {
ex.printStackTrace();
}
return s;
}
public static void main(String[] args) {
String SQLPath = "config/xxx_create.utf8.sql";
String dbFile = "db" + File.separator + "test.db";
String SQLFileCharsetName = "UTF-8";
// String SQLFileCharsetName = "GBK";
int[] retRowsExpected = { 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 };
boolean isSuccess = createDBFromSQLFile(SQLPath, SQLFileCharsetName, dbFile, retRowsExpected);
logger.info("创建数据库" + (isSuccess ? "成功" : "失败"));
}
}
