private void fetchPrimaryKeys() {
Vector temp = new Vector(20);
try {
if (cConn == null) {
return;
}
if (dbmeta == null) {
dbmeta = cConn.getMetaData();
}
ResultSet colList = dbmeta.getPrimaryKeys(null, null, tableName);
while (colList.next()) {
temp.addElement(colList.getString("COLUMN_NAME"));
}
colList.close();
} catch (SQLException e) {
ZaurusEditor.printStatus("SQL Exception: " + e.getMessage());
}
primaryKeys = new String[temp.size()];
temp.copyInto(primaryKeys);
pkColIndex = new int[primaryKeys.length];
for (int i = 0; i < primaryKeys.length; i++) {
pkColIndex[i] = this.getColIndex(primaryKeys[i]);
} // end of for (int i=0; i<primaryKeys.length; i++)
}
@Override
public void columnsForTables(Table table, DatabaseMetaData dbmd) {
try {
rs = dbmd.getColumns(null, "%", table.getName(), null);
ResultSet priamryKeyRs = dbmd.getPrimaryKeys(null, null, table.getName());
String priamryKey = null;
while (priamryKeyRs.next()) {
priamryKey = priamryKeyRs.getString("COLUMN_NAME");
}
while (rs.next()) {
Column column = new Column();
String name = rs.getString("COLUMN_NAME");
column.setName(name);
column.setType(rs.getString("TYPE_NAME"));
int columnSize = rs.getInt("COLUMN_SIZE");
int nullable = rs.getInt("nullable");
column.setNullable(nullable == 1);
column.setDataSize(columnSize);
if (column.getName().equals(priamryKey)) {
column.setPrimaryKey(true);
}
table.addColumn(column);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private String getPrimaryKey(
DatabaseMetaData meta, String dbName, String schema, String tableName) throws SQLException {
String pKey = null;
ResultSet resultSet = meta.getPrimaryKeys(dbName, schema, tableName);
if (resultSet.next()) {
resultSet = meta.getPrimaryKeys(dbName, schema, tableName);
} else {
resultSet = meta.getPrimaryKeys(null, schema, tableName);
}
while (resultSet.next()) {
pKey = resultSet.getString(DBConstants.DataServiceGenerator.COLUMN_NAME);
return pKey;
}
return pKey;
}
/** Basic test of DatabaseMetaData functions that access system tables */
public void testTwo() throws Exception {
Connection conn = newConnection();
int updateCount;
try {
TestUtil.testScript(conn, "testrun/hsqldb/TestSelf.txt");
DatabaseMetaData dbmeta = conn.getMetaData();
dbmeta.allProceduresAreCallable();
dbmeta.getBestRowIdentifier(null, null, "T_1", DatabaseMetaData.bestRowTransaction, true);
dbmeta.getCatalogs();
dbmeta.getColumnPrivileges(null, "PUBLIC", "T_1", "%");
dbmeta.getColumns("PUBLIC", "PUBLIC", "T_1", "%");
dbmeta.getCrossReference(null, null, "T_1", null, null, "T_1");
dbmeta.getExportedKeys(null, null, "T_1");
dbmeta.getFunctionColumns(null, "%", "%", "%");
dbmeta.getFunctions(null, "%", "%");
dbmeta.getImportedKeys("PUBLIC", "PUBLIC", "T_1");
dbmeta.getIndexInfo("PUBLIC", "PUBLIC", "T1", true, true);
dbmeta.getPrimaryKeys("PUBLIC", "PUBLIC", "T_1");
dbmeta.getProcedureColumns(null, null, "%", "%");
dbmeta.getProcedures("PUBLIC", "%", "%");
dbmeta.getSchemas(null, "#");
dbmeta.getTablePrivileges(null, "%", "%");
dbmeta.getUDTs(null, "%", "%", new int[] {Types.DISTINCT});
} catch (Exception e) {
assertTrue("unable to prepare or execute DDL", false);
} finally {
conn.close();
}
}
public ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException {
_conn.checkOpen();
try {
return DelegatingResultSet.wrapResultSet(_conn, _meta.getPrimaryKeys(catalog, schema, table));
} catch (SQLException e) {
handleException(e);
throw new AssertionError();
}
}
public List<TableIndex> getIndexes(DatabaseMetaData dbMeta, String tableName)
throws SQLException {
List<TableIndex> indexes = new ArrayList<TableIndex>();
ResultSet pkInfo = null;
String pkName = null;
// ArrayList<String> pkFields = new ArrayList<String>();
ResultSet indexInfo = null;
try {
indexInfo = dbMeta.getIndexInfo(null, dbSchema, tableName, false, true);
// need to ensure that the PK is not returned as an index
pkInfo = dbMeta.getPrimaryKeys(null, dbSchema, tableName);
if (pkInfo.next()) {
pkName = pkInfo.getString("PK_NAME");
}
// Map<Integer,String> tempPk = new HashMap<Integer,String>();
// while ( pkInfo.next() ) {
// tempPk.put( pkInfo.getInt( "KEY_SEQ" ), pkInfo.getString( "COLUMN_NAME" ) );
// }
TableIndex currIndex = null;
while (indexInfo.next()) {
if (indexInfo.getString("INDEX_NAME") == null) continue;
// System.out.println( "Row: " + indexInfo.getString( "INDEX_NAME" ) + "/" +
// indexInfo.getString( "COLUMN_NAME" ) );
if (currIndex == null || !indexInfo.getString("INDEX_NAME").equals(currIndex.name)) {
currIndex = new TableIndex();
currIndex.name = indexInfo.getString("INDEX_NAME");
currIndex.unique = !indexInfo.getBoolean("NON_UNIQUE");
// if has the same name as the PK, skip adding it to the index list
if (pkName == null || !pkName.equals(currIndex.name)) {
indexes.add(currIndex);
// System.out.println( "Added " + currIndex.name + " to index list");
} else {
// System.out.println( "Skipping PK: " + currIndex.name );
}
}
currIndex.columns.add(indexInfo.getString("COLUMN_NAME"));
}
} catch (SQLException e) {
log(
"WARN: Could not read indexes for Table " + tableName + " : " + e.getMessage(),
Project.MSG_WARN);
} finally {
if (indexInfo != null) {
indexInfo.close();
}
if (pkInfo != null) {
pkInfo.close();
}
}
return indexes;
}
/** Returns a table's primary key columns as a Set of strings. */
public static Set<String> getPrimaryKey(DatabaseMetaData metadata, String tableName)
throws Exception {
Set<String> columns = new HashSet<String>();
ResultSet keys =
metadata.getPrimaryKeys(
metadata.getConnection().getCatalog(), metadata.getUserName(), tableName);
while (keys.next()) {
columns.add(keys.getString(PRIMARY_PK_COL_NAME));
}
keys.close();
return columns;
}
/**
* @param meta
* @throws SQLException
*/
private void initPrimaryKeys(DatabaseMetaData meta) throws SQLException {
if (properties == null) return;
ResultSet rs = null;
try {
rs = meta.getPrimaryKeys(null, getSchema(), getName());
while (rs.next()) setPrimaryColumn(rs);
} finally {
if (rs != null) rs.close();
}
}
public void generate(String tableName) throws SQLException, IOException {
String schemaName = null;
int idx = tableName.indexOf('.');
if (idx != -1) {
schemaName = tableName.substring(0, idx);
tableName = tableName.substring(idx + 1);
}
DatabaseMetaData meta = con.getMetaData();
Set<String> keySet = new HashSet<String>();
ResultSet rs = meta.getPrimaryKeys(null, schemaName, tableName);
try {
while (rs.next()) {
String name = rs.getString(4);
keySet.add(name);
}
} finally {
rs.close();
}
TableInfo table = new TableInfo(tableName);
rs = meta.getColumns(null, schemaName, tableName, "%");
try {
while (rs.next()) {
String name = rs.getString(4);
int type = rs.getInt(5);
boolean pk = keySet.contains(name);
boolean autoInc = "YES".equals(rs.getString(23));
ColumnInfo col = new ColumnInfo(name, type, pk);
table.addColumn(col);
if (pk && keySet.size() == 1 && autoInc) {
table.setUseSerialKey(true);
}
}
} finally {
rs.close();
}
File outputFile = new File(this.outputDir, table.getClazzName() + ".java");
Writer writer = new OutputStreamWriter(new FileOutputStream(outputFile), "utf-8");
try {
Template template = engine.getTemplate("jp/co/flect/sql/Table.template");
VelocityContext context = new VelocityContext();
context.put("packageName", this.packageName);
context.put("superClazz", this.superClass);
context.put("table", table);
template.merge(context, writer);
} finally {
writer.close();
}
}
public CompositeMap getPrimaryKeys() throws SQLException {
CompositeMap primaryKeyArray =
new CommentCompositeMap(BMUtil.BMPrefix, AuroraConstant.BMUri, "primary-key");
String tableName = getTableName();
if (tableName == null) return primaryKeyArray;
DatabaseMetaData dbMetaData = getDBMetaData();
ResultSet tableRet = dbMetaData.getPrimaryKeys(null, dbMetaData.getUserName(), tableName);
while (tableRet.next()) {
CompositeMap field =
new CommentCompositeMap(BMUtil.BMPrefix, AuroraConstant.BMUri, "pk-field");
field.put("name", tableRet.getString("COLUMN_NAME").toLowerCase());
primaryKeyArray.addChild(field);
}
return primaryKeyArray;
}
/**
* Get a list of all primary keys that are defined for the table.
*
* @param conn database connection
* @param schema schema name
* @param table table name
* @return list of all primary keys
* @throws SQLException Thrown if an SQL statement failed to be executed.
*/
private static String[] getPrimaryKeys(
final Connection conn, final String schema, final String table) throws SQLException {
final ArrayList<String> result = new ArrayList<String>();
final DatabaseMetaData metaData = conn.getMetaData();
ResultSet rs = null;
try {
rs = metaData.getPrimaryKeys(conn.getCatalog(), schema, table);
while (rs.next()) {
final StringBuilder indexInfo = new StringBuilder();
for (int index = 4; index <= 6; index++) {
if (indexInfo.length() > 0) {
indexInfo.append('/');
}
indexInfo.append(rs.getString(index));
}
result.add(indexInfo.toString());
}
} finally {
IOUtils.closeResultSet(rs);
}
return result.toArray(new String[result.size()]);
}
public void refreshPrimaryKeys(DatabaseMetaData metaData) throws SQLException {
primaryKeys.clear();
ResultSet rs = null;
try {
rs = metaData.getPrimaryKeys(catalog, schema, name);
while (rs.next()) {
PrimaryKey pk = new PrimaryKey();
primaryKeys.add(pk);
pk.setOwner(this);
pk.setColumnName(rs.getString("COLUMN_NAME"));
pk.setKeySequence(rs.getShort("KEY_SEQ"));
pk.setName(rs.getString("PK_NAME"));
}
} catch (SQLException sqle) {
throw sqle;
} finally {
try {
rs.close();
} catch (Exception ex) {
}
}
}
public List<Table> queryFields(DBConfig config) {
List<Table> tableInfoList = new ArrayList<Table>();
Connection conn = null;
try {
List<TableConfiguration> tableList = (List<TableConfiguration>) config.getTableList();
if (tableList == null || tableList.size() <= 0) return tableInfoList;
conn = ConnectionFactory.getInstance().getConnection(config);
DatabaseMetaData databaseMetaData = conn.getMetaData();
for (TableConfiguration table : tableList) {
Table tableInfo = new Table();
String localCatalog = table.getCatalog();
String localSchema = table.getSchema();
String localTableName = table.getTableName();
if (databaseMetaData.storesLowerCaseIdentifiers()) {
localCatalog = localCatalog == null ? null : localCatalog.toLowerCase();
localSchema = localSchema == null ? null : localSchema.toLowerCase();
localTableName = localTableName == null ? null : localTableName.toLowerCase();
} else if (databaseMetaData.storesUpperCaseIdentifiers()) {
localCatalog = localCatalog == null ? null : localCatalog.toUpperCase();
localSchema = localSchema == null ? null : localSchema.toUpperCase();
localTableName = localTableName == null ? null : localTableName.toUpperCase();
}
Statement stmt = conn.createStatement();
ResultSet tableRs = stmt.executeQuery("SHOW CREATE TABLE " + localTableName);
if (tableRs != null && tableRs.next()) {
String create = tableRs.getString(2);
String comment = parse(create);
tableInfo.setComment(comment);
}
ResultSet rs = databaseMetaData.getColumns(localCatalog, localSchema, localTableName, null);
tableInfo.setSerialVersionUID(System.nanoTime() + "L");
while (rs.next()) {
tableInfo.setCatalog(rs.getString("TABLE_CAT"));
tableInfo.setSchema(rs.getString("TABLE_SCHEM"));
tableInfo.setName(rs.getString("TABLE_NAME"));
tableInfo.setCode(rs.getString("TABLE_NAME"));
Column introspectedColumn = new Column();
introspectedColumn.setTableAlias(table.getTableName());
introspectedColumn.setName(rs.getString("COLUMN_NAME"));
introspectedColumn.setJdbcType(rs.getInt("DATA_TYPE"));
introspectedColumn.setDataType(
JdbcTypeNameTranslator.getJdbcTypeName(rs.getInt("DATA_TYPE"))); // $NON-NLS-1$
introspectedColumn.setLength(rs.getInt("COLUMN_SIZE")); // $NON-NLS-1$
introspectedColumn.setCode(rs.getString("COLUMN_NAME"));
/*introspectedColumn.setActualColumnName(rs
.getString("COLUMN_NAME"));*/
//$NON-NLS-1$
introspectedColumn.setNullable(
rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable); // $NON-NLS-1$
introspectedColumn.setScale(rs.getInt("DECIMAL_DIGITS")); // $NON-NLS-1$
introspectedColumn.setComment(rs.getString("REMARKS"));
tableInfo.addColumn(introspectedColumn);
PropertyBean pb = new PropertyBean();
pb.setName(convertFirstUpper(getFieldName(rs.getString("COLUMN_NAME"))));
pb.setType(JdbcType2Java.calculateJavaType(introspectedColumn));
String importType = JdbcType2Java.importJavaType(introspectedColumn);
if (importType != null && !importType.equals("")) {
if (importType.indexOf("java.lang") < 0
&& !tableInfo.getImportList().contains(importType))
tableInfo.getImportList().add(importType);
}
tableInfo.getPropertyBeanList().add(pb);
}
closeResultSet(rs);
rs = databaseMetaData.getPrimaryKeys(localCatalog, localSchema, localTableName);
while (rs.next()) {
tableInfo.addPrimaryKeyColumn(rs.getString("COLUMN_NAME"));
}
closeResultSet(rs);
tableInfoList.add(tableInfo);
}
} catch (Exception e) {
e.printStackTrace();
}
return tableInfoList;
}
public void copyDbData(String dbin, String dbout) throws SQLException {
// Do some connections and collect statements
Connection inConn = makeConnection(dbin);
Statement inSt = inConn.createStatement();
DatabaseMetaData dbmeta = inConn.getMetaData();
Connection outConn = makeConnection(dbout);
Statement outSt = outConn.createStatement();
ResultSet tables = getTables(inConn);
forEach(
tables,
rs -> {
try {
// Okay, we'v got our table name
String tableName = rs.getString("TABLE_NAME");
// PreparedStatement st = DriverManager.getConnection(addr, user,
// passwd).prepareStatement(format("CREATE TABLE %s.%s (LIKE %s.%s)", dbout, tableName,
// dbin, tableName));
// st.execute();
// st.close();
// Lets fetch all data from it
ResultSet data = inSt.executeQuery(format("SELECT * FROM \"%s\"", tableName));
// Lets get table schema from metadata and generate creation operator
ResultSetMetaData datamd = data.getMetaData();
int columnCount = datamd.getColumnCount();
StringBuilder sb = new StringBuilder();
sb.append("CREATE TABLE ").append(tableName).append(" (");
for (int i = 1; i <= columnCount; i++) {
if (i > 1) sb.append(", ");
sb.append(datamd.getColumnName(i)).append(" ").append(datamd.getColumnTypeName(i));
if (datamd.isNullable(i) == ResultSetMetaData.columnNoNulls) sb.append(" NOT NULL");
// Tons of parameters could be parsed here except of references and keys
// if (precision != 0) {
// sb.append("(").append(precision).append(")");
// }
switch (datamd.getColumnTypeName(i)) {
case "character":
case "time":
case "":
sb.append("(").append(datamd.getPrecision(i)).append(")");
}
}
sb.append(");");
// Looks good, lets create this in our outer database
LOGGER.info("Execute: '{}'", sb.toString());
outSt.execute(sb.toString());
// Setting up primary keys
Map<String, StringBuilder> primaryKeys = new HashMap<>();
ResultSet primaryKeysRs = dbmeta.getPrimaryKeys(null, null, tableName);
forEach(
primaryKeysRs,
pkRs -> {
try {
String columName = pkRs.getString("COLUMN_NAME");
String pkName = pkRs.getString("PK_NAME");
if (!primaryKeys.containsKey(pkName)) {
primaryKeys.put(pkName, new StringBuilder(columName));
} else {
primaryKeys.get(pkName).append(", ").append(columName);
}
} catch (SQLException e) {
// Should never be reached
e.printStackTrace();
}
});
primaryKeys
.entrySet()
.forEach(
entry -> {
String pkName = entry.getKey();
String pk = entry.getValue().toString();
String query = format("ALTER TABLE %s ADD PRIMARY KEY (%s)", tableName, pk);
try {
LOGGER.info("Execute: '{}'", query);
outSt.executeUpdate(query);
} catch (SQLException e) {
LOGGER.error("Setting '{}' as primary key in '{}' failed", pk, tableName);
e.printStackTrace();
}
});
// Generate insertion statement
StringBuilder prepareQuery = new StringBuilder("INSERT INTO ");
prepareQuery.append(tableName).append(" VALUES (");
for (int i = 0; i < columnCount; i++) {
if (i > 0) prepareQuery.append(", ");
prepareQuery.append("?");
}
prepareQuery.append(");");
LOGGER.info("Prepare statement with: {}", prepareQuery);
PreparedStatement pst = outConn.prepareStatement(prepareQuery.toString());
// And put all our's data in it
forEach(
data,
dataRs -> {
try {
for (int i = 1; i <= columnCount; i++) {
pst.setObject(i, dataRs.getObject(i));
}
LOGGER.info("Execute: '{}'", pst.toString());
pst.execute();
} catch (SQLException e) {
e.printStackTrace();
}
/*
try {
StringBuilder insert = new StringBuilder();
insert.append("INSERT INTO \"").append(tableName).append("\" VALUES (");
for (int i = 1; i <= columnCount; i++) {
if (i > 1) insert.append(", ");
insert.append(dataRs.getString(i));
}
insert.append(");");
// Finally put insert it in table
// Timestamps would be inserted incorrect
LOGGER.info("Execute: '{}'", insert.toString());
outSt.executeUpdate(insert.toString());
} catch (SQLException e) {
LOGGER.error("Failed to insert into '{}'", tableName);
}
*/
});
} catch (SQLException e) {
LOGGER.error("Failed to create table");
e.printStackTrace();
}
});
// Lets deal up with foreign keys
tables.beforeFirst();
forEach(
tables,
rs -> {
try {
String tableName = rs.getString("TABLE_NAME");
ResultSet foreignKeysRs = dbmeta.getImportedKeys(null, null, tableName);
Map<String, String> pkTables = new HashMap<>();
Map<String, StringBuilder> pkKeys = new HashMap<>();
Map<String, StringBuilder> fkKeys = new HashMap<>();
forEach(
foreignKeysRs,
keyRs -> {
try {
String pkTable = keyRs.getString("PKTABLE_NAME");
String pkColumn = keyRs.getString("PKCOLUMN_NAME");
String fkColumn = keyRs.getString("FKCOLUMN_NAME");
String fkName = keyRs.getString("FK_NAME");
if (!pkTables.containsKey(fkName)) {
pkTables.put(fkName, pkTable);
pkKeys.put(fkName, new StringBuilder(pkColumn));
fkKeys.put(fkName, new StringBuilder(fkColumn));
} else {
pkKeys.get(fkName).append(", ").append(pkColumn);
fkKeys.get(fkName).append(", ").append(fkColumn);
}
} catch (SQLException e) {
e.printStackTrace();
// Should newer be reached
}
});
pkTables
.entrySet()
.forEach(
entry -> {
String keyName = entry.getKey();
String extTableName = entry.getValue();
String alterFK =
format(
"ALTER TABLE %s ADD FOREIGN KEY (%s) REFERENCES %s(%s);",
tableName,
// keyName,
fkKeys.get(keyName),
extTableName,
pkKeys.get(keyName));
LOGGER.info("Execute: '{}'", alterFK);
try {
outSt.executeUpdate(alterFK);
} catch (SQLException e) {
e.printStackTrace();
}
});
} catch (SQLException e) {
e.printStackTrace();
}
});
}
/** Dump the whole database to an SQL string */
public static void dumpDB(ArrayList<String> tab, Connection dbConn, String filePath) {
String columnNameQuote = "`";
BufferedWriter out = null;
try {
out = new BufferedWriter(new FileWriter(filePath));
DatabaseMetaData dbMetaData = dbConn.getMetaData();
StringBuffer result;
ResultSet rs = dbMetaData.getTables(null, null, null, new String[] {"TABLE"});
if (!rs.next()) {
System.err.println("Unable to find any tables");
rs.close();
} else {
// Right, we have some tables, so we can go to work.
// the details we have are
// TABLE_CAT String => table catalog (may be null)
// TABLE_SCHEM String => table schema (may be null)
// TABLE_NAME String => table name
// TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE",
// "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
// REMARKS String => explanatory comment on the table
// TYPE_CAT String => the types catalog (may be null)
// TYPE_SCHEM String => the types schema (may be null)
// TYPE_NAME String => type name (may be null)
// SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed
// table (may be null)
// REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created.
// Values are "SYSTEM", "USER", "DERIVED". (may be null)
// We will ignore the schema and stuff, because people might want to import it somewhere
// else
// We will also ignore any tables that aren't of type TABLE for now.
// We use a do-while because we've already caled rs.next to see if there are any rows
do {
result = new StringBuffer();
String tableName = rs.getString("TABLE_NAME");
if (tab != null && !tab.contains(tableName)) {
continue;
}
String tableType = rs.getString("TABLE_TYPE");
if ("TABLE".equalsIgnoreCase(tableType)) {
result.append("\n\n-- " + tableName);
result.append(
"\nCREATE TABLE " + columnNameQuote + tableName + columnNameQuote + " (\n");
ResultSet tableMetaData = dbMetaData.getColumns(null, null, tableName, "%");
boolean firstLine = true;
while (tableMetaData.next()) {
if (firstLine) {
firstLine = false;
} else {
// If we're not the first line, then finish the previous line with a comma
result.append(",\n");
}
String columnName = tableMetaData.getString("COLUMN_NAME");
String columnType = tableMetaData.getString("TYPE_NAME");
// WARNING: this may give daft answers for some types on some databases (eg JDBC-ODBC
// link)
int columnSize = tableMetaData.getInt("COLUMN_SIZE");
String nullable = tableMetaData.getString("IS_NULLABLE");
String nullString = "NULL";
if ("NO".equalsIgnoreCase(nullable)) {
nullString = "NOT NULL";
}
result.append(
" "
+ columnNameQuote
+ columnName
+ columnNameQuote
+ " "
+ columnType
+ " ("
+ columnSize
+ ")"
+ " "
+ nullString);
}
tableMetaData.close();
// Now we need to put the primary key constraint
try {
ResultSet primaryKeys = dbMetaData.getPrimaryKeys(null, null, tableName);
// What we might get:
// TABLE_CAT String => table catalog (may be null)
// TABLE_SCHEM String => table schema (may be null)
// TABLE_NAME String => table name
// COLUMN_NAME String => column name
// KEY_SEQ short => sequence number within primary key
// PK_NAME String => primary key name (may be null)
String primaryKeyName = null;
StringBuffer primaryKeyColumns = new StringBuffer();
while (primaryKeys.next()) {
String thisKeyName = primaryKeys.getString("PK_NAME");
if ((thisKeyName != null && primaryKeyName == null)
|| (thisKeyName == null && primaryKeyName != null)
|| (thisKeyName != null && !thisKeyName.equals(primaryKeyName))
|| (primaryKeyName != null && !primaryKeyName.equals(thisKeyName))) {
// the keynames aren't the same, so output all that we have so far (if anything)
// and start a new primary key entry
if (primaryKeyColumns.length() > 0) {
// There's something to output
result.append(",\n PRIMARY KEY ");
if (primaryKeyName != null) {
result.append(primaryKeyName);
}
result.append("(" + primaryKeyColumns.toString() + ")");
}
// Start again with the new name
primaryKeyColumns = new StringBuffer();
primaryKeyName = thisKeyName;
}
// Now append the column
if (primaryKeyColumns.length() > 0) {
primaryKeyColumns.append(", ");
}
primaryKeyColumns.append(primaryKeys.getString("COLUMN_NAME"));
}
if (primaryKeyColumns.length() > 0) {
// There's something to output
result.append(",\n PRIMARY KEY ");
if (primaryKeyName != null) {
result.append(primaryKeyName);
}
result.append(" (" + primaryKeyColumns.toString() + ")");
}
} catch (SQLException e) {
// NB you will get this exception with the JDBC-ODBC link because it says
// [Microsoft][ODBC Driver Manager] Driver does not support this function
System.err.println(
"Unable to get primary keys for table " + tableName + " because " + e);
}
result.append("\n);\n");
// Right, we have a table, so we can go and dump it
dumpTable(dbConn, result, tableName);
out.write(result.toString());
}
} while (rs.next());
rs.close();
}
dbConn.close();
out.close();
} catch (IOException ex) {
Logger.getLogger(Db2Sql.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException e) {
e.printStackTrace(); // To change body of catch statement use Options | File Templates.
}
return;
}
/** INTERNAL */
@Override
public void init(
Connection conn,
String schemaName,
String triggerName,
String tableName,
boolean before,
int type)
throws SQLException {
this.schema = schemaName;
this.table = tableName;
this.indexPath = getIndexPath(conn);
this.indexAccess = getIndexAccess(conn);
ArrayList<String> keyList = New.arrayList();
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs =
meta.getColumns(
null,
StringUtils.escapeMetaDataPattern(schemaName),
StringUtils.escapeMetaDataPattern(tableName),
null);
ArrayList<String> columnList = New.arrayList();
while (rs.next()) {
columnList.add(rs.getString("COLUMN_NAME"));
}
columnTypes = new int[columnList.size()];
columns = new String[columnList.size()];
columnList.toArray(columns);
rs =
meta.getColumns(
null,
StringUtils.escapeMetaDataPattern(schemaName),
StringUtils.escapeMetaDataPattern(tableName),
null);
for (int i = 0; rs.next(); i++) {
columnTypes[i] = rs.getInt("DATA_TYPE");
}
if (keyList.size() == 0) {
rs = meta.getPrimaryKeys(null, StringUtils.escapeMetaDataPattern(schemaName), tableName);
while (rs.next()) {
keyList.add(rs.getString("COLUMN_NAME"));
}
}
if (keyList.size() == 0) {
throw throwException("No primary key for table " + tableName);
}
ArrayList<String> indexList = New.arrayList();
PreparedStatement prep =
conn.prepareStatement(
"SELECT COLUMNS FROM " + SCHEMA + ".INDEXES WHERE SCHEMA=? AND TABLE=?");
prep.setString(1, schemaName);
prep.setString(2, tableName);
rs = prep.executeQuery();
if (rs.next()) {
String cols = rs.getString(1);
if (cols != null) {
for (String s : StringUtils.arraySplit(cols, ',', true)) {
indexList.add(s);
}
}
}
if (indexList.size() == 0) {
indexList.addAll(columnList);
}
keys = new int[keyList.size()];
setColumns(keys, keyList, columnList);
indexColumns = new int[indexList.size()];
setColumns(indexColumns, indexList, columnList);
}
/**
* Reads the DatabaseMetaData for the details of this table including primary keys and indexes.
*
* @param metaData the database meta data
*/
void read(DatabaseMetaData metaData) throws SQLException {
ResultSet rs = null;
// primary keys
try {
rs = metaData.getPrimaryKeys(null, schema, table);
while (rs.next()) {
String c = rs.getString("COLUMN_NAME");
primaryKeys.add(c);
}
closeSilently(rs);
// indexes
rs = metaData.getIndexInfo(null, schema, table, false, true);
indexes = Utils.newHashMap();
while (rs.next()) {
IndexInspector info = new IndexInspector(rs);
if (info.type.equals(IndexType.UNIQUE)) {
String name = info.name.toLowerCase();
if (name.startsWith("primary")
|| name.startsWith("sys_idx_sys_pk")
|| name.startsWith("sql")
|| name.endsWith("_pkey")) {
// skip primary key indexes
continue;
}
}
if (indexes.containsKey(info.name)) {
indexes.get(info.name).addColumn(rs);
} else {
indexes.put(info.name, info);
}
}
closeSilently(rs);
// columns
rs = metaData.getColumns(null, schema, table, null);
columns = Utils.newHashMap();
while (rs.next()) {
ColumnInspector col = new ColumnInspector();
col.name = rs.getString("COLUMN_NAME");
col.type = rs.getString("TYPE_NAME");
col.clazz = ModelUtils.getClassForSqlType(col.type, dateTimeClass);
col.size = rs.getInt("COLUMN_SIZE");
col.nullable = rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable;
try {
Object autoIncrement = rs.getObject("IS_AUTOINCREMENT");
if (autoIncrement instanceof Boolean) {
col.isAutoIncrement = (Boolean) autoIncrement;
} else if (autoIncrement instanceof String) {
String val = autoIncrement.toString().toLowerCase();
col.isAutoIncrement = val.equals("true") | val.equals("yes");
} else if (autoIncrement instanceof Number) {
Number n = (Number) autoIncrement;
col.isAutoIncrement = n.intValue() > 0;
}
} catch (SQLException s) {
// throw s;
}
if (primaryKeys.size() == 1) {
if (col.name.equalsIgnoreCase(primaryKeys.get(0))) {
col.isPrimaryKey = true;
}
}
if (!col.isAutoIncrement) {
col.defaultValue = rs.getString("COLUMN_DEF");
}
columns.put(col.name.toLowerCase(), col);
}
} finally {
closeSilently(rs);
}
}
@Test
public void testPrimaryKeys() throws Exception {
TestMMDatabaseMetaData.compareResultSet(
dbMetadata.getPrimaryKeys(VDB, null, "%")); // $NON-NLS-1$ //$NON-NLS-2$
}
public ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException {
return throwExceptionDelegate.getPrimaryKeys(catalog, schema, table);
}
@Test
public void columnOrderOfgetPrimaryKeys() throws SQLException {
ResultSet rs;
ResultSetMetaData rsmeta;
stat.executeUpdate("create table nopk (c1, c2, c3, c4);");
stat.executeUpdate("create table pk1 (col1 primary key, col2, col3);");
stat.executeUpdate("create table pk2 (col1, col2 primary key, col3);");
stat.executeUpdate("create table pk3 (col1, col2, col3, col4, primary key (col3, col2 ));");
// extra spaces and mixed case are intentional, do not remove!
stat.executeUpdate(
"create table pk4 (col1, col2, col3, col4, "
+ "\r\nCONSTraint\r\nnamed primary\r\n\t\t key (col3, col2 ));");
rs = meta.getPrimaryKeys(null, null, "nopk");
assertFalse(rs.next());
rsmeta = rs.getMetaData();
assertEquals(rsmeta.getColumnCount(), 6);
assertEquals(rsmeta.getColumnName(1), "TABLE_CAT");
assertEquals(rsmeta.getColumnName(2), "TABLE_SCHEM");
assertEquals(rsmeta.getColumnName(3), "TABLE_NAME");
assertEquals(rsmeta.getColumnName(4), "COLUMN_NAME");
assertEquals(rsmeta.getColumnName(5), "KEY_SEQ");
assertEquals(rsmeta.getColumnName(6), "PK_NAME");
rs.close();
rs = meta.getPrimaryKeys(null, null, "pk1");
assertTrue(rs.next());
assertEquals(rs.getString("PK_NAME"), null);
assertEquals(rs.getString("COLUMN_NAME"), "col1");
assertFalse(rs.next());
rs.close();
rs = meta.getPrimaryKeys(null, null, "pk2");
assertTrue(rs.next());
assertEquals(rs.getString("PK_NAME"), null);
assertEquals(rs.getString("COLUMN_NAME"), "col2");
assertFalse(rs.next());
rs.close();
rs = meta.getPrimaryKeys(null, null, "pk3");
assertTrue(rs.next());
assertEquals(rs.getString("COLUMN_NAME"), "col2");
assertEquals(rs.getString("PK_NAME"), null);
assertEquals(rs.getInt("KEY_SEQ"), 1);
assertTrue(rs.next());
assertEquals(rs.getString("COLUMN_NAME"), "col3");
assertEquals(rs.getString("PK_NAME"), null);
assertEquals(rs.getInt("KEY_SEQ"), 0);
assertFalse(rs.next());
rs.close();
rs = meta.getPrimaryKeys(null, null, "pk4");
assertTrue(rs.next());
assertEquals(rs.getString("COLUMN_NAME"), "col2");
assertEquals(rs.getString("PK_NAME"), "named");
assertEquals(rs.getInt("KEY_SEQ"), 1);
assertTrue(rs.next());
assertEquals(rs.getString("COLUMN_NAME"), "col3");
assertEquals(rs.getString("PK_NAME"), "named");
assertEquals(rs.getInt("KEY_SEQ"), 0);
assertFalse(rs.next());
rs.close();
}
public void testOne() throws Exception {
Connection conn = newConnection();
PreparedStatement pstmt;
int updateCount;
try {
pstmt = conn.prepareStatement("DROP TABLE t1 IF EXISTS");
pstmt.executeUpdate();
pstmt.close();
pstmt =
conn.prepareStatement(
"CREATE TABLE t1 (cha CHARACTER, dec DECIMAL, doub DOUBLE, lon BIGINT, \"IN\" INTEGER, sma SMALLINT, tin TINYINT, "
+ "dat DATE DEFAULT CURRENT_DATE, tim TIME DEFAULT CURRENT_TIME, timest TIMESTAMP DEFAULT CURRENT_TIMESTAMP );");
updateCount = pstmt.executeUpdate();
assertTrue("expected update count of zero", updateCount == 0);
pstmt = conn.prepareStatement("CREATE INDEX t1 ON t1 (cha );");
updateCount = pstmt.executeUpdate();
pstmt = conn.prepareStatement("DROP TABLE t2 IF EXISTS");
updateCount = pstmt.executeUpdate();
pstmt =
conn.prepareStatement(
"CREATE TABLE t2 (cha CHARACTER, dec DECIMAL, doub DOUBLE, lon BIGINT, \"IN\" INTEGER, sma SMALLINT, tin TINYINT, "
+ "dat DATE DEFAULT CURRENT_DATE, tim TIME DEFAULT CURRENT_TIME, timest TIMESTAMP DEFAULT CURRENT_TIMESTAMP );");
updateCount = pstmt.executeUpdate();
pstmt = conn.prepareStatement("CREATE INDEX t2 ON t2 (cha );");
updateCount = pstmt.executeUpdate();
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rsp = dbmd.getTablePrivileges(null, null, "T1");
while (rsp.next()) {
System.out.println("Table: " + rsp.getString(3) + " priv: " + rsp.getString(6));
}
rsp = dbmd.getIndexInfo(null, null, "T1", false, false);
while (rsp.next()) {
System.out.println("Table: " + rsp.getString(3) + " IndexName: " + rsp.getString(6));
}
rsp = dbmd.getIndexInfo(null, null, "T2", false, false);
while (rsp.next()) {
System.out.println("Table: " + rsp.getString(3) + " IndexName: " + rsp.getString(6));
}
pstmt = conn.prepareStatement("DROP INDEX t2;");
updateCount = pstmt.executeUpdate();
rsp = dbmd.getIndexInfo(null, null, "T2", false, false);
assertTrue("expected getIndexInfo returns empty resultset", rsp.next() == false);
ResultSet rs = dbmd.getTables(null, null, "T1", new String[] {"TABLE"});
ArrayList tablesarr = new ArrayList();
int i;
for (i = 0; rs.next(); i++) {
String tempstr = rs.getString("TABLE_NAME").trim().toLowerCase();
tablesarr.add(tempstr);
}
rs.close();
assertTrue("expected table t1 count of 1", i == 1);
Iterator it = tablesarr.iterator();
for (; it.hasNext(); ) {
// create new ArrayList and HashMap for the table
String tablename = ((String) it.next()).trim();
List collist = new ArrayList(30);
rs = dbmd.getColumns(null, null, tablename.toUpperCase(), null);
for (i = 0; rs.next(); i++) {
collist.add(rs.getString("COLUMN_NAME").trim().toLowerCase());
}
rs.close();
}
pstmt = conn.prepareStatement("DROP TABLE t_1 IF EXISTS");
pstmt.executeUpdate();
pstmt.close();
pstmt =
conn.prepareStatement(
"CREATE TABLE t_1 (cha CHARACTER(10), deci DECIMAL(10,2), doub DOUBLE, lon BIGINT, \"IN\" INTEGER, sma SMALLINT, tin TINYINT, "
+ "dat DATE DEFAULT CURRENT_DATE, tim TIME DEFAULT CURRENT_TIME, timest TIMESTAMP DEFAULT CURRENT_TIMESTAMP, bool BOOLEAN );");
updateCount = pstmt.executeUpdate();
assertTrue("expected update count of zero", updateCount == 0);
rs = dbmd.getTables(null, null, "T\\_1", new String[] {"TABLE"});
for (i = 0; rs.next(); i++) {
String tempstr = rs.getString("TABLE_NAME").trim().toLowerCase();
tablesarr.add(tempstr);
}
rs.close();
assertTrue("expected table t_1 count of 1", i == 1);
// test various methods
dbmd.getPrimaryKeys(null, null, "T_1");
dbmd.getImportedKeys(null, null, "T_1");
dbmd.getCrossReference(null, null, "T_1", null, null, "T_1");
// test ResultSetMetaData
pstmt = conn.prepareStatement("INSERT INTO T_1 (cha, deci, doub) VALUES ('name', 10.23, 0)");
pstmt.executeUpdate();
pstmt.close();
pstmt = conn.prepareStatement("SELECT * FROM T_1");
rs = pstmt.executeQuery();
ResultSetMetaData md = rs.getMetaData();
int x = md.getColumnDisplaySize(1);
int y = md.getColumnDisplaySize(2);
int b = md.getPrecision(2);
int c = md.getScale(1);
int d = md.getScale(2);
String e = md.getColumnClassName(10);
boolean testresult =
(x == 10)
&& (y == 12)
&& (b == 10)
&& (c == 0)
&& (d == 2)
&& e.equals("java.sql.Timestamp");
assertTrue("wrong result metadata", testresult);
e = md.getColumnClassName(11);
testresult = e.equals("java.lang.Boolean");
assertTrue("wrong result metadata", testresult);
pstmt.close();
//
} catch (Exception e) {
assertTrue("unable to prepare or execute DDL", false);
} finally {
conn.close();
}
}
/**
* Read metadata about a table from the database.
*
* @param table The RDBMS table.
* @return A map of information about the columns. The key is the name of the column, a String;
* the value is a ColumnInfo object.
* @exception SQLException If there is a problem retrieving information from the RDBMS.
*/
private static Map<String, ColumnInfo> retrieveColumnInfo(String table) throws SQLException {
Connection connection = null;
ResultSet pkcolumns = null;
ResultSet columns = null;
try {
String schema = ConfigurationManager.getProperty("db.schema");
if (StringUtils.isBlank(schema)) {
schema = null;
}
String catalog = null;
int dotIndex = table.indexOf('.');
if (dotIndex > 0) {
catalog = table.substring(0, dotIndex);
table = table.substring(dotIndex + 1, table.length());
log.warn("catalog: " + catalog);
log.warn("table: " + table);
}
connection = getConnection();
DatabaseMetaData metadata = connection.getMetaData();
Map<String, ColumnInfo> results = new HashMap<String, ColumnInfo>();
int max = metadata.getMaxTableNameLength();
String tname = ((max > 0) && (table.length() >= max)) ? table.substring(0, max - 1) : table;
pkcolumns = metadata.getPrimaryKeys(catalog, schema, tname);
Set<String> pks = new HashSet<String>();
while (pkcolumns.next()) {
pks.add(pkcolumns.getString(4));
}
columns = metadata.getColumns(catalog, schema, tname, null);
while (columns.next()) {
String column = columns.getString(4);
ColumnInfo cinfo = new ColumnInfo();
cinfo.setName(column);
cinfo.setType((int) columns.getShort(5));
if (pks.contains(column)) {
cinfo.setIsPrimaryKey(true);
}
results.put(column, cinfo);
}
return Collections.unmodifiableMap(results);
} finally {
if (pkcolumns != null) {
try {
pkcolumns.close();
} catch (SQLException sqle) {
}
}
if (columns != null) {
try {
columns.close();
} catch (SQLException sqle) {
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException sqle) {
}
}
}
}
protected ResultSet getPrimaryKeyResultSet(
DatabaseMetaData md, String schemaName, String tableName) throws SQLException {
return md.getPrimaryKeys(schemaName, null, tableName);
}
protected void alter(Class<?> tableClass, Alterable... alterables) throws Exception {
try (LoggingTimer loggingTimer = new LoggingTimer()) {
Field tableNameField = tableClass.getField("TABLE_NAME");
String tableName = (String) tableNameField.get(null);
DatabaseMetaData databaseMetaData = connection.getMetaData();
try (ResultSet rs1 = databaseMetaData.getPrimaryKeys(null, null, tableName);
ResultSet rs2 =
databaseMetaData.getIndexInfo(
null, null, normalizeName(tableName, databaseMetaData), false, false)) {
Set<String> primaryKeyNames = new HashSet<>();
while (rs1.next()) {
String primaryKeyName = StringUtil.toUpperCase(rs1.getString("PK_NAME"));
if (primaryKeyName != null) {
primaryKeyNames.add(primaryKeyName);
}
}
Map<String, Set<String>> columnNamesMap = new HashMap<>();
while (rs2.next()) {
String indexName = StringUtil.toUpperCase(rs2.getString("INDEX_NAME"));
if ((indexName == null) || primaryKeyNames.contains(indexName)) {
continue;
}
Set<String> columnNames = columnNamesMap.get(indexName);
if (columnNames == null) {
columnNames = new HashSet<>();
columnNamesMap.put(indexName, columnNames);
}
columnNames.add(StringUtil.toUpperCase(rs2.getString("COLUMN_NAME")));
}
for (Alterable alterable : alterables) {
for (Map.Entry<String, Set<String>> entry : columnNamesMap.entrySet()) {
if (alterable.shouldDropIndex(entry.getValue())) {
runSQL("drop index " + entry.getKey() + " on " + tableName);
}
}
runSQL(alterable.getSQL(tableName));
List<ObjectValuePair<String, IndexMetadata>> objectValuePairs =
getIndexesSQL(tableClass.getClassLoader(), tableName);
if (objectValuePairs == null) {
continue;
}
for (ObjectValuePair<String, IndexMetadata> objectValuePair : objectValuePairs) {
IndexMetadata indexMetadata = objectValuePair.getValue();
if (alterable.shouldAddIndex(Arrays.asList(indexMetadata.getColumnNames()))) {
runSQLTemplateString(objectValuePair.getKey(), false, true);
}
}
}
} catch (SQLException sqle) {
if (_log.isWarnEnabled()) {
_log.warn("Fallback to recreating the table", sqle);
}
Field tableColumnsField = tableClass.getField("TABLE_COLUMNS");
Field tableSQLCreateField = tableClass.getField("TABLE_SQL_CREATE");
Field tableSQLAddIndexesField = tableClass.getField("TABLE_SQL_ADD_INDEXES");
upgradeTable(
tableName,
(Object[][]) tableColumnsField.get(null),
(String) tableSQLCreateField.get(null),
(String[]) tableSQLAddIndexesField.get(null));
}
}
}
public void testMetaData() {
String ddl0 =
"DROP TABLE ADDRESSBOOK IF EXISTS; DROP TABLE ADDRESSBOOK_CATEGORY IF EXISTS; DROP TABLE USER IF EXISTS;";
String ddl1 =
"CREATE TABLE USER(USER_ID INTEGER NOT NULL PRIMARY KEY,LOGIN_ID VARCHAR(128) NOT NULL,USER_NAME VARCHAR(254) DEFAULT ' ' NOT NULL,CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UPDATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,LAST_ACCESS_DATE TIMESTAMP,CONSTRAINT IXUQ_LOGIN_ID0 UNIQUE(LOGIN_ID))";
String ddl2 =
"CREATE TABLE ADDRESSBOOK_CATEGORY(USER_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,CATEGORY_NAME VARCHAR(60) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK_CATEGORY PRIMARY KEY(USER_ID,CATEGORY_ID),CONSTRAINT FK_ADRBKCAT1 FOREIGN KEY(USER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE)";
String ddl3 =
"CREATE TABLE ADDRESSBOOK(USER_ID INTEGER NOT NULL,ADDRESSBOOK_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,FIRST VARCHAR(64) DEFAULT '' NOT NULL,LAST VARCHAR(64) DEFAULT '' NOT NULL,NOTE VARCHAR(128) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK PRIMARY KEY(USER_ID,ADDRESSBOOK_ID),CONSTRAINT FK_ADRBOOK1 FOREIGN KEY(USER_ID,CATEGORY_ID) REFERENCES ADDRESSBOOK_CATEGORY(USER_ID,CATEGORY_ID) ON DELETE CASCADE)";
String result1 = "1";
String result2 = "2";
String result3 = "3";
String result4 = "4";
String result5 = "5";
try {
stmnt.execute(ddl0);
stmnt.execute(ddl1);
stmnt.execute(ddl2);
stmnt.execute(ddl3);
DatabaseMetaData md = connection.getMetaData();
{
System.out.println("Testing DatabaseMetaData methods");
System.out.println(md.getDatabaseMajorVersion());
System.out.println(md.getDatabaseMinorVersion());
System.out.println(md.getDatabaseProductName());
System.out.println(md.getDatabaseProductVersion());
System.out.println(md.getDefaultTransactionIsolation());
System.out.println(md.getDriverMajorVersion());
System.out.println(md.getDriverMinorVersion());
System.out.println(md.getDriverName());
System.out.println(md.getDriverVersion());
System.out.println(md.getExtraNameCharacters());
System.out.println(md.getIdentifierQuoteString());
System.out.println(md.getJDBCMajorVersion());
System.out.println(md.getJDBCMinorVersion());
System.out.println(md.getMaxBinaryLiteralLength());
System.out.println(md.getMaxCatalogNameLength());
System.out.println(md.getMaxColumnsInGroupBy());
System.out.println(md.getMaxColumnsInIndex());
System.out.println(md.getMaxColumnsInOrderBy());
System.out.println(md.getMaxColumnsInSelect());
System.out.println(md.getMaxColumnsInTable());
System.out.println(md.getMaxConnections());
System.out.println(md.getMaxCursorNameLength());
System.out.println(md.getMaxIndexLength());
System.out.println(md.getMaxProcedureNameLength());
System.out.println(md.getMaxRowSize());
System.out.println(md.getMaxSchemaNameLength());
System.out.println(md.getMaxStatementLength());
System.out.println(md.getMaxStatements());
System.out.println(md.getMaxTableNameLength());
System.out.println(md.getMaxUserNameLength());
System.out.println(md.getNumericFunctions());
System.out.println(md.getProcedureTerm());
System.out.println(md.getResultSetHoldability());
System.out.println(md.getSchemaTerm());
System.out.println(md.getSearchStringEscape());
System.out.println("Testing DatabaseMetaData.getSQLKeywords()");
System.out.println(md.getSQLKeywords());
System.out.println(md.getSQLStateType());
System.out.println("Testing DatabaseMetaData.getStringFunctions()");
System.out.println(md.getStringFunctions());
System.out.println("Testing DatabaseMetaData.getSystemFunctions()");
System.out.println(md.getSystemFunctions());
System.out.println("Testing DatabaseMetaData.getTimeDateFunctions()");
System.out.println(md.getTimeDateFunctions());
System.out.println(md.getURL());
System.out.println(md.getUserName());
System.out.println(DatabaseMetaData.importedKeyCascade);
System.out.println(md.isCatalogAtStart());
System.out.println(md.isReadOnly());
ResultSet rs;
rs = md.getPrimaryKeys(null, null, "USER");
ResultSetMetaData rsmd = rs.getMetaData();
String result0 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result0 += rs.getString(i + 1) + ":";
}
result0 += "\n";
}
rs.close();
System.out.println(result0);
}
{
ResultSet rs;
rs = md.getBestRowIdentifier(null, null, "USER", 0, true);
ResultSetMetaData rsmd = rs.getMetaData();
String result0 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result0 += rs.getString(i + 1) + ":";
}
result0 += "\n";
}
rs.close();
System.out.println(result0);
}
{
ResultSet rs = md.getImportedKeys(null, null, "ADDRESSBOOK");
ResultSetMetaData rsmd = rs.getMetaData();
result1 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result1 += rs.getString(i + 1) + ":";
}
result1 += "\n";
}
rs.close();
System.out.println(result1);
}
{
ResultSet rs =
md.getCrossReference(null, null, "ADDRESSBOOK_CATEGORY", null, null, "ADDRESSBOOK");
ResultSetMetaData rsmd = rs.getMetaData();
result2 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result2 += rs.getString(i + 1) + ":";
}
result2 += "\n";
}
rs.close();
System.out.println(result2);
}
{
ResultSet rs = md.getExportedKeys(null, null, "USER");
ResultSetMetaData rsmd = rs.getMetaData();
result3 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result3 += rs.getString(i + 1) + ":";
}
result3 += "\n";
}
rs.close();
System.out.println(result3);
}
{
ResultSet rs = md.getCrossReference(null, null, "USER", null, null, "ADDRESSBOOK_CATEGORY");
ResultSetMetaData rsmd = rs.getMetaData();
result4 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result4 += rs.getString(i + 1) + ":";
}
result4 += "\n";
}
rs.close();
System.out.println(result4);
}
{
stmnt.execute("DROP TABLE T IF EXISTS;");
stmnt.execute("CREATE TABLE T (I IDENTITY, A CHAR(20), B CHAR(20));");
stmnt.execute("INSERT INTO T VALUES (NULL, 'get_column_name', '" + getColumnName + "');");
ResultSet rs = stmnt.executeQuery("SELECT I, A, B, A \"aliasA\", B \"aliasB\", 1 FROM T;");
ResultSetMetaData rsmd = rs.getMetaData();
result5 = "";
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result5 += rsmd.getColumnName(i + 1) + ":" + rs.getString(i + 1) + ":";
}
result5 += "\n";
}
rs.close();
rs = stmnt.executeQuery("SELECT I, A, B, A \"aliasA\", B \"aliasB\", 1 FROM T;");
rsmd = rs.getMetaData();
for (; rs.next(); ) {
for (int i = 0; i < rsmd.getColumnCount(); i++) {
result5 += rsmd.getColumnLabel(i + 1) + ":" + rs.getString(i + 1) + ":";
}
result5 += "\n";
}
System.out.println(result5);
System.out.println("first column identity: " + rsmd.isAutoIncrement(1));
rsmd.isCaseSensitive(1);
rsmd.isCurrency(1);
rsmd.isDefinitelyWritable(1);
rsmd.isNullable(1);
rsmd.isReadOnly(1);
rsmd.isSearchable(1);
rsmd.isSigned(1);
rsmd.isWritable(1);
rs.close();
// test identity with PreparedStatement
pstmnt = connection.prepareStatement("INSERT INTO T VALUES (?,?,?)");
pstmnt.setString(1, null);
pstmnt.setString(2, "test");
pstmnt.setString(3, "test2");
pstmnt.executeUpdate();
pstmnt = connection.prepareStatement("call identity()");
ResultSet rsi = pstmnt.executeQuery();
rsi.next();
int identity = rsi.getInt(1);
System.out.println("call identity(): " + identity);
rsi.close();
}
} catch (SQLException e) {
fail(e.getMessage());
}
System.out.println("testMetaData complete");
// assert equality of exported and imported with xref
assertEquals(result1, result2);
assertEquals(result3, result4);
}
private void tryReadMetaData(Connection conn, String oCatalog, String oSchema, String tableName)
throws SQLException {
DatabaseMetaData meta = conn.getMetaData();
storesLowerCase = meta.storesLowerCaseIdentifiers();
storesMixedCase = meta.storesMixedCaseIdentifiers();
storesMixedCaseQuoted = meta.storesMixedCaseQuotedIdentifiers();
supportsMixedCaseIdentifiers = meta.supportsMixedCaseIdentifiers();
ResultSet rs = meta.getTables(oCatalog, oSchema, tableName, null);
if (rs.next() && rs.next()) {
throw DbException.get(ErrorCode.SCHEMA_NAME_MUST_MATCH, tableName);
}
rs.close();
rs = meta.getColumns(null, null, tableName, null);
int i = 0;
ArrayList<Column> columnList = New.arrayList();
HashMap<String, Column> columnMap = New.hashMap();
String catalog = null, schema = null;
while (rs.next()) {
String thisCatalog = rs.getString("TABLE_CAT");
if (catalog == null) {
catalog = thisCatalog;
}
String thisSchema = rs.getString("TABLE_SCHEM");
if (schema == null) {
schema = thisSchema;
}
if (!StringUtils.equals(catalog, thisCatalog) || !StringUtils.equals(schema, thisSchema)) {
// if the table exists in multiple schemas or tables,
// use the alternative solution
columnMap.clear();
columnList.clear();
break;
}
String n = rs.getString("COLUMN_NAME");
n = convertColumnName(n);
int sqlType = rs.getInt("DATA_TYPE");
long precision = rs.getInt("COLUMN_SIZE");
precision = convertPrecision(sqlType, precision);
int scale = rs.getInt("DECIMAL_DIGITS");
scale = convertScale(sqlType, scale);
int displaySize = MathUtils.convertLongToInt(precision);
int type = DataType.convertSQLTypeToValueType(sqlType);
Column col = new Column(n, type, precision, scale, displaySize);
col.setTable(this, i++);
columnList.add(col);
columnMap.put(n, col);
}
rs.close();
// check if the table is accessible
Statement stat = null;
try {
stat = conn.createStatement();
rs = stat.executeQuery("SELECT * FROM " + tableName + " T WHERE 1=0");
if (columnList.size() == 0) {
// alternative solution
ResultSetMetaData rsMeta = rs.getMetaData();
for (i = 0; i < rsMeta.getColumnCount(); ) {
String n = rsMeta.getColumnName(i + 1);
n = convertColumnName(n);
int sqlType = rsMeta.getColumnType(i + 1);
long precision = rsMeta.getPrecision(i + 1);
precision = convertPrecision(sqlType, precision);
int scale = rsMeta.getScale(i + 1);
scale = convertScale(sqlType, scale);
int displaySize = rsMeta.getColumnDisplaySize(i + 1);
int type = DataType.getValueTypeFromResultSet(rsMeta, i + 1);
Column col = new Column(n, type, precision, scale, displaySize);
col.setTable(this, i++);
columnList.add(col);
columnMap.put(n, col);
}
}
rs.close();
} catch (Exception e) {
throw DbException.get(
ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1, e, tableName + "(" + e.toString() + ")");
} finally {
JdbcUtils.closeSilently(stat);
}
Column[] cols = new Column[columnList.size()];
columnList.toArray(cols);
setColumns(cols);
// create scan index
// load primary keys
try {
rs = meta.getPrimaryKeys(null, null, tableName);
} catch (Exception e) {
// Some ODBC bridge drivers don't support it:
// some combinations of "DataDirect SequeLink(R) for JDBC"
// http://www.datadirect.com/index.ssp
rs = null;
}
String pkName = "";
ArrayList<Column> list;
if (rs != null && rs.next()) {
// the problem is, the rows are not sorted by KEY_SEQ
list = New.arrayList();
do {
int idx = rs.getInt("KEY_SEQ");
if (pkName == null) {
pkName = rs.getString("PK_NAME");
}
while (list.size() < idx) {
list.add(null);
}
String col = rs.getString("COLUMN_NAME");
col = convertColumnName(col);
Column column = columnMap.get(col);
if (idx == 0) {
// workaround for a bug in the SQLite JDBC driver
list.add(column);
} else {
list.set(idx - 1, column);
}
} while (rs.next());
addIndex(pkName, list, IndexType.createPrimaryKey(false));
rs.close();
}
try {
rs = meta.getIndexInfo(null, null, tableName, false, true);
} catch (Exception e) {
// Oracle throws an exception if the table is not found or is a
// SYNONYM
rs = null;
}
String indexName = null;
list = New.arrayList();
IndexType indexType = null;
if (rs != null) {
while (rs.next()) {
if (rs.getShort("TYPE") == DatabaseMetaData.tableIndexStatistic) {
// ignore index statistics
continue;
}
String newIndex = rs.getString("INDEX_NAME");
if (pkName.equals(newIndex)) {
continue;
}
if (indexName != null && !indexName.equals(newIndex)) {
addIndex(indexName, list, indexType);
indexName = null;
}
if (indexName == null) {
indexName = newIndex;
list.clear();
}
boolean unique = !rs.getBoolean("NON_UNIQUE");
indexType = unique ? IndexType.createUnique(false) : IndexType.createNonUnique();
String col = rs.getString("COLUMN_NAME");
col = convertColumnName(col);
Column column = columnMap.get(col);
list.add(column);
}
rs.close();
}
if (indexName != null) {
addIndex(indexName, list, indexType);
}
shardingKeyIndex();
}
