List of usage examples for javax.servlet.jsp.jstl.sql Result getRowCount
public int getRowCount();
From source file:pivotal.au.se.gemfirexdweb.controller.QueryController.java
@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() + "/GemFireXD-Web/login"); return null; } else {/*w w w . ja v a 2 s. c om*/ Connection conn = AdminUtil.getConnection((String) session.getAttribute("user_key")); if (conn == null) { response.sendRedirect(request.getContextPath() + "/GemFireXD-Web/login"); return null; } else { if (conn.isClosed()) { response.sendRedirect(request.getContextPath() + "/GemFireXD-Web/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) { if (queryAttribute.getSaveWorksheet().equals("Y")) { response.setContentType(SAVE_CONTENT_TYPE); response.setHeader("Content-Disposition", "attachment; filename=" + FILENAME); ServletOutputStream out = response.getOutputStream(); out.println(queryAttribute.getQuery()); out.close(); return 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 { final String explain = queryAttribute.getExplainPlan(); if (!explain.equals("N")) { logger.debug("Need to run explain plan."); String explainString = ""; if (explain.equals("Y")) { explainString = "explain as xml %s"; } else if (explain.equals("T")) { explainString = "explain %s"; } String xPlan = QueryUtil.runExplainPlan(conn, String.format(explainString, query)); logger.debug("received xPath : " + xPlan); if (explain.equals("Y")) { model.addAttribute("explainresult", xPlan); } else if (explain.equals("T")) { model.addAttribute("explaintxtresult", xPlan); } } 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"; }
From source file:vmware.au.se.sqlfireweb.controller.QueryController.java
@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; }/*from w ww . j ava2 s. com*/ 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"; }