List of usage examples for java.sql ResultSetMetaData getColumnType
int getColumnType(int column) throws SQLException;
From source file:be.dataminded.nifi.plugins.util.JdbcCommon.java
/** * Creates an Avro schema from a result set. If the table/record name is known a priori and provided, use that as a * fallback for the record name if it cannot be retrieved from the result set, and finally fall back to a default value. * * @param rs The result set to convert to Avro * @param recordName The a priori record name to use if it cannot be determined from the result set. * @return A Schema object representing the result set converted to an Avro record * @throws SQLException if any error occurs during conversion *//* w w w. j ava2s . c o m*/ public static Schema createSchema(final ResultSet rs, String recordName, boolean convertNames) throws SQLException { final ResultSetMetaData meta = rs.getMetaData(); final int nrOfColumns = meta.getColumnCount(); String tableName = StringUtils.isEmpty(recordName) ? "NiFi_ExecuteSQL_Record" : recordName; if (nrOfColumns > 0) { String tableNameFromMeta = meta.getTableName(1); if (!StringUtils.isBlank(tableNameFromMeta)) { tableName = tableNameFromMeta; } } if (convertNames) { tableName = normalizeNameForAvro(tableName); } final FieldAssembler<Schema> builder = SchemaBuilder.record(tableName).namespace("any.data").fields(); /** * Some missing Avro types - Decimal, Date types. May need some additional work. */ for (int i = 1; i <= nrOfColumns; i++) { /** * as per jdbc 4 specs, getColumnLabel will have the alias for the column, if not it will have the column name. * so it may be a better option to check for columnlabel first and if in case it is null is someimplementation, * check for alias. Postgres is the one that has the null column names for calculated fields. */ String nameOrLabel = StringUtils.isNotEmpty(meta.getColumnLabel(i)) ? meta.getColumnLabel(i) : meta.getColumnName(i); String columnName = convertNames ? normalizeNameForAvro(nameOrLabel) : nameOrLabel; switch (meta.getColumnType(i)) { case CHAR: case LONGNVARCHAR: case LONGVARCHAR: case NCHAR: case NVARCHAR: case VARCHAR: case CLOB: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; case BIT: case BOOLEAN: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().booleanType().endUnion() .noDefault(); break; case INTEGER: if (meta.isSigned(i)) { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion() .noDefault(); } else { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion() .noDefault(); } break; case SMALLINT: case TINYINT: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion() .noDefault(); break; case BIGINT: // Check the precision of the BIGINT. Some databases allow arbitrary precision (> 19), but Avro won't handle that. // If the precision > 19 (or is negative), use a string for the type, otherwise use a long. The object(s) will be converted // to strings as necessary int precision = meta.getPrecision(i); if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); } else { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion() .noDefault(); } break; // java.sql.RowId is interface, is seems to be database // implementation specific, let's convert to String case ROWID: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; case FLOAT: case REAL: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().floatType().endUnion() .noDefault(); break; case DOUBLE: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion() .noDefault(); break; // Did not find direct suitable type, need to be clarified!!!! case DECIMAL: case NUMERIC: int scale = meta.getScale(i); if (scale == 0) { if (meta.getPrecision(i) < 10) { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion() .noDefault(); } else { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType() .endUnion().noDefault(); } } else { builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion() .noDefault(); } break; // Did not find direct suitable type, need to be clarified!!!! case DATE: case TIME: case TIMESTAMP: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion() .noDefault(); break; case BINARY: case VARBINARY: case LONGVARBINARY: case ARRAY: case BLOB: builder.name(columnName).type().unionOf().nullBuilder().endNull().and().bytesType().endUnion() .noDefault(); break; default: throw new IllegalArgumentException("createSchema: Unknown SQL type " + meta.getColumnType(i) + " cannot be converted to Avro type"); } } return builder.endRecord(); }
From source file:com.cloudera.sqoop.manager.SqlManager.java
/** * Get column types for a query statement that we do not modify further. */// w ww. java 2 s.c o m protected Map<String, Integer> getColumnTypesForRawQuery(String stmt) { ResultSet results; try { results = execute(stmt); } catch (SQLException sqlE) { LOG.error("Error executing statement: " + sqlE.toString()); release(); return null; } try { Map<String, Integer> colTypes = new HashMap<String, Integer>(); int cols = results.getMetaData().getColumnCount(); ResultSetMetaData metadata = results.getMetaData(); for (int i = 1; i < cols + 1; i++) { int typeId = metadata.getColumnType(i); String colName = metadata.getColumnName(i); if (colName == null || colName.equals("")) { colName = metadata.getColumnLabel(i); } colTypes.put(colName, Integer.valueOf(typeId)); } return colTypes; } catch (SQLException sqlException) { LOG.error("Error reading from database: " + sqlException.toString()); return null; } finally { try { results.close(); getConnection().commit(); } catch (SQLException sqlE) { LOG.warn("SQLException closing ResultSet: " + sqlE.toString()); } release(); } }
From source file:com.svds.resttest.services.GenericDataService.java
/** * Obtain metaDataSet from rs and add to genericResultsOutput * // w w w .j ava 2s .c o m * @param rs result set containing metadata * @param metaDataSet metadata map to add to * @param genericResultsOutput output object to add metadata to * @throws SQLException */ private void getMetaData(ResultSet rs, Map<String, Integer> metaDataSet, GenericResultsOutput genericResultsOutput) throws SQLException { LOG.info(">>>>Starting getMetaData"); ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); List<MetaData> metaDataArrayList = genericResultsOutput.getMetaData(); for (int i = 1; i <= columnCount; i++) { metaDataSet.put(rsmd.getColumnName(i), Integer.valueOf(i)); Map<String, Object> columnAttributes = new HashMap<>(); columnAttributes.put("ColumnTypeName", rsmd.getColumnTypeName(i)); columnAttributes.put("IndexNumber", i); MetaData metaData = new MetaData(); metaData.setColumnName(rsmd.getColumnName(i)); metaData.setColumnAttributes(columnAttributes); LOG.info("getColumnType : " + rsmd.getColumnType(i)); LOG.info("getColumnTypeName : " + rsmd.getColumnTypeName(i)); LOG.info("index : " + i); metaDataArrayList.add(metaData); } LOG.info(">>>>Ending getMetaData"); }
From source file:com.mapd.utility.SQLImporter.java
private void createMapDTable(ResultSetMetaData metaData) { StringBuilder sb = new StringBuilder(); sb.append("Create table ").append(cmd.getOptionValue("targetTable")).append("("); // Now iterate the metadata try {/*from www . jav a2 s .c om*/ for (int i = 1; i <= metaData.getColumnCount(); i++) { if (i > 1) { sb.append(","); } LOGGER.debug("Column name is " + metaData.getColumnName(i)); LOGGER.debug("Column type is " + metaData.getColumnTypeName(i)); LOGGER.debug("Column type is " + metaData.getColumnType(i)); sb.append(metaData.getColumnName(i)).append(" "); sb.append(getColType(metaData.getColumnType(i), metaData.getPrecision(i), metaData.getScale(i))); } sb.append(")"); if (Integer.valueOf(cmd.getOptionValue("fragmentSize", "0")) > 0) { sb.append(" with (fragment_size = "); sb.append(cmd.getOptionValue("fragmentSize", "0")); sb.append(")"); } } catch (SQLException ex) { LOGGER.error("Error processing the metadata - " + ex.toString()); exit(1); } executeMapDCommand(sb.toString()); }
From source file:org.apache.kylin.query.adhoc.PushDownRunnerJdbcImpl.java
@Override public void executeQuery(String query, List<List<String>> results, List<SelectedColumnMeta> columnMetas) throws Exception { Statement statement = null;//from ww w . j av a 2s . co m Connection connection = this.getConnection(); ResultSet resultSet = null; //extract column metadata ResultSetMetaData metaData = null; int columnCount = 0; try { statement = connection.createStatement(); resultSet = statement.executeQuery(query); extractResults(resultSet, results); metaData = resultSet.getMetaData(); columnCount = metaData.getColumnCount(); // fill in selected column meta for (int i = 1; i <= columnCount; ++i) { columnMetas.add(new SelectedColumnMeta(metaData.isAutoIncrement(i), metaData.isCaseSensitive(i), false, metaData.isCurrency(i), metaData.isNullable(i), false, metaData.getColumnDisplaySize(i), metaData.getColumnLabel(i), metaData.getColumnName(i), null, null, null, metaData.getPrecision(i), metaData.getScale(i), metaData.getColumnType(i), metaData.getColumnTypeName(i), metaData.isReadOnly(i), false, false)); } } catch (SQLException sqlException) { throw sqlException; } finally { DBUtils.closeQuietly(resultSet); DBUtils.closeQuietly(statement); closeConnection(connection); } }
From source file:com.tesora.dve.sql.util.JdbcConnectionResourceResponse.java
private void assertEqualProxyConnMetadata(String cntxt, ProxyConnectionResourceResponse pcrr) throws Throwable { ResultSetMetaData rsmd = results.getMetaData(); ColumnSet sysColumns = pcrr.getColumns(); assertEquals(cntxt + " mismatched column set width", rsmd.getColumnCount(), sysColumns.getColumnList().size()); List<ColumnMetadata> sysCols = sysColumns.getColumnList(); for (int i = 0; i < rsmd.getColumnCount(); i++) { ColumnMetadata sc = sysCols.get(i); String colcntxt = cntxt + " column " + sc.getAliasName(); // still don't handle non column labels right assertEquals(colcntxt + " mismatched column name", rsmd.getColumnName(i + 1), sc.getName()); assertEquals(colcntxt + " mismatched column label", rsmd.getColumnLabel(i + 1), sc.getAliasName()); if (rsmd.getColumnType(i + 1) != sc.getDataType()) { // emit names - easier to read fail(colcntxt + " mismatched column type. Expected " + rsmd.getColumnTypeName(i + 1) + " (" + rsmd.getColumnType(i + 1) + ") but found " + sc.getTypeName() + " (" + sc.getDataType() + ")"); }// ww w. j a v a 2s . c o m } }
From source file:CSVWriter.java
/** * Writes the entire ResultSet to a CSV file. * * The caller is responsible for closing the ResultSet. * * @param rs the recordset to write/*from w w w . j a v a2 s. c o m*/ * @param includeColumnNames true if you want column names in the output, false otherwise * */ public void writeAll(java.sql.ResultSet rs, boolean includeColumnNames) throws SQLException, IOException { ResultSetMetaData metadata = rs.getMetaData(); if (includeColumnNames) { writeColumnNames(metadata); } int columnCount = metadata.getColumnCount(); while (rs.next()) { String[] nextLine = new String[columnCount]; for (int i = 0; i < columnCount; i++) { nextLine[i] = getColumnValue(rs, metadata.getColumnType(i + 1), i + 1); } writeNext(nextLine); } }
From source file:ResultsDecoratorSQL.java
public void write(ResultSet rs) throws IOException, SQLException { ResultSetMetaData md = rs.getMetaData(); // This assumes you're not using a Join!! String tableName = md.getTableName(1); int cols = md.getColumnCount(); StringBuffer sb = new StringBuffer("insert into ").append(tableName).append("("); for (int i = 1; i <= cols; i++) { sb.append(md.getColumnName(i));//from w ww. j av a 2 s . c o m if (i != cols) { sb.append(", "); } } sb.append(") values ("); String insertCommand = sb.toString(); while (rs.next()) { println(insertCommand); for (int i = 1; i <= cols; i++) { String tmp = rs.getString(i); if (rs.wasNull()) { print("null"); } else { int type = md.getColumnType(i); // Don't quote numeric types; quote all others for now. switch (type) { case Types.BIGINT: case Types.DECIMAL: case Types.DOUBLE: case Types.FLOAT: case Types.INTEGER: print(tmp); break; default: tmp = tmp.replaceAll("'", "''"); print("'" + tmp + "'"); } } if (i != cols) { print(", "); } } println(");"); } }
From source file:org.seasar.dbflute.logic.jdbc.metadata.synonym.DfSynonymExtractorOracle.java
protected List<DfColumnMeta> getSynonymColumns(Connection conn, UnifiedSchema synonymOwner, String synonymName) throws SQLException { final List<DfColumnMeta> columnList = new ArrayList<DfColumnMeta>(); Statement st = null;/*from w ww . jav a 2 s .c om*/ ResultSet rs = null; try { st = conn.createStatement(); final String synonymSqlName = synonymOwner.buildSchemaQualifiedName(synonymName); final String sql = "select * from " + synonymSqlName + " where 0=1"; rs = st.executeQuery(sql); final ResultSetMetaData metaData = rs.getMetaData(); int count = metaData.getColumnCount(); for (int i = 0; i < count; i++) { int index = i + 1; String columnName = metaData.getColumnName(index); int columnType = metaData.getColumnType(index); String columnTypeName = metaData.getColumnTypeName(index); int precision = metaData.getPrecision(index); int scale = metaData.getScale(index); int nullableType = metaData.isNullable(index); DfColumnMeta column = new DfColumnMeta(); column.setColumnName(columnName); column.setJdbcDefValue(columnType); column.setDbTypeName(columnTypeName); column.setColumnSize(precision); column.setDecimalDigits(scale); column.setRequired(nullableType == ResultSetMetaData.columnNoNulls); columnList.add(column); } return columnList; } finally { if (st != null) { try { st.close(); } catch (SQLException ignored) { } } if (rs != null) { try { rs.close(); } catch (SQLException ignored) { } } } }
From source file:edu.ku.brc.specify.conversion.MSULichensFixer.java
private void convertTaxonRecords() { IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn); txMapper = IdMapperMgr.getInstance().addTableMapper("taxonname", "TaxonNameID", false); txTypMapper = IdMapperMgr.getInstance().addTableMapper("TaxonomyType", "TaxonomyTypeID", false); txUnitTypMapper = IdMapperMgr.getInstance().addTableMapper("TaxonomicUnitType", "TaxonomicUnitTypeID", false);/* ww w .j a va 2 s . co m*/ mappers = new IdMapperIFace[] { txMapper, txMapper, txTypMapper, txMapper, txUnitTypMapper }; newToOldColMap.put("TaxonID", "TaxonNameID"); newToOldColMap.put("ParentID", "ParentTaxonNameID"); newToOldColMap.put("TaxonTreeDefID", "TaxonomyTypeID"); newToOldColMap.put("TaxonTreeDefItemID", "TaxonomicUnitTypeID"); newToOldColMap.put("Name", "TaxonName"); newToOldColMap.put("FullName", "FullTaxonName"); newToOldColMap.put("IsAccepted", "Accepted"); oldToNewColMap.put("TaxonNameID", "TaxonID"); oldToNewColMap.put("ParentTaxonNameID", "ParentID"); oldToNewColMap.put("TaxonomyTypeID", "TaxonTreeDefID"); oldToNewColMap.put("TaxonomicUnitTypeID", "TaxonTreeDefItemID"); oldToNewColMap.put("TaxonName", "Name"); oldToNewColMap.put("FullTaxonName", "FullName"); oldToNewColMap.put("Accepted", "IsAccepted"); BasicSQLUtils.setDBConnection(newDBConn); StringBuilder newSB = new StringBuilder(); StringBuilder vl = new StringBuilder(); for (int i = 0; i < cols.length; i++) { fieldToColHash.put(cols[i], i + 1); colToFieldHash.put(i + 1, cols[i]); if (newSB.length() > 0) newSB.append(", "); newSB.append(cols[i]); if (vl.length() > 0) vl.append(','); vl.append('?'); } StringBuilder oldSB = new StringBuilder(); for (int i = 0; i < oldCols.length; i++) { oldFieldToColHash.put(oldCols[i], i + 1); if (oldSB.length() > 0) oldSB.append(", "); oldSB.append("ttx."); oldSB.append(oldCols[i]); } rankIdOldDBInx = oldFieldToColHash.get("RankID"); String sqlStr = String.format("SELECT %s FROM taxon ", newSB.toString()); log.debug(sqlStr); String fromClause = " FROM taxonname ttx LEFT JOIN msu_lichens.taxonname_TaxonNameID ON OldID = ttx.TaxonNameID LEFT JOIN msu_lichens_6.taxon AS ntx ON NewID = ntx.TaxonID WHERE ntx.TaxonID IS NULL"; String sql = String.format("SELECT %s %s", oldSB.toString(), fromClause); log.debug(sql); String cntSQL = String.format("SELECT COUNT(*) %s", fromClause); log.debug(cntSQL); int txCnt = BasicSQLUtils.getCountAsInt(oldDBConn, cntSQL); if (frame != null) { frame.setProcess(0, txCnt); } log.debug(txCnt); String pStr = String.format("INSERT INTO taxon (%s) VALUES (%s)", newSB.toString(), vl.toString()); log.debug(pStr); try { stmtTx = newDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs1 = stmtTx.executeQuery(sqlStr); ResultSetMetaData rsmd1 = rs1.getMetaData(); colTypes = new int[rsmd1.getColumnCount()]; colSizes = new int[rsmd1.getColumnCount()]; for (int i = 0; i < colTypes.length; i++) { colTypes[i] = rsmd1.getColumnType(i + 1); colSizes[i] = rsmd1.getPrecision(i + 1); } rs1.close(); stmtTx.close(); missingParentTaxonCount = 0; lastEditedByInx = oldFieldToColHash.get("LastEditedBy"); modifiedByAgentInx = fieldToColHash.get("ModifiedByAgentID"); stmtTx = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); pStmtTx = newDBConn.prepareStatement(pStr); int cnt = 0; ResultSet rs = stmtTx.executeQuery(sql); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { processRow(rs, rsmd, null); cnt++; if (cnt % 1000 == 0) { log.debug(cnt); if (frame != null) { frame.setProcess(cnt); } } } rs.close(); if (frame != null) { frame.setProcess(txCnt, txCnt); } String msg = String.format("Stranded Taxon (no parent): %d", missingParentTaxonCount); tblWriter.log(msg); log.debug(msg); if (missingParentTaxonCount > 0) { if (frame != null) frame.setDesc("Renumbering the tree nodes, this may take a while..."); HashSet<Integer> ttdHash = new HashSet<Integer>(); for (CollectionInfo colInfo : CollectionInfo.getFilteredCollectionInfoList()) { if (!ttdHash.contains(colInfo.getTaxonTreeDef().getId())) { DataProviderSessionIFace session = null; try { session = DataProviderFactory.getInstance().createSession(); TaxonTreeDef taxonTreeDef = colInfo.getTaxonTreeDef(); taxonTreeDef = (TaxonTreeDef) session .getData("FROM TaxonTreeDef WHERE id = " + taxonTreeDef.getId()); sql = "SELECT TaxonID FROM taxon WHERE RankID = 0 AND TaxonTreeDefID = " + taxonTreeDef.getId(); log.debug(sql); Integer txRootId = BasicSQLUtils.getCount(sql); Taxon txRoot = (Taxon) session.getData("FROM Taxon WHERE id = " + txRootId); NodeNumberer<Taxon, TaxonTreeDef, TaxonTreeDefItem> nodeNumberer = new NodeNumberer<Taxon, TaxonTreeDef, TaxonTreeDefItem>( txRoot.getDefinition()); nodeNumberer.doInBackground(); } catch (Exception ex) { //session.rollback(); ex.printStackTrace(); } finally { if (session != null) { session.close(); } } ttdHash.add(colInfo.getTaxonTreeDef().getId()); } } if (frame != null) frame.setDesc("Renumbering done."); } missingParentTaxonCount = 0; } catch (SQLException ex) { ex.printStackTrace(); } finally { try { stmtTx.close(); pStmtTx.close(); } catch (Exception ex) { } } System.out.println("Done."); }