List of usage examples for java.sql ResultSetMetaData getColumnCount
int getColumnCount() throws SQLException;
ResultSet object. From source file:com.kumarvv.setl.core.Loader.java
/** * build select columns from database/* w w w . ja v a2 s. com*/ * * @param load * @return */ protected String buildSelectColumns(Load load) { if (load == null || StringUtils.isEmpty(load.getTable())) { return ""; } StringBuilder sql = new StringBuilder(); sql.append("SELECT * FROM ").append(load.getTable()).append(" WHERE 1>2"); final Set<String> cols = new LinkedHashSet<>(); try (JdbcRowSet cjrs = rowSetUtil.getRowSet(def.getToDS())) { cjrs.setCommand(sql.toString()); cjrs.execute(); ResultSetMetaData meta = cjrs.getMetaData(); for (int i = 1; i <= meta.getColumnCount(); i++) { cols.add(meta.getColumnName(i).toLowerCase()); } } catch (SQLException sqle) { Logger.error("buildSelectColumns failed: " + sqle.getMessage()); Logger.trace(sqle); } String colStr = StringUtils.join(cols, ", "); Logger.debug("load={}, selectColumns={}", load.getTable(), sql.toString()); return colStr; }
From source file:com.tonbeller.jpivot.mondrian.ScriptableMondrianDrillThroughTableModel.java
/** * execute sql query/*from w w w . j a va 2s.c o m*/ * @throws Exception */ private void executeQuery() { Connection con = null; try { InputStream catExtIs = ScriptableMondrianDrillThroughTableModel.class.getClassLoader() .getResourceAsStream("/" + catalogExtension); Digester catExtDigester = new Digester(); catExtDigester.push(this); catExtDigester.addSetProperties("extension"); catExtDigester.addObjectCreate("extension/script", "com.tonbeller.jpivot.mondrian.script.ScriptColumn"); catExtDigester.addSetProperties("extension/script"); catExtDigester.addSetNext("extension/script", "addScript"); catExtDigester.parse(catExtIs); URL scriptsBaseURL = Thread.currentThread().getContextClassLoader().getResource(scriptRootUrl); scriptEngine = new GroovyScriptEngine(new URL[] { scriptsBaseURL }); con = getConnection(); Statement s = con.createStatement(); s.setMaxRows(maxResults); ResultSet rs = s.executeQuery(sql); ResultSetMetaData md = rs.getMetaData(); int numCols = md.getColumnCount(); List columnTitlesList = new ArrayList(); // set column headings for (int i = 0; i < numCols; i++) { // columns are 1 based columnTitlesList.add(i, md.getColumnName(i + 1)); } // loop on script columns for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) { final ScriptColumn sc = (ScriptColumn) sIt.next(); columnTitlesList.add(sc.getPosition() - 1, sc.getTitle()); } columnTitles = (String[]) columnTitlesList.toArray(new String[0]); // loop through rows List tempRows = new ArrayList(); Map scriptInput = new HashMap(); Binding binding = new Binding(); while (rs.next()) { List rowList = new ArrayList(); scriptInput.clear(); // loop on columns, 1 based for (int i = 0; i < numCols; i++) { rowList.add(i, rs.getObject(i + 1)); scriptInput.put(columnTitles[i], rs.getObject(i + 1)); } binding.setVariable("input", scriptInput); // loop on script columns for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) { final ScriptColumn sc = (ScriptColumn) sIt.next(); scriptEngine.run(sc.getFile(), binding); final Object output = binding.getVariable("output"); if (output instanceof Map) { Map outMap = (Map) output; rowList.add(sc.getPosition() - 1, new DefaultCell((String) outMap.get("URL"), (String) outMap.get("Value"))); } else if (output instanceof String) { rowList.add(sc.getPosition() - 1, (String) output); } else { throw new Exception("Unknown groovy script return type (not a Map nor String)."); } } tempRows.add(new DefaultTableRow(rowList.toArray())); } rs.close(); rows = (TableRow[]) tempRows.toArray(new TableRow[0]); } catch (Exception e) { e.printStackTrace(); logger.error("?", e); // problem occured, set table model to zero size rows = new TableRow[1]; columnTitles = new String[1]; columnTitles[0] = "An error occured"; Object[] row = new Object[1]; row[0] = e.toString(); rows[0] = new DefaultTableRow(row); ready = false; return; } finally { try { con.close(); } catch (Exception e1) { // ignore } } ready = true; }
From source file:com.groupon.odo.proxylib.SQLService.java
/** * Gets all of the column names for a result meta data * * @param rsmd//from w w w.j ava2s.c om * @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:com.mirth.connect.connectors.jdbc.JdbcConnectorService.java
public Object invoke(String method, Object object, String sessionsId) throws Exception { if (method.equals("getInformationSchema")) { // method 'getInformationSchema' will return Set<Table> Connection connection = null; try {/*from w w w . j a v a 2s.c om*/ Properties properties = (Properties) object; String driver = properties.getProperty(DatabaseReaderProperties.DATABASE_DRIVER); String address = properties.getProperty(DatabaseReaderProperties.DATABASE_URL); String user = properties.getProperty(DatabaseReaderProperties.DATABASE_USERNAME); String password = properties.getProperty(DatabaseReaderProperties.DATABASE_PASSWORD); // Although these properties are not persisted, they used by the JdbcConnectorService String tableNamePatternExp = properties .getProperty(DatabaseReaderProperties.DATABASE_TABLE_NAME_PATTERN_EXPRESSION); String selectLimit = properties.getProperty(DatabaseReaderProperties.DATABASE_SELECT_LIMIT); String schema = null; Class.forName(driver); int oldLoginTimeout = DriverManager.getLoginTimeout(); DriverManager.setLoginTimeout(30); connection = DriverManager.getConnection(address, user, 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 (user.equalsIgnoreCase(schemaResult)) { schema = schemaResult; } } } finally { if (schemasResult != null) { schemasResult.close(); } } // based on the table name pattern, attempt to retrieve the table information List<String> tablePatternList = translateTableNamePatternExpression(tableNamePatternExp); List<String> tableNameList = new ArrayList<String>(); // go through each possible table name patterns and query for the tables for (String tableNamePattern : tablePatternList) { 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("\\?", 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, null); // retrieve all relevant column information for (int i = 0; backupRs.next(); i++) { 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 Exception("Could not retrieve database tables and columns.", e); } finally { if (connection != null) { connection.close(); } } } return null; }
From source file:com.tonbeller.jpivot.mondrian.script.ScriptableMondrianDrillThroughTableModel.java
/** * execute sql query/* www . j a v a 2s .c om*/ * @throws Exception */ private void executeQuery() { Connection con = null; try { InputStream catExtIs = ScriptableMondrianDrillThroughTableModel.class.getClassLoader() .getResourceAsStream("/" + catalogExtension); if (catExtIs != null) { Digester catExtDigester = new Digester(); catExtDigester.push(this); catExtDigester.addSetProperties("extension"); catExtDigester.addObjectCreate("extension/script", "com.tonbeller.jpivot.mondrian.script.ScriptColumn"); catExtDigester.addSetProperties("extension/script"); catExtDigester.addSetNext("extension/script", "addScript"); catExtDigester.parse(catExtIs); URL scriptsBaseURL = Thread.currentThread().getContextClassLoader().getResource(scriptRootUrl); scriptEngine = new GroovyScriptEngine(new URL[] { scriptsBaseURL }); } con = getConnection(); Statement s = con.createStatement(); s.setMaxRows(maxResults); ResultSet rs = s.executeQuery(sql); ResultSetMetaData md = rs.getMetaData(); int numCols = md.getColumnCount(); List columnTitlesList = new ArrayList(); // set column headings for (int i = 0; i < numCols; i++) { // columns are 1 based columnTitlesList.add(i, md.getColumnName(i + 1)); } // loop on script columns for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) { final ScriptColumn sc = (ScriptColumn) sIt.next(); columnTitlesList.add(sc.getPosition() - 1, sc.getTitle()); } columnTitles = (String[]) columnTitlesList.toArray(new String[0]); // loop through rows List tempRows = new ArrayList(); Map scriptInput = new HashMap(); Binding binding = new Binding(); while (rs.next()) { List rowList = new ArrayList(); scriptInput.clear(); // loop on columns, 1 based for (int i = 0; i < numCols; i++) { rowList.add(i, rs.getObject(i + 1)); scriptInput.put(columnTitles[i], rs.getObject(i + 1)); } binding.setVariable("input", scriptInput); // loop on script columns for (ListIterator sIt = scripts.listIterator(); sIt.hasNext();) { final ScriptColumn sc = (ScriptColumn) sIt.next(); scriptEngine.run(sc.getFile(), binding); final Object output = binding.getVariable("output"); if (output instanceof Map) { Map outMap = (Map) output; rowList.add(sc.getPosition() - 1, new DefaultCell((String) outMap.get("URL"), (String) outMap.get("Value"))); } else if (output instanceof String) { rowList.add(sc.getPosition() - 1, (String) output); } else { throw new Exception("Unknown groovy script return type (not a Map nor String)."); } } tempRows.add(new DefaultTableRow(rowList.toArray())); } rs.close(); rows = (TableRow[]) tempRows.toArray(new TableRow[0]); } catch (Exception e) { e.printStackTrace(); logger.error("?", e); // problem occured, set table model to zero size rows = new TableRow[1]; columnTitles = new String[1]; columnTitles[0] = "An error occured"; Object[] row = new Object[1]; row[0] = e.toString(); rows[0] = new DefaultTableRow(row); ready = false; return; } finally { try { con.close(); } catch (Exception e1) { // ignore } } ready = true; }
From source file:at.ac.tuwien.inso.subcat.reporter.Reporter.java
private void exportRows(ExporterConfig config, Project project, int commitDictId, int bugDictId, Settings settings, final ReportWriter formatter, String outputPath, Map<String, Object> vars) throws SQLException, Exception { formatter.init(project, settings, outputPath); model.rawForeach(config.getQuery(), vars, new ResultCallback() { @Override// ww w . ja va2 s . co m public void processResult(ResultSet res) throws SemanticException, SQLException, Exception { ResultSetMetaData meta = res.getMetaData(); String[] titles = new String[meta.getColumnCount()]; for (int i = 0; i < titles.length; i++) { titles[i] = meta.getColumnLabel(i + 1); } formatter.writeHeader(titles); while (res.next()) { String[] data = new String[titles.length]; for (int i = 0; i < data.length; i++) { data[i] = res.getString(i + 1); } formatter.writeSet(data); } formatter.writeFooter(titles); } }); }
From source file:com.mirth.connect.connectors.jdbc.DatabaseConnectorService.java
public Object invoke(String channelId, String method, Object object, String sessionsId) throws Exception { if (method.equals("getInformationSchema")) { // method 'getInformationSchema' will return Set<Table> Connection connection = null; try {/* www .j a v a 2 s . c om*/ DatabaseConnectionInfo databaseConnectionInfo = (DatabaseConnectionInfo) object; String driver = databaseConnectionInfo.getDriver(); String address = replacer.replaceValues(databaseConnectionInfo.getUrl(), channelId); String user = replacer.replaceValues(databaseConnectionInfo.getUsername(), channelId); String password = replacer.replaceValues(databaseConnectionInfo.getPassword(), channelId); // Although these properties are not persisted, they used by the JdbcConnectorService String tableNamePatternExp = databaseConnectionInfo.getTableNamePatternExpression(); String selectLimit = databaseConnectionInfo.getSelectLimit(); String schema = null; Class.forName(driver); int oldLoginTimeout = DriverManager.getLoginTimeout(); DriverManager.setLoginTimeout(30); connection = DriverManager.getConnection(address, user, 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 (user.equalsIgnoreCase(schemaResult)) { schema = schemaResult; } } } finally { if (schemasResult != null) { schemasResult.close(); } } // based on the table name pattern, attempt to retrieve the table information List<String> tablePatternList = translateTableNamePatternExpression(tableNamePatternExp); List<String> tableNameList = new ArrayList<String>(); // go through each possible table name patterns and query for the tables for (String tableNamePattern : tablePatternList) { 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("\\?", 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, null); // retrieve all relevant column information for (int i = 0; backupRs.next(); i++) { 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 Exception("Could not retrieve database tables and columns.", e); } finally { if (connection != null) { connection.close(); } } } return null; }
From source file:com.datatorrent.contrib.enrichment.JDBCLoader.java
protected ArrayList<Object> getDataFrmResult(Object result) throws RuntimeException { try {/*from w w w . j ava 2 s . c om*/ ResultSet resultSet = (ResultSet) result; if (resultSet.next()) { ResultSetMetaData rsdata = resultSet.getMetaData(); // If the includefields is empty, populate it from ResultSetMetaData if (CollectionUtils.isEmpty(includeFields)) { if (includeFields == null) { includeFields = new ArrayList<String>(); } for (int i = 1; i <= rsdata.getColumnCount(); i++) { includeFields.add(rsdata.getColumnName(i)); } } ArrayList<Object> res = new ArrayList<Object>(); for (String f : includeFields) { res.add(resultSet.getObject(f)); } return res; } else { return null; } } catch (SQLException e) { throw new RuntimeException(e); } }
From source file:hmp.HMPClassiferSummary.java
private void run(String[] args) throws ClassNotFoundException, FileNotFoundException, SQLException, IOException, ParseException { /*//from ww w. j av a2s .c om * Get command line options */ cli = getOptions(args); /* * Determine if rdp results is file or directory */ File rdpFile = new File(cli.getOptionValue("rdpDir")); boolean isDirectory = false; if (rdpFile.isDirectory()) { isDirectory = true; } /* * Setup database connections * */ MySQLConnector connector = new MySQLConnector("localhost", "biosql", "biosql", "biosql"); conn = connector.getConnection(); connector = new MySQLConnector("localhost", cli.getOptionValue("db"), cli.getOptionValue("dbUser"), cli.getOptionValue("dbPassword")); microbiome = connector.getConnection(); /* * create output directory */ createOutputDir("summary"); /* * Determine which columns are available for rdp levels */ rdpLevelCols = new ArrayList<String>(); ResultSet rsCol = microbiome.createStatement().executeQuery("select * from rdp_result_data limit 10"); ResultSetMetaData rsMeta = rsCol.getMetaData(); int colNumber = rsMeta.getColumnCount(); for (int i = 1; i < colNumber + 1; i++) { rdpLevelCols.add(rsMeta.getColumnName(i)); } /* * process file/files */ PrintWriter writer = new PrintWriter(new FileWriter("summary_all.txt")); writer.println("sampleid|level|taxa|count|len|sd|gc|sd|skew"); if (isDirectory) { File[] files = getFiles(rdpFile); int count = 0; for (int i = 0; i < files.length; i++) { if (count >= 0) { System.out.println("===PROCESSING FILE " + i + " ==="); processReads(files[i], writer); count++; } } } else { processReads(rdpFile, writer); } writer.close(); System.out.println("Done!"); }
From source file:com.icsshs.datatransfer.database.impl.QueryBeanProcessor.java
/** * The positions in the returned array represent column numbers. The * values stored at each position represent the index in the * <code>PropertyDescriptor[]</code> for the bean property that matches * the column name. If no bean property was found for a column, the * position is set to <code>PROPERTY_NOT_FOUND</code>. * * @param rsmd The <code>ResultSetMetaData</code> containing column * information./*from w ww. j a v a 2 s .c o m*/ * * @param props The bean property descriptors. * * @throws SQLException if a database access error occurs * * @return An int[] with column index to property index mappings. The 0th * element is meaningless because JDBC column indexing starts at 1. */ protected int[] mapColumnsToProperties(ResultSetMetaData rsmd, PropertyDescriptor[] props) throws SQLException { int cols = rsmd.getColumnCount(); int[] columnToProperty = new int[cols + 1]; Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND); for (int col = 1; col <= cols; col++) { String columnName = rsmd.getColumnLabel(col); if (null == columnName || 0 == columnName.length()) { columnName = rsmd.getColumnName(col); } for (int i = 0; i < props.length; i++) { if (columnName.equalsIgnoreCase(StringHelper.toUnderscore(props[i].getName()))) { columnToProperty[col] = i; break; } } } return columnToProperty; }