package xgenerator.dao.impl;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import xgenerator.constants.ConstraintTypes;
import xgenerator.converter.OracleDataTypeConverter;
import xgenerator.converter.PropertyTypeConverter;
import xgenerator.converter.DataTypeConverter;
import xgenerator.jdbc.JdbcContext;
import xgenerator.model.FieldMetadata;
import xgenerator.util.NotationUtils;
import xgenerator.util.StringUtils;
public class MetadataDaoOracleImpl extends MetadataDaoJdbcAdapter {
/**
* OracleDataTypeConverter
*/
private final DataTypeConverter dataTypeConverter = new OracleDataTypeConverter();
/**
* JavaTypeConverter
*/
private PropertyTypeConverter propertyTypeConverter;
/**
* <p>
* Title：获取所有表
* </p>
* @author <a href="mailto:shushanlee@msn.com">liss</a>
* @param dataSourceName
* @return
* @see xgenerator.dao.impl.MetadataDaoJdbcAdapter#getTables(java.lang.String)
*/
public Vector<String> getTables(String dataSourceName) {
Vector<String> tables = new Vector<String>();
if (StringUtils.isBlank(dataSourceName)) {
return tables;
}
if(MOCK) {
tables.add("EAP_USERS");
tables.add("EAP_ROLES");
tables.add("EAP_MENUS");
return tables;
}
JdbcContext jdbcContext = JdbcContext.getJdbcContext(dataSourceName);
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = jdbcContext.currentConnection();
stmt = conn.createStatement();
String sql = "SELECT T.TABLE_NAME FROM USER_TABLES T ORDER BY T.TABLE_NAME";
//System.out.println(sql);
rs = stmt.executeQuery(sql);
while(rs.next()) {
tables.add(rs.getString("TABLE_NAME"));
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
} finally {
JdbcContext.close(stmt, rs);
}
return tables;
}
/**
* <p>
* Title：获取表注释
* </p>
* @author <a href="mailto:shushanlee@msn.com">liss</a>
* @param dataSourceName
* @param tableName
* @return
* @see xgenerator.dao.impl.MetadataDaoJdbcAdapter#getTableComments(java.lang.String, java.lang.String)
*/
public String getTableComments(String dataSourceName, String tableName) {
String tableComments = "";
if(MOCK) {
tableComments = "用户管理";
return tableComments;
}
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JdbcContext.getJdbcContext(dataSourceName).currentConnection();
stmt = conn.createStatement();
String sql = "SELECT T.COMMENTS FROM USER_TAB_COMMENTS T WHERE T.TABLE_NAME='" + tableName + "'";
//System.out.println(sql);
rs = stmt.executeQuery(sql);
if(rs.next()) {
tableComments = rs.getString("COMMENTS");
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
} finally {
JdbcContext.close(stmt, rs);
}
return tableComments;
}
/**
* <p>
* Title：获取所有列
* </p>
* @author <a href="mailto:shushanlee@msn.com">liss</a>
* @param dataSourceName
* @param tableName
* @return
* @see xgenerator.dao.impl.MetadataDaoJdbcAdapter#getTableColumns(java.lang.String, java.lang.String)
*/
public Vector<String> getTableColumns(String dataSourceName, String tableName) {
Vector<String> tableColumns = new Vector<String>();
if (null == tableName || "".equals(tableName)) {
return tableColumns;
}
if(MOCK) {
tableColumns.add("USER_ID");
tableColumns.add("USER_NAME");
tableColumns.add("USER_AGE");
return tableColumns;
}
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JdbcContext.getJdbcContext(dataSourceName).currentConnection();
stmt = conn.createStatement();
String sql = "SELECT T.COLUMN_NAME FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME='" + tableName + "' ORDER BY T.COLUMN_ID";
//System.out.println(sql);
rs = stmt.executeQuery(sql);
while(rs.next()) {
tableColumns.add(rs.getString("COLUMN_NAME"));
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
} finally {
JdbcContext.close(stmt, rs);
}
return tableColumns;
}
/**
* <p>
* Title：获取列元数据
* </p>
* @author <a href="mailto:shushanlee@msn.com">liss</a>
* @param dataSourceName
* @param tableName
* @return
* @see xgenerator.dao.impl.MetadataDaoJdbcAdapter#getTableColumnMetadata(java.lang.String, java.lang.String)
*/
public List<FieldMetadata> getTableColumnMetadata(String dataSourceName, String tableName) {
List<FieldMetadata> fieldMetadatas = new ArrayList<FieldMetadata>();
if(MOCK) {
Object[][] data = { { "用户名", "USER_NAME", "用户名", "VARCHAR", new Integer(256), null, false, "", true, "PRIMARY KEY" },
{ "密码", "PASSWORD", "密码", "VARCHAR", new Integer(4), null, false, null, true, "FOREIGN KEY" },
{ "创建时间", "CREATE_DATE", "创建时间", "DATE", null, null, true, "select sysdate() from dual", false, null },
{ "年龄", "AGE", "年龄", "NUMBER", new Integer(10), null, true, null, false, null },
{ "存款", "MONEY", "存款", "FLOAT", new Integer(10), new Integer(5), true, new Float(0.00000F), false, null },
{ "备注", "DESCRIPTION", "备注", "CLOB", new Integer(1000), null, true, null, false, null } };
for (int i = 0; i < data.length; i++) {
FieldMetadata oneFieldMetadata = new FieldMetadata();
oneFieldMetadata.setDisplayName(String.valueOf(data[i][0]));
String columnName = String.valueOf(data[i][1]);
oneFieldMetadata.setColumnName(columnName);
oneFieldMetadata.setPropertyName(NotationUtils.lowerCamelCase(columnName));
oneFieldMetadata.setComments(String.valueOf(data[i][2]));
String dataType = String.valueOf(data[i][3]);
int dataLength = null != data[i][4] ? (Integer) data[i][4] : 0;
int dataPrecision = null != data[i][5] ? (Integer) data[i][5] : 0;
int dataScale = 3;
String sqlType = this.dataTypeConverter.convert(dataType, dataLength, dataPrecision, dataScale);
oneFieldMetadata.setSqlDataType(sqlType);
oneFieldMetadata.setDataLength(dataLength);
oneFieldMetadata.setDataPrecision(dataPrecision);
oneFieldMetadata.setDataScale(dataScale);
String propertyType = this.getPropertyTypeConverter().convert(sqlType);
oneFieldMetadata.setPropertyType(propertyType);
oneFieldMetadata.setNullable((Boolean) data[i][6]);
oneFieldMetadata.setDataDefault(null != data[i][7] ? "" + data[i][7] : null);
oneFieldMetadata.setKeyField((Boolean) data[i][8]); // 是否为键
oneFieldMetadata.setConstraintType(null != data[i][9] ? (String) data[i][9] : null); // 键类型
fieldMetadatas.add(oneFieldMetadata);
}
return fieldMetadatas;
}
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JdbcContext.getJdbcContext(dataSourceName).currentConnection();
stmt = conn.createStatement();
//String sql = "SELECT A.*, B.* FROM (SELECT S.COMMENTS, T.COLUMN_ID, T.COLUMN_NAME, T.DATA_TYPE, T.DATA_LENGTH, T.DATA_PRECISION, T.DATA_SCALE, T.NULLABLE, T.DEFAULT_LENGTH, T.DATA_DEFAULT, T.CHAR_LENGTH FROM USER_COL_COMMENTS S, USER_TAB_COLUMNS T WHERE S.TABLE_NAME = T.TABLE_NAME AND S.COLUMN_NAME = T.COLUMN_NAME AND T.TABLE_NAME = '" + tableName + "') A, (SELECT U.COLUMN_NAME CONSTRAINT_COLUMN_NAME, U.POSITION, C.CONSTRAINT_NAME, C.CONSTRAINT_TYPE, C.TABLE_NAME, C.R_CONSTRAINT_NAME, C.DELETE_RULE, C.INDEX_NAME FROM USER_CONS_COLUMNS U, USER_CONSTRAINTS C WHERE U.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND U.OWNER = C.OWNER AND U.TABLE_NAME = C.TABLE_NAME AND C.CONSTRAINT_TYPE IN ('P', 'R') AND U.TABLE_NAME = '" + tableName + "') B WHERE A.COLUMN_NAME = B.CONSTRAINT_COLUMN_NAME(+) ORDER BY A.COLUMN_ID";
String sql = "SELECT A.*, B.* FROM (SELECT S.COMMENTS, S.TABLE_NAME, T.COLUMN_ID, T.COLUMN_NAME, T.DATA_TYPE, T.DATA_LENGTH, T.DATA_PRECISION, T.DATA_SCALE, T.NULLABLE, T.DEFAULT_LENGTH, T.DATA_DEFAULT, T.CHAR_LENGTH FROM USER_COL_COMMENTS S, USER_TAB_COLUMNS T WHERE S.TABLE_NAME = T.TABLE_NAME AND S.COLUMN_NAME = T.COLUMN_NAME AND T.TABLE_NAME = '" + tableName + "') A, (SELECT U.COLUMN_NAME CONSTRAINT_COLUMN_NAME, U.POSITION, C.R_TABLE_NAME, C.R_COLUMN_NAME, C.CONSTRAINT_NAME, C.CONSTRAINT_TYPE, C.TABLE_NAME, C.R_CONSTRAINT_NAME, C.DELETE_RULE, C.INDEX_NAME FROM USER_CONS_COLUMNS U, (SELECT N.TABLE_NAME AS R_TABLE_NAME, N.CONSTRAINT_COLUMN_NAME AS R_COLUMN_NAME, M.* from (SELECT H.COLUMN_NAME AS CONSTRAINT_COLUMN_NAME, G.* from USER_CONSTRAINTS G, USER_CONS_COLUMNS H WHERE G.OWNER=H.OWNER AND G.TABLE_NAME=H.TABLE_NAME AND G.CONSTRAINT_NAME=H.CONSTRAINT_NAME AND G.CONSTRAINT_TYPE IN('P', 'R')) M, (SELECT H.COLUMN_NAME AS CONSTRAINT_COLUMN_NAME, G.* from USER_CONSTRAINTS G, USER_CONS_COLUMNS H WHERE G.OWNER=H.OWNER AND G.TABLE_NAME=H.TABLE_NAME AND G.CONSTRAINT_NAME=H.CONSTRAINT_NAME AND G.CONSTRAINT_TYPE IN('P', 'R')) N where M.R_CONSTRAINT_NAME=N.CONSTRAINT_NAME(+) AND M.TABLE_NAME='" + tableName + "' AND M.CONSTRAINT_TYPE IN('P', 'R')) C WHERE U.OWNER = C.OWNER AND U.TABLE_NAME = C.TABLE_NAME AND U.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND U.COLUMN_NAME = C.CONSTRAINT_COLUMN_NAME AND C.CONSTRAINT_TYPE IN ('P', 'R') AND U.TABLE_NAME = '" + tableName + "') B WHERE A.TABLE_NAME = B.TABLE_NAME(+) AND A.COLUMN_NAME = B.CONSTRAINT_COLUMN_NAME(+) ORDER BY A.COLUMN_ID";
// System.out.println(sql);
rs = stmt.executeQuery(sql);
FieldMetadata oneFieldMetadata = null;
while(rs.next()) {
oneFieldMetadata = new FieldMetadata();
oneFieldMetadata.setColumnName(rs.getString("COLUMN_NAME"));
oneFieldMetadata.setComments(rs.getString("COMMENTS"));
oneFieldMetadata.setDisplayName(rs.getString("COMMENTS"));
String dataType = rs.getString("DATA_TYPE");
int dataLength = rs.getInt("DATA_LENGTH");
int dataPrecision = rs.getInt("DATA_PRECISION");
int dataScale = rs.getInt("DATA_SCALE");
String sqlType = this.dataTypeConverter.convert(dataType, dataLength, dataPrecision, dataScale);
oneFieldMetadata.setSqlDataType(sqlType);
oneFieldMetadata.setDataLength(dataLength);
oneFieldMetadata.setDataPrecision(dataPrecision);
oneFieldMetadata.setDataScale(dataScale);
String nullableFlag = rs.getString("NULLABLE");
if("Y".equals(nullableFlag)) {
oneFieldMetadata.setNullable(true);
} else {
oneFieldMetadata.setNullable(false);
}
oneFieldMetadata.setDefaultLength(rs.getInt("DEFAULT_LENGTH"));
oneFieldMetadata.setDataDefault(rs.getString("DATA_DEFAULT"));
oneFieldMetadata.setCharLength(rs.getInt("CHAR_LENGTH"));
oneFieldMetadata.setPropertyName(NotationUtils.lowerCamelCase(rs.getString("COLUMN_NAME")));
String propertyType = this.getPropertyTypeConverter().convert(sqlType);
oneFieldMetadata.setPropertyType(propertyType);
String constraintType = rs.getString("CONSTRAINT_TYPE"); // 约束类型
if(StringUtils.isNotBlank(constraintType)) {
oneFieldMetadata.setKeyField(true); // 是否为键
} else {
oneFieldMetadata.setKeyField(false); // 是否为键
}
if("P".equals(constraintType)) {
oneFieldMetadata.setConstraintType(ConstraintTypes.PK_TYPE);
} else if("R".equals(constraintType)) {
oneFieldMetadata.setConstraintType(ConstraintTypes.FK_TYPE);
oneFieldMetadata.setReferencingTable(rs.getString("R_TABLE_NAME"));
oneFieldMetadata.setReferencingColumn(rs.getString("R_COLUMN_NAME"));
oneFieldMetadata.setDeleteRule(rs.getString("DELETE_RULE"));
} else if("C".equals(constraintType)) {
oneFieldMetadata.setConstraintType(ConstraintTypes.CHECK_TYPE);
}
fieldMetadatas.add(oneFieldMetadata);
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
} finally {
JdbcContext.close(stmt, rs);
}
return fieldMetadatas;
}
/**
* <p>
* Title：策略模式，设置属性类型转换策略
* </p>
* @param propertyTypeConverter
* @see xgenerator.dao.MetadataDao#setPropertyTypeConverter(xgenerator.converter.PropertyTypeConverter)
*/
public void setPropertyTypeConverter(PropertyTypeConverter propertyTypeConverter) {
this.propertyTypeConverter = propertyTypeConverter;
}
/**
* @return the propertyTypeConverter
*/
public PropertyTypeConverter getPropertyTypeConverter() {
return this.propertyTypeConverter;
}
}