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 
 23 //import org.apache.log4j.Logger;
 24 //import org.apache.log4j.PropertyConfigurator;
 25 
 26 import utils.*;
 27 import constants.*;
 28 
 29 public class If_exists
 30 {
 31     Connection  localConnection = null;
 32 
 33     public If_exists()
 34     {
 35         BD_Connector localBD_Connector = new BD_Connector();
 36         localConnection = localBD_Connector.getConnection();
 37     }
 38     /*
 39      *
 40      */
 41      public int updateUsing_EXISTS(String passedTableName)
 42      {
 43         if(null == localConnection)
 44             return(Constants.ERROR_RUN);
 45         PreparedStatement   localPreparedStatement      = null;
 46         //==========================
 47         try
 48         {
 49             // UPDATE TABLE_1
 50             // set
 51             // NAME = 'Eldin', IMAGE_NAME = 'BB', ITEM_NUMBER = '00002', ITEM_ID = '123',
 52             // ITEM_DESCRIPTION = 'Sam List Description 123', DISTRIBUTED_BY = 'SamEldin',
 53             // DISTRIBUTED_DATE = 1080414, DISTRIBUTED_TIME = 123619
 54             // WHERE
 55             // NAME = 'Eldin' and IMAGE_NAME = 'BB'
 56             // and
 57             // EXISTS (SELECT * FROM TABLE_1  WHERE NAME = 'Eldin' and ITEM_NUMBER = '00002');
 58                 String qryString        = "";
 59                 String tableName        = DatabaseKeysPropertyManager.getParameter(passedTableName);
 60                 //==========================
 61                 qryString       =       "UPDATE "
 62                                 + tableName
 63                                 + "SET "
 64                                 + "NAME = 'Eldin', "
 65                                 + "IMAGE_NAME = 'BB', "
 66                                 + "ITEM_NUMBER = '00002', "
 67                                 + "ITEM_ID = '123', "
 68                                 + "ITEM_DESCRIPTION = 'Sam List Description 123', "
 69                                 + "DISTRIBUTED_BY = 'SamEldin', "
 70                                 + "DISTRIBUTED_DATE = 1080414,"
 71                                 + "DISTRIBUTED_TIME = 123619 "
 72                                 + "WHERE "
 73                                 + "NAME = 'Eldin' and IMAGE_NAME = 'BB' "
 74                                 + "AND "
 75                                 + "EXISTS "
 76                                 + "(SELECT * "
 77                                 + tableName
 78                                 + " WHERE "
 79                                 + "NAME = 'Eldin' and ITEM_NUMBER = '00002'"
 80                                 + ");";
 81 
 82                 localPreparedStatement = localConnection.prepareStatement(qryString);
 83                 localPreparedStatement.executeQuery();
 84                 localPreparedStatement.close();
 85         }
 86         catch(MissingResourceException eMissingResourceException)
 87         {
 88             eMissingResourceException.printStackTrace();
 89             return(Constants.ERROR_RUN);
 90         }
 91         catch(SQLException eSQLException)
 92         {
 93             eSQLException.printStackTrace();
 94             return(Constants.ERROR_RUN);
 95         }
 96         return(Constants.NORMAL_RUN);
 97      }
 98     /*
 99      *
100      */
101      public int selectUsing_NOT_EXISTS(String passedTableName)
102      {
103         if(null == localConnection)
104             return(Constants.ERROR_RUN);
105         PreparedStatement   localPreparedStatement      = null;
106         ResultSet           localResultSet              = null;
107         //==========================
108         try
109         {
110             // SELECT NAME, IMAGE_NAME, ITEM _NUMBER, ITEM _ID, ITEM _DESCRIPTION, DISTRIBUTED_BY, DISTRIBUTED_DATE, DISTRIBUTED_TIME
111             // From TABLE_1
112             // where not EXISTS (SELECT * TABLE_1
113             // WHERE NAME = 'Eldin' and IMAGE_NAME = 'BB' and ITEM _NUMBER = '00002' and ITEM _ID = '123'
114             // and ITEM _DESCRIPTION = 'joe  Description' and DISTRIBUTED_BY = 'SamEldin'
115             // and DISTRIBUTED_DATE = 1080414 and DISTRIBUTED_TIME = 123619);
116 
117                 String qryString        = "";
118                 String tableName        = DatabaseKeysPropertyManager.getParameter(passedTableName);
119                 //==========================
120                 qryString       =       "SELECT "
121                                 + "NAME, "
122                                 + "IMAGE_NAME, "
123                                 + "ITEM_NUMBER, "
124                                 + "ITEM_ID, "
125                                 + "ITEM_DESCRIPTION, "
126                                 + "DISTRIBUTED_BY, "
127                                 + "DISTRIBUTED_DATE, "
128                                 + "DISTRIBUTED_TIME, "
129                                 + "FROM "
130                                 + tableName
131                                 + " NOT "
132                                 + "EXISTS "
133                                 + "(SELECT * "
134                                 + tableName
135                                 + " WHERE "
136                                 + "NAME = 'Eldin'" 
137                                 + " AND "
138                                 + " ITEM_NUMBER = '00002'"
139                                 + " AND "
140                                 + "DISTRIBUTED_TIME = 123619"
141                                 + ");";
142 
143                 localPreparedStatement = localConnection.prepareStatement(qryString);
144                 localPreparedStatement.executeQuery();
145                 localResultSet = localPreparedStatement.executeQuery();
146                 ArrayList localXYZArrayList = new ArrayList();
147                 //===========================================
148                 while(localResultSet.next())
149                 {
150                     // Start retrieving your fields
151                     String xStringValue = localResultSet.getString("Location_Number");
152                     long   xLongValue   = localResultSet.getLong("long lll");
153                     int    xIntValue    = localResultSet.getInt("int iii");
154                     int    xIntValue2   = localResultSet.getInt(4);
155                 }
156 
157                 localPreparedStatement.close();
158         }
159         catch(MissingResourceException eMissingResourceException)
160         {
161             eMissingResourceException.printStackTrace();
162             return(Constants.ERROR_RUN);
163         }
164         catch(SQLException eSQLException)
165         {
166             eSQLException.printStackTrace();
167             return(Constants.ERROR_RUN);
168         }
169         return(Constants.NORMAL_RUN);
170      }
171     /*
172      *
173      */
174      public int insertUsing_IF_NOT_EXISTS(String passedTableName)
175      {
176         if(null == localConnection)
177             return(Constants.ERROR_RUN);
178         PreparedStatement   localPreparedStatement      = null;
179         //==========================
180         try
181         {
182             // if NOT EXISTS (SELECT * FROM table_1 WHERE NAME = 'Sam' and ITEM _NUMBER = '00002')
183             // INSERT INTO table_1 (NAME, IMAGE_NAME, ITEM _NUMBER, ITEM _ID, DISTRIBUTION_DESCRIPTION, DISTRIBUTED_BY, DISTRIBUTED_DATE, DISTRIBUTED_TIME)
184             // VALUES('Sam', 'BB', '00002', '123', 'Sam List Description' , 'SamEldin', 1080414, 123619);
185 
186                 String qryString        = "";
187                 String tableName        = DatabaseKeysPropertyManager.getParameter(passedTableName);
188                 //==========================
189                 qryString       =       " IF NOT "
190                                 + "EXISTS "
191                                 + "(SELECT * FROM "
192                                 + tableName
193                                 + " WHERE "
194                                 + "NAME = 'Eldin'"
195                                 + " AND "
196                                 + " ITEM_NUMBER = '00002'"
197                                 + " AND "
198                                 + "DISTRIBUTED_TIME = 123619"
199                                 + ")"
200                                 + " INSERT INTO "
201                                 + tableName
202                                 + "(NAME, IMAGE_NAME, ITEM _NUMBER, ITEM _ID, "
203                                 + " DISTRIBUTION_DESCRIPTION, DISTRIBUTED_BY, DISTRIBUTED_DATE, DISTRIBUTED_TIME)"
204                                 + " VALUES('Sam', 'BB', '00002', '123', 'Sam List Description' , 'SamEldin', 1080414, 123619);";
205 
206                 localPreparedStatement = localConnection.prepareStatement(qryString);
207                 localPreparedStatement.executeQuery();
208                 localPreparedStatement.close();
209         }
210         catch(MissingResourceException eMissingResourceException)
211         {
212             eMissingResourceException.printStackTrace();
213             return(Constants.ERROR_RUN);
214         }
215         catch(SQLException eSQLException)
216         {
217             eSQLException.printStackTrace();
218             return(Constants.ERROR_RUN);
219         }
220         return(Constants.NORMAL_RUN);
221      }
222 }
223 
224