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.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; }