@Test @Sql(scripts = { "/sql/instrument/clearInstrument.sql", "/sql/instrument/insertInstrumentData.sql" }) public void test_search() { Pagination<Instrument> page = (Pagination<Instrument>) instrumentJdbcRepository.search(getInstrumentSearch()); assertThat(page.getPagedData().get(0).getAmount()).isEqualTo("1.00"); assertThat(page.getPagedData().get(0).getBank().getId()).isEqualTo("code"); assertThat(page.getPagedData().get(0).getBankAccount().getAccountNumber()).isEqualTo("accountNumber"); assertThat(page.getPagedData().get(0).getBranchName()).isEqualTo("branchName"); assertThat(page.getPagedData().get(0).getDrawer()).isEqualTo("drawer"); assertThat(page.getPagedData().get(0).getFinancialStatus().getId()).isEqualTo("1"); assertThat(page.getPagedData().get(0).getInstrumentType().getName()).isEqualTo("name"); assertThat(page.getPagedData().get(0).getPayee()).isEqualTo("payee"); assertThat(page.getPagedData().get(0).getSerialNo()).isEqualTo("serialNo"); assertThat(page.getPagedData().get(0).getSurrenderReason().getId()).isEqualTo("1"); assertThat(page.getPagedData().get(0).getTransactionNumber()).isEqualTo("transactionNumber"); assertThat(page.getPagedData().get(0).getTransactionType()).isEqualTo(TransactionType.Credit); }
public Pagination<Instrument> search(InstrumentSearch domain) { InstrumentSearchEntity instrumentSearchEntity = new InstrumentSearchEntity(); instrumentSearchEntity.toEntity(domain); String searchQuery = "select :selectfields from :tablename :condition :orderby "; Map<String, Object> paramValues = new HashMap<>(); StringBuffer params = new StringBuffer(); if (instrumentSearchEntity.getSortBy() != null && !instrumentSearchEntity.getSortBy().isEmpty()) { validateSortByOrder(instrumentSearchEntity.getSortBy()); validateEntityFieldName(instrumentSearchEntity.getSortBy(), InstrumentEntity.class); } String orderBy = "order by id"; if (instrumentSearchEntity.getSortBy() != null && !instrumentSearchEntity.getSortBy().isEmpty()) orderBy = "order by " + instrumentSearchEntity.getSortBy(); searchQuery = searchQuery.replace(":tablename", InstrumentEntity.TABLE_NAME); searchQuery = searchQuery.replace(":selectfields", " * "); if (instrumentSearchEntity.getTenantId() != null) { if (params.length() > 0) params.append(" and "); params.append("tenantId =:tenantId"); paramValues.put("tenantId", instrumentSearchEntity.getTenantId()); } if (instrumentSearchEntity.getId() != null) { if (params.length() > 0) params.append(" and "); params.append("id =:id"); paramValues.put("id", instrumentSearchEntity.getId()); } if (instrumentSearchEntity.getTransactionNumber() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionNumber =:transactionNumber"); paramValues.put("transactionNumber", instrumentSearchEntity.getTransactionNumber()); } if (instrumentSearchEntity.getTransactionDate() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionDate =:transactionDate"); paramValues.put("transactionDate", instrumentSearchEntity.getTransactionDate()); } if (instrumentSearchEntity.getAmount() != null) { if (params.length() > 0) params.append(" and "); params.append("amount =:amount"); paramValues.put("amount", instrumentSearchEntity.getAmount()); } if (instrumentSearchEntity.getInstrumentTypeId() != null) { if (params.length() > 0) params.append(" and "); params.append("instrumentTypeid =:instrumentType"); paramValues.put("instrumentType", instrumentSearchEntity.getInstrumentTypeId()); } if (instrumentSearchEntity.getBankId() != null) { if (params.length() > 0) params.append(" and "); params.append("bankid =:bank"); paramValues.put("bank", instrumentSearchEntity.getBankId()); } if (instrumentSearchEntity.getBranchName() != null) { if (params.length() > 0) params.append(" and "); params.append("branchName =:branchName"); paramValues.put("branchName", instrumentSearchEntity.getBranchName()); } if (instrumentSearchEntity.getBankAccountId() != null) { if (params.length() > 0) params.append(" and "); params.append("bankAccountid =:bankAccount"); paramValues.put("bankAccount", instrumentSearchEntity.getBankAccountId()); } if (instrumentSearchEntity.getFinancialStatusId() != null) { if (params.length() > 0) params.append(" and "); params.append("financialStatusid =:financialStatus"); paramValues.put("financialStatus", instrumentSearchEntity.getFinancialStatusId()); } if (instrumentSearchEntity.getRemittanceVoucherId() != null) { if (params.length() > 0) params.append(" and "); params.append("remittanceVoucherId =:remittanceVoucherId"); paramValues.put("remittanceVoucherId", instrumentSearchEntity.getRemittanceVoucherId()); } if (instrumentSearchEntity.getTransactionType() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionType =:transactionType"); paramValues.put("transactionType", instrumentSearchEntity.getTransactionType()); } if (instrumentSearchEntity.getPayee() != null) { if (params.length() > 0) params.append(" and "); params.append("payee =:payee"); paramValues.put("payee", instrumentSearchEntity.getPayee()); } if (instrumentSearchEntity.getDrawer() != null) { if (params.length() > 0) params.append(" and "); params.append("drawer =:drawer"); paramValues.put("drawer", instrumentSearchEntity.getDrawer()); } if (instrumentSearchEntity.getSurrenderReasonId() != null) { if (params.length() > 0) params.append(" and "); params.append("surrenderReasonid =:surrenderReason"); paramValues.put("surrenderReason", instrumentSearchEntity.getSurrenderReasonId()); } if (instrumentSearchEntity.getSerialNo() != null) { if (params.length() > 0) params.append(" and "); params.append("serialNo =:serialNo"); paramValues.put("serialNo", instrumentSearchEntity.getSerialNo()); } if (instrumentSearchEntity.getReceiptIds() != null) { InstrumentVoucherSearch ivs = new InstrumentVoucherSearch(); ivs.setTenantId(instrumentSearchEntity.getTenantId()); ivs.setReceiptIds(instrumentSearchEntity.getReceiptIds()); Pagination<InstrumentVoucher> instrumentVoucherList = instrumentVoucherJdbcRepository.search(ivs); if (instrumentVoucherList != null && instrumentVoucherList.getPagedData() != null && !instrumentVoucherList.getPagedData().isEmpty()) { List<String> idList = new ArrayList<>(); for (InstrumentVoucher iv : instrumentVoucherList.getPagedData()) { idList.add(iv.getInstrument().getId()); } if (!idList.isEmpty()) { if (params.length() > 0) params.append(" and "); params.append("id in (:ids)"); paramValues.put("ids", idList); } } } if (instrumentSearchEntity.getIds() != null) { if (params.length() > 0) params.append(" and "); params.append("id in (:ids)"); paramValues.put("ids", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getIds().split(",")))); } if (instrumentSearchEntity.getFinancialStatuses() != null) { if (params.length() > 0) params.append(" and "); params.append("financialStatusId in (:financialStatuses)"); paramValues.put("financialStatuses", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getFinancialStatuses().split(",")))); } if (instrumentSearchEntity.getInstrumentTypes() != null) { if (params.length() > 0) params.append(" and "); params.append("instrumentTypeId in (:instrumentTypes)"); paramValues.put("instrumentTypes", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getInstrumentTypes().split(",")))); } if (instrumentSearchEntity.getTransactionFromDate() != null && instrumentSearchEntity.getTransactionToDate() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionDate >= :fromDate and transactionDate <= :toDate"); paramValues.put("fromDate", instrumentSearchEntity.getTransactionFromDate()); paramValues.put("toDate", instrumentSearchEntity.getTransactionToDate()); } Pagination<Instrument> page = new Pagination<>(); if (instrumentSearchEntity.getOffset() != null) page.setOffset(instrumentSearchEntity.getOffset()); if (instrumentSearchEntity.getPageSize() != null) page.setPageSize(instrumentSearchEntity.getPageSize()); if (params.length() > 0) searchQuery = searchQuery.replace(":condition", " where " + params.toString()); else searchQuery = searchQuery.replace(":condition", ""); searchQuery = searchQuery.replace(":orderby", orderBy); page = (Pagination<Instrument>) getPagination(searchQuery, page, paramValues); searchQuery = searchQuery + " :pagination"; searchQuery = searchQuery.replace(":pagination", "limit " + page.getPageSize() + " offset " + page.getOffset() * page.getPageSize()); BeanPropertyRowMapper row = new BeanPropertyRowMapper(InstrumentEntity.class); List<InstrumentEntity> instrumentEntities = namedParameterJdbcTemplate.query(searchQuery.toString(), paramValues, row); page.setTotalResults(instrumentEntities.size()); List<Instrument> instruments = new ArrayList<>(); StringBuffer ids = new StringBuffer(); for (InstrumentEntity instrumentEntity : instrumentEntities) { if (ids != null && ids.length() > 0) { ids.append(","); } ids.append(instrumentEntity.getId()); instruments.add(instrumentEntity.toDomain()); } populateInstrumentVouchers(instruments, ids, domain.getTenantId()); page.setPagedData(instruments); return page; }
InstrumentJdbcRepository extends JdbcRepository { public Pagination<Instrument> search(InstrumentSearch domain) { InstrumentSearchEntity instrumentSearchEntity = new InstrumentSearchEntity(); instrumentSearchEntity.toEntity(domain); String searchQuery = "select :selectfields from :tablename :condition :orderby "; Map<String, Object> paramValues = new HashMap<>(); StringBuffer params = new StringBuffer(); if (instrumentSearchEntity.getSortBy() != null && !instrumentSearchEntity.getSortBy().isEmpty()) { validateSortByOrder(instrumentSearchEntity.getSortBy()); validateEntityFieldName(instrumentSearchEntity.getSortBy(), InstrumentEntity.class); } String orderBy = "order by id"; if (instrumentSearchEntity.getSortBy() != null && !instrumentSearchEntity.getSortBy().isEmpty()) orderBy = "order by " + instrumentSearchEntity.getSortBy(); searchQuery = searchQuery.replace(":tablename", InstrumentEntity.TABLE_NAME); searchQuery = searchQuery.replace(":selectfields", " * "); if (instrumentSearchEntity.getTenantId() != null) { if (params.length() > 0) params.append(" and "); params.append("tenantId =:tenantId"); paramValues.put("tenantId", instrumentSearchEntity.getTenantId()); } if (instrumentSearchEntity.getId() != null) { if (params.length() > 0) params.append(" and "); params.append("id =:id"); paramValues.put("id", instrumentSearchEntity.getId()); } if (instrumentSearchEntity.getTransactionNumber() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionNumber =:transactionNumber"); paramValues.put("transactionNumber", instrumentSearchEntity.getTransactionNumber()); } if (instrumentSearchEntity.getTransactionDate() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionDate =:transactionDate"); paramValues.put("transactionDate", instrumentSearchEntity.getTransactionDate()); } if (instrumentSearchEntity.getAmount() != null) { if (params.length() > 0) params.append(" and "); params.append("amount =:amount"); paramValues.put("amount", instrumentSearchEntity.getAmount()); } if (instrumentSearchEntity.getInstrumentTypeId() != null) { if (params.length() > 0) params.append(" and "); params.append("instrumentTypeid =:instrumentType"); paramValues.put("instrumentType", instrumentSearchEntity.getInstrumentTypeId()); } if (instrumentSearchEntity.getBankId() != null) { if (params.length() > 0) params.append(" and "); params.append("bankid =:bank"); paramValues.put("bank", instrumentSearchEntity.getBankId()); } if (instrumentSearchEntity.getBranchName() != null) { if (params.length() > 0) params.append(" and "); params.append("branchName =:branchName"); paramValues.put("branchName", instrumentSearchEntity.getBranchName()); } if (instrumentSearchEntity.getBankAccountId() != null) { if (params.length() > 0) params.append(" and "); params.append("bankAccountid =:bankAccount"); paramValues.put("bankAccount", instrumentSearchEntity.getBankAccountId()); } if (instrumentSearchEntity.getFinancialStatusId() != null) { if (params.length() > 0) params.append(" and "); params.append("financialStatusid =:financialStatus"); paramValues.put("financialStatus", instrumentSearchEntity.getFinancialStatusId()); } if (instrumentSearchEntity.getRemittanceVoucherId() != null) { if (params.length() > 0) params.append(" and "); params.append("remittanceVoucherId =:remittanceVoucherId"); paramValues.put("remittanceVoucherId", instrumentSearchEntity.getRemittanceVoucherId()); } if (instrumentSearchEntity.getTransactionType() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionType =:transactionType"); paramValues.put("transactionType", instrumentSearchEntity.getTransactionType()); } if (instrumentSearchEntity.getPayee() != null) { if (params.length() > 0) params.append(" and "); params.append("payee =:payee"); paramValues.put("payee", instrumentSearchEntity.getPayee()); } if (instrumentSearchEntity.getDrawer() != null) { if (params.length() > 0) params.append(" and "); params.append("drawer =:drawer"); paramValues.put("drawer", instrumentSearchEntity.getDrawer()); } if (instrumentSearchEntity.getSurrenderReasonId() != null) { if (params.length() > 0) params.append(" and "); params.append("surrenderReasonid =:surrenderReason"); paramValues.put("surrenderReason", instrumentSearchEntity.getSurrenderReasonId()); } if (instrumentSearchEntity.getSerialNo() != null) { if (params.length() > 0) params.append(" and "); params.append("serialNo =:serialNo"); paramValues.put("serialNo", instrumentSearchEntity.getSerialNo()); } if (instrumentSearchEntity.getReceiptIds() != null) { InstrumentVoucherSearch ivs = new InstrumentVoucherSearch(); ivs.setTenantId(instrumentSearchEntity.getTenantId()); ivs.setReceiptIds(instrumentSearchEntity.getReceiptIds()); Pagination<InstrumentVoucher> instrumentVoucherList = instrumentVoucherJdbcRepository.search(ivs); if (instrumentVoucherList != null && instrumentVoucherList.getPagedData() != null && !instrumentVoucherList.getPagedData().isEmpty()) { List<String> idList = new ArrayList<>(); for (InstrumentVoucher iv : instrumentVoucherList.getPagedData()) { idList.add(iv.getInstrument().getId()); } if (!idList.isEmpty()) { if (params.length() > 0) params.append(" and "); params.append("id in (:ids)"); paramValues.put("ids", idList); } } } if (instrumentSearchEntity.getIds() != null) { if (params.length() > 0) params.append(" and "); params.append("id in (:ids)"); paramValues.put("ids", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getIds().split(",")))); } if (instrumentSearchEntity.getFinancialStatuses() != null) { if (params.length() > 0) params.append(" and "); params.append("financialStatusId in (:financialStatuses)"); paramValues.put("financialStatuses", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getFinancialStatuses().split(",")))); } if (instrumentSearchEntity.getInstrumentTypes() != null) { if (params.length() > 0) params.append(" and "); params.append("instrumentTypeId in (:instrumentTypes)"); paramValues.put("instrumentTypes", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getInstrumentTypes().split(",")))); } if (instrumentSearchEntity.getTransactionFromDate() != null && instrumentSearchEntity.getTransactionToDate() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionDate >= :fromDate and transactionDate <= :toDate"); paramValues.put("fromDate", instrumentSearchEntity.getTransactionFromDate()); paramValues.put("toDate", instrumentSearchEntity.getTransactionToDate()); } Pagination<Instrument> page = new Pagination<>(); if (instrumentSearchEntity.getOffset() != null) page.setOffset(instrumentSearchEntity.getOffset()); if (instrumentSearchEntity.getPageSize() != null) page.setPageSize(instrumentSearchEntity.getPageSize()); if (params.length() > 0) searchQuery = searchQuery.replace(":condition", " where " + params.toString()); else searchQuery = searchQuery.replace(":condition", ""); searchQuery = searchQuery.replace(":orderby", orderBy); page = (Pagination<Instrument>) getPagination(searchQuery, page, paramValues); searchQuery = searchQuery + " :pagination"; searchQuery = searchQuery.replace(":pagination", "limit " + page.getPageSize() + " offset " + page.getOffset() * page.getPageSize()); BeanPropertyRowMapper row = new BeanPropertyRowMapper(InstrumentEntity.class); List<InstrumentEntity> instrumentEntities = namedParameterJdbcTemplate.query(searchQuery.toString(), paramValues, row); page.setTotalResults(instrumentEntities.size()); List<Instrument> instruments = new ArrayList<>(); StringBuffer ids = new StringBuffer(); for (InstrumentEntity instrumentEntity : instrumentEntities) { if (ids != null && ids.length() > 0) { ids.append(","); } ids.append(instrumentEntity.getId()); instruments.add(instrumentEntity.toDomain()); } populateInstrumentVouchers(instruments, ids, domain.getTenantId()); page.setPagedData(instruments); return page; } }
InstrumentJdbcRepository extends JdbcRepository { public Pagination<Instrument> search(InstrumentSearch domain) { InstrumentSearchEntity instrumentSearchEntity = new InstrumentSearchEntity(); instrumentSearchEntity.toEntity(domain); String searchQuery = "select :selectfields from :tablename :condition :orderby "; Map<String, Object> paramValues = new HashMap<>(); StringBuffer params = new StringBuffer(); if (instrumentSearchEntity.getSortBy() != null && !instrumentSearchEntity.getSortBy().isEmpty()) { validateSortByOrder(instrumentSearchEntity.getSortBy()); validateEntityFieldName(instrumentSearchEntity.getSortBy(), InstrumentEntity.class); } String orderBy = "order by id"; if (instrumentSearchEntity.getSortBy() != null && !instrumentSearchEntity.getSortBy().isEmpty()) orderBy = "order by " + instrumentSearchEntity.getSortBy(); searchQuery = searchQuery.replace(":tablename", InstrumentEntity.TABLE_NAME); searchQuery = searchQuery.replace(":selectfields", " * "); if (instrumentSearchEntity.getTenantId() != null) { if (params.length() > 0) params.append(" and "); params.append("tenantId =:tenantId"); paramValues.put("tenantId", instrumentSearchEntity.getTenantId()); } if (instrumentSearchEntity.getId() != null) { if (params.length() > 0) params.append(" and "); params.append("id =:id"); paramValues.put("id", instrumentSearchEntity.getId()); } if (instrumentSearchEntity.getTransactionNumber() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionNumber =:transactionNumber"); paramValues.put("transactionNumber", instrumentSearchEntity.getTransactionNumber()); } if (instrumentSearchEntity.getTransactionDate() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionDate =:transactionDate"); paramValues.put("transactionDate", instrumentSearchEntity.getTransactionDate()); } if (instrumentSearchEntity.getAmount() != null) { if (params.length() > 0) params.append(" and "); params.append("amount =:amount"); paramValues.put("amount", instrumentSearchEntity.getAmount()); } if (instrumentSearchEntity.getInstrumentTypeId() != null) { if (params.length() > 0) params.append(" and "); params.append("instrumentTypeid =:instrumentType"); paramValues.put("instrumentType", instrumentSearchEntity.getInstrumentTypeId()); } if (instrumentSearchEntity.getBankId() != null) { if (params.length() > 0) params.append(" and "); params.append("bankid =:bank"); paramValues.put("bank", instrumentSearchEntity.getBankId()); } if (instrumentSearchEntity.getBranchName() != null) { if (params.length() > 0) params.append(" and "); params.append("branchName =:branchName"); paramValues.put("branchName", instrumentSearchEntity.getBranchName()); } if (instrumentSearchEntity.getBankAccountId() != null) { if (params.length() > 0) params.append(" and "); params.append("bankAccountid =:bankAccount"); paramValues.put("bankAccount", instrumentSearchEntity.getBankAccountId()); } if (instrumentSearchEntity.getFinancialStatusId() != null) { if (params.length() > 0) params.append(" and "); params.append("financialStatusid =:financialStatus"); paramValues.put("financialStatus", instrumentSearchEntity.getFinancialStatusId()); } if (instrumentSearchEntity.getRemittanceVoucherId() != null) { if (params.length() > 0) params.append(" and "); params.append("remittanceVoucherId =:remittanceVoucherId"); paramValues.put("remittanceVoucherId", instrumentSearchEntity.getRemittanceVoucherId()); } if (instrumentSearchEntity.getTransactionType() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionType =:transactionType"); paramValues.put("transactionType", instrumentSearchEntity.getTransactionType()); } if (instrumentSearchEntity.getPayee() != null) { if (params.length() > 0) params.append(" and "); params.append("payee =:payee"); paramValues.put("payee", instrumentSearchEntity.getPayee()); } if (instrumentSearchEntity.getDrawer() != null) { if (params.length() > 0) params.append(" and "); params.append("drawer =:drawer"); paramValues.put("drawer", instrumentSearchEntity.getDrawer()); } if (instrumentSearchEntity.getSurrenderReasonId() != null) { if (params.length() > 0) params.append(" and "); params.append("surrenderReasonid =:surrenderReason"); paramValues.put("surrenderReason", instrumentSearchEntity.getSurrenderReasonId()); } if (instrumentSearchEntity.getSerialNo() != null) { if (params.length() > 0) params.append(" and "); params.append("serialNo =:serialNo"); paramValues.put("serialNo", instrumentSearchEntity.getSerialNo()); } if (instrumentSearchEntity.getReceiptIds() != null) { InstrumentVoucherSearch ivs = new InstrumentVoucherSearch(); ivs.setTenantId(instrumentSearchEntity.getTenantId()); ivs.setReceiptIds(instrumentSearchEntity.getReceiptIds()); Pagination<InstrumentVoucher> instrumentVoucherList = instrumentVoucherJdbcRepository.search(ivs); if (instrumentVoucherList != null && instrumentVoucherList.getPagedData() != null && !instrumentVoucherList.getPagedData().isEmpty()) { List<String> idList = new ArrayList<>(); for (InstrumentVoucher iv : instrumentVoucherList.getPagedData()) { idList.add(iv.getInstrument().getId()); } if (!idList.isEmpty()) { if (params.length() > 0) params.append(" and "); params.append("id in (:ids)"); paramValues.put("ids", idList); } } } if (instrumentSearchEntity.getIds() != null) { if (params.length() > 0) params.append(" and "); params.append("id in (:ids)"); paramValues.put("ids", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getIds().split(",")))); } if (instrumentSearchEntity.getFinancialStatuses() != null) { if (params.length() > 0) params.append(" and "); params.append("financialStatusId in (:financialStatuses)"); paramValues.put("financialStatuses", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getFinancialStatuses().split(",")))); } if (instrumentSearchEntity.getInstrumentTypes() != null) { if (params.length() > 0) params.append(" and "); params.append("instrumentTypeId in (:instrumentTypes)"); paramValues.put("instrumentTypes", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getInstrumentTypes().split(",")))); } if (instrumentSearchEntity.getTransactionFromDate() != null && instrumentSearchEntity.getTransactionToDate() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionDate >= :fromDate and transactionDate <= :toDate"); paramValues.put("fromDate", instrumentSearchEntity.getTransactionFromDate()); paramValues.put("toDate", instrumentSearchEntity.getTransactionToDate()); } Pagination<Instrument> page = new Pagination<>(); if (instrumentSearchEntity.getOffset() != null) page.setOffset(instrumentSearchEntity.getOffset()); if (instrumentSearchEntity.getPageSize() != null) page.setPageSize(instrumentSearchEntity.getPageSize()); if (params.length() > 0) searchQuery = searchQuery.replace(":condition", " where " + params.toString()); else searchQuery = searchQuery.replace(":condition", ""); searchQuery = searchQuery.replace(":orderby", orderBy); page = (Pagination<Instrument>) getPagination(searchQuery, page, paramValues); searchQuery = searchQuery + " :pagination"; searchQuery = searchQuery.replace(":pagination", "limit " + page.getPageSize() + " offset " + page.getOffset() * page.getPageSize()); BeanPropertyRowMapper row = new BeanPropertyRowMapper(InstrumentEntity.class); List<InstrumentEntity> instrumentEntities = namedParameterJdbcTemplate.query(searchQuery.toString(), paramValues, row); page.setTotalResults(instrumentEntities.size()); List<Instrument> instruments = new ArrayList<>(); StringBuffer ids = new StringBuffer(); for (InstrumentEntity instrumentEntity : instrumentEntities) { if (ids != null && ids.length() > 0) { ids.append(","); } ids.append(instrumentEntity.getId()); instruments.add(instrumentEntity.toDomain()); } populateInstrumentVouchers(instruments, ids, domain.getTenantId()); page.setPagedData(instruments); return page; } InstrumentJdbcRepository(NamedParameterJdbcTemplate namedParameterJdbcTemplate, JdbcTemplate jdbcTemplate,
            InstrumentVoucherJdbcRepository instrumentVoucherJdbcRepository); }
