List of usage examples for java.sql ResultSetMetaData getColumnCount
int getColumnCount() throws SQLException;
ResultSet
object. From source file:com.waveerp.systemDBDirect.java
public ArrayList loadTableEntries() { // Added by Jammi Dee 05/03/2012 registrySystem rss = new registrySystem(); url = rss.readRegistry("NA", "NA", "NA", "DBURL"); dbName = rss.readRegistry("NA", "NA", "NA", "DBDATABASE"); driver = rss.readRegistry("NA", "NA", "NA", "DBDRIVER"); user = rss.readRegistry("NA", "NA", "NA", "DBUSER"); password = rss.readRegistry("NA", "NA", "NA", "DBPASSWORD"); // Added by Jammi Dee 05/03/2012 // Call the encryption management system desEncryption de = new desEncryption(); de.Encrypter("", ""); ArrayList trList = new ArrayList(); try {/* w w w . j av a2 s . c om*/ Class.forName(getDriver()); con = DriverManager.getConnection(url + dbName, user, password); ps = con.createStatement(); rs = ps.executeQuery(querystring); ///////////////////////////////////////////// // Get the number of columns here. I need // this to add dynaminism to my table loader ///////////////////////////////////////////// ResultSetMetaData rsmd = rs.getMetaData(); setColCount(rsmd.getColumnCount()); /////////////////////////////////////////// // Load the column types to an array // Never access it directly, java simply // returns NULL, whew I don't know why /////////////////////////////////////////// String[] colTypes = new String[colCount]; for (int j = 0; j <= getColCount() - 1; j++) { colTypes[j] = rsmd.getColumnTypeName(j + 1); } while (rs.next()) { //System.out.println(rs.getString(1)); String[] item = new String[colCount]; for (int j = 0; j < getColCount(); j++) { if (colTypes[j] == "VARCHAR") { item[j] = rs.getString(j + 1); } if (colTypes[j] == "VARCHAR2") { item[j] = rs.getString(j + 1); } if (colTypes[j] == "NUMBER") { item[j] = Double.toString(rs.getDouble(j + 1)); } if (colTypes[j] == "DATE") { item[j] = rs.getDate(j + 1).toString(); } if (colTypes[j] == "DATETIME") { item[j] = colTypes[j]; // lrs.getTimestamp(j+1).toString(); } if (colTypes[j] == "TIMESTAMP") { item[j] = colTypes[j]; } } trList.add(item); } ps.close(); con.close(); } catch (Exception e) { //System.out.println(e.getMessage()); e.printStackTrace(); } return trList; }
From source file:com.waveerp.jsonLibrary02.java
public String loadDataEntries() { // Added by Jammi Dee 05/03/2012 registrySystem rss = new registrySystem(); url = rss.readRegistry("NA", "NA", "NA", "DBURL"); dbName = rss.readRegistry("NA", "NA", "NA", "DBDATABASE"); driver = rss.readRegistry("NA", "NA", "NA", "DBDRIVER"); user = rss.readRegistry("NA", "NA", "NA", "DBUSER"); password = rss.readRegistry("NA", "NA", "NA", "DBPASSWORD"); // Added by Jammi Dee 05/03/2012 // Call the encryption management system desEncryption de = new desEncryption(); de.Encrypter("", ""); try {/*from www .ja va 2 s.c o m*/ Class.forName(getDriver()); con = DriverManager.getConnection(url + dbName, user, password); ps = con.createStatement(); rs = ps.executeQuery(querystring); ///////////////////////////////////////////// // Get the number of columns here. I need // this to add dynaminism to my table loader ///////////////////////////////////////////// ResultSetMetaData rsmd = rs.getMetaData(); setColCount(rsmd.getColumnCount()); /////////////////////////////////////////// // Load the column types to an array // Never access it directly, java simply // returns NULL, whew I don't know why /////////////////////////////////////////// String[] colTypes = new String[colCount]; for (int j = 0; j <= getColCount() - 1; j++) { colTypes[j] = rsmd.getColumnTypeName(j + 1); } /** * Initialize the working arrays here for the process * Added by Jammi Dee 06/06/2012 */ while (rs.next()) { String id = rs.getString(1); } rs.last(); int rowCount = rs.getRow(); nodeid = new String[rowCount]; nodedesc = new String[rowCount]; nodeparent = new String[rowCount]; int ipoint = 0; rs.beforeFirst(); while (rs.next()) { nodeid[ipoint] = rs.getString(1); nodedesc[ipoint] = rs.getString(2); nodeparent[ipoint] = rs.getString(3); // Increment the pointer ipoint = ipoint + 1; } ps.close(); con.close(); } catch (Exception e) { System.out.println(e.getMessage()); e.printStackTrace(); return "FAILED"; } return "SUCCESS"; }
From source file:com.alfaariss.oa.engine.attribute.gather.processor.jdbc.JDBCGatherer.java
/** * Gathers attributes from JDBC storage to the supplied attributes object. * @see com.alfaariss.oa.engine.core.attribute.gather.processor.IProcessor#process(java.lang.String, com.alfaariss.oa.api.attribute.IAttributes) *//* w w w . j a v a2 s . co m*/ @Override public void process(String sUserId, IAttributes oAttributes) throws AttributeException { PreparedStatement oPreparedStatement = null; ResultSet oResultSet = null; Connection oConnection = null; try { oConnection = _oDataSource.getConnection(); oPreparedStatement = oConnection.prepareStatement(_sSelectQuery); oPreparedStatement.setString(1, sUserId); oResultSet = oPreparedStatement.executeQuery(); if (oResultSet.next()) { ResultSetMetaData oResultSetMetaData = oResultSet.getMetaData(); int iCount = oResultSetMetaData.getColumnCount(); for (int i = 1; i <= iCount; i++) { String sName = oResultSetMetaData.getColumnName(i); Object oValue = oResultSet.getObject(sName); String sMappedName = _htMapper.get(sName); if (sMappedName != null) sName = sMappedName; if (oValue == null) oValue = ""; oAttributes.put(sName, oValue); } } } catch (SQLException e) { _logger.error("Could not gather attributes for user with id: " + sUserId, e); throw new AttributeException(SystemErrors.ERROR_RESOURCE_RETRIEVE); } catch (Exception e) { _logger.fatal("Could not initialize object", e); throw new AttributeException(SystemErrors.ERROR_INTERNAL); } finally { try { if (oResultSet != null) oResultSet.close(); } catch (Exception e) { _logger.error("Could not close resultset", e); } try { if (oPreparedStatement != null) oPreparedStatement.close(); } catch (Exception e) { _logger.error("Could not close statement", e); } try { if (oConnection != null) oConnection.close(); } catch (Exception e) { _logger.error("Could not disconnect prepared statement", e); } } }
From source file:jp.primecloud.auto.tool.management.db.SQLExecuter.java
public List<List<Object>> showColumn(String sql) throws SQLException, Exception { Connection con = null;/* w w w .ja v a2 s . c om*/ Statement stmt = null; ResultSet rs = null; log.info("[" + sql + "] ???"); List<List<Object>> results = new ArrayList<List<Object>>(); try { con = dbConnector.getConnection(); stmt = con.createStatement(); rs = stmt.executeQuery(sql); ResultSetMetaData rsMetaData = rs.getMetaData(); int size = rsMetaData.getColumnCount(); List<Object> columnNames = new ArrayList<Object>(); for (int n = 1; n <= size; n++) { columnNames.add(rsMetaData.getColumnName(n)); } results.add(columnNames); while (rs.next()) { List<Object> columns = new ArrayList<Object>(); for (int i = 1; i <= size; i++) { columns.add(rs.getObject(i)); } results.add(columns); } 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:org.jfree.data.jdbc.JDBCCategoryDataset.java
/** * Populates the dataset by executing the supplied query against the * existing database connection. If no connection exists then no action * is taken./*from www . j av a 2 s . c o m*/ * <p> * The results from the query are extracted and cached locally, thus * applying an upper limit on how many rows can be retrieved successfully. * * @param con the connection. * @param query the query. * * @throws SQLException if there is a problem executing the query. */ public void executeQuery(Connection con, String query) throws SQLException { Statement statement = null; ResultSet resultSet = null; try { statement = con.createStatement(); resultSet = statement.executeQuery(query); ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); if (columnCount < 2) { throw new SQLException("JDBCCategoryDataset.executeQuery() : insufficient columns " + "returned from the database."); } // Remove any previous old data int i = getRowCount(); while (--i >= 0) { removeRow(i); } while (resultSet.next()) { // first column contains the row key... Comparable rowKey = resultSet.getString(1); for (int column = 2; column <= columnCount; column++) { Comparable columnKey = metaData.getColumnName(column); int columnType = metaData.getColumnType(column); switch (columnType) { case Types.TINYINT: case Types.SMALLINT: case Types.INTEGER: case Types.BIGINT: case Types.FLOAT: case Types.DOUBLE: case Types.DECIMAL: case Types.NUMERIC: case Types.REAL: { Number value = (Number) resultSet.getObject(column); if (this.transpose) { setValue(value, columnKey, rowKey); } else { setValue(value, rowKey, columnKey); } break; } case Types.DATE: case Types.TIME: case Types.TIMESTAMP: { Date date = (Date) resultSet.getObject(column); Number value = new Long(date.getTime()); if (this.transpose) { setValue(value, columnKey, rowKey); } else { setValue(value, rowKey, columnKey); } break; } case Types.CHAR: case Types.VARCHAR: case Types.LONGVARCHAR: { String string = (String) resultSet.getObject(column); try { Number value = Double.valueOf(string); if (this.transpose) { setValue(value, columnKey, rowKey); } else { setValue(value, rowKey, columnKey); } } catch (NumberFormatException e) { // suppress (value defaults to null) } break; } default: // not a value, can't use it (defaults to null) break; } } } fireDatasetChanged(new DatasetChangeInfo()); //TODO: fill in real change info } finally { if (resultSet != null) { try { resultSet.close(); } catch (Exception e) { // report this? } } if (statement != null) { try { statement.close(); } catch (Exception e) { // report this? } } } }
From source file:com.novartis.opensource.yada.format.ResultSetResultJSONConverter.java
/** * Converts data from a {@link java.sql.ResultSet} into a {@link JSONArray} containing * one {@link JSONObject} per row//www . j a v a 2 s. c om * @param rs the result set containing the data to convert to JSON * @return a json array containing the data * @throws SQLException when iteration or access to {@code rs} fails */ protected JSONArray getJSONRows(ResultSet rs) throws SQLException { JSONArray rows = new JSONArray(); ResultSetMetaData rsmd = rs.getMetaData(); if (rsmd == null) rsmd = new RowSetMetaDataImpl(); List<String> convertedResult = new ArrayList<>(); while (rs.next()) { JSONObject row = new JSONObject(); String colValue; for (int i = 1; i <= rsmd.getColumnCount(); i++) { String origColName = rsmd.getColumnName(i); if (!origColName.toLowerCase().equals(JDBCAdaptor.ROWNUM_ALIAS)) { boolean harmonize = isHarmonized(); boolean prune = harmonize ? ((JSONObject) this.harmonyMap).has(Harmonizer.PRUNE) && ((JSONObject) this.harmonyMap).getBoolean(Harmonizer.PRUNE) : false; String col = origColName; if (harmonize) { if (((JSONObject) this.harmonyMap).has(origColName)) { col = ((JSONObject) this.harmonyMap).getString(origColName); } else if (prune) { col = ""; } } //TODO handle empty result set more intelligently // OLD WAY adds headers to empty object when rs is empty if (!"".equals(col)) { if (null == rs.getString(origColName) || NULL.equals(rs.getString(origColName))) { colValue = NULL_REPLACEMENT; } else { colValue = rs.getString(origColName); } row.put(col, colValue); } } } rows.put(row); convertedResult.add(row.toString()); } if (rows.length() > 0) { for (String key : JSONObject.getNames(rows.getJSONObject(0))) { getYADAQueryResult().addConvertedHeader(key); } getYADAQueryResult().getConvertedResults().add(convertedResult); } return rows; }
From source file:eu.stratosphere.api.io.jdbc.JDBCInputFormat.java
/** * Stores the next resultSet row in a Record * // www . jav a 2s. co m * @param record * target Record * @return boolean value indicating that the operation was successful */ @Override public boolean nextRecord(Record record) { try { resultSet.next(); ResultSetMetaData rsmd = resultSet.getMetaData(); int column_count = rsmd.getColumnCount(); record.setNumFields(column_count); for (int pos = 0; pos < column_count; pos++) { int type = rsmd.getColumnType(pos + 1); retrieveTypeAndFillRecord(pos, type, record); } return true; } catch (SQLException e) { throw new IllegalArgumentException("Couldn't read data:\t" + e.getMessage()); } catch (NotTransformableSQLFieldException e) { throw new IllegalArgumentException( "Couldn't read data because of unknown column sql-type:\t" + e.getMessage()); } catch (NullPointerException e) { throw new IllegalArgumentException("Couldn't access resultSet:\t" + e.getMessage()); } }
From source file:org.syncope.core.util.ImportExport.java
private void doExportTable(final TransformerHandler handler, final Connection conn, final String tableName) throws SQLException, SAXException { AttributesImpl atts = new AttributesImpl(); PreparedStatement stmt = null; ResultSet rs = null;/*from w ww . j a v a2s .c o m*/ try { stmt = conn.prepareStatement("SELECT * FROM " + tableName + " a"); rs = stmt.executeQuery(); for (int rowNo = 0; rs.next(); rowNo++) { atts.clear(); ResultSetMetaData metaData = rs.getMetaData(); for (int i = 0; i < metaData.getColumnCount(); i++) { String columnName = metaData.getColumnName(i + 1); String value = rs.getString(columnName); if (value != null) { atts.addAttribute("", "", columnName, "CDATA", value); } } handler.startElement("", "", tableName, atts); handler.endElement("", "", tableName); } } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { LOG.error("While closing result set", e); } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { LOG.error("While closing result set", e); } } } }
From source file:com.cws.esolutions.security.dao.reference.impl.SecurityReferenceDAOImpl.java
/** * @see com.cws.esolutions.security.dao.reference.interfaces.ISecurityReferenceDAO#obtainSecurityQuestionList() *//* w w w . j ava 2 s. c o m*/ public synchronized List<String> obtainSecurityQuestionList() throws SQLException { final String methodName = ISecurityReferenceDAO.CNAME + "#obtainSecurityQuestionList() throws SQLException"; if (DEBUG) { DEBUGGER.debug(methodName); } Connection sqlConn = null; ResultSet resultSet = null; CallableStatement stmt = null; List<String> questionList = null; try { sqlConn = dataSource.getConnection(); if (sqlConn.isClosed()) { throw new SQLException("Unable to obtain application datasource connection"); } sqlConn.setAutoCommit(true); stmt = sqlConn.prepareCall("{CALL retrieve_user_questions()}"); if (DEBUG) { DEBUGGER.debug("CallableStatement: {}", stmt); } if (stmt.execute()) { resultSet = stmt.getResultSet(); resultSet.last(); int iRowCount = resultSet.getRow(); if (iRowCount == 0) { throw new SQLException("No security questions are currently configured."); } resultSet.first(); ResultSetMetaData resultData = resultSet.getMetaData(); int iColumns = resultData.getColumnCount(); questionList = new ArrayList<String>(); for (int x = 1; x < iColumns + 1; x++) { if (DEBUG) { DEBUGGER.debug("resultSet.getObject: {}", resultSet.getObject(resultData.getColumnName(x))); } // check if column is null resultSet.getObject(resultData.getColumnName(x)); // if the column was null, insert n/a, otherwise, insert the column's contents questionList.add((String) (resultSet.wasNull() ? "N/A" : resultSet.getObject(resultData.getColumnName(x)))); } } } catch (SQLException sqx) { throw new SQLException(sqx.getMessage(), sqx); } finally { if (resultSet != null) { resultSet.close(); } if (stmt != null) { stmt.close(); } if ((sqlConn != null) && (!(sqlConn.isClosed()))) { sqlConn.close(); } } return questionList; }
From source file:com.abixen.platform.service.businessintelligence.multivisualisation.service.impl.AbstractDatabaseService.java
public List<DataSourceColumnWeb> getColumns(Connection connection, String tableName) { List<DataSourceColumnWeb> columns = new ArrayList<>(); try {// www . j a va 2 s . c om ResultSetMetaData rsmd = getDatabaseMetaData(connection, tableName); int columnCount = rsmd.getColumnCount(); IntStream.range(1, columnCount + 1).forEach(i -> { try { columns.add(prepareDataSourceColumns(rsmd, i)); } catch (SQLException e) { throw new PlatformRuntimeException(e); } }); } catch (SQLException e) { throw new PlatformRuntimeException(e); } return columns; }