Filter rows in DataSet using JPA/Hibernate

I have the following code which is used to retrieve data from multiple tables (using joins) and then mapping every row into a DTOList but I also need to apply filters based on user preferences: per table1.name or table2.name, table3, etc.

So I just want to know what would be the best way to do it in terms of performance and best practices;

retrieving all rows and then apply the filters with lambdas (easier)
change the query to a dynamic query with Criteria or something else?
Any other solution=?

@Repository
public class ArchiveRepository {

    @Autowired
    EntityManager em;

    String queryStr = "select wsr.id as sampleid, s.id as slideid, tb.name as batchname, k.lot_number as kitlot, " +
            "       'STRING' as slidetype, tb.worklist_name as worklist, wsr.final_call as results, " +
            "       wa.final_pattern_ids as patterns, 'edited/yesno' as edited, wsr.last_modified_by as user, wsr.last_modified_date as time " +
            "        from slide s " +
            "         left join table2 tb on s.test_batch_id = tb.id " +
            "         left join table3 k on tb.kit_lot_id = k.id " +
            "         left join table4 w on s.id = w.slide_id " +
            "         left join tabl5 pw on pw.well_id = w.id " +
            "         left join tabl6 cw on cw.well_id = w.id " +
            "         left join tabl7 wsr on wsr.patient_well_sample_id = pw.id or wsr.control_sample_id = cw.id " +
            "         left join (select * from *** (other subselect)) wa on wa.well_sample_id = wsr.**id or wa.well_sample_id = wsr.**id " +
            "where tb.state = 'STATENEEDED'";

    public ArchiveDataListDTO getArchiveData(){
        Query query = em.createNativeQuery(queryStr);
        ArchiveDataListDTO archiveDataListDTO = new ArchiveDataListDTO();
        List<Object[]> resultL = (List<Object[]>)query.getResultList();
        for( Object[] o : resultL){
                archiveDataListDTO.addArchiveDataRow(
            new ArchiveDataDTO((String)o[0], String.valueOf(o[1]), (String) o[2], (String) o[3], (String) o[4], (String) o[5],
        (String) o[6], (String) o[7], (String) o[8], (String) o[9], (String) o[10]));
        }
        return archiveDataListDTO;
    }

}

**
note I struggled some with the code cause I wanted to apply @sqlresultsetmapping to avoid manual results mapping but it just didn´t work, most of the examples out there are when you have an entity in the DB but in this case I retrieve from many tables.**

Thanks so much

Answer

I ended up creating the query on the fly; depending on the filters I get from UI i assemble the query with Java and send it to DB, it´s easier since this required many tables…

Advertisement

Answer