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.apache.ddlutils.task.DumpMetadataTask.java

/**
 * Dumps the columns of the indicated table.
 * //w  w w.  j av a2  s  .  co  m
 * @param xmlWriter   The xml writer to write to
 * @param metaData    The database metadata
 * @param catalogName The catalog name
 * @param schemaName  The schema name
 * @param tableName   The table name
 */
private void dumpColumns(PrettyPrintingXmlWriter xmlWriter, final DatabaseMetaData metaData,
        final String catalogName, final String schemaName, final String tableName) throws SQLException {
    performResultSetXmlOperation(xmlWriter, null, new ResultSetXmlOperation() {
        public ResultSet getResultSet() throws SQLException {
            return metaData.getColumns(catalogName, schemaName, tableName, _columnPattern);
        }

        public void handleRow(PrettyPrintingXmlWriter xmlWriter, ResultSet result) throws SQLException {
            Set columns = getColumnsInResultSet(result);
            String columnName = result.getString("COLUMN_NAME");

            if ((columnName != null) && (columnName.length() > 0)) {
                xmlWriter.writeElementStart(null, "column");
                xmlWriter.writeAttribute(null, "name", columnName);

                addIntAttribute(xmlWriter, "typeCode", result, columns, "DATA_TYPE");
                addStringAttribute(xmlWriter, "type", result, columns, "TYPE_NAME");
                addIntAttribute(xmlWriter, "size", result, columns, "COLUMN_SIZE");
                addIntAttribute(xmlWriter, "digits", result, columns, "DECIMAL_DIGITS");
                addIntAttribute(xmlWriter, "precision", result, columns, "NUM_PREC_RADIX");
                if (columns.contains("NULLABLE")) {
                    try {
                        switch (result.getInt("NULLABLE")) {
                        case DatabaseMetaData.columnNoNulls:
                            xmlWriter.writeAttribute(null, "nullable", "false");
                            break;
                        case DatabaseMetaData.columnNullable:
                            xmlWriter.writeAttribute(null, "nullable", "true");
                            break;
                        default:
                            xmlWriter.writeAttribute(null, "nullable", "unknown");
                            break;
                        }
                    } catch (SQLException ex) {
                        log("Could not read the NULLABLE value for colum '" + columnName + "' of table '"
                                + tableName + "' from the result set: " + ex.getStackTrace(), Project.MSG_ERR);
                    }
                }
                addStringAttribute(xmlWriter, "remarks", result, columns, "REMARKS");
                addStringAttribute(xmlWriter, "defaultValue", result, columns, "COLUMN_DEF");
                addIntAttribute(xmlWriter, "maxByteLength", result, columns, "CHAR_OCTET_LENGTH");
                addIntAttribute(xmlWriter, "index", result, columns, "ORDINAL_POSITION");
                if (columns.contains("IS_NULLABLE")) {
                    try {
                        String value = result.getString("IS_NULLABLE");

                        if ("no".equalsIgnoreCase(value)) {
                            xmlWriter.writeAttribute(null, "isNullable", "false");
                        } else if ("yes".equalsIgnoreCase(value)) {
                            xmlWriter.writeAttribute(null, "isNullable", "true");
                        } else {
                            xmlWriter.writeAttribute(null, "isNullable", "unknown");
                        }
                    } catch (SQLException ex) {
                        log("Could not read the IS_NULLABLE value for colum '" + columnName + "' of table '"
                                + tableName + "' from the result set: " + ex.getStackTrace(), Project.MSG_ERR);
                    }
                }
                addStringAttribute(xmlWriter, "refCatalog", result, columns, "SCOPE_CATLOG");
                addStringAttribute(xmlWriter, "refSchema", result, columns, "SCOPE_SCHEMA");
                addStringAttribute(xmlWriter, "refTable", result, columns, "SCOPE_TABLE");
                addShortAttribute(xmlWriter, "sourceTypeCode", result, columns, "SOURCE_DATA_TYPE");

                xmlWriter.writeElementEnd();
            }
        }

        public void handleError(SQLException ex) {
            log("Could not read the colums for table '" + tableName + "' from the result set: "
                    + ex.getStackTrace(), Project.MSG_ERR);
        }
    });
}

