List of usage examples for java.sql ResultSetMetaData getColumnName
String getColumnName(int column) throws SQLException;
From source file:Database.Handler.java
@SuppressWarnings("unchecked") private List<T> mapRersultSetToObject(ResultSet rs, Class outputClass) { List<T> outputList = null; try {/*w w w.ja v a2 s . c o m*/ if (rs != null) { if (outputClass.isAnnotationPresent(Entity.class)) { ResultSetMetaData rsmd = rs.getMetaData(); Field[] fields = outputClass.getDeclaredFields(); while (rs.next()) { T bean = (T) outputClass.newInstance(); //System.out.println("rsmd = "+rsmd.getColumnCount()); for (int i = 1; i <= rsmd.getColumnCount(); i++) { String columnName = rsmd.getColumnName(i); Object columnValue = rs.getObject(i); for (Field field : fields) { if (field.isAnnotationPresent(Column.class)) { Column column = field.getAnnotation(Column.class); if (column.name().equalsIgnoreCase(columnName) && columnValue != null) { //System.out.println(field.getName() + "=====>" + columnValue); BeanUtils.setProperty(bean, field.getName(), columnValue); break; } } } } if (outputList == null) { outputList = new ArrayList<T>(); } outputList.add(bean); } } else { // throw some error System.out.println("output class is not annotationPresented"); } } else { return null; } } catch (SQLException ex) { Logger.getLogger(Handler.class.getName()).log(Level.SEVERE, null, ex); } catch (SecurityException ex) { Logger.getLogger(Handler.class.getName()).log(Level.SEVERE, null, ex); } catch (InstantiationException ex) { Logger.getLogger(Handler.class.getName()).log(Level.SEVERE, null, ex); } catch (IllegalAccessException ex) { Logger.getLogger(Handler.class.getName()).log(Level.SEVERE, null, ex); } catch (InvocationTargetException ex) { Logger.getLogger(Handler.class.getName()).log(Level.SEVERE, null, ex); } return outputList; }
From source file:jp.primecloud.auto.tool.management.db.SQLExecuter.java
public List<Map<String, Object>> showColumns(String sql) throws SQLException, Exception { Connection con = null;// www. jav a2 s. com Statement stmt = null; ResultSet rs = null; log.info("[" + sql + "] ???"); List<Map<String, Object>> results = new ArrayList<Map<String, Object>>(); try { con = dbConnector.getConnection(); stmt = con.createStatement(); rs = stmt.executeQuery(sql); ResultSetMetaData rsMetaData = rs.getMetaData(); int size = rsMetaData.getColumnCount(); while (rs.next()) { Map<String, Object> result = new HashMap<String, Object>(); for (int i = 1; i <= size; i++) { result.put(parseColumnName(rsMetaData.getColumnName(i)), rs.getObject(i)); } results.add(result); } log.info("[" + sql + "] ????"); } catch (SQLException e) { log.error(e.getMessage(), e); throw new SQLException(e); } catch (Exception e) { log.error(e.getMessage(), e); throw new Exception(e); } finally { try { dbConnector.closeConnection(con, stmt, rs); } catch (Exception e) { e.printStackTrace(); } } return results; }
From source file:com.mvdb.etl.dao.impl.JdbcGenericDAO.java
@Override public void fetchMetadata(String objectName, File snapshotDirectory) { final Metadata metadata = new Metadata(); metadata.setTableName(objectName);//from w w w . j a va 2 s . co m String sql = "SELECT * FROM " + objectName + " limit 1"; final Map<String, ColumnMetadata> metaDataMap = new HashMap<String, ColumnMetadata>(); metadata.setColumnMetadataMap(metaDataMap); metadata.setTableName(objectName); getJdbcTemplate().query(sql, new RowCallbackHandler() { @Override public void processRow(ResultSet row) throws SQLException { ResultSetMetaData rsm = row.getMetaData(); int columnCount = rsm.getColumnCount(); for (int column = 1; column < (columnCount + 1); column++) { ColumnMetadata columnMetadata = new ColumnMetadata(); columnMetadata.setColumnLabel(rsm.getColumnLabel(column)); columnMetadata.setColumnName(rsm.getColumnName(column)); columnMetadata.setColumnType(rsm.getColumnType(column)); columnMetadata.setColumnTypeName(rsm.getColumnTypeName(column)); metaDataMap.put(rsm.getColumnName(column), columnMetadata); } } }); writeMetadata(metadata, snapshotDirectory); }
From source file:org.apache.kylin.rest.adhoc.AdHocRunnerJdbcImpl.java
@Override public void executeQuery(String query, List<List<String>> results, List<SelectedColumnMeta> columnMetas) throws Exception { Statement statement = null;/* w w w.ja v a2 s.co m*/ Connection connection = this.getConnection(); ResultSet resultSet = null; try { statement = connection.createStatement(); resultSet = statement.executeQuery(query); extractResults(resultSet, results); } catch (SQLException sqlException) { throw sqlException; } //extract column metadata ResultSetMetaData metaData = null; int columnCount = 0; try { 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; } closeConnection(connection); }
From source file:com.mirth.connect.connectors.jdbc.DatabaseConnectorServlet.java
@Override public SortedSet<Table> getTables(String channelId, String channelName, String driver, String url, String username, String password, Set<String> tableNamePatterns, String selectLimit, Set<String> resourceIds) { CustomDriver customDriver = null;/*from www .j a va 2 s . c om*/ Connection connection = null; try { url = replacer.replaceValues(url, channelId, channelName); username = replacer.replaceValues(username, channelId, channelName); password = replacer.replaceValues(password, channelId, channelName); String schema = null; try { MirthContextFactory contextFactory = contextFactoryController.getContextFactory(resourceIds); try { ClassLoader isolatedClassLoader = contextFactory.getIsolatedClassLoader(); if (isolatedClassLoader != null) { customDriver = new CustomDriver(isolatedClassLoader, driver); logger.debug("Custom driver created: " + customDriver.toString() + ", Version " + customDriver.getMajorVersion() + "." + customDriver.getMinorVersion()); } else { logger.debug("Custom classloader is not being used, defaulting to DriverManager."); } } catch (Exception e) { logger.debug("Error creating custom driver, defaulting to DriverManager.", e); } } catch (Exception e) { logger.debug("Error retrieving context factory, defaulting to DriverManager.", e); } if (customDriver == null) { Class.forName(driver); } int oldLoginTimeout = DriverManager.getLoginTimeout(); DriverManager.setLoginTimeout(30); if (customDriver != null) { connection = customDriver.connect(url, username, password); } else { connection = DriverManager.getConnection(url, username, password); } DriverManager.setLoginTimeout(oldLoginTimeout); DatabaseMetaData dbMetaData = connection.getMetaData(); // the sorted set to hold the table information SortedSet<Table> tableInfoList = new TreeSet<Table>(); // Use a schema if the user name matches one of the schemas. // Fix for Oracle: MIRTH-1045 ResultSet schemasResult = null; try { schemasResult = dbMetaData.getSchemas(); while (schemasResult.next()) { String schemaResult = schemasResult.getString(1); if (username.equalsIgnoreCase(schemaResult)) { schema = schemaResult; } } } finally { if (schemasResult != null) { schemasResult.close(); } } // based on the table name pattern, attempt to retrieve the table information tableNamePatterns = translateTableNamePatterns(tableNamePatterns); List<String> tableNameList = new ArrayList<String>(); // go through each possible table name patterns and query for the tables for (String tableNamePattern : tableNamePatterns) { ResultSet rs = null; try { rs = dbMetaData.getTables(null, schema, tableNamePattern, TABLE_TYPES); // based on the result set, loop through to store the table name so it can be used to // retrieve the table's column information while (rs.next()) { tableNameList.add(rs.getString("TABLE_NAME")); } } finally { if (rs != null) { rs.close(); } } } // for each table, grab their column information for (String tableName : tableNameList) { ResultSet rs = null; ResultSet backupRs = null; boolean fallback = false; try { // apparently it's much more efficient to use ResultSetMetaData to retrieve // column information. So each driver is defined with their own unique SELECT // statement to query the table columns and use ResultSetMetaData to retrieve // the column information. If driver is not defined with the select statement // then we'll define to the generic method of getting column information, but // this could be extremely slow List<Column> columnList = new ArrayList<Column>(); if (StringUtils.isEmpty(selectLimit)) { logger.debug("No select limit is defined, using generic method"); rs = dbMetaData.getColumns(null, null, tableName, null); // retrieve all relevant column information for (int i = 0; rs.next(); i++) { Column column = new Column(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"), rs.getInt("COLUMN_SIZE")); columnList.add(column); } } else { logger.debug( "Select limit is defined, using specific select query : '" + selectLimit + "'"); // replace the '?' with the appropriate schema.table name, and use ResultSetMetaData to // retrieve column information final String schemaTableName = StringUtils.isNotEmpty(schema) ? "\"" + schema + "\".\"" + tableName + "\"" : "\"" + tableName + "\""; final String queryString = selectLimit.trim().replaceAll("\\?", Matcher.quoteReplacement(schemaTableName)); Statement statement = connection.createStatement(); try { rs = statement.executeQuery(queryString); ResultSetMetaData rsmd = rs.getMetaData(); // retrieve all relevant column information for (int i = 1; i < rsmd.getColumnCount() + 1; i++) { Column column = new Column(rsmd.getColumnName(i), rsmd.getColumnTypeName(i), rsmd.getPrecision(i)); columnList.add(column); } } catch (SQLException sqle) { logger.info("Failed to execute '" + queryString + "', fall back to generic approach to retrieve column information"); fallback = true; } finally { if (statement != null) { statement.close(); } } // failed to use selectLimit method, so we need to fall back to generic // if this generic approach fails, then there's nothing we can do if (fallback) { // Re-initialize in case some columns were added before failing columnList = new ArrayList<Column>(); logger.debug("Using fallback method for retrieving columns"); backupRs = dbMetaData.getColumns(null, null, tableName.replace("/", "//"), null); // retrieve all relevant column information while (backupRs.next()) { Column column = new Column(backupRs.getString("COLUMN_NAME"), backupRs.getString("TYPE_NAME"), backupRs.getInt("COLUMN_SIZE")); columnList.add(column); } } } // create table object and add to the list of table definitions Table table = new Table(tableName, columnList); tableInfoList.add(table); } finally { if (rs != null) { rs.close(); } if (backupRs != null) { backupRs.close(); } } } return tableInfoList; } catch (Exception e) { throw new MirthApiException(new Exception("Could not retrieve database tables and columns.", e)); } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { } } } }
From source file:com.netspective.axiom.value.source.QueryResultsValueSource.java
public Value getQueryResults(ValueContext vc, int style) { ValueSources.getInstance().assertValueContextInstance(DatabaseConnValueContext.class, vc, this); DatabaseConnValueContext dcvc = (DatabaseConnValueContext) vc; SqlManager sqlManager = null;// w ww. j a v a 2 s . c om try { sqlManager = getSqlManager(dcvc); if (sqlManager == null) throw new RuntimeException("Unable to locate SQL Manager for " + this); } catch (Exception e) { log.error("Error retrieving SQL Manager", e); throw new NestableRuntimeException(e); } Query query = sqlManager.getQuery(queryId); if (query == null) { log.error("Unable to locate Query '" + queryId + "' in SQL Manager '" + sqlManager + "' in " + this + ". Available: " + sqlManager.getQueries().getNames()); if (style == RESULTSTYLE_PRESENTATION) { PresentationValue pValue = new PresentationValue(); PresentationValue.Items items = pValue.createItems(); items.addItem("Unable to find query " + queryId); return pValue; } else return new GenericValue("Unable to find query " + queryId); } String dataSourceIdText = dataSourceId != null ? dataSourceId.getTextValue(vc) : null; QueryResultSet qrs = null; try { if (params == null) qrs = query.execute(dcvc, dataSourceIdText, null); else { Object[] parameters = new Object[params.length]; for (int p = 0; p < params.length; p++) parameters[p] = params[p].getValue(vc).getValueForSqlBindParam(); qrs = query.execute(dcvc, dataSourceIdText, parameters); } } catch (Exception e) { log.error("Error executing query", e); throw new NestableRuntimeException(e); } Value value = null; try { ResultSet rs = qrs.getResultSet(); switch (style) { case RESULTSTYLE_SINGLECOLUMN_OBJECT: if (rs.next()) value = new GenericValue(rs.getObject(1)); else value = null; break; case RESULTSTYLE_FIRST_ROW_MAP_OBJECT: if (rs.next()) { Map rowMap = new HashMap(); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; i <= rsmd.getColumnCount(); i++) rowMap.put(rsmd.getColumnName(i), rs.getObject(i)); value = new GenericValue(rowMap); } else value = null; break; case RESULTSTYLE_ALL_ROWS_MAP_LIST: List rows = new ArrayList(); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { Map rowMap = new HashMap(); for (int i = 1; i <= rsmd.getColumnCount(); i++) rowMap.put(rsmd.getColumnName(i), rs.getObject(i)); rows.add(rowMap); } value = new GenericValue(rows); break; case RESULTSTYLE_FIRST_ROW_LIST: rsmd = rs.getMetaData(); if (rs.next()) { List row = new ArrayList(); for (int i = 1; i <= rsmd.getColumnCount(); i++) row.add(rs.getObject(i)); value = new GenericValue(row); } else value = null; break; case RESULTSTYLE_ALL_ROWS_LIST: rsmd = rs.getMetaData(); rows = new ArrayList(); while (rs.next()) { List row = new ArrayList(); for (int i = 1; i <= rsmd.getColumnCount(); i++) row.add(rs.getObject(i)); rows.add(row); } value = new GenericValue(rows); break; case RESULTSTYLE_RESULTSET: value = new GenericValue(qrs); break; case RESULTSTYLE_PRESENTATION: PresentationValue pValue = new PresentationValue(); PresentationValue.Items items = pValue.createItems(); rsmd = rs.getMetaData(); rows = new ArrayList(); switch (rsmd.getColumnCount()) { case 1: while (rs.next()) items.addItem(rs.getString(1)); break; default: while (rs.next()) items.addItem(rs.getString(1), rs.getString(2)); break; } value = pValue; break; default: throw new RuntimeException("Invalid style " + resultStyle + " in " + this); } } catch (Exception e) { log.error("Error retrieving results", e); throw new NestableRuntimeException(e); } finally { if (resultStyle != RESULTSTYLE_RESULTSET) { try { if (qrs != null) qrs.close(true); } catch (SQLException e) { log.error("Error closing result set", e); throw new NestableRuntimeException(e); } } } return value; }
From source file:com.sangupta.fileanalysis.db.DBResultViewer.java
/** * View resutls of a {@link ResultSet}.// w w w . j a va2s.c om * * @param resultSet * @throws SQLException */ public void viewResult(ResultSet resultSet) throws SQLException { if (resultSet == null) { // nothing to do return; } // collect the meta ResultSetMetaData meta = resultSet.getMetaData(); final int numColumns = meta.getColumnCount(); final int[] displaySizes = new int[numColumns + 1]; final int[] colType = new int[numColumns + 1]; for (int index = 1; index <= numColumns; index++) { colType[index] = meta.getColumnType(index); displaySizes[index] = getColumnSize(meta.getTableName(index), meta.getColumnName(index), colType[index]); } // display the header row for (int index = 1; index <= numColumns; index++) { center(meta.getColumnLabel(index), displaySizes[index]); } System.out.println("|"); for (int index = 1; index <= numColumns; index++) { System.out.print("+" + StringUtils.repeat('-', displaySizes[index] + 2)); } System.out.println("+"); // start iterating over the result set int rowsDisplayed = 0; int numRecords = 0; while (resultSet.next()) { // read and display the value rowsDisplayed++; numRecords++; for (int index = 1; index <= numColumns; index++) { switch (colType[index]) { case Types.DECIMAL: case Types.DOUBLE: case Types.REAL: format(resultSet.getDouble(index), displaySizes[index]); continue; case Types.INTEGER: case Types.SMALLINT: format(resultSet.getInt(index), displaySizes[index]); continue; case Types.VARCHAR: format(resultSet.getString(index), displaySizes[index], false); continue; case Types.TIMESTAMP: format(resultSet.getTimestamp(index), displaySizes[index]); continue; case Types.BIGINT: format(resultSet.getBigDecimal(index), displaySizes[index]); continue; } } // terminator for row and new line System.out.println("|"); // check for rows displayed if (rowsDisplayed == 20) { // ask the user if more data needs to be displayed String cont = ConsoleUtils.readLine("Type \"it\" for more: ", true); if (!"it".equalsIgnoreCase(cont)) { break; } // continue; rowsDisplayed = 0; continue; } } System.out.println("\nTotal number of records found: " + numRecords); }
From source file:annis.sqlgen.FindSqlGenerator.java
public Match mapRow(ResultSet rs, int rowNum) throws SQLException { Match match = new Match(); // get size of solution ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); // the order of columns is not determined and I have to combined two // values, so save them here and combine later String node_name = null;/*from w w w . j a v a 2s .c o m*/ List<String> corpus_path = null; //get path if (outputCorpusPath) { for (int column = 1; column <= columnCount; ++column) { if (corpusPathExtractor != null && metaData.getColumnName(column).startsWith("path_name")) { corpus_path = corpusPathExtractor.extractCorpusPath(rs, metaData.getColumnName(column)); } } } // one match per column for (int column = 1; column <= columnCount; ++column) { if (metaData.getColumnName(column).startsWith("node_name")) { node_name = rs.getString(column); } else // no more matches in this row if an id was NULL if (rs.wasNull()) { break; } if (outputCorpusPath && node_name != null) { match.setSaltId(buildSaltId(corpus_path, node_name)); node_name = null; } } return match; }
From source file:com.groupon.odo.proxylib.SQLService.java
/** * Gets all of the column names for a result meta data * * @param rsmd//from www . j a v a 2s . c o m * @return */ private String[] getColumnNames(ResultSetMetaData rsmd) throws Exception { ArrayList<String> names = new ArrayList<String>(); // Get result set meta data int numColumns = rsmd.getColumnCount(); // Get the column names; column indices start from 1 for (int i = 1; i < numColumns + 1; i++) { String columnName = rsmd.getColumnName(i); names.add(columnName); } return names.toArray(new String[0]); }
From source file:org.apache.sqoop.common.test.db.DatabaseProvider.java
/** * Dump content of given table to log.// www .jav a2 s .co m * * @param tableName Name of the table */ public void dumpTable(TableName tableName) { String query = "SELECT * FROM " + getTableFragment(tableName); List<String> list = new LinkedList<String>(); ResultSet rs = null; try { rs = executeQuery(query); // Header with column names ResultSetMetaData md = rs.getMetaData(); for (int i = 0; i < md.getColumnCount(); i++) { list.add(md.getColumnName(i + 1)); } LOG.info("Dumping table " + tableName); LOG.info("|" + StringUtils.join(list, "|") + "|"); // Table rows while (rs.next()) { list.clear(); for (int i = 0; i < md.getColumnCount(); i++) { list.add(rs.getObject(i + 1).toString()); } LOG.info("|" + StringUtils.join(list, "|") + "|"); } } catch (SQLException e) { LOG.info("Ignoring exception: ", e); } finally { closeResultSetWithStatement(rs); } }