| D:\JavaFrameworks\InsuranceFramework\src\java\db_adapter\SelectSQLStatements.java |
1
2
3
4
5
6 package db_adapter;
7
8
9
10 @author
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
28
29
30 import utils.*;
31 import constants.*;
32
33
34 public class SelectSQLStatements
35 {
36 Connection localConnection = null;
37
38 public SelectSQLStatements()
39 {
40 BD_Connector localBD_Connector = new BD_Connector();
41 localConnection = localBD_Connector.getConnection();
42 }
43
44
45
46 public int select(String passedTableName_1, String passedTableName_2, String locationNumber, long passedID, long passedID_2)
47 {
48 if(null == localConnection)
49 return(Constants.ERROR_RUN);
50 PreparedStatement localPreparedStatement = null;
51 ResultSet localResultSet = null;
52
53 try
54 {
55
56
57
58 String qryString = "";
59 String tableName_1 = DatabaseKeysPropertyManager.getParameter(passedTableName_1);
60 String tableName_2 = DatabaseKeysPropertyManager.getParameter(passedTableName_2);
61
62 qryString = "SELECT * FROM "
63 + tableName_1
64 + " , "
65 + tableName_2
66 + " WHERE "
67 + tableName_1
68 + "."
69 + ".LOCATION_NUMBER"
70 + " = "
71 + "'"
72 + locationNumber
73 + "'"
74 + " AND "
75 + tableName_2
76 + ".LOCATION_NUMBER"
77 + " = "
78 + "'"
79 + locationNumber
80 + "'"
81 + " AND "
82 + tableName_1
83 + "."
84 + ".ID_NUMBER"
85 + " = "
86 + passedID
87 + " AND "
88 + tableName_2
89 + ".ID_NUMBER"
90 + " = "
91 + passedID_2
92 + " ORDER BY digits("
93 + tableName_1
94 + ".ID_NUMBER)"
95 + " DESC";
96
97 localPreparedStatement = localConnection.prepareStatement(qryString);
98 localResultSet = localPreparedStatement.executeQuery();
99 ArrayList localXYZArrayList = new ArrayList();
100
101 while(localResultSet.next())
102 {
103 String xStringValue = localResultSet.getString("Location_Number");
104 long xLongValue = localResultSet.getLong("long lll");
105 int xIntValue = localResultSet.getInt("int iii");
106 int xIntValue2 = localResultSet.getInt(4);
107 Date sqlDate = localResultSet.getDate("date");
108 long longDateValue = 0;
109 String dateString = "";
110 longDateValue = localResultSet.getLong("ERB_DATE_NOTE_ENTERED");
111 if(longDateValue > 0)
112 {
113 dateString = DateStringConverter.convertlongToDateString(longDateValue);
114 }
115 else
116 dateString = "?";
117 Date tempDate = new Date(longDateValue);
118 String DATE_FORMAT_NOW = "MM/dd/yyyy";
119 String TIME_FORMAT_NOW = "H:mm:ss";
120 Calendar cal = Calendar.getInstance();
121 SimpleDateFormat dayformat = new SimpleDateFormat(DATE_FORMAT_NOW);
122 String today = dayformat.format(cal.getTime());
123 SimpleDateFormat timeformat = new SimpleDateFormat(TIME_FORMAT_NOW);
124 String time = dayformat.format(cal.getTime());
125 }
126 localPreparedStatement.close();
127 }
128 catch(MissingResourceException eMissingResourceException)
129 {
130 eMissingResourceException.printStackTrace();
131 return(Constants.ERROR_RUN);
132 }
133 catch(SQLException eSQLException)
134 {
135 eSQLException.printStackTrace();
136 return(Constants.ERROR_RUN);
137 }
138 return(Constants.NORMAL_RUN);
139 }
140
141
142
143 public int select(String passedTableName, long passedID)
144 {
145 if(null == localConnection)
146 return(Constants.ERROR_RUN);
147 PreparedStatement localPreparedStatement = null;
148 ResultSet localResultSet = null;
149
150 try
151 {
152
153
154
155 String qryString = "";
156 String tableName = DatabaseKeysPropertyManager.getParameter(passedTableName);
157
158 boolean case_1 = true;
159 if(case_1)
160 {
161 qryString = "SELECT MAX(ITEM_ID) FROM "
162 + tableName
163 + " WHERE "
164 + "USER_ID"
165 + " = "
166 + "'"
167 + passedID
168 + "'";
169 }
170 else
171 {
172 qryString = "SELECT ITEM_ID FROM "
173 + tableName
174 + " WHERE "
175 + "USER_ID"
176 + " = "
177 + "'"
178 + passedID
179 + "' order by digits(ITEM_ID) desc";
180
181
182
183 }
184 localPreparedStatement = localConnection.prepareStatement(qryString);
185 localResultSet = localPreparedStatement.executeQuery();
186
187 int itemID = 1;
188 String tempItem_ID_String = "";
189 int maxItemID = 0;
190 while(localResultSet.next())
191 {
192 tempItem_ID_String = localResultSet.getString("ITEM_ID_STRING");
193 if( (tempItem_ID_String != null)
194 && (tempItem_ID_String.length() > 0)
195 )
196 {
197 tempItem_ID_String = tempItem_ID_String.trim();
198 try
199 {
200 itemID = Integer.parseInt(tempItem_ID_String) + 1;
201 }
202 catch(NumberFormatException e)
203 {
204 itemID = 1;
205 }
206 }
207 if(maxItemID < itemID)
208 maxItemID = itemID;
209 }
210 localPreparedStatement.close();
211 }
212 catch(MissingResourceException eMissingResourceException)
213 {
214 eMissingResourceException.printStackTrace();
215 return(Constants.ERROR_RUN);
216 }
217 catch(SQLException eSQLException)
218 {
219 eSQLException.printStackTrace();
220 return(Constants.ERROR_RUN);
221 }
222 return(Constants.NORMAL_RUN);
223 }
224
225
226
227 public int searchWithLIKE(String passedTableName, String userName, String address, long userID)
228 {
229 if(null == localConnection)
230 return(Constants.ERROR_RUN);
231 PreparedStatement localPreparedStatement = null;
232 ResultSet localResultSet = null;
233
234 try
235 {
236
237
238
239 String qryString = "";
240 String tableName = DatabaseKeysPropertyManager.getParameter(passedTableName);
241
242 qryString = "SELECT * FROM "
243 + tableName
244 + " WHERE ";
245 userName = userName.trim();
246 if( (userName != null) && (userName.length() > 0) )
247 {
248 qryString = qryString
249 + " USER_NAME LIKE "
250 + "'%"
251 + userName
252 + "%' "
253 + " AND";
254 }
255 else
256 {
257 address = address.trim();
258 if( (address != null) && (address.length() > 0) )
259 qryString = qryString
260 + " ADDRESS = "
261 + "'"
262 + address
263 + "'"
264 + " AND";
265 }
266 qryString = qryString
267 + " USER_ID = "
268 + ""
269 + userID
270 + "";
271 localPreparedStatement = localConnection.prepareStatement(qryString);
272 localResultSet = localPreparedStatement.executeQuery();
273 ArrayList localXYZArrayList = new ArrayList();
274
275 while(localResultSet.next())
276 {
277 }
278 localPreparedStatement.close();
279 }
280 catch(MissingResourceException eMissingResourceException)
281 {
282 eMissingResourceException.printStackTrace();
283 return(Constants.ERROR_RUN);
284 }
285 catch(SQLException eSQLException)
286 {
287 eSQLException.printStackTrace();
288 return(Constants.ERROR_RUN);
289 }
290 return(Constants.NORMAL_RUN);
291 }
292
293
294
295 public int selectLEFT_OUTER_JOIN(String passedTableName_1, String passedTableName_2, String locationNumber, long passedID, long passedID_2)
296 {
297 if(null == localConnection)
298 return(Constants.ERROR_RUN);
299 PreparedStatement localPreparedStatement = null;
300 ResultSet localResultSet = null;
301
302 try
303 {
304
305
306
307
308 String qryString = "";
309 String tableName_1 = DatabaseKeysPropertyManager.getParameter(passedTableName_1);
310 String tableName_2 = DatabaseKeysPropertyManager.getParameter(passedTableName_2);
311
312 qryString = "SELECT * FROM "
313 + tableName_1 + ".* , "
314 + tableName_2 +".LOCATION_NAME "
315 + " FROM "
316 + tableName_1
317 + " LEFT OUTER JOIN "
318 + tableName_2
319 + " ON ("
320 + tableName_1 + "."
321 + "LOCATION_DL1 = "
322 + tableName_2 + "."
323 + "LOCATION_NUMBER "
324 + " AND "
325 + tableName_2 + "."
326 + "LOCATION_TYPE = 'D')"
327 + " WHERE ";
328 locationNumber = locationNumber.trim();
329 if( (locationNumber != null) && (locationNumber.length() > 0) )
330 {
331 qryString = qryString
332 + tableName_1 + "."
333 + "LOCATION_NUMBER LIKE "
334 + "'%"
335 + locationNumber
336 + "%' "
337 + " AND ";
338 }
339 qryString = qryString
340 + tableName_1 + "."
341 + " USER_ID = "
342 + ""
343 + passedID
344 + "";
345
346 localPreparedStatement = localConnection.prepareStatement(qryString);
347 localResultSet = localPreparedStatement.executeQuery();
348
349 while(localResultSet.next())
350 {
351 }
352 localPreparedStatement.close();
353 }
354 catch(MissingResourceException eMissingResourceException)
355 {
356 eMissingResourceException.printStackTrace();
357 return(Constants.ERROR_RUN);
358 }
359 catch(SQLException eSQLException)
360 {
361 eSQLException.printStackTrace();
362 return(Constants.ERROR_RUN);
363 }
364 return(Constants.NORMAL_RUN);
365 }
366 }
367
368