public void run() { Connection con = null; Statement stmt = null; try { DataSource ds = getDataSource(); con = ds.getConnection(); if (executeOnlyIf(con, onlyIfQuery)) { stmt = con.createStatement(); if (query != null) { executeQuery(stmt, query); } else if (update != null) { executeUpdate(stmt, update); } else { throw new IllegalStateException("Both query and update properties are unset"); } } else { LOG.debug("Skipped because of " + onlyIfQuery); } } catch (RuntimeException e) { throw e; } catch (Throwable t) { if (ignore.matcher(t.getMessage()).matches()) { LOG.info("Ignoring " + t.getMessage()); } else { throw new RuntimeException(t.getMessage(), t); } } finally { try { if (stmt != null) { stmt.close(); } } catch (Exception g) { } try { if (con != null) { con.close(); } } catch (Exception g) { } } }
/** * Gets the instances of <CODE>Template</CODE> that have an ID that is already in the database. * * @return An <CODE>ArrayList</CODE> containing the instances of <CODE>Template</CODE> already in * the database. * @throws java.sql.SQLException Thrown on sql exception. */ public ArrayList findTemplatesInDatabase() throws java.sql.SQLException { ArrayList templatesInDatabase = new ArrayList(); Connection oracleConnection = getDataSource().getConnection(); try { Statement query = oracleConnection.createStatement(); try { StringBuffer sql = new StringBuffer("SELECT TMPL_ID FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TEMPLATE WHERE TMPL_ID IN ("); ArrayList templates = getTemplates(); int templateCount = templates.size(); for (int i = 0; i < templateCount; i++) { if (i > 0) sql.append(", "); sql.append("'"); sql.append(((Template) templates.get(i)).getID()); sql.append("'"); } sql.append(")"); ResultSet result = query.executeQuery(sql.toString()); try { while (result.next()) { String templateID = result.getString("TMPL_ID"); for (int i = 0; i < templateCount; i++) { Template currentTemplate = (Template) templates.get(i); if (templateID.equals(currentTemplate.getID())) { templatesInDatabase.add(currentTemplate); currentTemplate.setInDatabase(true); } } } } finally { result.close(); } } finally { query.close(); } } finally { oracleConnection.close(); } return templatesInDatabase; }
public String getId(Connection con) throws Exception { String ret = null; String sql = "select seq_id.nextval from dual"; Statement st = con.createStatement(); ResultSet rs = st.executeQuery(sql); if (rs.next()) { ret = sdf2.format(new java.util.Date()) + rs.getString(1); } else { throw new Exception("fail get id from oracle seq_id"); } rs.close(); st.close(); return ret; }
protected boolean executeOnlyIf(Connection con, String q) throws SQLException { if (q == null) return true; Statement stmt = null; try { stmt = con.createStatement(); q = q.replace("$PREFIX", getPrefix()); LOG.debug(" Executing query " + q); ResultSet rs = stmt.executeQuery(q); rs.next(); boolean res = rs.getBoolean(1); LOG.debug("Result: " + res); return res; } catch (SQLException sqe) { LOG.error(sqe.getMessage() + " from " + q); throw sqe; } finally { try { if (stmt != null) { stmt.close(); } } catch (Exception g) { } } }
public String[] send() { String ret[] = {"0", "internal_problem"}; if (!this.validateInput()) { ret[1] = "input_error"; return ret; } Connection con = null; try { con = DbCon.getConnection(); con.setAutoCommit(false); String sql = "select c.acc_no, t.status from customer c, tsel_cust_account t where c.msisdn = ? and t.acc_no = c.acc_no"; PreparedStatement ps = con.prepareStatement(sql); ps.setString(1, this.msisdn); ResultSet rs = ps.executeQuery(); String acc_no = ""; String status = ""; boolean b1 = false; boolean b2 = false; boolean b3 = false; if (rs.next()) { acc_no = rs.getString(1); b1 = true; status = rs.getString(2); if (status != null && status.equals("1")) { b2 = true; } else { ret[1] = "account_not_active"; } } rs.close(); ps.close(); double balance_cust = 0; double balance_merchant = 0; if (b1 && b2) { sql = "select * from tsel_cust_account where acc_no = ? for update"; ps = con.prepareStatement(sql); ps.setString(1, acc_no); rs = ps.executeQuery(); if (rs.next()) { b3 = true; balance_cust = rs.getDouble("balance"); } else { throw new Exception("acc_no not found " + msisdn + " accno:" + acc_no); } rs.close(); ps.close(); if (b3) { sql = "update tsel_cust_account set balance=balance+? where acc_no = ?"; ps = con.prepareStatement(sql); ps.setString(1, this.amount); ps.setString(2, acc_no); ps.executeUpdate(); String db_trxid = this.getId(con); // history tsel_account sql = "insert into tsel_cust_account_history values(?, ?, ?, ?, ?, ?, ?, sysdate, ?, ?, ?)"; ps = con.prepareStatement(sql); ps.setString(1, db_trxid); ps.setString(2, "txid"); ps.setString(3, acc_no); ps.setString(4, String.valueOf(UssdTx.TSEL_CASH_IN)); ps.setString(5, "0"); ps.setString(6, this.amount); ps.setDouble(7, (balance_cust + Double.parseDouble(this.amount))); ps.setString(8, this.loadingTerminalId); ps.setString(9, ""); ps.setString(10, this.userlogin); ps.executeUpdate(); ps.close(); // end of history tsel_account // merchant_id, merchant_acc String merchant_acc_no = ""; sql = "select m.acc_no from merchant m, loading_terminal l where terminal_id = ? and l.merchant_id = m.merchant_id"; ps = con.prepareStatement(sql); ps.setString(1, this.loadingTerminalId); rs = ps.executeQuery(); if (rs.next()) { merchant_acc_no = rs.getString(1); } else { throw new Exception("merchantid not found " + this.loadingTerminalId); } rs.close(); ps.close(); sql = "select balance from tsel_merchant_account where acc_no = ? for update "; ps = con.prepareStatement(sql); ps.setString(1, merchant_acc_no); rs = ps.executeQuery(); if (rs.next()) { balance_merchant = rs.getDouble(1); } else { throw new Exception("merchant_acc_no not exist"); } rs.close(); ps.close(); sql = "update tsel_merchant_account set balance=balance-? where acc_no = ?"; ps = con.prepareStatement(sql); ps.setString(1, this.amount); ps.setString(2, merchant_acc_no); ps.executeUpdate(); ps.close(); sql = "insert into tsel_merchant_account_history values(?, ?, ?, ?, ?, ?, ?, ?, sysdate, ?, ?, ?)"; ps = con.prepareStatement(sql); ps.setString(1, db_trxid); ps.setString(2, "txid"); ps.setString(3, merchant_acc_no); ps.setString(4, this.msisdn); ps.setString(5, String.valueOf(UssdTx.TSEL_CASH_IN)); ps.setString(6, this.amount); ps.setString(7, "0"); ps.setDouble(8, (balance_merchant - Double.parseDouble(this.amount))); ps.setString(9, this.loadingTerminalId); ps.setString(10, ""); ps.setString(11, this.userlogin); ps.executeUpdate(); ps.close(); // end of merchant_account history con.commit(); ret[0] = "1"; ret[1] = "Success"; } else { con.rollback(); } } else { if (!b1) { ret[1] = "account_not_found"; } con.rollback(); } } catch (Exception e) { e.printStackTrace(System.out); try { con.rollback(); } catch (Exception e2) { } } finally { if (con != null) { try { con.setAutoCommit(true); } catch (Exception e4) { } } if (con != null) { try { con.close(); } catch (Exception e3) { } } } return ret; }
public void actionPerformed(ActionEvent ae) { os = a.getText(); Pattern pat = Pattern.compile("[ .]"); String ms[] = pat.split(os), v; // Get whole String from Text area in tokens form Pattern pat1 = Pattern.compile("[ ]"); String ms1[] = pat1.split(os); // for string aaray int hcode[] = {100, 102, 220, 110, 111}, ecode[] = new int[20], j, p = 0; // p for check entry & hcode=Hindi lang.code ecode=English lang. code ResultSet rs; b.setText(""); try { Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "student"); Statement stmt = con.createStatement(); for (i = 0; i <= ms.length; i++) { v = "'" + ms[i] + "'"; p = 0; if (p == 0) { rs = stmt.executeQuery("select hnoun from noun where enoun=" + v); while (rs.next()) { ms1[i] = (rs.getString(1)); p++; ecode[i] = 100; } } if (p == 0) { rs = stmt.executeQuery("select hhv from hv where ehv=" + v); while (rs.next()) { ms1[i] = (rs.getString(1)); p++; ecode[i] = 111; } } if (p == 0) { rs = stmt.executeQuery("select hneg from neg where eneg=" + v); while (rs.next()) { ms1[i] = (rs.getString(1)); p++; ecode[i] = 220; } } if (p == 0) { rs = stmt.executeQuery("select hverb from verb where everb=" + v); while (rs.next()) { ms1[i] = (rs.getString(1)); p++; ecode[i] = 110; } } if (p == 0) { rs = stmt.executeQuery("select hpre from pre where epre=" + v); while (rs.next()) { ms1[i] = (rs.getString(1)); p++; ecode[i] = 140; } } if (p == 0) { rs = stmt.executeQuery("select hpro from pro where epro=" + v); while (rs.next()) { ms1[i] = (rs.getString(1)); p++; ecode[i] = 102; } } if (p == 0) { rs = stmt.executeQuery("select hart from art where eart=" + v); while (rs.next()) { ms1[i] = (rs.getString(1)); p++; ecode[i] = 123; } } } con.close(); } catch (Exception e) { System.out.println(e); } for (i = 0; i < 5; i++) { for (j = 0; j < ms.length; j++) { if (hcode[i] == ecode[j]) { b.append(ms1[j] + " "); // print to hindi code wise } } } }
/** * Saves the templates to the database. * * @throws java.sql.SQLException Thrown on sql error. */ public void saveToDatabase() throws java.sql.SQLException { setProgressIndeterminate(true); setMessage("Saving Templates"); ArrayList templates = getTemplates(); Connection oracleConnection = getDataSource().getConnection(); try { oracleConnection.setAutoCommit(false); Statement query = oracleConnection.createStatement(); try { int templateCount = templates.size(); // First remove any existing entries. StringBuffer sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_SGNL_FLD"); StringBuffer whereClause = new StringBuffer(" WHERE TMPL_ID IN ("); for (int i = 0; i < templateCount; i++) { if (i > 0) whereClause.append(", "); whereClause.append("'"); whereClause.append(((Template) templates.get(i)).getID()); whereClause.append("'"); } whereClause.append(")"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_MACRO"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_SGNL_REC"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_SGNL_FLD"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TEMPLATE"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_ARCH_REQ"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_ARCH_REQ_GRP"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("DELETE FROM "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_ARCH_REQ_GRP_ARCH_REQ"); sql.append(whereClause); query.execute(sql.toString()); sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append( ".TEMPLATE (TMPL_ID, TMPL_DESC, EXT_SRC_FILE_NM, EXT_SRC_FILE_MOD_DTE) VALUES (?, ?, ?, ?)"); PreparedStatement templateInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_MACRO (TMPL_ID, MACRO_ID) VALUES (?, ?)"); PreparedStatement macroInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append( ".TMPL_SGNL_REC (TMPL_ID, TMPL_SGNL_ID, REC_TYPE_ID, ARCH_IND, ARCH_FREQ, ARCH_TYPE) VALUES (?, ?, ?, ?, ?, ?)"); PreparedStatement signalInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append( ".TMPL_SGNL_FLD (TMPL_ID, TMPL_SGNL_ID, FLD_ID, REC_TYPE_ID, VAL) VALUES (?, ?, ?, ?, ?)"); PreparedStatement fieldInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_ARCH_REQ (TMPL_ID, ARCH_REQ_FILE_NM) VALUES (?, ?)"); PreparedStatement requestInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append(".TMPL_ARCH_REQ_GRP (TMPL_ID, ARCH_REQ_GRP_FILE_NM) VALUES (?, ?)"); PreparedStatement groupInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("INSERT INTO "); sql.append(MPSBrowserView.SCHEMA); sql.append( ".TMPL_ARCH_REQ_GRP_ARCH_REQ (TMPL_ID, ARCH_REQ_GRP_FILE_NM, ARCH_REQ_FILE_NM) VALUES (?, ?, ?)"); PreparedStatement requestGroupInsertStatement = oracleConnection.prepareStatement(sql.toString()); try { sql = new StringBuffer("UPDATE "); sql.append(MPSBrowserView.SCHEMA); sql.append( ".TMPL_SGNL_REC SET ARCH_IND = ?, ARCH_FREQ = ?, ARCH_TYPE = ?, ARCH_REQ_FILE = ? WHERE TMPL_ID = ? AND TMPL_SGNL_ID = ? AND REC_TYPE_ID = ?"); PreparedStatement signalUpdateStatement = oracleConnection.prepareStatement(sql.toString()); try { int progress = 0; setProgressMaximum(importedFieldCount + importedMacroCount); setProgressValue(0); setProgressIndeterminate(false); for (int templateIndex = 0; templateIndex < templateCount; templateIndex++) { Template currentTemplate = (Template) templates.get(templateIndex); String currentTemplateID = currentTemplate.getID(); templateInsertStatement.setString(1, currentTemplateID); String currentDescription = currentTemplate.getDescription(); if (currentDescription == null) templateInsertStatement.setNull(2, Types.VARCHAR); else templateInsertStatement.setString(2, currentDescription); templateInsertStatement.setString(3, currentTemplate.getFileName()); templateInsertStatement.setTimestamp( 4, currentTemplate.getFileModifiedDate()); templateInsertStatement.execute(); // Need to insert macros. int macroCount = currentTemplate.getMacroCount(); for (int macroIndex = 0; macroIndex < macroCount; macroIndex++) { macroInsertStatement.setString(1, currentTemplateID); String currentMacro = currentTemplate.getMacroAt(macroIndex); macroInsertStatement.setString(2, currentMacro); macroInsertStatement.execute(); setProgressValue(++progress); } int signalCount = currentTemplate.getSignalCount(); for (int signalIndex = 0; signalIndex < signalCount; signalIndex++) { Signal currentSignal = currentTemplate.getSignalAt(signalIndex); String currentSignalID = currentSignal.getID(); String currentRecordTypeID = currentSignal.getType().getRecordType().getID(); signalInsertStatement.setString(1, currentTemplateID); signalInsertStatement.setString(2, currentSignalID); signalInsertStatement.setString(3, currentRecordTypeID); signalInsertStatement.setString(4, currentSignal.getArchiveIndicator()); BigDecimal currentFrequency = currentSignal.getArchiveFrequency(); if (currentFrequency == null) currentFrequency = new BigDecimal("60"); // 60 default in RDB signalInsertStatement.setBigDecimal(5, currentFrequency); String currentType = currentSignal.getArchiveType(); if (currentType == null) currentType = "Monitor"; // 'Monitor' default in RDB signalInsertStatement.setString(6, currentType); signalInsertStatement.execute(); int fieldCount = currentSignal.getFieldCount(); for (int fieldIndex = 0; fieldIndex < fieldCount; fieldIndex++) { SignalField currentField = currentSignal.getFieldAt(fieldIndex); fieldInsertStatement.setString(1, currentTemplateID); fieldInsertStatement.setString(2, currentSignalID); fieldInsertStatement.setString(3, currentField.getType().getID()); fieldInsertStatement.setString(4, currentRecordTypeID); fieldInsertStatement.setString(5, currentField.getValue()); fieldInsertStatement.execute(); if (isParseCanceled()) { oracleConnection.rollback(); return; } setProgressValue(++progress); } } // Insert archive requests. int requestCount = currentTemplate.getArchiveRequestCount(); for (int requestIndex = 0; requestIndex < requestCount; requestIndex++) { ArchiveRequest currentRequest = currentTemplate.getArchiveRequestAt(requestIndex); String currentRequestFileName = currentRequest.getFileName(); requestInsertStatement.setString(1, currentTemplateID); requestInsertStatement.setString(2, currentRequestFileName); requestInsertStatement.execute(); signalCount = currentRequest.getSignalCount(); for (int signalIndex = 0; signalIndex < signalCount; signalIndex++) { Signal currentSignal = currentRequest.getSignalAt(signalIndex); String currentSignalID = currentSignal.getID(); signalUpdateStatement.setString( 1, currentSignal.getArchiveIndicator()); signalUpdateStatement.setBigDecimal( 2, currentSignal.getArchiveFrequency()); signalUpdateStatement.setString(3, currentSignal.getArchiveType()); signalUpdateStatement.setString(4, currentRequestFileName); signalUpdateStatement.setString(5, currentTemplateID); signalUpdateStatement.setString(6, currentSignalID); signalUpdateStatement.setString( 7, currentSignal.getType().getRecordType().getID()); signalUpdateStatement.execute(); int fieldCount = currentSignal.getFieldCount(); } } // Insert archive groups. int groupCount = currentTemplate.getArchiveGroupCount(); for (int groupIndex = 0; groupIndex < groupCount; groupIndex++) { ArchiveGroup currentGroup = currentTemplate.getArchiveGroupAt(groupIndex); groupInsertStatement.setString(1, currentTemplateID); String currentGroupFileName = currentGroup.getFileName(); groupInsertStatement.setString(2, currentGroupFileName); groupInsertStatement.execute(); requestCount = currentGroup.getArchiveRequestCount(); for (int requestIndex = 0; requestIndex < requestCount; requestIndex++) { ArchiveRequest currentRequest = currentGroup.getArchiveRequestAt(requestIndex); String currentRequestFileName = currentRequest.getFileName(); requestGroupInsertStatement.setString(1, currentTemplateID); requestGroupInsertStatement.setString(2, currentGroupFileName); requestGroupInsertStatement.setString( 3, currentRequest.getFileName()); } } } } finally { signalUpdateStatement.close(); } } finally { requestGroupInsertStatement.close(); } } finally { groupInsertStatement.close(); } } finally { requestInsertStatement.close(); } } finally { fieldInsertStatement.close(); } } finally { signalInsertStatement.close(); } } finally { macroInsertStatement.close(); } } finally { templateInsertStatement.close(); } } catch (java.sql.SQLException ex) { oracleConnection.rollback(); throw ex; } finally { query.close(); } if (isParseCanceled()) oracleConnection.rollback(); else oracleConnection.commit(); } finally { oracleConnection.close(); } }