List of usage examples for org.apache.commons.dbutils DbUtils closeQuietly
public static void closeQuietly(Connection conn, Statement stmt, ResultSet rs)
Connection, Statement and ResultSet. From source file:com.graph.pie.PieGraphData.java
public PieGraphData(String startDate, String endDate) { java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; try {// w ww. j a v a 2 s.co m con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement( "SELECT DISTINCT TOP 15 LeadOrganization.Country, COUNT(LeadOrganization.Country) as c FROM LeadOrganization, LeadRemoteAddress, leadSession WHERE leadSession.remoteAddress = LeadRemoteAddress.ip AND LeadRemoteAddress.LeadOrganization_uid = LeadOrganization.Id AND LeadRemoteAddress.prefered = 1 AND LeadOrganization.Country != '' AND leadSession.timeIn > CONVERT(date, ?) AND leadSession.timeIn < CONVERT(date, ?) GROUP BY LeadOrganization.Country HAVING COUNT(LeadOrganization.Country) > 0 ORDER BY c DESC"); stmt.setString(1, startDate); stmt.setString(2, endDate); rs = stmt.executeQuery(); languages = new HashMap<>(); while (rs.next()) { /* if (rs.getString("userAgent") != null) { String lang = new UserAgentDetector().parseUserAgent(rs.getString("userAgent")).getLocale().country.getLabel(); if (languages.containsKey(lang)) { languages.put(lang, languages.get(lang) + 1); } else { languages.put(lang, 1); } } */ Locale l = new Locale("", rs.getString("Country")); languages.put(l.getDisplayCountry(), rs.getInt("c")); } //languages.remove("Unknown"); con.close(); } catch (IOException | SQLException | PropertyVetoException ex) { Logger.getLogger(PieGraphData.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(con, stmt, rs); } }
From source file:com.settings.SettingsServlet.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from w w w . j ava2 s .co m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("application/json"); if (request.getParameter("get") != null) { java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; try { con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement("SELECT * FROM leadCustomSearch ORDER BY Id DESC"); rs = stmt.executeQuery(); String data = ""; while (rs.next()) { data += "<a class=\"list-group-item\" href=\"#\"><h4 class=\"list-group-item-heading\">" + rs.getString("pageTitle") + " <i class=\"fa fa-minus pull-right delete\" id=\"" + rs.getString("Id") + "\"></i></h4><p class=\"list-group-item-text\">Type: " + rs.getString("type") + "</p></a>"; } con.close(); response.getWriter().write(new Gson().toJson(data)); } catch (SQLException | PropertyVetoException ex) { Logger.getLogger(PageServlet.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(con, stmt, rs); } } if (request.getParameter("autocomplete") != null) { java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; try { con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement("SELECT DISTINCT pageTitle FROM leadSession"); rs = stmt.executeQuery(); List<String> data = new ArrayList<>(); while (rs.next()) { if (rs.getString("pageTitle") != null) { data.add(rs.getString("pageTitle")); } } con.close(); response.getWriter().write(new Gson().toJson(data)); } catch (SQLException | PropertyVetoException ex) { Logger.getLogger(PageServlet.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(con, stmt, rs); } } if (request.getParameter("add") != null) { java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; try { con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement("INSERT INTO leadCustomSearch ([pageTitle],[type]) VALUES (?,?)"); stmt.setString(1, request.getParameter("add")); stmt.setString(2, "exclude"); stmt.executeUpdate(); con.close(); response.getWriter().write(new Gson().toJson("success")); } catch (SQLException | PropertyVetoException ex) { Logger.getLogger(PageServlet.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(con, stmt, rs); } } if (request.getParameter("delete") != null) { java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; try { con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement("DELETE leadCustomSearch WHERE Id = ?"); stmt.setString(1, request.getParameter("delete")); stmt.executeUpdate(); con.close(); response.getWriter().write(new Gson().toJson("success")); } catch (SQLException | PropertyVetoException ex) { Logger.getLogger(PageServlet.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(con, stmt, rs); } } }
From source file:com.graph.line.LineGraphData.java
public LineGraphData(String timeIn, String timeOut) { java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; try {/* w w w . jav a 2 s . c o m*/ con = ControlPanelPool.getInstance().getConnection(); DateTimeFormatter format = org.joda.time.format.DateTimeFormat.forPattern("MM/dd/yyyy"); LocalDate startDate = LocalDate.parse(timeIn, format); LocalDate endDate = LocalDate.parse(timeOut, format); int days = Days.daysBetween(startDate, endDate).getDays(); //List<LocalDate> dates = new ArrayList<>(days); sessionCount = new ArrayList<>(); addressCount = new ArrayList<>(); dateCount = new ArrayList<>(); for (int i = 0; i < days; i++) { LocalDate d2 = startDate.withFieldAdded(DurationFieldType.days(), i + 1); //dates.add(d); stmt = con.prepareStatement( "SELECT COUNT(DISTINCT remoteAddress) as remoteAdd, COUNT(DISTINCT sessionId) as sess FROM leadSession WHERE timeIn > CONVERT(date, ?) AND timeIn < CONVERT(date, ?)"); stmt.setString(1, startDate.withFieldAdded(DurationFieldType.days(), i).toString()); stmt.setString(2, startDate.withFieldAdded(DurationFieldType.days(), i + 1).toString()); rs = stmt.executeQuery(); while (rs.next()) { sessionCount.add(rs.getString("sess")); addressCount.add(rs.getString("remoteAdd")); } dateCount.add(startDate.withFieldAdded(DurationFieldType.days(), i).toString()); } con.close(); } catch (IOException | SQLException | PropertyVetoException ex) { Logger.getLogger(LineGraphData.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(con, stmt, rs); } }
From source file:com.graph.bar.BarGraphData.java
public BarGraphData(String startDate, String endDate, String domain) { java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; try {//w ww . j av a 2 s. c o m con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement(""); stmt = con.prepareStatement( "SELECT DISTINCT leadSession.referer, COUNT(leadSession.referer) as c FROM leadSession WHERE leadSession.timeIn > CONVERT(date, ?) AND leadSession.timeIn < CONVERT(date, ?) AND leadSession.referer != '' AND leadSession.referer is not null GROUP BY leadSession.referer HAVING COUNT(leadSession.referer) > 0"); stmt.setString(1, startDate); stmt.setString(2, endDate); rs = stmt.executeQuery(); refs = new ConcurrentHashMap<>(); while (rs.next()) { if (refs.containsKey(rs.getString("referer").split("/")[2])) { refs.put(rs.getString("referer").split("/")[2], refs.get(rs.getString("referer").split("/")[2]) + rs.getInt("c")); } else { refs.put(rs.getString("referer").split("/")[2], rs.getInt("c")); } } refs.put("Google", 0); refs.put("Yahoo", 0); refs.put("Bing", 0); refs.put("Other", 0); for (String key : refs.keySet()) { try { if (refs.get(key) != null && key.contains("google")) { refs.put("Google", refs.get("Google") + refs.get(key)); refs.remove(key); } if (refs.get(key) != null && key.contains("yahoo")) { refs.put("Yahoo", refs.get("Yahoo") + refs.get(key)); refs.remove(key); } if (refs.get(key) != null && key.contains("bing")) { refs.put("Bing", refs.get("Bing") + refs.get(key)); refs.remove(key); } //if (refs.get(key) != null && refs.get(key) < 6) { // refs.put("Other", refs.get("Other") + refs.get(key)); // refs.remove(key); //} if (refs.get(key) != null && key.contains(domain)) { //remove the self referred links refs.remove(key); } } catch (Exception e) { e.printStackTrace(); } } con.close(); } catch (IOException | SQLException | PropertyVetoException ex) { Logger.getLogger(BarGraphData.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(con, stmt, rs); } }
From source file:com.rem.RemData.java
public RemData(String ip, String contextPath) { java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; contacts = new ArrayList<>(); try {/*w w w . j av a 2s .co m*/ con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement( "SELECT DISTINCT TOP 30 leadSession.pageTitle, leadSession.location, COUNT(leadSession.pageTitle) as c FROM leadSession WHERE leadSession.remoteAddress = ? GROUP BY leadSession.pageTitle, leadSession.location HAVING COUNT(leadSession.pageTitle) > 0 ORDER BY c DESC"); //insert after where for timed search ->> leadSession.timeIn > CONVERT(date, ?) AND leadSession.timeIn < CONVERT(date, ?) AND //stmt.setString(1, startdate); //stmt.setString(2, enddate); stmt.setString(1, ip); rs = stmt.executeQuery(); pagesVisited = ""; while (rs.next()) { pagesVisited += "<a class=\"list-group-item\" target=\"_blank\" style=\"display: block; margin: 5px; font-size: 12pt;\" href=\"" + rs.getString("location") + "\" target=\"_blank\"><i class=\"fa fa-link fa-fw\"></i> " + ((rs.getString("pageTitle").length() < 70) ? rs.getString("pageTitle") : (rs.getString("pageTitle").substring(0, 70) + "...")) + " <span class=\"pull-right text-muted small\"><em>" + rs.getString("c") + " visits</em></span></a>"; } stmt = con.prepareStatement( "SELECT LeadOrganization.Id as networkId, LeadOrganization.Name as networkName, LeadOrganization.Country as networkCountry, LeadContact.Name as contactName, LeadContact.BusinessName as businessName, LeadContact.Email as contactEmail, LeadContact.Address as contactAddress, LeadContact.Id as contactId, LeadContact.Phone as contactPhone FROM LeadRemoteAddress, LeadOrganization, LeadContact WHERE ip = ? AND LeadRemoteAddress.prefered = 1 AND LeadRemoteAddress.LeadOrganization_uid = LeadOrganization.Id AND LeadRemoteAddress.ip = LeadContact.RemoteAddress"); stmt.setString(1, ip); rs = stmt.executeQuery(); while (rs.next()) { RemContact c = new RemContact(); c.name = rs.getString("contactName"); c.company = rs.getString("businessName"); c.email = rs.getString("contactEmail"); c.address = rs.getString("contactAddress"); c.id = rs.getString("contactId"); c.phone = rs.getString("contactPhone"); networkId = rs.getString("networkId"); networkName = rs.getString("networkName"); networkCountry = rs.getString("networkCountry"); contacts.add(c); } con.close(); } catch (SQLException | PropertyVetoException | IOException ex) { Logger.getLogger(RemData.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(con, stmt, rs); } }
From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java
@Override public boolean healthCheck() { Connection conn = null;/*from w w w . j a va 2 s .c o m*/ CallableStatement stmt = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("select now()"); stmt.execute(); return true; } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, null); } return false; }
From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java
@Override public String signin(final int userUid) { Connection conn = null;/*from w w w . j a va2 s . c o m*/ CallableStatement stmt = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_SIGNIN (?,?)}"); stmt.setInt(1, userUid); stmt.registerOutParameter(2, Types.VARCHAR); stmt.execute(); return stmt.getString(2); } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, null); } return null; }
From source file:com.page.PageServlet.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from w w w .ja v a 2 s . c o m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("application/json"); if (request.getParameter("url") != null) { java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; try { con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement( "SELECT TOP 5 remoteAddress, pageTitle, COUNT(pageTitle) AS c FROM leadSession WHERE location = ? GROUP BY remoteAddress, pageTitle ORDER BY c DESC"); stmt.setString(1, request.getParameter("url")); rs = stmt.executeQuery(); Map<String, String> data = new HashMap<>(); String left = ""; String right = ""; String title = ""; int counter = 0; while (rs.next()) { if (counter == 0) { title = rs.getString("pageTitle"); } if (counter < 5) { left += "<a class=\"list-group-item\" href=\"" + request.getContextPath() + "/rem?ip=" + rs.getString("remoteAddress") + "\"><h4 class=\"list-group-item-heading\">" + rs.getString("remoteAddress") + "</h4><p class=\"list-group-item-text\">Total clicks: " + rs.getString("c") + "</p></a>"; } counter++; } stmt = con.prepareStatement( "SELECT TOP 10 pageTitle, COUNT(pageTitle) AS c FROM leadSession WHERE remoteAddress IN (SELECT DISTINCT remoteAddress FROM leadSession WHERE location = ?) AND pageTitle NOT IN (SELECT pageTitle FROM leadCustomSearch WHERE type = 'exclude' AND pageTitle IS NOT NULL) AND pageTitle != ? GROUP BY pageTitle ORDER BY c DESC"); stmt.setString(1, request.getParameter("url")); stmt.setString(2, title); rs = stmt.executeQuery(); while (rs.next()) { right += "<a data-title=\"" + rs.getString("pageTitle") + "\" class=\"list-group-item\" href=\"#\"><h4 class=\"list-group-item-heading\">" + rs.getString("pageTitle") + "</h4><p class=\"list-group-item-text\">Total clicks: " + rs.getString("c") + " <i class=\"pull-right fa fa-trash trash\"></i></p></a>"; } con.close(); data.put("left", "<a data-toggle=\"collapse\" href=\"#collapse1\">Show users who are most interested in '" + title + "' <i class=\"fa fa-sort-down\"></i></a><div id=\"collapse1\" class=\"panel-collapse collapse\"><ul class=\"list-group\">" + left + "</ul></div>"); data.put("right", right); data.put("lefttitle", ""); data.put("righttitle", "Users who are intrested in '" + title + "' are also most interested in"); response.getWriter().write(new Gson().toJson(data)); } catch (SQLException | PropertyVetoException ex) { Logger.getLogger(PageServlet.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(con, stmt, rs); } } }
From source file:ch.newscron.referral.ReferralManager.java
/** * Provided a connection, statement and resultSet, closes all of these by using DbUtils * @param connection an open Connection to a database * @param statement an object needed to execute a database query/update * @param resultSet the returned information of a query *///from www.j a v a2 s. c o m public static void disconnect(Connection connection, Statement statement, ResultSet resultSet) { DbUtils.closeQuietly(connection, statement, resultSet); }
From source file:com.logger.TrackServlet.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from w w w. jav a 2s . c o m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); /* Pass the id of the user in the request url that is added when the js file is generated compare it to the domain selected during the registration, id the incoming request domain name matches the one selected during the registration store data The domain verification should happen once per request by verifying the associated user registered domain name with the current location domain name and if those are matching proceed to writing the record to the database in the contact parsing store the domain name of the website where the information was extracted from also for the verification logic it could work the opposite way, where the domain where the request comes from is ran against the database, where the if of the table is looked up and inserted into the remaining lead session requiring statements */ if (request.getParameter("q") == null) { String sessionId = request.getSession().getId(); String remoteAddress = request.getRemoteAddr(); //InetAddress tempAddress = InetAddress.getByName(remoteAddress); //String hostName = tempAddress.getHostName(); String pageTitle = request.getParameter("trackPageTitle"); String userAgent = request.getParameter("trackUserAgent"); String tempId = request.getParameter("id"); //String domain = request.getParameter("trackDomain"); //not needed for now, then use for client domain registration comparison String screenHeight = request.getParameter("trackScreenHeight"); String referer = request.getParameter("trackReferer"); String screenWidth = request.getParameter("trackScreenWidth"); String location = request.getParameter("trackLocation"); java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; try { con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement( "INSERT INTO [dbo].[leadSession] ([sessionId],[remoteAddress],[pageTitle],[referer],[location],[userAgent],[screenHeight],[screenWidth],[text],[temp],[timeIn],[timeOut]) VALUES (?,?,?,?,?,?,?,?,?,?,GETDATE(),?)"); stmt.setString(1, sessionId); stmt.setString(2, remoteAddress); stmt.setString(3, pageTitle); stmt.setString(4, referer); stmt.setString(5, location); stmt.setString(6, userAgent); stmt.setString(7, screenHeight); stmt.setString(8, screenWidth); stmt.setString(9, ""); stmt.setString(10, tempId); stmt.setString(11, ""); stmt.executeUpdate(); con.close(); } catch (SQLException | PropertyVetoException ex) { Logger.getLogger(TrackServlet.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(con, stmt, rs); } //store the company data try { JSONObject obj = new JSONObject(getJsonData(remoteAddress)); if (!ipExists(remoteAddress)) { for (int i = 0; i < obj.getJSONObject("data").getJSONArray("records").length(); i++) { JSONArray org = obj.getJSONObject("data").getJSONArray("records").getJSONArray(i); storeIp(remoteAddress, getOrganizationId(org), i == 0); } } } catch (JSONException ex) { Logger.getLogger(TrackServlet.class.getName()).log(Level.SEVERE, null, ex); } response.setHeader("Cache-control", "no-cache, no-store"); response.setHeader("Pragma", "no-cache"); response.setHeader("Expires", "-1"); response.setStatus(204); } if (request.getParameter("q") != null) { java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; try { if (request.getParameter("q").length() > 1) { con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement( "UPDATE leadSession SET text = ?, timeOut = GETDATE() WHERE temp = ?"); stmt.setString(1, request.getParameter("q").substring(1)); stmt.setString(2, request.getParameter("id")); stmt.executeUpdate(); parseContactInfo(request.getParameter("q"), request.getRemoteAddr(), request.getParameter("trackDomain"), request.getParameter("name"), request.getParameter("email"), request.getParameter("web"), request.getParameter("addr"), request.getParameter("bb"), request.getParameter("phone")); } else { con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement("UPDATE leadSession SET timeOut = GETDATE() WHERE temp = ?"); stmt.setString(1, request.getParameter("id")); stmt.executeUpdate(); } con.close(); } catch (SQLException | PropertyVetoException ex) { Logger.getLogger(TrackServlet.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(stmt); DbUtils.closeQuietly(con); } response.setStatus(204); } }