InstrumentJdbcRepository extends JdbcRepository { public Pagination<Instrument> search(InstrumentSearch domain) { InstrumentSearchEntity instrumentSearchEntity = new InstrumentSearchEntity(); instrumentSearchEntity.toEntity(domain); String searchQuery = "select :selectfields from :tablename :condition :orderby "; Map<String, Object> paramValues = new HashMap<>(); StringBuffer params = new StringBuffer(); if (instrumentSearchEntity.getSortBy() != null && !instrumentSearchEntity.getSortBy().isEmpty()) { validateSortByOrder(instrumentSearchEntity.getSortBy()); validateEntityFieldName(instrumentSearchEntity.getSortBy(), InstrumentEntity.class); } String orderBy = "order by id"; if (instrumentSearchEntity.getSortBy() != null && !instrumentSearchEntity.getSortBy().isEmpty()) orderBy = "order by " + instrumentSearchEntity.getSortBy(); searchQuery = searchQuery.replace(":tablename", InstrumentEntity.TABLE_NAME); searchQuery = searchQuery.replace(":selectfields", " * "); if (instrumentSearchEntity.getTenantId() != null) { if (params.length() > 0) params.append(" and "); params.append("tenantId =:tenantId"); paramValues.put("tenantId", instrumentSearchEntity.getTenantId()); } if (instrumentSearchEntity.getId() != null) { if (params.length() > 0) params.append(" and "); params.append("id =:id"); paramValues.put("id", instrumentSearchEntity.getId()); } if (instrumentSearchEntity.getTransactionNumber() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionNumber =:transactionNumber"); paramValues.put("transactionNumber", instrumentSearchEntity.getTransactionNumber()); } if (instrumentSearchEntity.getTransactionDate() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionDate =:transactionDate"); paramValues.put("transactionDate", instrumentSearchEntity.getTransactionDate()); } if (instrumentSearchEntity.getAmount() != null) { if (params.length() > 0) params.append(" and "); params.append("amount =:amount"); paramValues.put("amount", instrumentSearchEntity.getAmount()); } if (instrumentSearchEntity.getInstrumentTypeId() != null) { if (params.length() > 0) params.append(" and "); params.append("instrumentTypeid =:instrumentType"); paramValues.put("instrumentType", instrumentSearchEntity.getInstrumentTypeId()); } if (instrumentSearchEntity.getBankId() != null) { if (params.length() > 0) params.append(" and "); params.append("bankid =:bank"); paramValues.put("bank", instrumentSearchEntity.getBankId()); } if (instrumentSearchEntity.getBranchName() != null) { if (params.length() > 0) params.append(" and "); params.append("branchName =:branchName"); paramValues.put("branchName", instrumentSearchEntity.getBranchName()); } if (instrumentSearchEntity.getBankAccountId() != null) { if (params.length() > 0) params.append(" and "); params.append("bankAccountid =:bankAccount"); paramValues.put("bankAccount", instrumentSearchEntity.getBankAccountId()); } if (instrumentSearchEntity.getFinancialStatusId() != null) { if (params.length() > 0) params.append(" and "); params.append("financialStatusid =:financialStatus"); paramValues.put("financialStatus", instrumentSearchEntity.getFinancialStatusId()); } if (instrumentSearchEntity.getRemittanceVoucherId() != null) { if (params.length() > 0) params.append(" and "); params.append("remittanceVoucherId =:remittanceVoucherId"); paramValues.put("remittanceVoucherId", instrumentSearchEntity.getRemittanceVoucherId()); } if (instrumentSearchEntity.getTransactionType() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionType =:transactionType"); paramValues.put("transactionType", instrumentSearchEntity.getTransactionType()); } if (instrumentSearchEntity.getPayee() != null) { if (params.length() > 0) params.append(" and "); params.append("payee =:payee"); paramValues.put("payee", instrumentSearchEntity.getPayee()); } if (instrumentSearchEntity.getDrawer() != null) { if (params.length() > 0) params.append(" and "); params.append("drawer =:drawer"); paramValues.put("drawer", instrumentSearchEntity.getDrawer()); } if (instrumentSearchEntity.getSurrenderReasonId() != null) { if (params.length() > 0) params.append(" and "); params.append("surrenderReasonid =:surrenderReason"); paramValues.put("surrenderReason", instrumentSearchEntity.getSurrenderReasonId()); } if (instrumentSearchEntity.getSerialNo() != null) { if (params.length() > 0) params.append(" and "); params.append("serialNo =:serialNo"); paramValues.put("serialNo", instrumentSearchEntity.getSerialNo()); } if (instrumentSearchEntity.getReceiptIds() != null) { InstrumentVoucherSearch ivs = new InstrumentVoucherSearch(); ivs.setTenantId(instrumentSearchEntity.getTenantId()); ivs.setReceiptIds(instrumentSearchEntity.getReceiptIds()); Pagination<InstrumentVoucher> instrumentVoucherList = instrumentVoucherJdbcRepository.search(ivs); if (instrumentVoucherList != null && instrumentVoucherList.getPagedData() != null && !instrumentVoucherList.getPagedData().isEmpty()) { List<String> idList = new ArrayList<>(); for (InstrumentVoucher iv : instrumentVoucherList.getPagedData()) { idList.add(iv.getInstrument().getId()); } if (!idList.isEmpty()) { if (params.length() > 0) params.append(" and "); params.append("id in (:ids)"); paramValues.put("ids", idList); } } } if (instrumentSearchEntity.getIds() != null) { if (params.length() > 0) params.append(" and "); params.append("id in (:ids)"); paramValues.put("ids", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getIds().split(",")))); } if (instrumentSearchEntity.getFinancialStatuses() != null) { if (params.length() > 0) params.append(" and "); params.append("financialStatusId in (:financialStatuses)"); paramValues.put("financialStatuses", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getFinancialStatuses().split(",")))); } if (instrumentSearchEntity.getInstrumentTypes() != null) { if (params.length() > 0) params.append(" and "); params.append("instrumentTypeId in (:instrumentTypes)"); paramValues.put("instrumentTypes", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getInstrumentTypes().split(",")))); } if (instrumentSearchEntity.getTransactionFromDate() != null && instrumentSearchEntity.getTransactionToDate() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionDate >= :fromDate and transactionDate <= :toDate"); paramValues.put("fromDate", instrumentSearchEntity.getTransactionFromDate()); paramValues.put("toDate", instrumentSearchEntity.getTransactionToDate()); } Pagination<Instrument> page = new Pagination<>(); if (instrumentSearchEntity.getOffset() != null) page.setOffset(instrumentSearchEntity.getOffset()); if (instrumentSearchEntity.getPageSize() != null) page.setPageSize(instrumentSearchEntity.getPageSize()); if (params.length() > 0) searchQuery = searchQuery.replace(":condition", " where " + params.toString()); else searchQuery = searchQuery.replace(":condition", ""); searchQuery = searchQuery.replace(":orderby", orderBy); page = (Pagination<Instrument>) getPagination(searchQuery, page, paramValues); searchQuery = searchQuery + " :pagination"; searchQuery = searchQuery.replace(":pagination", "limit " + page.getPageSize() + " offset " + page.getOffset() * page.getPageSize()); BeanPropertyRowMapper row = new BeanPropertyRowMapper(InstrumentEntity.class); List<InstrumentEntity> instrumentEntities = namedParameterJdbcTemplate.query(searchQuery.toString(), paramValues, row); page.setTotalResults(instrumentEntities.size()); List<Instrument> instruments = new ArrayList<>(); StringBuffer ids = new StringBuffer(); for (InstrumentEntity instrumentEntity : instrumentEntities) { if (ids != null && ids.length() > 0) { ids.append(","); } ids.append(instrumentEntity.getId()); instruments.add(instrumentEntity.toDomain()); } populateInstrumentVouchers(instruments, ids, domain.getTenantId()); page.setPagedData(instruments); return page; } InstrumentJdbcRepository(NamedParameterJdbcTemplate namedParameterJdbcTemplate, JdbcTemplate jdbcTemplate,
            InstrumentVoucherJdbcRepository instrumentVoucherJdbcRepository); InstrumentEntity create(InstrumentEntity entity); InstrumentEntity update(InstrumentEntity entity); InstrumentEntity delete(InstrumentEntity entity); Pagination<Instrument> search(InstrumentSearch domain); InstrumentEntity findById(InstrumentEntity entity); }
