List of usage examples for java.sql ResultSetMetaData getColumnCount
int getColumnCount() throws SQLException;
ResultSet object. From source file:it.unibas.spicy.persistence.idgenerator.utils.ReadDB.java
public ArrayList<InputDataModel> readTargetDatabase(String[] targetColumns) throws SQLException, IOException, ClassNotFoundException { ArrayList<InputDataModel> inputData = new ArrayList<>(); ArrayList<String> configurationProperties = getExportDatabaseConfig(); Connection connection = null; try {//from ww w . j a v a 2 s .com connection = getConnectionToDatabase(configurationProperties.get(0), configurationProperties.get(1) + configurationProperties.get(4), configurationProperties.get(2), configurationProperties.get(3)); Statement statement = connection.createStatement(); String columnsToQuery = ""; for (String column : targetColumns) { columnsToQuery += column + ","; } ResultSet tableRows = statement.executeQuery( "SELECT " + columnsToQuery.substring(0, columnsToQuery.length() - 1) + " FROM " + table + ";"); ResultSetMetaData rsmd = tableRows.getMetaData(); int columnsNumber = rsmd.getColumnCount(); while (tableRows.next()) { InputDataModel idm = new InputDataModel(); for (int i = 1; i <= columnsNumber; i++) { idm.addValue(String.valueOf(tableRows.getObject(i))); } inputData.add(idm); } } catch (ClassNotFoundException | SQLException e) { System.err.println(e.getMessage()); System.exit(-1); } finally { if (connection != null) connection.close(); } return inputData; }
From source file:BadProfessor.java
public void checkData(String sql) throws Exception { ResultSet rs = st.executeQuery(sql); ResultSetMetaData metadata = rs.getMetaData(); for (int i = 0; i < metadata.getColumnCount(); i++) { System.out.print("\t" + metadata.getColumnLabel(i + 1)); }/*from w w w . j a v a 2 s .c o m*/ System.out.println("\n----------------------------------"); while (rs.next()) { for (int i = 0; i < metadata.getColumnCount(); i++) { Object value = rs.getObject(i + 1); if (value == null) { System.out.print("\t "); } else { System.out.print("\t" + value.toString().trim()); } } System.out.println(""); } }
From source file:com.googlecode.datasourcetester.server.DataSourceTesterServiceImpl.java
public String[][] queryDataSource(String dataSourceJndiName, String query) { Connection conn = null;//from w w w . ja v a 2 s.c om try { InitialContext jndiContext = new InitialContext(); DataSource ds = (DataSource) jndiContext.lookup(dataSourceJndiName); conn = ds.getConnection(); PreparedStatement stmt = conn.prepareStatement(query); ResultSet rs = stmt.executeQuery(); ResultSetMetaData resMeta = rs.getMetaData(); LinkedList<String[]> rowList = new LinkedList<String[]>(); String[] colLabels = new String[resMeta.getColumnCount()]; for (int colNr = 1; colNr <= resMeta.getColumnCount(); colNr++) { colLabels[colNr - 1] = resMeta.getColumnName(colNr); } rowList.add(colLabels); while (rs.next()) { String[] rowData = new String[resMeta.getColumnCount()]; for (int colNr = 1; colNr <= resMeta.getColumnCount(); colNr++) { rowData[colNr - 1] = rs.getString(colNr); } rowList.add(rowData); } conn.close(); return rowList.toArray(new String[rowList.size()][]); } catch (Exception e) { logger.error(e.getMessage(), e); try { if (conn != null && !conn.isClosed()) { conn.close(); } } catch (SQLException sqlEx) { logger.error(sqlEx.getMessage(), sqlEx); } return null; } }
From source file:RawSQLServlet.java
/** Do the SQL query */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String query = request.getParameter("sql"); response.setContentType("text/html"); PrintWriter out = response.getWriter(); if (query == null) { out.println("<b>Error: malformed query, contact administrator</b>"); return;//from w w w . j a v a 2s .c o m } // NB MUST also check for admin privs before proceding! try { // SQL out.println("<p>Your query: <b>" + query + "</b></p>"); stmt.execute(query); ResultSet rs = stmt.getResultSet(); if (rs == null) { // print updatecount out.println("<p>Result: updateCount = <b>" + stmt.getUpdateCount() + "</p>"); } else { // process resultset out.println("<br>Your response:"); ResultSetMetaData md = rs.getMetaData(); int count = md.getColumnCount(); out.println("<table border=1>"); out.print("<tr>"); for (int i = 1; i <= count; i++) { out.print("<th>"); out.print(md.getColumnName(i)); } out.println("</tr>"); while (rs.next()) { out.print("<tr>"); for (int i = 1; i <= count; i++) { out.print("<td>"); out.print(rs.getString(i)); } out.println("</tr>"); } } out.println("</table>"); // rs.close(); } catch (SQLException ex) { out.print("<B>" + getClass() + ": SQL Error:</B>\n" + ex); out.print("<pre>"); ex.printStackTrace(out); out.print("</pre>"); } }
From source file:br.com.manish.ahy.kernel.util.DAOUtil.java
public List<Map<String, Object>> executeSQLQuery(String sql) { List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>(); try {/* w w w . j a v a2 s .com*/ log.info("executeSQLQuery: " + sql); rs = stmt.executeQuery(sql); while (rs.next()) { Map<String, Object> item = new HashMap<String, Object>(); ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; i < rsmd.getColumnCount() + 1; i++) { item.put(rsmd.getColumnLabel(i), rs.getObject(i)); } ret.add(item); } } catch (Exception e) { throw new OopsException(e, "Problem when executing sql command."); } return ret; }
From source file:ch.cern.db.flume.sink.kite.util.InferSchemaFromTable.java
public Schema getSchema() throws SQLException { FieldAssembler<Schema> builder = SchemaBuilder.record("log").fields(); Connection connection = DriverManager.getConnection(connection_url, connection_user, connection_password); Statement statement = connection.createStatement(); String query = "SELECT * " + "FROM " + tableName + " WHERE 0=1"; LOG.info("Running query for obtaining metadata: " + query); ResultSet result = statement.executeQuery(query); ResultSetMetaData metadata = result.getMetaData(); int columnCount = metadata.getColumnCount(); for (int i = 1; i <= columnCount; i++) { String columnName = metadata.getColumnName(i); int columnType = metadata.getColumnType(i); boolean nullable = metadata.isNullable(i) != ResultSetMetaData.columnNoNulls; FieldTypeBuilder<Schema> field = builder.name(columnName).doc("SQL type: " + columnType).type(); switch (columnType) { case java.sql.Types.SMALLINT: case java.sql.Types.TINYINT: case java.sql.Types.INTEGER: case java.sql.Types.BIGINT: if (nullable) field.nullable().intType().noDefault(); else// w w w . ja v a 2 s . co m field.intType().noDefault(); break; case java.sql.Types.BOOLEAN: if (nullable) field.nullable().booleanType().noDefault(); else field.booleanType().noDefault(); break; case java.sql.Types.NUMERIC: case java.sql.Types.DOUBLE: case java.sql.Types.FLOAT: if (nullable) field.nullable().doubleType().noDefault(); else field.doubleType().noDefault(); break; case java.sql.Types.TIMESTAMP: case -101: //TIMESTAMP(3) WITH TIME ZONE case -102: //TIMESTAMP(6) WITH LOCAL TIME ZONE default: if (nullable) field.nullable().stringType().noDefault(); else field.stringType().noDefault(); break; } } return builder.endRecord(); }
From source file:com.itdaoshi.dokeos.dao.UserDAObject.java
@Override protected Long getNextPrimaryID() { QueryRunner run = new QueryRunner(); ResultSetHandler h = new ResultSetHandler() { public Object handle(ResultSet rs) throws SQLException { if (!rs.next()) { return null; }/*from w w w . j a va 2 s .c o m*/ ResultSetMetaData meta = rs.getMetaData(); int cols = meta.getColumnCount(); Object[] result = new Object[cols]; for (int i = 0; i < cols; i++) { result[i] = rs.getObject(i + 1); } return result; } }; try { Object[] result = (Object[]) run.query(conn, "SELECT MAX(user_id) FROM USER ", h); return (Long) result[0] + 1; // do something with the result } catch (Exception e) { e.printStackTrace(); } return null; }
From source file:com.teradata.benchto.driver.execution.QueryExecutionDriver.java
private void logRow(int rowNumber, ResultSet resultSet) throws SQLException { ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); StringJoiner joiner = new StringJoiner("; ", "[", "]"); for (int i = 1; i <= resultSetMetaData.getColumnCount(); ++i) { joiner.add(resultSetMetaData.getColumnName(i) + ": " + resultSet.getObject(i)); }/*ww w .j a v a 2 s. co m*/ LOG.info("Row: " + rowNumber + ", column values: " + joiner.toString()); }
From source file:it.unibas.spicy.persistence.idgenerator.utils.ReadDB.java
public ArrayList<InputDataModel> readSourceDatabase(ArrayList<ColumnMatcherModel> cmmList) throws SQLException, IOException, ClassNotFoundException { ArrayList<InputDataModel> inputData = new ArrayList<>(); ArrayList<String> configurationProperties = getExportDatabaseConfig(); Connection connection = null; try {//from w ww .j a v a2s . co m connection = getConnectionToDatabase(configurationProperties.get(0), configurationProperties.get(1) + configurationProperties.get(4), configurationProperties.get(2), configurationProperties.get(3)); Statement statement = connection.createStatement(); String columnsToQuery = ""; for (ColumnMatcherModel cmm : cmmList) { if (!cmm.getSourceColumn().equalsIgnoreCase("CONSTANT_VALUE_SOURCE")) { columnsToQuery += cmm.getSourceColumn() + ","; } } ResultSet tableRows = null; if (columnsToQuery.length() > 0) { columnsToQuery = columnsToQuery.substring(0, columnsToQuery.length() - 1); tableRows = statement.executeQuery("SELECT " + columnsToQuery + " FROM " + table + ";"); } else { tableRows = statement.executeQuery("SELECT * FROM " + table + ";"); } ResultSetMetaData rsmd = tableRows.getMetaData(); int columnsNumber = rsmd.getColumnCount(); while (tableRows.next()) { InputDataModel idm = new InputDataModel(); for (int i = 1; i <= columnsNumber; i++) { idm.addValue(String.valueOf(tableRows.getObject(i))); if (columnsToQuery.length() > 0) { idm.addKey(columnsToQuery.split(",")[i - 1]); } else { idm.addKey("none"); } } inputData.add(idm); } } catch (ClassNotFoundException | SQLException e) { System.err.println(e.getMessage()); System.exit(-1); } finally { if (connection != null) connection.close(); } return inputData; }
From source file:uk.ac.kcl.rowmappers.DocumentRowMapper.java
private void mapDBFields(Document doc, ResultSet rs) throws SQLException, IOException { //add additional query fields for ES export ResultSetMetaData meta = rs.getMetaData(); int colCount = meta.getColumnCount(); for (int col = 1; col <= colCount; col++) { Object value = rs.getObject(col); if (value != null) { String colLabel = meta.getColumnLabel(col).toLowerCase(); if (!fieldsToIgnore.contains(colLabel)) { DateTime dateTime;//from w w w . j a v a2s .c om //map correct SQL time types switch (meta.getColumnType(col)) { case 91: Date dt = (Date) value; dateTime = new DateTime(dt.getTime()); doc.getAssociativeArray().put(meta.getColumnLabel(col).toLowerCase(), eSCompatibleDateTimeFormatter.print(dateTime)); break; case 93: Timestamp ts = (Timestamp) value; dateTime = new DateTime(ts.getTime()); doc.getAssociativeArray().put(meta.getColumnLabel(col).toLowerCase(), eSCompatibleDateTimeFormatter.print(dateTime)); break; default: doc.getAssociativeArray().put(meta.getColumnLabel(col).toLowerCase(), rs.getString(col)); break; } } } //map binary content from FS or database if required (as per docman reader) if (value != null && meta.getColumnLabel(col).equalsIgnoreCase(binaryContentFieldName)) { switch (binaryContentSource) { case "database": doc.setBinaryContent(rs.getBytes(col)); break; case "fileSystemWithDBPath": Resource resource = context.getResource(pathPrefix + rs.getString(col)); doc.setBinaryContent(IOUtils.toByteArray(resource.getInputStream())); break; default: break; } } } }