From source file:com.evolveum.midpoint.repo.sql.OrgClosureManager.java

private boolean autoUpdateClosureTableStructure(SqlRepositoryServiceImpl service) {

    if (repoConfiguration.isSkipOrgClosureStructureCheck()) {
        LOGGER.debug("Skipping org closure structure check.");
        return false;
    }/*from  w ww  .  ja v a2s . c o m*/

    SessionFactory sf = service.getSessionFactory();
    if (sf instanceof SessionFactoryImpl) {
        SessionFactoryImpl sfi = ((SessionFactoryImpl) sf);
        LOGGER.debug("SessionFactoryImpl.getSettings() = {}; auto update schema = {}", sfi.getSettings(),
                sfi.getSettings() != null ? sfi.getSettings().isAutoUpdateSchema() : null);
        if (sfi.getSettings() != null && sfi.getSettings().isAutoUpdateSchema()) {

            LOGGER.info("Checking the closure table structure.");

            final Session session = service.getSessionFactory().openSession();
            final Holder<Boolean> wrongNumberOfColumns = new Holder<>(false);
            session.doWork(new Work() {
                @Override
                public void execute(Connection connection) throws SQLException {
                    DatabaseMetaData meta = connection.getMetaData();
                    if (meta == null) {
                        LOGGER.warn("No database metadata found.");
                    } else {
                        ResultSet rsColumns = meta.getColumns(null, null, CLOSURE_TABLE_NAME, null);
                        int columns = 0;
                        while (rsColumns.next()) {
                            LOGGER.debug("Column: {} {}", rsColumns.getString("TYPE_NAME"),
                                    rsColumns.getString("COLUMN_NAME"));
                            columns++;
                        }
                        if (columns > 0) {
                            LOGGER.info("There are {} columns in {} (obtained via DatabaseMetaData)", columns,
                                    CLOSURE_TABLE_NAME);
                            if (columns != 3) {
                                wrongNumberOfColumns.setValue(true);
                            }
                            return;
                        }
                        // perhaps some problem here... let's try another way out
                        try {
                            Statement stmt = connection.createStatement();
                            ResultSet rs = stmt.executeQuery("select * from " + CLOSURE_TABLE_NAME);
                            int cols = rs.getMetaData().getColumnCount();
                            if (cols > 0) {
                                LOGGER.info("There are {} columns in {} (obtained via resultSet.getMetaData())",
                                        cols, CLOSURE_TABLE_NAME);
                                if (cols != 3) {
                                    wrongNumberOfColumns.setValue(true);
                                }
                            } else {
                                LOGGER.warn(
                                        "Couldn't determine the number of columns in {}. In case of problems, please fix your database structure manually using DB scripts in 'config' folder.",
                                        CLOSURE_TABLE_NAME);
                            }
                            rs.close(); // don't care about closing them in case of failure
                            stmt.close();
                        } catch (RuntimeException e) {
                            LoggingUtils.logException(LOGGER,
                                    "Couldn't obtain the number of columns in {}. In case of problems running midPoint, please fix your database structure manually using DB scripts in 'config' folder.",
                                    e, CLOSURE_TABLE_NAME);
                        }
                    }
                }
            });
            if (wrongNumberOfColumns.getValue()) {
                session.getTransaction().begin();
                LOGGER.info("Wrong number of columns detected; dropping table " + CLOSURE_TABLE_NAME);
                Query q = session.createSQLQuery("drop table " + CLOSURE_TABLE_NAME);
                q.executeUpdate();
                session.getTransaction().commit();

                LOGGER.info(
                        "Calling hibernate hbm2ddl SchemaUpdate tool to create the table in the necessary form.");
                new SchemaUpdate(sfi.getServiceRegistry(), service.getSessionFactoryBean().getConfiguration())
                        .execute(false, true);
                LOGGER.info(
                        "Done, table was (hopefully) created. If not, please fix your database structure manually using DB scripts in 'config' folder.");
                return true;
            }
        } else {
            // auto schema update is disabled
        }
    } else {
        LOGGER.warn("SessionFactory is not of type SessionFactoryImpl; it is {}",
                sf != null ? sf.getClass() : "null");
    }
    return false;
}

From source file:org.apache.ctakes.ytex.uima.mapper.DocumentMapperServiceImpl.java

/**
 * load mapping info/*  w w  w  .  ja v a  2s .c o  m*/
 * 
 * @param type
 * @return
 */
private AnnoMappingInfo initMapInfo(final FeatureStructure fs) {
    final Type type = fs.getType();
    final String annoName = type.getShortName().toLowerCase();
    AnnoMappingInfo mapInfoTmp;
    final UimaType ut = uimaTypeMap.get(type.getName());
    if (this.mapAnnoMappingInfo.containsKey(type.getName())) {
        mapInfoTmp = this.mapAnnoMappingInfo.get(type.getName()).deepCopy();
    } else {
        mapInfoTmp = new AnnoMappingInfo();
    }
    final AnnoMappingInfo mapInfo = mapInfoTmp;
    if (ut != null)
        mapInfo.setUimaTypeId(ut.getUimaTypeID());
    // first see if the table name has been set in beans-uima.xml
    if (Strings.isNullOrEmpty(mapInfo.getTableName())) {
        // next see if the table name has been set in ref_uima_type
        if (ut != null && !Strings.isNullOrEmpty(ut.getTableName()))
            mapInfo.setTableName(ut.getTableName());
        else
            // default to anno_[short name]
            mapInfo.setTableName("anno_" + annoName);
    }
    final List<Feature> features = type.getFeatures();
    // get the non primitive fields
    for (Feature f : features) {
        if (f.getRange().isArray() && !f.getRange().getComponentType().isPrimitive()) {
            // add this field to the list of fields to store
            this.tl_mapFieldInfo.get().put(type.getName(), f.getShortName());
        }
    }
    this.sessionFactory.getCurrentSession().doWork(new Work() {
        @Override
        public void execute(Connection conn) throws SQLException {
            ResultSet rs = null;

            try {
                DatabaseMetaData dmd = conn.getMetaData();
                // get columns for corresponding table
                // mssql - add schema prefix
                // oracle - convert table name to upper case
                rs = dmd.getColumns(null, "mssql".equals(dbType) || "hsql".equals(dbType) ? dbSchema : null,
                        "orcl".equals(dbType) || "hsql".equals(dbType) ? mapInfo.getTableName().toUpperCase()
                                : mapInfo.getTableName(),
                        null);
                while (rs.next()) {
                    String colName = rs.getString("COLUMN_NAME");
                    int colSize = rs.getInt("COLUMN_SIZE");
                    int dataType = rs.getInt("DATA_TYPE");
                    if ("anno_base_id".equalsIgnoreCase(colName)) {
                        // skip anno_base_id
                        continue;
                    }
                    if ("uima_type_id".equalsIgnoreCase(colName)) {
                        // see if there is a uima_type_id column
                        // for FeatureStructures that are not annotations
                        // there can be a field for the uima_type_id
                        if (!(fs instanceof Annotation)
                                && Strings.isNullOrEmpty(mapInfo.getUimaTypeIdColumnName())) {
                            mapInfo.setUimaTypeIdColumnName(colName);
                        }
                    } else if ("coveredText".equalsIgnoreCase(colName)) {
                        // see if there is a coveredText column, store the
                        // covered
                        // text here
                        ColumnMappingInfo coveredTextColumn = new ColumnMappingInfo();
                        coveredTextColumn.setColumnName(colName);
                        mapInfo.setCoveredTextColumn(coveredTextColumn);
                        coveredTextColumn.setSize(colSize);
                    } else {
                        // possibility 1: the column is already mapped to
                        // the field
                        // if so, then just set the size
                        if (!updateSize(mapInfo, colName, colSize, dataType)) {
                            // possibility 2: the column is not mapped - see
                            // if
                            // it matches a field
                            // iterate through features, see which match the
                            // column
                            for (Feature f : features) {
                                String annoFieldName = f.getShortName();
                                if (f.getRange().isPrimitive() && annoFieldName.equalsIgnoreCase(colName)) {
                                    // primitive attribute
                                    ColumnMappingInfo fmap = new ColumnMappingInfo();
                                    fmap.setAnnoFieldName(annoFieldName);
                                    fmap.setColumnName(colName);
                                    fmap.setSize(colSize);
                                    fmap.setSqlType(dataType);
                                    mapInfo.getMapField().put(colName, fmap);
                                    break;
                                } else if (!f.getRange().isArray() && !f.getRange().isPrimitive()
                                        && annoFieldName.equalsIgnoreCase(colName)
                                        && (dataType == Types.INTEGER || dataType == Types.SMALLINT
                                                || dataType == Types.BIGINT || dataType == Types.NUMERIC
                                                || dataType == Types.FLOAT || dataType == Types.DOUBLE)) {
                                    // this feature is a reference to
                                    // another
                                    // annotation.
                                    // this column is numeric - a match
                                    ColumnMappingInfo fmap = new ColumnMappingInfo();
                                    fmap.setAnnoFieldName(annoFieldName);
                                    fmap.setColumnName(colName);
                                    fmap.setSize(colSize);
                                    fmap.setSqlType(dataType);
                                    mapInfo.getMapField().put(colName, fmap);
                                    break;
                                }
                            }
                        }
                    }
                }
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException e) {
                    }
                }
            }
        }
    });
    // don't map this annotation if no fields match columns
    if (mapInfo.getMapField().size() == 0 && mapInfo.getCoveredTextColumn() == null
            && Strings.isNullOrEmpty(mapInfo.getUimaTypeIdColumnName()))
        return null;
    // generate sql
    StringBuilder b = new StringBuilder("insert into ");
    b.append(this.getTablePrefix()).append(mapInfo.getTableName());
    b.append("(anno_base_id");
    // add coveredText column if available
    if (mapInfo.getCoveredTextColumn() != null) {
        b.append(", coveredText");
    }
    // add uima_type_id column if available
    if (mapInfo.getUimaTypeIdColumnName() != null) {
        b.append(", uima_type_id");
    }
    // add other fields
    for (Map.Entry<String, ColumnMappingInfo> fieldEntry : mapInfo.getMapField().entrySet()) {
        b.append(", ").append(dialect.openQuote()).append(fieldEntry.getValue().getColumnName())
                .append(dialect.closeQuote());
    }
    b.append(") values (?");
    // add coveredText bind param
    if (mapInfo.getCoveredTextColumn() != null) {
        b.append(", ?");
    }
    // add uimaTypeId bind param
    if (mapInfo.getUimaTypeIdColumnName() != null) {
        b.append(", ?");
    }
    // add bind params for other fields
    b.append(Strings.repeat(", ?", mapInfo.getMapField().size())).append(")");
    mapInfo.setSql(b.toString());
    if (log.isInfoEnabled())
        log.info("sql insert for type " + type.getName() + ": " + mapInfo.getSql());
    if (log.isDebugEnabled())
        log.debug("initMapInfo(" + annoName + "): " + mapInfo);
    return mapInfo;
}

From source file:org.nuclos.server.masterdata.ejb3.MetaDataFacadeBean.java

/**
 * @return Script (with results if selected)
 *///from w  w w.  j  a v  a2s  . c  o  m
@Override
@RolesAllowed("Login")
public List<MasterDataVO> transformTable(String url, String user, String password, String schema,
        String table) {

    List<MasterDataVO> lstFields = new ArrayList<MasterDataVO>();

    Connection connect = null;
    try {
        connect = DriverManager.getConnection(url, user, password);
        DatabaseMetaData dbmeta = connect.getMetaData();
        ResultSet rsCols = dbmeta.getColumns(null, schema.toUpperCase(), table, "%");
        while (rsCols.next()) {
            String colName = rsCols.getString("COLUMN_NAME");
            int colsize = rsCols.getInt("COLUMN_SIZE");
            int postsize = rsCols.getInt("DECIMAL_DIGITS");
            int columsType = rsCols.getInt("DATA_TYPE");
            String sJavaType = getBestJavaType(columsType);
            if (postsize > 0)
                sJavaType = "java.lang.Double";

            MasterDataMetaVO metaFieldVO = masterDataFacade
                    .getMetaData(NuclosEntity.ENTITYFIELD.getEntityName());
            MasterDataVO mdFieldVO = new MasterDataVO(metaFieldVO, false);

            mdFieldVO.setField("foreignentityfield", null);
            mdFieldVO.setField("unique", Boolean.FALSE);
            mdFieldVO.setField("logbook", Boolean.FALSE);
            mdFieldVO.setField("entity", NuclosEntity.ENTITYFIELD.getEntityName());
            mdFieldVO.setField("formatinput", null);
            mdFieldVO.setField("entityId", null);
            mdFieldVO.setField("datascale", colsize);
            mdFieldVO.setField("label", org.apache.commons.lang.StringUtils.capitalize(colName.toLowerCase()));
            mdFieldVO.setField("nullable", Boolean.TRUE);
            mdFieldVO.setField("dataprecision", postsize);
            mdFieldVO.setField("dbfield", colName.toLowerCase());
            mdFieldVO.setField("description",
                    org.apache.commons.lang.StringUtils.capitalize(colName.toLowerCase()));
            mdFieldVO.setField("name", colName.toLowerCase());
            mdFieldVO.setField("entityfieldDefault", null);
            mdFieldVO.setField("foreignentity", null);
            mdFieldVO.setField("formatoutput", null);
            mdFieldVO.setField("datatype", sJavaType);
            mdFieldVO.setField("searchable", Boolean.FALSE);
            mdFieldVO.setField("foreignentity", null);
            mdFieldVO.setField("foreignentityfield", null);
            lstFields.add(mdFieldVO);
        }
        rsCols.close();
    } catch (Exception e) {
        LOG.info("transformTable: " + e, e);
    } finally {
        try {
            if (connect != null) {
                connect.close();
            }
        } catch (Exception e) {
            LOG.info("transformTable: " + e, e);
        }
    }
    return lstFields;
}

