List of usage examples for org.apache.commons.dbutils QueryRunner update
public int update(String sql, Object... params) throws SQLException
From source file:azkaban.executor.JdbcExecutorLoader.java
@Override public int removeExecutionLogsByTime(long millis) throws ExecutorManagerException { final String DELETE_BY_TIME = "DELETE FROM execution_logs WHERE upload_time < ?"; QueryRunner runner = createQueryRunner(); int updateNum = 0; try {//from w w w.ja v a2 s . c o m updateNum = runner.update(DELETE_BY_TIME, millis); } catch (SQLException e) { e.printStackTrace(); throw new ExecutorManagerException("Error deleting old execution_logs before " + millis, e); } return updateNum; }
From source file:jdao.JDAO.java
public static int insertSet(int dbType, Connection conn, QueryRunner ds, String table, Map cols, boolean onDuplicateKeyUpdate, Collection updateFields) throws Exception { if (dbType != JDAO.DB_TYPE_MYSQL) { throw new IllegalArgumentException("DB TYPE NOT MYSQL"); }// w w w .ja va2 s .com Vector vv = new Vector(); String setqq = buildSet(dbType, vv, cols); StringBuilder qq = new StringBuilder(); qq.append("INSERT INTO " + table + " SET "); qq.append(setqq); if (onDuplicateKeyUpdate) { Map um = new HashMap(); for (Object o : updateFields) { um.put(o, cols.get(o)); } String setuqq = buildSet(dbType, vv, um); qq.append(" ON DUPLICATE KEY UPDATE "); qq.append(setuqq); } if (conn == null) { return ds.update(qq.toString(), vv.toArray()); } return ds.update(conn, qq.toString(), vv.toArray()); }
From source file:com.neu.controller.MessageController.java
protected ModelAndView handleRequestInternal(HttpServletRequest request, HttpServletResponse response) throws Exception { DataSource ds = (DataSource) this.getApplicationContext().getBean("myDataSource"); String action = request.getParameter("action"); ModelAndView mv = new ModelAndView(); HttpSession session = request.getSession(); String userName = (String) session.getAttribute("userName"); if (action.equalsIgnoreCase("reply")) { try {/* w w w . ja v a2 s . c om*/ String receiver = request.getParameter("to"); System.out.println("Printing receiver in reply case: " + receiver); QueryRunner run = new QueryRunner(ds); ResultSetHandler<UsersBean> user = new BeanHandler<UsersBean>(UsersBean.class); UsersBean ub = run.query("select * from userstable where userName =?", user, receiver); if (ub != null) { System.out.println("printing userEmail received from DB: " + ub.getUserEmail()); mv.addObject("toEmail", ub.getUserEmail()); mv.addObject("to", receiver); } mv.setViewName("reply"); } catch (SQLException e) { System.out.println(e); } } else if (action.equalsIgnoreCase("sent")) { System.out.println("In sent case"); try { String receiver = request.getParameter("to"); String receiverEmail = request.getParameter("toEmail"); System.out.println("printing receiver email: " + receiverEmail); QueryRunner run = new QueryRunner(ds); ResultSetHandler<UsersBean> user = new BeanHandler<UsersBean>(UsersBean.class); UsersBean ub = run.query("select * from userstable where userName =?", user, userName); if (ub != null) { String senderEmail = ub.getUserEmail(); System.out.println("printing senderemail: " + senderEmail); ResultSetHandler<MessageBean> msg = new BeanHandler<MessageBean>(MessageBean.class); Object[] params = new Object[4]; params[0] = userName; params[1] = request.getParameter("message"); Date d = new Date(); SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd"); String messageDate = format.format(d); params[2] = messageDate; params[3] = receiver; int inserts = run.update( "Insert into messages (fromUser,message,messageDate,userName) values(?,?,?,?)", params);//Logic to send the email try { Email email = new SimpleEmail(); email.setHostName("smtp.googlemail.com");//If a server is capable of sending email, then you don't need the authentication. In this case, an email server needs to be running on that machine. Since we are running this application on the localhost and we don't have a email server, we are simply asking gmail to relay this email. email.setSmtpPort(465); email.setAuthenticator( new DefaultAuthenticator("contactapplication2017@gmail.com", "springmvc")); email.setSSLOnConnect(true); email.setFrom(senderEmail);//This email will appear in the from field of the sending email. It doesn't have to be a real email address.This could be used for phishing/spoofing! email.setSubject("Thanks for Signing Up!"); email.setMsg("Welcome to Web tools Lab 5 Spring Application sign up email test!"); email.addTo(receiverEmail);//Will come from the database email.send(); } catch (Exception e) { System.out.println("Email Exception" + e.getMessage()); e.printStackTrace(); } mv.setViewName("messageSent"); } else { mv.addObject("error", "true"); mv.setViewName("index"); } } catch (Exception ex) { System.out.println("Error Message" + ex.getMessage()); ex.printStackTrace(); } } return mv; }
From source file:jdao.JDAO.java
public static int insert(int dbType, Connection conn, QueryRunner ds, String table, Map cols, boolean onDuplicateKeyUpdate, Collection updateFields) throws Exception { if (onDuplicateKeyUpdate && (dbType != JDAO.DB_TYPE_MYSQL) && (dbType != JDAO.DB_TYPE_CRATE)) { throw new IllegalArgumentException("DB TYPE NOT MYSQL"); }/*from www.ja v a 2 s .c o m*/ Vector parm = new Vector(); StringBuilder qq = new StringBuilder(); qq.append("INSERT INTO " + table + " ( "); boolean op = true; for (Object kv : cols.entrySet()) { parm.add(((Map.Entry) kv).getValue()); if (!op) { qq.append(","); } qq.append(((Map.Entry) kv).getKey()); op = false; } qq.append(" ) VALUES ("); op = true; for (Object v : parm) { if (!op) { qq.append(","); } qq.append("?"); op = false; } qq.append(" ) "); if (onDuplicateKeyUpdate) { Map um = new HashMap(); for (Object o : updateFields) { um.put(o, cols.get(o)); } String setuqq = buildSet(dbType, parm, um); qq.append(" ON DUPLICATE KEY UPDATE "); qq.append(setuqq); } if (conn == null) { return ds.update(qq.toString(), parm.toArray()); } return ds.update(conn, qq.toString(), parm.toArray()); }
From source file:com.neu.controller.LoginController.java
protected ModelAndView handleRequestInternal(HttpServletRequest request, HttpServletResponse response) throws Exception { DataSource ds = (DataSource) this.getApplicationContext().getBean("myDataSource"); String action = request.getParameter("action"); ModelAndView mv = new ModelAndView(); HttpSession session = request.getSession(); if (action.equalsIgnoreCase("login")) { try {//from w ww .ja va2 s . c o m String userName = request.getParameter("user"); String password = request.getParameter("password"); QueryRunner run = new QueryRunner(ds); ResultSetHandler<UsersBean> user = new BeanHandler<UsersBean>(UsersBean.class); Object[] params = new Object[2]; params[0] = userName; params[1] = password; UsersBean ub = run.query("select * from userstable where userName =? and userPassword=?", user, params); if (ub != null) { ResultSetHandler<List<MessageBean>> messages = new BeanListHandler<MessageBean>( MessageBean.class); List<MessageBean> msg = run.query("select * from messages where userName =?", messages, userName); session.setAttribute("userName", userName); session.setAttribute("messageList", msg); mv.setViewName("userhome"); } else { mv.addObject("error", "true"); mv.setViewName("index"); } } catch (Exception ex) { System.out.println("Error Message" + ex.getMessage()); } } else if (action.equalsIgnoreCase("logout")) { session.invalidate(); mv.setViewName("index"); } else if (action.equalsIgnoreCase("signup")) { System.out.println("sign up"); // // String userName = request.getParameter("user"); // String password = request.getParameter("password"); // String emailObj = request.getParameter("emailObj"); // // System.out.println("printing details: " + userName + " " +password + " "+emailObj); mv.setViewName("signup"); } else if (action.equalsIgnoreCase("signupsubmit")) { System.out.println("sign up submit"); String userName = request.getParameter("user"); String password = request.getParameter("password"); String email = request.getParameter("email"); System.out.println("printing details: " + userName + " " + password + " " + email); if (userName.equals("") || (password.equals("")) || (email.equals(""))) { System.out.println("empty values"); mv.addObject("error", "true"); } else { ResultSetHandler<UsersBean> user = new BeanHandler<UsersBean>(UsersBean.class); Object[] params = new Object[3]; params[0] = userName; params[1] = password; params[2] = email; QueryRunner run = new QueryRunner(ds); int inserts = run.update("insert into userstable (UserName,UserPassword,UserEmail) values (?,?,?)", params);//Logic to insert into table System.out.println("inserts value " + inserts); if (inserts > 0) { mv.addObject("success", "true"); Email emailObj = new SimpleEmail(); emailObj.setHostName("smtp.googlemail.com");//If a server is capable of sending emailObj, then you don't need the authentication. In this case, an emailObj server needs to be running on that machine. Since we are running this application on the localhost and we don't have a emailObj server, we are simply asking gmail to relay this emailObj. emailObj.setSmtpPort(465); emailObj.setAuthenticator( new DefaultAuthenticator("contactapplication2017@gmail.com", "springmvc")); emailObj.setSSLOnConnect(true); emailObj.setFrom("webtools@hello.com");//This emailObj will appear in the from field of the sending emailObj. It doesn't have to be a real emailObj address.This could be used for phishing/spoofing! emailObj.setSubject("TestMail"); emailObj.setMsg("This is spring MVC Contact Application sending you the email"); emailObj.addTo(email);//Will come from the sign up details emailObj.send(); } } mv.setViewName("signup"); } return mv; }
From source file:com.iana.boesc.dao.BOESCDaoImpl.java
@Override public boolean insertDVIRRecord(DVIR_EDI322Bean eb, DVIRRCDStatisticBean bean, String boescUserId, String userType, File file) throws Exception { logger.info("----- getPopulatedDataAndInsert ............" + " boescUserId ::" + boescUserId + " userType ::" + userType); QueryRunner qrun = new QueryRunner(getDataSource()); Connection conn = getConnection(); conn.setAutoCommit(false);// www .j a v a 2s . c om PreparedStatement pstmt = null; ResultSet rs = null; try { StringBuilder sbQuery = new StringBuilder( "INSERT INTO DVIR_TRAN_SET (ISA_HEADER, GS_HEADER, SENDER_ID, SENDER_TYPE, ISA_DATETIME, GS_CONTROL, ST_CONTROL,"); sbQuery.append( " INSP_DATE, INSP_TIME, INSP_TIME_ZONE, EQ_PREFIX, EQ_NUMBER, CHASSIS_ID, IEP_SCAC, PORT_QUAL, PORT_ID, "); sbQuery.append( " DRV_STATE_ABBR, DRV_LIC_NO, DRV_NAME, MC_SCAC, MC_NAME, RCD_INFO, IEP_DOT, MC_EIN, MC_DOT, IDD_PIN, "); sbQuery.append( " Q5_SEG, N7_SEG, R4_SEG, N1_SEG, N1_DR, RCD_00, RCD_01, RCD_02, RCD_03, RCD_04, RCD_05, RCD_06, RCD_07, RCD_08, RCD_09, "); if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_IEP)) { sbQuery.append(" IEP_ID, STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_MRV)) { sbQuery.append(" MRV_ID, STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_FO)) { sbQuery.append(" FO_ID, STATUS "); } sbQuery.append( " ,CREATED_DATE ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); //get Additional Details from GIER DB GIERInfoDetails gierInfo = null; gierInfo = getDVIRAdditionaldetails(eb.getEqpInitial(), eb.getEqpNumber()); System.out.println("Before UIIA Datasource"); UIIAInfoDetails uiiaInfo = null; uiiaInfo = getUIIAdetailsforDVIR(eb); //logger.info("gierInfo ::"+gierInfo); pstmt = conn.prepareStatement(sbQuery.toString(), Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, eb.getIsaheader()); pstmt.setString(2, eb.getGsHeader()); pstmt.setString(3, eb.getSenderId()); pstmt.setString(4, userType); pstmt.setString(5, eb.getIsaDateTime()); pstmt.setString(6, eb.getGsControl()); pstmt.setString(7, eb.getStControl()); pstmt.setString(8, eb.getInspDate()); pstmt.setString(9, eb.getInspTime()); pstmt.setString(10, eb.getInspTimeZone()); pstmt.setString(11, eb.getEqpInitial()); pstmt.setString(12, eb.getEqpNumber()); pstmt.setString(13, eb.getChassisId()); pstmt.setString(14, gierInfo.getCompanySCACCode()); pstmt.setString(15, eb.getPortQualifier()); pstmt.setString(16, eb.getPortIdentifier()); pstmt.setString(17, eb.getDrvState()); pstmt.setString(18, eb.getDrvLicNo()); pstmt.setString(19, uiiaInfo.getDrvName()); pstmt.setString(20, eb.getMcScac()); pstmt.setString(21, eb.getMcName()); pstmt.setString(22, eb.getRcdInfo()); pstmt.setString(23, gierInfo.getUsDotNumber()); pstmt.setString(24, uiiaInfo.getMcEin()); pstmt.setString(25, uiiaInfo.getMcDot()); pstmt.setString(26, uiiaInfo.getIddPin()); pstmt.setString(27, eb.getQ5Details()); pstmt.setString(28, eb.getN7Details()); pstmt.setString(29, eb.getR4Details()); pstmt.setString(30, eb.getN1Details()); pstmt.setString(31, eb.getN1DrDetails()); pstmt.setInt(32, bean.getNoDefectsCount()); pstmt.setInt(33, bean.getBrakesCount()); pstmt.setInt(34, bean.getLightsCount()); pstmt.setInt(35, bean.getWheelCount()); pstmt.setInt(36, bean.getAirlineCount()); pstmt.setInt(37, bean.getCouplingCount()); pstmt.setInt(38, bean.getFrameCount()); pstmt.setInt(39, bean.getBolsterCount()); pstmt.setInt(40, bean.getFastenerCount()); pstmt.setInt(41, bean.getSliderCount()); pstmt.setString(42, boescUserId); pstmt.setString(43, GlobalVariables.STATUS_PENDING); pstmt.setObject(44, DateTimeFormater.getSqlSysTimestamp()); int dbStat = 0; int dvirKey = 0; dbStat = pstmt.executeUpdate(); rs = pstmt.getGeneratedKeys(); if (dbStat != 0) { if (rs != null) { while (rs.next()) { dvirKey = rs.getInt(1); logger.info("dvirKey: " + dvirKey); } } } if (dvirKey != 0) { conn.commit(); //Update BOESC_UNIQUE_NO : using business logic String sql = "UPDATE DVIR_TRAN_SET SET DVIR_NO = ? WHERE DVIR_TRAN_ID = ? "; qrun.update(sql, new Object[] { Utility.addPadToUniqueNum(dvirKey, "DVIR-"), dvirKey }); logger.info("Record Inserted successfully for DVIR..." + file.getName()); return true; } else { conn.rollback(); logger.error("Failure Data insertion in DVIR.."); } } finally { try { if (rs != null) { rs.close(); } if (pstmt != null) { pstmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex1) { logger.error( "Caught SQL exception while closing prepared statement /resultset " + ex1.getMessage()); ex1.printStackTrace(); throw ex1; } catch (Exception e) { logger.error("Caught SQL exception in finally block " + e.getMessage()); e.printStackTrace(); throw e; } } return false; }
From source file:com.iana.boesc.dao.BOESCDaoImpl.java
@Override public boolean getPopulatedDataAndInsert(EDI322Bean eb, String boescUserId, String userType, File file, List<String> finalErrorList, Map<Integer, Object> fileTransStatus) throws Exception { logger.info("----- getPopulatedDataAndInsert ............" + " boescUserId ::" + boescUserId + " userType ::" + userType); String status = ""; logger.info("----- ISA ............"); String gsHeader = ""; String gsControl = ""; String st_control = ""; String n7Header = ""; String q5Header = ""; String equip_prefix = ""; String equip_number = ""; String w2Header = ""; String r4Header = ""; String r13Header = ""; String n9Header = ""; String eventType = ""; String port_qual = ""; String iana_splc = ""; QueryRunner qrun = new QueryRunner(getDataSource()); logger.info("----- GE ............"); for (int i = 0; i < eb.getListGSDetails().size(); i++) { gsHeader = eb.getListGSDetails().get(i).getHeaderDetails(); gsControl = eb.getListGSDetails().get(i).getGroupControlNumber(); logger.info("gsControl ::" + gsControl + " gsHeader ::" + gsHeader); int startIndex = i + 1; logger.info("----- ST & SE ............"); for (int a = 0; a < eb.getSTDetailsMap().get(startIndex).size(); a++) { Connection conn = getConnection(); conn.setAutoCommit(false);// w ww .ja v a 2 s. c o m PreparedStatement pstmt = null; ResultSet rs = null; StringBuilder sbQuery = new StringBuilder( "INSERT INTO BOESC_TRAN_SET (ISA_HEADER, GS_HEADER, INPUT_TYPE, SENDER_ID, SENDER_TYPE, "); sbQuery.append( " ISA_DATETIME, GS_CONTROL, ST_CONTROL, EVENT_TYPE, EQ_PREFIX, EQ_NUMBER, CHASSIS_ID, IEP_SCAC, IEP_DOT, PORT_QUAL, IANA_SPLC, "); sbQuery.append(" POOL_ID, POOL_NAME, Q5_SEG, N7_SEG, W2_SEG, R4_SEG, N9_SEG, R13_SEG, "); if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_IEP)) { sbQuery.append(" RECEIVER_ID, REC_STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_MRV)) { sbQuery.append(" MRV_ID, MRV_STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_FO)) { sbQuery.append(" FO_ID, FO_STATUS "); } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_SU)) { } try { status = ""; int changedIndex = a + 1; //very important Variable if (fileTransStatus != null && fileTransStatus.size() > 0) { logger.info("-------------------- changedIndex ::" + changedIndex + " fileTransStatus.get(startIndex) ::" + fileTransStatus.get(changedIndex)); if (fileTransStatus.get(changedIndex) == null) { status = GlobalVariables.STATUS_PENDING; } else { status = GlobalVariables.STATUS_REJECTED; } } else { status = GlobalVariables.STATUS_PENDING; } r13Header = ""; r4Header = ""; n9Header = ""; port_qual = ""; iana_splc = ""; GIERInfoDetails gierInfo = null; st_control = eb.getSTDetailsMap().get(startIndex).get(a).getTransactionSetControlNumber(); logger.info(" st_control :" + st_control); /*String transactionControlNumberSE = eb.getSEDetailsMap().get(startIndex).get(a).getTransactionSetControlNumber(); logger.info(" transactionControlNumberSE :"+transactionControlNumberSE );*/ logger.info("----- N7 ............"); for (int q = 0; q < eb.getN7DetailsMap().get(startIndex).get(changedIndex).size(); q++) { n7Header = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails(); logger.info("n7Header ::" + n7Header); equip_prefix = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q) .getEquipmentInitial(); equip_number = eb.getN7DetailsMap().get(startIndex).get(changedIndex).get(q) .getEquipmentNumber(); logger.info("equip_prefix ::" + equip_prefix); logger.info("equip_number ::" + equip_number); equip_prefix = equip_prefix == null || equip_prefix.trim().length() == 0 ? "" : equip_prefix; equip_number = equip_number == null || equip_number.trim().length() == 0 ? "" : equip_number; gierInfo = getDVIRAdditionaldetails(equip_prefix, equip_number); //logger.info("gierInfo ::"+gierInfo); } logger.info("----- Q5 ............"); for (int q = 0; q < eb.getQ5DetailsMap().get(startIndex).get(changedIndex).size(); q++) { q5Header = eb.getQ5DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails(); eventType = eb.getQ5DetailsMap().get(startIndex).get(changedIndex).get(q).getStatusCode(); logger.info("q5Header ::" + q5Header + " eventType ::" + eventType); } logger.info("----- W2 ............"); for (int q = 0; q < eb.getW2DetailsMap().get(startIndex).get(changedIndex).size(); q++) { w2Header = eb.getW2DetailsMap().get(startIndex).get(changedIndex).get(q).getHeaderDetails(); logger.info("w2Header ::" + w2Header); } logger.info("----- R4 ............"); String tempR4Header = ""; String tempPort_qual = ""; String tempIana_splc = ""; for (int q = 0; q < eb.getR4DetailsMap().get(startIndex).get(changedIndex).size(); q++) { tempR4Header = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q) .getHeaderDetails(); tempPort_qual = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q) .getLocationQualifier(); tempIana_splc = eb.getR4DetailsMap().get(startIndex).get(changedIndex).get(q) .getLocationIdentifier(); r4Header = r4Header + GlobalVariables.FIELD_SEPARATOR + tempR4Header; port_qual = port_qual + GlobalVariables.FIELD_SEPARATOR + tempPort_qual; iana_splc = iana_splc + GlobalVariables.FIELD_SEPARATOR + tempIana_splc; logger.info("r4Header ::" + r4Header + " port_qual:: " + port_qual + " iana_splc ::" + iana_splc); } r4Header = r4Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? r4Header.substring(1) : r4Header; port_qual = port_qual.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? port_qual.substring(1) : port_qual; iana_splc = iana_splc.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? iana_splc.substring(1) : iana_splc; logger.info("----- R13 ............"); String tempR13Header = ""; for (int q = 0; q < eb.getR13DetailsMap().get(startIndex).get(changedIndex).size(); q++) { tempR13Header = eb.getR13DetailsMap().get(startIndex).get(changedIndex).get(q) .getHeaderDetails(); r13Header = r13Header + GlobalVariables.FIELD_SEPARATOR + tempR13Header; logger.info("r13Header ::" + r13Header); } r13Header = r13Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? r13Header.substring(1) : r13Header; logger.info("----- N9 ............"); String tempN9Header = ""; for (int q = 0; q < eb.getN9DetailsMap().get(startIndex).get(changedIndex).size(); q++) { tempN9Header = eb.getN9DetailsMap().get(startIndex).get(changedIndex).get(q) .getHeaderDetails(); n9Header = n9Header + GlobalVariables.FIELD_SEPARATOR + tempN9Header; logger.info("n9Header ::" + n9Header); } n9Header = n9Header.startsWith(GlobalVariables.FIELD_SEPARATOR) == true ? n9Header.substring(1) : n9Header; sbQuery.append( " , CREATED_DATE) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); if (gierInfo == null) { gierInfo = new GIERInfoDetails(); //this situation happen when all segment are missing except : ISA,SE,ST,GE,GS,IEA } equip_prefix = equip_prefix == null || equip_prefix.trim().length() == 0 ? "" : equip_prefix; equip_number = equip_number == null || equip_number.trim().length() == 0 ? "" : equip_number; pstmt = conn.prepareStatement(sbQuery.toString(), Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, eb.getISADetails().getHeaderDetails()); pstmt.setString(2, gsHeader); pstmt.setString(3, GlobalVariables.INPUT_TYPE_BOESC_322); pstmt.setString(4, eb.getISADetails().getInterchangeSenderId()); pstmt.setString(5, userType); pstmt.setString(6, eb.getISADetails().getInterchangeDate()); pstmt.setString(7, gsControl); pstmt.setString(8, st_control); pstmt.setString(9, eventType); pstmt.setString(10, equip_prefix); pstmt.setString(11, equip_number); pstmt.setString(12, equip_prefix + equip_number); pstmt.setString(13, gierInfo.getCompanySCACCode() == null ? "" : gierInfo.getCompanySCACCode()); pstmt.setString(14, gierInfo.getUsDotNumber() == null ? "" : gierInfo.getUsDotNumber()); pstmt.setString(15, port_qual); pstmt.setString(16, iana_splc); pstmt.setString(17, gierInfo.getChassisPoolId() == null ? "" : gierInfo.getChassisPoolId()); pstmt.setString(18, gierInfo.getChassisPoolName() == null ? "" : gierInfo.getChassisPoolName()); pstmt.setString(19, q5Header); pstmt.setString(20, n7Header); pstmt.setString(21, w2Header); pstmt.setString(22, r4Header); pstmt.setString(23, n9Header); pstmt.setString(24, r13Header); pstmt.setString(25, boescUserId); pstmt.setString(26, status); pstmt.setObject(27, DateTimeFormater.getSqlSysTimestamp()); logger.info("query :: " + sbQuery.toString()); int dbStat = 0; int boescKey = 0; dbStat = pstmt.executeUpdate(); rs = pstmt.getGeneratedKeys(); if (dbStat != 0) { if (rs != null) { while (rs.next()) { boescKey = rs.getInt(1); logger.info("boescKey: " + boescKey); } } conn.commit(); } else { conn.rollback(); } if (boescKey != 0) { //Update BOESC_UNIQUE_NO : using business logic String sql = "UPDATE BOESC_TRAN_SET SET BOESC_UNIQUE_NO = ? WHERE BOESC_TRAN_ID = ? "; qrun.update(sql, new Object[] { Utility.addPadToUniqueNum(boescKey, "BOESC-"), boescKey }); logger.info("Record Inserted successfully for BOESC..." + file.getName()); return true; } else { logger.error("Failure Data insertion in BOESC.."); } } finally { try { if (rs != null) { rs.close(); } if (pstmt != null) { pstmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex1) { logger.error("Caught SQL exception while closing prepared statement /resultset " + ex1.getMessage()); ex1.printStackTrace(); throw ex1; } catch (Exception e) { logger.error("Caught SQL exception in finally block " + e.getMessage()); e.printStackTrace(); throw e; } } } } return false; }
From source file:org.apache.lens.server.user.LDAPBackedDatabaseUserConfigLoader.java
@Override public Map<String, String> getUserConfig(final String loggedInUser) throws UserConfigLoaderException { try {//from w w w. j a v a 2 s. c om final String[] intermediateKey = intermediateCache.get(loggedInUser, new Callable<String[]>() { @Override public String[] call() throws Exception { String[] config = queryDatabase(intermediateQuerySql, true, loggedInUser, Timestamp.valueOf(DateTime.now().toString(DATE_TIME_FORMATTER))); if (config == null) { config = getAttributes(loggedInUser); Object[] updateArray = new Object[config.length + 2]; for (int i = 0; i < config.length; i++) { updateArray[i + 1] = config[i]; } updateArray[0] = loggedInUser; updateArray[config.length + 1] = Timestamp .valueOf(DateTime.now().plusHours(expiryHours).toString(DATE_TIME_FORMATTER)); QueryRunner runner = new QueryRunner(ds); runner.update(intermediateDeleteSql, loggedInUser); runner.update(intermediateInsertSql, updateArray); } return config; } }); return cache.get(intermediateKey, new Callable<Map<String, String>>() { @Override public Map<String, String> call() throws Exception { final String[] argsAsArray = queryDatabase(querySql, false, intermediateKey); if (argsAsArray.length != keys.length) { throw new UserConfigLoaderException( "size of columns retrieved by db query(" + argsAsArray.length + ") " + "is not equal to the number of keys required(" + keys.length + ")."); } return new HashMap<String, String>() { { for (int i = 0; i < keys.length; i++) { put(keys[i], argsAsArray[i]); } } }; } }); } catch (ExecutionException e) { throw new UserConfigLoaderException(e); } }
From source file:org.codesearch.commons.database.DBAccessImpl.java
/** * {@inheritDoc}/*from w w w .j a va2 s.co m*/ */ @Override public synchronized void deleteRepository(String repoName) throws DatabaseAccessException { QueryRunner run = new QueryRunner(dataSource); try { run.update(STMT_DELETE_REPOSITORY, repoName); } catch (SQLException ex) { throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex); } }
From source file:org.codesearch.commons.database.DBAccessImpl.java
/** * {@inheritDoc}//from w ww. j a v a 2 s .c o m */ @Override public synchronized void createRepositoryEntry(String repositoryName) throws DatabaseAccessException { QueryRunner run = new QueryRunner(dataSource); try { run.update(STMT_CREATE_REPOSITORY_ENTRY, repositoryName); } catch (SQLException ex) { throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex); } }