@Test
public void testTableMetadataScan() throws Exception {
Connection conn = DriverManager.getConnection(getUrl());
try {
// make sure connections w/o tenant id only see non-tenant-specific tables, both SYSTEM and
// USER
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs = meta.getTables(null, null, null, null);
assertTrue(rs.next());
assertTableMetaData(rs, TYPE_SCHEMA, TYPE_SEQUENCE, SYSTEM);
assertTrue(rs.next());
assertTableMetaData(rs, TYPE_SCHEMA, TYPE_TABLE, SYSTEM);
assertTrue(rs.next());
assertTableMetaData(rs, null, PARENT_TABLE_NAME, USER);
assertFalse(rs.next());
// make sure connections w/o tenant id only see non-tenant-specific columns
rs = meta.getColumns(null, null, null, null);
while (rs.next()) {
assertNotEquals(TENANT_TABLE_NAME, rs.getString("TABLE_NAME"));
}
} finally {
conn.close();
}
conn = DriverManager.getConnection(PHOENIX_JDBC_TENANT_SPECIFIC_URL);
try {
// make sure tenant-specific connections only see their own tables
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs = meta.getTables(null, null, null, null);
assertTrue(rs.next());
assertTableMetaData(rs, null, TENANT_TABLE_NAME, USER);
assertFalse(rs.next());
// make sure tenants see paren table's columns and their own
rs = meta.getColumns(null, null, null, null);
assertTrue(rs.next());
assertColumnMetaData(rs, null, TENANT_TABLE_NAME, "user");
assertTrue(rs.next());
assertColumnMetaData(rs, null, TENANT_TABLE_NAME, "tenant_id");
assertTrue(rs.next());
assertColumnMetaData(rs, null, TENANT_TABLE_NAME, "id");
assertTrue(rs.next());
assertColumnMetaData(rs, null, TENANT_TABLE_NAME, "tenant_col");
assertFalse(rs.next());
} finally {
conn.close();
}
}
@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();
}
}
// answer the index of the column named colName in the table tabName
private int getColIndex(String colName, String tabName) {
int ordPos = 0;
try {
if (cConn == null) {
return -1;
}
if (dbmeta == null) {
dbmeta = cConn.getMetaData();
}
ResultSet colList = dbmeta.getColumns(null, null, tabName, colName);
colList.next();
ordPos = colList.getInt("ORDINAL_POSITION");
colList.close();
} catch (SQLException e) {
System.out.println("SQL Exception: " + e.getMessage());
}
return ordPos - 1;
}
private ResultSet getColumnNames(
DatabaseMetaData metaData,
String schema,
String dbName,
String tableName,
String columnNamePattern)
throws SQLException {
ResultSet columnNames = metaData.getColumns(dbName, schema, tableName, columnNamePattern);
if (columnNames.next()) {
columnNames = metaData.getColumns(dbName, schema, tableName, columnNamePattern);
} else {
columnNames = metaData.getColumns(null, schema, tableName, columnNamePattern);
}
return columnNames;
}
@Test
public void test() throws Exception {
String connect = "jdbc:sqlite:test.hbgb";
Class.forName("org.sqlite.JDBC");
Connection conn = null;
conn = DriverManager.getConnection(connect);
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getTables(null, null, null, null);
int cols = rs.getMetaData().getColumnCount();
while (rs.next()) {
System.out.println(rs.getString(3).toLowerCase());
}
rs.close();
rs = dbmd.getColumns(null, null, "features_matrix", "value%");
cols = rs.getMetaData().getColumnCount();
while (rs.next()) {
StringBuilder sb = new StringBuilder();
for (int i = 1; i <= cols; i++) {
sb.append(rs.getString(i)).append(", ");
}
System.out.println(sb.toString());
}
rs.close();
conn.close();
}
public List<ColsInfo> getIn(String tableName) {
List<ColsInfo> listOfLists = new ArrayList<ColsInfo>();
try {
ResultSet rsColumns = null;
DatabaseMetaData meta = connect.getMetaData();
rsColumns = meta.getColumns(null, "%", tableName, "%");
Statement st;
st = connect.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM " + tableName);
ResultSetMetaData rsMetaData = rs.getMetaData();
int i = 1;
while (rsColumns.next()) {
ColsInfo tmp = new ColsInfo();
tmp.setColTitle(rsColumns.getString("COLUMN_NAME"));
tmp.setColType(rsColumns.getString("TYPE_NAME"));
tmp.setColSize(rsColumns.getInt("COLUMN_SIZE"));
tmp.setColPosition(rsColumns.getInt("ORDINAL_POSITION"));
tmp.setIsNullable(rsColumns.getInt("NULLABLE"));
tmp.setAutoIncrement(rsMetaData.isAutoIncrement(i));
i++;
listOfLists.add(tmp);
}
return listOfLists;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
@Test
public void columnOrderOfgetColumns() throws SQLException {
ResultSet rs = meta.getColumns(null, null, "test", null);
assertTrue(rs.next());
ResultSetMetaData rsmeta = rs.getMetaData();
assertEquals(rsmeta.getColumnCount(), 22);
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), "DATA_TYPE");
assertEquals(rsmeta.getColumnName(6), "TYPE_NAME");
assertEquals(rsmeta.getColumnName(7), "COLUMN_SIZE");
assertEquals(rsmeta.getColumnName(8), "BUFFER_LENGTH");
assertEquals(rsmeta.getColumnName(9), "DECIMAL_DIGITS");
assertEquals(rsmeta.getColumnName(10), "NUM_PREC_RADIX");
assertEquals(rsmeta.getColumnName(11), "NULLABLE");
assertEquals(rsmeta.getColumnName(12), "REMARKS");
assertEquals(rsmeta.getColumnName(13), "COLUMN_DEF");
assertEquals(rsmeta.getColumnName(14), "SQL_DATA_TYPE");
assertEquals(rsmeta.getColumnName(15), "SQL_DATETIME_SUB");
assertEquals(rsmeta.getColumnName(16), "CHAR_OCTET_LENGTH");
assertEquals(rsmeta.getColumnName(17), "ORDINAL_POSITION");
assertEquals(rsmeta.getColumnName(18), "IS_NULLABLE");
// should be SCOPE_CATALOG, but misspelt in the standard
assertEquals(rsmeta.getColumnName(19), "SCOPE_CATLOG");
assertEquals(rsmeta.getColumnName(20), "SCOPE_SCHEMA");
assertEquals(rsmeta.getColumnName(21), "SCOPE_TABLE");
assertEquals(rsmeta.getColumnName(22), "SOURCE_DATA_TYPE");
}
private void source(JCodeModel codeModel, JDefinedClass clazz) throws SQLException, Exception {
DatabaseMetaData dmd = conn.getMetaData();
ResultSet rs = dmd.getColumns(null, schema.toUpperCase(), table, null);
String productName = dmd.getDatabaseProductName();
try {
while (rs.next()) {
String columnName = rs.getString("COLUMN_NAME");
int dataType = rs.getInt("DATA_TYPE");
int columnSize = rs.getInt("COLUMN_SIZE");
int digits = rs.getInt("DECIMAL_DIGITS");
String remarks = this.getColumnComment(table, columnName);
String fieldName = toCamelCase(columnName);
// privateなインスタンス変数
JFieldVar field = this.field(codeModel, clazz, fieldName, dataType, columnSize, digits);
field.javadoc().add(remarks);
getter(clazz, field, fieldName, remarks);
setter(codeModel, clazz, field, fieldName, remarks);
}
} finally {
rs.close();
}
}
private static Struct checkTableFill(DatabaseMetaData md, String dbName, String tableName)
throws SQLException, PageException {
Struct rows = new CastableStruct(tableName, Struct.TYPE_LINKED);
ResultSet columns = md.getColumns(dbName, null, tableName, null);
// print.o(new QueryImpl(columns,""));
try {
String name;
Object nullable;
while (columns.next()) {
name = columns.getString("COLUMN_NAME");
nullable = columns.getObject("IS_NULLABLE");
rows.setEL(
KeyImpl.init(name),
new ColumnInfo(
name,
columns.getInt("DATA_TYPE"),
columns.getString("TYPE_NAME"),
columns.getInt("COLUMN_SIZE"),
Caster.toBooleanValue(nullable)));
}
} finally {
DBUtil.closeEL(columns);
} // Table susid defined for cfc susid does not exist.
return rows;
}
/** 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();
}
}
/**
* Retrieves all the column names and types for a given table from JDBC metadata. It returns a
* List of Lists. Each element of the returned List is a List with:
*
* <p>element 0 => a String object for the column name. element 1 => an Integer object for the
* column type. element 2 => size of the column. element 3 => null type.
*
* @param dbMeta JDBC metadata.
* @param tableName Table from which to retrieve column information.
* @return The list of columns in <code>tableName</code>.
* @throws SQLException
*/
public List<List<Object>> getColumns(DatabaseMetaData dbMeta, String tableName)
throws SQLException {
List<List<Object>> columns = new ArrayList<List<Object>>();
ResultSet columnSet = null;
try {
columnSet = dbMeta.getColumns(null, dbSchema, tableName, null);
while (columnSet.next()) {
String name = columnSet.getString(4);
Integer sqlType = new Integer(columnSet.getString(5));
Integer size = new Integer(columnSet.getInt(7));
Integer decimalDigits = new Integer(columnSet.getInt(9));
Integer nullType = new Integer(columnSet.getInt(11));
String defValue = columnSet.getString(13);
List<Object> col = new ArrayList<Object>(6);
col.add(name);
col.add(sqlType);
col.add(size);
col.add(nullType);
col.add(defValue);
col.add(decimalDigits);
columns.add(col);
}
} finally {
if (columnSet != null) {
columnSet.close();
}
}
return columns;
}
public void refreshColumns(DatabaseMetaData metaData) throws SQLException {
columns.clear();
ResultSet rs = null;
try {
rs = metaData.getColumns(catalog, schema, name, null);
while (rs.next()) {
Column column = new Column();
columns.add(column);
column.setOwner(this);
column.setName(rs.getString("COLUMN_NAME"));
column.setDataType(rs.getInt("DATA_TYPE"));
column.setTypeName(rs.getString("TYPE_NAME"));
column.setSize(rs.getInt("COLUMN_SIZE"));
column.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
column.setRadix(rs.getInt("NUM_PREC_RADIX"));
column.setNullable(rs.getInt("NULLABLE"));
column.setRemarks(rs.getString("REMARKS"));
column.setDefaultValue(rs.getString("COLUMN_DEF"));
column.setCharOctetLength(rs.getInt("CHAR_OCTET_LENGTH"));
column.setOrdinalPosition(rs.getInt("ORDINAL_POSITION"));
}
} catch (SQLException sqle) {
throw sqle;
} finally {
try {
rs.close();
} catch (Exception ex) {
}
}
}
/**
* Get a list of all table columns for the given combination of schema name and table name.
*
* @param conn database connection
* @param schema schema name
* @param table table name
* @return list of all table columns
* @throws SQLException Thrown if an SQL statement failed to be executed.
*/
private static String[] getColumns(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.getColumns(conn.getCatalog(), schema, table, null);
final int columns = rs.getMetaData().getColumnCount();
while (rs.next()) {
final StringBuilder column = new StringBuilder();
for (int index = 4; (index <= columns) && (index <= 22); index++) {
// ignore column position
if (index != 17) {
if (column.length() > 0) {
column.append('/');
}
column.append(rs.getString(index));
}
}
result.add(column.toString());
}
} finally {
IOUtils.closeResultSet(rs);
}
return result.toArray(new String[result.size()]);
}
/**
* Get the metadata of the table (field names, types, is nullable). Collect the info to {@link
* #meta} map.
*
* @throws Exception
*/
private void getMeta() throws Exception {
try {
// Connection conn = DButil.getConnection();
String db = conn.getCatalog();
DatabaseMetaData metaData = conn.getMetaData();
ResultSet colMeta = metaData.getColumns(db, null, TABLE_NAME, "");
meta = new HashMap<String, FieldMetadata>();
// the name of the column
String name;
// the type of the column
String type;
// the fields is nullable?
boolean nullable;
while (colMeta.next()) {
name = colMeta.getString("COLUMN_NAME");
type = colMeta.getString("TYPE_NAME");
nullable = (colMeta.getString("NULLABLE").equals("1"));
meta.put(name, new FieldMetadata(name, type, nullable));
}
db = null;
colMeta.close();
metaData = null;
// DButil.closeConnection(conn);
} catch (SQLException e) {
logException(e, "getMeta()");
throw new Exception(e);
}
}
public boolean doesColumnExist(String table, String column) throws SQLException {
DatabaseMetaData meta = connection.getMetaData();
ResultSet result = meta.getColumns(null, null, table, column);
boolean res = result.next();
result.close();
return res;
}
public ResultSet getColumnsResultSet(DatabaseMetaData md, String schemaName, String tableName)
throws SQLException {
// TENT-288: the following call is different from MySQL's, because of
// getColumns(catalog, schema, table, col) definition and because in
// PostgreSQL schema~eventSchema (not catalog~eventSchema as in MySQL).
return md.getColumns(null, schemaName, tableName, null);
}
/**
* Get array of ColumnInfos that contain Column Name and its associated PDataType
*
* @param columns
* @return
* @throws SQLException
*/
private ColumnInfo[] generateColumnInfo(List<String> columns) throws SQLException {
Map<String, Integer> columnNameToTypeMap = Maps.newLinkedHashMap();
DatabaseMetaData dbmd = conn.getMetaData();
// TODO: escape wildcard characters here because we don't want that behavior here
String escapedTableName = StringUtil.escapeLike(tableName);
String[] schemaAndTable = escapedTableName.split("\\.");
ResultSet rs = null;
try {
rs =
dbmd.getColumns(
null,
(schemaAndTable.length == 1 ? "" : schemaAndTable[0]),
(schemaAndTable.length == 1 ? escapedTableName : schemaAndTable[1]),
null);
while (rs.next()) {
columnNameToTypeMap.put(
rs.getString(QueryUtil.COLUMN_NAME_POSITION), rs.getInt(QueryUtil.DATA_TYPE_POSITION));
}
} finally {
if (rs != null) {
rs.close();
}
}
ColumnInfo[] columnType;
if (columns == null) {
int i = 0;
columnType = new ColumnInfo[columnNameToTypeMap.size()];
for (Map.Entry<String, Integer> entry : columnNameToTypeMap.entrySet()) {
columnType[i++] = new ColumnInfo(entry.getKey(), entry.getValue());
}
} else {
// Leave "null" as indication to skip b/c it doesn't exist
columnType = new ColumnInfo[columns.size()];
for (int i = 0; i < columns.size(); i++) {
String columnName = SchemaUtil.normalizeIdentifier(columns.get(i).trim());
Integer sqlType = columnNameToTypeMap.get(columnName);
if (sqlType == null) {
if (isStrict) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.COLUMN_NOT_FOUND)
.setColumnName(columnName)
.setTableName(tableName)
.build()
.buildException();
}
unfoundColumnCount++;
} else {
columnType[i] = new ColumnInfo(columnName, sqlType);
}
}
if (unfoundColumnCount == columns.size()) {
throw new SQLExceptionInfo.Builder(SQLExceptionCode.COLUMN_NOT_FOUND)
.setColumnName(Arrays.toString(columns.toArray(new String[0])))
.setTableName(tableName)
.build()
.buildException();
}
}
return columnType;
}
public int getColumnLength(String table, String column) throws SQLException {
DatabaseMetaData meta = connection.getMetaData();
ResultSet result = meta.getColumns(null, null, table, column);
if (result.next()) {
return result.getInt("COLUMN_SIZE");
}
return -1;
}
/** Creates tables if they do not exist. */
private void initializeTables() {
Connection conn = null;
Statement statement = null;
try {
conn = retrieveConnection();
statement = conn.createStatement();
statement.setQueryTimeout(30);
// One table holding the playername id relation
String playerQuery =
String.format(
"CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY AUTOINCREMENT, %s STRING)",
playerTable, "name");
statement.executeUpdate(playerQuery);
// One column for every message
StringBuilder columns = new StringBuilder();
for (MessageNode node : MessageNode.getMessageNodes()) {
MsgCategory cat = messages.getCat(node);
if (node.getColumnName() != null
&& (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME)) {
columns.append(',');
columns.append(node.getColumnName());
}
}
String msgQuery =
String.format(
"CREATE TABLE IF NOT EXISTS %s (id INTEGER PRIMARY KEY UNIQUE %s)",
msgTable, columns);
statement.executeUpdate(msgQuery);
// Check if all columns are present
DatabaseMetaData dmd = conn.getMetaData();
// Add missing columns
for (MessageNode node : MessageNode.getMessageNodes()) {
MsgCategory cat = messages.getCat(node);
if (cat == MsgCategory.TUTORIAL || cat == MsgCategory.ONE_TIME) {
ResultSet set = dmd.getColumns(null, null, msgTable, node.getColumnName());
if (!set.next()) {
String updateQuery =
String.format("ALTER TABLE %s ADD COLUMN %s", msgTable, node.getColumnName());
statement.executeUpdate(updateQuery);
}
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
if (statement != null) statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Checks database metadata to see if a column exists in a table. This method is sensitive to the
* case of both the provided table name and column name.
*
* @param dbMetaData the database metadata to be used to look up this column
* @param tableName the case sensitive table name
* @param columnName the case sensitive column name
* @throws SQLException if an exception is encountered while accessing the database
*/
public static boolean columnExistsCaseSensitive(
DatabaseMetaData dbMetaData, String tableName, String columnName) throws SQLException {
ResultSet rsTables = dbMetaData.getColumns(null, null, tableName, columnName);
try {
boolean found = rsTables.next();
return found;
} finally {
closeJDBCResultSet(rsTables);
}
}
private void getTableStructure() throws SQLException {
DatabaseMetaData meta = con.getMetaData();
ResultSet rs = meta.getColumns(null, null, destinationTable, null);
while (rs.next()) {
tableColumns.add(rs.getString("COLUMN_NAME"));
tableColumnType.add(rs.getString("TYPE_NAME"));
tableColumnSize.add(rs.getString("COLUMN_SIZE"));
}
rs.close();
}
public ResultSet getColumns(
String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
throws SQLException {
_conn.checkOpen();
try {
return DelegatingResultSet.wrapResultSet(
_conn, _meta.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern));
} catch (SQLException e) {
handleException(e);
throw new AssertionError();
}
}
protected boolean getCompatibilityFulltextTable(DatabaseMetaData metadata) throws SQLException {
ResultSet rs = metadata.getColumns(null, null, Model.FULLTEXT_TABLE_NAME, "%");
while (rs.next()) {
// COLUMN_NAME=fulltext DATA_TYPE=1111 TYPE_NAME=tsvector
String columnName = rs.getString("COLUMN_NAME");
if (Model.FULLTEXT_FULLTEXT_KEY.equals(columnName)) {
String typeName = rs.getString("TYPE_NAME");
return "tsvector".equals(typeName);
}
}
return false;
}
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();
}
}
@Test
public void testMatchInParametersAndSqlTypeInfoWrapping() throws Exception {
final String TABLE = "customers";
final String USER = "******";
ResultSet metaDataResultSet = mock(ResultSet.class);
given(metaDataResultSet.next()).willReturn(true, false);
given(metaDataResultSet.getString("TABLE_SCHEM")).willReturn(USER);
given(metaDataResultSet.getString("TABLE_NAME")).willReturn(TABLE);
given(metaDataResultSet.getString("TABLE_TYPE")).willReturn("TABLE");
ResultSet columnsResultSet = mock(ResultSet.class);
given(columnsResultSet.next()).willReturn(true, true, true, true, false);
given(columnsResultSet.getString("COLUMN_NAME"))
.willReturn("id", "name", "customersince", "version");
given(columnsResultSet.getInt("DATA_TYPE"))
.willReturn(Types.INTEGER, Types.VARCHAR, Types.DATE, Types.NUMERIC);
given(columnsResultSet.getBoolean("NULLABLE")).willReturn(false, true, true, false);
given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
given(databaseMetaData.getDatabaseProductName()).willReturn("1.0");
given(databaseMetaData.getUserName()).willReturn(USER);
given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true);
given(databaseMetaData.getTables(null, null, TABLE, null)).willReturn(metaDataResultSet);
given(databaseMetaData.getColumns(null, USER, TABLE, null)).willReturn(columnsResultSet);
MapSqlParameterSource map = new MapSqlParameterSource();
map.addValue("id", 1);
map.addValue("name", "Sven");
map.addValue("customersince", new Date());
map.addValue("version", 0);
map.registerSqlType("customersince", Types.DATE);
map.registerSqlType("version", Types.NUMERIC);
context.setTableName(TABLE);
context.processMetaData(dataSource, new ArrayList<>(), new String[] {});
List<Object> values = context.matchInParameterValuesWithInsertColumns(map);
assertEquals("wrong number of parameters: ", 4, values.size());
assertTrue("id not wrapped with type info", values.get(0) instanceof Number);
assertTrue("name not wrapped with type info", values.get(1) instanceof String);
assertTrue("date wrapped with type info", values.get(2) instanceof SqlParameterValue);
assertTrue("version wrapped with type info", values.get(3) instanceof SqlParameterValue);
verify(metaDataResultSet, atLeastOnce()).next();
verify(columnsResultSet, atLeastOnce()).next();
verify(metaDataResultSet).close();
verify(columnsResultSet).close();
}
/**
* Delivers a list with {@link Field}'s for the given {@link Table}.
*
* @param table
* @return
*/
private List<Field> getFieldsForTable(Table table) {
List<Field> fields = new ArrayList<Field>();
try {
DatabaseMetaData meta = getConnection().getMetaData();
ResultSet columns = meta.getColumns(null, null, table.getName(), null);
while (columns.next()) {
Field field = new Field(columns.getString("COLUMN_NAME"), columns.getInt("DATA_TYPE"));
fields.add(field);
}
} catch (SQLException e) {
e.printStackTrace();
}
return fields;
}
/**
* Prints in the console the columns metadata, based in the Arraylist of tables passed as
* parameter.
*
* @param tables
* @throws SQLException
*/
public static void getColumnsMetadata(ArrayList<String> tables) throws SQLException {
ResultSet rs = null;
// Print the columns properties of the actual table
for (String actualTable : tables) {
rs = metadata.getColumns(null, null, actualTable, null);
System.out.println(actualTable.toUpperCase());
while (rs.next()) {
System.out.println(
rs.getString("COLUMN_NAME")
+ " "
+ rs.getString("TYPE_NAME")
+ " "
+ rs.getString("COLUMN_SIZE"));
}
System.out.println("\n");
}
}
/**
* Checks whether or not a column exists in the table.
*
* @param name name of the column
* @return true if exists, false otherwise
*/
public boolean columnExists(String name) {
// Check if the table exists
try {
DatabaseMetaData meta = database.getMeta();
ResultSet result = meta.getColumns(null, null, this.name, name);
boolean exists = result.next();
result.close();
return exists;
}
// An error occurred
catch (Exception ex) {
database.getLogger().severe("Unable to validate table: " + ex.getMessage());
}
return false;
}
public static void main(String[] args) throws Exception {
Connection conn = ConnectionManager.getInstance().getConnection();
ResultSet rsTables = null;
ResultSet rsColumns = null;
ArrayList<String> tables = new ArrayList<>();
try {
DatabaseMetaData metadata = conn.getMetaData();
String[] tableTypes = {"TABLE"};
rsTables = metadata.getTables(null, "%", "%", tableTypes);
while (rsTables.next()) {
tables.add(rsTables.getString("TABLE_NAME"));
}
for (String tableName : tables) {
System.out.println("Table: " + tableName);
System.out.println("--------------------");
rsColumns = metadata.getColumns(null, "%", tableName, "%");
while (rsColumns.next()) {
StringBuilder builder = new StringBuilder();
builder.append(rsColumns.getString("COLUMN_NAME"));
builder.append(": ");
builder.append(rsColumns.getString("TYPE_NAME"));
System.out.println(builder.toString());
}
System.out.println("");
}
} catch (Exception e) {
System.err.println(e);
} finally {
rsTables.close();
rsColumns.close();
}
ConnectionManager.getInstance().close();
}
@Test
public void testTableWithSingleColumnGeneratedKey() throws Exception {
final String TABLE = "customers";
final String USER = "******";
ResultSet metaDataResultSet = mock(ResultSet.class);
given(metaDataResultSet.next()).willReturn(true, false);
given(metaDataResultSet.getString("TABLE_SCHEM")).willReturn(USER);
given(metaDataResultSet.getString("TABLE_NAME")).willReturn(TABLE);
given(metaDataResultSet.getString("TABLE_TYPE")).willReturn("TABLE");
ResultSet columnsResultSet = mock(ResultSet.class);
given(columnsResultSet.next()).willReturn(true, false);
given(columnsResultSet.getString("COLUMN_NAME")).willReturn("id");
given(columnsResultSet.getInt("DATA_TYPE")).willReturn(Types.INTEGER);
given(columnsResultSet.getBoolean("NULLABLE")).willReturn(false);
given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
given(databaseMetaData.getDatabaseProductName()).willReturn("1.0");
given(databaseMetaData.getUserName()).willReturn(USER);
given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true);
given(databaseMetaData.getTables(null, null, TABLE, null)).willReturn(metaDataResultSet);
given(databaseMetaData.getColumns(null, USER, TABLE, null)).willReturn(columnsResultSet);
MapSqlParameterSource map = new MapSqlParameterSource();
String[] keyCols = new String[] {"id"};
context.setTableName(TABLE);
context.processMetaData(dataSource, new ArrayList<>(), keyCols);
List<Object> values = context.matchInParameterValuesWithInsertColumns(map);
String insertString = context.createInsertString(keyCols);
assertEquals("wrong number of parameters: ", 0, values.size());
assertEquals(
"empty insert not generated correctly", "INSERT INTO customers () VALUES()", insertString);
verify(metaDataResultSet, atLeastOnce()).next();
verify(columnsResultSet, atLeastOnce()).next();
verify(metaDataResultSet).close();
verify(columnsResultSet).close();
}