From source file:com.evolveum.midpoint.repo.sql.helpers.OrgClosureManager.java

private boolean autoUpdateClosureTableStructure(SqlRepositoryServiceImpl service) {

    if (sqlRepositoryService.getConfiguration().isSkipOrgClosureStructureCheck()) {
        LOGGER.debug("Skipping org closure structure check.");
        return false;
    }//from  w  ww .ja  v a 2  s  . c  om

    SessionFactory sf = service.getSessionFactory();
    if (sf instanceof SessionFactoryImpl) {
        SessionFactoryImpl sfi = ((SessionFactoryImpl) sf);
        LOGGER.debug("SessionFactoryImpl.getSettings() = {}; auto update schema = {}", sfi.getSettings(),
                sfi.getSettings() != null ? sfi.getSettings().isAutoUpdateSchema() : null);
        if (sfi.getSettings() != null && sfi.getSettings().isAutoUpdateSchema()) {

            LOGGER.info("Checking the closure table structure.");

            final Session session = service.getSessionFactory().openSession();
            final Holder<Boolean> wrongNumberOfColumns = new Holder<>(false);
            session.doWork(new Work() {
                @Override
                public void execute(Connection connection) throws SQLException {
                    DatabaseMetaData meta = connection.getMetaData();
                    if (meta == null) {
                        LOGGER.warn("No database metadata found.");
                    } else {
                        ResultSet rsColumns = meta.getColumns(null, null, CLOSURE_TABLE_NAME, null);
                        int columns = 0;
                        while (rsColumns.next()) {
                            LOGGER.debug("Column: {} {}", rsColumns.getString("TYPE_NAME"),
                                    rsColumns.getString("COLUMN_NAME"));
                            columns++;
                        }
                        if (columns > 0) {
                            LOGGER.debug("There are {} columns in {} (obtained via DatabaseMetaData)", columns,
                                    CLOSURE_TABLE_NAME);
                            if (columns != 3) {
                                wrongNumberOfColumns.setValue(true);
                            }
                            return;
                        }
                        // perhaps some problem here... let's try another way out
                        try {
                            Statement stmt = connection.createStatement();
                            ResultSet rs = stmt.executeQuery("select * from " + CLOSURE_TABLE_NAME);
                            int cols = rs.getMetaData().getColumnCount();
                            if (cols > 0) {
                                LOGGER.debug(
                                        "There are {} columns in {} (obtained via resultSet.getMetaData())",
                                        cols, CLOSURE_TABLE_NAME);
                                if (cols != 3) {
                                    wrongNumberOfColumns.setValue(true);
                                }
                            } else {
                                LOGGER.warn(
                                        "Couldn't determine the number of columns in {}. In case of problems, please fix your database structure manually using DB scripts in 'config' folder.",
                                        CLOSURE_TABLE_NAME);
                            }
                            rs.close(); // don't care about closing them in case of failure
                            stmt.close();
                        } catch (RuntimeException e) {
                            LoggingUtils.logException(LOGGER,
                                    "Couldn't obtain the number of columns in {}. In case of problems running midPoint, please fix your database structure manually using DB scripts in 'config' folder.",
                                    e, CLOSURE_TABLE_NAME);
                        }
                    }
                }
            });
            if (wrongNumberOfColumns.getValue()) {
                session.getTransaction().begin();
                LOGGER.info("Wrong number of columns detected; dropping table " + CLOSURE_TABLE_NAME);
                Query q = session.createSQLQuery("drop table " + CLOSURE_TABLE_NAME);
                q.executeUpdate();
                session.getTransaction().commit();

                LOGGER.info(
                        "Calling hibernate hbm2ddl SchemaUpdate tool to create the table in the necessary form.");
                new SchemaUpdate(sfi.getServiceRegistry(), service.getSessionFactoryBean().getConfiguration())
                        .execute(false, true);
                LOGGER.info(
                        "Done, table was (hopefully) created. If not, please fix your database structure manually using DB scripts in 'config' folder.");
                return true;
            }
        } else {
            // auto schema update is disabled
        }
    } else {
        LOGGER.warn("SessionFactory is not of type SessionFactoryImpl; it is {}",
                sf != null ? sf.getClass() : "null");
    }
    return false;
}

