List of usage examples for java.sql PreparedStatement executeBatch
int[] executeBatch() throws SQLException;
From source file:com.viettel.logistic.wms.dao.StockGoodsSerialDAO.java
public ResultDTO updateOrInsertSyncImportGoodsRevoke(StockTransDTO stockTrans, StockTransDetailDTO stockTransDetail, List<StockTransSerialDTO> lstStockTransSerial, Connection connection, String serialStatus, boolean isUpdate) { ResultDTO resultDTO = new ResultDTO(); //THONG TIN SO LUONG NHAP Double amount = 0D;/*from www. j a v a 2s .co m*/ Double amountIssue = 0D; //Cau lenh cap nhat serial StringBuilder sqlStockGoodsSerial = new StringBuilder(); List paramsStockGoodsSerial; List paramsStockTransSerial; PreparedStatement prstmtInsertStockTransSerial; PreparedStatement prstmtInsertStockGoodsSerial; String fromSerial; String toSerial; String serial; String prefixSerial = ""; String suffixFromSerial; String suffixToSerial; int numberNeedToCommit = 0; int numberOfSuccess = 0; int numberOfFail = 0; if (isUpdate) {//Neu la cap nhat sqlStockGoodsSerial.append(" UPDATE stock_goods_serial "); sqlStockGoodsSerial.append( " SET status = ?, change_date = to_date(?,'dd/MM/yyyy hh24:mi:ss'), cell_code = ?, barcode = ?, "); sqlStockGoodsSerial.append(" bincode = ?, goods_state = ?, import_stock_trans_id = ?, order_id = ?, "); sqlStockGoodsSerial.append(" owner_id = ?, owner_type = ? "); sqlStockGoodsSerial.append(" WHERE cust_id = ? AND goods_id = ? AND serial = ? "); sqlStockGoodsSerial.append(" AND status = ? "); sqlStockGoodsSerial.append(" LOG ERRORS REJECT LIMIT UNLIMITED"); } else {//Neu la them moi sqlStockGoodsSerial .append(" INSERT INTO stock_goods_serial (id, cust_id, owner_id, owner_type, goods_id,"); sqlStockGoodsSerial.append(" goods_state, status,sale_type, change_user,"); sqlStockGoodsSerial.append(" price,channel_type_id, barcode, change_date,"); sqlStockGoodsSerial.append(" import_date, sale_date, bincode, add_infor, cell_code,"); sqlStockGoodsSerial.append(" serial,partner_id,import_stock_trans_id,order_id) "); sqlStockGoodsSerial.append(" VALUES (STOCK_GOODS_SERIAL_SEQ.nextval,?,?,?,?,?,?,?,?,TO_NUMBER(?),"); sqlStockGoodsSerial.append(" TO_NUMBER(?),?,to_date(?,'dd/MM/yyyy hh24:mi:ss'),"); sqlStockGoodsSerial.append( " to_date(?,'dd/MM/yyyy hh24:mi:ss'),?,?,?,?,?,TO_NUMBER(?),TO_NUMBER(?),TO_NUMBER(?)) "); sqlStockGoodsSerial.append(" LOG ERRORS REJECT LIMIT UNLIMITED "); } //Cau lenh them moi giao dich StringBuilder sqlStockTransSerial = new StringBuilder(); sqlStockTransSerial.append(" INSERT INTO stock_trans_serial "); sqlStockTransSerial.append(" ( stock_trans_serial_id, stock_trans_id,"); sqlStockTransSerial.append(" stock_trans_detail_id, stock_trans_date, goods_id,"); sqlStockTransSerial.append(" goods_code, goods_name, goods_state, goods_unit_type,"); sqlStockTransSerial.append(" from_serial, to_serial,"); sqlStockTransSerial.append(" amount_order, amount_real, bincode, barcode, "); sqlStockTransSerial.append(" create_datetime,"); sqlStockTransSerial.append(" cell_code ) "); sqlStockTransSerial.append(" VALUES (STOCK_TRANS_SERIAL_SEQ.nextval,?,?,"); sqlStockTransSerial.append(" to_date(?,'dd/MM/yyyy hh24:mi:ss'),?,?,?,?,?,?,?,?,?,?,?,"); sqlStockTransSerial.append(" to_date(?,'dd/MM/yyyy hh24:mi:ss'),?) "); sqlStockTransSerial.append(" LOG ERRORS REJECT LIMIT UNLIMITED "); try { //3. TAO PREPARE STATEMENT prstmtInsertStockTransSerial = connection.prepareStatement(sqlStockTransSerial.toString()); prstmtInsertStockGoodsSerial = connection.prepareStatement(sqlStockGoodsSerial.toString()); //Chi tiet serial for (StockTransSerialDTO stockTransSerial : lstStockTransSerial) { fromSerial = stockTransSerial.getFromSerial(); toSerial = stockTransSerial.getToSerial(); stockTransSerial.setStockTransId(stockTrans.getStockTransId()); stockTransSerial.setStockTransDetailId(stockTransDetail.getStockTransDetailId()); numberNeedToCommit++; //SET PARAMS FOR STOCK_TRANS_SERIAL paramsStockTransSerial = setParamsStockTransSerial(stockTransSerial); //SET PARAMS AND ADD TO BATCH for (int idx = 0; idx < paramsStockTransSerial.size(); idx++) { prstmtInsertStockTransSerial.setString(idx + 1, DataUtil.nvl(paramsStockTransSerial.get(idx), "").toString()); } prstmtInsertStockTransSerial.addBatch(); //Insert chi tiet serial if (!StringUtils.isInteger(fromSerial) || !StringUtils.isInteger(toSerial)) {//Serial la ky tu serial = fromSerial; if (isUpdate) { paramsStockGoodsSerial = setParamForStockGoodsSerialInvoke(stockTrans, stockTransDetail, stockTransSerial, serial, serialStatus); } else { paramsStockGoodsSerial = setParamsStockGoodsSerial(stockTrans, stockTransDetail, stockTransSerial, serial, serialStatus); } //SET PARAMS AND ADD TO BATCH for (int idx = 0; idx < paramsStockGoodsSerial.size(); idx++) { try { prstmtInsertStockGoodsSerial.setString(idx + 1, DataUtil.nvl(paramsStockGoodsSerial.get(idx), "").toString()); } catch (Exception e) { System.out.println(idx); } } prstmtInsertStockGoodsSerial.addBatch(); //bo sung them amount issue } else {//Serial la so //Kiem tra do dai serial kneu >19 thi cat do kieu Long chi co do dai toi da 19 int iLengthSuffixSerial = 0; if (fromSerial.length() > Constants.SERIAL_LIMIT) { prefixSerial = fromSerial.substring(0, fromSerial.length() - Constants.SERIAL_LIMIT); suffixFromSerial = fromSerial.substring(fromSerial.length() - Constants.SERIAL_LIMIT, fromSerial.length()); suffixToSerial = toSerial.substring(toSerial.length() - Constants.SERIAL_LIMIT, toSerial.length()); iLengthSuffixSerial = suffixFromSerial.length(); } else { suffixFromSerial = fromSerial; suffixToSerial = toSerial; iLengthSuffixSerial = fromSerial.length(); } // String tmpSuffixSerial; for (Long lSerial = Long.parseLong(suffixFromSerial); lSerial <= Long .parseLong(suffixToSerial); lSerial++) { tmpSuffixSerial = DataUtil.lPad(lSerial.toString(), "0", iLengthSuffixSerial); serial = prefixSerial + tmpSuffixSerial; if (isUpdate) { paramsStockGoodsSerial = setParamForStockGoodsSerialInvoke(stockTrans, stockTransDetail, stockTransSerial, serial, serialStatus); } else { paramsStockGoodsSerial = setParamsStockGoodsSerial(stockTrans, stockTransDetail, stockTransSerial, serial, serialStatus); } //SET PARAMS AND ADD TO BATCH for (int idx = 0; idx < paramsStockGoodsSerial.size(); idx++) { prstmtInsertStockGoodsSerial.setString(idx + 1, DataUtil.nvl(paramsStockGoodsSerial.get(idx), "").toString()); } prstmtInsertStockGoodsSerial.addBatch(); //Bo sung them thong tin so luong amount issue //amountIssue++; } } //END IF if (numberNeedToCommit >= Constants.COMMIT_NUM) { try { prstmtInsertStockGoodsSerial.executeBatch(); prstmtInsertStockTransSerial.executeBatch(); numberOfSuccess = numberOfSuccess + numberNeedToCommit; } catch (Exception ex) { numberOfFail = numberOfFail + numberNeedToCommit; } numberNeedToCommit = 0; } } //END FOR if (numberNeedToCommit > 0) { try { prstmtInsertStockGoodsSerial.executeBatch(); prstmtInsertStockTransSerial.executeBatch(); numberOfSuccess += numberNeedToCommit; } catch (Exception ex) { // connection.rollback(); numberOfFail += numberNeedToCommit; } } prstmtInsertStockGoodsSerial.close(); prstmtInsertStockTransSerial.close(); } catch (SQLException ex) { ex.printStackTrace(); } //lay so luong hang hoa insert vao ban err$_ List<StockGoodsSerialInforDTO> lstError = getListErrorImportRevoke(stockTrans.getStockTransId()); int amountError = 0; if (lstError != null) { amountError = lstError.size(); } Double strAmount = Double.parseDouble(lstStockTransSerial.size() + ""); numberOfSuccess = Integer.parseInt(String.format("%.0f", strAmount)) - amountError;//tru so luong hang insert loi => so luong hang insert thanh cong numberOfFail = amountError;//so luong hang loi do ta ton tai serial cua khach hang trong kho amountIssue = (double) numberOfSuccess; // resultDTO.setMessage(ParamUtils.SUCCESS); resultDTO.setQuantityFail(numberOfFail); resultDTO.setQuantitySucc(numberOfSuccess); resultDTO.setAmount(amount); resultDTO.setAmountIssue(amountIssue); // tra ve list serial loi resultDTO.setLstStockGoodsSerialInforDTO(lstError); return resultDTO; }
From source file:com.pari.nm.utils.db.ReportDBHelper.java
public static void insertIntoTempPaginationTable(TableDefinition tableDefinition, ServiceDescriptor descriptor, ServiceContainer sImpl, String reportId, String sessionId, ArrayList<String> childColNameList) { Connection c = null;//w ww .ja v a 2s . co m PreparedStatement ps = null; if (sImpl != null) { UserSession session = UserSessionManager.getInstance().getCurrentUserSession(); PaginationReportDbHelper.instance.setLoginTimeZoneName(session.getLoginTimeZone()); try { StringBuffer sb = new StringBuffer(); String tblName = getTempPaginationTblName(reportId, sessionId); if (tblName == null) { throw new Exception( "No Table Exist with ReportId:\t" + reportId + "and SessionId:\t" + sessionId); } c = DBHelper.getConnection(); if (c == null) { logger.error("Unable to get Connection."); return; } Service[] allServs = sImpl.getAllServices(); if (allServs == null || allServs.length <= 0) { logger.info("Now Rows fetchd for ReportId:" + reportId + "and SessionId:\t" + sessionId); } else { logger.info("Number of Records are:\t" + allServs.length); } // Adding to check sb = new StringBuffer(); sb.append("INSERT INTO " + tblName + "\t Values("); ArrayList<String> colNameList = null; if (reportId.equals("extended_device_attributes_report")) { colNameList = getColumnInfo(tableDefinition); } else { colNameList = getColumnInfo(descriptor); } if (colNameList != null && !colNameList.isEmpty()) { for (int i = 0; i < colNameList.size() - 1; i++) { sb.append("?,"); } sb.append("?)"); } try { ps = c.prepareStatement(sb.toString()); } catch (SQLException e1) { logger.error("Exception Occured while Executing statement:", e1); } Service[] rowsArr = sImpl.getAllServices(); if (rowsArr == null) { logger.info("Report Contians No Data."); } else { for (Service rowService : rowsArr) { int i = 1; if (colNameList != null && !colNameList.isEmpty()) { for (String colName : colNameList) { try { Object value = rowService.getAttribute(colName); if (value == null) { ps.setString(i++, ""); } else if (value instanceof ServiceContainerImpl) { ps.setString(i++, String.valueOf(value)); if (value != null) { insertChildTableInfo(c, value, reportId, sessionId, sImpl, childColNameList, tableDefinition, colName); } } else { ColumnDefinition columnDefinition = tableDefinition.getColumn(colName); if (null == columnDefinition) { ps.setString(i++, String.valueOf(value)); continue; } RendererType renderer = columnDefinition.getRenderer(); switch (renderer) { case BOOLEAN: { value = (true == Boolean.valueOf(String.valueOf(value))) ? "Yes" : "No"; ps.setString(i++, String.valueOf(value)); break; } case DATE: case DATE_TIME: { String temp = String.valueOf(value); Long longValue = (true == temp.isEmpty()) ? new Long(0L) : Long.valueOf(temp); value = (0 == longValue || -1 == longValue) ? "" : PaginationReportDbHelper.instance.convertDateFormat(longValue, "EEE, MMM d, yyyy HH:mm:ss Z"); // value = (0 == longValue) ? "" : new SimpleDateFormat( // "EEE, MMM d, yyyy HH:mm:ss Z").format(new Date(longValue)); ps.setString(i++, String.valueOf(value)); break; } case EOL_DATE: case EOL_DIFF_DATE: { String temp = String.valueOf(value); Long longValue = (true == temp.isEmpty()) ? new Long(0L) : Long.valueOf(temp); value = (0 == longValue) ? "" : PaginationReportDbHelper.instance.convertDateFormat(longValue, "MMM d, yyyy"); // value = (0 == longValue) ? "" : new SimpleDateFormat("MMM d, yyyy") // .format(new Date(longValue)); ps.setString(i++, String.valueOf(value)); break; } default: { ps.setString(i++, String.valueOf(value)); } } } } catch (SQLException e) { logger.error( "Exception Occured while Inserting Data into Temporary Pagination Table:", e); return; } } } try { ps.addBatch(); } catch (SQLException e) { e.printStackTrace(); } } } try { int[] count = ps.executeBatch(); logger.info("Number of Statements Executed was:\t" + count.length); } catch (SQLException e1) { logger.error("Exception Occured while Executing Batch Insert.", e1); } } catch (Exception e) { logger.error("Exception Occured while Inserting Data into Temporary Pagination Table:", e); } finally { if (ps != null) { try { ps.close(); } catch (SQLException e) { logger.error("Exception Occured while Closing the Prepared Statement Object.", e); } } if (c != null) { try { c.setAutoCommit(true); DBHelper.releaseConnection(c); } catch (SQLException e) { logger.error("Exception Occured while Closing the Connection Object.", e); } } } } }
From source file:HSqlPrimerDesign.java
@SuppressWarnings("Duplicates") public static void locations(Connection connection) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException, IOException { long time = System.nanoTime(); String base = new File("").getAbsolutePath(); DpalLoad.main(new String[0]); Dpal_Inst = DpalLoad.INSTANCE_WIN64; System.out.println(Dpal_Inst); Connection db = connection;/*from w ww. jav a2 s . c o m*/ db.setAutoCommit(false); Statement stat = db.createStatement(); PrintWriter log = new PrintWriter(new File("javalog.log")); stat.execute("SET FILES LOG FALSE;"); PreparedStatement st = db.prepareStatement("INSERT INTO Primerdb.MatchedPrimers(" + "Primer, PrimerMatch, Comp,FragAVG,FragVAR,H2SD,L2SD, Cluster, Strain)" + "Values(?,?,?,?,?,?,?,?,?)"); ResultSet call = stat.executeQuery("Select * From Primerdb.Phages;"); List<String[]> phages = new ArrayList<>(); while (call.next()) { String[] r = new String[3]; r[0] = call.getString("Strain"); r[1] = call.getString("Cluster"); r[2] = call.getString("Name"); phages.add(r); // if(strain.equals("-myco")) { // if (r[2].equals("xkcd")) { // strain = r[0]; // } // }else if(strain.equals("-arthro")){ // if (r[2].equals("ArV1")) { // strain = r[0]; // } // } } call.close(); Set<String> strains = phages.stream().map(y -> y[0]).collect(Collectors.toSet()); for (String x : strains) { Set<String> clust = phages.stream().filter(y -> y[0].equals(x)).map(y -> y[1]) .collect(Collectors.toSet()); String[] clusters = clust.toArray(new String[clust.size()]); // String z ="A1"; for (String z : clusters) { System.out.println("Starting:" + z); List<Primer> primers = new ArrayList<>(); Set<Matches> matched = new HashSet<>(); Set<String> clustphage = phages.stream().filter(a -> a[0].equals(x) && a[1].equals(z)) .map(a -> a[2]).collect(Collectors.toSet()); String[] clustphages = clustphage.toArray(new String[clustphage.size()]); if (clustphages.length > 1) { try { ResultSet resultSet = stat .executeQuery("Select * from primerdb.primers" + " where Strain ='" + x + "' and Cluster ='" + z + "' and UniqueP = true" + " and Hairpin = false"); while (resultSet.next()) { Primer primer = new Primer(resultSet.getString("Sequence")); primer.setTm(resultSet.getDouble("Tm")); primers.add(primer); } resultSet.close(); } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + z); } System.out.println(primers.size()); Set<Primer> primerlist2 = primers.stream().collect(Collectors.toSet()); Primer[] primers2 = primerlist2.toArray(new Primer[primerlist2.size()]); Map<String, Map<CharSequence, List<Integer>>> locations = Collections .synchronizedMap(new HashMap<>()); clustphage.stream().forEach(phage -> { String[] seqs = Fasta.parse(base + "/Fastas/" + phage + ".fasta"); String sequence = seqs[0] + seqs[1]; Map<String, List<Integer>> seqInd = new HashMap<>(); for (int i = 0; i <= sequence.length() - 10; i++) { String sub = sequence.substring(i, i + 10); if (seqInd.containsKey(sub)) { seqInd.get(sub).add(i); } else { List<Integer> list = new ArrayList<>(); list.add(i); seqInd.put(sub, list); } } Map<CharSequence, List<Integer>> alllocs = new HashMap<>(); for (Primer primer : primers2) { List<Integer> locs = new ArrayList<>(); String sequence1 = primer.getSequence(); String frag = sequence1.substring(0, 10); List<Integer> integers = seqInd.get(frag); if (integers != null) { for (Integer i : integers) { if ((sequence1.length() + i) < sequence.length() && sequence.substring(i, sequence1.length() + i).equals(sequence1)) { locs.add(i); } } } alllocs.put(sequence1, locs); } locations.put(phage, alllocs); }); System.out.println("locations found"); System.out.println((System.nanoTime() - time) / Math.pow(10, 9) / 60.0); final int[] k = new int[] { 0 }; primerlist2.parallelStream().forEach(a -> { int matches = 0; int i = 0; while (primers2[i] != a) { i++; } for (int j = i + 1; j < primers2.length; j++) { double[] frags = new double[clustphages.length]; int phageCounter = 0; Primer b = primers2[j]; boolean match = true; if (matches > 0) { break; } if (Math.abs(a.getTm() - b.getTm()) > 5.0 || a.getSequence().equals(b.getSequence())) { continue; } for (String phage : clustphages) { List<Integer> loc1 = locations.get(phage).get(a.getSequence()); List<Integer> loc2 = locations.get(phage).get(b.getSequence()); // if(loc1.size()==0){ // System.out.println(phage+" "+a.getSequence()); // } if (loc1.size() == 0 || loc2.size() == 0) { // if (loc1.size()!=1||loc2.size()!=1){ match = false; break; } boolean found = false; int fragCount = 0; int l1 = loc1.get(0); int l2 = loc2.get(0); int count1 = 0; int count2 = 0; int frag = Math.abs(l1 - l2); while (!found) { if (frag >= 500 && frag <= 2000) { fragCount++; if (++count1 < loc1.size()) l1 = loc1.get(count1); else if (++count2 < loc2.size()) l2 = loc2.get(count2); } else if (l1 < l2 && frag < 500) { count2++; } else if (l1 > l2 && frag < 500) { count1++; } else if (l1 > l2 && frag > 2000) { count2++; } else if (l1 < l2 && frag > 2000) { count1++; } else { break; } if (count1 < loc1.size() && count2 < loc2.size()) { l1 = loc1.get(count1); l2 = loc2.get(count2); frag = Math.abs(l1 - l2); } else { if (fragCount == 1) { found = true; frags[phageCounter++] = frag + 0.0; } else { break; } } } if (!found) { match = false; break; } } if (match) { matches++; matched.add(new Matches(a, b, frags)); } } // k[0]++; // System.out.println(k[0]); }); System.out.println((System.nanoTime() - time) / Math.pow(10, 9) / 60.0); System.out.println("Primers matched"); int c = 0; int i = 0; try { for (Matches primerkey : matched) { c++; String primer1 = primerkey.one.getSequence(); String primer2 = primerkey.two.getSequence(); st.setString(1, primer1); st.setString(2, primer2); st.setDouble(3, complementarity(primer1, primer2, Dpal_Inst)); st.setDouble(4, primerkey.stats.getMean()); st.setDouble(5, primerkey.stats.getVariance()); st.setDouble(6, primerkey.stats.getMean() + 2 * primerkey.stats.getStandardDeviation()); st.setDouble(7, primerkey.stats.getMean() - 2 * primerkey.stats.getStandardDeviation()); st.setString(8, z); st.setString(9, x); st.addBatch(); i++; if (i == 1000) { i = 0; st.executeBatch(); db.commit(); } } if (i > 0) { st.executeBatch(); db.commit(); } } catch (SQLException e) { e.printStackTrace(); System.out.println("Error occurred at " + x + " " + z); } System.out.println(c); } log.println(z); log.flush(); System.gc(); } } stat.execute("SET FILES LOG TRUE;"); st.close(); stat.close(); System.out.println("Matches Submitted"); }
From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java
/** * Delete the records of external APIStore details. * * @param apiId APIIdentifier/*from www.ja v a2 s .c om*/ * @param apiStoreSet APIStores set * @return added/failed * @throws APIManagementException */ public boolean deleteExternalAPIStoresDetails(APIIdentifier apiId, Set<APIStore> apiStoreSet) throws APIManagementException { Connection conn = null; PreparedStatement ps = null; boolean state = false; try { conn = APIMgtDBUtil.getConnection(); conn.setAutoCommit(false); String sqlQuery = SQLConstants.REMOVE_EXTERNAL_API_STORE_SQL; //Get API Id int apiIdentifier; apiIdentifier = getAPIID(apiId, conn); if (apiIdentifier == -1) { String msg = "Could not load API record for: " + apiId.getApiName(); log.error(msg); } ps = conn.prepareStatement(sqlQuery); for (Object storeObject : apiStoreSet) { APIStore store = (APIStore) storeObject; ps.setInt(1, apiIdentifier); ps.setString(2, store.getName()); ps.setString(3, store.getType()); ps.addBatch(); } ps.executeBatch(); conn.commit(); state = true; } catch (SQLException e) { if (conn != null) { try { conn.rollback(); } catch (SQLException e1) { log.error("Failed to rollback deleting external apistore details ", e1); } } log.error("Failed to delete external apistore details", e); state = false; } catch (APIManagementException e) { log.error("Failed to delete external apistore details", e); state = false; } finally { APIMgtDBUtil.closeAllConnections(ps, conn, null); } return state; }
From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java
/** * Updateexternal APIStores details to which APIs published * * @param apiIdentifier API Identifier//from w ww.j a v a2s. co m * @throws APIManagementException if failed to add Application */ public void updateExternalAPIStoresDetails(APIIdentifier apiIdentifier, Set<APIStore> apiStoreSet, Connection conn) throws APIManagementException, SQLException { PreparedStatement ps = null; try { conn.setAutoCommit(false); //This query to add external APIStores to database table String sqlQuery = SQLConstants.UPDATE_EXTERNAL_API_STORE_SQL; ps = conn.prepareStatement(sqlQuery); //Get API Id int apiId; apiId = getAPIID(apiIdentifier, conn); if (apiId == -1) { String msg = "Could not load API record for: " + apiIdentifier.getApiName(); log.error(msg); } for (Object storeObject : apiStoreSet) { APIStore store = (APIStore) storeObject; ps.setString(1, store.getEndpoint()); ps.setString(2, store.getType()); ps.setInt(3, apiId); ps.setString(4, store.getName()); ps.addBatch(); } ps.executeBatch(); ps.clearBatch(); conn.commit(); } catch (SQLException e) { log.error("Error while updating External APIStore details to the database for API : ", e); } finally { APIMgtDBUtil.closeAllConnections(ps, null, null); } }
From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java
/** * Store external APIStore details to which APIs successfully published * * @param apiId APIIdentifier/*from w ww . j ava 2s . c o m*/ * @param apiStoreSet APIStores set * @return added/failed * @throws APIManagementException */ public boolean addExternalAPIStoresDetails(APIIdentifier apiId, Set<APIStore> apiStoreSet) throws APIManagementException { Connection conn = null; PreparedStatement ps = null; boolean state = false; try { conn = APIMgtDBUtil.getConnection(); conn.setAutoCommit(false); //This query to add external APIStores to database table String sqlQuery = SQLConstants.ADD_EXTERNAL_API_STORE_SQL; //Get API Id int apiIdentifier; apiIdentifier = getAPIID(apiId, conn); if (apiIdentifier == -1) { String msg = "Could not load API record for: " + apiId.getApiName(); log.error(msg); } ps = conn.prepareStatement(sqlQuery); for (Object storeObject : apiStoreSet) { APIStore store = (APIStore) storeObject; ps.setInt(1, apiIdentifier); ps.setString(2, store.getName()); ps.setString(3, store.getDisplayName()); ps.setString(4, store.getEndpoint()); ps.setString(5, store.getType()); ps.addBatch(); } ps.executeBatch(); conn.commit(); state = true; } catch (SQLException e) { if (conn != null) { try { conn.rollback(); } catch (SQLException e1) { log.error("Failed to rollback storing external apistore details ", e1); } } log.error("Failed to store external apistore details", e); state = false; } catch (APIManagementException e) { log.error("Failed to store external apistore details", e); state = false; } finally { APIMgtDBUtil.closeAllConnections(ps, conn, null); } return state; }
From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java
/** * Remove scope entries from DB, when delete APIs * * @param apiIdentifier The {@link APIIdentifier} of the API */// ww w .ja v a 2 s.com private void removeAPIScope(APIIdentifier apiIdentifier) throws APIManagementException { Set<Scope> scopes = getAPIScopes(apiIdentifier); Connection connection = null; PreparedStatement prepStmt = null; PreparedStatement deleteOauth2ResourceScopePrepStmt = null; PreparedStatement deleteOauth2ScopePrepStmt = null; int scopeId; int apiId = -1; String deleteAPIScopeQuery = SQLConstants.REMOVE_FROM_API_SCOPES_SQL; String deleteOauth2ScopeQuery = SQLConstants.REMOVE_FROM_OAUTH_SCOPE_SQL; String deleteOauth2ResourceScopeQuery = SQLConstants.REMOVE_FROM_OAUTH_RESOURCE_SQL; try { connection = APIMgtDBUtil.getConnection(); connection.setAutoCommit(false); prepStmt = connection.prepareStatement(deleteAPIScopeQuery); prepStmt.setInt(1, apiId); prepStmt.execute(); if (!scopes.isEmpty()) { deleteOauth2ResourceScopePrepStmt = connection.prepareStatement(deleteOauth2ResourceScopeQuery); deleteOauth2ScopePrepStmt = connection.prepareStatement(deleteOauth2ScopeQuery); for (Scope scope : scopes) { scopeId = scope.getId(); deleteOauth2ResourceScopePrepStmt.setInt(1, scopeId); deleteOauth2ResourceScopePrepStmt.addBatch(); deleteOauth2ScopePrepStmt.setInt(1, scopeId); deleteOauth2ScopePrepStmt.addBatch(); } deleteOauth2ResourceScopePrepStmt.executeBatch(); deleteOauth2ScopePrepStmt.executeBatch(); } connection.commit(); } catch (SQLException e) { handleException("Error while removing the scopes for the API: " + apiIdentifier.getApiName() + " from the database", e); } finally { APIMgtDBUtil.closeAllConnections(deleteOauth2ResourceScopePrepStmt, null, null); APIMgtDBUtil.closeAllConnections(deleteOauth2ScopePrepStmt, null, null); APIMgtDBUtil.closeAllConnections(prepStmt, connection, null); } }
From source file:org.wso2.carbon.identity.application.mgt.dao.impl.ApplicationDAOImpl.java
/** * @param applicationId/*from www . j a v a 2 s . com*/ * @param localAndOutboundAuthConfig * @param connection * @throws SQLException * @throws IdentityApplicationManagementException */ private void updateLocalAndOutboundAuthenticationConfiguration(int applicationId, LocalAndOutboundAuthenticationConfig localAndOutboundAuthConfig, Connection connection) throws SQLException, IdentityApplicationManagementException { int tenantID = CarbonContext.getThreadLocalCarbonContext().getTenantId(); PreparedStatement updateAuthTypePrepStmt = null; if (localAndOutboundAuthConfig == null) { // no local or out-bound configuration for this service provider. return; } PreparedStatement storeSendAuthListOfIdPsPrepStmt = null; try { storeSendAuthListOfIdPsPrepStmt = connection .prepareStatement(ApplicationMgtDBQueries.UPDATE_BASIC_APPINFO_WITH_SEND_AUTH_LIST_OF_IDPS); // IS_SEND_LOCAL_SUBJECT_ID=? WHERE TENANT_ID= ? AND ID = ? storeSendAuthListOfIdPsPrepStmt.setString(1, localAndOutboundAuthConfig.isAlwaysSendBackAuthenticatedListOfIdPs() ? "1" : "0"); storeSendAuthListOfIdPsPrepStmt.setInt(2, tenantID); storeSendAuthListOfIdPsPrepStmt.setInt(3, applicationId); storeSendAuthListOfIdPsPrepStmt.executeUpdate(); } finally { IdentityApplicationManagementUtil.closeStatement(storeSendAuthListOfIdPsPrepStmt); } PreparedStatement storeUseTenantDomainInLocalSubjectIdStmt = null; try { storeUseTenantDomainInLocalSubjectIdStmt = connection.prepareStatement( ApplicationMgtDBQueries.UPDATE_BASIC_APPINFO_WITH_USE_TENANT_DOMAIN_LOCAL_SUBJECT_ID); // IS_USE_TENANT_DIMAIN_LOCAL_SUBJECT_ID=? WHERE TENANT_ID= ? AND ID = ? storeUseTenantDomainInLocalSubjectIdStmt.setString(1, localAndOutboundAuthConfig.isUseTenantDomainInLocalSubjectIdentifier() ? "1" : "0"); storeUseTenantDomainInLocalSubjectIdStmt.setInt(2, tenantID); storeUseTenantDomainInLocalSubjectIdStmt.setInt(3, applicationId); storeUseTenantDomainInLocalSubjectIdStmt.executeUpdate(); } finally { IdentityApplicationManagementUtil.closeStatement(storeUseTenantDomainInLocalSubjectIdStmt); } PreparedStatement storeUseUserstoreDomainInLocalSubjectIdStmt = null; try { storeUseUserstoreDomainInLocalSubjectIdStmt = connection.prepareStatement( ApplicationMgtDBQueries.UPDATE_BASIC_APPINFO_WITH_USE_USERSTORE_DOMAIN_LOCAL_SUBJECT_ID); // IS_USE_USERSTORE_DIMAIN_LOCAL_SUBJECT_ID=? WHERE TENANT_ID= ? AND ID = ? storeUseUserstoreDomainInLocalSubjectIdStmt.setString(1, localAndOutboundAuthConfig.isUseUserstoreDomainInLocalSubjectIdentifier() ? "1" : "0"); storeUseUserstoreDomainInLocalSubjectIdStmt.setInt(2, tenantID); storeUseUserstoreDomainInLocalSubjectIdStmt.setInt(3, applicationId); storeUseUserstoreDomainInLocalSubjectIdStmt.executeUpdate(); } finally { IdentityApplicationManagementUtil.closeStatement(storeUseUserstoreDomainInLocalSubjectIdStmt); } PreparedStatement storeSubjectClaimUri = null; try { storeSubjectClaimUri = connection .prepareStatement(ApplicationMgtDBQueries.UPDATE_BASIC_APPINFO_WITH_SUBJECT_CLAIM_URI); // SUBJECT_CLAIM_URI=? WHERE TENANT_ID= ? AND ID = ? storeSubjectClaimUri.setString(1, CharacterEncoder.getSafeText(localAndOutboundAuthConfig.getSubjectClaimUri())); storeSubjectClaimUri.setInt(2, tenantID); storeSubjectClaimUri.setInt(3, applicationId); storeSubjectClaimUri.executeUpdate(); } finally { IdentityApplicationManagementUtil.closeStatement(storeSubjectClaimUri); } AuthenticationStep[] authSteps = localAndOutboundAuthConfig.getAuthenticationSteps(); if (authSteps == null || authSteps.length == 0) { // if no authentication steps defined - it should be the default behavior. localAndOutboundAuthConfig.setAuthenticationType(ApplicationConstants.AUTH_TYPE_DEFAULT); } try { if (localAndOutboundAuthConfig.getAuthenticationType() == null) { // no authentication type defined - set to default. localAndOutboundAuthConfig.setAuthenticationType(ApplicationConstants.AUTH_TYPE_DEFAULT); } updateAuthTypePrepStmt = connection .prepareStatement(ApplicationMgtDBQueries.UPDATE_BASIC_APPINFO_WITH_AUTH_TYPE); // AUTH_TYPE=? WHERE TENANT_ID= ? AND ID = ? updateAuthTypePrepStmt.setString(1, CharacterEncoder.getSafeText(localAndOutboundAuthConfig.getAuthenticationType())); updateAuthTypePrepStmt.setInt(2, tenantID); updateAuthTypePrepStmt.setInt(3, applicationId); updateAuthTypePrepStmt.execute(); } finally { IdentityApplicationManagementUtil.closeStatement(updateAuthTypePrepStmt); } if (authSteps != null && authSteps.length > 0) { // we have authentications steps defined. PreparedStatement storeStepIDPAuthnPrepStmt = null; storeStepIDPAuthnPrepStmt = connection.prepareStatement(ApplicationMgtDBQueries.STORE_STEP_IDP_AUTH); try { if (ApplicationConstants.AUTH_TYPE_LOCAL .equalsIgnoreCase(localAndOutboundAuthConfig.getAuthenticationType())) { // for local authentication there can only be only one authentication step and // only one local authenticator. if (authSteps.length != 1 || authSteps[0] == null || authSteps[0].getLocalAuthenticatorConfigs() == null || authSteps[0].getLocalAuthenticatorConfigs().length != 1 || (authSteps[0].getFederatedIdentityProviders() != null && authSteps[0].getFederatedIdentityProviders().length >= 1)) { String errorMessage = "Invalid local authentication configuration." + " For local authentication there can only be only one authentication step and only one local authenticator"; throw new IdentityApplicationManagementException(errorMessage); } } else if (ApplicationConstants.AUTH_TYPE_FEDERATED .equalsIgnoreCase(localAndOutboundAuthConfig.getAuthenticationType())) { // for federated authentication there can only be only one authentication step // and only one federated authenticator - which is the default authenticator of // the corresponding authenticator. if (authSteps.length != 1 || authSteps[0] == null || authSteps[0].getFederatedIdentityProviders() == null || authSteps[0].getFederatedIdentityProviders().length != 1 || authSteps[0].getLocalAuthenticatorConfigs().length > 0) { String errorMessage = "Invalid federated authentication configuration." + " For federated authentication there can only be only one authentication step and only one federated authenticator"; throw new IdentityApplicationManagementException(errorMessage); } IdentityProvider fedIdp = authSteps[0].getFederatedIdentityProviders()[0]; IdentityProviderDAO idpDAO = ApplicationMgtSystemConfig.getInstance().getIdentityProviderDAO(); String defualtAuthName = idpDAO.getDefaultAuthenticator(fedIdp.getIdentityProviderName()); // set the default authenticator. FederatedAuthenticatorConfig defaultAuth = new FederatedAuthenticatorConfig(); defaultAuth.setName(defualtAuthName); fedIdp.setDefaultAuthenticatorConfig(defaultAuth); fedIdp.setFederatedAuthenticatorConfigs(new FederatedAuthenticatorConfig[] { defaultAuth }); } // iterating through each step. for (AuthenticationStep authStep : authSteps) { int stepId = 0; IdentityProvider[] federatedIdps = authStep.getFederatedIdentityProviders(); // an authentication step should have at least one federated identity // provider or a local authenticator. if ((federatedIdps == null || federatedIdps.length == 0) && (authStep.getLocalAuthenticatorConfigs() == null || authStep.getLocalAuthenticatorConfigs().length == 0)) { String errorMesssage = "Invalid authentication configuration." + "An authentication step should have at least one federated identity " + "provider or a local authenticator"; throw new IdentityApplicationManagementException(errorMesssage); } // we have valid federated identity providers. PreparedStatement storeStepPrepStmtz = null; ResultSet result = null; try { String dbProductName = connection.getMetaData().getDatabaseProductName(); storeStepPrepStmtz = connection.prepareStatement(ApplicationMgtDBQueries.STORE_STEP_INFO, new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "ID") }); // TENANT_ID, STEP_ORDER, APP_ID storeStepPrepStmtz.setInt(1, tenantID); storeStepPrepStmtz.setInt(2, authStep.getStepOrder()); storeStepPrepStmtz.setInt(3, applicationId); storeStepPrepStmtz.setString(4, authStep.isSubjectStep() ? "1" : "0"); storeStepPrepStmtz.setString(5, authStep.isAttributeStep() ? "1" : "0"); storeStepPrepStmtz.execute(); result = storeStepPrepStmtz.getGeneratedKeys(); if (result.next()) { stepId = result.getInt(1); } } finally { IdentityApplicationManagementUtil.closeResultSet(result); IdentityApplicationManagementUtil.closeStatement(storeStepPrepStmtz); } if (authStep.getLocalAuthenticatorConfigs() != null && authStep.getLocalAuthenticatorConfigs().length > 0) { for (LocalAuthenticatorConfig lclAuthenticator : authStep.getLocalAuthenticatorConfigs()) { // set the identity provider name to LOCAL. int authenticatorId = getAuthentictorID(connection, tenantID, ApplicationConstants.LOCAL_IDP_NAME, lclAuthenticator.getName()); if (authenticatorId < 0) { authenticatorId = addAuthenticator(connection, tenantID, ApplicationConstants.LOCAL_IDP_NAME, lclAuthenticator.getName(), lclAuthenticator.getDisplayName()); } if (authenticatorId > 0) { // ID, TENANT_ID, AUTHENTICATOR_ID storeStepIDPAuthnPrepStmt.setInt(1, stepId); storeStepIDPAuthnPrepStmt.setInt(2, tenantID); storeStepIDPAuthnPrepStmt.setInt(3, authenticatorId); storeStepIDPAuthnPrepStmt.addBatch(); } if (debugMode) { log.debug("Updating Local IdP of Application " + applicationId + " Step Order: " + authStep.getStepOrder() + " IdP: " + ApplicationConstants.LOCAL_IDP + " Authenticator: " + lclAuthenticator.getName()); } } } // we have federated identity providers. if (federatedIdps != null && federatedIdps.length > 0) { // iterating through each IDP of the step for (IdentityProvider federatedIdp : federatedIdps) { String idpName = federatedIdp.getIdentityProviderName(); // the identity provider name wso2carbon-local-idp is reserved. if (ApplicationConstants.LOCAL_IDP.equalsIgnoreCase(idpName)) { throw new IdentityApplicationManagementException( "The federated IdP name cannot be equal to " + ApplicationConstants.LOCAL_IDP); } FederatedAuthenticatorConfig[] authenticators = federatedIdp .getFederatedAuthenticatorConfigs(); if (authenticators != null && authenticators.length > 0) { for (FederatedAuthenticatorConfig authenticator : authenticators) { // ID, TENANT_ID, AUTHENTICATOR_ID int authenticatorId = getAuthentictorID(connection, tenantID, idpName, authenticator.getName()); if (authenticatorId > 0) { if (authenticator != null) { storeStepIDPAuthnPrepStmt.setInt(1, stepId); storeStepIDPAuthnPrepStmt.setInt(2, tenantID); storeStepIDPAuthnPrepStmt.setInt(3, authenticatorId); storeStepIDPAuthnPrepStmt.addBatch(); if (debugMode) { log.debug("Updating Federated IdP of Application " + applicationId + " Step Order: " + authStep.getStepOrder() + " IdP: " + idpName + " Authenticator: " + authenticator); } } } } } } } } storeStepIDPAuthnPrepStmt.executeBatch(); } finally { IdentityApplicationManagementUtil.closeStatement(storeStepIDPAuthnPrepStmt); } } }
From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java
/** * Deletes an Application along with subscriptions, keys and registration data * * @param application Application object to be deleted from the database which has the application Id * @throws APIManagementException//from www.j av a2 s . co m */ public void deleteApplication(Application application) throws APIManagementException { Connection connection = null; PreparedStatement deleteMappingQuery = null; PreparedStatement prepStmt = null; PreparedStatement prepStmtGetConsumerKey = null; PreparedStatement deleteRegistrationQuery = null; PreparedStatement deleteSubscription = null; PreparedStatement deleteDomainApp = null; PreparedStatement deleteAppKey = null; PreparedStatement deleteApp = null; ResultSet rs = null; String getSubscriptionsQuery = SQLConstants.GET_SUBSCRIPTION_ID_OF_APPLICATION_SQL; String getConsumerKeyQuery = SQLConstants.GET_CONSUMER_KEY_OF_APPLICATION_SQL; String deleteKeyMappingQuery = SQLConstants.REMOVE_APPLICATION_FROM_SUBSCRIPTION_KEY_MAPPINGS_SQL; String deleteSubscriptionsQuery = SQLConstants.REMOVE_APPLICATION_FROM_SUBSCRIPTIONS_SQL; String deleteApplicationKeyQuery = SQLConstants.REMOVE_APPLICATION_FROM_APPLICATION_KEY_MAPPINGS_SQL; String deleteDomainAppQuery = SQLConstants.REMOVE_APPLICATION_FROM_DOMAIN_MAPPINGS_SQL; String deleteApplicationQuery = SQLConstants.REMOVE_APPLICATION_FROM_APPLICATIONS_SQL; String deleteRegistrationEntry = SQLConstants.REMOVE_APPLICATION_FROM_APPLICATION_REGISTRATIONS_SQL; try { connection = APIMgtDBUtil.getConnection(); connection.setAutoCommit(false); prepStmt = connection.prepareStatement(getSubscriptionsQuery); prepStmt.setInt(1, application.getId()); rs = prepStmt.executeQuery(); List<Integer> subscriptions = new ArrayList<Integer>(); while (rs.next()) { subscriptions.add(rs.getInt("SUBSCRIPTION_ID")); } deleteMappingQuery = connection.prepareStatement(deleteKeyMappingQuery); for (Integer subscriptionId : subscriptions) { deleteMappingQuery.setInt(1, subscriptionId); deleteMappingQuery.addBatch(); } deleteMappingQuery.executeBatch(); if (log.isDebugEnabled()) { log.debug("Subscription Key mapping details are deleted successfully for Application - " + application.getName()); } deleteRegistrationQuery = connection.prepareStatement(deleteRegistrationEntry); deleteRegistrationQuery.setInt(1, application.getId()); deleteRegistrationQuery.execute(); if (log.isDebugEnabled()) { log.debug("Application Registration details are deleted successfully for Application - " + application.getName()); } deleteSubscription = connection.prepareStatement(deleteSubscriptionsQuery); deleteSubscription.setInt(1, application.getId()); deleteSubscription.execute(); if (log.isDebugEnabled()) { log.debug( "Subscription details are deleted successfully for Application - " + application.getName()); } prepStmtGetConsumerKey = connection.prepareStatement(getConsumerKeyQuery); prepStmtGetConsumerKey.setInt(1, application.getId()); rs = prepStmtGetConsumerKey.executeQuery(); ArrayList<String> consumerKeys = new ArrayList<String>(); deleteDomainApp = connection.prepareStatement(deleteDomainAppQuery); while (rs.next()) { String consumerKey = rs.getString("CONSUMER_KEY"); // This is true when OAuth app has been created by pasting consumer key/secret in the screen. String mode = rs.getString("CREATE_MODE"); if (consumerKey != null) { deleteDomainApp.setString(1, consumerKey); deleteDomainApp.addBatch(); KeyManagerHolder.getKeyManagerInstance().deleteMappedApplication(consumerKey); // OAuth app is deleted if only it has been created from API Store. For mapped clients we don't // call delete. if (!"MAPPED".equals(mode)) { // Adding clients to be deleted. consumerKeys.add(consumerKey); } } } deleteDomainApp.executeBatch(); deleteAppKey = connection.prepareStatement(deleteApplicationKeyQuery); deleteAppKey.setInt(1, application.getId()); deleteAppKey.execute(); if (log.isDebugEnabled()) { log.debug("Application Key Mapping details are deleted successfully for Application - " + application.getName()); } deleteApp = connection.prepareStatement(deleteApplicationQuery); deleteApp.setInt(1, application.getId()); deleteApp.execute(); if (log.isDebugEnabled()) { log.debug("Application " + application.getName() + " is deleted successfully."); } connection.commit(); for (String consumerKey : consumerKeys) { //delete on oAuthorization server. KeyManagerHolder.getKeyManagerInstance().deleteApplication(consumerKey); } } catch (SQLException e) { handleException("Error while removing application details from the database", e); } finally { APIMgtDBUtil.closeAllConnections(prepStmtGetConsumerKey, connection, rs); APIMgtDBUtil.closeAllConnections(prepStmt, null, rs); APIMgtDBUtil.closeAllConnections(deleteApp, null, null); APIMgtDBUtil.closeAllConnections(deleteAppKey, null, null); APIMgtDBUtil.closeAllConnections(deleteMappingQuery, null, null); APIMgtDBUtil.closeAllConnections(deleteRegistrationQuery, null, null); APIMgtDBUtil.closeAllConnections(deleteSubscription, null, null); APIMgtDBUtil.closeAllConnections(deleteDomainApp, null, null); APIMgtDBUtil.closeAllConnections(deleteAppKey, null, null); APIMgtDBUtil.closeAllConnections(deleteApp, null, null); } }
From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java
/** * Adds URI templates define for an API/*w w w.ja v a 2 s. c o m*/ * * @param apiId * @param api * @param connection * @throws APIManagementException */ public void addURLTemplates(int apiId, API api, Connection connection) throws APIManagementException { if (apiId == -1) { //application addition has failed return; } PreparedStatement prepStmt = null; PreparedStatement scopePrepStmt = null; String query = SQLConstants.ADD_URL_MAPPING_SQL; String scopeQuery = SQLConstants.ADD_OAUTH2_RESOURCE_SCOPE_SQL; try { //connection = APIMgtDBUtil.getConnection(); prepStmt = connection.prepareStatement(query); scopePrepStmt = connection.prepareStatement(scopeQuery); Iterator<URITemplate> uriTemplateIterator = api.getUriTemplates().iterator(); URITemplate uriTemplate; for (; uriTemplateIterator.hasNext();) { uriTemplate = uriTemplateIterator.next(); prepStmt.setInt(1, apiId); prepStmt.setString(2, uriTemplate.getHTTPVerb()); prepStmt.setString(3, uriTemplate.getAuthType()); prepStmt.setString(4, uriTemplate.getUriTemplate()); //If API policy is available then set it for all the resources if (StringUtils.isEmpty(api.getApiLevelPolicy())) { prepStmt.setString(5, uriTemplate.getThrottlingTier()); } else { prepStmt.setString(5, api.getApiLevelPolicy()); } InputStream is; if (uriTemplate.getMediationScript() != null) { is = new ByteArrayInputStream( uriTemplate.getMediationScript().getBytes(Charset.defaultCharset())); } else { is = null; } if (connection.getMetaData().getDriverName().contains("PostgreSQL") || connection.getMetaData().getDatabaseProductName().contains("DB2")) { if (uriTemplate.getMediationScript() != null) { prepStmt.setBinaryStream(6, is, uriTemplate.getMediationScript().getBytes(Charset.defaultCharset()).length); } else { prepStmt.setBinaryStream(6, is, 0); } } else { prepStmt.setBinaryStream(6, is); } prepStmt.addBatch(); if (uriTemplate.getScope() != null) { scopePrepStmt.setString(1, APIUtil.getResourceKey(api, uriTemplate)); if (uriTemplate.getScope().getId() == 0) { String scopeKey = uriTemplate.getScope().getKey(); Scope scopeByKey = APIUtil.findScopeByKey(api.getScopes(), scopeKey); if (scopeByKey != null) { if (scopeByKey.getId() > 0) { uriTemplate.getScopes().setId(scopeByKey.getId()); } } } scopePrepStmt.setInt(2, uriTemplate.getScope().getId()); scopePrepStmt.addBatch(); } } prepStmt.executeBatch(); prepStmt.clearBatch(); scopePrepStmt.executeBatch(); scopePrepStmt.clearBatch(); } catch (SQLException e) { handleException("Error while adding URL template(s) to the database for API : " + api.getId(), e); } finally { APIMgtDBUtil.closeAllConnections(prepStmt, null, null); APIMgtDBUtil.closeAllConnections(scopePrepStmt, null, null); } }