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.constants.MySqlDataTypes;
import xgenerator.converter.MySqlDataTypeConverter;
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 MetadataDaoMySqlImpl extends MetadataDaoJdbcAdapter {
/**
* MySqlDataTypeConverter
*/
private final DataTypeConverter dataTypeConverter = new MySqlDataTypeConverter();
/**
* 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> tableList = new Vector<String>();
JdbcContext jdbcContext = JdbcContext.getJdbcContext(dataSourceName);
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = jdbcContext.currentConnection();
String tableSchema = conn.getCatalog();
stmt = conn.createStatement();
String sql = "SELECT T.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_CATALOG='DEF' AND TABLE_SCHEMA='" + tableSchema + "' ORDER BY T.TABLE_NAME";
rs = stmt.executeQuery(sql);
while(rs.next()) {
tableList.add(rs.getString("TABLE_NAME"));
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
} finally {
JdbcContext.close(stmt, rs);
}
return tableList;
}
/**
* <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 = "";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JdbcContext.getJdbcContext(dataSourceName).currentConnection();
String tableSchema = conn.getCatalog();
stmt = conn.createStatement();
String sql = "SELECT T.TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_CATALOG='DEF' AND TABLE_SCHEMA='" + tableSchema + "' AND TABLE_NAME='" + tableName + "'";
rs = stmt.executeQuery(sql);
if(rs.next()) {
tableComments = rs.getString("TABLE_COMMENT");
}
} 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> tableList = new Vector<String>();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JdbcContext.getJdbcContext(dataSourceName).currentConnection();
String tableSchema = conn.getCatalog();
stmt = conn.createStatement();
String sql = "SELECT T.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS T WHERE T.TABLE_CATALOG='DEF' AND TABLE_SCHEMA='" + tableSchema + "' AND T.TABLE_NAME='" + tableName + "' ORDER BY T.COLUMN_NAME";
rs = stmt.executeQuery(sql);
while(rs.next()) {
tableList.add(rs.getString("COLUMN_NAME"));
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
} finally {
JdbcContext.close(stmt, rs);
}
return tableList;
}
/**
* <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> tableList = new ArrayList<FieldMetadata>();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JdbcContext.getJdbcContext(dataSourceName).currentConnection();
String tableSchema = conn.getCatalog();
stmt = conn.createStatement();
String sql = "SELECT T.ORDINAL_POSITION, T.COLUMN_NAME, T.COLUMN_COMMENT, T.COLUMN_DEFAULT, T.COLUMN_KEY, T.IS_NULLABLE, T.DATA_TYPE, T.CHARACTER_MAXIMUM_LENGTH, T.CHARACTER_OCTET_LENGTH, T.NUMERIC_PRECISION, T.NUMERIC_SCALE, T.COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS T WHERE T.TABLE_CATALOG='DEF' AND T.TABLE_SCHEMA='" + tableSchema + "' AND T.TABLE_NAME='" + tableName + "' ORDER BY T.ORDINAL_POSITION";
// System.out.println(sql);
rs = stmt.executeQuery(sql);
FieldMetadata fieldMetadata = null;
while(rs.next()) {
fieldMetadata = new FieldMetadata();
fieldMetadata.setColumnName(rs.getString("COLUMN_NAME"));
fieldMetadata.setComments(rs.getString("COLUMN_COMMENT"));
fieldMetadata.setDisplayName(rs.getString("COLUMN_COMMENT"));
String dataType = rs.getString("DATA_TYPE");
int dataLength = 0;
int dataPrecision = 0;
int dataScale = 0;
if(MySqlDataTypes.INT_TYPE.equalsIgnoreCase(dataType)
|| MySqlDataTypes.SMALLINT_TYPE.equalsIgnoreCase(dataType)
|| MySqlDataTypes.MEDIUMINT_TYPE.equalsIgnoreCase(dataType)
|| MySqlDataTypes.INT_TYPE.equalsIgnoreCase(dataType)
|| MySqlDataTypes.BIGINT_TYPE.equalsIgnoreCase(dataType)
|| MySqlDataTypes.FLOAT_TYPE.equalsIgnoreCase(dataType)
|| MySqlDataTypes.DOUBLE_TYPE.equalsIgnoreCase(dataType)
|| MySqlDataTypes.NUMERIC_TYPE.equalsIgnoreCase(dataType)
|| MySqlDataTypes.DECIMAL_TYPE.equalsIgnoreCase(dataType)) {
dataLength = 255; //最大长度
dataPrecision = rs.getInt("NUMERIC_PRECISION"); //实际长度
dataScale = rs.getInt("NUMERIC_SCALE");
} else if(MySqlDataTypes.CHAR_TYPE.equalsIgnoreCase(dataType) || MySqlDataTypes.VARCHAR_TYPE.equalsIgnoreCase(dataType)) {
dataLength = rs.getInt("CHARACTER_OCTET_LENGTH"); //最大长度
dataPrecision = rs.getInt("CHARACTER_MAXIMUM_LENGTH"); //实际长度
}
String sqlType = this.dataTypeConverter.convert(dataType, dataLength, dataPrecision, dataScale);
fieldMetadata.setSqlDataType(sqlType);
fieldMetadata.setDataLength(dataLength);
fieldMetadata.setDataPrecision(dataPrecision);
fieldMetadata.setDataScale(dataScale);
String nullableFlag = rs.getString("IS_NULLABLE");
if("YES".equals(nullableFlag)) {
fieldMetadata.setNullable(true);
} else {
fieldMetadata.setNullable(false);
}
fieldMetadata.setDefaultLength(rs.getInt("CHARACTER_OCTET_LENGTH"));
fieldMetadata.setDataDefault(rs.getString("COLUMN_DEFAULT"));
fieldMetadata.setCharLength(rs.getInt("CHARACTER_MAXIMUM_LENGTH"));
fieldMetadata.setPropertyName(NotationUtils.lowerCamelCase(rs.getString("COLUMN_NAME")));
String propertyType = this.getPropertyTypeConverter().convert(sqlType);
fieldMetadata.setPropertyType(propertyType);
String constraintType = rs.getString("COLUMN_KEY"); // 约束类型
if(StringUtils.isNotBlank(constraintType)) {
fieldMetadata.setKeyField(true); // 是否为键
} else {
fieldMetadata.setKeyField(false); // 是否为键
}
if("PRI".equals(constraintType)) {
fieldMetadata.setConstraintType(ConstraintTypes.PK_TYPE);
} else if("REF".equals(constraintType)) {
fieldMetadata.setConstraintType(ConstraintTypes.FK_TYPE);
fieldMetadata.setReferencingTable(rs.getString("R_TABLE_NAME"));
fieldMetadata.setReferencingColumn(rs.getString("R_COLUMN_NAME"));
fieldMetadata.setDeleteRule(rs.getString("DELETE_RULE"));
} else if("CHK".equals(constraintType)) {
fieldMetadata.setConstraintType(ConstraintTypes.CHECK_TYPE);
}
tableList.add(fieldMetadata);
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage(), e);
} finally {
JdbcContext.close(stmt, rs);
}
return tableList;
}
/**
* <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;
}
}