Thursday, June 27, 2013

JPQL: parameter using LIKE clause

Example named query:

    @NamedQuery(
        name = "BismarkPlan.findBismarkPlanByClientIdAndBismarkPlanNameOrBismarkPlanCode"
        ,query  = "SELECT bp FROM BismarkPlan bp"
                    + ", BusinessProposalFormalization bpf"
                    + ", BusinessProposalFormalizationItem bpfi"        
                + " WHERE bpfi.businessProposalFormalizationId = bpf.id"
                    + " AND bpfi.bismarkPlanId = bp.id"
                    + " AND bpf.clientId =:clientId"
                    + " AND (bp.code LIKE :code OR bp.name LIKE :name)"
    )

then calling the query:

Long clientId = 2;
String bismarkPlanCode = "PB";
String bismarkPlanName = "Plan";
        
// TODO: use JPA querybuilder instead of namedqueries
Query query = em.createNamedQuery("BismarkPlan.findBismarkPlanByClientIdAndBismarkPlanNameOrBismarkPlanCode");
query.setParameter("clientId", clientId);
query.setParameter("code", "%" + bismarkPlanCode + "%");
query.setParameter("name", "%" + bismarkPlanName + "%");
List<BismarkPlan> returnList = query.getResultList();

Resource:
http://stackoverflow.com/questions/1341104/parameter-in-like-clause-jpql


No comments: