/** * This method is used to search sub-category * * @param SubCategoryVO - search criteria * @throws BusinessException - wrap exception in business object of exception framework * @throws ApplicationException - wrap exception in application object of exception framework */ public List searchSubCategory(SubCategoryVO object1, int lowerBound, int upperBound) throws BusinessException, ApplicationException { Connection conn = null; Connection conn1 = null; PreparedStatement preStat = null; PreparedStatement preStat1 = null; PreparedStatement preStat2 = null; ResultSet resultSet = null; ResultSet resultSet1 = null; ResultSet rs1 = null; ArrayList<SubCategoryVO> arrayList = new ArrayList<SubCategoryVO>(); String stausStr = null; String catName = null; lowerBound = lowerBound + 1; try { // String sqlQuery2="select count(*) from CRM_SUB_CATEGORY where // PARENT_ID = ? AND SUB_CATEGORY_NAME LIKE ? AND // SUB_CATEGORY_DESCRIPTION LIKE ?"; //select the records matching // the search criteria // // String sqlQuery="select * from (select a.*, row_number() over() // rnum from (select * from CRM_SUB_CATEGORY where PARENT_ID = ? AND // SUB_CATEGORY_NAME LIKE ?" + // " AND SUB_CATEGORY_DESCRIPTION LIKE ?)a)b where rnum // >="+lowerBound+" and rnum <="+upperBound+""; //select the records // matching the search criteria StringBuffer sqlQuery = new StringBuffer("select * from (select a.*, row_number() over() rnum from ("); sqlQuery.append("select * from CRM_SUB_CATEGORY"); // fetch all // userID StringBuffer sqlQueryinner = new StringBuffer("select count(*) from (select * from CRM_SUB_CATEGORY"); if (!(object1.getCategoryId() == -1)) { sqlQuery.append(" INTERSECT SELECT * FROM CRM_SUB_CATEGORY WHERE PARENT_ID= "); sqlQuery.append(object1.getCategoryId()); sqlQueryinner.append(" INTERSECT SELECT * FROM CRM_SUB_CATEGORY WHERE PARENT_ID= "); sqlQueryinner.append(object1.getCategoryId()); } if (!(object1.getSubCategoryName().equals("")) || object1.getSubCategoryName() == null) { sqlQuery.append(" INTERSECT SELECT * FROM CRM_SUB_CATEGORY WHERE SUB_CATEGORY_NAME LIKE "); sqlQuery.append("'%" + object1.getSubCategoryName() + "%'"); sqlQueryinner.append( " INTERSECT SELECT * FROM CRM_SUB_CATEGORY WHERE SUB_CATEGORY_NAME LIKE "); sqlQueryinner.append("'%" + object1.getSubCategoryName() + "%'"); } if (!(object1.getDescription().equals("") || object1.getDescription() == null)) { sqlQuery.append( " INTERSECT SELECT * FROM CRM_SUB_CATEGORY WHERE SUB_CATEGORY_DESCRIPTION LIKE "); sqlQuery.append("'%" + object1.getDescription() + "%'"); sqlQueryinner.append( " INTERSECT SELECT * FROM CRM_SUB_CATEGORY WHERE SUB_CATEGORY_DESCRIPTION LIKE "); sqlQueryinner.append("'%" + object1.getDescription() + "%'"); } sqlQuery.append(")a) b where rnum >=" + lowerBound + " and rnum <=" + upperBound); sqlQueryinner.append(")h"); conn = DBConnection.getDBConnection(); conn1 = DBConnection.getDBConnection(); if (conn != null) { String sql = sqlQuery.toString(); String sql1 = sqlQueryinner.toString(); preStat = conn.prepareStatement( sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); resultSet = preStat.executeQuery(); if (!resultSet.next()) { throw new NoRecordExistsException( "No Record Found", "subcategory.search.norecord.exception"); } else { resultSet.previous(); // move the cursor to the first row while (resultSet.next()) { String stat = resultSet.getString("STATUS"); if (stat.equals("1")) { stausStr = AdminConstantsImpl.ADMIN_SUBCATEGORY_STATUS_1; } else { stausStr = AdminConstantsImpl.ADMIN_SUBCATEGORY_STATUS_0; } int catId = resultSet.getInt("PARENT_ID"); if (conn1 != null) { String sqlQuery1 = "select CATEGORY_NAME from CRM_CATEGORY where CATEGORY_ID = ? "; preStat1 = conn1.prepareStatement(sqlQuery1); preStat1.setInt(1, catId); rs1 = preStat1.executeQuery(); if (rs1.next()) { catName = rs1.getString("CATEGORY_NAME"); } } SubCategoryVO voObj = new SubCategoryVO(); voObj.setCategoryName(catName); voObj.setSubCategoryId(resultSet.getInt("SUB_CATEGORY_ID")); voObj.setSubCategoryName(resultSet.getString("SUB_CATEGORY_NAME")); voObj.setDescription(resultSet.getString("SUB_CATEGORY_DESCRIPTION")); voObj.setStatus(stausStr); arrayList.add(voObj); } } preStat2 = conn.prepareStatement( sql1, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); resultSet1 = preStat2.executeQuery(); if (resultSet1.next()) { noOfRecords = resultSet1.getInt(1); getRecordCount(); // get the number of records in the // result set } } else { logger.info("Connection is null "); } conn.close(); } catch (SQLException sql) { logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + sql); throw new ApplicationException(AdminConstantsImpl.APPLICATION_SQL_EXCEPTION, sql); } catch (NoRecordExistsException e) { throw new NoRecordExistsException(e.getBusinessReason(), e.getBusinessCode()); } catch (Exception e) { logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + e); throw new ApplicationException(AdminConstantsImpl.APPLICATION_CONNECTION_EXCEPTION, e); } finally { try { if (conn != null) { conn.close(); } if (conn1 != null) { conn1.close(); } if (resultSet != null) { resultSet.close(); } if (rs1 != null) { rs1.close(); } if (preStat != null) { preStat.close(); } if (preStat1 != null) { preStat1.close(); } } catch (SQLException sqlExcep) { logger.error(AdminConstantsImpl.LOGGER_EXCEPTION_MESSAGE + sqlExcep); throw new ApplicationException(AdminConstantsImpl.APPLICATION_SQL_EXCEPTION, sqlExcep); } } return arrayList; }