package com.vci.server.omd.ddlTool;
|
|
import java.io.File;
|
import java.io.FileWriter;
|
import java.io.IOException;
|
import java.io.Writer;
|
import java.math.BigDecimal;
|
import java.util.ArrayList;
|
import java.util.Iterator;
|
import java.util.List;
|
import org.dom4j.Document;
|
import org.dom4j.DocumentException;
|
import org.dom4j.DocumentHelper;
|
import org.dom4j.Element;
|
import org.hibernate.Session;
|
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
|
|
/**
|
*
|
* 业务类型系统级属性：
|
1) 对象唯一性标记：OID VTString 36 必填
|
2) 版本对象唯一性标记：REVISIONOID VTString 36
|
3) 名称对象唯一性标记：NAMEOID VTString 36
|
业务类型名：btwName VTString 36
|
最新版本：ISLastR VTINTEGER //标记是否为同一个名称对象中最新版本
|
第一个版本：ISFirstR VTINTEGER //标记是否为同一个名称对象中的第一个版本对象
|
最新版次：ISLastV VTINTEGER //标记是否为同一个版本的最新版次
|
第一个版次：ISFirstV VTINTEGER //标记是否为同一个版本的第一个版次
|
|
|
4) 创建者：Creator VTString 36 ref User
|
5) 创建时间：CreateTime VTDateTime
|
6) 最后修改者：LastModifier VTString 36 ref User
|
7) 最后修改时间：LastModifyTime VTDateTime
|
8) 时间戳：TIMESTAMP VTLong
|
|
ID varchar2(36),
|
NAME varchar2(128),
|
DESCRIPTION varchar2(255),
|
owner varchar2(36),
|
checkInBy varchar2(36),
|
checkInTime timestamp,
|
checkoutBy varchar2(36),
|
checkoutTime timestamp,
|
copyFromVersion varchar2(36),
|
|
业务类型中版本相关属性：
|
1) 版本号规则：RevisionRule VTString 36 ref RevisionRule
|
2) 版次号规则：VersionRule VTString 36
|
3) 版本号序号：RevisionSeq VTINTEGER
|
4) 版本号值：RevisionValue VTString 10
|
5) 版次号序号：VersionSeq VTINTEGER
|
6) 版次号值：VersionValue VTString 10
|
|
|
业务类型中生命周期相关属性：
|
1）生命周期模板：LCTID VTString ref LCT
|
2）生命周期状态：LCStatus VTString ref LCS
|
|
|
链接类型系统级属性：
|
1） 对象唯一性标记：OID VTString 必填
|
2） 创建者：Creator VTString 36 ref User
|
3） 创建时间：CreateTime VTDateTime
|
4） 最后修改者：LastModifier VTString 36 ref User
|
5） 最后修改时间：LastModifyTime VTDateTime
|
6） From对象唯一性标记：OID VTString 36 必填
|
7） From版本对象唯一性标记：REVISIONOID VTString 36
|
8） From名称对象唯一性标记：NAMEOID VTString 36
|
From业务类型名：btwName VTString 36
|
9） To对象唯一性标记：OID VTString 36 必填
|
10） To版本对象唯一性标记：REVISIONOID VTString 36
|
11） To名称对象唯一性标记：NAMEOID VTString 36
|
To业务类型名：btwName VTString 36
|
12) 时间戳：TIMESTAMP VTLong
|
|
* @author Administrator
|
*
|
*/
|
public class DDLForOracle{
|
private String btDDLCreatePath = "";
|
private String btDDLDropPath = "";
|
private String btDDLAlterPath = "";
|
private String linkTypeDDLCreatePath = "";
|
private String linkTypeDDLDropPath = "";
|
private static final String BTM = "btm";
|
private static final String ATTRIBITEM = "attribItem";
|
private static final String LINKTYPES = "linkTypes";
|
private static final String LINKTYPE = "linkType";
|
private static final String ATTRIBUTE = "attibute";
|
private static final String ROW = "row";
|
private final String otherFieldBt = "\n\tOID VARCHAR2(36) not null," +
|
"\n\tREVISIONOID VARCHAR2(36),\n\tNAMEOID VARCHAR2(36),\n\tBtwName VARCHAR2(36)," +
|
"\n\tISLastR CHAR(1),\n\tISFirstR CHAR(1),\n\tISLastV CHAR(1),\n\tISFirstV CHAR(1)," +
|
"\n\tCreator VARCHAR2(36)," +
|
"\n\tCreateTime TIMESTAMP,\n\tLastModifier VARCHAR2(36),\n\tLastModifyTime TIMESTAMP," +
|
"\n\tRevisionRule VARCHAR2(36),\n\tVersionRule VARCHAR2(36),\n\tRevisionSeq NUMBER," +
|
"\n\tRevisionValue VARCHAR2(10),\n\tVersionSeq NUMBER,\n\tVersionValue VARCHAR2(10)," +
|
"\n\tLCTID VARCHAR2(36),\n\tLCStatus VARCHAR2(36),\n\tTS TIMESTAMP," +
|
"\n\tID VARCHAR2(36),\n\tNAME VARCHAR2(128),\n\tDESCRIPTION VARCHAR2(255)," +
|
"\n\tOWNER VARCHAR2(36),\n\tCHECKINBY VARCHAR2(36),\n\tCHECKINTIME TIMESTAMP," +
|
"\n\tCHECKOUTBY VARCHAR2(36),\n\tCHECKOUTTIME TIMESTAMP,\n\tCOPYFROMVERSION VARCHAR2(36),\n\t";
|
private final String otherFieldLt = "\n\tOID VARCHAR2(36) not null," +
|
"\n\tCreator VARCHAR2(36),\n\tCreateTime TIMESTAMP,\n\tLastModifier VARCHAR2(36)," +
|
"\n\tLastModifyTime TIMESTAMP,\n\tF_OID VARCHAR2(36) not null,\n\tF_REVISIONOID VARCHAR2(36)," +
|
"\n\tF_NAMEOID VARCHAR2(36),\n\tF_BtwName VARCHAR2(36),\n\tT_OID VARCHAR2(36) not null,\n\tT_REVISIONOID VARCHAR2(36)," +
|
"\n\tT_NAMEOID VARCHAR2(36),\n\tT_BtwName VARCHAR2(36),\n\tTS TIMESTAMP,\n\t";
|
private final String NEWLINE = "\n";
|
private static DDLForOracle gOracle = null;
|
private static final String PLATFORMBTM_ = "PLATFORMBTM_";
|
|
private DDLForOracle(){
|
|
}
|
public static DDLForOracle getInstance(){
|
if(gOracle == null){
|
gOracle = new DDLForOracle();
|
}
|
return gOracle;
|
}
|
/**
|
* 为业务类型生成DDL
|
* @param apContent
|
* @param btContent
|
* @return
|
*/
|
public boolean generatorODDLForBt(String apContent, String btContent) {
|
btDDLCreatePath = DataFileConfig.getProperty("btDDLCreatePathOracle");
|
btDDLDropPath = DataFileConfig.getProperty("btDDLDropPathOracle");
|
// 业务类型DDL
|
Document btDocument = null;
|
try {
|
btDocument = DocumentHelper.parseText(btContent);
|
} catch (DocumentException e) {
|
e.printStackTrace();
|
}
|
if (btDocument == null) {
|
return false;
|
}
|
boolean flag = generateDDLForBt(btDocument, apContent);
|
|
return flag;
|
}
|
|
/**
|
* 为链接类型生成DDL
|
* @param apContent
|
* @param ltContent
|
* @return
|
*/
|
public boolean generatorODDLForLt(String apContent, String ltContent) {
|
linkTypeDDLCreatePath = DataFileConfig.getProperty("linkTypeDDLCreatePathOracle");
|
linkTypeDDLDropPath = DataFileConfig.getProperty("linkTypeDDLDropPathOracle");
|
// 业务类型DDL
|
Document ltDocument = null;
|
try {
|
ltDocument = DocumentHelper.parseText(ltContent);
|
} catch (DocumentException e) {
|
e.printStackTrace();
|
}
|
if (ltDocument == null) {
|
return false;
|
}
|
boolean flag = generateDDLForLt(ltDocument, apContent);
|
|
return flag;
|
}
|
|
/**
|
* 业务类型DDL
|
* @param document
|
* @return
|
*/
|
private boolean generateDDLForBt(Document document, String apContent){
|
Element rootNode = document.getRootElement();
|
Element tableNode = rootNode.element(BTM);
|
List<Element> rowNodes = tableNode.elements(ROW);
|
ArrayList<String> sqlCreateList = new ArrayList<String>();
|
ArrayList<String> sqlDropList = new ArrayList<String>();
|
|
Document apDocument = null;
|
try {
|
apDocument = DocumentHelper.parseText(apContent);
|
} catch (DocumentException e) {
|
e.printStackTrace();
|
}
|
if(apDocument == null){
|
return false;
|
}
|
Element apRootNode = apDocument.getRootElement();
|
Element apTableNode = apRootNode.element(ATTRIBITEM);
|
List<Element> apRowNodes = apTableNode.elements(ROW);
|
|
for(Iterator<Element> i = rowNodes.iterator(); i.hasNext();){
|
Element rowNode = i.next();
|
String tableName = PLATFORMBTM_ + rowNode.element("name").getText().toUpperCase();
|
String[] apNames = getBtwApNameArray(document, rowNode.element("name").getText());
|
String sqlCreate = "create Table " + tableName + "(" + otherFieldBt;
|
String sqlDrop = "drop Table " + tableName + ";\n";
|
for(int k = 0; k < apNames.length; k++){
|
sqlCreate += getAPSql(apRowNodes, apNames[k]);
|
}
|
sqlCreate = sqlCreate.substring(0, sqlCreate.lastIndexOf(","));
|
sqlCreate += "\n);\n";
|
sqlCreate += "alter table " + tableName + " add constraint PK_" + tableName + " primary key (OID);\n";
|
|
sqlCreateList.add(sqlCreate);
|
sqlDropList.add(sqlDrop);
|
}
|
writeFile(btDDLCreatePath, sqlCreateList);
|
writeFile(btDDLDropPath, sqlDropList);
|
return true;
|
}
|
|
/**
|
* 链接类型DDL
|
* @param document
|
* @return
|
*/
|
private boolean generateDDLForLt(Document document, String apContent){
|
Element rootNode = document.getRootElement();
|
Element dataNode = rootNode.element(LINKTYPES);
|
List<Element> rowNodes = dataNode.elements(LINKTYPE);
|
ArrayList<String> sqlCreateList = new ArrayList<String>();
|
ArrayList<String> sqlDropList = new ArrayList<String>();
|
|
Document apDocument = null;
|
try {
|
apDocument = DocumentHelper.parseText(apContent);
|
} catch (DocumentException e) {
|
e.printStackTrace();
|
}
|
if(apDocument == null){
|
return false;
|
}
|
Element apRootNode = apDocument.getRootElement();
|
Element apTableNode = apRootNode.element(ATTRIBITEM);
|
List<Element> apRowNodes = apTableNode.elements(ROW);
|
|
for(Iterator<Element> i = rowNodes.iterator(); i.hasNext();){
|
Element rowNode = i.next();
|
String tableName = rowNode.elementText("name").toUpperCase();
|
String sqlCreate = "create Table " + tableName + "(" + otherFieldLt;
|
String sqlDrop = "drop Table " + tableName + ";\n";
|
|
List<Element> apNodes_ = rowNode.elements(ATTRIBUTE);
|
for(Iterator<Element> k = apNodes_.iterator(); k.hasNext();){
|
Element apNode_ = k.next();
|
String apName = apNode_.elementText("name");
|
sqlCreate += getAPSql(apRowNodes, apName);
|
}
|
|
sqlCreate = sqlCreate.substring(0, sqlCreate.lastIndexOf(","));
|
sqlCreate += "\n);\n";
|
sqlCreate += "alter table " + tableName + " add constraint PK_" + tableName + " primary key (OID);\n";
|
|
sqlCreateList.add(sqlCreate);
|
sqlDropList.add(sqlDrop);
|
}
|
|
writeFile(linkTypeDDLCreatePath, sqlCreateList);
|
writeFile(linkTypeDDLDropPath, sqlDropList);
|
return true;
|
}
|
|
/**
|
* 将ddl的内容(contentList)写进sql文件里
|
* @param ddlPath
|
* @param contentList
|
*/
|
private void writeFile(String ddlPath, ArrayList<String> contentList){
|
File file = new File(ddlPath);
|
File pFile = file.getParentFile();
|
if(!pFile.exists()){
|
pFile.mkdirs();
|
}
|
if(!file.exists()){
|
try {
|
file.createNewFile();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
try {
|
|
|
FileWriter fW = new FileWriter(file);
|
for(int i = 0; i < contentList.size(); i++){
|
fW.write(contentList.get(i));
|
}
|
fW.flush();
|
fW.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
|
}
|
|
/**
|
* 根据属性名获取该属性字段的DDL
|
* @param apRowNodes
|
* @param apName
|
* @return
|
*/
|
private String getAPSql(List<Element> apRowNodes, String apName){
|
Element apRowNode = null;
|
String sqlAp = "";
|
for(Iterator<Element> i = apRowNodes.iterator(); i.hasNext();){
|
Element apRowNode_ = i.next();
|
if(apRowNode_.element("name").getText().equals(apName)){
|
apRowNode = apRowNode_;
|
break;
|
}
|
}
|
|
if(apRowNode == null){
|
return sqlAp;
|
}
|
|
String vtType = apRowNode.elementText("vtDataType");
|
String other = apRowNode.elementText("other");
|
String defValue = apRowNode.elementText("defValue");
|
|
if(vtType.equals("VTString")){
|
int length = 50;
|
String lengthStr = getOtherValueByType(other, "length");
|
if(lengthStr != null && !lengthStr.equals("")){
|
length = Integer.valueOf(lengthStr);
|
}
|
sqlAp += apName.toUpperCase() + " VARCHAR2(" + length + ")";
|
if(!defValue.equals("")){
|
sqlAp += " default '" + defValue + "'";
|
}
|
sqlAp += ",\n\t";
|
}else if(vtType.equals("VTInteger") || vtType.equals("VTLong")){
|
sqlAp += apName.toUpperCase() + " NUMBER";
|
if(!defValue.equals("")){
|
sqlAp += " default " + defValue;
|
}
|
sqlAp += ",\n\t";
|
}else if(vtType.equals("VTDouble")){
|
int length = 20;
|
String lengthStr = getOtherValueByType(other, "length");
|
if(lengthStr != null && !lengthStr.equals("")){
|
length = Integer.valueOf(lengthStr);
|
}
|
|
int accuracy = 2;
|
String accuracyStr = getOtherValueByType(other, "accuracy");
|
if(accuracyStr != null && !accuracyStr.equals("")){
|
accuracy = Integer.valueOf(accuracyStr);
|
}
|
sqlAp += apName.toUpperCase() + " NUMBER(" + length + ", " + accuracy +")";
|
if(!defValue.equals("")){
|
sqlAp += " default " + defValue;
|
}
|
sqlAp += ",\n\t";
|
}else if(vtType.equals("VTBoolean")){
|
sqlAp += apName.toUpperCase() + " VARCHAR2(8)";
|
if(!defValue.equals("")){
|
sqlAp += " default '" + defValue + "'";
|
}
|
sqlAp += ",\n\t";
|
}else if(vtType.equals("VTImage")){
|
sqlAp += apName.toUpperCase() + " VARCHAR2(255)";
|
sqlAp += ",\n\t";
|
}else if(vtType.equals("VTDate")){
|
sqlAp += apName.toUpperCase() + " TIMESTAMP";
|
sqlAp += ",\n\t";
|
}else if(vtType.equals("VTTime")){
|
sqlAp += apName.toUpperCase() + " TIMESTAMP";
|
sqlAp += ",\n\t";
|
}else if(vtType.equals("VTDateTime")){
|
sqlAp += apName.toUpperCase() + " TIMESTAMP";
|
sqlAp += ",\n\t";
|
}else if(vtType.equals("VTNote")){
|
sqlAp += apName.toUpperCase() + " VARCHAR2(255)";
|
sqlAp += ",\n\t";
|
}else if(vtType.equals("VTFilePath")){
|
sqlAp += apName.toUpperCase() + " VARCHAR2(255)";
|
sqlAp += ",\n\t";
|
}
|
|
return sqlAp;
|
}
|
|
/**
|
* 获取属性other中type的值
|
* @param other
|
* @param type
|
* @return
|
*/
|
public String getOtherValueByType(String other, String type){
|
String[] otherArray = other.split(";");
|
for(int i = 0; i < otherArray.length; i++){
|
String otherValue = otherArray[i];
|
if(otherValue.contains(type)){
|
return otherValue.substring(otherValue.indexOf("=") + 2, otherValue.length());
|
}
|
}
|
return null;
|
|
}
|
|
/**
|
* 获取业务类型的属性名数组
|
*/
|
public String[] getBtwApNameArray(Document document, String btwName){
|
ArrayList<String> nameList = new ArrayList<String>();
|
|
Element rootNode = document.getRootElement();
|
Element tableNode = rootNode.element(BTM);
|
List<Element> rowNodes = tableNode.elements(ROW);
|
while(!btwName.equals("")){
|
for(Iterator<Element> i = rowNodes.iterator(); i.hasNext();){
|
Element rowNode = i.next();
|
if(rowNode.elementText("name").equals(btwName)){
|
|
String[] apNames = rowNode.elementText("apName").split(",");
|
for(int k = 0; k < apNames.length; k++){
|
nameList.add(apNames[k]);
|
}
|
btwName = rowNode.elementText("fName");
|
break;
|
}
|
}
|
}
|
|
return nameList.toArray(new String[0]);
|
}
|
|
/**
|
* 创建业务类型table
|
* @param sql
|
* @return
|
*/
|
public boolean executeUpdate(String sql){
|
try {
|
Session session = HibernateSessionFactory.getSession();
|
/**
|
* executeUpdate returns: The Number of entities updated or deleted.
|
*/
|
session.createSQLQuery(sql).executeUpdate();
|
} catch (Exception e) {
|
e.printStackTrace();
|
return false;
|
}
|
return true;
|
}
|
|
/**
|
* 记录修改Table DDL(alter table t add(column type)
|
* Oracle
|
*/
|
public boolean alterTableDDL(String sql){
|
btDDLAlterPath = DataFileConfig.getProperty("btDDLAlterPathOracle");
|
File file = new File(btDDLAlterPath);
|
File pFile = file.getParentFile();
|
if(!pFile.exists()){
|
pFile.mkdirs();
|
}
|
if(!file.exists()){
|
try {
|
file.createNewFile();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
try {
|
Writer writer = new FileWriter(file);
|
writer.write(sql);
|
writer.write(NEWLINE);
|
writer.flush();
|
writer.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
return true;
|
}
|
|
/**
|
*
|
* @param name
|
* @return
|
*/
|
public boolean hasInstance(String name){
|
boolean flag = false;
|
Session session = HibernateSessionFactory.getSession();
|
//判断表是否存在, 表不存在则该表无数据
|
String sql_ = "select count(1) from user_tables where TABLE_NAME = '" + name.toUpperCase() +"'";
|
List<Object> list_ = session.createSQLQuery(sql_).list();
|
// 当list.get(i)中Object数量为1时, list.get(i)为Object
|
// 当list.get(i)中Object数量 > 1时, list.get(i)为Object
|
Object obj_ = list_.get(0);
|
int count_ = ((BigDecimal) obj_).intValue();
|
if (count_ < 1) {
|
return false;
|
}
|
String sql = "select count(*) from " + name;
|
List<Object> list = session.createSQLQuery(sql).list();
|
//当list.get(i)中Object数量为1时, list.get(i)为Object
|
//当list.get(i)中Object数量 > 1时, list.get(i)为Object
|
Object obj = list.get(0);
|
int count = ((BigDecimal)obj).intValue();
|
if(count > 0){
|
flag = true;
|
}
|
return flag;
|
}
|
}
|
