m2u-upass-core / src / my / com / upass / db / DBOperations.java @ 5:f608e6319b70
History | View | Annotate | Download (30.2 KB)
1 |
package my.com.upass.db; |
---|---|
2 |
|
3 |
import java.math.BigDecimal; |
4 |
import java.sql.CallableStatement; |
5 |
import java.sql.Connection; |
6 |
import java.sql.PreparedStatement; |
7 |
import java.sql.ResultSet; |
8 |
import java.sql.SQLException; |
9 |
import java.sql.Statement; |
10 |
import java.util.Date; |
11 |
|
12 |
import my.com.upass.Constants; |
13 |
import my.com.upass.TokenBean; |
14 |
import my.com.upass.pojo.TacBean; |
15 |
import my.com.upass.pojo.UserBean; |
16 |
import net.penril.generic.hibernate.HibernateUtils; |
17 |
|
18 |
import org.apache.log4j.Logger; |
19 |
|
20 |
public class DBOperations { |
21 |
|
22 |
public static final Logger logger = Logger.getLogger (DBOperations.class); |
23 |
public Connection conn = null; |
24 |
|
25 |
public DBOperations() {
|
26 |
// constructor
|
27 |
try {
|
28 |
this.conn = getConnection();
|
29 |
} catch(Exception e) { |
30 |
logger.error("EXC: DBOperations");
|
31 |
logger.error(getFullTrace(e)); |
32 |
this.conn = null; |
33 |
} |
34 |
//// System.out.println("DBOperations()-exit " + this.conn);
|
35 |
} |
36 |
|
37 |
public Connection getConnection() throws Exception { |
38 |
|
39 |
// Connection conn;
|
40 |
// String use_jndi = "N";
|
41 |
//
|
42 |
// Config cfg = Config.getInstance();
|
43 |
// Properties cfgData = cfg.getConfig();
|
44 |
// try {
|
45 |
// use_jndi = cfgData.get("DB_USE_JNDI").toString(); //Y/N
|
46 |
// } catch (Exception e) {
|
47 |
// logger.error("ERR: Property entry not found..");
|
48 |
// }
|
49 |
//
|
50 |
// if ( use_jndi.equals("Y") ) {
|
51 |
// String DB_DATASRC_REF = null;
|
52 |
// try {
|
53 |
// DB_DATASRC_REF = cfgData.get("DB_DATASRC_REF").toString();//java:OracleUpassDS
|
54 |
// } catch (Exception e) {
|
55 |
// logger.error("ERR: Property entry not found..");
|
56 |
// return null;
|
57 |
// }
|
58 |
// InitialContext ctx = new InitialContext();
|
59 |
// DataSource datasource = (DataSource)ctx.lookup(DB_DATASRC_REF);
|
60 |
// conn = datasource.getConnection();
|
61 |
// } else {
|
62 |
// // the below is for self test only
|
63 |
// String driver = null;
|
64 |
// String url = null;
|
65 |
// String username = null;
|
66 |
// String password = null;
|
67 |
//
|
68 |
// try {
|
69 |
// driver = cfgData.get("DB_DRIVER").toString(); //"oracle.jdbc.driver.OracleDriver";
|
70 |
// url = cfgData.get("DB_URL").toString(); //"jdbc:oracle:thin:@127.0.0.1:1521:XE";
|
71 |
// username = cfgData.get("DB_USERNAME").toString(); //"am01";
|
72 |
// password = cfgData.get("DB_PASSWORD").toString(); //"am01";
|
73 |
// } catch (Exception e) {
|
74 |
// logger.error("ERR: Property entry not found..");
|
75 |
// return null;
|
76 |
// }
|
77 |
//
|
78 |
// Class.forName(driver); // load Oracle driver
|
79 |
// conn = DriverManager.getConnection(url, username, password);
|
80 |
// }
|
81 |
Connection conn = HibernateUtils.currentSession().connection();
|
82 |
|
83 |
conn.setAutoCommit(false);
|
84 |
return conn;
|
85 |
} |
86 |
|
87 |
public boolean isValidConnection() throws Exception { |
88 |
if (this.conn == null || this.conn.isClosed()) { |
89 |
System.out.println("ERR: Invalid connection!"); |
90 |
return false; |
91 |
} |
92 |
return true; |
93 |
} |
94 |
|
95 |
public void close() { |
96 |
// System.out.println("Close connection!");
|
97 |
try {
|
98 |
if (this.conn != null) { |
99 |
this.conn.close();
|
100 |
} |
101 |
} catch (Exception e) { |
102 |
logger.error(getFullTrace(e)); |
103 |
} |
104 |
} |
105 |
|
106 |
|
107 |
public ResultSet executeSQLSelect(PreparedStatement pstmt) throws Exception{ |
108 |
|
109 |
ResultSet rs = null; |
110 |
try {
|
111 |
if ( isValidConnection() ) {
|
112 |
if (pstmt == null) |
113 |
return null; |
114 |
rs = pstmt.executeQuery(); |
115 |
} else {
|
116 |
logger.info("ERR: executeSQL: Invalid DB Conn");
|
117 |
return null; |
118 |
}// end conn not valid
|
119 |
}catch(SQLException ex) { |
120 |
logger.error("EXC: executeSQL " + ex.getMessage());
|
121 |
return null; |
122 |
} |
123 |
return rs;
|
124 |
} |
125 |
|
126 |
public int executeSQLUpdate(PreparedStatement pstmt) throws Exception { |
127 |
int rc = 0; |
128 |
try {
|
129 |
if ( isValidConnection() ) {
|
130 |
if (pstmt == null) |
131 |
return 0; |
132 |
rc = pstmt.executeUpdate(); //rc is the row count for Insert/modi/dele
|
133 |
} else {
|
134 |
logger.info("ERR: executeSQLUpdate: Invalid DB Conn");
|
135 |
return 0; |
136 |
}// end conn not valid
|
137 |
}catch(SQLException ex) { |
138 |
logger.error("EXC: executeSQLUpdate " + ex.getMessage());
|
139 |
//
|
140 |
// rollback
|
141 |
//
|
142 |
try {
|
143 |
this.conn.rollback();
|
144 |
logger.info("EXC:1 executeSQLUpdate :: rollback.");
|
145 |
} catch(SQLException rbex) { |
146 |
logger.error("EXC:executeSQLUpdate :: rollback failed! " + rbex.getMessage());
|
147 |
} |
148 |
// rollback completed
|
149 |
|
150 |
return 0; |
151 |
} finally {
|
152 |
//
|
153 |
//this.conn.close();
|
154 |
//
|
155 |
} |
156 |
|
157 |
return rc;
|
158 |
} |
159 |
|
160 |
|
161 |
|
162 |
public static java.sql.Timestamp getCurrentSqlDate() { |
163 |
java.util.Date today = new java.util.Date();
|
164 |
return new java.sql.Timestamp(today.getTime()); |
165 |
} |
166 |
|
167 |
public static java.sql.Timestamp convertSqlDate(java.util.Date d) { |
168 |
if (d == null) { |
169 |
//d = new java.util.Date();
|
170 |
return null; |
171 |
} |
172 |
return new java.sql.Timestamp(d.getTime()); |
173 |
} |
174 |
|
175 |
public long getCurrSeqID(int mode) { |
176 |
|
177 |
|
178 |
// setup the unique ID
|
179 |
long lVal=0; |
180 |
|
181 |
String sqlGetSeqNbr = null; |
182 |
String modeVal = null; |
183 |
|
184 |
String sqlGetNextSeqNbr = "SELECT seq_user_id.NEXTVAL FROM dual"; |
185 |
String nextVal = "NEXTVAL"; |
186 |
|
187 |
String sqlGetCurrSeqNbr = "SELECT seq_user_id.CURRVAL FROM dual"; |
188 |
String currVal = "CURRVAL"; |
189 |
|
190 |
|
191 |
if (mode == Constants.MODE_QCURRVAL) {
|
192 |
sqlGetSeqNbr = sqlGetCurrSeqNbr; |
193 |
modeVal = currVal; |
194 |
} else {
|
195 |
sqlGetSeqNbr = sqlGetNextSeqNbr; |
196 |
modeVal = nextVal; |
197 |
} |
198 |
|
199 |
ResultSet rs = null; |
200 |
Statement stmt = null; |
201 |
try {
|
202 |
stmt = this.conn.createStatement();
|
203 |
rs = stmt.executeQuery(sqlGetSeqNbr); |
204 |
if (rs.next()) {
|
205 |
lVal = rs.getInt(modeVal); |
206 |
logger.info("seq_user_id.value=" + lVal);
|
207 |
} |
208 |
} catch (SQLException ex) { |
209 |
logger.error("SQLEXC: getCurrSeqID " + ex.getMessage());
|
210 |
} finally {
|
211 |
logger.info(": getCurrSeqID Final");
|
212 |
try {
|
213 |
rs.close(); |
214 |
stmt.close(); |
215 |
//this.conn.close();
|
216 |
} catch (SQLException ex) { |
217 |
logger.error (getFullTrace (ex)); |
218 |
} |
219 |
} |
220 |
return lVal;
|
221 |
} |
222 |
|
223 |
public long getSqlServerSeqID() { |
224 |
|
225 |
// setup the unique ID
|
226 |
BigDecimal nextValue = new BigDecimal(0); |
227 |
CallableStatement cstmt = null; |
228 |
try {
|
229 |
cstmt = conn.prepareCall(//
|
230 |
"{call nextval_SEQ_USER_ID(?)}");
|
231 |
cstmt.registerOutParameter(1, java.sql.Types.BIGINT);
|
232 |
cstmt.execute(); |
233 |
nextValue = cstmt.getBigDecimal(1);
|
234 |
logger.info("seq_user_id.value=" + nextValue);
|
235 |
} catch (SQLException ex) { |
236 |
logger.error("SQLEXC: getSqlServerSeqID " + ex.getMessage());
|
237 |
} finally {
|
238 |
logger.info(": getSqlServerSeqID Final");
|
239 |
try {
|
240 |
cstmt.close(); |
241 |
} catch (SQLException ex) { |
242 |
logger.error (getFullTrace (ex)); |
243 |
} |
244 |
} |
245 |
return nextValue.longValue ();
|
246 |
} |
247 |
|
248 |
///////////////////////////////////////////////////////////////////////
|
249 |
// User Bean
|
250 |
///////////////////////////////////////////////////////////////////////
|
251 |
|
252 |
/**
|
253 |
*
|
254 |
*/
|
255 |
public UserBean getUserFromStore(String userAlias) { |
256 |
return getUserFromStore(userAlias, Constants.MODE_QUSERBEAN);
|
257 |
} |
258 |
|
259 |
public UserBean getUserFromStore(String userAlias, int sqlMode) { |
260 |
|
261 |
String sqlStmt0 = "SELECT * FROM tb_am_user WHERE u_user_alias = ?"; |
262 |
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"; |
263 |
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"; |
264 |
|
265 |
String sqlStmt = null; |
266 |
|
267 |
switch (sqlMode) {
|
268 |
case Constants.MODE_QUSERBEAN: sqlStmt = sqlStmt0; break; |
269 |
case Constants.MODE_QTACBEAN: sqlStmt = sqlStmt1; break; |
270 |
case Constants.MODE_QVASCOBEAN: sqlStmt = sqlStmt2; break; |
271 |
} |
272 |
|
273 |
|
274 |
ResultSet rs = null; |
275 |
PreparedStatement pstmt = null; |
276 |
|
277 |
UserBean ub = new UserBean();
|
278 |
|
279 |
try {
|
280 |
pstmt = this.conn.prepareStatement(sqlStmt);
|
281 |
pstmt.setString(1, userAlias);
|
282 |
rs = executeSQLSelect(pstmt); |
283 |
|
284 |
if (rs.next()) {
|
285 |
ub.setUserID( rs.getLong("U_USER_ID") );
|
286 |
ub.setUserAlias(rs.getString("U_USER_ALIAS"));
|
287 |
ub.setUserType(rs.getInt("U_USER_TYPE"));
|
288 |
ub.setDescription(rs.getString("U_USER_DESC"));
|
289 |
ub.setUdateCreated( rs.getTimestamp("U_DATE_CREATED"));
|
290 |
ub.setUdateLastUsed(rs.getTimestamp("U_DATE_LAST_USED"));
|
291 |
ub.setUuseCount( rs.getInt("U_USE_COUNT"));
|
292 |
ub.setUdateLastActivated(rs.getTimestamp("U_DATE_LAST_ACT"));
|
293 |
ub.setUdateLastLocked(rs.getTimestamp("U_DATE_LAST_LOCK"));
|
294 |
ub.setUstate(rs.getInt("U_USER_STATE"));
|
295 |
ub.setUdateLockedFrom(rs.getTimestamp("U_DATE_LOCK_FROM"));
|
296 |
ub.setUdateLockedTo(rs.getTimestamp("U_DATE_LOCK_TO"));
|
297 |
ub.setPcipherText(rs.getString("P_PASSWD_CIPHERTEXT"));
|
298 |
ub.setPstate(rs.getInt("P_PASSWD_STATE"));
|
299 |
ub.setPdateCreated(rs.getTimestamp("P_DATE_GENERATED"));
|
300 |
ub.setPdateFirstUsed(rs.getTimestamp("P_DATE_FIRST_USED"));
|
301 |
ub.setPdateLastUsed(rs.getTimestamp("P_DATE_LAST_USED"));
|
302 |
ub.setPuseCount(rs.getInt("P_USE_COUNT"));
|
303 |
ub.setPerrorCount(rs.getInt("P_ERROR_COUNT"));
|
304 |
ub.setPexpiredStatus(rs.getInt("P_EXPIRED_STATUS"));
|
305 |
ub.setPdateExpired(rs.getTimestamp("P_DATE_EXPIRED"));
|
306 |
ub.setPhistoryList(rs.getString("P_PASSWD_HIST"));
|
307 |
|
308 |
|
309 |
switch (sqlMode) {
|
310 |
case Constants.MODE_QUSERBEAN:
|
311 |
break;
|
312 |
case Constants.MODE_QTACBEAN:
|
313 |
TacBean tb = new TacBean();
|
314 |
tb.setCipherText(rs.getString("T_TAC_CIPHERTEXT"));
|
315 |
tb.setState(rs.getInt("T_TAC_STATE"));
|
316 |
tb.setDateCreated(rs.getTimestamp("T_DATE_GENERATED"));
|
317 |
tb.setDateFirstUsed(rs.getTimestamp("T_DATE_FIRST_USED"));
|
318 |
tb.setDateLastUsed(rs.getTimestamp("T_DATE_LAST_USED"));
|
319 |
tb.setUseCount(rs.getInt("T_USE_COUNT"));
|
320 |
tb.setErrorCount(rs.getInt("T_ERROR_COUNT"));
|
321 |
break;
|
322 |
|
323 |
case Constants.MODE_QVASCOBEAN: sqlStmt = sqlStmt2;
|
324 |
break;
|
325 |
} |
326 |
|
327 |
|
328 |
|
329 |
} else { //no record found |
330 |
logger.info("ERR: no record found!");
|
331 |
ub = null;
|
332 |
} //
|
333 |
} catch (SQLException ex) { |
334 |
logger.error("SQLEXC:1 getUserFromStore " + ex.getMessage());
|
335 |
ub = null;
|
336 |
} catch (Exception e) { |
337 |
logger.error("EXC:1 getUserFromStore " + e.getMessage());
|
338 |
ub = null;
|
339 |
} finally { // close database resources |
340 |
try {
|
341 |
rs.close(); |
342 |
pstmt.close(); |
343 |
//this.conn.close();
|
344 |
} catch (SQLException ex) { |
345 |
logger.error("SQLException:Final getUserFromStore " + ex.getMessage());
|
346 |
} catch (Exception e) { |
347 |
logger.error("Exception:Final getUserFromStore " + e.getMessage());
|
348 |
} |
349 |
}//finally--
|
350 |
|
351 |
return ub;
|
352 |
} |
353 |
|
354 |
/**
|
355 |
*
|
356 |
*/
|
357 |
public boolean updateUserToStore(UserBean ub) { |
358 |
|
359 |
boolean rc = false; |
360 |
String sqlStmt = "UPDATE tb_am_user SET " + |
361 |
"U_USER_TYPE=?, U_USER_DESC=?, U_DATE_LAST_USED=?, U_USE_COUNT=?, U_DATE_LAST_ACT=?, " +
|
362 |
"U_DATE_LAST_LOCK=?, U_USER_STATE=?, U_DATE_LOCK_FROM=?, U_DATE_LOCK_TO=?, " +
|
363 |
"P_PASSWD_CIPHERTEXT=?, P_PASSWD_STATE=?, P_DATE_GENERATED=?, P_DATE_FIRST_USED=?, " +
|
364 |
"P_DATE_LAST_USED=?, P_USE_COUNT=?, P_ERROR_COUNT=?, P_EXPIRED_STATUS=?, P_DATE_EXPIRED=?, " +
|
365 |
"P_PASSWD_HIST=? WHERE u_user_id = ?";
|
366 |
int nRow = 0; |
367 |
|
368 |
// insert into DB
|
369 |
PreparedStatement pstmt = null; |
370 |
try {
|
371 |
pstmt = this.conn.prepareStatement(sqlStmt);
|
372 |
pstmt.setInt(1,ub.getUserType());
|
373 |
pstmt.setString(2, ub.getDescription());
|
374 |
|
375 |
java.sql.Timestamp today = getCurrentSqlDate(); |
376 |
pstmt.setTimestamp(3, today); //last used |
377 |
pstmt.setInt(4, ub.getUuseCount()+1); |
378 |
pstmt.setTimestamp(5, convertSqlDate(ub.getUdateLastActivated())); //last act |
379 |
pstmt.setTimestamp(6, convertSqlDate(ub.getUdateLastLocked())); //last lock |
380 |
|
381 |
pstmt.setInt(7,ub.getUstate());
|
382 |
pstmt.setTimestamp(8, convertSqlDate(ub.getUdateLockedFrom())); // lock ft |
383 |
pstmt.setTimestamp(9, convertSqlDate(ub.getUdateLockedTo())); //lock to |
384 |
pstmt.setString(10, ub.getPcipherText());
|
385 |
pstmt.setInt(11,ub.getPstate());
|
386 |
pstmt.setTimestamp(12, convertSqlDate(ub.getUdateCreated() )); //generated |
387 |
pstmt.setTimestamp(13, convertSqlDate(ub.getPdateFirstUsed()) ); //1st used |
388 |
pstmt.setTimestamp(14, convertSqlDate(ub.getPdateLastUsed()) ); //1ast used |
389 |
pstmt.setInt(15,ub.getPuseCount());
|
390 |
pstmt.setInt(16,ub.getPerrorCount());
|
391 |
pstmt.setInt(17,ub.getPexpiredStatus());
|
392 |
pstmt.setTimestamp(18, convertSqlDate(ub.getPdateExpired()) );
|
393 |
pstmt.setString(19, ub.getPhistoryList());
|
394 |
|
395 |
pstmt.setLong(20, ub.getUserID());
|
396 |
|
397 |
nRow = executeSQLUpdate(pstmt); |
398 |
//// System.out.println(nRow + " Row modified to table Key=" + ub.getUserID() + "/" + ub.getUserAlias());
|
399 |
|
400 |
if (nRow < 1) { |
401 |
System.out.println("ERR: no row modified into user table!"); |
402 |
//
|
403 |
// rollback
|
404 |
//
|
405 |
try {
|
406 |
this.conn.rollback();
|
407 |
logger.info("INFO:1 updateUserToStore :: rollback.");
|
408 |
} catch(Exception rbe) { |
409 |
logger.error("EXC:updateUserToStore :: rollback failed!");
|
410 |
} |
411 |
// rollback completed
|
412 |
rc = false;
|
413 |
} else {
|
414 |
////System.out.println("GOOD: perf db commit!");
|
415 |
this.conn.commit();
|
416 |
rc = true;
|
417 |
} |
418 |
|
419 |
} catch (Exception e) { |
420 |
logger.error("EXC:1 updateUserToStore");
|
421 |
logger.error(getFullTrace(e)); |
422 |
rc = false;
|
423 |
} finally { // close database resources |
424 |
try {
|
425 |
pstmt.close(); |
426 |
//this.conn.close();
|
427 |
} catch (Exception e) { |
428 |
logger.error("EXC:Final updateUserToStore");
|
429 |
logger.error(getFullTrace(e)); |
430 |
} |
431 |
} //finally--
|
432 |
|
433 |
return rc;
|
434 |
} |
435 |
|
436 |
/**
|
437 |
*
|
438 |
*/
|
439 |
public boolean insertUserToStore(UserBean ub) { |
440 |
boolean rc = false; |
441 |
int nRow = 0; |
442 |
|
443 |
long nextVal= getSqlServerSeqID();
|
444 |
|
445 |
String sqlStmt1 = "INSERT INTO tb_am_usermas (m_user_alias, m_user_id) VALUES (?, ?)"; |
446 |
|
447 |
//TODO: use buff string.. later
|
448 |
// String sqlStmt2 = "INSERT ALL " +
|
449 |
// "INTO tb_am_user (U_USER_ID, U_USER_ALIAS, U_USER_TYPE, U_USER_DESC, U_USER_STATE," +
|
450 |
// "P_PASSWD_CIPHERTEXT, P_DATE_EXPIRED, P_PASSWD_HIST) VALUES (?, ?, ?, ?, ?, ?, ?, ?) " +
|
451 |
// "INTO tb_am_tac (T_USER_ID) VALUES (?) SELECT * FROM dual";
|
452 |
String sqlStmt2 = "INSERT " + |
453 |
"INTO tb_am_user (U_USER_ID, U_USER_ALIAS, U_USER_TYPE, U_USER_DESC, U_USER_STATE," +
|
454 |
"P_PASSWD_CIPHERTEXT, P_DATE_EXPIRED, P_PASSWD_HIST, U_DATE_CREATED, U_USE_COUNT, P_PASSWD_STATE, P_DATE_GENERATED, P_USE_COUNT, P_ERROR_COUNT, P_EXPIRED_STATUS) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
|
455 |
|
456 |
String sqlStmt3 = "INSERT INTO tb_am_tac (T_USER_ID, T_TAC_STATE, T_DATE_GENERATED, T_USE_COUNT, T_ERROR_COUNT) VALUES (?, ?, ?, ?, ?)"; |
457 |
|
458 |
// insert into DB
|
459 |
PreparedStatement pstmt = null; |
460 |
try {
|
461 |
pstmt = this.conn.prepareStatement(sqlStmt1);
|
462 |
pstmt.setString(1, ub.getUserAlias());
|
463 |
pstmt.setLong(2, nextVal);
|
464 |
|
465 |
nRow = executeSQLUpdate(pstmt); |
466 |
//// System.out.println(nRow + " Row inserted to table");
|
467 |
|
468 |
if (nRow < 1) { |
469 |
logger.info("ERR: no row inserted into master table!");
|
470 |
rc = false;
|
471 |
} else {
|
472 |
pstmt = this.conn.prepareStatement(sqlStmt2);
|
473 |
pstmt.setLong(1, nextVal);
|
474 |
pstmt.setString(2, ub.getUserAlias());
|
475 |
pstmt.setInt(3,ub.getUserType());
|
476 |
pstmt.setString(4, ub.getDescription());
|
477 |
pstmt.setInt(5,ub.getUstate());
|
478 |
pstmt.setString(6, ub.getPcipherText());
|
479 |
pstmt.setTimestamp(7, convertSqlDate(ub.getPdateExpired()) );
|
480 |
pstmt.setString(8, ub.getPhistoryList());
|
481 |
pstmt.setTimestamp(9, convertSqlDate(new Date())); |
482 |
pstmt.setInt(10, 0); |
483 |
pstmt.setInt(11, 0); |
484 |
pstmt.setTimestamp(12, convertSqlDate(new Date())); |
485 |
pstmt.setInt(13, 0); |
486 |
pstmt.setInt(14, 0); |
487 |
pstmt.setInt(15, 1); |
488 |
|
489 |
nRow = executeSQLUpdate(pstmt); |
490 |
|
491 |
logger.info(nRow + " Row inserted to table");
|
492 |
|
493 |
pstmt = this.conn.prepareStatement(sqlStmt3);
|
494 |
pstmt.setLong(1, nextVal);
|
495 |
pstmt.setLong(2, 0); |
496 |
pstmt.setTimestamp (3, convertSqlDate(new Date())); |
497 |
pstmt.setInt(4, 0); |
498 |
pstmt.setInt(5, 0); |
499 |
nRow = executeSQLUpdate(pstmt); |
500 |
|
501 |
logger.info(nRow + " Row inserted to table");
|
502 |
|
503 |
if (nRow < 1) { |
504 |
logger.info("ERR: no row inserted into user table!");
|
505 |
//
|
506 |
// rollback
|
507 |
//
|
508 |
try {
|
509 |
this.conn.rollback();
|
510 |
logger.info("INFO:1 insertUserToStore :: rollback.");
|
511 |
} catch(Exception rbe) { |
512 |
logger.error("EXC:insertUserToStore :: rollback failed!");
|
513 |
} |
514 |
// rollback completed
|
515 |
rc = false;
|
516 |
} else {
|
517 |
////System.out.println("GOOD: perf db commit!");
|
518 |
this.conn.commit();
|
519 |
rc = true;
|
520 |
} |
521 |
} |
522 |
|
523 |
} catch (Exception e) { |
524 |
logger.error(getFullTrace(e)); |
525 |
logger.error("EXC:1 insertUserToStore");
|
526 |
rc = false;
|
527 |
} finally { // close database resources |
528 |
try {
|
529 |
pstmt.close(); |
530 |
//this.conn.close();
|
531 |
} catch (Exception e) { |
532 |
logger.error("EXC:Final insertUserToStore");
|
533 |
logger.error(getFullTrace(e)); |
534 |
} |
535 |
}//finally--
|
536 |
|
537 |
return rc;
|
538 |
} |
539 |
|
540 |
///////////////////////////////////////////////////////////////////////
|
541 |
// TAC Bean
|
542 |
///////////////////////////////////////////////////////////////////////
|
543 |
|
544 |
public TacBean getTacFromStore(String userAlias) { |
545 |
|
546 |
//String sqlStmt0 = "SELECT * FROM tb_am_user WHERE u_user_alias = ?";
|
547 |
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"; |
548 |
//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";
|
549 |
|
550 |
String sqlStmt = null; |
551 |
|
552 |
sqlStmt = sqlStmt1; |
553 |
/*
|
554 |
switch (sqlMode) {
|
555 |
case Constants.MODE_QUSERBEAN: sqlStmt = sqlStmt0; break;
|
556 |
case Constants.MODE_QTACBEAN: sqlStmt = sqlStmt1; break;
|
557 |
case Constants.MODE_QVASCOBEAN: sqlStmt = sqlStmt2; break;
|
558 |
}*/
|
559 |
|
560 |
|
561 |
ResultSet rs = null; |
562 |
PreparedStatement pstmt = null; |
563 |
|
564 |
TacBean tb = new TacBean();
|
565 |
|
566 |
try {
|
567 |
pstmt = this.conn.prepareStatement(sqlStmt);
|
568 |
pstmt.setString(1, userAlias);
|
569 |
rs = executeSQLSelect(pstmt); |
570 |
|
571 |
if (rs.next()) {
|
572 |
tb.setUserID(rs.getLong("T_USER_ID"));
|
573 |
tb.setCipherText(rs.getString("T_TAC_CIPHERTEXT"));
|
574 |
tb.setState(rs.getInt("T_TAC_STATE"));
|
575 |
tb.setDateCreated(rs.getTimestamp("T_DATE_GENERATED"));
|
576 |
tb.setDateFirstUsed(rs.getTimestamp("T_DATE_FIRST_USED"));
|
577 |
tb.setDateLastUsed(rs.getTimestamp("T_DATE_LAST_USED"));
|
578 |
tb.setUseCount(rs.getInt("T_USE_COUNT"));
|
579 |
tb.setErrorCount(rs.getInt("T_ERROR_COUNT"));
|
580 |
tb.setSessionId(rs.getString("T_SESSION_ID"));
|
581 |
|
582 |
} else { //no record found |
583 |
logger.info("ERR: no record found!");
|
584 |
tb = null;
|
585 |
} //
|
586 |
} catch (SQLException ex) { |
587 |
logger.error("SQLEXC:1 getTacFromStore " + ex.getMessage());
|
588 |
tb = null;
|
589 |
} catch (Exception e) { |
590 |
logger.error("EXC:1 getTacFromStore " + e.getMessage());
|
591 |
tb = null;
|
592 |
} finally { // close database resources |
593 |
try {
|
594 |
rs.close(); |
595 |
pstmt.close(); |
596 |
//this.conn.close();
|
597 |
} catch (SQLException ex) { |
598 |
logger.error("EXC:Final getTacFromStore " + ex.getMessage());
|
599 |
} |
600 |
}//finally--
|
601 |
|
602 |
return tb;
|
603 |
} |
604 |
|
605 |
public boolean updateTacToStore(TacBean tb) { |
606 |
|
607 |
boolean rc = false; |
608 |
String sqlStmt = "UPDATE tb_am_tac SET " + |
609 |
"T_TAC_CIPHERTEXT=?, T_TAC_STATE=?, T_DATE_GENERATED=?, " +
|
610 |
"T_DATE_FIRST_USED=?, T_DATE_LAST_USED=?, T_USE_COUNT=?, T_ERROR_COUNT=?, T_SESSION_ID=?"+
|
611 |
"WHERE t_user_id = ?";
|
612 |
int nRow = 0; |
613 |
|
614 |
// insert into DB
|
615 |
PreparedStatement pstmt = null; |
616 |
try {
|
617 |
pstmt = this.conn.prepareStatement(sqlStmt);
|
618 |
pstmt.setString(1, tb.getCipherText());
|
619 |
pstmt.setInt(2, tb.getState());
|
620 |
|
621 |
//java.sql.Timestamp today = getCurrentSqlDate();
|
622 |
pstmt.setTimestamp(3, convertSqlDate(tb.getDateCreated()) ); //generated |
623 |
pstmt.setTimestamp(4, convertSqlDate(tb.getDateFirstUsed()) ); //1st used |
624 |
pstmt.setTimestamp(5, convertSqlDate(tb.getDateLastUsed()) ); //1ast used |
625 |
pstmt.setInt(6,tb.getUseCount());
|
626 |
pstmt.setInt(7,tb.getErrorCount());
|
627 |
pstmt.setString(8, tb.getSessionId());
|
628 |
pstmt.setLong(9, tb.getUserID());
|
629 |
|
630 |
nRow = executeSQLUpdate(pstmt); |
631 |
//// System.out.println(nRow + " Row modified to table Key=" + tb.getTUserID());
|
632 |
|
633 |
if (nRow < 1) { |
634 |
logger.info("ERR: no row modified into TAC table!");
|
635 |
//
|
636 |
// rollback
|
637 |
//
|
638 |
try {
|
639 |
this.conn.rollback();
|
640 |
logger.info("INFO:1 updateTacToStore :: rollback.");
|
641 |
} catch(Exception rbe) { |
642 |
logger.error("EXC:updateTacToStore :: rollback failed!");
|
643 |
} |
644 |
// rollback completed
|
645 |
rc = false;
|
646 |
} else {
|
647 |
////System.out.println("GOOD: perf db commit!");
|
648 |
this.conn.commit();
|
649 |
rc = true;
|
650 |
} |
651 |
|
652 |
} catch (Exception e) { |
653 |
logger.error("EXC:1 updateTacToStore");
|
654 |
logger.error(getFullTrace(e)); |
655 |
rc = false;
|
656 |
} finally { // close database resources |
657 |
try {
|
658 |
pstmt.close(); |
659 |
//this.conn.close();
|
660 |
} catch (Exception e) { |
661 |
logger.error("EXC:Final updateTacToStore");
|
662 |
logger.error(getFullTrace(e)); |
663 |
} |
664 |
} //finally--
|
665 |
|
666 |
return rc;
|
667 |
} |
668 |
|
669 |
|
670 |
/**
|
671 |
* TOKEN Operations
|
672 |
*/
|
673 |
public boolean insertTokenToStore(TokenBean tnb) { |
674 |
boolean rc = false; |
675 |
int nRow = 0; |
676 |
|
677 |
/*
|
678 |
01 V_SERIAL_NO NOT NULL VARCHAR2(22)
|
679 |
02 V_USER_ID NOT NULL NUMBER(9)
|
680 |
03 V_DP_MODEL VARCHAR2(5)
|
681 |
04 V_DP_AUTH_MODE VARCHAR2(2)
|
682 |
05 V_DP_CIPHERTEXT NOT NULL BLOB
|
683 |
06 V_BAK_CIPHERTEXT NOT NULL BLOB
|
684 |
07 V_DP_ASSIGNED NOT NULL NUMBER(1)
|
685 |
08 V_DATE_ASSIGNED TIMESTAMP(6)
|
686 |
09 V_DATE_FIRST_USED TIMESTAMP(6)
|
687 |
10 V_DATE_LAST_USED TIMESTAMP(6)
|
688 |
11 V_USE_COUNT NOT NULL NUMBER(6)
|
689 |
12 V_ERROR_COUNT NOT NULL NUMBER(2)
|
690 |
13 V_DP_STATE NOT NULL NUMBER(1)
|
691 |
14 V_BATCH_NO NOT NULL NUMBER(9)
|
692 |
*/
|
693 |
|
694 |
//long nextVal= getCurrSeqID( Constants.MODE_QNEXTVAL);
|
695 |
|
696 |
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," + |
697 |
"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) " +
|
698 |
"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
|
699 |
|
700 |
// insert into DB
|
701 |
PreparedStatement pstmt = null; |
702 |
try {
|
703 |
pstmt = this.conn.prepareStatement(sqlStmt);
|
704 |
pstmt.setString(1, tnb.getVserialNumber());
|
705 |
pstmt.setLong(2, tnb.getVuserID());
|
706 |
pstmt.setString(3, tnb.getVdpModel());
|
707 |
pstmt.setString(4, tnb.getVdpAuthMode());
|
708 |
pstmt.setBytes(5, tnb.getVdpCipherText());
|
709 |
pstmt.setBytes(6, tnb.getVbkCipherText());
|
710 |
pstmt.setInt(7, tnb.getVassigned());
|
711 |
pstmt.setTimestamp(8, convertSqlDate( tnb.getVdateAssigned() ) );
|
712 |
pstmt.setTimestamp(9, convertSqlDate( tnb.getVdateFirstUsed() ) );
|
713 |
pstmt.setTimestamp(10, convertSqlDate( tnb.getVdateLastUsed() ) );
|
714 |
pstmt.setInt(11, tnb.getVuseCount());
|
715 |
pstmt.setInt(12, tnb.getVerrorCount());
|
716 |
pstmt.setInt(13, tnb.getVstate());
|
717 |
pstmt.setString(14, tnb.getVbatchNo());
|
718 |
|
719 |
nRow = executeSQLUpdate(pstmt); |
720 |
//// System.out.println(nRow + " Row inserted to table");
|
721 |
|
722 |
if (nRow < 1) { |
723 |
logger.info("ERR: no row inserted into token table!");
|
724 |
//
|
725 |
// rollback
|
726 |
//
|
727 |
try {
|
728 |
this.conn.rollback();
|
729 |
logger.error("INFO:1 insertTokenToStore :: rollback.");
|
730 |
} catch(Exception rbe) { |
731 |
logger.error("EXC:insertTokenToStore :: rollback failed!");
|
732 |
} |
733 |
// rollback completed
|
734 |
rc = false;
|
735 |
} else {
|
736 |
////System.out.println("GOOD: perf db commit!");
|
737 |
this.conn.commit();
|
738 |
rc = true;
|
739 |
} |
740 |
|
741 |
|
742 |
} catch (Exception e) { |
743 |
//logger.error(getFullTrace(e));
|
744 |
logger.error("EXC:1 insertTokenToStore");
|
745 |
rc = false;
|
746 |
} finally { // close database resources |
747 |
try {
|
748 |
pstmt.close(); |
749 |
//this.conn.close();
|
750 |
} catch (Exception e) { |
751 |
logger.error("EXC:Final insertTokenToStore");
|
752 |
logger.error(getFullTrace(e)); |
753 |
} |
754 |
}//finally--
|
755 |
|
756 |
return rc;
|
757 |
} |
758 |
|
759 |
public TokenBean getTokenFromStoreBySerialNumber(String serialNumber) { |
760 |
String sqlStmt = "SELECT * FROM tb_am_vasco WHERE v_serial_no = ?"; |
761 |
return getTokenFromStore(sqlStmt, serialNumber);
|
762 |
} |
763 |
|
764 |
public TokenBean getTokenFromStoreByUserAlias(String userAlias) { |
765 |
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"; |
766 |
return getTokenFromStore(sqlStmt, userAlias);
|
767 |
} |
768 |
|
769 |
private TokenBean getTokenFromStore(String sqlStmt, String dbKey) { |
770 |
|
771 |
ResultSet rs = null; |
772 |
PreparedStatement pstmt = null; |
773 |
|
774 |
TokenBean tb = new TokenBean();
|
775 |
|
776 |
try {
|
777 |
pstmt = this.conn.prepareStatement(sqlStmt);
|
778 |
pstmt.setString(1, dbKey);
|
779 |
rs = executeSQLSelect(pstmt); |
780 |
|
781 |
if (rs.next()) {
|
782 |
|
783 |
tb.setVserialNumber(rs.getString("V_SERIAL_NO"));
|
784 |
tb.setVuserID(rs.getLong("V_USER_ID"));
|
785 |
tb.setVdpModel(rs.getString("V_DP_MODEL"));
|
786 |
tb.setVdpAuthMode(rs.getString("V_DP_AUTH_MODE"));
|
787 |
tb.setVdpCipherText(rs.getBytes("V_DP_CIPHERTEXT"));
|
788 |
tb.setVbkCipherText(rs.getBytes("V_BAK_CIPHERTEXT"));
|
789 |
tb.setVassigned(rs.getInt("V_DP_ASSIGNED"));
|
790 |
tb.setVdateAssigned(rs.getTimestamp("V_DATE_ASSIGNED"));
|
791 |
tb.setVdateFirstUsed(rs.getTimestamp("V_DATE_FIRST_USED"));
|
792 |
tb.setVdateLastUsed(rs.getTimestamp("V_DATE_LAST_USED"));
|
793 |
tb.setVuseCount(rs.getInt("V_USE_COUNT"));
|
794 |
tb.setVerrorCount(rs.getInt("V_ERROR_COUNT"));
|
795 |
tb.setVstate(rs.getInt("V_DP_STATE"));
|
796 |
tb.setVbatchNo(rs.getString("V_BATCH_NO"));
|
797 |
} else { //no record found |
798 |
logger.info("ERR: no record found! " + dbKey);
|
799 |
tb = null;
|
800 |
} //
|
801 |
} catch (SQLException ex) { |
802 |
logger.error("SQLEXC:1 getTokenFromStore " + ex.getMessage());
|
803 |
tb = null;
|
804 |
} catch (Exception e) { |
805 |
logger.error("EXC:1 getTokenFromStore " + e.getMessage());
|
806 |
tb = null;
|
807 |
} finally { // close database resources |
808 |
try {
|
809 |
rs.close(); |
810 |
pstmt.close(); |
811 |
//this.conn.close();
|
812 |
} catch (SQLException ex) { |
813 |
logger.error("EXC:Final getTokenFromStore " + ex.getMessage());
|
814 |
} |
815 |
}//finally--
|
816 |
|
817 |
return tb;
|
818 |
} |
819 |
|
820 |
public boolean updateTokenToStore(TokenBean tb) { |
821 |
|
822 |
/*
|
823 |
* Backup BLOB (V_BAK_CIPHERTEXT) is read only.
|
824 |
*/
|
825 |
|
826 |
boolean rc = false; |
827 |
String sqlStmt = "UPDATE tb_am_vasco SET " + |
828 |
"V_USER_ID=?, V_DP_MODEL=?, V_DP_AUTH_MODE=?, V_DP_CIPHERTEXT=?, V_DP_ASSIGNED=?, V_DATE_ASSIGNED=?," +
|
829 |
"V_DATE_FIRST_USED=?, V_DATE_LAST_USED=?, V_USE_COUNT=?, V_ERROR_COUNT=?, V_DP_STATE=?, V_BATCH_NO=?" +
|
830 |
" WHERE V_SERIAL_NO = ?";
|
831 |
int nRow = 0; |
832 |
|
833 |
// insert into DB
|
834 |
PreparedStatement pstmt = null; |
835 |
try {
|
836 |
pstmt = this.conn.prepareStatement(sqlStmt);
|
837 |
|
838 |
pstmt.setLong(1, tb.getVuserID());
|
839 |
pstmt.setString(2, tb.getVdpModel());
|
840 |
pstmt.setString(3, tb.getVdpAuthMode());
|
841 |
pstmt.setBytes(4, tb.getVdpCipherText());
|
842 |
pstmt.setInt(5, tb.getVassigned());
|
843 |
pstmt.setTimestamp(6, convertSqlDate(tb.getVdateAssigned()) ); //1st used |
844 |
pstmt.setTimestamp(7, convertSqlDate(tb.getVdateFirstUsed()) ); //1st used |
845 |
pstmt.setTimestamp(8, convertSqlDate(tb.getVdateLastUsed()) ); //1ast used |
846 |
pstmt.setInt(9,tb.getVuseCount());
|
847 |
pstmt.setInt(10,tb.getVerrorCount());
|
848 |
pstmt.setInt(11, tb.getVstate());
|
849 |
pstmt.setString(12, tb.getVbatchNo());
|
850 |
pstmt.setString(13, tb.getVserialNumber());
|
851 |
|
852 |
nRow = executeSQLUpdate(pstmt); |
853 |
//// System.out.println(nRow + " Row modified to table Key=" + tb.getVSerialNumber());
|
854 |
|
855 |
if (nRow < 1) { |
856 |
logger.info("ERR: no row modified into user table!");
|
857 |
//
|
858 |
// rollback
|
859 |
//
|
860 |
try {
|
861 |
this.conn.rollback();
|
862 |
logger.info("INFO:1 updateTokenToStore :: rollback.");
|
863 |
} catch(Exception rbe) { |
864 |
logger.error("EXC:updateTokenToStore :: rollback failed!");
|
865 |
} |
866 |
// rollback completed
|
867 |
rc = false;
|
868 |
} else {
|
869 |
////System.out.println("GOOD: perf db commit!");
|
870 |
this.conn.commit();
|
871 |
rc = true;
|
872 |
} |
873 |
|
874 |
} catch (Exception e) { |
875 |
logger.error("EXC:1 updateTokenToStore");
|
876 |
logger.error(getFullTrace(e)); |
877 |
rc = false;
|
878 |
} finally { // close database resources |
879 |
try {
|
880 |
pstmt.close(); |
881 |
//this.conn.close();
|
882 |
} catch (Exception e) { |
883 |
logger.error("EXC:Final updateTokenToStore");
|
884 |
logger.error(getFullTrace(e)); |
885 |
} |
886 |
} //finally--
|
887 |
|
888 |
return rc;
|
889 |
} |
890 |
|
891 |
public boolean deleteTokenFromStoreBySerialNumber(String serialNumber) { |
892 |
|
893 |
boolean rc = false; |
894 |
String sqlStmt = "DELETE FROM tb_am_vasco WHERE V_SERIAL_NO = ?"; |
895 |
int nRow = 0; |
896 |
|
897 |
// insert into DB
|
898 |
PreparedStatement pstmt = null; |
899 |
try {
|
900 |
pstmt = this.conn.prepareStatement(sqlStmt);
|
901 |
pstmt.setString(1, serialNumber);
|
902 |
nRow = executeSQLUpdate(pstmt); |
903 |
|
904 |
// System.out.println(nRow + " Row deleted From table Key=" + serialNumber);
|
905 |
|
906 |
if (nRow < 1) { |
907 |
logger.info("ERR: no row deleted from token table!");
|
908 |
//
|
909 |
// rollback
|
910 |
//
|
911 |
try {
|
912 |
this.conn.rollback();
|
913 |
logger.info("INFO:1 deleteTokenFromStoreBySerialNumber :: rollback.");
|
914 |
} catch(Exception rbe) { |
915 |
logger.error("EXC:deleteTokenFromStoreBySerialNumber :: rollback failed!");
|
916 |
} |
917 |
// rollback completed
|
918 |
rc = false;
|
919 |
} else {
|
920 |
////System.out.println("GOOD: perf db commit!");
|
921 |
this.conn.commit();
|
922 |
rc = true;
|
923 |
} |
924 |
|
925 |
} catch (Exception e) { |
926 |
logger.error("EXC:1 deleteTokenFromStoreBySerialNumber");
|
927 |
logger.error(getFullTrace(e)); |
928 |
rc = false;
|
929 |
} finally { // close database resources |
930 |
try {
|
931 |
pstmt.close(); |
932 |
//this.conn.close();
|
933 |
} catch (Exception e) { |
934 |
logger.error("EXC:Final deleteTokenFromStoreBySerialNumber");
|
935 |
logger.error(getFullTrace(e)); |
936 |
} |
937 |
} //finally--
|
938 |
|
939 |
return rc;
|
940 |
|
941 |
} |
942 |
|
943 |
public static String getFullTrace (Exception e) |
944 |
{ |
945 |
String newLine = "\r\n"; |
946 |
StringBuffer m_sErrorMessage = new StringBuffer (); |
947 |
|
948 |
m_sErrorMessage.append (e.getMessage ()); |
949 |
|
950 |
m_sErrorMessage.append (newLine); |
951 |
StackTraceElement[] m_oElements = e.getStackTrace (); |
952 |
m_sErrorMessage.append ("Exception class : "
|
953 |
+ m_oElements[0].getClassName () + ", class method : " |
954 |
+ m_oElements[0].getMethodName () + ", line number : " |
955 |
+ m_oElements[0].getLineNumber ());
|
956 |
m_sErrorMessage.append (newLine); |
957 |
|
958 |
for (int i = 0; i < m_oElements.length; i++) |
959 |
{ |
960 |
m_sErrorMessage.append (m_oElements[i].toString ()); |
961 |
m_sErrorMessage.append (newLine); |
962 |
} |
963 |
return m_sErrorMessage.toString ();
|
964 |
} |
965 |
}//end class
|