1 /*
  2  * To change this template, choose Tools | Templates
  3  * and open the template in the editor.
  4  */
  5 
  6 package db_adapter;
  7 
  8 /**
  9  *
 10  * @author sameldin
 11  */
 12 import java.io.*;
 13 import java.sql.Connection;
 14 import java.sql.DriverManager;
 15 import java.sql.PreparedStatement;
 16 import java.sql.ResultSet;
 17 import java.sql.SQLException;
 18 import java.util.ArrayList;
 19 import java.util.Hashtable;
 20 import java.util.MissingResourceException;
 21 import java.util.ResourceBundle;
 22 import java.util.Calendar;
 23 import java.util.Date;
 24 import java.text.SimpleDateFormat;
 25 
 26 
 27 //import org.apache.log4j.Logger;
 28 //import org.apache.log4j.PropertyConfigurator;
 29 
 30 import utils.*;
 31 import constants.*;
 32 
 33 public class MathOperations
 34 {
 35     Connection  localConnection = null;
 36 
 37     public MathOperations()
 38     {
 39         BD_Connector localBD_Connector = new BD_Connector();
 40         localConnection = localBD_Connector.getConnection();
 41     }
 42      /*
 43       *
 44       */
 45      public int selectUsingMathFunctions(String customerTable)
 46      {
 47         //  ========================================================
 48         //  SELECT  COUNT(*)  AS numberOfItems,
 49         //          MIN(productPrice) AS minProductPrice
 50         //          MAX(productPrice) AS maxProductPrice
 51         //          AVE(productPrice) AS averageProductPrice
 52         //          AVE(DISTINCT productPrice) AS distintAverageProductPrice
 53         //          SUM(productPrice * numberOfItems) AS TotalCostProductPrice
 54         //  FROM customerTable
 55         //      customerId = '1234';
 56          if(null == localConnection)
 57             return(Constants.ERROR_RUN);
 58          PreparedStatement  localPreparedStatement  = null;
 59          ResultSet          localResultSet          = null;
 60          //===================================
 61          try
 62          {
 63             String qryString    = "";
 64             String localCustomerTable       = DatabaseKeysPropertyManager.getParameter(customerTable);
 65             //=============================
 66             qryString   =       "SELECT COUNT(*)  AS numberOfItems, "
 67                             + " MIN(productPrice) AS minProductPrice "
 68                             + " MAX(productPrice) AS maxProductPrice "
 69                             + " AVE(productPrice) AS averageProductPrice "
 70                             + " AVE(DISTINCT productPrice) AS distintAverageProductPrice "
 71                             + " SUM(productPrice * numberOfItems) AS TotalCostProductPrice "
 72                             + " FROM  "
 73                             + localCustomerTable
 74                             + " customerId = '1234';";
 75                 //===============================
 76                 localPreparedStatement = localConnection.prepareStatement(qryString);
 77                 localResultSet = localPreparedStatement.executeQuery();
 78                 ArrayList localXYZArrayList = new ArrayList();
 79                 //===========================================
 80                 while(localResultSet.next())
 81                 {
 82                     // Start retrieving your fields
 83                     long localNumberOfItems = localResultSet.getLong("numberOfItems");
 84                     long localMinProductPrice   = localResultSet.getLong("minProductPrice");
 85                     long localMaxProductPrice    = localResultSet.getLong("maxProductPrice");
 86                     long localAverageProductPrice   = localResultSet.getLong("averageProductPrice");
 87                     // ...
 88                 }
 89 
 90             localPreparedStatement = localConnection.prepareStatement(qryString);
 91             localResultSet = localPreparedStatement.executeQuery();
 92 
 93          }
 94          catch(MissingResourceException eMissingResourceException)
 95          {
 96             eMissingResourceException.printStackTrace();
 97             return(Constants.ERROR_RUN);
 98          }
 99          catch(SQLException eSQLException)
100          {
101             eSQLException.printStackTrace();
102             return(Constants.ERROR_RUN);
103          }
104         return(Constants.NORMAL_RUN);
105      }
106 
107 }
108 
109