Implementing Repository layer

Methods in the service layer, upon performing all the business logic, call methods in the Repository layer to persist or lookup data i.e. it interacts with the configured data store. For executing the queries, JdbcTemplate class is used. JdbcTemplate takes care of creation and release of resources such as creating and closing the connection etc. All database operations namely insert, update, search and delete can be performed on the database using methods of JdbcTemplate class.

On DIGIT however, we handle create and update operations asynchronously. Our persister service listens on the topic to which service applications are pushed for insertion and updation. Persister then takes care of executing insert and update operations on the database without hogging our application’s threads.

That leaves us with execution of search queries on the database to return applications as per the search parameters provided by the user.

For this guide, these are the steps that we will be taking to implement repository layer -

i) Create querybuilder and rowmapper folders within repository folder.

ii) Create a class by the name of VoterApplicationQueryBuilder in querybuilder folder and annotate it with @Component annotation. Put the following content in VoterApplicationQueryBuilder class -

@Component
public class VoterApplicationQueryBuilder {

    private static final String BASE_VTR_QUERY = " SELECT vtr.id as vid, vtr.tenantid as vtenantid, vtr.assemblyconstituency as vassemblyconstituency, vtr.applicationnumber as vapplicationnumber, vtr.applicantid as vapplicantid, vtr.datesinceresidence as vdatesinceresidence, vtr.createdby as vcreatedby, vtr.lastmodifiedby as vlastmodifiedby, vtr.createdtime as vcreatedtime, vtr.lastmodifiedtime as vlastmodifiedtime, ";

    private static final String ADDRESS_SELECT_QUERY = " add.id as aid, add.tenantid as atenantid, add.doorno as adoorno, add.latitude as alatitude, add.longitude as alongitude, add.buildingname as abuildingname, add.addressid as aaddressid, add.addressnumber as aaddressnumber, add.type as atype, add.addressline1 as aaddressline1, add.addressline2 as aaddressline2, add.landmark as alandmark, add.street as astreet, add.city as acity, add.locality as alocality, add.pincode as apincode, add.detail as adetail, add.registrationid as aregistrationid ";

    private static final String FROM_TABLES = " FROM eg_vt_registration vtr LEFT JOIN eg_vt_address add ON vtr.id = add.registrationid ";

    private final String ORDERBY_CREATEDTIME = " ORDER BY vtr.createdtime DESC ";

    public String getVoterApplicationSearchQuery(VoterApplicationSearchCriteria criteria, List<Object> preparedStmtList){
        StringBuilder query = new StringBuilder(BASE_VTR_QUERY);
        query.append(ADDRESS_SELECT_QUERY);
        query.append(FROM_TABLES);

        if(!ObjectUtils.isEmpty(criteria.getTenantId())){
            addClauseIfRequired(query, preparedStmtList);
            query.append(" vtr.tenantid = ? ");
            preparedStmtList.add(criteria.getTenantId());
        }
        if(!ObjectUtils.isEmpty(criteria.getStatus())){
            addClauseIfRequired(query, preparedStmtList);
            query.append(" vtr.status = ? ");
            preparedStmtList.add(criteria.getStatus());
        }
        if(!CollectionUtils.isEmpty(criteria.getIds())){
            addClauseIfRequired(query, preparedStmtList);
            query.append(" vtr.id IN ( ").append(createQuery(criteria.getIds())).append(" ) ");
            addToPreparedStatement(preparedStmtList, criteria.getIds());
        }
        if(!ObjectUtils.isEmpty(criteria.getApplicationNumber())){
            addClauseIfRequired(query, preparedStmtList);
            query.append(" vtr.applicationnumber = ? ");
            preparedStmtList.add(criteria.getApplicationNumber());
        }

        // order voter registration applications based on their createdtime in latest first manner
        query.append(ORDERBY_CREATEDTIME);

        return query.toString();
    }

    private void addClauseIfRequired(StringBuilder query, List<Object> preparedStmtList){
        if(preparedStmtList.isEmpty()){
            query.append(" WHERE ");
        }else{
            query.append(" AND ");
        }
    }

    private String createQuery(List<String> ids) {
        StringBuilder builder = new StringBuilder();
        int length = ids.size();
        for (int i = 0; i < length; i++) {
            builder.append(" ?");
            if (i != length - 1)
                builder.append(",");
        }
        return builder.toString();
    }

