package com.exelon.drillinginfo.service; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.text.ParseException; import java.util.List; import javax.sql.DataSource; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import com.exelon.drillinginfo.dto.ListPdenDesc; import com.exelon.drillinginfo.dto.PdenDescDto; import com.exelon.drillinginfo.error.LoadPdenDescException; /** * Pden Desc * * @author Shakeel Ahmed * @version 1.0 Apr 27, 2016 */ @Service @Transactional public class PdenDescService { @Autowired private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource); } public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public final static Logger logger = LoggerFactory.getLogger(PdenDescService.class); public ListPdenDesc createDrillingInfo(ListPdenDesc drillingInfoDto) throws ParseException, SQLException { List listpenddesc = drillingInfoDto.getListpdendesc(); Connection connection = this.jdbcTemplate.getDataSource().getConnection(); PreparedStatement ps = null; CallableStatement proc = null; CallableStatement endproc = null; int id = 0; try { connection.setAutoCommit(false); proc = connection.prepareCall("{ call process_control.start_transaction_run(?, ?) }"); proc.setString(1, "Load data into drilling_info_staging.pden_desc"); proc.registerOutParameter(2, java.sql.Types.INTEGER); proc.executeUpdate(); id = proc.getInt(2); logger.info("Run id: " + id); String sql = "INSERT INTO drilling_info_staging.pden_desc " + "(entity_id, cmn, district, pden_type, prod_type, state, country, pden_name, api_no, comp_date, offshore, field, reservoir, lease_no, commingle_no, commingle_date, " + "county_id, county, well_no, curr_oper_id, curr_oper_no, curr_oper_name, liq_gath_name_1, gas_gath_name_1, status, drill_type , elevation, elevation_type, total_depth, upper_perf," + "lower_perf, liq_grav, gas_grav, liq_daily, gas_daily, liq_cum, gas_cum, latitude, longitude, loc_remark, first_prod_date, last_prod_date, latest_liq, latest_gas, latest_wtr, latest_wcnt, " + "prior12_liq, prior12_gas, prior12_wtr, first_liq, first_gas, first_wtr, first12_liq, first12_gas, first12_wtr, wtr_cum, wtr_year, liq_year, gas_year," + "location, section ,qtr_qtr, merid, ocs_area, gor, _yield, rkb_elev, water_depth, res_vert_depth, max_vert_depth, spud_date, latitude_botm, longitude_botm," + "latitude_ex1, longitude_ex1, prior_liq_cum, prior_gas_cum, prior_wtr_cum, basin, common_oper_name, lessee_avail, pgc_area, quad_name," + "_abstract, survey ,tx_block, tx_sec, subsurvey, tx_scrap, field_no, months_produced, reported_oper_name, formation, peak_gas, peak_liq, " + "latest_test_date, latest_flow_res, latest_whsip, twp, rng, well_id, lease_id, alloc_plus, first6_liq, first6_gas, first6_wtr," + "first24_liq, first24_gas, first24_wtr, first60_liq, first60_gas, first60_wtr, first6_boe, first12_boe, first24_boe, first60_boe, first6_mmcfge, first12_mmcfge, first24_mmcfge, first60_mmcfge," + "decl_3mo_liq, decl_3mo_gas, decl_3mo_wtr, decl_12mo_liq, decl_12mo_gas,decl_12mo_wtr, decl_24mo_liq, decl_24mo_gas, decl_24mo_wtr, decl_60mo_liq, decl_60mo_gas, decl_60mo_wtr," + "gor_2nd_mo, gor_latest_mo, cfged_prac_ip, cfged_cur_rate, mmcfge_cum, bcfge_cum, gor_cum, max_actv_wells, peak_liq_daily, peak_gas_daily, peak_boe, peak_mmcfge, yield_2nd_mo, yield_latest_mo, " + " liq_prac_ip_daily, gas_prac_ip_daily, liq_max_daily, gas_max_daily, wtr_daily, boe_cum, max_wtr_daily, boe_prac_ip, peak_gas_month, peak_gas_month_no, peak_liq_month, peak_liq_month_no, " + " peak_daily_gas_month, peak_daily_gas_month_no, peak_daily_liq_month, peak_daily_liq_month_no , peak_boe_month, peak_boe_month_no, peak_mmcfge_month, peak_mmcfge_month_no, transaction_run_id )" + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,?, ?," + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )"; // String sql = "INSERT INTO drilling_info_staging.pden_desc " + "(entity_id, cmn, district, pden_type, prod_type, state, country, pden_name, api_no, comp_date, offshore, field, reservoir, lease_no, commingle_no, commingle_date, " // + "county_id, county, well_no, curr_oper_id, curr_oper_no, curr_oper_name, liq_gath_name_1, gas_gath_name_1, status, transaction_run_id )" // + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )"; ps = connection.prepareStatement(sql); //Setting Batch Size to 3 for Better Performance. final int batchSize = 3; int count = 0; for (PdenDescDto pdenDescDto: listpenddesc) { ps.setInt(1, pdenDescDto.getEntityid().intValue()); ps.setString(2, pdenDescDto.getCmn().replace('\0', ' ')); ps.setString(3, pdenDescDto.getDistrict().replace('\0', ' ')); ps.setString(4, pdenDescDto.getPden_type().replace('\0', ' ')); ps.setString(5, pdenDescDto.getProd_type().replace('\0', ' ')); ps.setString(6, pdenDescDto.getState().replace('\0', ' ')); ps.setString(7, pdenDescDto.getCountry().replace('\0', ' ')); ps.setString(8, pdenDescDto.getPden_name().replace('\0', ' ')); ps.setString(9, pdenDescDto.getApi_no().replace('\0', ' ')); if (pdenDescDto.getComp_date() != ""){ ps.setDate(10, java.sql.Date.valueOf(pdenDescDto.getComp_date())); } if (pdenDescDto.getComp_date() == ""){ ps.setNull(10, java.sql.Types.DATE); } ps.setString(11, pdenDescDto.getOffshore().replace('\0', ' ')); ps.setString(12, pdenDescDto.getField().replace('\0', ' ')); ps.setString(13, pdenDescDto.getReservoir().replace('\0', ' ')); ps.setString(14, pdenDescDto.getLease_no().replace('\0', ' ')); ps.setString(15, pdenDescDto.getCommingle_no().replace('\0', ' ')); if (pdenDescDto.getCommingle_date() != ""){ ps.setDate(16, java.sql.Date.valueOf(pdenDescDto.getCommingle_date())); } if (pdenDescDto.getCommingle_date() == ""){ ps.setNull(16, java.sql.Types.DATE); } ps.setString(17, pdenDescDto.getCounty_id().replace('\0', ' ')); ps.setString(18, pdenDescDto.getCounty().replace('\0', ' ')); ps.setString(19, pdenDescDto.getWell_no().replace('\0', ' ')); ps.setDouble(20, pdenDescDto.getCurr_oper_id()); ps.setString(21, pdenDescDto.getCurr_oper_no().replace('\0', ' ')); ps.setString(22, pdenDescDto.getCurr_oper_name().replace('\0', ' ')); ps.setString(23, pdenDescDto.getLiq_gath_name_1().replace('\0', ' ')); ps.setString(24, pdenDescDto.getGas_gath_name_1().replace('\0', ' ')); ps.setString(25, pdenDescDto.getStatus().replace('\0', ' ')); ps.setString(26, pdenDescDto.getDrill_type().replace('\0', ' ')); ps.setDouble(27, pdenDescDto.getElevation()); ps.setString(28, pdenDescDto.getElevation_type().replace('\0', ' ')); ps.setDouble(29, pdenDescDto.getTotal_depth()); ps.setDouble(30, pdenDescDto.getUpper_perf()); ps.setDouble(31, pdenDescDto.getLower_perf()); ps.setDouble(32, pdenDescDto.getLiq_grav()); ps.setDouble(33, pdenDescDto.getGas_grav()); ps.setDouble(34, pdenDescDto.getLiq_daily()); ps.setDouble(35, pdenDescDto.getGas_daily()); ps.setDouble(36, pdenDescDto.getLiq_cum()); ps.setDouble(37, pdenDescDto.getGas_cum()); ps.setDouble(38, pdenDescDto.getLatitude()); ps.setDouble(39, pdenDescDto.getLongitude()); ps.setString(40, pdenDescDto.getLoc_remark().replace('\0', ' ')); if (pdenDescDto.getFirst_prod_date() != ""){ ps.setDate(41, java.sql.Date.valueOf(pdenDescDto.getFirst_prod_date())); } if (pdenDescDto.getFirst_prod_date() == ""){ ps.setNull(41, java.sql.Types.DATE); } if (pdenDescDto.getLast_prod_date() != ""){ ps.setDate(42, java.sql.Date.valueOf(pdenDescDto.getLast_prod_date())); } if (pdenDescDto.getLast_prod_date() == ""){ ps.setNull(42, java.sql.Types.DATE); } ps.setDouble(43, pdenDescDto.getLatest_liq()); ps.setDouble(44, pdenDescDto.getLatest_gas()); ps.setDouble(45, pdenDescDto.getLatest_wtr()); ps.setDouble(46, pdenDescDto.getLatest_wcnt()); ps.setDouble(47, pdenDescDto.getPrior12_liq()); ps.setDouble(48, pdenDescDto.getPrior12_gas()); ps.setDouble(49, pdenDescDto.getPrior12_wtr()); ps.setDouble(50, pdenDescDto.getFirst_liq()); ps.setDouble(51, pdenDescDto.getFirst_gas()); ps.setDouble(52, pdenDescDto.getFirst_wtr()); ps.setDouble(53, pdenDescDto.getFirst12_liq()); ps.setDouble(54, pdenDescDto.getFirst12_gas()); ps.setDouble(55, pdenDescDto.getFirst12_wtr()); ps.setDouble(56, pdenDescDto.getWtr_cum()); ps.setDouble(57, pdenDescDto.getWtr_year()); ps.setDouble(58, pdenDescDto.getLiq_year()); ps.setDouble(59, pdenDescDto.getGas_year()); ps.setString(60, pdenDescDto.getLocation().replace('\0', ' ')); ps.setString(61, pdenDescDto.getSection().replace('\0', ' ')); ps.setString(62, pdenDescDto.getQtr_qtr().replace('\0', ' ')); ps.setString(63, pdenDescDto.getMerid().replace('\0', ' ')); ps.setString(64, pdenDescDto.getOcs_area().replace('\0', ' ')); ps.setDouble(65, pdenDescDto.getGor()); ps.setDouble(66, pdenDescDto.get_yield()); ps.setDouble(67, pdenDescDto.getRkb_elev()); ps.setDouble(68, pdenDescDto.getWater_depth()); ps.setDouble(69, pdenDescDto.getRes_vert_depth()); ps.setDouble(70, pdenDescDto.getMax_vert_depth()); if (pdenDescDto.getSpud_date() != ""){ ps.setDate(71, java.sql.Date.valueOf(pdenDescDto.getSpud_date())); } if (pdenDescDto.getSpud_date() == ""){ ps.setNull(71, java.sql.Types.DATE); } ps.setDouble(72, pdenDescDto.getLatitude_botm()); ps.setDouble(73, pdenDescDto.getLongitude_botm()); ps.setDouble(74, pdenDescDto.getLatitude_ex1()); ps.setDouble(75, pdenDescDto.getLongitude_ex1()); ps.setDouble(76, pdenDescDto.getPrior_liq_cum()); ps.setDouble(77, pdenDescDto.getPrior_gas_cum()); ps.setDouble(78, pdenDescDto.getPrior_wtr_cum()); ps.setString(79, pdenDescDto.getBasin().replace('\0', ' ')); ps.setString(80, pdenDescDto.getCommon_oper_name().replace('\0', ' ')); ps.setString(81, pdenDescDto.getLessee_avail().replace('\0', ' ')); ps.setString(82, pdenDescDto.getPgc_area().replace('\0', ' ')); ps.setString(83, pdenDescDto.getQuad_name().replace('\0', ' ')); ps.setString(84, pdenDescDto.get_abstract().replace('\0', ' ')); ps.setString(85, pdenDescDto.getSurvey().replace('\0', ' ')); ps.setString(86, pdenDescDto.getTx_block().replace('\0', ' ')); ps.setString(87, pdenDescDto.getTx_sec().replace('\0', ' ')); ps.setString(88, pdenDescDto.getSubsurvey().replace('\0', ' ')); ps.setString(89, pdenDescDto.getTx_scrap().replace('\0', ' ')); ps.setString(90, pdenDescDto.getField_no().replace('\0', ' ')); ps.setDouble(91, pdenDescDto.getMonths_produced()); ps.setString(92, pdenDescDto.getReported_oper_name().replace('\0', ' ')); ps.setString(93, pdenDescDto.getFormation().replace('\0', ' ')); ps.setDouble(94, pdenDescDto.getPeak_gas()); ps.setDouble(95, pdenDescDto.getPeak_liq()); if (pdenDescDto.getLatest_test_date() != ""){ ps.setDate(96, java.sql.Date.valueOf(pdenDescDto.getLatest_test_date())); } if (pdenDescDto.getLatest_test_date() == ""){ ps.setNull(96, java.sql.Types.DATE); } ps.setDouble(97, pdenDescDto.getLatest_flow_res()); ps.setDouble(98, pdenDescDto.getLatest_whsip()); ps.setString(99, pdenDescDto.getTwp().replace('\0', ' ')); ps.setString(100, pdenDescDto.getRng().replace('\0', ' ')); ps.setDouble(101, pdenDescDto.getWell_id()); ps.setDouble(102, pdenDescDto.getLease_id()); ps.setString(103, pdenDescDto.getAlloc_plus().replace('\0', ' ')); ps.setDouble(104, pdenDescDto.getFirst6_liq()); ps.setDouble(105, pdenDescDto.getFirst6_gas()); ps.setDouble(106, pdenDescDto.getFirst6_wtr()); ps.setDouble(107, pdenDescDto.getFirst24_liq()); ps.setDouble(108, pdenDescDto.getFirst24_gas()); ps.setDouble(109, pdenDescDto.getFirst24_wtr()); ps.setDouble(110, pdenDescDto.getFirst60_liq()); ps.setDouble(111, pdenDescDto.getFirst60_gas()); ps.setDouble(112, pdenDescDto.getFirst60_wtr()); ps.setDouble(113, pdenDescDto.getFirst6_boe()); ps.setDouble(114, pdenDescDto.getFirst12_boe()); ps.setDouble(115, pdenDescDto.getFirst24_boe()); ps.setDouble(116, pdenDescDto.getFirst60_boe()); ps.setDouble(117, pdenDescDto.getFirst6_mmcfge()); ps.setDouble(118, pdenDescDto.getFirst12_mmcfge()); ps.setDouble(119, pdenDescDto.getFirst24_mmcfge()); ps.setDouble(120, pdenDescDto.getFirst60_mmcfge()); ps.setDouble(121, pdenDescDto.getDecl_3mo_liq()); ps.setDouble(122, pdenDescDto.getDecl_3mo_gas()); ps.setDouble(123, pdenDescDto.getDecl_3mo_wtr()); ps.setDouble(124, pdenDescDto.getDecl_12mo_liq()); ps.setDouble(125, pdenDescDto.getDecl_12mo_gas()); ps.setDouble(126, pdenDescDto.getDecl_12mo_wtr()); ps.setDouble(127, pdenDescDto.getDecl_24mo_liq()); ps.setDouble(128, pdenDescDto.getDecl_24mo_gas()); ps.setDouble(129, pdenDescDto.getDecl_24mo_wtr()); ps.setDouble(130, pdenDescDto.getDecl_60mo_liq()); ps.setDouble(131, pdenDescDto.getDecl_60mo_gas()); ps.setDouble(132, pdenDescDto.getDecl_60mo_wtr()); ps.setDouble(133, pdenDescDto.getGor_2nd_mo()); ps.setDouble(134, pdenDescDto.getGor_latest_mo()); ps.setDouble(135, pdenDescDto.getCfged_prac_ip()); ps.setDouble(136, pdenDescDto.getCfged_cur_rate()); ps.setDouble(137, pdenDescDto.getMmcfge_cum()); ps.setDouble(138, pdenDescDto.getBcfge_cum()); ps.setDouble(139, pdenDescDto.getGor_cum()); ps.setDouble(140, pdenDescDto.getMax_actv_wells()); ps.setDouble(141, pdenDescDto.getPeak_liq_daily()); ps.setDouble(142, pdenDescDto.getPeak_gas_daily()); ps.setDouble(143, pdenDescDto.getPeak_boe()); ps.setDouble(144, pdenDescDto.getPeak_mmcfge()); ps.setDouble(145, pdenDescDto.getYield_2nd_mo()); ps.setDouble(146, pdenDescDto.getYield_latest_mo()); ps.setDouble(147, pdenDescDto.getLiq_prac_ip_daily()); ps.setDouble(148, pdenDescDto.getGas_prac_ip_daily()); ps.setDouble(149, pdenDescDto.getLiq_max_daily()); ps.setDouble(150, pdenDescDto.getGas_max_daily()); ps.setDouble(151, pdenDescDto.getWtr_daily()); ps.setDouble(152, pdenDescDto.getBoe_cum()); ps.setDouble(153, pdenDescDto.getMax_wtr_daily()); ps.setDouble(154, pdenDescDto.getBoe_prac_ip()); if (pdenDescDto.getPeak_gas_month() != ""){ ps.setDate(155, java.sql.Date.valueOf(pdenDescDto.getPeak_gas_month())); } if (pdenDescDto.getPeak_gas_month() == ""){ ps.setNull(155, java.sql.Types.DATE); } ps.setDouble(156, pdenDescDto.getPeak_gas_month_no()); if (pdenDescDto.getPeak_liq_month() != ""){ ps.setDate(157, java.sql.Date.valueOf(pdenDescDto.getPeak_liq_month())); } if (pdenDescDto.getPeak_liq_month() == ""){ ps.setNull(157, java.sql.Types.DATE); } ps.setDouble(158, pdenDescDto.getPeak_liq_month_no()); if (pdenDescDto.getPeak_daily_gas_month() != ""){ ps.setDate(159, java.sql.Date.valueOf(pdenDescDto.getPeak_daily_gas_month())); } if (pdenDescDto.getPeak_daily_gas_month() == ""){ ps.setNull(159, java.sql.Types.DATE); } ps.setDouble(160, pdenDescDto.getPeak_daily_gas_month_no()); if (pdenDescDto.getPeak_daily_liq_month() != ""){ ps.setDate(161, java.sql.Date.valueOf(pdenDescDto.getPeak_daily_liq_month())); } if (pdenDescDto.getPeak_daily_liq_month() == ""){ ps.setNull(161, java.sql.Types.DATE); } ps.setDouble(162, pdenDescDto.getPeak_daily_liq_month_no()); if (pdenDescDto.getPeak_boe_month() != ""){ ps.setDate(163, java.sql.Date.valueOf(pdenDescDto.getPeak_boe_month())); } if (pdenDescDto.getPeak_boe_month() == ""){ ps.setNull(163, java.sql.Types.DATE); } ps.setDouble(164, pdenDescDto.getPeak_boe_month_no()); if (pdenDescDto.getPeak_mmcfge_month() != ""){ ps.setDate(165, java.sql.Date.valueOf(pdenDescDto.getPeak_mmcfge_month())); } if (pdenDescDto.getPeak_mmcfge_month() == ""){ ps.setNull(165, java.sql.Types.DATE); } ps.setDouble(166, pdenDescDto.getPeak_mmcfge_month_no()); ps.setInt(167, id); // ps.setInt(26, id); ps.addBatch(); if(++count % batchSize == 0) { ps.executeBatch(); ps.clearBatch(); } } ps.executeBatch(); // insert non batch remaining data ps.clearBatch(); connection.commit(); endproc = connection.prepareCall("{ call process_control.end_transaction_run(?) }"); endproc.setInt(1, id); endproc.execute(); } catch (SQLException e ) { String sqlMessage = e.getMessage(); String sqlState = e.getSQLState(); int vendorCode = e.getErrorCode(); logger.error("Exception occurred:"); logger.error("Message: " + sqlMessage); logger.error("SQL state: " + sqlState); logger.error("Vendor code: " + vendorCode + "\n----------------"); if (connection != null) { try { logger.error("Transaction is being rolled back"); connection.rollback(); } catch(SQLException excep) { String sqlmessage = excep.getMessage(); String sqlstate = excep.getSQLState(); int vendorcode = excep.getErrorCode(); logger.error("Exception occurred:"); logger.error("Message: " + sqlmessage); logger.error("SQL state: " + sqlstate); logger.error("Vendor code: " + vendorcode + "\n----------------"); throw new LoadPdenDescException("Batch Insertion Roll Back failed", "BATCH_INSERT_ROLLBACK_ERROR", excep); } } throw new LoadPdenDescException("Batch Insertion failed", "BATCH_INSERT_ERROR", e); } finally { if (endproc != null) { endproc.close(); } if (proc != null) { proc.close(); } if (ps != null) { ps.close(); } connection.setAutoCommit(true); if (connection != null) { connection.close(); } } return drillingInfoDto; } }