m2u-upass-core / src / my / com / upass / db / DBOperations.java @ 68:805ffcfa8c6f
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 | 46:8f67d8567943 | hadi | import my.com.upass.generic.hibernate.HibernateUtils; |
| 12 | 0:02300db8682b | hadi | import my.com.upass.pojo.TacBean; |
| 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 |