Example usage for java.sql DatabaseMetaData getColumns

List of usage examples for java.sql DatabaseMetaData getColumns

Introduction

In this page you can find the example usage for java.sql DatabaseMetaData getColumns.

Prototype

ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
        throws SQLException;

Source Link

Document

Retrieves a description of table columns available in the specified catalog.

Usage

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);

    }
}