/** * 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(); } } }
/** * Generates physical or logical plan files. * * @param statement statement used * @param planFileName name of plan file * @param queryString query for which plan is being generated * @param type physical or logical * @param timeout time allowed for generation of plan file * @throws Exception */ public void generatePlan( Statement statement, String planFileName, String queryString, String type, long timeout) throws Exception { String query = "explain plan "; if (type.equals("logical")) { query += "without implementation "; } query += "for " + queryString; ResultSet resultSet = null; String planString = ""; try { LOG.debug("Submitting query:\n" + query.trim()); RunThread runThread = new RunThread(statement, query); processThread(runThread, timeout); resultSet = runThread.getResultSet(); resultSet.next(); resultSet.next(); planString = new String(resultSet.getBytes(2)); } finally { if (resultSet != null) { resultSet.close(); } } BufferedWriter writer = new BufferedWriter(new FileWriter(new File(planFileName))); writer.write(planString); writer.close(); }
/** * Executes a JDBC Query and iterates through the resultSet * * @param query * @param queryFileName * @param statement * @return */ public boolean executeQueryJDBC(String query, String queryFileName, Statement statement) throws Exception { boolean status = true; ResultSet resultSet = null; long startTime = 0l; long connTime = Long.MIN_VALUE; long executeTime = Long.MIN_VALUE; long firstRowFetchTime = Long.MIN_VALUE; long endTime = Long.MIN_VALUE; long lastRowFetchTime = Long.MIN_VALUE; long rowCount = 0l; int columnCount = 0; try { LOG.info("Extracted Query from : " + queryFileName); String basicFileName = (new File(queryFileName)).getName(); String queryLabel = basicFileName.subSequence(0, basicFileName.lastIndexOf(".q")).toString(); LOG.info("Executing Query : " + queryLabel); startTime = System.currentTimeMillis(); // Time to Connect connTime = System.currentTimeMillis(); LOG.info("Connect Time: " + ((connTime - startTime) / 1000f) + " sec"); RunThread runThread = new RunThread(statement, query); boolean success = processThread(runThread); if (!success) { return false; } resultSet = runThread.getResultSet(); // Time to Execute executeTime = System.currentTimeMillis(); LOG.info("Execute Time: " + ((executeTime - connTime) / 1000f) + " sec"); columnCount = resultSet.getMetaData().getColumnCount(); while (resultSet.next()) { if (rowCount == 0) firstRowFetchTime = System.currentTimeMillis(); rowCount++; lastRowFetchTime = System.currentTimeMillis(); } } catch (SQLException e) { e.printStackTrace(); LOG.error(e.getMessage()); status = false; } finally { endTime = System.currentTimeMillis(); try { LOG.info("Closing connections"); if (resultSet != null) { resultSet.close(); } } catch (SQLException e) { LOG.error("[ERROR] During close: " + e.getMessage()); status = false; } if (!status) LOG.error( "Last row was fetched at " + new Date(lastRowFetchTime) + " [" + ((endTime - lastRowFetchTime) / 1000f) + " secs ago]"); LOG.info("Time to fetch 1st row : " + ((firstRowFetchTime - executeTime) / 1000f) + " sec"); LOG.info( "Fetched " + rowCount + " rows with " + columnCount + " columns in " + ((endTime - executeTime) / 1000f) + " sec"); LOG.info("Fetch Rate: " + (rowCount * 1000f / (endTime - executeTime)) + " rows/sec "); } LOG.info("Total Time: " + (System.currentTimeMillis() - startTime) / 1000f + " sec "); return status; }