一、介绍
本文实现的是java语言通过配置文件的方式，运行sql运行文件的功能，解决系统开发时需要用户自己在数据库里手动运行sql文件的问题
二、项目结构
image.png
image.png
三、代码
3.1、代码运行入口
DatabaseManageUtil.initEOSDatabase("Oracle", "ceshi","Aa11111111", "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "oracle.jdbc.driver.OracleDriver",new String[]{"coframe"});
3.2、DatabaseManageUtil.java代码
/*******************************************************************************
* $Header: /cvsroot/PTP50/code/eos/develop/src/governor/com.primeton.eos.governor.base/com.primeton.governor.base/src/com/primeton/governor/management/DatabaseManageUtil.java,v 1.2 2013/09/04 13:55:58 wuyh Exp $
* $Revision: 1.2 $
* $Date: 2013/09/04 13:55:58 $
*
*==============================================================================
*
* Copyright (c) 2001-2006 Primeton Technologies, Ltd.
* All rights reserved.
*
* Created on 2008-2-13
*******************************************************************************/
package com.primeton.governor.management;
import com.eos.internal.governor.dbinit.DatabaseInitializerFactory;
import com.eos.internal.governor.dbinit.IDatabaseInitializer;
import com.primeton.governor.help.InvokeStatus;
import java.sql.Connection;
import java.sql.DriverManager;
/**
* 数据库管理用帮助类
*
* @author Administrator
* @date 2008-02-13 18:01:26
*/
//("数据库管理工具")
public class DatabaseManageUtil {
private static Connection getConnection(String user, String pwd, String jdbcurl, String jdbcDriver) throws Exception {
Class.forName(jdbcDriver);
return DriverManager.getConnection(jdbcurl, user, pwd);
}
//("根据选项进行数据库初始化")
public static InvokeStatus initEOSDatabase(String dbType, String user,
String pwd, String jdbcurl, String jdbcDriver,String[] options) {
Connection connection = null;
try {
connection = getConnection(user, pwd, jdbcurl, jdbcDriver);
IDatabaseInitializer initializer = DatabaseInitializerFactory.getDatabaseInitializer();
String ret = initializer.initialize(connection, options);
if (ret != null && ret.trim().length() > 0) {
return new InvokeStatus(false, ret);
} else {
return new InvokeStatus(true, null);
}
} catch (Throwable e) {
return new InvokeStatus(false, e.getMessage());
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (Throwable ignore) {
}
}
}
//("根据初始化选项判断数据库是否已经初始化")
public static boolean isInitialized(String user, String pwd,
String jdbcurl, String jdbcDriver,String[] options) {
Connection connection = null;
try {
connection = getConnection(user, pwd, jdbcurl, jdbcDriver);
IDatabaseInitializer initializer = DatabaseInitializerFactory.getDatabaseInitializer();
for(String option:options) {
if( initializer.isInitialized(connection, option))
return true;
}
return false;
} catch (Exception e) {
return false;
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (Throwable ignore) {
}
}
}
//("查找数据库初始的选项")
public static String[] getDBInitOptions(){
IDatabaseInitializer initializer = DatabaseInitializerFactory.getDatabaseInitializer();
return initializer.getComponentNames();
}
}
3.3、DatabaseInitializerImpl.java代码
/*******************************************************************************
* $Header: /cvsroot/PTP50/workdir/eos/develop/src/governor/com.primeton.eos.governor.base/com.primeton.governor.dbinit/src/com/primeton/governor/dbinit/impl/DatabaseInitializerImpl.java,v 1.1 2013/10/12 01:38:03 wuyh Exp $
* $Revision: 1.1 $
* $Date: 2013/10/12 01:38:03 $
*
*==============================================================================
*
* Copyright (c) 2001-2006 Primeton Technologies, Ltd.
* All rights reserved.
*
* Created on 2010-5-28
*******************************************************************************/
package com.primeton.governor.dbinit.impl;
import com.eos.internal.governor.dbinit.IDatabaseInitializer;
import com.primeton.governor.dbinit.impl.script.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.io.InputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
/**
* 初始化实现类
*
* @author yourname (mailto:yourname@primeton.com)
*/
public class DatabaseInitializerImpl implements IDatabaseInitializer {
static Log logger = LogFactory.getLog(DatabaseInitializerImpl.class);
public static final String OTHER_DB_TYPE_PREFIX="Other::";
/**
* 初始化
*
* @param connection 连接
* @param componentNames 组件名称列表
* @return 初始化结果，如果为空，则初始化成功，否则为错误信息
*/
public String initialize(Connection connection, String[] componentNames) {
if (connection == null) {
return "Connection is null!";
}
if (componentNames == null || componentNames.length == 0) {
return null;
}
try {
DatabaseMetaData metaData = connection.getMetaData();
String databaseProductName = metaData.getDatabaseProductName();
SqlScript script = null;
String dbType = null;
if (databaseProductName.toLowerCase().indexOf("db2") != -1) {
script = new DB2Script();
dbType = "db2";
} else if (databaseProductName.toLowerCase().indexOf("oracle") != -1) {
script = new OracleScript();
dbType = "oracle";
} else if (databaseProductName.toLowerCase().indexOf("sql server") != -1) {
script = new SQLServerScript();
dbType = "sqlserver";
} else if (databaseProductName.toLowerCase().indexOf("informix") != -1) {
script = new InformixScript();
dbType = "informix";
} else if (databaseProductName.toLowerCase().indexOf("adaptive server enterprise") != -1
|| databaseProductName.toLowerCase().indexOf("sybase adaptive server iq") != -1) {// sybase
script = new SybaseScript();
dbType = "sybase";
} else if (databaseProductName.toLowerCase().indexOf("mysql") != -1) {
script = new MySqlScript();
dbType = "mysql";
} else if (databaseProductName.toLowerCase().indexOf("dm dbms") != -1){
script = new DaMengScript();
dbType = "DM";
} else {
// return "Not support this database " + databaseProductName;
script = new OtherDBScript();
dbType = OTHER_DB_TYPE_PREFIX + databaseProductName.toLowerCase();
}
StringBuffer messageResultBuf = new StringBuffer();
for (String moduleName : componentNames) {
if (moduleName == null) {
continue;
}
List<String[]> scriptDefineList = ScriptDefineLoader.getDefineScripts(moduleName, dbType);
if (scriptDefineList == null || scriptDefineList.size() == 0) {
messageResultBuf.append("Not found script for module '" + moduleName + "' in database: '" + dbType + "'!\r\n");
continue;
}
for (String[] scriptDefine : scriptDefineList) {
if (scriptDefine == null || scriptDefine.length != 2) {
messageResultBuf.append("Not found script for module '" + moduleName + "' in database: '" + dbType + "'!\r\n");
continue;
}
try {
runScript(script, connection, new URL(scriptDefine[0]), scriptDefine[1], messageResultBuf);
} catch (Throwable e) {
logger.debug(e);
}
}
}
return messageResultBuf.toString();
} catch (Throwable t) {
logger.debug(t);
return t.getMessage();
}
}
private static void runScript(SqlScript script, Connection connection, URL defineUrl, String encoding, StringBuffer messageResultBuf) {
InputStream in = null;
try {
in = defineUrl.openStream();
script.run(in, encoding == null ? "UTF-8" : encoding, connection, messageResultBuf);
} catch (Throwable t) {
logger.debug(t);
} finally {
try {
if (in != null) {
in.close();
}
} catch (Throwable ignore) {
}
}
}
public String[] getComponentNames() {
return ScriptDefineLoader.getComponentNames();
}
/**
* 判断指定数据库是否已经初始化过
*
* @return
*/
public boolean isInitialized(Connection connection, String componentName) {
if (connection == null) {
return false;
}
String tableName = ScriptDefineLoader.getTestTableName(componentName);
ResultSet rs = null;
Statement stmt = null;
String testSql = null;
if (tableName != null && tableName.trim().length() > 0) {
testSql = "select * from " + tableName;
} else {
testSql = "select * from EOS_UNIQUE_TABLE";
}
try {
stmt = connection.createStatement();
rs = stmt.executeQuery(testSql);
return true;
} catch (Exception ex) {
logger.debug(ex);
return false;
} finally {
try {
if (rs != null)
rs.close();
} catch (Exception ex) {
}
try {
if (stmt != null)
stmt.close();
} catch (Exception ex) {
}
}
}
}
3.4、ScriptDefineLoader.java代码
/*******************************************************************************
* $Header: /cvsroot/PTP50/workdir/eos/develop/src/governor/com.primeton.eos.governor.base/com.primeton.governor.dbinit/src/com/primeton/governor/dbinit/impl/ScriptDefineLoader.java,v 1.1 2013/10/12 01:38:03 wuyh Exp $
* $Revision: 1.1 $
* $Date: 2013/10/12 01:38:03 $
*
*==============================================================================
*
* Copyright (c) 2001-2006 Primeton Technologies, Ltd.
* All rights reserved.
*
* Created on 2010-5-31
*******************************************************************************/
package com.primeton.governor.dbinit.impl;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.File;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.util.*;
/**
* 脚本定义加载
*
* 注意：数据库初始化的功能不可以依赖EOS Server的jar
*
* @author yourname (mailto:yourname@primeton.com)
*/
public class ScriptDefineLoader {
private static Log logger = LogFactory.getLog(ScriptDefineLoader.class);
private static List<String> componentList = new ArrayList<String>();
private static List<Integer> componentIndexList = new ArrayList<Integer>();
//<moduleName, <dbType, <{script, encoding}>>>
private static Map<String, Map<String, List<String[]>>> scriptDefineMap = null;
private static Map<String, String> testTableMap = null;
private static ClassLoader loader = ScriptDefineLoader.class.getClassLoader();
private static DocumentBuilder docbuilder = null;
static {
init();
}
private static void init() {
try {
//classPath下的META-INF/scripts/script-define.xml
Enumeration<URL> e = loader.getResources("scripts/aa/script-define.xml");
while(e.hasMoreElements()) {
URL defineUrl = e.nextElement();
System.out.println("-------------"+defineUrl.getPath());
loadScript(null, defineUrl);
}
} catch (Throwable t) {
logger.debug(t);
}
}
public static void loadScript(String rootDir, URL defineUrl) {
if (scriptDefineMap == null) {
scriptDefineMap = new LinkedHashMap<String, Map<String, List<String[]>>>();
}
if (testTableMap == null) {
testTableMap = new LinkedHashMap<String, String>();
}
if (docbuilder == null) {
try {
DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
dbf.setIgnoringElementContentWhitespace(true);
docbuilder = dbf.newDocumentBuilder();
} catch (Throwable t) {
t.printStackTrace();
}
}
InputStream in = null;
try {
in = defineUrl.openStream();
InputSource isrc = new InputSource(new InputStreamReader(in, "UTF-8"));
Document doc = docbuilder.parse(isrc);
Element docElec = (Element) doc.getDocumentElement();
NodeList componentNodeList = docElec.getElementsByTagName("component");
for (int i = 0; i < componentNodeList.getLength(); i++) {
Element componentElem = (Element)componentNodeList.item(i);
String componentName = componentElem.getAttribute("name");
String indexStr = componentElem.getAttribute("index");
int index = 1000;
if (indexStr != null && indexStr.trim().length() > 0) {
try {
index = Integer.parseInt(indexStr.trim());
} catch (Throwable ignore) {
}
}
addComponent(componentName, index);
Map<String, List<String[]>> componentMap = scriptDefineMap.get(componentName);
if (componentMap == null) {
componentMap = new LinkedHashMap<String, List<String[]>>();
scriptDefineMap.put(componentName, componentMap);
}
String testTableName = componentElem.getAttribute("test-table");
testTableMap.put(componentName, testTableName);
NodeList groupNodeList = componentElem.getElementsByTagName("group");
for (int j = 0; j < groupNodeList.getLength(); j++) {
Element groupElem = (Element)groupNodeList.item(j);
String groupType = groupElem.getAttribute("type");
List<String[]> scriptList = componentMap.get(groupType);
if (scriptList == null) {
scriptList = new ArrayList<String[]>();
componentMap.put(groupType, scriptList);
}
NodeList scriptNodeList = groupElem.getElementsByTagName("script");
for (int k = 0; k < scriptNodeList.getLength(); k++) {
Element scriptElem = (Element)scriptNodeList.item(k);
addScript(scriptList, rootDir, scriptElem.getAttribute("uri"), scriptElem.getAttribute("encoding"));
}
}
}
} catch (Throwable t) {
logger.debug(defineUrl.getFile(), t);
} finally {
try {
if (in != null) {
in.close();
}
} catch (Throwable ignore) {
}
}
}
private static void addComponent(String componentName, int index) {
if (componentName == null || componentList.contains(componentName)) {
return;
}
boolean inserted = false;
for (int i = 0; i < componentIndexList.size(); i++) {
int sortData = componentIndexList.get(i);
if (sortData > index) {
componentList.add(i, componentName);
componentIndexList.add(i, index);
inserted = true;
break;
}
}
if (!inserted) {
// 加到最后
componentList.add(componentName);
componentIndexList.add(index);
}
}
@SuppressWarnings("deprecation")
private static void addScript(List<String[]> scriptList, String rootDir, String scirptUri, String encoding) {
try {
if (scirptUri == null || scirptUri.length() == 0) {
return;
}
Enumeration<URL> e = loader.getResources(scirptUri);
while(e.hasMoreElements()) {
URL defineUrl = e.nextElement();
scriptList.add(new String[]{defineUrl.toExternalForm(), encoding});
}
if (rootDir == null || rootDir.length() == 0) {
return;
}
File scriptFile = new File(rootDir, scirptUri);
if (scriptFile.exists()) {
scriptList.add(new String[]{scriptFile.toURL().toExternalForm(), encoding});
}
} catch (Throwable e) {
logger.debug(e);
}
}
public static void destory() {
if (scriptDefineMap != null ) {
scriptDefineMap.clear();
scriptDefineMap = null;
}
if (testTableMap != null) {
testTableMap.clear();
testTableMap = null;
}
docbuilder = null;
}
public static List<String[]> getDefineScripts(String componentName, String groupType) {
List<String[]> scriptList = new ArrayList<String[]>();
if (scriptDefineMap != null) {
Map<String, List<String[]>> map = scriptDefineMap.get(componentName);
if (map != null) {
List<String[]> list = map.get(groupType);
if (list != null) {
scriptList.addAll(list);
}
// 增加对OtherDB的支持
if (groupType.startsWith(DatabaseInitializerImpl.OTHER_DB_TYPE_PREFIX)) {
groupType = groupType.substring(DatabaseInitializerImpl.OTHER_DB_TYPE_PREFIX.length());
for (String definedGroupType : map.keySet()) {
if (groupType.toLowerCase().contains(definedGroupType.toLowerCase())) {
list = map.get(definedGroupType);
if (list != null) {
scriptList.addAll(list);
}
break;
}
}
}
//----------------
}
}
return scriptList;
}
public static String[] getComponentNames() {
if (componentList != null) {
return componentList.toArray(new String[0]);
}
return new String[0];
}
public static String getTestTableName(String componentName) {
if (testTableMap != null) {
return testTableMap.get(componentName);
}
return null;
}
}
3.5、OracleScript.java代码
package com.primeton.governor.dbinit.impl.script;
import java.io.BufferedReader;
import java.io.CharArrayWriter;
import java.io.IOException;
public class OracleScript extends SqlScript {
//Added by Majie 2012.4.5
protected String getProcedureStatement(BufferedReader r) throws IOException {
//存放读入的每行内容
StringBuffer procedureContent = new StringBuffer();
String lineContent = null;
String trimedLine = null;
int contentPosition = -1;
while ((lineContent = r.readLine()) != null) {
//--开始的注释行
if ((contentPosition = lineContent.indexOf("--")) != -1) {
if(contentPosition==0)
lineContent="";
else
lineContent = lineContent.substring(0, contentPosition - 1);
trimedLine = lineContent.trim();
}
//判断/*开始的注释行
if ((contentPosition = lineContent.indexOf("/*")) != -1) {
lineContent = lineContent.substring(0, contentPosition - 1);
trimedLine = lineContent.trim();
if (!trimedLine.equals("")) {
procedureContent.append(lineContent + "\n");
while (true) {
lineContent = r.readLine();
if (lineContent == null) break;
if ((contentPosition = lineContent.indexOf("*/")) == -1) continue;
else {
lineContent = lineContent.substring(contentPosition + 2, lineContent.length());
break;
}
}
}
}
//判断空行
trimedLine = lineContent.trim();
if (trimedLine.equals("")) continue;
procedureContent.append(lineContent + "\n");
}
logger.warn(procedureContent.toString(), null);
return procedureContent.toString();
}
protected String getNextStatement(BufferedReader r) throws IOException {
boolean isFinish = false;//是否完成标志位
boolean inString = false;//是否是字符串标志位
boolean isComment = false;//是否是注释表示位
int i = 0;
int count = 0;
int lineLength = 0;//当前行字符长度
String line = null;//SQL 语句
CharArrayWriter caw = new CharArrayWriter();//存放 SQL 语句
//针对当前行进行操作
while (!isFinish) {
line = r.readLine();
if (line == null) {
isFinish = true;
} else {
line = line.trim();
lineLength = line.length();
i = 0;
while (i < lineLength) {
//一个字符一个字符的检查
char c = line.charAt(i);
//如果是注释行的话，检查是否有 '*/'，有的话就跳过2位，否则只跳1位字符
if (isComment) {
if (c == '*' && i < (lineLength - 1)
&& line.charAt(i + 1) == '/') {
isComment = false;
i += 2;
} else {
i++;
}
continue;
}
//如果不是注释行的话，检查是否以 '\' 开头
/* first check if a string begins */
if (c == '\'') {
caw.write(c);
if (inString) {
count++;
if (count >= 2) {
count = 0;
}
} else {
inString = true;
count = 0;
}
} else {
if (inString && count == 1) {
inString = false;
}
if (!inString) {
// check special characters:'/',';','-','(',')';
if (c == '/') { // end of sql statement;
if (i == (lineLength - 1)) {
isFinish = true;
break;
} else {
if (line.charAt(i + 1) == '*') {
isComment = true;
}
}
} else if (c == ';') {
isFinish = true;
break;
} else if (c == '-') {
// check if single line comment;
if (i < (lineLength - 1)
&& line.charAt(i + 1) == '-') {
break;
}
//Add By Kevin Yung At 2008-03-20 Start
caw.write(c);
//Add By Kevin Yung At 2008-03-20 End
} else {
caw.write(c);
}
} else {
caw.write(c);
}
}
i++;
}
}
caw.write(' ');
}
if (line == null) {
return null;
}
if (caw.size() < 1) {
return new String("");
} else {
return caw.toString();
}
}
}
3.6、script-define.xml文件，用于配置数据库文件
<?xml version="1.0" encoding="UTF-8"?>
<scripts>
<component name="coframe" index="2000" test-table="CAP_FORM">
<group type="oracle">
<script uri="META-INF/scripts/coframe/Oracle/coframe.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/Oracle/coframe_init_data.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/Oracle/platform_init_data.sql" encoding="UTF-8" />
</group>
<group type="db2">
<script uri="META-INF/scripts/coframe/DB2/coframe.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/DB2/coframe_init_data.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/DB2/platform_init_data.sql" encoding="UTF-8" />
</group>
<group type="sybase">
<script uri="META-INF/scripts/coframe/Sybase/coframe.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/Sybase/coframe_init_data.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/Sybase/platform_init_data.sql" encoding="UTF-8" />
</group>
<group type="sqlserver">
<script uri="META-INF/scripts/coframe/SQLServer/coframe.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/SQLServer/coframe_init_data.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/SQLServer/platform_init_data.sql" encoding="UTF-8" />
</group>
<group type="mysql">
<script uri="META-INF/scripts/coframe/Mysql/coframe.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/Mysql/coframe_init_data.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/Mysql/platform_init_data.sql" encoding="UTF-8" />
</group>
<group type="kingbasees">
<script uri="META-INF/scripts/coframe/KingbaseES/coframe.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/KingbaseES/coframe_init_data.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/KingbaseES/platform_init_data.sql" encoding="UTF-8" />
</group>
<group type="DM">
<script uri="META-INF/scripts/coframe/DaMeng/coframe.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/DaMeng/coframe_init_data.sql" encoding="UTF-8" />
<script uri="META-INF/scripts/coframe/DaMeng/platform_init_data.sql" encoding="UTF-8" />
</group>
</component>
</scripts>
3.7、数据库文件目录结构
image.png
image.png
image.png
image.png