From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java

/**
 * @param connection/*ww w.  j  av a2 s. c  o m*/
 * @param tableName
 * @return
 */
public static boolean doesTableExist(final Connection connection, final String tableName) {
    try {
        DatabaseMetaData mdm = connection.getMetaData();
        ResultSet rs = mdm.getColumns(connection.getCatalog(), connection.getCatalog(), tableName, null);
        if (rs.next()) {
            rs.close();
            return true;
        }
        rs.close();

    } catch (SQLException ex) {
        ex.printStackTrace();
    }
    return false;
}

From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java

public static List<String> getFieldNamesFromSchema(final Connection connection, final String tableName,
        final List<String> fields) {
    try {/*  w ww .jav a  2  s  .  c  o m*/
        DatabaseMetaData mdm = connection.getMetaData();
        ResultSet rs = mdm.getColumns(connection.getCatalog(), connection.getCatalog(), tableName, null);
        while (rs.next()) {
            fields.add(rs.getString("COLUMN_NAME"));
        }
        rs.close();
        return fields;

    } catch (SQLException ex) {
        ex.printStackTrace();
    }
    return null;
}

From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java

public static List<String> getFieldNamesFromSchema(final Connection connection, final String tableName) {
    try {//from  ww  w .  j  a v a 2s.  c  o m
        ArrayList<String> fields = new ArrayList<String>();

        DatabaseMetaData mdm = connection.getMetaData();
        ResultSet rs = mdm.getColumns(connection.getCatalog(), connection.getCatalog(), tableName, null);
        while (rs.next()) {
            fields.add(rs.getString("COLUMN_NAME"));
        }
        rs.close();
        return fields;

    } catch (SQLException ex) {
        ex.printStackTrace();
    }
    return null;
}

