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.mobiaware.auction.data.impl.MySqlDataServiceImpl.java
@Override public void signout(final int userUid) { Connection conn = null;/*from w ww . jav a 2 s . c o m*/ CallableStatement stmt = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_SIGNOUT (?)}"); stmt.setInt(1, userUid); stmt.execute(); } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, null); } }
From source file:gov.nih.nci.cacisweb.dao.virtuoso.VirtuosoCommonUtilityDAO.java
/** * This method provides a central location for close operations that need to be performed on the database * connection./* w w w . j ava2 s. c o m*/ * * @throws KCCBusinessException */ public void closeCaCISConnection() throws DAOException { try { DbUtils.closeQuietly(cacisConnection, pstmt, rs); DbUtils.closeQuietly(stmt); DbUtils.closeQuietly(cs); } catch (Exception e) { log.error(e.getMessage()); throw new DAOException(e.getMessage()); } }
From source file:dbutils.DbUtilsTemplate.java
/** * ??//w w w .j av a2s . c o m * * @param sql sql? * @param params ? * @return (?, -1) * @throws SQLException */ public long insert(String sql, Object[] params) throws SQLException { long result = -1L; Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = dataSource.getConnection(); stmt = conn.prepareStatement(sql); fillStatement(stmt, params); int affectCount = stmt.executeUpdate(); if (affectCount <= 0) return -1L; rs = stmt.getGeneratedKeys(); result = rs.next() ? rs.getLong(1) : -1; conn.commit(); } catch (SQLException e) { LOG.error("Error occured while attempting to insert data", e); if (conn != null) { conn.rollback(); } throw e; } finally { DbUtils.closeQuietly(conn, stmt, rs); } return result; }
From source file:com.engine.QuoteServletData.java
/** * * @param id - zTblOpp4.ID/*w w w. j av a2s . c o m*/ */ public QuoteServletData(String id) { items = new ArrayList<>(); 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 zTblOpp4 WHERE z3ID = ?"); stmt.setString(1, id); rs = stmt.executeQuery(); originZip = ""; country = ""; while (rs.next()) { originZip = rs.getString("ZIP"); country = rs.getString("COUNTRY"); } productClass = ""; */ System.out.println("tblOppMngr4.z4ID = " + id); String z3Id = ""; String z1Id = ""; stmt = con.prepareStatement("SELECT * FROM tblOppMngr4 WHERE z4ID = ?"); stmt.setString(1, id); rs = stmt.executeQuery(); while (rs.next()) { z3Id = rs.getString("z3ID"); z1Id = rs.getString("z1ID"); originZip = rs.getString("SFZIP"); } stmt = con.prepareStatement("SELECT * FROM tblOppMngr1 WHERE z1ID = ?"); stmt.setString(1, z1Id); rs = stmt.executeQuery(); while (rs.next()) { destinationZip = rs.getString("STZIP"); } //https://www.zipcodeapi.com/rest/NGaSQLHFvsGMP1rbcB7RJbb67rX5JAqxgAq6m7LSAsEpt5BFGIxqUIw29u7S4xqk/distance.json/10801/08854/mile //get the warehouse and compare the zip codes and find out which one is the closest and use that //for generating the quote that is crossdock //while the regular quote should be generated as well //the warehouse should also be checked to be able to handle the hazmat material if the matrial is hazmat //when it is a crossdock the notify prior to delivery fee should be added //if it is a direct order do not notify prior to delivery stmt = con.prepareStatement("SELECT * FROM tblOppMngr3 WHERE z1ID = ?"); stmt.setString(1, z1Id); rs = stmt.executeQuery(); while (rs.next()) { if (rs.getString("HZMT") != null && !rs.getString("HZMT").isEmpty()) { if (rs.getString("HZMT").startsWith("Haz")) { productClass = "85"; } if (rs.getString("HZMT").startsWith("Non")) { productClass = "65"; } } else { productClass = "65"; } productUm = rs.getString("UNITMEASURE") == null ? "LB" : rs.getString("UNITMEASURE"); productWeight = (rs.getDouble("QUANT") == 0 ? 1 : rs.getDouble("QUANT")) * (rs.getDouble("MEASURE") == 0 ? 1 : rs.getDouble("MEASURE")); Item it = new Item(rs.getString("ID"), productClass, String.valueOf(productWeight)); items.add(it); } stmt = con.prepareStatement("SELECT * FROM tblUMMultiplier WHERE UM = ?"); stmt.setString(1, productUm); rs = stmt.executeQuery(); while (rs.next()) { productWeight = productWeight * rs.getFloat("LBmultplier"); } stmt = con.prepareStatement("SELECT * FROM tblWarehouse WHERE Active = 1"); ConcurrentMap<String, String> warehouseZip = new ConcurrentHashMap<>(); rs = stmt.executeQuery(); while (rs.next()) { if (rs.getString("WHZIP") != null) { warehouseZip.put(rs.getString("WHZIP"), ""); } } for (String key : warehouseZip.keySet()) { if (key.matches("[0-9]+")) { URL url = new URL("http://10.1.1.58:8080/zip/distance?zip1=" + originZip + "&zip2=" + key); URLConnection conn = url.openConnection(); BufferedReader br = new BufferedReader(new InputStreamReader(conn.getInputStream())); String inputLine; while ((inputLine = br.readLine()) != null) { warehouseZip.put(key, inputLine.substring(1, inputLine.length() - 1)); } br.close(); } } double min = 10000.0; for (String key : warehouseZip.keySet()) { if (!warehouseZip.get(key).isEmpty() && warehouseZip.get(key) != null) { if (Double.valueOf(warehouseZip.get(key)) < min) { min = Double.valueOf(warehouseZip.get(key)); whZip = key; } } } con.close(); } catch (IOException | SQLException | PropertyVetoException ex) { Logger.getLogger(QuoteServletData.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 Auction getAuction(final int uid) { Auction obj = null;// w ww.jav a 2 s.c om Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETAUCTION (?)}"); stmt.setInt(1, uid); rs = stmt.executeQuery(); if (rs.next()) { AuctionBuilder builder = Auction.newBuilder().setUid(rs.getInt("UID")) .setName(rs.getString("NAME")); Date startdate = rs.getDate("STARTDATE"); if (startdate != null) { builder.setStartDate(startdate.getTime()); } Date enddate = rs.getDate("ENDDATE"); if (enddate != null) { builder.setEndDate(enddate.getTime()); } builder.setLogoUrl(rs.getString("LOGOURL")); builder.setColor(rs.getString("COLOR")); obj = builder.build(); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("AUCTION [method:{} result:{}]", new Object[] { "get", obj != null ? obj.toString() : "[error]" }); } return obj; }
From source file:com.organization.OrganizationData.java
public OrganizationData(String startdate, String enddate, String id, String contextPath) { java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; try {/*w w w .j av a2 s.c o m*/ con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement("SELECT * FROM LeadOrganization WHERE Id = ?"); stmt.setString(1, id); rs = stmt.executeQuery(); while (rs.next()) { orgName = rs.getString("Name"); address = rs.getString("Address"); city = rs.getString("City"); state = rs.getString("StateProv"); zip = rs.getString("PostalCode"); country = rs.getString("Country"); } stmt = con.prepareStatement( "SELECT DISTINCT TOP 30 leadSession.pageTitle, leadSession.location, COUNT(leadSession.pageTitle) as c FROM leadSession, LeadRemoteAddress, LeadOrganization WHERE leadSession.timeIn > CONVERT(date, ?) AND leadSession.timeIn < CONVERT(date, ?) AND LeadRemoteAddress.ip = leadSession.remoteAddress AND LeadRemoteAddress.LeadOrganization_uid = LeadOrganization.id AND LeadOrganization.Id = ? GROUP BY leadSession.pageTitle, leadSession.location HAVING COUNT(leadSession.pageTitle) > 0 ORDER BY c DESC"); stmt.setString(1, startdate); stmt.setString(2, enddate); stmt.setString(3, id); rs = stmt.executeQuery(); pagesVisited = ""; while (rs.next()) { pagesVisited += "<a style=\"display: block; padding-bottom: 5px;\" href=\"" + rs.getString("location") + "\" target=\"_blank\">" + ((rs.getString("pageTitle").length() < 70) ? rs.getString("pageTitle") : (rs.getString("pageTitle").substring(0, 70) + "...")) + " <span style=\"color: black; font-size: 14pt;\"> (" + rs.getString("c") + ")</span>" + "</a>"; } stmt = con.prepareStatement( "SELECT DISTINCT TOP 30 leadSession.remoteAddress FROM leadSession, LeadRemoteAddress, LeadOrganization, LeadContact WHERE leadSession.timeIn > CONVERT(date, ?) AND leadSession.timeIn < CONVERT(date, ?) AND LeadRemoteAddress.ip = leadSession.remoteAddress AND LeadRemoteAddress.LeadOrganization_uid = LeadOrganization.id AND LeadOrganization.Id = ?"); stmt.setString(1, startdate); stmt.setString(2, enddate); stmt.setString(3, id); rs = stmt.executeQuery(); remoteUsers = ""; while (rs.next()) { remoteUsers += "<span style=\"display: block;\"><a href=\"" + contextPath + "/rem?startdate=" + startdate + "&enddate=" + enddate + "&ip=" + rs.getString("remoteAddress") + "\">" + rs.getString("remoteAddress") + "</a></span>"; } stmt = con.prepareStatement( "SELECT DISTINCT TOP 5 LeadContact.Name, LeadContact.Email, LeadContact.WebSite, LeadContact.RemoteAddress, LeadContact.Address, LeadContact.Phone, LeadContact.BusinessName FROM leadSession, LeadRemoteAddress, LeadOrganization, LeadContact WHERE leadSession.timeIn > CONVERT(date, ?) AND leadSession.timeIn < CONVERT(date, ?) AND LeadRemoteAddress.ip = leadSession.remoteAddress AND LeadRemoteAddress.LeadOrganization_uid = LeadOrganization.id AND LeadOrganization.Id = ? AND LeadContact.RemoteAddress = leadSession.remoteAddress"); stmt.setString(1, startdate); stmt.setString(2, enddate); stmt.setString(3, id); rs = stmt.executeQuery(); contactInformation = ""; while (rs.next()) { contactInformation += "<div style=\"display: block;\"><span>" + rs.getString("Name") + " </span><span>" + rs.getString("Email") + "</span><span style=\"display: block;\">" + rs.getString("Phone") + "</span><span style=\"display: block;\">" + rs.getString("WebSite") + "</span><span style=\"display: block;\">" + rs.getString("BusinessName") + "</span><span style=\"display: block;\">" + rs.getString("Address") + "</span><span style=\"display: block;\">" + rs.getString("RemoteAddress") + "</span></div>"; } if (contactInformation.isEmpty()) { contactInformation = "none"; } stmt = con.prepareStatement( "SELECT DISTINCT TOP 30 leadSession.referer, COUNT(leadSession.referer) as c FROM leadSession, LeadRemoteAddress, LeadOrganization WHERE leadSession.timeIn > CONVERT(date, ?) AND leadSession.timeIn < CONVERT(date, ?) AND LeadRemoteAddress.ip = leadSession.remoteAddress AND LeadRemoteAddress.LeadOrganization_uid = LeadOrganization.id AND LeadOrganization.Id = ? AND leadSession.referer != '' GROUP BY leadSession.referer HAVING COUNT(leadSession.referer) > 0 ORDER BY c DESC"); stmt.setString(1, startdate); stmt.setString(2, enddate); stmt.setString(3, id); rs = stmt.executeQuery(); referers = ""; Map<String, Integer> refs = new HashMap<>(); 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")); } //referers += "<span style=\"display: block; padding-bottom: 5px;\">"+rs.getString("referer").split("/")[2]+" ("+rs.getString("c")+")"+"</span>"; } for (String key : refs.keySet()) { referers += "<span style=\"display: block; padding-bottom: 5px;\">" + key + " (" + refs.get(key) + ")" + "</span>"; } con.close(); } catch (SQLException | PropertyVetoException | IOException ex) { Logger.getLogger(OrganizationData.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(con, stmt, rs); } }
From source file:com.index.IndexData.java
public IndexData(String startdate, String enddate, String table, String contextPath) { java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; try {/*from w w w . j a v a 2 s .c o m*/ con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement( "SELECT COUNT(DISTINCT remoteAddress) as remoteAdd, COUNT(DISTINCT sessionId) as sess FROM " + table + " WHERE timeIn > CONVERT(date, ?) AND timeIn < CONVERT(date, ?)"); stmt.setString(1, startdate); stmt.setString(2, enddate); rs = stmt.executeQuery(); totalAddressCount = ""; totalSessionCount = ""; while (rs.next()) { totalAddressCount = rs.getString("remoteAdd"); totalSessionCount = rs.getString("sess"); } stmt = con.prepareStatement("SELECT TOP 12 LeadOrganization.Id, LeadOrganization.Name, COUNT(DISTINCT " + table + ".remoteAddress) as c, COUNT(" + table + ".remoteAddress) as p FROM LeadOrganization, " + table + ", LeadRemoteAddress WHERE " + table + ".remoteAddress = LeadRemoteAddress.ip AND LeadRemoteAddress.LeadOrganization_uid = LeadOrganization.id AND LeadRemoteAddress.prefered = 1 AND LeadOrganization.Address != '' AND " + table + ".timeIn > CONVERT(date, ?) AND " + table + ".timeIn < CONVERT(date, ?) GROUP BY LeadOrganization.Name, LeadOrganization.Id HAVING COUNT(" + table + ".remoteAddress) > 0"); stmt.setString(1, startdate); stmt.setString(2, enddate); rs = stmt.executeQuery(); companyLinks = ""; while (rs.next()) { //companyLinks += "<a style=\"display: block; font-size: 12pt; padding-bottom: 5px;\" href=\"" + contextPath + "/org?id=" + rs.getString("Id") + "&startdate=" + startdate + "&enddate=" + enddate + "\">" + rs.getString("Name") + " <span style=\"color: black;\">(<small>visitors:</small> " + rs.getString("c") + ", <small>page views:</small> " + rs.getString("p") + ")</span>" + "</a>"; companyLinks += "<a href=\"" + contextPath + "/org?id=" + rs.getString("id") + "&startdate=" + startdate + "&enddate=" + enddate + "\" class=\"list-group-item list-group-item-action\"><h5 class=\"list-group-item-heading\"><b><i class=\"fa fa-address-card fa-fw\"></i> " + rs.getString("Name") + "</b></h5><p class=\"list-group-item-text\">visitors: " + rs.getString("c") + " page views: " + rs.getString("p") + "</p></a>\n"; } stmt = con.prepareStatement("SELECT TOP 18 * FROM LeadContact WHERE RemoteAddress IN (SELECT " + table + ".remoteAddress FROM " + table + " WHERE " + table + ".timeIn > CONVERT(date, ?) AND " + table + ".timeIn < CONVERT(date, ?)) ORDER BY id DESC"); stmt.setString(1, startdate); stmt.setString(2, enddate); rs = stmt.executeQuery(); contacts = ""; while (rs.next()) { //contacts += "<a href=\""+contextPath+"/contact?id="+rs.getString("id")+"\" class=\"list-group-item list-group-item-action\"><h5 class=\"list-group-item-heading\"><b>"+rs.getString("Name")+"</b></h5><p class=\"list-group-item-text\"><a href=\"mailto:"+rs.getString("Email")+"\">"+rs.getString("Email")+"</a> <a href=\"tel:"+rs.getString("Phone")+"\">"+rs.getString("Phone")+"</a><span style=\"display: block;\"><a href=\""+rs.getString("WebSite")+"\">"+rs.getString("BusinessName")+"</a></span><span style=\"display: block;\">"+rs.getString("Address")+"</span></p></a>\n"; contacts += "<a class=\"list-group-item list-group-item-action\" href=\"" + contextPath + "/contact?id=" + rs.getString("id") + "\">" + "<h5 class=\"list-group-item-heading\"><b style=\"font-size: 12pt;\"><i class=\"fa fa-address-card-o fa-fw\"></i> " + (rs.getString("Name") == null ? "" : rs.getString("Name")) + "</b>" + "<span style=\"padding-left: 20px; font-size: 12pt;\">" + (rs.getString("Phone") == null || rs.getString("Phone").isEmpty() ? "" : "tel.: " + rs.getString("Phone")) + "</span></h5>" + "<p class=\"list-group-item-text\">" + "<span style=\"display: block; padding-bottom: 5px;\">" + (rs.getString("Email") == null ? "" : rs.getString("Email")) + "</span>" + "<span style=\"display: block;\">" + (rs.getString("BusinessName") == null ? "" : rs.getString("BusinessName")) + "<span style=\"padding-left: 20px;\">" + (rs.getString("WebSite") == null ? "" : rs.getString("WebSite")) + "</span></span><span style=\"display: block; font-size: 10pt; padding-top: 8px;\"><i>" + (rs.getString("Address") == null ? "" : rs.getString("Address")) + "</i></span></p></a>"; } stmt = con.prepareStatement("SELECT TOP 8 COUNT(pageTitle) as c, pageTitle, location FROM " + table + " WHERE " + table + ".timeIn > CONVERT(date, ?) AND " + table + ".timeIn < CONVERT(date, ?) GROUP BY pageTitle, location ORDER BY COUNT(pageTitle) DESC"); stmt.setString(1, startdate); stmt.setString(2, enddate); rs = stmt.executeQuery(); mostlyVisited = ""; while (rs.next()) { mostlyVisited += " <a href=\"" + rs.getString("location") + "\" class=\"list-group-item\">\n" + " <i class=\"fa fa-link fa-fw\"></i> " + (rs.getString("pageTitle").length() > 70 ? rs.getString("pageTitle").substring(0, 70) + "..." : rs.getString("pageTitle")) + "\n" + " <span class=\"pull-right text-muted small\"><em>" + rs.getString("c") + " visits</em>\n" + " </span>\n" + " </a>"; } con.close(); } catch (SQLException | PropertyVetoException | IOException ex) { Logger.getLogger(IndexData.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 List<Auction> getAuctions(final int start, final int length, final String sort, final String dir) { List<Auction> objs = Lists.newArrayList(); Connection conn = null;//from w w w . j a v a 2 s. com CallableStatement stmt = null; ResultSet rs = null; try { conn = _dataSource.getConnection(); stmt = conn.prepareCall("{call SP_GETAUCTIONS (?,?,?,?)}"); stmt.setInt(1, start); stmt.setInt(2, length); stmt.setString(3, sort); stmt.setString(4, dir); rs = stmt.executeQuery(); while (rs.next()) { AuctionBuilder builder = Auction.newBuilder().setUid(rs.getInt("UID")) .setName(rs.getString("NAME")); Date startdate = rs.getDate("STARTDATE"); if (startdate != null) { builder.setStartDate(startdate.getTime()); } Date enddate = rs.getDate("ENDDATE"); if (enddate != null) { builder.setEndDate(enddate.getTime()); } builder.setLogoUrl(rs.getString("LOGOURL")); builder.setColor(rs.getString("COLOR")); objs.add(builder.build()); } } catch (SQLException e) { LOG.error(Throwables.getStackTraceAsString(e)); } finally { DbUtils.closeQuietly(conn, stmt, rs); } if (LOG.isDebugEnabled()) { LOG.debug("AUCTION [method:{} result:{}]", new Object[] { "get", objs.size() }); } return ImmutableList.copyOf(objs); }
From source file:com.contact.ContactData.java
public ContactData(String id, String contextPath) { java.sql.Connection con = null; java.sql.PreparedStatement stmt = null; java.sql.ResultSet rs = null; try {/*from www . ja v a 2s . co m*/ con = ControlPanelPool.getInstance().getConnection(); stmt = con.prepareStatement("SELECT * FROM leadContact WHERE Id = ?"); stmt.setString(1, id); rs = stmt.executeQuery(); while (rs.next()) { name = rs.getString("Name"); email = rs.getString("Email"); website = rs.getString("WebSite"); remoteAddress = rs.getString("RemoteAddress"); address = rs.getString("Address"); phone = rs.getString("Phone"); businessName = rs.getString("BusinessName"); source = rs.getString("Source"); } stmt = con.prepareStatement( "SELECT * FROM leadContact WHERE (BusinessName = ? AND BusinessName != '' AND BusinessName != 'NA' AND BusinessName != 'N/A' AND BusinessName != '-' AND Id != ? AND Email != ?) OR (WebSite = ? AND WebSite != '' AND WebSite != '-' AND WebSite != 'NA' AND WebSite != 'N/A' AND Id != ? AND Email != ?)"); stmt.setString(1, businessName); stmt.setString(2, id); stmt.setString(3, email); stmt.setString(4, website); stmt.setString(5, id); stmt.setString(6, email); rs = stmt.executeQuery(); businessCollegues = ""; while (rs.next()) { businessCollegues += "<a href=\"" + contextPath + "/contact?id=" + rs.getString("Id") + "\" class=\"list-group-item\">" + rs.getString("Name") + "</a>\n"; } businessNameHeader = ""; if (businessName != null && !businessName.isEmpty()) { businessNameHeader = businessName + " Employees"; } relatedIp = new ArrayList<>(); if (email != null && !email.isEmpty()) { stmt = con.prepareStatement( "SELECT RemoteAddress FROM LeadContact WHERE Id IN (SELECT Id FROM LeadContact WHERE Email = ?)"); stmt.setString(1, email); rs = stmt.executeQuery(); while (rs.next()) { relatedIp.add(rs.getString("RemoteAddress")); } } pageViews = new HashMap<>(); totalPagesVisited = "0"; totalClicks = "0"; String relatedIps = ""; if (relatedIp != null && !relatedIp.isEmpty()) { for (int i = 0; i < relatedIp.size(); i++) { stmt = con.prepareStatement( "SELECT DISTINCT TOP 15 leadSession.pageTitle, leadSession.location, COUNT(*) as pageViews FROM leadSession WHERE remoteAddress = ? GROUP BY leadSession.pageTitle, leadSession.location ORDER BY COUNT(*) DESC"); stmt.setString(1, relatedIp.get(i)); rs = stmt.executeQuery(); while (rs.next()) { if (pageViews.containsKey(rs.getString("pageTitle"))) { pageViews.get(rs.getString( "pageTitle")).visitCount = (pageViews.get(rs.getString("pageTitle")).visitCount + rs.getInt("pageViews")); } else { pageViews.put(rs.getString("pageTitle"), new PageNamePair(rs.getString("pageTitle"), rs.getString("location"), rs.getInt("pageViews"))); } } relatedIps += relatedIp.get(i) + "','"; } } if (relatedIps != null && !relatedIps.isEmpty()) { stmt = con.prepareStatement( "SELECT COUNT(*) AS pCount, COUNT(DISTINCT pageTitle) as dpCount FROM leadSession WHERE remoteAddress IN ('" + relatedIps + "')"); rs = stmt.executeQuery(); while (rs.next()) { totalPagesVisited = rs.getString("dpCount"); totalClicks = rs.getString("pCount"); } } stmt = con.prepareStatement( "SELECT LeadOrganization.Id, LeadOrganization.Name, LeadOrganization.Address, LeadOrganization.City, LeadOrganization.StateProv, LeadOrganization.PostalCode, LeadOrganization.Country FROM LeadRemoteAddress, LeadOrganization WHERE ip = ? AND prefered = 1 AND LeadRemoteAddress.LeadOrganization_uid = LeadOrganization.Id"); stmt.setString(1, remoteAddress); rs = stmt.executeQuery(); networkOwner = ""; geoLocation = ""; while (rs.next()) { networkOwner = rs.getString("Name"); Locale l = new Locale("", rs.getString("Country")); networkOwnerId = rs.getString("Id"); geoLocation = rs.getString("Address") == null || rs.getString("Address").isEmpty() ? "" : rs.getString("Address") + ", "; geoLocation += rs.getString("City") == null || rs.getString("City").isEmpty() ? "" : rs.getString("City") + ", "; geoLocation += rs.getString("City") == null || rs.getString("StateProv").isEmpty() ? "" : rs.getString("StateProv") + " "; geoLocation += rs.getString("City") == null || rs.getString("PostalCode").isEmpty() ? "" : rs.getString("PostalCode") + ", "; geoLocation += l.getDisplayCountry(); } detailedPageViews = ""; stmt = con.prepareStatement("SELECT location, timeIn FROM leadSession WHERE remoteAddress IN ('" + relatedIps + "') ORDER BY Id DESC"); rs = stmt.executeQuery(); while (rs.next()) { detailedPageViews += "<a target=\"_blank\" href=\"" + rs.getString("location") + "\" class=\"list-group-item\">\n" + " <i class=\"fa fa-link fa-fw\"></i> " + rs.getString("location") + "\n" + " <span class=\"pull-right text-muted small\"><em>" + rs.getString("timeIn") + "</em>\n" + " </span>\n" + " </a>"; } con.close(); } catch (SQLException | PropertyVetoException | IOException ex) { Logger.getLogger(ContactData.class.getName()).log(Level.SEVERE, null, ex); } finally { DbUtils.closeQuietly(con, stmt, rs); } }
From source file:com.zionex.t3sinc.util.db.SincDatabaseUtility.java
public void close(Connection connection, Statement statement, ResultSet resultSet) { DbUtils.closeQuietly(connection, statement, resultSet); }