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 //import org.apache.log4j.Logger;
 27 //import org.apache.log4j.PropertyConfigurator;
 28 
 29 import utils.*;
 30 import constants.*;
 31 
 32 public class CASE_Expression
 33 {
 34     Connection  localConnection = null;
 35 
 36     public CASE_Expression()
 37     {
 38         BD_Connector localBD_Connector = new BD_Connector();
 39         localConnection = localBD_Connector.getConnection();
 40     }
 41      /*
 42       *
 43       */
 44      public int selectUsingCASE_Expression(String productionTable)
 45      {
 46         //  SELECT   ProductNumber, Name, 'Price Range' =
 47         //        CASE
 48         //           WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
 49         //           WHEN ListPrice < 50 THEN 'Under $50'
 50         //           WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
 51         //           WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
 52         //           ELSE 'Over $1000'
 53         //        END
 54         //  FROM Production.Product
 55         //          ORDER BY ProductNumber ;
 56 
 57          if(null == localConnection)
 58             return(Constants.ERROR_RUN);
 59          PreparedStatement  localPreparedStatement  = null;
 60          ResultSet          localResultSet          = null;
 61          //===================================
 62          try
 63          {
 64             String qryString    = "";
 65             String localProductionTable       = DatabaseKeysPropertyManager.getParameter(productionTable);
 66             //=============================
 67             qryString   =       " SELECT ProductNumber, Name, 'Price Range' = "
 68                             + " CASE "
 69                             + "     WHEN ListPrice =  0 THEN 'Mfg item - not for resale'"
 70                             + "     WHEN ListPrice < 50 THEN 'Under $50'"
 71                             + "     WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'"
 72                             + "     WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'"
 73                             + "     ELSE 'Over $1000'"
 74                             + " END "
 75                             + " FROM  "
 76                             + localProductionTable
 77                             + " ORDER BY ProductNumber";
 78                 //===============================
 79                 localPreparedStatement = localConnection.prepareStatement(qryString);
 80                 localResultSet = localPreparedStatement.executeQuery();
 81                 ArrayList localXYZArrayList = new ArrayList();
 82                 //===========================================
 83                 while(localResultSet.next())
 84                 {
 85                     // Start retrieving your fields
 86                     String xStringValue = localResultSet.getString("Location_Number");
 87                     long   xLongValue   = localResultSet.getLong("long lll");
 88                     int    xIntValue    = localResultSet.getInt("int iii");
 89                     int    xIntValue2   = localResultSet.getInt(4);
 90                 }
 91 
 92             localPreparedStatement = localConnection.prepareStatement(qryString);
 93             localResultSet = localPreparedStatement.executeQuery();
 94 
 95          }
 96          catch(MissingResourceException eMissingResourceException)
 97          {
 98             eMissingResourceException.printStackTrace();
 99             return(Constants.ERROR_RUN);
100          }
101          catch(SQLException eSQLException)
102          {
103             eSQLException.printStackTrace();
104             return(Constants.ERROR_RUN);
105          }
106         return(Constants.NORMAL_RUN);
107      }
108 
109 }
110 
111