Java tutorial
/* Copyright (c) 2013 GoPivotal, Inc. All Rights Reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; only version 2 of the License, and no later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA The full text of the GPL is provided in the COPYING file. */ package vmware.au.se.sqlfireweb.controller; import java.sql.Connection; import java.sql.SQLException; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Iterator; import java.util.LinkedList; import java.util.List; import java.util.SortedMap; import java.util.TreeMap; import java.util.regex.Pattern; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import javax.servlet.jsp.jstl.sql.Result; import org.apache.commons.fileupload.FileItem; import org.apache.commons.fileupload.FileItemFactory; import org.apache.commons.fileupload.disk.DiskFileItemFactory; import org.apache.commons.fileupload.servlet.ServletFileUpload; import org.apache.log4j.Logger; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.ModelAttribute; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import vmware.au.se.sqlfireweb.beans.CommandResult; import vmware.au.se.sqlfireweb.beans.QueryWindow; import vmware.au.se.sqlfireweb.main.UserPref; import vmware.au.se.sqlfireweb.utils.ConnectionManager; import vmware.au.se.sqlfireweb.utils.QueryUtil; @Controller public class QueryController { protected static Logger logger = Logger.getLogger("controller"); // add comments here private final String QUERY_TYPES[] = { "SELECT", "INSERT", "DELETE", "DDL", "UPDATE", "CALL", "COMMIT", "ROLLBACK" }; @RequestMapping(value = "/query", method = RequestMethod.GET) public String worksheet(Model model, HttpServletResponse response, HttpServletRequest request, HttpSession session) throws Exception { if (session.getAttribute("user_key") == null) { logger.debug("user_key is null new Login required"); response.sendRedirect(request.getContextPath() + "/sqlfireweb/login"); return null; } logger.debug("Received request to show query worksheet"); UserPref userPrefs = (UserPref) session.getAttribute("prefs"); String action = request.getParameter("action"); if (action != null) { CommandResult result = new CommandResult(); ConnectionManager cm = ConnectionManager.getInstance(); Connection conn = cm.getConnection(session.getId()); if (action.trim().equals("commit")) { logger.debug("commit action requested"); result = QueryUtil.runCommitOrRollback(conn, true, "N"); addCommandToHistory(session, userPrefs, "commit"); } else if (action.trim().equals("rollback")) { logger.debug("rollback action requested"); result = QueryUtil.runCommitOrRollback(conn, false, "N"); addCommandToHistory(session, userPrefs, "rollback"); } model.addAttribute("result", result); } // Create new QueryWindow and add to model // This is the formBackingObject model.addAttribute("queryAttribute", new QueryWindow()); // This will resolve to /WEB-INF/jsp/query.jsp return "query"; } @RequestMapping(value = "/query", method = RequestMethod.POST) public String worksheetAction(@ModelAttribute("queryAttribute") QueryWindow queryAttribute, Model model, HttpServletResponse response, HttpServletRequest request, HttpSession session) throws Exception { if (session.getAttribute("user_key") == null) { logger.debug("user_key is null new Login required"); response.sendRedirect(request.getContextPath() + "/sqlfireweb/login"); return null; } logger.debug("Received request to action SQL from query worksheet"); logger.info(queryAttribute); UserPref userPrefs = (UserPref) session.getAttribute("prefs"); ConnectionManager cm = ConnectionManager.getInstance(); if (queryAttribute.getQuery() != null) { // retrieve connection Connection conn = cm.getConnection(session.getId()); String query = queryAttribute.getQuery().trim(); logger.debug("Query = " + query); String[] splitQueryStr = spiltQuery(query); CommandResult result = new CommandResult(); if (query.length() > 0) { if (splitQueryStr.length == 1) { String s = checkForComments(query); s = s.trim(); if (determineQueryType(s).equals("SELECT")) { try { if (queryAttribute.getExplainPlan().equals("Y")) { logger.debug("Need to run explain plan"); model.addAttribute("explainresult", QueryUtil.runExplainPlan(conn, query)); } else { if (queryAttribute.getShowMember().equals("Y")) { String replace = "select dsid() as \"Member\","; s = query.toLowerCase().replaceFirst("select", replace); } long start = System.currentTimeMillis(); Result res = QueryUtil.runQuery(conn, s, userPrefs.getMaxRecordsinSQLQueryWindow()); long end = System.currentTimeMillis(); double timeTaken = new Double(end - start).doubleValue(); DecimalFormat df = new DecimalFormat("#.##"); model.addAttribute("queryResults", res); model.addAttribute("query", s); model.addAttribute("querysql", s); if (queryAttribute.getQueryCount().equals("Y")) { model.addAttribute("queryResultCount", res.getRowCount()); } if (queryAttribute.getElapsedTime().equals("Y")) { model.addAttribute("elapsedTime", df.format(timeTaken / 1000)); } addCommandToHistory(session, userPrefs, s); } } catch (Exception ex) { result.setCommand(s); result.setMessage(ex.getMessage() == null ? "Unable to run query" : ex.getMessage()); result.setRows(-1); model.addAttribute("result", result); model.addAttribute("query", s); } } else { if (s.length() > 0) { if (determineQueryType(s).equals("COMMIT")) { result = QueryUtil.runCommitOrRollback(conn, true, queryAttribute.getElapsedTime()); model.addAttribute("result", result); if (result.getMessage().startsWith("SUCCESS")) { addCommandToHistory(session, userPrefs, s); } } else if (determineQueryType(s).equals("ROLLBACK")) { result = QueryUtil.runCommitOrRollback(conn, false, queryAttribute.getElapsedTime()); model.addAttribute("result", result); if (result.getMessage().startsWith("SUCCESS")) { addCommandToHistory(session, userPrefs, s); } } else if (determineQueryType(s).equals("CALL")) { String procName = getProcName(s); if (procName != null) { String schema = null; int x = procName.indexOf("."); if (x != -1) { String newProcName = procName.substring((procName.indexOf(".") + 1)); schema = procName.substring(0, (procName.indexOf("."))); procName = newProcName; } else { schema = (String) session.getAttribute("schema"); } logger.debug("schema for stored procedure = " + schema); logger.debug("call statement called for proc with name " + procName); // need to get schema name to check proc details int numberOfDynamicResultSets = QueryUtil.checkForDynamicResultSetProc(conn, schema, procName); if (numberOfDynamicResultSets > 0) { logger.debug("call statement with " + numberOfDynamicResultSets + " dynamic resultset(s)"); try { List<Result> procResults = QueryUtil.runStoredprocWithResultSet(conn, s, userPrefs.getMaxRecordsinSQLQueryWindow(), numberOfDynamicResultSets); model.addAttribute("procresults", procResults); model.addAttribute("callstatement", procName); model.addAttribute("dynamicresults", numberOfDynamicResultSets); addCommandToHistory(session, userPrefs, s); } catch (Exception ex) { result.setCommand(s); result.setMessage(ex.getMessage() == null ? "Unable to run query" : ex.getMessage()); result.setRows(-1); model.addAttribute("result", result); model.addAttribute("query", s); } } else { result = QueryUtil.runCommand(conn, s, queryAttribute.getElapsedTime()); model.addAttribute("result", result); if (result.getMessage().startsWith("SUCCESS")) { addCommandToHistory(session, userPrefs, s); } } } else { result = QueryUtil.runCommand(conn, s, queryAttribute.getElapsedTime()); model.addAttribute("result", result); if (result.getMessage().startsWith("SUCCESS")) { addCommandToHistory(session, userPrefs, s); } } } else { result = QueryUtil.runCommand(conn, s, queryAttribute.getElapsedTime()); model.addAttribute("result", result); if (result.getMessage().startsWith("SUCCESS")) { addCommandToHistory(session, userPrefs, s); } } } } } else { logger.debug("multiple SQL statements need to be executed"); SortedMap<String, Object> queryResults = handleMultipleStatements(splitQueryStr, conn, userPrefs, queryAttribute, session); logger.debug("keys : " + queryResults.keySet()); model.addAttribute("sqlResultMap", queryResults); model.addAttribute("statementsExecuted", queryResults.size()); } } } else { if (ServletFileUpload.isMultipartContent(request)) { logger.debug("is multipartcontent request"); FileItemFactory factory = new DiskFileItemFactory(); ServletFileUpload upload = new ServletFileUpload(factory); List<?> fileItemsList = upload.parseRequest(request); logger.debug("fileItemList size = " + fileItemsList.size()); Iterator<?> it = fileItemsList.iterator(); while (it.hasNext()) { FileItem fileItemTemp = (FileItem) it.next(); if (fileItemTemp.getFieldName().equals("sqlfilename")) { QueryWindow qw = new QueryWindow(); qw.setQuery(fileItemTemp.getString()); model.addAttribute("queryAttribute", qw); model.addAttribute("sqlfile", fileItemTemp.getName()); } } } } return "query"; } @RequestMapping(value = "/executequery", method = RequestMethod.GET) public String executeQuery(@ModelAttribute("queryAttribute") QueryWindow queryAttribute, Model model, HttpServletResponse response, HttpServletRequest request, HttpSession session) throws Exception { if (session.getAttribute("user_key") == null) { logger.debug("user_key is null new Login required"); response.sendRedirect(request.getContextPath() + "/isqlfire/login"); return null; } logger.debug("Received request to action a query directly"); UserPref userPrefs = (UserPref) session.getAttribute("prefs"); ConnectionManager cm = ConnectionManager.getInstance(); // retrieve connection Connection conn = cm.getConnection(session.getId()); String query = request.getParameter("query"); logger.debug("Query = " + query); QueryWindow qw = new QueryWindow(); qw.setQuery(query); qw.setElapsedTime("N"); qw.setExplainPlan("N"); qw.setQueryCount("N"); qw.setShowMember("N"); CommandResult result = new CommandResult(); String s = query.trim(); if (determineQueryType(s).equals("SELECT")) { try { Result res = QueryUtil.runQuery(conn, query, userPrefs.getMaxRecordsinSQLQueryWindow()); logger.debug("Query run"); model.addAttribute("queryResults", res); model.addAttribute("queryAttribute", qw); model.addAttribute("querysql", query); } catch (Exception ex) { logger.debug("in here"); result.setCommand(query); result.setMessage(ex.getMessage() == null ? "Unable to run query" : ex.getMessage()); result.setRows(-1); model.addAttribute("result", result); model.addAttribute("query", query); } } else if (determineQueryType(s).equals("COMMIT")) { result = QueryUtil.runCommitOrRollback(conn, true, qw.getElapsedTime()); model.addAttribute("result", result); } else if (determineQueryType(s).equals("ROLLBACK")) { result = QueryUtil.runCommitOrRollback(conn, false, qw.getElapsedTime()); model.addAttribute("result", result); } else if (determineQueryType(s).equals("CALL")) { String procName = getProcName(s); if (procName != null) { String schema = null; int x = procName.indexOf("."); if (x != -1) { String newProcName = procName.substring((procName.indexOf(".") + 1)); schema = procName.substring(0, (procName.indexOf("."))); procName = newProcName; } else { schema = (String) session.getAttribute("schema"); } logger.debug("schema for stored procedure = " + schema); logger.debug("call statement called for proc with name " + procName); // need to get schema name to check proc details int numberOfDynamicResultSets = QueryUtil.checkForDynamicResultSetProc(conn, schema, procName); if (numberOfDynamicResultSets > 0) { logger.debug("call statement with " + numberOfDynamicResultSets + " dynamic resultset(s)"); try { List<Result> procResults = QueryUtil.runStoredprocWithResultSet(conn, s, userPrefs.getMaxRecordsinSQLQueryWindow(), numberOfDynamicResultSets); model.addAttribute("procresults", procResults); model.addAttribute("callstatement", procName); model.addAttribute("dynamicresults", numberOfDynamicResultSets); } catch (Exception ex) { result.setCommand(s); result.setMessage(ex.getMessage() == null ? "Unable to run query" : ex.getMessage()); result.setRows(-1); model.addAttribute("result", result); model.addAttribute("query", s); } } else { result = QueryUtil.runCommand(conn, s, qw.getElapsedTime()); model.addAttribute("result", result); } } else { result = QueryUtil.runCommand(conn, s, qw.getElapsedTime()); model.addAttribute("result", result); } } else { result = QueryUtil.runCommand(conn, s, qw.getElapsedTime()); model.addAttribute("result", result); } return "query"; } private String determineQueryType(String query) { String sQuery = query.toLowerCase().trim(); if (sQuery.startsWith("select")) { return decodeType(0); } else if (sQuery.startsWith("insert")) { return decodeType(1); } else if (sQuery.startsWith("delete")) { return decodeType(2); } else if (sQuery.startsWith("alter")) { return decodeType(3); } else if (sQuery.startsWith("update")) { return decodeType(4); } else if (sQuery.startsWith("call")) { return decodeType(5); } else if (sQuery.equals("commit;") || sQuery.equals("commit")) { return decodeType(6); } else if (sQuery.equals("rollback;") || sQuery.equals("rollback")) { return decodeType(7); } else { return decodeType(3); } } private String decodeType(int type) { return QUERY_TYPES[type]; } private String[] spiltQuery(String query) { Pattern pattern = Pattern.compile(";\\s", Pattern.MULTILINE); String[] splitQueryStr = pattern.split(query); logger.debug("split query = {" + Arrays.toString(splitQueryStr) + "}"); return splitQueryStr; } private SortedMap<String, Object> handleMultipleStatements(String[] splitQueryStr, Connection conn, UserPref userPrefs, QueryWindow queryAttribute, HttpSession session) throws SQLException { int counter = 9000; SortedMap<String, Object> queryResults = new TreeMap<String, Object>(); for (String nextQuery : splitQueryStr) { CommandResult result = new CommandResult(); List queryResult = new ArrayList(); String s = checkForComments(nextQuery.trim()); s = s.trim(); if (determineQueryType(s).equals("SELECT")) { Result res = null; try { long start = System.currentTimeMillis(); res = QueryUtil.runQuery(conn, s, userPrefs.getMaxRecordsinSQLQueryWindow()); long end = System.currentTimeMillis(); double timeTaken = new Double(end - start).doubleValue(); DecimalFormat df = new DecimalFormat("#.##"); queryResult.add(s); queryResult.add(res); if (queryAttribute.getElapsedTime().equals("Y")) { queryResult.add(df.format(timeTaken / 1000)); } queryResults.put(counter + "SELECT", queryResult); addCommandToHistory(session, userPrefs, s); } catch (Exception ex) { result.setCommand(s); result.setMessage(ex.getMessage() == null ? "Unable to run query" : ex.getMessage()); result.setRows(-1); queryResults.put(counter + "SELECTERROR", result); } counter++; } else { if (s.length() > 0) { if (determineQueryType(s).equals("COMMIT")) { result = QueryUtil.runCommitOrRollback(conn, true, queryAttribute.getElapsedTime()); } else if (determineQueryType(s).equals("ROLLBACK")) { result = QueryUtil.runCommitOrRollback(conn, false, queryAttribute.getElapsedTime()); } else { result = QueryUtil.runCommand(conn, s, queryAttribute.getElapsedTime()); } if (result.getMessage().startsWith("SUCCESS")) { addCommandToHistory(session, userPrefs, s); } if (determineQueryType(s).equals("INSERT")) { queryResults.put(counter + "INSERT", result); } else if (determineQueryType(s).equals("UPDATE")) { queryResults.put(counter + "UPDATE", result); } else if (determineQueryType(s).equals("DELETE")) { queryResults.put(counter + "DELETE", result); } else if (determineQueryType(s).equals("COMMIT")) { queryResults.put(counter + "COMMI", result); } else if (determineQueryType(s).equals("ROLLBACK")) { queryResults.put(counter + "ROLLBACK", result); } else { queryResults.put(counter + "DDL", result); } counter++; } } } return queryResults; } private String checkForComments(String s) { if (s.startsWith("--")) { int index = s.indexOf("\n"); if (index != -1) { String newQuery = s.substring(s.indexOf("\n")); if (newQuery.trim().startsWith("--")) { return checkForComments(newQuery.trim()); } else { return newQuery.trim(); } } else { return ""; } } else { return s; } } private String getProcName(String sql) { String query = sql.toLowerCase().trim(); String proc = null; try { int startIndex = query.indexOf(" "); int endIndex = query.indexOf("("); proc = query.substring(startIndex, (endIndex)); return proc.trim().toUpperCase(); } catch (Exception ex) { return null; } } private void addCommandToHistory(HttpSession session, UserPref prefs, String sql) { @SuppressWarnings("unchecked") LinkedList<String> historyList = (LinkedList<String>) session.getAttribute("history"); int maxsize = prefs.getHistorySize(); if (historyList.size() == maxsize) { historyList.remove((maxsize - 1)); historyList.addFirst(sql); } else { historyList.addFirst(sql); } } }