From source file:com.glaf.core.util.DBUtils.java

public static List<FieldDefinition> getFieldDefinitions(String tableName) {
    List<FieldDefinition> columns = new java.util.ArrayList<FieldDefinition>();
    Connection conn = null;//from  w  w w . j a  va2 s . com
    ResultSet rs = null;
    try {
        conn = DBConnectionFactory.getConnection();
        List<String> primaryKeys = getPrimaryKeys(conn, tableName);
        String dbType = DBConnectionFactory.getDatabaseType(conn);
        DatabaseMetaData metaData = conn.getMetaData();

        if ("h2".equals(dbType)) {
            tableName = tableName.toUpperCase();
        } else if ("oracle".equals(dbType)) {
            tableName = tableName.toUpperCase();
        } else if ("db2".equals(dbType)) {
            tableName = tableName.toUpperCase();
        } else if ("mysql".equals(dbType)) {
            tableName = tableName.toLowerCase();
        } else if ("postgresql".equals(dbType)) {
            tableName = tableName.toLowerCase();
        }
        rs = metaData.getColumns(null, null, tableName, null);
        while (rs.next()) {
            String name = rs.getString("COLUMN_NAME");
            int dataType = rs.getInt("DATA_TYPE");
            int nullable = rs.getInt("NULLABLE");
            int length = rs.getInt("COLUMN_SIZE");
            int ordinal = rs.getInt("ORDINAL_POSITION");
            FieldDefinition column = new ColumnDefinition();
            column.setColumnName(name);
            column.setType(FieldType.getJavaType(dataType));
            if (nullable == 1) {
                column.setNullable(true);
            } else {
                column.setNullable(false);
            }
            column.setLength(length);
            column.setSortNo(ordinal);

            if ("String".equals(column.getType())) {
                if (column.getLength() > 8000) {
                    column.setType("Clob");
                }
            }

            if (primaryKeys.contains(name)) {
                column.setNullable(false);
            }

            column.setName(StringTools.camelStyle(name));
            column.setEnglishTitle(StringTools.camelStyle(name));

            columns.add(column);
        }
        return columns;
    } catch (Exception ex) {
        ex.printStackTrace();
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(rs);
        JdbcUtils.close(conn);
    }
}

From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java

/**
 * @param connection//from w  ww . ja  v  a 2s  .  co  m
 * @param tableName
 * @return
 */
public static List<FieldMetaData> getFieldMetaDataFromSchema(final Connection connection,
        final String tableName) {
    try {
        ArrayList<FieldMetaData> fields = new ArrayList<FieldMetaData>();

        DatabaseMetaData mdm = connection.getMetaData();
        ResultSet rs = mdm.getColumns(connection.getCatalog(), connection.getCatalog(), tableName, null);
        while (rs.next()) {
            /*System.out.println("-------- " + rs.getString("COLUMN_NAME")+" ----------");
            for (int i=1;i<=rs.getMetaData().getColumnCount();i++)
            {
            System.out.println(rs.getMetaData().getColumnName(i)+"="+rs.getObject(i));
                    
            }*/

            String typeStr = rs.getString("TYPE_NAME");
            FieldMetaData fmd = new FieldMetaData(rs.getString("COLUMN_NAME"), typeStr,
                    typeStr.startsWith("DATE"), false, StringUtils.contains(typeStr.toLowerCase(), "varchar"));
            fmd.setSqlType(rs.getInt("DATA_TYPE"));
            fields.add(fmd);
        }
        rs.close();
        return fields;

    } catch (SQLException ex) {
        ex.printStackTrace();
    }
    return null;
}