/**
* 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;
}
