List of usage examples for java.sql ResultSetMetaData getColumnLabel
String getColumnLabel(int column) throws SQLException;
From source file:org.jboss.dashboard.dataset.sql.SQLDataSet.java
public void load() throws Exception { DataSource targetDS = CoreServices.lookup().getDataSourceManager().getDataSource(dataSource); if (targetDS == null) return;// w ww .j a va 2s .c o m Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; CodeBlockTrace trace = null; try { // Get the connection. conn = targetDS.getConnection(); // Execute the query. lastExecutedStmt = createSQLStatament(); trace = new SQLStatementTrace(lastExecutedStmt.getSQLSentence()).begin(); trace.addRuntimeConstraint(new DataSetLoadConstraints(this)); log.debug("Load data set from datasource=" + dataSource + " SQL=" + lastExecutedStmt.getSQLSentence()); stmt = lastExecutedStmt.getPreparedStatement(conn); rs = stmt.executeQuery(); // Get the properties definition. ResultSetMetaData meta = rs.getMetaData(); int propsSize = meta.getColumnCount(); SQLDataSet.this.setPropertySize(propsSize); for (int i = 0; i < propsSize; i++) { SQLDataProperty dp = createSQLProperty(); String propId = StringUtils.isNotBlank(meta.getColumnLabel(i + 1)) ? meta.getColumnLabel(i + 1) : meta.getColumnName(i + 1); dp.setPropertyId(propId.toLowerCase()); // dp.setPropertyId(meta.getColumnName(i + 1).toLowerCase()); dp.setType(meta.getColumnType(i + 1)); dp.setTableName(meta.getTableName(i + 1)); dp.setColumnName(meta.getColumnName(i + 1)); addProperty(dp, i); } // Get rows and populate the data set values. int index = 0; while (rs.next()) { Object[] row = new Object[propsSize]; for (int i = 0; i < propsSize; i++) row[i] = rs.getObject(i + 1); SQLDataSet.this.addRowValues(row); // Check load constraints (every 10,000 rows) if (++index == 10000) { trace.checkRuntimeConstraints(); index = 0; } } // Once we got the dataset initialized then calculate the domain for each property. for (int i = 0; i < properties.length; i++) { SQLDataProperty property = (SQLDataProperty) properties[i]; property.calculateDomain(); } } catch (Exception e) { if (lastExecutedStmt != null) { log.error("Error in load() SQLDataset. SQL = " + lastExecutedStmt.getSQLSentence(), e); } throw e; } finally { try { if (rs != null) rs.close(); } catch (Exception e) { log.warn("Error closing ResultSet: ", e); } try { if (stmt != null) stmt.close(); } catch (Exception e) { log.warn("Error closing PreparedStatement: ", e); } if (conn != null) { conn.close(); } if (trace != null) { trace.end(); } } }
From source file:com.graphaware.importer.data.access.QueueDbDataReader.java
/** * {@inheritDoc}//from www. jav a 2 s . com */ @Override public final void read(final String query, final String hint) { if (records != null) { throw new IllegalStateException("Previous reader hasn't been closed"); } LOG.info("Start query: \n" + query); if (query.startsWith("alter")) { jdbcTemplate.execute(query); noMoreRecords = true; return; } records = new ArrayBlockingQueue<>(queueCapacity()); new Thread(new Runnable() { @Override public void run() { Date d1 = Calendar.getInstance().getTime(); try { jdbcTemplate.query(query, new ResultSetExtractor<Void>() { @Override public Void extractData(ResultSet rs) throws SQLException, DataAccessException { ResultSetMetaData metaData = rs.getMetaData(); int colCount = metaData.getColumnCount(); while (rs.next()) { Map<String, String> columns = new HashMap<>(); for (int i = 1; i <= colCount; i++) { columns.put(metaData.getColumnLabel(i), rs.getString(i)); } columns.put(ROW, String.valueOf(rs.getRow())); try { records.offer(columns, 1, TimeUnit.HOURS); } catch (InterruptedException e) { LOG.warn( "Was waiting for more than 1 hour to insert a record for processing, had to drop it"); } } return null; } }); } finally { noMoreRecords = true; } long diffInSeconds = TimeUnit.MILLISECONDS .toSeconds(Calendar.getInstance().getTime().getTime() - d1.getTime()); LOG.info("Finished querying for " + hint + " in " + diffInSeconds + " seconds"); } }, "DB READER - " + hint).start(); }
From source file:org.jpos.qi.system.SQLQueryObject.java
@Override public String toString() { try {/*from w w w . j av a2 s.co m*/ Object res = DB.exec(db -> { StringBuilder sb = new StringBuilder(""); for (int n = 0; n < queries.length; n++) { String query = queries[n]; String title = titles[n]; int mxrows = maxRows[n]; sb.append(' ').append(title).append("\n\n"); db.session().doWork(new Work() { @Override public void execute(Connection connection) throws SQLException { PreparedStatement stmt = connection.prepareStatement(query); ResultSet rs = stmt.executeQuery(); ResultSetMetaData md = rs.getMetaData(); int cols = md.getColumnCount(); String[] header = new String[cols]; int[] colsize = new int[cols]; for (int i = 1; i <= cols; i++) { header[i - 1] = StringUtils.defaultIfEmpty(md.getColumnLabel(i), md.getColumnName(i)); colsize[i - 1] = header[i - 1].length(); } int rows = 0; String[][] out = new String[mxrows][cols]; while (rs.next() && rows < mxrows) { for (int i = 1; i <= cols; i++) { out[rows][i - 1] = rs.getString(i); if (out[rows][i - 1] == null) out[rows][i - 1] = " "; int l = out[rows][i - 1].length(); if (colsize[i - 1] < l) colsize[i - 1] = l; } rows++; } rs.close(); stmt.close(); StringBuilder sbSep = new StringBuilder(" "); sb.append(' '); for (int i = 1; i <= cols; i++) { if (isNumericDataType(md.getColumnType(i))) sb.append(StringUtils.leftPad(header[i - 1], colsize[i - 1])); else sb.append(StringUtils.rightPad(header[i - 1], colsize[i - 1])); sbSep.append(StringUtils.repeat('-', colsize[i - 1])); sb.append(' '); sbSep.append(' '); } sb.append('\n'); sbSep.append('\n'); sb.append(sbSep); for (int j = 0; j < rows; j++) { sb.append(' '); for (int i = 1; i <= cols; i++) { if (isNumericDataType(md.getColumnType(i))) sb.append(StringUtils.leftPad(out[j][i - 1], colsize[i - 1])); else sb.append(StringUtils.rightPad(out[j][i - 1], colsize[i - 1])); sb.append(' '); } sb.append('\n'); } sb.append(sbSep).append('\n'); } }); } sb.append(" Last refreshed at ").append(new Date()); return sb; }); return res.toString(); } catch (Exception e) { QI.getQI().getLog().error(e); return e.toString(); } }
From source file:org.sakaiproject.lap.dao.Data.java
/** * Processes the results from a query/*from w w w . j av a 2s. c om*/ * 1. Executes the prepared statement * 2. Creates CSV strings * 3. Saves CSV string to a file * * @param preparedStatement the prepared statement * @param directory the directory to save the file in * @param fileName the name of the file * @param isManualExtraction is this from a manual extraction? * @return true, if successful operations * @throws Exception on errors */ private boolean saveResultsToFile(PreparedStatement preparedStatement, String directory, String fileName, boolean isManualExtraction) throws Exception { ResultSet results = executePreparedStatement(preparedStatement); ResultSetMetaData metadata = results.getMetaData(); int numberOfColumns = metadata.getColumnCount(); // header row List<String> header = new ArrayList<String>(); for (int i = 1; i <= numberOfColumns; i++) { header.add(metadata.getColumnLabel(i)); } String csvData = CsvUtils.setAsCsvRow(header); // data rows while (results.next()) { List<String> row = new ArrayList<String>(); for (int i = 1; i <= numberOfColumns; i++) { row.add(results.getString(i)); } csvData += CsvUtils.setAsCsvRow(row); } boolean success = fileService.saveStringToFile(csvData, directory, fileName, isManualExtraction); return success; }
From source file:org.danann.cernunnos.sql.ColumnIteratorTask.java
public void perform(TaskRequest req, TaskResponse res) { ResultSetMetaData rsmd = (ResultSetMetaData) req.getAttribute(SqlAttributes.RESULT_SET_METADATA); final int columnCount; try {//www.j a va 2 s . c o m columnCount = rsmd.getColumnCount(); } catch (SQLException sqle) { throw SQL_EXCEPTION_TRANSLATOR.translate("Failed to get column count from ResultSetMetaData", "UNKNOWN", sqle); } for (int i = 1; i <= columnCount; i++) { final String columnName; try { columnName = rsmd.getColumnLabel(i); } catch (SQLException sqle) { throw SQL_EXCEPTION_TRANSLATOR .translate("Failed to get column name " + i + " from ResultSetMetaData", "UNKNOWN", sqle); } res.setAttribute(SqlAttributes.COLUMN_NAME, columnName); super.performSubtasks(req, res); } }
From source file:org.onion.ezorm.executor.AbstractJdbcSqlExecutor.java
@Override public <T> T single(SQL sql, ObjectWrapper<T> wrapper) throws SQLException { if (sql instanceof EmptySQL) return null; SQLInfo info = compileSql(sql);/*from ww w .j ava2s .c o m*/ printSql(info); Connection connection = getConnection(); PreparedStatement statement = connection.prepareStatement(info.getSql()); //? this.preparedParam(statement, info); ResultSet resultSet = statement.executeQuery(); ResultSetMetaData metaData = resultSet.getMetaData(); int count = metaData.getColumnCount(); List<String> headers = new ArrayList<>(); for (int i = 1; i <= count; i++) { headers.add(metaData.getColumnLabel(i)); } wrapper.setUp(headers); int index = 0; T data = null; if (resultSet.next()) { data = wrapper.newInstance(); for (int i = 0; i < headers.size(); i++) { Object value = resultSet.getObject(i + 1); wrapper.wrapper(data, index, headers.get(i), value); } // for (String header : headers) { // Object value = resultSet.getObject(header); // wrapper.wrapper(data, index, header, value); // } index++; wrapper.done(data); } closeResultSet(resultSet); closeStatement(statement); releaseConnection(connection); return data; }
From source file:com.thinkbiganalytics.schema.QueryRunner.java
/** * Initializes the query result with the specified metadata. * * @param queryResult the query result to initialize * @param rsMetaData the result set metadata for the query * @throws SQLException if the metadata is not available *//*from w w w . j av a2 s . c om*/ private void initQueryResult(@Nonnull final DefaultQueryResult queryResult, @Nonnull final ResultSetMetaData rsMetaData) throws SQLException { final List<QueryResultColumn> columns = new ArrayList<>(); final Map<String, Integer> displayNameMap = new HashMap<>(); for (int i = 1; i <= rsMetaData.getColumnCount(); i++) { final DefaultQueryResultColumn column = new DefaultQueryResultColumn(); column.setField(rsMetaData.getColumnName(i)); String displayName = rsMetaData.getColumnLabel(i); column.setHiveColumnLabel(displayName); //remove the table name if it exists displayName = StringUtils.contains(displayName, ".") ? StringUtils.substringAfterLast(displayName, ".") : displayName; Integer count = 0; if (displayNameMap.containsKey(displayName)) { count = displayNameMap.get(displayName); count++; } displayNameMap.put(displayName, count); column.setDisplayName(displayName + "" + (count > 0 ? count : "")); column.setTableName(StringUtils.substringAfterLast(rsMetaData.getColumnName(i), ".")); column.setDataType(ParserHelper.sqlTypeToHiveType(rsMetaData.getColumnType(i))); column.setNativeDataType(rsMetaData.getColumnTypeName(i)); columns.add(column); } queryResult.setColumns(columns); }
From source file:org.apache.sqoop.connector.hbase.HbaseToInitializer.java
@Override public Schema getSchema(InitializerContext context, LinkConfiguration linkConfig, ToJobConfiguration toJobConfig) { executor = new HbaseExecutor(linkConfig.linkConfig); String schemaName = toJobConfig.toJobConfig.tableName; if (schemaName == null) { throw new SqoopException(GenericJdbcConnectorError.GENERIC_JDBC_CONNECTOR_0019, "Table name extraction not supported yet."); }//from w ww .j a v a2 s. c o m if (toJobConfig.toJobConfig.schemaName != null) { schemaName = toJobConfig.toJobConfig.schemaName + "." + schemaName; } Schema schema = new Schema(schemaName); ResultSet rs = null; ResultSetMetaData rsmt = null; try { rs = executor.executeQuery("SELECT * FROM " + schemaName + " WHERE 1 = 0"); rsmt = rs.getMetaData(); for (int i = 1; i <= rsmt.getColumnCount(); i++) { String columnName = rsmt.getColumnName(i); if (StringUtils.isEmpty(columnName)) { columnName = rsmt.getColumnLabel(i); if (StringUtils.isEmpty(columnName)) { columnName = "Column " + i; } } Column column = SqlTypesUtils.sqlTypeToSchemaType(rsmt.getColumnType(i), columnName, rsmt.getPrecision(i), rsmt.getScale(i)); schema.addColumn(column); } return schema; } catch (SQLException e) { throw new SqoopException(GenericJdbcConnectorError.GENERIC_JDBC_CONNECTOR_0016, e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { LOG.info("Ignoring exception while closing ResultSet", e); } } } }
From source file:TerminalMonitor.java
static public void processResults(ResultSet results) throws SQLException { try {/* ww w .j a v a2 s .c o m*/ ResultSetMetaData meta = results.getMetaData(); StringBuffer bar = new StringBuffer(); StringBuffer buffer = new StringBuffer(); int cols = meta.getColumnCount(); int row_count = 0; int i, width = 0; // Prepare headers for each of the columns // The display should look like: // -------------------------------------- // | Column One | Column Two | // -------------------------------------- // | Row 1 Value | Row 1 Value | // -------------------------------------- // create the bar that is as long as the total of all columns for (i = 1; i <= cols; i++) { width += meta.getColumnDisplaySize(i); } width += 1 + cols; for (i = 0; i < width; i++) { bar.append('-'); } bar.append('\n'); buffer.append(bar.toString() + "|"); // After the first bar goes the column labels for (i = 1; i <= cols; i++) { StringBuffer filler = new StringBuffer(); String label = meta.getColumnLabel(i); int size = meta.getColumnDisplaySize(i); int x; // If the label is longer than the column is wide, // then we truncate the column label if (label.length() > size) { label = label.substring(0, size); } // If the label is shorter than the column, pad it with spaces if (label.length() < size) { int j; x = (size - label.length()) / 2; for (j = 0; j < x; j++) { filler.append(' '); } label = filler + label + filler; if (label.length() > size) { label = label.substring(0, size); } else { while (label.length() < size) { label += " "; } } } // Add the column header to the buffer buffer.append(label + "|"); } // Add the lower bar buffer.append("\n" + bar.toString()); // Format each row in the result set and add it on while (results.next()) { row_count++; buffer.append('|'); // Format each column of the row for (i = 1; i <= cols; i++) { StringBuffer filler = new StringBuffer(); Object value = results.getObject(i); int size = meta.getColumnDisplaySize(i); String str; if (results.wasNull()) { str = "NULL"; } else { str = value.toString(); } if (str.length() > size) { str = str.substring(0, size); } if (str.length() < size) { int j, x; x = (size - str.length()) / 2; for (j = 0; j < x; j++) { filler.append(' '); } str = filler + str + filler; if (str.length() > size) { str = str.substring(0, size); } else { while (str.length() < size) { str += " "; } } } buffer.append(str + "|"); } buffer.append("\n"); } // Stick a row count up at the top if (row_count == 0) { buffer = new StringBuffer("No rows selected.\n"); } else if (row_count == 1) { buffer = new StringBuffer("1 row selected.\n" + buffer.toString() + bar.toString()); } else { buffer = new StringBuffer(row_count + " rows selected.\n" + buffer.toString() + bar.toString()); } System.out.print(buffer.toString()); System.out.flush(); } catch (SQLException e) { throw e; } finally { try { results.close(); } catch (SQLException e) { } } }
From source file:org.apache.calcite.avatica.jdbc.JdbcMeta.java
/** * Convert from JDBC metadata to Avatica columns. *//* www . j a va 2 s . c om*/ protected static List<ColumnMetaData> columns(ResultSetMetaData metaData) throws SQLException { if (metaData == null) { return Collections.emptyList(); } final List<ColumnMetaData> columns = new ArrayList<>(); for (int i = 1; i <= metaData.getColumnCount(); i++) { final SqlType sqlType = SqlType.valueOf(metaData.getColumnType(i)); final ColumnMetaData.Rep rep = ColumnMetaData.Rep.of(sqlType.internal); ColumnMetaData.AvaticaType t = ColumnMetaData.scalar(metaData.getColumnType(i), metaData.getColumnTypeName(i), rep); ColumnMetaData md = new ColumnMetaData(i - 1, metaData.isAutoIncrement(i), metaData.isCaseSensitive(i), metaData.isSearchable(i), metaData.isCurrency(i), metaData.isNullable(i), metaData.isSigned(i), metaData.getColumnDisplaySize(i), metaData.getColumnLabel(i), metaData.getColumnName(i), metaData.getSchemaName(i), metaData.getPrecision(i), metaData.getScale(i), metaData.getTableName(i), metaData.getCatalogName(i), t, metaData.isReadOnly(i), metaData.isWritable(i), metaData.isDefinitelyWritable(i), metaData.getColumnClassName(i)); columns.add(md); } return columns; }