List of usage examples for java.sql DatabaseMetaData getColumns
ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
throws SQLException;
From source file:org.kuali.test.ui.components.sqlquerypanel.DatabasePanel.java
private void loadTableColumns(DatabaseConnection dbconn, DatabaseMetaData dmd, SqlQueryNode node) throws Exception { ResultSet res = null;//from w ww .j a va 2 s. c om try { TableData td = (TableData) node.getUserObject(); String colkey = (td.getSchema() + "." + td.getName()); List<ColumnData> cols = tableColumnData.get(colkey); if (cols != null) { for (ColumnData cd : cols) { ColumnData cdata = (ColumnData) cd.clone(); node.add(new SqlQueryNode(getMainframe().getConfiguration(), cdata)); td.getColumns().add(cdata); } } else { res = dmd.getColumns(null, td.getSchema(), td.getName(), null); while (res.next()) { String cname = res.getString(4); int dataType = res.getInt(5); int width = res.getInt(7); int decimalDigits = res.getInt(9); String displayName = getColumnDisplayName(td.getName(), cname, dbconn.getConfiguredTablesOnly()); if (StringUtils.isNotBlank(displayName)) { ColumnData cd = new ColumnData(td.getSchema(), cname, displayName); cd.setDataType(dataType); cd.setDecimalDigits(decimalDigits); cd.setWidth(width); td.getColumns().add(cd); } } HashMap<String, ColumnData> map = new HashMap<String, ColumnData>(); for (ColumnData cd : td.getColumns()) { map.put(cd.getName(), cd); } res.close(); try { res = dmd.getPrimaryKeys(null, td.getSchema(), td.getName()); while (res.next()) { String cname = res.getString(4); int seq = res.getInt(5); ColumnData cd = map.get(cname); if (cd != null) { cd.setPrimaryKeyIndex(seq); } } } catch (SQLException ex) { LOG.warn("error obtaining primary keys for table " + td.getName()); } Collections.sort(td.getColumns()); for (ColumnData cd : td.getColumns()) { node.add(new SqlQueryNode(getMainframe().getConfiguration(), cd)); } tableColumnData.put(colkey, td.getColumns()); } } finally { Utils.closeDatabaseResources(null, null, res); } }
From source file:org.talend.core.model.metadata.DBConnectionFillerImplTest.java
/** * //ww w . j a va 2 s.c o m * test fill columns for odbc teradata in method "fillColumns(ColumnSet colSet, DatabaseMetaData dbJDBCMetadata, * List<String> columnFilter, String columnPattern)". * * @param columnSet * @throws SQLException */ private void testFillColumnCommon_TeraOdbc(ColumnSet columnSet) throws SQLException { // ColumnSet columnSet = orgomg.cwm.resource.relational.RelationalFactory.eINSTANCE.createColumnSet(); columnSet.setName("table1");//$NON-NLS-1$ DatabaseMetaData dbJDBCMetadata = mock(DatabaseMetaData.class); List<String> columnFilter = new ArrayList<String>(); Schema schema = mock(Schema.class); when(schema.getName()).thenReturn("talend");//$NON-NLS-1$ PowerMockito.mockStatic(CatalogHelper.class); when(CatalogHelper.getParentCatalog(columnSet)).thenReturn(null); PowerMockito.mockStatic(SchemaHelper.class); when(SchemaHelper.getParentSchema(columnSet)).thenReturn(schema); stub(method(MetadataConnectionUtils.class, "isSybase", DatabaseMetaData.class)).toReturn(false);//$NON-NLS-1$ stub(method(MetadataConnectionUtils.class, "isMssql", DatabaseMetaData.class)).toReturn(false);//$NON-NLS-1$ stub(method(MetadataConnectionUtils.class, "isOracle", DatabaseMetaData.class)).toReturn(true);//$NON-NLS-1$ PowerMockito.mockStatic(ConnectionUtils.class); when(ConnectionUtils.isOdbcTeradata(dbJDBCMetadata)).thenReturn(true); ResultSet rs = mock(ResultSet.class); when(rs.next()).thenReturn(true).thenReturn(true).thenReturn(false); when(rs.getString(GetColumn.COLUMN_NAME.name())).thenReturn("column1").thenReturn("column2");//$NON-NLS-1$ //$NON-NLS-2$ when(rs.getString(GetColumn.TYPE_NAME.name())).thenReturn("VARCHAR");//$NON-NLS-1$ when(rs.getInt(GetColumn.NULLABLE.name())).thenReturn(0); when(rs.getInt(GetColumn.DATA_TYPE.name())).thenReturn(1); when(rs.getString(GetColumn.REMARKS.name())).thenReturn("");//$NON-NLS-1$ when(rs.getString(GetColumn.IS_NULLABLE.name())).thenReturn("YES");//$NON-NLS-1$ when(dbJDBCMetadata.getColumns(anyString(), anyString(), anyString(), anyString())).thenReturn(rs); PowerMockito.mockStatic(ConnectionHelper.class); when(ConnectionHelper.getConnection(columnSet)).thenReturn(dbConnection); stub(method(MetadataToolHelper.class, "validateValueForDBType", String.class)).toReturn("VARCHAR");//$NON-NLS-1$ //$NON-NLS-2$ PowerMockito.mockStatic(ColumnSetHelper.class); stub(method(ColumnSetHelper.class, "addColumns")).toReturn(true);//$NON-NLS-1$ List<TdColumn> fillColumns = dBConnectionFillerImpl.fillColumns(columnSet, dbJDBCMetadata, columnFilter, null); assertNotNull(fillColumns); assertTrue(fillColumns.size() == 2); // for odbc teradata,should assert like this for (TdColumn tdColumn : fillColumns) { assertEquals(tdColumn.getSqlDataType().getNumericPrecision(), 0); assertEquals(tdColumn.getSqlDataType().getNumericPrecisionRadix(), 0); assertEquals(tdColumn.getLength(), 0); assertNull(null, tdColumn.getInitialValue().getBody()); } }
From source file:org.apache.kylin.rest.service.QueryService.java
protected List<TableMeta> getMetadata(CubeManager cubeMgr, String project, boolean cubedOnly) throws SQLException { Connection conn = null;//from w ww. j a v a2 s . c o m ResultSet columnMeta = null; List<TableMeta> tableMetas = null; if (StringUtils.isBlank(project)) { return Collections.emptyList(); } ResultSet JDBCTableMeta = null; try { DataSource dataSource = cacheService.getOLAPDataSource(project); conn = dataSource.getConnection(); DatabaseMetaData metaData = conn.getMetaData(); JDBCTableMeta = metaData.getTables(null, null, null, null); tableMetas = new LinkedList<TableMeta>(); Map<String, TableMeta> tableMap = new HashMap<String, TableMeta>(); while (JDBCTableMeta.next()) { String catalogName = JDBCTableMeta.getString(1); String schemaName = JDBCTableMeta.getString(2); // Not every JDBC data provider offers full 10 columns, e.g., PostgreSQL has only 5 TableMeta tblMeta = new TableMeta(catalogName == null ? Constant.FakeCatalogName : catalogName, schemaName == null ? Constant.FakeSchemaName : schemaName, JDBCTableMeta.getString(3), JDBCTableMeta.getString(4), JDBCTableMeta.getString(5), null, null, null, null, null); if (!cubedOnly || getProjectManager().isExposedTable(project, schemaName + "." + tblMeta.getTABLE_NAME())) { tableMetas.add(tblMeta); tableMap.put(tblMeta.getTABLE_SCHEM() + "#" + tblMeta.getTABLE_NAME(), tblMeta); } } columnMeta = metaData.getColumns(null, null, null, null); while (columnMeta.next()) { String catalogName = columnMeta.getString(1); String schemaName = columnMeta.getString(2); // kylin(optiq) is not strictly following JDBC specification ColumnMeta colmnMeta = new ColumnMeta(catalogName == null ? Constant.FakeCatalogName : catalogName, schemaName == null ? Constant.FakeSchemaName : schemaName, columnMeta.getString(3), columnMeta.getString(4), columnMeta.getInt(5), columnMeta.getString(6), columnMeta.getInt(7), getInt(columnMeta.getString(8)), columnMeta.getInt(9), columnMeta.getInt(10), columnMeta.getInt(11), columnMeta.getString(12), columnMeta.getString(13), getInt(columnMeta.getString(14)), getInt(columnMeta.getString(15)), columnMeta.getInt(16), columnMeta.getInt(17), columnMeta.getString(18), columnMeta.getString(19), columnMeta.getString(20), columnMeta.getString(21), getShort(columnMeta.getString(22)), columnMeta.getString(23)); if (!cubedOnly || getProjectManager().isExposedColumn(project, schemaName + "." + colmnMeta.getTABLE_NAME(), colmnMeta.getCOLUMN_NAME())) { tableMap.get(colmnMeta.getTABLE_SCHEM() + "#" + colmnMeta.getTABLE_NAME()).addColumn(colmnMeta); } } } finally { close(columnMeta, null, conn); if (JDBCTableMeta != null) { JDBCTableMeta.close(); } } return tableMetas; }
From source file:it.eng.spagobi.meta.initializer.PhysicalModelInitializer.java
/** * Get columns names that are present in the database but not in the passed physical model * //from w w w . j av a 2 s . c om * @param connection * jdbc connection to the database * @param physicalModel * physical model to check */ public List<String> getMissingColumnsNames(Connection connection, PhysicalModel physicalModel) { try { DatabaseMetaData dbMeta = connection.getMetaData(); List<String> tablesOnDatabase = new ArrayList<String>(); List<String> newColumnsNames = new ArrayList<String>(); ResultSet tableRs = dbMeta.getTables(physicalModel.getCatalog(), physicalModel.getSchema(), null, new String[] { "TABLE", "VIEW" }); while (tableRs.next()) { String tableName = tableRs.getString("TABLE_NAME"); tablesOnDatabase.add(tableName); } tableRs.close(); EList<PhysicalTable> originalTables = physicalModel.getTables(); Iterator<String> tablesIterator = tablesOnDatabase.iterator(); // iterate for each table while (tablesIterator.hasNext()) { String tableName = tablesIterator.next(); PhysicalTable physicalTable = findTable(tableName, originalTables); if (physicalTable != null) { ResultSet rs = dbMeta.getColumns(physicalModel.getCatalog(), physicalModel.getSchema(), physicalTable.getName(), null); while (rs.next()) { String columnName = rs.getString("COLUMN_NAME"); // check if the column exists in the physicalModel PhysicalColumn physicalColumn = findColumn(columnName, physicalTable.getColumns()); if (physicalColumn == null) { // new column on database newColumnsNames.add(tableName + "." + columnName); } } } } return newColumnsNames; } catch (SQLException e) { throw new RuntimeException("Physical Model - Impossible to get missing tables names", e); } }
From source file:de.erdesignerng.dialect.JDBCReverseEngineeringStrategy.java
/** * Reverse engineer an existing table.//w ww.j a va2 s. co m * * @param aModel the model * @param aOptions the options * @param aNotifier the notifier * @param aTableEntry the table * @param aConnection the connection * * @return a Map of former ModelProperties of model items in case they have been replaced during reverse engineering into an existing model * * @throws SQLException is thrown in case of an error * @throws ReverseEngineeringException is thrown in case of an error */ protected final Map<String, ModelProperties> reverseEngineerTable(Model aModel, ReverseEngineeringOptions aOptions, ReverseEngineeringNotifier aNotifier, TableEntry aTableEntry, Connection aConnection) throws SQLException, ReverseEngineeringException { Map<String, ModelProperties> theExistingModelItemProperties = null; aNotifier.notifyMessage(ERDesignerBundle.ENGINEERINGTABLE, aTableEntry.getTableName()); DatabaseMetaData theMetaData = aConnection.getMetaData(); String theTablePattern = getEscapedPattern(theMetaData, aTableEntry.getTableName()); String theSchemaPattern = getEscapedPattern(theMetaData, aTableEntry.getSchemaName()); ResultSet theTablesResultSet = theMetaData.getTables(aTableEntry.getCatalogName(), theSchemaPattern, theTablePattern, new String[] { aTableEntry.getTableType().toString() }); while (theTablesResultSet.next()) { String theTableRemarks = theTablesResultSet.getString("REMARKS"); Table theNewTable = new Table(); theNewTable.setName(dialect.getCastType().cast(aTableEntry.getTableName())); theNewTable.setOriginalName(aTableEntry.getTableName()); switch (aOptions.getTableNaming()) { case INCLUDE_SCHEMA: theNewTable.setSchema(aTableEntry.getSchemaName()); break; default: } if (!StringUtils.isEmpty(theTableRemarks)) { theNewTable.setComment(theTableRemarks); } // Reverse engineer attributes ResultSet theColumnsResultSet = theMetaData.getColumns(aTableEntry.getCatalogName(), theSchemaPattern, theTablePattern, null); while (theColumnsResultSet.next()) { String theColumnName = null; String theTypeName = null; Integer theSize = null; Integer theFraction = null; int theRadix = 0; int theNullable = 0; String theDefaultValue = null; String theColumnRemarks = null; try { theColumnName = theColumnsResultSet.getString("COLUMN_NAME"); } catch (SQLException e) { } try { theTypeName = theColumnsResultSet.getString("TYPE_NAME"); } catch (SQLException e) { } try { theSize = theColumnsResultSet.getInt("COLUMN_SIZE"); } catch (SQLException e) { } try { theFraction = theColumnsResultSet.getInt("DECIMAL_DIGITS"); } catch (SQLException e) { } try { theRadix = theColumnsResultSet.getInt("NUM_PREC_RADIX"); } catch (SQLException e) { } try { theNullable = theColumnsResultSet.getInt("NULLABLE"); } catch (SQLException e) { } try { theDefaultValue = theColumnsResultSet.getString("COLUMN_DEF"); if (!StringUtils.isEmpty(theDefaultValue)) { theDefaultValue = theDefaultValue.trim(); } } catch (SQLException e) { } try { theColumnRemarks = theColumnsResultSet.getString("REMARKS"); } catch (SQLException e) { } Attribute<Table> theAttribute = new Attribute<>(); theAttribute.setName(dialect.getCastType().cast(theColumnName)); if (!StringUtils.isEmpty(theColumnRemarks)) { theAttribute.setComment(theColumnRemarks); } // Search for the datatype in the domains, the dialect specific and the user defined datatypes DataType theDataType = aModel.getAvailableDataTypes() .findByName(dialect.convertTypeNameToRealTypeName(theTypeName)); if (theDataType == null) { throw new ReverseEngineeringException("Unknown data type " + theTypeName + " for " + aTableEntry.getTableName() + "." + theColumnName); } boolean isNullable = true; switch (theNullable) { case DatabaseMetaData.columnNoNulls: isNullable = false; break; case DatabaseMetaData.columnNullable: isNullable = true; break; default: LOGGER.warn("Unknown nullability : " + theNullable + " for " + theColumnName + " of table " + theNewTable.getName()); } theAttribute.setDatatype(theDataType); theAttribute.setSize(theSize); theAttribute.setFraction(theFraction); theAttribute.setScale(theRadix); theAttribute.setDefaultValue(theDefaultValue); theAttribute.setNullable(isNullable); reverseEngineerAttribute(theAttribute, aTableEntry, aConnection); try { theNewTable.addAttribute(aModel, theAttribute); } catch (ElementAlreadyExistsException | ElementInvalidNameException e) { throw new ReverseEngineeringException(e.getMessage(), e); } } theColumnsResultSet.close(); // Reverse engineer primary keys reverseEngineerPrimaryKey(aModel, aTableEntry, theMetaData, theNewTable); // Reverse engineer indexes try { reverseEngineerIndexes(aModel, aTableEntry, theMetaData, theNewTable, aNotifier); } catch (SQLException e) { // if there is an sql exception, just ignore it } // We are done here try { aModel.addTable(theNewTable); } catch (ElementAlreadyExistsException e1) { //this manages the reverse engineering into an existing model and cares only about the table names of the model that conflict with the new table names from the connection //TODO: also care about tables that are no longer part of the connection, but still exist in the local model. E.g. show a dialog and ask the user what to do (delete/keep) try { //buffer the properties (e.g. position in model) of the existing table and its relations (e.g. the offset of the title) that are going to be replaced Table theExistingTable = aModel.getTables().findByName(theNewTable.getName()); RelationList theExistingRelations = aModel.getRelations().getAllRelataionsOf(theExistingTable); if (theExistingModelItemProperties == null) { theExistingModelItemProperties = new HashMap<>(); } //store former layouting data for the table and its relations in the old graph theExistingModelItemProperties.put(theExistingTable.getName(), theExistingTable.getProperties()); for (Relation anExistingRelation : theExistingRelations) { theExistingModelItemProperties.put(anExistingRelation.getName(), anExistingRelation.getProperties()); } //remove old table and its relations aModel.removeTable(theExistingTable); //add the new table without relations aModel.addTable(theNewTable); } catch (ElementAlreadyExistsException | ElementInvalidNameException | VetoException e2) { throw new ReverseEngineeringException(e2.getMessage()); } } catch (ElementInvalidNameException | VetoException e3) { throw new ReverseEngineeringException(e3.getMessage()); } } theTablesResultSet.close(); return theExistingModelItemProperties; }
From source file:org.apache.hive.jdbc.TestJdbcWithMiniHS2.java
@Test public void testTempTable() throws Exception { // Create temp table with current connection String tempTableName = "tmp1"; Statement stmt = conTestDb.createStatement(); stmt.execute("CREATE TEMPORARY TABLE " + tempTableName + " (key string, value string)"); stmt.execute("load data local inpath '" + kvDataFilePath.toString() + "' into table " + tempTableName); String resultVal = "val_238"; String queryStr = "SELECT * FROM " + tempTableName + " where value = '" + resultVal + "'"; ResultSet res = stmt.executeQuery(queryStr); assertTrue(res.next());/*from w ww .j a va 2s . com*/ assertEquals(resultVal, res.getString(2)); res.close(); stmt.close(); // Test getTables() DatabaseMetaData md = conTestDb.getMetaData(); assertTrue(md.getConnection() == conTestDb); ResultSet rs = md.getTables(null, null, tempTableName, null); boolean foundTable = false; while (rs.next()) { String tableName = rs.getString(3); if (tableName.equalsIgnoreCase(tempTableName)) { assertFalse("Table not found yet", foundTable); foundTable = true; } } assertTrue("Found temp table", foundTable); // Test getTables() with no table name pattern rs = md.getTables(null, null, null, null); foundTable = false; while (rs.next()) { String tableName = rs.getString(3); if (tableName.equalsIgnoreCase(tempTableName)) { assertFalse("Table not found yet", foundTable); foundTable = true; } } assertTrue("Found temp table", foundTable); // Test getColumns() rs = md.getColumns(null, null, tempTableName, null); assertTrue("First row", rs.next()); assertTrue(rs.getString(3).equalsIgnoreCase(tempTableName)); assertTrue(rs.getString(4).equalsIgnoreCase("key")); assertEquals(Types.VARCHAR, rs.getInt(5)); assertTrue("Second row", rs.next()); assertTrue(rs.getString(3).equalsIgnoreCase(tempTableName)); assertTrue(rs.getString(4).equalsIgnoreCase("value")); assertEquals(Types.VARCHAR, rs.getInt(5)); // A second connection should not be able to see the table Connection conn2 = DriverManager.getConnection(miniHS2.getJdbcURL(testDbName), System.getProperty("user.name"), "bar"); Statement stmt2 = conn2.createStatement(); stmt2.execute("USE " + testDbName); boolean gotException = false; try { res = stmt2.executeQuery(queryStr); } catch (SQLException err) { // This is expected to fail. assertTrue("Expecting table not found error, instead got: " + err, err.getMessage().contains("Table not found")); gotException = true; } assertTrue("Exception while querying non-existing temp table", gotException); conn2.close(); }
From source file:org.apache.kylin.rest.service.QueryService.java
@SuppressWarnings("checkstyle:methodlength") protected List<TableMetaWithType> getMetadataV2(CubeManager cubeMgr, String project, boolean cubedOnly) throws SQLException, IOException { //Message msg = MsgPicker.getMsg(); Connection conn = null;/* www. ja v a 2 s . c o m*/ ResultSet columnMeta = null; List<TableMetaWithType> tableMetas = null; Map<String, TableMetaWithType> tableMap = null; Map<String, ColumnMetaWithType> columnMap = null; if (StringUtils.isBlank(project)) { return Collections.emptyList(); } ResultSet JDBCTableMeta = null; try { DataSource dataSource = cacheService.getOLAPDataSource(project); conn = dataSource.getConnection(); DatabaseMetaData metaData = conn.getMetaData(); JDBCTableMeta = metaData.getTables(null, null, null, null); tableMetas = new LinkedList<TableMetaWithType>(); tableMap = new HashMap<String, TableMetaWithType>(); columnMap = new HashMap<String, ColumnMetaWithType>(); while (JDBCTableMeta.next()) { String catalogName = JDBCTableMeta.getString(1); String schemaName = JDBCTableMeta.getString(2); // Not every JDBC data provider offers full 10 columns, e.g., PostgreSQL has only 5 TableMetaWithType tblMeta = new TableMetaWithType( catalogName == null ? Constant.FakeCatalogName : catalogName, schemaName == null ? Constant.FakeSchemaName : schemaName, JDBCTableMeta.getString(3), JDBCTableMeta.getString(4), JDBCTableMeta.getString(5), null, null, null, null, null); if (!cubedOnly || getProjectManager().isExposedTable(project, schemaName + "." + tblMeta.getTABLE_NAME())) { tableMetas.add(tblMeta); tableMap.put(tblMeta.getTABLE_SCHEM() + "#" + tblMeta.getTABLE_NAME(), tblMeta); } } columnMeta = metaData.getColumns(null, null, null, null); while (columnMeta.next()) { String catalogName = columnMeta.getString(1); String schemaName = columnMeta.getString(2); // kylin(optiq) is not strictly following JDBC specification ColumnMetaWithType colmnMeta = new ColumnMetaWithType( catalogName == null ? Constant.FakeCatalogName : catalogName, schemaName == null ? Constant.FakeSchemaName : schemaName, columnMeta.getString(3), columnMeta.getString(4), columnMeta.getInt(5), columnMeta.getString(6), columnMeta.getInt(7), getInt(columnMeta.getString(8)), columnMeta.getInt(9), columnMeta.getInt(10), columnMeta.getInt(11), columnMeta.getString(12), columnMeta.getString(13), getInt(columnMeta.getString(14)), getInt(columnMeta.getString(15)), columnMeta.getInt(16), columnMeta.getInt(17), columnMeta.getString(18), columnMeta.getString(19), columnMeta.getString(20), columnMeta.getString(21), getShort(columnMeta.getString(22)), columnMeta.getString(23)); if (!cubedOnly || getProjectManager().isExposedColumn(project, schemaName + "." + colmnMeta.getTABLE_NAME(), colmnMeta.getCOLUMN_NAME())) { tableMap.get(colmnMeta.getTABLE_SCHEM() + "#" + colmnMeta.getTABLE_NAME()).addColumn(colmnMeta); columnMap.put(colmnMeta.getTABLE_SCHEM() + "#" + colmnMeta.getTABLE_NAME() + "#" + colmnMeta.getCOLUMN_NAME(), colmnMeta); } } } finally { close(columnMeta, null, conn); if (JDBCTableMeta != null) { JDBCTableMeta.close(); } } ProjectInstance projectInstance = getProjectManager().getProject(project); for (String modelName : projectInstance.getModels()) { DataModelDesc dataModelDesc = modelService.listAllModels(modelName, project, true).get(0); if (!dataModelDesc.isDraft()) { // update table type: FACT for (TableRef factTable : dataModelDesc.getFactTables()) { String factTableName = factTable.getTableIdentity().replace('.', '#'); if (tableMap.containsKey(factTableName)) { tableMap.get(factTableName).getTYPE().add(TableMetaWithType.tableTypeEnum.FACT); } else { // should be used after JDBC exposes all tables and columns // throw new BadRequestException(msg.getTABLE_META_INCONSISTENT()); } } // update table type: LOOKUP for (TableRef lookupTable : dataModelDesc.getLookupTables()) { String lookupTableName = lookupTable.getTableIdentity().replace('.', '#'); if (tableMap.containsKey(lookupTableName)) { tableMap.get(lookupTableName).getTYPE().add(TableMetaWithType.tableTypeEnum.LOOKUP); } else { // throw new BadRequestException(msg.getTABLE_META_INCONSISTENT()); } } // update column type: PK and FK for (JoinTableDesc joinTableDesc : dataModelDesc.getJoinTables()) { JoinDesc joinDesc = joinTableDesc.getJoin(); for (String pk : joinDesc.getPrimaryKey()) { String columnIdentity = (dataModelDesc.findTable(pk.substring(0, pk.indexOf("."))) .getTableIdentity() + pk.substring(pk.indexOf("."))).replace('.', '#'); if (columnMap.containsKey(columnIdentity)) { columnMap.get(columnIdentity).getTYPE().add(ColumnMetaWithType.columnTypeEnum.PK); } else { // throw new BadRequestException(msg.getCOLUMN_META_INCONSISTENT()); } } for (String fk : joinDesc.getForeignKey()) { String columnIdentity = (dataModelDesc.findTable(fk.substring(0, fk.indexOf("."))) .getTableIdentity() + fk.substring(fk.indexOf("."))).replace('.', '#'); if (columnMap.containsKey(columnIdentity)) { columnMap.get(columnIdentity).getTYPE().add(ColumnMetaWithType.columnTypeEnum.FK); } else { // throw new BadRequestException(msg.getCOLUMN_META_INCONSISTENT()); } } } // update column type: DIMENSION AND MEASURE List<ModelDimensionDesc> dimensions = dataModelDesc.getDimensions(); for (ModelDimensionDesc dimension : dimensions) { for (String column : dimension.getColumns()) { String columnIdentity = (dataModelDesc.findTable(dimension.getTable()).getTableIdentity() + "." + column).replace('.', '#'); if (columnMap.containsKey(columnIdentity)) { columnMap.get(columnIdentity).getTYPE() .add(ColumnMetaWithType.columnTypeEnum.DIMENSION); } else { // throw new BadRequestException(msg.getCOLUMN_META_INCONSISTENT()); } } } String[] measures = dataModelDesc.getMetrics(); for (String measure : measures) { String columnIdentity = (dataModelDesc.findTable(measure.substring(0, measure.indexOf("."))) .getTableIdentity() + measure.substring(measure.indexOf("."))).replace('.', '#'); if (columnMap.containsKey(columnIdentity)) { columnMap.get(columnIdentity).getTYPE().add(ColumnMetaWithType.columnTypeEnum.MEASURE); } else { // throw new BadRequestException(msg.getCOLUMN_META_INCONSISTENT()); } } } } return tableMetas; }
From source file:net.ymate.platform.persistence.jdbc.scaffold.EntityGenerator.java
/** * @param dbName ???//from w ww. j av a 2 s .c o m * @param dbUserName ?? * @param tableName ?? * @return ????? */ private TableMeta getTableMeta(String dbName, String dbUserName, String tableName) { IConnectionHolder _connHolder = null; Statement _statement = null; ResultSet _resultSet = null; Map<String, ColumnInfo> _tableFields = new LinkedHashMap<String, ColumnInfo>(); List<String> _pkFields = new LinkedList<String>(); TableMeta _meta = new TableMeta(_pkFields, _tableFields); try { _connHolder = __jdbc.getDefaultConnectionHolder(); String _dbType = _connHolder.getDialect().getName(); DatabaseMetaData _dbMetaData = _connHolder.getConnection().getMetaData(); System.out.println(">>> Catalog: " + dbName); System.out.println(">>> Schema: " + dbUserName); System.out.println(">>> Table: " + tableName); _resultSet = _dbMetaData.getPrimaryKeys(dbName, _dbType.equalsIgnoreCase("oracle") ? dbUserName.toUpperCase() : dbUserName, tableName); if (_resultSet == null) { System.err.println("Database table \"" + tableName + "\" primaryKey resultSet is null, ignored"); return null; } else { while (_resultSet.next()) { _pkFields.add(_resultSet.getString(4).toLowerCase()); } if (_pkFields.isEmpty()) { System.err .println("Database table \"" + tableName + "\" does not set the primary key, ignored"); return null; } else { // System.out.println(">>> " + "COLUMN_NAME / " + "COLUMN_CLASS_NAME / " + "PRIMARY_KEY / " + "AUTO_INCREMENT / " + "SIGNED / " + "PRECISION / " + "SCALE / " + "NULLABLE / " + "DEFAULT / " + "REMARKS"); // _statement = _connHolder.getConnection().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); _resultSet = _statement.executeQuery( "SELECT * FROM ".concat(_connHolder.getDialect().wrapIdentifierQuote(tableName))); ResultSetMetaData _rsMetaData = _resultSet.getMetaData(); // for (int _idx = 1; _idx <= _rsMetaData.getColumnCount(); _idx++) { // ?? ResultSet _column = _dbMetaData.getColumns(dbName, _dbType.equalsIgnoreCase("oracle") ? dbUserName.toUpperCase() : dbUserName, tableName, _rsMetaData.getColumnName(_idx)); if (_column.next()) { // ???? _tableFields.put(_rsMetaData.getColumnName(_idx).toLowerCase(), new ColumnInfo(_rsMetaData.getColumnName(_idx).toLowerCase(), _rsMetaData.getColumnClassName(_idx), _rsMetaData.isAutoIncrement(_idx), _rsMetaData.isSigned(_idx), _rsMetaData.getPrecision(_idx), _rsMetaData.getScale(_idx), _rsMetaData.isNullable(_idx), _column.getString("COLUMN_DEF"), _column.getString("REMARKS"))); System.out.println("--> " + _rsMetaData.getColumnName(_idx).toLowerCase() + "\t" + _rsMetaData.getColumnClassName(_idx) + "\t" + _pkFields.contains(_rsMetaData.getColumnName(_idx).toLowerCase()) + "\t" + _rsMetaData.isAutoIncrement(_idx) + "\t" + _rsMetaData.isSigned(_idx) + "\t" + _rsMetaData.getPrecision(_idx) + "\t" + _rsMetaData.getScale(_idx) + "\t" + _rsMetaData.isNullable(_idx) + "\t" + _column.getString("COLUMN_DEF") + "\t" + _column.getString("REMARKS")); } _column.close(); } } } } catch (Exception e) { if (e instanceof RuntimeException) { throw (RuntimeException) e; } throw new RuntimeException(e); } finally { if (_statement != null) { try { _statement.close(); } catch (SQLException e) { _LOG.warn("", e); } } if (_resultSet != null) { try { _resultSet.close(); } catch (SQLException e) { _LOG.warn("", e); } } if (_connHolder != null) { _connHolder.release(); } } return _meta; }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
@Test public void testParentReferences() throws Exception { /* Test parent references from Statement */ Statement s = this.con.createStatement(); ResultSet rs = s.executeQuery("SELECT * FROM " + dataTypeTableName); assertTrue(s.getConnection() == this.con); assertTrue(rs.getStatement() == s);/*from w w w . j a v a 2 s.co m*/ rs.close(); s.close(); /* Test parent references from PreparedStatement */ PreparedStatement ps = this.con.prepareStatement("SELECT * FROM " + dataTypeTableName); rs = ps.executeQuery(); assertTrue(ps.getConnection() == this.con); assertTrue(rs.getStatement() == ps); rs.close(); ps.close(); /* Test DatabaseMetaData queries which do not have a parent Statement */ DatabaseMetaData md = this.con.getMetaData(); assertTrue(md.getConnection() == this.con); rs = md.getCatalogs(); assertNull(rs.getStatement()); rs.close(); rs = md.getColumns(null, null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getFunctions(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getImportedKeys(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getPrimaryKeys(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getProcedureColumns(null, null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getProcedures(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getSchemas(); assertNull(rs.getStatement()); rs.close(); rs = md.getTableTypes(); assertNull(rs.getStatement()); rs.close(); rs = md.getTables(null, null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getTypeInfo(); assertNull(rs.getStatement()); rs.close(); }
From source file:it.eng.spagobi.meta.initializer.PhysicalModelInitializer.java
/** * Get tables and columns names that are present in the database but not in the passed physical model * /*from w w w. j av a2 s . c o m*/ * @param connection * jdbc connection to the database * @param physicalModel * physical model to check */ public List<String> getRemovedTablesAndColumnsNames(Connection connection, PhysicalModel physicalModel) { try { DatabaseMetaData dbMeta = connection.getMetaData(); List<String> tablesOnDatabase = new ArrayList<String>(); List<String> tablesRemoved = new ArrayList<String>(); List<String> columnsRemoved = new ArrayList<String>(); ResultSet tableRs = dbMeta.getTables(physicalModel.getCatalog(), physicalModel.getSchema(), null, new String[] { "TABLE", "VIEW" }); while (tableRs.next()) { String tableName = tableRs.getString("TABLE_NAME"); tablesOnDatabase.add(tableName); } tableRs.close(); EList<PhysicalTable> originalTables = physicalModel.getTables(); Iterator<PhysicalTable> physicalTablesIterator = originalTables.iterator(); // 1- Check table existence while (physicalTablesIterator.hasNext()) { PhysicalTable originalTable = physicalTablesIterator.next(); String tableToFind = originalTable.getName(); if (!tablesOnDatabase.contains(tableToFind)) { // tables not found on database -> removed tablesRemoved.add(tableToFind); } else { // 2 - Check columns existence List<PhysicalColumn> physicalColumns = originalTable.getColumns(); ResultSet rs = dbMeta.getColumns(physicalModel.getCatalog(), physicalModel.getSchema(), originalTable.getName(), null); List<String> columnsNamesOnDb = new ArrayList<String>(); while (rs.next()) { String columnName = rs.getString("COLUMN_NAME"); columnsNamesOnDb.add(columnName); } for (PhysicalColumn physicalColumn : physicalColumns) { if (!columnsNamesOnDb.contains(physicalColumn.getName())) { // column not found on database -> removed columnsRemoved.add(originalTable.getName() + "." + physicalColumn.getName()); } } } } // merge two list tablesRemoved.addAll(columnsRemoved); return tablesRemoved; } catch (SQLException e) { throw new RuntimeException("Physical Model - Impossible to get missing tables names", e); } }