    private void addToPreparedStatement(List<Object> preparedStmtList, List<String> ids) {
        ids.forEach(id -> {
            preparedStmtList.add(id);
        });
    }
}

iii) Next, create a class by the name of VoterApplicationRowMapper under rowmapper folder and annotate it with @Component. Add the following content in the class -

@Component
public class VoterApplicationRowMapper implements ResultSetExtractor<List<VoterRegistrationApplication>> {
    public List<VoterRegistrationApplication> extractData(ResultSet rs) throws SQLException, DataAccessException {
        Map<String,VoterRegistrationApplication> voterRegistrationApplicationMap = new LinkedHashMap<>();

        while (rs.next()){
            String uuid = rs.getString("vapplicationnumber");
            VoterRegistrationApplication voterRegistrationApplication = voterRegistrationApplicationMap.get(uuid);

            if(voterRegistrationApplication == null) {

                Long lastModifiedTime = rs.getLong("vlastModifiedTime");
                if (rs.wasNull()) {
                    lastModifiedTime = null;
                }

                Applicant applicant = Applicant.builder().id(rs.getString("vapplicantid")).build();

                AuditDetails auditdetails = AuditDetails.builder()
                        .createdBy(rs.getString("vcreatedBy"))
                        .createdTime(rs.getLong("vcreatedTime"))
                        .lastModifiedBy(rs.getString("vlastModifiedBy"))
                        .lastModifiedTime(lastModifiedTime)
                        .build();

                voterRegistrationApplication = VoterRegistrationApplication.builder()
                        .applicationNumber(rs.getString("vapplicationnumber"))
                        .tenantId(rs.getString("vtenantid"))
                        .id(rs.getString("vid"))
                        .assemblyConstituency(rs.getString("vassemblyconstituency"))
                        .dateSinceResidence(rs.getInt("vdatesinceresidence"))
                        .applicant(applicant)
                        .auditDetails(auditdetails)
                        .build();
            }
            addChildrenToProperty(rs, voterRegistrationApplication);
            voterRegistrationApplicationMap.put(uuid, voterRegistrationApplication);
        }
        return new ArrayList<>(voterRegistrationApplicationMap.values());
    }

    private void addChildrenToProperty(ResultSet rs, VoterRegistrationApplication voterRegistrationApplication)
            throws SQLException {
        addAddressToApplication(rs, voterRegistrationApplication);
    }

    private void addAddressToApplication(ResultSet rs, VoterRegistrationApplication voterRegistrationApplication) throws SQLException {
        Address address = Address.builder()
                                    .id(rs.getString("aid"))
                                    .tenantId(rs.getString("atenantid"))
                                    .doorNo(rs.getString("adoorno"))
                                    .latitude(rs.getDouble("alatitude"))
                                    .longitude(rs.getDouble("alongitude"))
                                    .buildingName(rs.getString("abuildingname"))
                                    .addressId(rs.getString("aaddressid"))
                                    .addressNumber(rs.getString("aaddressnumber"))
                                    .type(rs.getString("atype"))
                                    .addressLine1(rs.getString("aaddressline1"))
                                    .addressLine2(rs.getString("aaddressline2"))
                                    .landmark(rs.getString("alandmark"))
                                    .street(rs.getString("astreet"))
                                    .city(rs.getString("acity"))
                                    .pincode(rs.getString("apincode"))
                                    .detail("adetail")
                                    .registrationId("aregistrationid")
                                    .build();


        voterRegistrationApplication.setAddress(address);

    }

}

iv) Finally, create a class by the name of VoterRegistrationRepository under repository folder and annotate it with @Repository annotation. Put the following content into the class -

@Slf4j
@Repository
public class VoterRegistrationRepository {

    @Autowired
    private VoterApplicationQueryBuilder queryBuilder;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private VoterApplicationRowMapper rowMapper;

    public List<VoterRegistrationApplication> getApplications(VoterApplicationSearchCriteria searchCriteria){
        List<Object> preparedStmtList = new ArrayList<>();
        String query = queryBuilder.getVoterApplicationSearchQuery(searchCriteria, preparedStmtList);
        log.info("Final query: " + query);
        return jdbcTemplate.query(query, preparedStmtList.toArray(), rowMapper);

    }
}