package com.vci.server.omd.ddlTool;
|
|
import java.io.File;
|
import java.math.BigDecimal;
|
import java.sql.Connection;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.util.List;
|
|
import org.hibernate.Session;
|
|
import com.vci.corba.common.VCIError;
|
import com.vci.corba.omd.atm.AttribItem;
|
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
|
import com.vci.server.base.utility.AttributeHelper;
|
|
public final class DDLHelper {
|
|
/**
|
* 业务类型的系统属性
|
*/
|
private static final String otherFieldBt = "\n\tOID VARCHAR2(36) not null," +
|
"\n\tREVISIONOID VARCHAR2(36),\n\tNAMEOID VARCHAR2(36),\n\tBtmName 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 static 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";
|
|
|
|
public static boolean executeSql(String sql) throws Throwable{
|
Session session = HibernateSessionFactory.getSession();
|
try{
|
session.createSQLQuery(sql).executeUpdate();
|
}catch(Throwable e){
|
throw e;
|
}
|
return true;
|
}
|
|
/**
|
* 清空表中数据 truncate table platformlt_ebom
|
*/
|
|
public static boolean truncateTable(String tableName) throws VCIError {
|
Session session = HibernateSessionFactory.getSession();
|
session.createSQLQuery("truncate table " + tableName).executeUpdate();
|
return true;
|
}
|
|
/**
|
* 清空表中数据 当表主键或唯一键被引用为外键时, 就算引用方没有数据, truncate还不是不能删除表中数据 而 delete可以 delete
|
* table platformlt_ebom
|
*/
|
|
public static boolean deleteTable(String tableName) throws VCIError {
|
Session session = HibernateSessionFactory.getSession();
|
session.createSQLQuery("delete from " + tableName).executeUpdate();
|
return true;
|
}
|
|
public static boolean dropTable(String tableName) throws VCIError {
|
if (!existsTable(tableName))
|
return true;
|
|
Session session = HibernateSessionFactory.getSession();
|
session.createSQLQuery("drop table " + tableName).executeUpdate();
|
return true;
|
}
|
|
public static boolean existsTable(String tableName) {
|
int count = 0;
|
try {
|
Connection connection = HibernateSessionFactory.getSessionConnection();
|
String sql = "SELECT COUNT(1) FROM User_Tables WHERE table_name = ?";
|
PreparedStatement pst = connection.prepareStatement(sql);
|
pst.setString(1, tableName);
|
ResultSet rs = pst.executeQuery();
|
|
if (rs.next()) {
|
count = rs.getInt(0);
|
}
|
} catch (Exception e) {
|
return false;
|
}
|
|
return count != 0;
|
}
|
|
/**
|
*
|
* @param name
|
* @return
|
*/
|
public static 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;
|
}
|
|
|
/**
|
* 获取属性字段的sql语句
|
* @param array
|
* @return
|
*/
|
public static String getAbSql(AttribItem abItem){
|
String sql = "";
|
if(abItem == null){
|
return sql;
|
}
|
String abName = abItem.name;
|
String vtType = abItem.vtDataType;
|
String other = abItem.other;
|
String defValue = abItem.defValue;
|
|
if(vtType.equals("VTString")){
|
int length = 50;
|
String lengthStr = AttributeHelper.getOtherValueByType(other, "length");
|
if(lengthStr != null && !lengthStr.equals("")){
|
length = Integer.valueOf(lengthStr);
|
}
|
sql += abName.toUpperCase() + " VARCHAR2(" + length + ")";
|
if(!defValue.equals("")){
|
sql += " default '" + defValue + "'";
|
}
|
sql += ",\n\t";
|
}else if(vtType.equals("VTInteger") || vtType.equals("VTLong")){
|
sql += abName.toUpperCase() + " NUMBER";
|
if(!defValue.equals("")){
|
sql += " default " + defValue;
|
}
|
sql += ",\n\t";
|
}else if(vtType.equals("VTDouble")){
|
int length = 20;
|
String lengthStr = AttributeHelper.getOtherValueByType(other, "length");
|
if(lengthStr != null && !lengthStr.equals("")){
|
length = Integer.valueOf(lengthStr);
|
}
|
|
int accuracy = 2;
|
String accuracyStr = AttributeHelper.getOtherValueByType(other, "accuracy");
|
if(accuracyStr != null && !accuracyStr.equals("")){
|
accuracy = Integer.valueOf(accuracyStr);
|
}
|
sql += abName.toUpperCase() + " NUMBER(" + length + ", " + accuracy +")";
|
if(!defValue.equals("")){
|
sql += " default " + defValue;
|
}
|
sql += ",\n\t";
|
}else if(vtType.equals("VTBoolean")){
|
sql += abName.toUpperCase() + " VARCHAR2(8)";
|
if(!defValue.equals("")){
|
sql += " default '" + defValue + "'";
|
}
|
sql += ",\n\t";
|
}else if(vtType.equals("VTImage")){
|
sql += abName.toUpperCase() + " VARCHAR2(255)";
|
sql += ",\n\t";
|
}else if(vtType.equals("VTDate")){
|
sql += abName.toUpperCase() + " DATE";
|
sql += ",\n\t";
|
}else if(vtType.equals("VTTime")){
|
sql += abName.toUpperCase() + " TIMESTAMP";
|
sql += ",\n\t";
|
}else if(vtType.equals("VTDateTime")){
|
sql += abName.toUpperCase() + " TIMESTAMP";
|
sql += ",\n\t";
|
}else if(vtType.equals("VTNote")){
|
sql += abName.toUpperCase() + " VARCHAR2(255)";
|
sql += ",\n\t";
|
}else if(vtType.equals("VTFilePath")){
|
sql += abName.toUpperCase() + " VARCHAR2(255)";
|
sql += ",\n\t";
|
}else if(vtType.equals("VTClob")){
|
sql += abName.toUpperCase() + " CLOB";
|
sql += ",\n\t";
|
}
|
|
return sql;
|
}
|
|
public static String getBTSysFields() {
|
return otherFieldBt;
|
}
|
|
public static String getLTSysFields() {
|
return otherFieldLt;
|
}
|
|
|
/**
|
* 获取属性other中type的值
|
* @param other
|
* @param type
|
* @return
|
*/
|
// static 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;
|
//
|
// }
|
}
|