InstrumentJdbcRepository extends JdbcRepository { public Pagination<Instrument> search(InstrumentSearch domain) { InstrumentSearchEntity instrumentSearchEntity = new InstrumentSearchEntity(); instrumentSearchEntity.toEntity(domain); String searchQuery = "select :selectfields from :tablename :condition :orderby "; Map<String, Object> paramValues = new HashMap<>(); StringBuffer params = new StringBuffer(); if (instrumentSearchEntity.getSortBy() != null && !instrumentSearchEntity.getSortBy().isEmpty()) { validateSortByOrder(instrumentSearchEntity.getSortBy()); validateEntityFieldName(instrumentSearchEntity.getSortBy(), InstrumentEntity.class); } String orderBy = "order by id"; if (instrumentSearchEntity.getSortBy() != null && !instrumentSearchEntity.getSortBy().isEmpty()) orderBy = "order by " + instrumentSearchEntity.getSortBy(); searchQuery = searchQuery.replace(":tablename", InstrumentEntity.TABLE_NAME); searchQuery = searchQuery.replace(":selectfields", " * "); if (instrumentSearchEntity.getTenantId() != null) { if (params.length() > 0) params.append(" and "); params.append("tenantId =:tenantId"); paramValues.put("tenantId", instrumentSearchEntity.getTenantId()); } if (instrumentSearchEntity.getId() != null) { if (params.length() > 0) params.append(" and "); params.append("id =:id"); paramValues.put("id", instrumentSearchEntity.getId()); } if (instrumentSearchEntity.getTransactionNumber() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionNumber =:transactionNumber"); paramValues.put("transactionNumber", instrumentSearchEntity.getTransactionNumber()); } if (instrumentSearchEntity.getTransactionDate() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionDate =:transactionDate"); paramValues.put("transactionDate", instrumentSearchEntity.getTransactionDate()); } if (instrumentSearchEntity.getAmount() != null) { if (params.length() > 0) params.append(" and "); params.append("amount =:amount"); paramValues.put("amount", instrumentSearchEntity.getAmount()); } if (instrumentSearchEntity.getInstrumentTypeId() != null) { if (params.length() > 0) params.append(" and "); params.append("instrumentTypeid =:instrumentType"); paramValues.put("instrumentType", instrumentSearchEntity.getInstrumentTypeId()); } if (instrumentSearchEntity.getBankId() != null) { if (params.length() > 0) params.append(" and "); params.append("bankid =:bank"); paramValues.put("bank", instrumentSearchEntity.getBankId()); } if (instrumentSearchEntity.getBranchName() != null) { if (params.length() > 0) params.append(" and "); params.append("branchName =:branchName"); paramValues.put("branchName", instrumentSearchEntity.getBranchName()); } if (instrumentSearchEntity.getBankAccountId() != null) { if (params.length() > 0) params.append(" and "); params.append("bankAccountid =:bankAccount"); paramValues.put("bankAccount", instrumentSearchEntity.getBankAccountId()); } if (instrumentSearchEntity.getFinancialStatusId() != null) { if (params.length() > 0) params.append(" and "); params.append("financialStatusid =:financialStatus"); paramValues.put("financialStatus", instrumentSearchEntity.getFinancialStatusId()); } if (instrumentSearchEntity.getRemittanceVoucherId() != null) { if (params.length() > 0) params.append(" and "); params.append("remittanceVoucherId =:remittanceVoucherId"); paramValues.put("remittanceVoucherId", instrumentSearchEntity.getRemittanceVoucherId()); } if (instrumentSearchEntity.getTransactionType() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionType =:transactionType"); paramValues.put("transactionType", instrumentSearchEntity.getTransactionType()); } if (instrumentSearchEntity.getPayee() != null) { if (params.length() > 0) params.append(" and "); params.append("payee =:payee"); paramValues.put("payee", instrumentSearchEntity.getPayee()); } if (instrumentSearchEntity.getDrawer() != null) { if (params.length() > 0) params.append(" and "); params.append("drawer =:drawer"); paramValues.put("drawer", instrumentSearchEntity.getDrawer()); } if (instrumentSearchEntity.getSurrenderReasonId() != null) { if (params.length() > 0) params.append(" and "); params.append("surrenderReasonid =:surrenderReason"); paramValues.put("surrenderReason", instrumentSearchEntity.getSurrenderReasonId()); } if (instrumentSearchEntity.getSerialNo() != null) { if (params.length() > 0) params.append(" and "); params.append("serialNo =:serialNo"); paramValues.put("serialNo", instrumentSearchEntity.getSerialNo()); } if (instrumentSearchEntity.getReceiptIds() != null) { InstrumentVoucherSearch ivs = new InstrumentVoucherSearch(); ivs.setTenantId(instrumentSearchEntity.getTenantId()); ivs.setReceiptIds(instrumentSearchEntity.getReceiptIds()); Pagination<InstrumentVoucher> instrumentVoucherList = instrumentVoucherJdbcRepository.search(ivs); if (instrumentVoucherList != null && instrumentVoucherList.getPagedData() != null && !instrumentVoucherList.getPagedData().isEmpty()) { List<String> idList = new ArrayList<>(); for (InstrumentVoucher iv : instrumentVoucherList.getPagedData()) { idList.add(iv.getInstrument().getId()); } if (!idList.isEmpty()) { if (params.length() > 0) params.append(" and "); params.append("id in (:ids)"); paramValues.put("ids", idList); } } } if (instrumentSearchEntity.getIds() != null) { if (params.length() > 0) params.append(" and "); params.append("id in (:ids)"); paramValues.put("ids", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getIds().split(",")))); } if (instrumentSearchEntity.getFinancialStatuses() != null) { if (params.length() > 0) params.append(" and "); params.append("financialStatusId in (:financialStatuses)"); paramValues.put("financialStatuses", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getFinancialStatuses().split(",")))); } if (instrumentSearchEntity.getInstrumentTypes() != null) { if (params.length() > 0) params.append(" and "); params.append("instrumentTypeId in (:instrumentTypes)"); paramValues.put("instrumentTypes", new ArrayList<String>(Arrays.asList(instrumentSearchEntity.getInstrumentTypes().split(",")))); } if (instrumentSearchEntity.getTransactionFromDate() != null && instrumentSearchEntity.getTransactionToDate() != null) { if (params.length() > 0) params.append(" and "); params.append("transactionDate >= :fromDate and transactionDate <= :toDate"); paramValues.put("fromDate", instrumentSearchEntity.getTransactionFromDate()); paramValues.put("toDate", instrumentSearchEntity.getTransactionToDate()); } Pagination<Instrument> page = new Pagination<>(); if (instrumentSearchEntity.getOffset() != null) page.setOffset(instrumentSearchEntity.getOffset()); if (instrumentSearchEntity.getPageSize() != null) page.setPageSize(instrumentSearchEntity.getPageSize()); if (params.length() > 0) searchQuery = searchQuery.replace(":condition", " where " + params.toString()); else searchQuery = searchQuery.replace(":condition", ""); searchQuery = searchQuery.replace(":orderby", orderBy); page = (Pagination<Instrument>) getPagination(searchQuery, page, paramValues); searchQuery = searchQuery + " :pagination"; searchQuery = searchQuery.replace(":pagination", "limit " + page.getPageSize() + " offset " + page.getOffset() * page.getPageSize()); BeanPropertyRowMapper row = new BeanPropertyRowMapper(InstrumentEntity.class); List<InstrumentEntity> instrumentEntities = namedParameterJdbcTemplate.query(searchQuery.toString(), paramValues, row); page.setTotalResults(instrumentEntities.size()); List<Instrument> instruments = new ArrayList<>(); StringBuffer ids = new StringBuffer(); for (InstrumentEntity instrumentEntity : instrumentEntities) { if (ids != null && ids.length() > 0) { ids.append(","); } ids.append(instrumentEntity.getId()); instruments.add(instrumentEntity.toDomain()); } populateInstrumentVouchers(instruments, ids, domain.getTenantId()); page.setPagedData(instruments); return page; } InstrumentJdbcRepository(NamedParameterJdbcTemplate namedParameterJdbcTemplate, JdbcTemplate jdbcTemplate,
            InstrumentVoucherJdbcRepository instrumentVoucherJdbcRepository); InstrumentEntity create(InstrumentEntity entity); InstrumentEntity update(InstrumentEntity entity); InstrumentEntity delete(InstrumentEntity entity); Pagination<Instrument> search(InstrumentSearch domain); InstrumentEntity findById(InstrumentEntity entity);  }