Java tutorial
// //"This sample program is provided AS IS and may be used, executed, copied and modified without royalty payment by customer (a) for its own //instruction and study, (b) in order to develop applications designed to run with an IBM WebSphere product, either for customer's own internal use //or for redistribution by customer, as part of such an application, in customer's own products. " // //Product 5630-A36, (C) COPYRIGHT International Business Machines Corp., 2001,2002 //All Rights Reserved * Licensed Materials - Property of IBM // package com.emc.plants.service.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Vector; import javax.naming.NamingException; import javax.sql.DataSource; import org.springframework.stereotype.Repository; import com.emc.plants.service.interfaces.ReportGenerator; import com.emc.plants.utils.Report; import com.emc.plants.utils.ReportFormat; import com.emc.plants.utils.Util; /** * ReportGeneratorBean is the implementation class for the {@link ReportGenerator} stateless session * EJB. ReportGeneratorBean implements each of the business methods in the <code>ReportGenerator</code> * EJB remote interface and each of the EJB lifecycle methods in the javax.ejb.SessionBean * interface. * * @see Report * @see ReportFormat * @see ReportGenerator */ //@Stateless (name="ReportGenerator") @Repository public class ReportGeneratorBean implements ReportGenerator { //ReportGenerator has not been updated to use PersistenceContext due to complex sql // @PersistenceContext(unitName="PBW") // EntityManager em; /** * Run the report to get the top selling items for a range of dates. * * @param startdate Start of date range. * @param enddate End of date range. * @param quantity Number of items to return in report. * @param reportFormat - Report format information. * @return Report containing results. */ @SuppressWarnings("unchecked") public Report getTopSellersForDates(java.util.Date startdate, java.util.Date enddate, int quantity, ReportFormat reportFormat) { Report report = null; Connection conn = null; ResultSet results = null; PreparedStatement sqlStatement = null; try { // Establish connection to datasource. String orderItemsTableName = "ORDERITEM"; DataSource ds = (DataSource) Util.getInitialContext().lookup("jdbc/PlantsByWebSphereDataSource"); conn = ds.getConnection(); // Set sort order of ascending or descending. String sortOrder; if (reportFormat.isAscending()) sortOrder = "ASC"; else sortOrder = "DESC"; // Set up where by clause. String startDateString = Long.toString(startdate.getTime()); if (startDateString.length() < 14) { StringBuffer sb = new StringBuffer(Util.ZERO_14); sb.replace((14 - startDateString.length()), 14, startDateString); startDateString = sb.toString(); } String endDateString = Long.toString(enddate.getTime()); if (endDateString.length() < 14) { StringBuffer sb = new StringBuffer(Util.ZERO_14); sb.replace((14 - endDateString.length()), 14, endDateString); endDateString = sb.toString(); } String whereString = " WHERE sellDate BETWEEN '" + startDateString + "' AND '" + endDateString + "' "; // Create SQL statement. String sqlString = "SELECT inventoryID, name, category," + " SUM(quantity * (price - cost)) as PROFIT FROM " + orderItemsTableName + whereString + " GROUP BY inventoryID, name, category ORDER BY PROFIT " + sortOrder + ", name"; Util.debug("sqlstring=" + sqlString); sqlStatement = conn.prepareStatement(sqlString); results = sqlStatement.executeQuery(); int i; // Initialize vectors to store data in. Vector[] vecs = new Vector[4]; for (i = 0; i < vecs.length; i++) { vecs[i] = new Vector(); } // Sift thru results. int count = 0; while ((results.next()) && (count < quantity)) { count++; i = 1; vecs[0].addElement(results.getString(i++)); vecs[1].addElement(results.getString(i++)); vecs[2].addElement(new Integer(results.getInt(i++))); vecs[3].addElement(new Float(results.getFloat(i++))); } // Create report. report = new Report(); report.setReportFieldByRow(Report.ORDER_INVENTORY_ID, vecs[0]); report.setReportFieldByRow(Report.ORDER_INVENTORY_NAME, vecs[1]); report.setReportFieldByRow(Report.ORDER_INVENTORY_CATEGORY, vecs[2]); report.setReportFieldByRow(Report.PROFITS, vecs[3]); } catch (Exception e) { Util.debug("exception in ReportGeneratorBean:getTopSellersForDates. " + e); e.printStackTrace(); } finally { // Clean up. try { if (results != null) results.close(); } catch (Exception ignore) { } try { if (sqlStatement != null) sqlStatement.close(); } catch (Exception ignore) { } // Close Connection. try { if (conn != null) conn.close(); } catch (Exception ignore) { } } return report; } /** * Run the report to get the top zip codes for a range of dates. * * @param startdate Start of date range. * @param enddate End of date range. * @param quantity Number of items to return in report. * @param reportFormat - Report format information. * @return Report containing results. */ @SuppressWarnings("unchecked") public Report getTopSellingZipsForDates(java.util.Date startdate, java.util.Date enddate, int quantity, ReportFormat reportFormat) { Report report = null; Connection conn = null; ResultSet results = null; PreparedStatement sqlStatement = null; try { // Establish connection to datasource. String orderInfoTableName = "ORDER1"; DataSource ds = (DataSource) Util.getInitialContext().lookup("jdbc/PlantsByWebSphereDataSource"); conn = ds.getConnection(); // Set sort order of ascending or descending. String sortOrder; if (reportFormat.isAscending()) sortOrder = "ASC"; else sortOrder = "DESC"; // Set up where by clause. String startDateString = Long.toString(startdate.getTime()); if (startDateString.length() < 14) { StringBuffer sb = new StringBuffer(Util.ZERO_14); sb.replace((14 - startDateString.length()), 14, startDateString); startDateString = sb.toString(); } String endDateString = Long.toString(enddate.getTime()); if (endDateString.length() < 14) { StringBuffer sb = new StringBuffer(Util.ZERO_14); sb.replace((14 - endDateString.length()), 14, endDateString); endDateString = sb.toString(); } String whereString = " WHERE sellDate BETWEEN '" + startDateString + "' AND '" + endDateString + "' "; // Create SQL statement. String sqlString = "SELECT billZip, SUM(profit) AS PROFITS FROM " + orderInfoTableName + whereString + " GROUP BY billZip ORDER BY PROFITS " + sortOrder + ", billZip"; Util.debug("sqlstring=" + sqlString + "="); sqlStatement = conn.prepareStatement(sqlString); results = sqlStatement.executeQuery(); int i; // Initialize vectors to store data in. Vector[] vecs = new Vector[2]; for (i = 0; i < vecs.length; i++) { vecs[i] = new Vector(); } // Sift thru results. int count = 0; while ((results.next()) && (count < quantity)) { count++; i = 1; vecs[0].addElement(results.getString(i++)); vecs[1].addElement(new Float(results.getFloat(i++))); } // Create report. report = new Report(); report.setReportFieldByRow(Report.ORDER_BILLZIP, vecs[0]); report.setReportFieldByRow(Report.PROFITS, vecs[1]); } catch (NamingException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { // Clean up. try { if (results != null) results.close(); } catch (Exception ignore) { } try { if (sqlStatement != null) sqlStatement.close(); } catch (Exception ignore) { } // Close Connection. try { if (conn != null) conn.close(); } catch (Exception ignore) { } } return report; } }