/** * Submits query via JDBC * * @param query SQL query string * @param statement sql statement to execute the query with * @param outputFilename name of file result set is to be written to * @param timeout time allowed for query execution * @throws Exception */ public void submitQueryJDBC( String query, Statement statement, String outputFilename, long timeout) throws Exception { BufferedWriter writer = null; if (outputFilename != null) { writer = new BufferedWriter(new FileWriter(new File(outputFilename))); } ResultSet resultSet = null; try { RunThread runThread = new RunThread(statement, query); processThread(runThread, timeout); resultSet = runThread.getResultSet(); if (resultSet == null) { throw runThread.getException(); } if (outputFilename == null) { return; } int columnCount = resultSet.getMetaData().getColumnCount(); columnLabels = new ArrayList<String>(); for (int i = 1; i <= columnCount; i++) { columnLabels.add(resultSet.getMetaData().getColumnLabel(i)); } Object[] types = new Object[columnCount]; for (int i = 1; i <= columnCount; i++) { types[i - 1] = resultSet.getMetaData().getColumnType(i); } ColumnList.setTypes(types); LOG.debug("Result set data types:"); LOG.debug(Utils.getTypesInStrings(ColumnList.getTypes())); while (resultSet.next()) { Object[] values = new Object[columnCount]; for (int i = 1; i <= columnCount; i++) { try { if (resultSet.getObject(i) == null) { values[i - 1] = null; continue; } values[i - 1] = new String(resultSet.getBytes(i)); } catch (Exception e) { if (resultSet.getMetaData().getColumnType(i) == Types.DATE) { values[i - 1] = resultSet.getDate(i); } else { values[i - 1] = resultSet.getObject(i); } } } ColumnList columnList = new ColumnList(values); if (writer != null) { writer.write(columnList + "\n"); } } if (writer != null) { writer.close(); } } finally { if (resultSet != null) { resultSet.close(); } } }
// this function should only be used while the sql resultset is still valid, // i.e. the connection related to the resultset is still valid (not closed) public static String getSqlResult(ResultSet resultSet) throws SQLException { StringBuffer stringBuffer = new StringBuffer(); List columnLabels = new ArrayList<String>(); try { int columnCount = resultSet.getMetaData().getColumnCount(); for (int i = 1; i <= columnCount; i++) { columnLabels.add(resultSet.getMetaData().getColumnLabel(i)); } List<Integer> types = Lists.newArrayList(); for (int i = 1; i <= columnCount; i++) { types.add(resultSet.getMetaData().getColumnType(i)); } LOG.debug("Result set data types:"); LOG.debug(Utils.getTypesInStrings(types)); stringBuffer.append(new ColumnList(types, columnLabels).toString() + "\n"); while (resultSet.next()) { List<Object> values = Lists.newArrayList(); for (int i = 1; i <= columnCount; i++) { try { if (resultSet.getObject(i) == null) { values.add(null); continue; } if (resultSet.getMetaData().getColumnType(i) == Types.NVARCHAR) { values.add(new String(resultSet.getBytes(i), "UTF-16")); } else { values.add(new String(resultSet.getBytes(i), "UTF-8")); } } catch (Exception e) { if (resultSet.getMetaData().getColumnType(i) == Types.DATE) { values.add(resultSet.getDate(i)); } else { values.add(resultSet.getObject(i)); } } } stringBuffer.append(new ColumnList(types, values).toString() + "\n"); } } catch (IllegalArgumentException | IllegalAccessException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } finally { if (resultSet != null) { resultSet.close(); } } return stringBuffer.toString(); }