m2u-upass-core / src / my / com / upass / db / DBOperations.java @ 38:3d22253b0fbc
History | View | Annotate | Download (16.6 KB)
1 | 0:02300db8682b | hadi | package my.com.upass.db; |
---|---|---|---|
2 | |||
3 | import java.sql.Connection; |
||
4 | import java.sql.PreparedStatement; |
||
5 | import java.sql.ResultSet; |
||
6 | import java.sql.SQLException; |
||
7 | import java.sql.Statement; |
||
8 | |||
9 | import my.com.upass.Constants; |
||
10 | 2:dcd717e1bcae | hadi | import my.com.upass.TokenBean; |
11 | 0:02300db8682b | hadi | import my.com.upass.pojo.TacBean; |
12 | import net.penril.generic.hibernate.HibernateUtils; |
||
13 | |||
14 | |||
15 | 9:16125cca68e4 | hadi | public class DBOperations extends MinimalDbOperations { |
16 | 0:02300db8682b | hadi | |
17 | public DBOperations() {
|
||
18 | // constructor
|
||
19 | try {
|
||
20 | this.conn = getConnection();
|
||
21 | } catch(Exception e) { |
||
22 | logger.error("EXC: DBOperations");
|
||
23 | logger.error(getFullTrace(e)); |
||
24 | this.conn = null; |
||
25 | } |
||
26 | //// System.out.println("DBOperations()-exit " + this.conn);
|
||
27 | } |
||
28 | |||
29 | public Connection getConnection() throws Exception { |
||
30 | |||
31 | // Connection conn;
|
||
32 | // String use_jndi = "N";
|
||
33 | //
|
||
34 | // Config cfg = Config.getInstance();
|
||
35 | // Properties cfgData = cfg.getConfig();
|
||
36 | // try {
|
||
37 | // use_jndi = cfgData.get("DB_USE_JNDI").toString(); //Y/N
|
||
38 | // } catch (Exception e) {
|
||
39 | // logger.error("ERR: Property entry not found..");
|
||
40 | // }
|
||
41 | //
|
||
42 | // if ( use_jndi.equals("Y") ) {
|
||
43 | // String DB_DATASRC_REF = null;
|
||
44 | // try {
|
||
45 | // DB_DATASRC_REF = cfgData.get("DB_DATASRC_REF").toString();//java:OracleUpassDS
|
||
46 | // } catch (Exception e) {
|
||
47 | // logger.error("ERR: Property entry not found..");
|
||
48 | // return null;
|
||
49 | // }
|
||
50 | // InitialContext ctx = new InitialContext();
|
||
51 | // DataSource datasource = (DataSource)ctx.lookup(DB_DATASRC_REF);
|
||
52 | // conn = datasource.getConnection();
|
||
53 | // } else {
|
||
54 | // // the below is for self test only
|
||
55 | // String driver = null;
|
||
56 | // String url = null;
|
||
57 | // String username = null;
|
||
58 | // String password = null;
|
||
59 | //
|
||
60 | // try {
|
||
61 | // driver = cfgData.get("DB_DRIVER").toString(); //"oracle.jdbc.driver.OracleDriver";
|
||
62 | // url = cfgData.get("DB_URL").toString(); //"jdbc:oracle:thin:@127.0.0.1:1521:XE";
|
||
63 | // username = cfgData.get("DB_USERNAME").toString(); //"am01";
|
||
64 | // password = cfgData.get("DB_PASSWORD").toString(); //"am01";
|
||
65 | // } catch (Exception e) {
|
||
66 | // logger.error("ERR: Property entry not found..");
|
||
67 | // return null;
|
||
68 | // }
|
||
69 | //
|
||
70 | // Class.forName(driver); // load Oracle driver
|
||
71 | // conn = DriverManager.getConnection(url, username, password);
|
||
72 | // }
|
||
73 | Connection conn = HibernateUtils.currentSession().connection();
|
||
74 | |||
75 | conn.setAutoCommit(false);
|
||
76 | return conn;
|
||
77 | } |
||
78 | |||
79 | public long getCurrSeqID(int mode) { |
||
80 | |||
81 | |||
82 | // setup the unique ID
|
||
83 | long lVal=0; |
||
84 | |||
85 | String sqlGetSeqNbr = null; |
||
86 | String modeVal = null; |
||
87 | |||
88 | String sqlGetNextSeqNbr = "SELECT seq_user_id.NEXTVAL FROM dual"; |
||
89 | String nextVal = "NEXTVAL"; |
||
90 | |||
91 | String sqlGetCurrSeqNbr = "SELECT seq_user_id.CURRVAL FROM dual"; |
||
92 | String currVal = "CURRVAL"; |
||
93 | |||
94 | |||
95 | if (mode == Constants.MODE_QCURRVAL) {
|
||
96 | sqlGetSeqNbr = sqlGetCurrSeqNbr; |
||
97 | modeVal = currVal; |
||
98 | } else {
|
||
99 | sqlGetSeqNbr = sqlGetNextSeqNbr; |
||
100 | modeVal = nextVal; |
||
101 | } |
||
102 | |||
103 | ResultSet rs = null; |
||
104 | Statement stmt = null; |
||
105 | try {
|
||
106 | stmt = this.conn.createStatement();
|
||
107 | rs = stmt.executeQuery(sqlGetSeqNbr); |
||
108 | if (rs.next()) {
|
||
109 | lVal = rs.getInt(modeVal); |
||
110 | logger.info("seq_user_id.value=" + lVal);
|
||
111 | } |
||
112 | } catch (SQLException ex) { |
||
113 | logger.error("SQLEXC: getCurrSeqID " + ex.getMessage());
|
||
114 | } finally {
|
||
115 | logger.info(": getCurrSeqID Final");
|
||
116 | try {
|
||
117 | rs.close(); |
||
118 | stmt.close(); |
||
119 | //this.conn.close();
|
||
120 | } catch (SQLException ex) { |
||
121 | logger.error (getFullTrace (ex)); |
||
122 | } |
||
123 | } |
||
124 | return lVal;
|
||
125 | } |
||
126 | |||
127 | 9:16125cca68e4 | hadi | |
128 | 0:02300db8682b | hadi | |
129 | ///////////////////////////////////////////////////////////////////////
|
||
130 | // User Bean
|
||
131 | ///////////////////////////////////////////////////////////////////////
|
||
132 | |||
133 | |||
134 | |||
135 | ///////////////////////////////////////////////////////////////////////
|
||
136 | // TAC Bean
|
||
137 | ///////////////////////////////////////////////////////////////////////
|
||
138 | |||
139 | public TacBean getTacFromStore(String userAlias) { |
||
140 | |||
141 | //String sqlStmt0 = "SELECT * FROM tb_am_user WHERE u_user_alias = ?";
|
||
142 | String sqlStmt1 = "SELECT * FROM tb_am_user a, tb_am_tac b WHERE a.u_user_alias = ? AND a.u_user_id = b.t_user_id"; |
||
143 | //String sqlStmt2 = "SELECT * FROM tb_am_user a, tb_am_vasco b WHERE a.u_user_alias = ? AND a.u_user_id = b.p_user_id";
|
||
144 | |||
145 | String sqlStmt = null; |
||
146 | |||
147 | sqlStmt = sqlStmt1; |
||
148 | /*
|
||
149 | switch (sqlMode) {
|
||
150 | case Constants.MODE_QUSERBEAN: sqlStmt = sqlStmt0; break;
|
||
151 | case Constants.MODE_QTACBEAN: sqlStmt = sqlStmt1; break;
|
||
152 | case Constants.MODE_QVASCOBEAN: sqlStmt = sqlStmt2; break;
|
||
153 | }*/
|
||
154 | |||
155 | |||
156 | ResultSet rs = null; |
||
157 | PreparedStatement pstmt = null; |
||
158 | |||
159 | TacBean tb = new TacBean();
|
||
160 | |||
161 | try {
|
||
162 | pstmt = this.conn.prepareStatement(sqlStmt);
|
||
163 | pstmt.setString(1, userAlias);
|
||
164 | rs = executeSQLSelect(pstmt); |
||
165 | |||
166 | if (rs.next()) {
|
||
167 | tb.setUserID(rs.getLong("T_USER_ID"));
|
||
168 | tb.setCipherText(rs.getString("T_TAC_CIPHERTEXT"));
|
||
169 | tb.setState(rs.getInt("T_TAC_STATE"));
|
||
170 | tb.setDateCreated(rs.getTimestamp("T_DATE_GENERATED"));
|
||
171 | tb.setDateFirstUsed(rs.getTimestamp("T_DATE_FIRST_USED"));
|
||
172 | tb.setDateLastUsed(rs.getTimestamp("T_DATE_LAST_USED"));
|
||
173 | tb.setUseCount(rs.getInt("T_USE_COUNT"));
|
||
174 | tb.setErrorCount(rs.getInt("T_ERROR_COUNT"));
|
||
175 | tb.setSessionId(rs.getString("T_SESSION_ID"));
|
||
176 | |||
177 | } else { //no record found |
||
178 | logger.info("ERR: no record found!");
|
||
179 | tb = null;
|
||
180 | } //
|
||
181 | } catch (SQLException ex) { |
||
182 | logger.error("SQLEXC:1 getTacFromStore " + ex.getMessage());
|
||
183 | tb = null;
|
||
184 | } catch (Exception e) { |
||
185 | logger.error("EXC:1 getTacFromStore " + e.getMessage());
|
||
186 | tb = null;
|
||
187 | } finally { // close database resources |
||
188 | try {
|
||
189 | rs.close(); |
||
190 | pstmt.close(); |
||
191 | //this.conn.close();
|
||
192 | } catch (SQLException ex) { |
||
193 | logger.error("EXC:Final getTacFromStore " + ex.getMessage());
|
||
194 | } |
||
195 | }//finally--
|
||
196 | |||
197 | return tb;
|
||
198 | } |
||
199 | |||
200 | public boolean updateTacToStore(TacBean tb) { |
||
201 | |||
202 | boolean rc = false; |
||
203 | String sqlStmt = "UPDATE tb_am_tac SET " + |
||
204 | "T_TAC_CIPHERTEXT=?, T_TAC_STATE=?, T_DATE_GENERATED=?, " +
|
||
205 | "T_DATE_FIRST_USED=?, T_DATE_LAST_USED=?, T_USE_COUNT=?, T_ERROR_COUNT=?, T_SESSION_ID=?"+
|
||
206 | "WHERE t_user_id = ?";
|
||
207 | int nRow = 0; |
||
208 | |||
209 | // insert into DB
|
||
210 | PreparedStatement pstmt = null; |
||
211 | try {
|
||
212 | pstmt = this.conn.prepareStatement(sqlStmt);
|
||
213 | pstmt.setString(1, tb.getCipherText());
|
||
214 | pstmt.setInt(2, tb.getState());
|
||
215 | |||
216 | //java.sql.Timestamp today = getCurrentSqlDate();
|
||
217 | pstmt.setTimestamp(3, convertSqlDate(tb.getDateCreated()) ); //generated |
||
218 | pstmt.setTimestamp(4, convertSqlDate(tb.getDateFirstUsed()) ); //1st used |
||
219 | pstmt.setTimestamp(5, convertSqlDate(tb.getDateLastUsed()) ); //1ast used |
||
220 | pstmt.setInt(6,tb.getUseCount());
|
||
221 | pstmt.setInt(7,tb.getErrorCount());
|
||
222 | pstmt.setString(8, tb.getSessionId());
|
||
223 | pstmt.setLong(9, tb.getUserID());
|
||
224 | |||
225 | nRow = executeSQLUpdate(pstmt); |
||
226 | //// System.out.println(nRow + " Row modified to table Key=" + tb.getTUserID());
|
||
227 | |||
228 | if (nRow < 1) { |
||
229 | logger.info("ERR: no row modified into TAC table!");
|
||
230 | //
|
||
231 | // rollback
|
||
232 | //
|
||
233 | try {
|
||
234 | this.conn.rollback();
|
||
235 | logger.info("INFO:1 updateTacToStore :: rollback.");
|
||
236 | } catch(Exception rbe) { |
||
237 | logger.error("EXC:updateTacToStore :: rollback failed!");
|
||
238 | } |
||
239 | // rollback completed
|
||
240 | rc = false;
|
||
241 | } else {
|
||
242 | ////System.out.println("GOOD: perf db commit!");
|
||
243 | this.conn.commit();
|
||
244 | rc = true;
|
||
245 | } |
||
246 | |||
247 | } catch (Exception e) { |
||
248 | logger.error("EXC:1 updateTacToStore");
|
||
249 | logger.error(getFullTrace(e)); |
||
250 | rc = false;
|
||
251 | } finally { // close database resources |
||
252 | try {
|
||
253 | pstmt.close(); |
||
254 | //this.conn.close();
|
||
255 | } catch (Exception e) { |
||
256 | logger.error("EXC:Final updateTacToStore");
|
||
257 | logger.error(getFullTrace(e)); |
||
258 | } |
||
259 | } //finally--
|
||
260 | |||
261 | return rc;
|
||
262 | } |
||
263 | |||
264 | |||
265 | /**
|
||
266 | * TOKEN Operations
|
||
267 | */
|
||
268 | public boolean insertTokenToStore(TokenBean tnb) { |
||
269 | boolean rc = false; |
||
270 | int nRow = 0; |
||
271 | |||
272 | /*
|
||
273 | 01 V_SERIAL_NO NOT NULL VARCHAR2(22)
|
||
274 | 02 V_USER_ID NOT NULL NUMBER(9)
|
||
275 | 03 V_DP_MODEL VARCHAR2(5)
|
||
276 | 04 V_DP_AUTH_MODE VARCHAR2(2)
|
||
277 | 05 V_DP_CIPHERTEXT NOT NULL BLOB
|
||
278 | 06 V_BAK_CIPHERTEXT NOT NULL BLOB
|
||
279 | 07 V_DP_ASSIGNED NOT NULL NUMBER(1)
|
||
280 | 08 V_DATE_ASSIGNED TIMESTAMP(6)
|
||
281 | 09 V_DATE_FIRST_USED TIMESTAMP(6)
|
||
282 | 10 V_DATE_LAST_USED TIMESTAMP(6)
|
||
283 | 11 V_USE_COUNT NOT NULL NUMBER(6)
|
||
284 | 12 V_ERROR_COUNT NOT NULL NUMBER(2)
|
||
285 | 13 V_DP_STATE NOT NULL NUMBER(1)
|
||
286 | 14 V_BATCH_NO NOT NULL NUMBER(9)
|
||
287 | */
|
||
288 | |||
289 | //long nextVal= getCurrSeqID( Constants.MODE_QNEXTVAL);
|
||
290 | |||
291 | String sqlStmt = "INSERT INTO tb_am_vasco (V_SERIAL_NO, V_USER_ID, V_DP_MODEL, V_DP_AUTH_MODE, V_DP_CIPHERTEXT, V_BAK_CIPHERTEXT," + |
||
292 | "V_DP_ASSIGNED, V_DATE_ASSIGNED, V_DATE_FIRST_USED, V_DATE_LAST_USED, V_USE_COUNT, V_ERROR_COUNT, V_DP_STATE, V_BATCH_NO) " +
|
||
293 | "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
|
||
294 | |||
295 | // insert into DB
|
||
296 | PreparedStatement pstmt = null; |
||
297 | try {
|
||
298 | pstmt = this.conn.prepareStatement(sqlStmt);
|
||
299 | pstmt.setString(1, tnb.getVserialNumber());
|
||
300 | pstmt.setLong(2, tnb.getVuserID());
|
||
301 | pstmt.setString(3, tnb.getVdpModel());
|
||
302 | pstmt.setString(4, tnb.getVdpAuthMode());
|
||
303 | pstmt.setBytes(5, tnb.getVdpCipherText());
|
||
304 | pstmt.setBytes(6, tnb.getVbkCipherText());
|
||
305 | pstmt.setInt(7, tnb.getVassigned());
|
||
306 | pstmt.setTimestamp(8, convertSqlDate( tnb.getVdateAssigned() ) );
|
||
307 | pstmt.setTimestamp(9, convertSqlDate( tnb.getVdateFirstUsed() ) );
|
||
308 | pstmt.setTimestamp(10, convertSqlDate( tnb.getVdateLastUsed() ) );
|
||
309 | pstmt.setInt(11, tnb.getVuseCount());
|
||
310 | pstmt.setInt(12, tnb.getVerrorCount());
|
||
311 | pstmt.setInt(13, tnb.getVstate());
|
||
312 | pstmt.setString(14, tnb.getVbatchNo());
|
||
313 | |||
314 | nRow = executeSQLUpdate(pstmt); |
||
315 | //// System.out.println(nRow + " Row inserted to table");
|
||
316 | |||
317 | if (nRow < 1) { |
||
318 | logger.info("ERR: no row inserted into token table!");
|
||
319 | //
|
||
320 | // rollback
|
||
321 | //
|
||
322 | try {
|
||
323 | this.conn.rollback();
|
||
324 | logger.error("INFO:1 insertTokenToStore :: rollback.");
|
||
325 | } catch(Exception rbe) { |
||
326 | logger.error("EXC:insertTokenToStore :: rollback failed!");
|
||
327 | } |
||
328 | // rollback completed
|
||
329 | rc = false;
|
||
330 | } else {
|
||
331 | ////System.out.println("GOOD: perf db commit!");
|
||
332 | this.conn.commit();
|
||
333 | rc = true;
|
||
334 | } |
||
335 | |||
336 | |||
337 | } catch (Exception e) { |
||
338 | //logger.error(getFullTrace(e));
|
||
339 | logger.error("EXC:1 insertTokenToStore");
|
||
340 | rc = false;
|
||
341 | } finally { // close database resources |
||
342 | try {
|
||
343 | pstmt.close(); |
||
344 | //this.conn.close();
|
||
345 | } catch (Exception e) { |
||
346 | logger.error("EXC:Final insertTokenToStore");
|
||
347 | logger.error(getFullTrace(e)); |
||
348 | } |
||
349 | }//finally--
|
||
350 | |||
351 | return rc;
|
||
352 | } |
||
353 | |||
354 | public TokenBean getTokenFromStoreBySerialNumber(String serialNumber) { |
||
355 | String sqlStmt = "SELECT * FROM tb_am_vasco WHERE v_serial_no = ?"; |
||
356 | return getTokenFromStore(sqlStmt, serialNumber);
|
||
357 | } |
||
358 | |||
359 | public TokenBean getTokenFromStoreByUserAlias(String userAlias) { |
||
360 | String sqlStmt = "SELECT * FROM tb_am_user a, tb_am_vasco b WHERE a.u_user_alias = ? AND a.u_user_id = b.v_user_id"; |
||
361 | return getTokenFromStore(sqlStmt, userAlias);
|
||
362 | } |
||
363 | |||
364 | private TokenBean getTokenFromStore(String sqlStmt, String dbKey) { |
||
365 | |||
366 | ResultSet rs = null; |
||
367 | PreparedStatement pstmt = null; |
||
368 | |||
369 | TokenBean tb = new TokenBean();
|
||
370 | |||
371 | try {
|
||
372 | pstmt = this.conn.prepareStatement(sqlStmt);
|
||
373 | pstmt.setString(1, dbKey);
|
||
374 | rs = executeSQLSelect(pstmt); |
||
375 | |||
376 | if (rs.next()) {
|
||
377 | |||
378 | tb.setVserialNumber(rs.getString("V_SERIAL_NO"));
|
||
379 | tb.setVuserID(rs.getLong("V_USER_ID"));
|
||
380 | tb.setVdpModel(rs.getString("V_DP_MODEL"));
|
||
381 | tb.setVdpAuthMode(rs.getString("V_DP_AUTH_MODE"));
|
||
382 | tb.setVdpCipherText(rs.getBytes("V_DP_CIPHERTEXT"));
|
||
383 | tb.setVbkCipherText(rs.getBytes("V_BAK_CIPHERTEXT"));
|
||
384 | tb.setVassigned(rs.getInt("V_DP_ASSIGNED"));
|
||
385 | tb.setVdateAssigned(rs.getTimestamp("V_DATE_ASSIGNED"));
|
||
386 | tb.setVdateFirstUsed(rs.getTimestamp("V_DATE_FIRST_USED"));
|
||
387 | tb.setVdateLastUsed(rs.getTimestamp("V_DATE_LAST_USED"));
|
||
388 | tb.setVuseCount(rs.getInt("V_USE_COUNT"));
|
||
389 | tb.setVerrorCount(rs.getInt("V_ERROR_COUNT"));
|
||
390 | tb.setVstate(rs.getInt("V_DP_STATE"));
|
||
391 | tb.setVbatchNo(rs.getString("V_BATCH_NO"));
|
||
392 | } else { //no record found |
||
393 | logger.info("ERR: no record found! " + dbKey);
|
||
394 | tb = null;
|
||
395 | } //
|
||
396 | } catch (SQLException ex) { |
||
397 | logger.error("SQLEXC:1 getTokenFromStore " + ex.getMessage());
|
||
398 | tb = null;
|
||
399 | } catch (Exception e) { |
||
400 | logger.error("EXC:1 getTokenFromStore " + e.getMessage());
|
||
401 | tb = null;
|
||
402 | } finally { // close database resources |
||
403 | try {
|
||
404 | rs.close(); |
||
405 | pstmt.close(); |
||
406 | //this.conn.close();
|
||
407 | } catch (SQLException ex) { |
||
408 | logger.error("EXC:Final getTokenFromStore " + ex.getMessage());
|
||
409 | } |
||
410 | }//finally--
|
||
411 | |||
412 | return tb;
|
||
413 | } |
||
414 | |||
415 | public boolean updateTokenToStore(TokenBean tb) { |
||
416 | |||
417 | /*
|
||
418 | * Backup BLOB (V_BAK_CIPHERTEXT) is read only.
|
||
419 | */
|
||
420 | |||
421 | boolean rc = false; |
||
422 | String sqlStmt = "UPDATE tb_am_vasco SET " + |
||
423 | "V_USER_ID=?, V_DP_MODEL=?, V_DP_AUTH_MODE=?, V_DP_CIPHERTEXT=?, V_DP_ASSIGNED=?, V_DATE_ASSIGNED=?," +
|
||
424 | "V_DATE_FIRST_USED=?, V_DATE_LAST_USED=?, V_USE_COUNT=?, V_ERROR_COUNT=?, V_DP_STATE=?, V_BATCH_NO=?" +
|
||
425 | " WHERE V_SERIAL_NO = ?";
|
||
426 | int nRow = 0; |
||
427 | |||
428 | // insert into DB
|
||
429 | PreparedStatement pstmt = null; |
||
430 | try {
|
||
431 | pstmt = this.conn.prepareStatement(sqlStmt);
|
||
432 | |||
433 | pstmt.setLong(1, tb.getVuserID());
|
||
434 | pstmt.setString(2, tb.getVdpModel());
|
||
435 | pstmt.setString(3, tb.getVdpAuthMode());
|
||
436 | pstmt.setBytes(4, tb.getVdpCipherText());
|
||
437 | pstmt.setInt(5, tb.getVassigned());
|
||
438 | pstmt.setTimestamp(6, convertSqlDate(tb.getVdateAssigned()) ); //1st used |
||
439 | pstmt.setTimestamp(7, convertSqlDate(tb.getVdateFirstUsed()) ); //1st used |
||
440 | pstmt.setTimestamp(8, convertSqlDate(tb.getVdateLastUsed()) ); //1ast used |
||
441 | pstmt.setInt(9,tb.getVuseCount());
|
||
442 | pstmt.setInt(10,tb.getVerrorCount());
|
||
443 | pstmt.setInt(11, tb.getVstate());
|
||
444 | pstmt.setString(12, tb.getVbatchNo());
|
||
445 | pstmt.setString(13, tb.getVserialNumber());
|
||
446 | |||
447 | nRow = executeSQLUpdate(pstmt); |
||
448 | //// System.out.println(nRow + " Row modified to table Key=" + tb.getVSerialNumber());
|
||
449 | |||
450 | if (nRow < 1) { |
||
451 | logger.info("ERR: no row modified into user table!");
|
||
452 | //
|
||
453 | // rollback
|
||
454 | //
|
||
455 | try {
|
||
456 | this.conn.rollback();
|
||
457 | logger.info("INFO:1 updateTokenToStore :: rollback.");
|
||
458 | } catch(Exception rbe) { |
||
459 | logger.error("EXC:updateTokenToStore :: rollback failed!");
|
||
460 | } |
||
461 | // rollback completed
|
||
462 | rc = false;
|
||
463 | } else {
|
||
464 | ////System.out.println("GOOD: perf db commit!");
|
||
465 | this.conn.commit();
|
||
466 | rc = true;
|
||
467 | } |
||
468 | |||
469 | } catch (Exception e) { |
||
470 | logger.error("EXC:1 updateTokenToStore");
|
||
471 | logger.error(getFullTrace(e)); |
||
472 | rc = false;
|
||
473 | } finally { // close database resources |
||
474 | try {
|
||
475 | pstmt.close(); |
||
476 | //this.conn.close();
|
||
477 | } catch (Exception e) { |
||
478 | logger.error("EXC:Final updateTokenToStore");
|
||
479 | logger.error(getFullTrace(e)); |
||
480 | } |
||
481 | } //finally--
|
||
482 | |||
483 | return rc;
|
||
484 | } |
||
485 | |||
486 | public boolean deleteTokenFromStoreBySerialNumber(String serialNumber) { |
||
487 | |||
488 | boolean rc = false; |
||
489 | String sqlStmt = "DELETE FROM tb_am_vasco WHERE V_SERIAL_NO = ?"; |
||
490 | int nRow = 0; |
||
491 | |||
492 | // insert into DB
|
||
493 | PreparedStatement pstmt = null; |
||
494 | try {
|
||
495 | pstmt = this.conn.prepareStatement(sqlStmt);
|
||
496 | pstmt.setString(1, serialNumber);
|
||
497 | nRow = executeSQLUpdate(pstmt); |
||
498 | |||
499 | // System.out.println(nRow + " Row deleted From table Key=" + serialNumber);
|
||
500 | |||
501 | if (nRow < 1) { |
||
502 | logger.info("ERR: no row deleted from token table!");
|
||
503 | //
|
||
504 | // rollback
|
||
505 | //
|
||
506 | try {
|
||
507 | this.conn.rollback();
|
||
508 | logger.info("INFO:1 deleteTokenFromStoreBySerialNumber :: rollback.");
|
||
509 | } catch(Exception rbe) { |
||
510 | logger.error("EXC:deleteTokenFromStoreBySerialNumber :: rollback failed!");
|
||
511 | } |
||
512 | // rollback completed
|
||
513 | rc = false;
|
||
514 | } else {
|
||
515 | ////System.out.println("GOOD: perf db commit!");
|
||
516 | this.conn.commit();
|
||
517 | rc = true;
|
||
518 | } |
||
519 | |||
520 | } catch (Exception e) { |
||
521 | logger.error("EXC:1 deleteTokenFromStoreBySerialNumber");
|
||
522 | logger.error(getFullTrace(e)); |
||
523 | rc = false;
|
||
524 | } finally { // close database resources |
||
525 | try {
|
||
526 | pstmt.close(); |
||
527 | //this.conn.close();
|
||
528 | } catch (Exception e) { |
||
529 | logger.error("EXC:Final deleteTokenFromStoreBySerialNumber");
|
||
530 | logger.error(getFullTrace(e)); |
||
531 | } |
||
532 | } //finally--
|
||
533 | |||
534 | return rc;
|
||
535 | |||
536 | } |
||
537 | }//end class |