sql Query builder in JPA query?

Question

I've made a search tool in java.

String query = "SELECT * FROM Customer WHERE 1 = 1 ";
        if (!firstname.isEmpty())   query += "AND cName = '"        + firstname     + "' ";
        if (!lastname.isEmpty())    query += "AND cLastName = '"    + lastname      + "' ";
        if (!epost.isEmpty())       query += "AND cEpost = '"       + epost         + "' ";
        if (!phonenumber.isEmpty()) query += "AND cPhonenumber '"   + phonenumber   + "' ";

That ouput this if all of those paramerets has values:

SELECT * FROM Customer WHERE 1 = 1 
AND cName = 'test' 
AND cLastName = 'test1' 
AND cEpost = 'test2' 
AND cPhonenumber 'test3'

This way I can get better results by filling in more data, but i can still choose to not do.. I need a solution for JPA for this.. any tips?

Thanks!

EDIT: End result based on the answer below:

public static List<Customer> searchCustomersByParameters(String firstname, String lastname,
    String epost, String phonenumber) {

    String sql = "SELECT c FROM Customer c WHERE 1 = 1 ";
    if (!firstname.isEmpty())   sql += "AND c.cName = :firstname ";
    if (!lastname.isEmpty())    sql += "AND c.cLastName = :lastname ";
    if (!epost.isEmpty())       sql += "AND c.cEpost = :epost ";
    if (!phonenumber.isEmpty()) sql += "AND c.cPhonenumber = :phonenumber";

    Query q = em.createQuery(sql);
    if (!firstname.isEmpty())   q.setParameter("firstname", firstname);
    if (!lastname.isEmpty())    q.setParameter("lastname", lastname);
    if (!epost.isEmpty())       q.setParameter("epost", epost);
    if (!phonenumber.isEmpty()) q.setParameter("phonenumber", phonenumber);

    return q.getResultList();


}

Side note: you should not do it like that: If just one of the parameters isn't properly escaped, anyone can execute arbitrary SQL code. Use ? as placeholders while constructing the query, then use prepared statements to automatically escape all parameters. — BackSlash
– BackSlash, Commented Dec 19, 2016 at 11:57

Lukas Eder · Accepted Answer · 2021-03-18 07:54:13Z

While it is of course possible to create dynamic SQL using string concatenation as suggested in this answer, a more type safe and less risky (in terms of SQL injection) approach is to use the JPA criteria API

public static List<Customer> searchCustomersByParameters(String firstname, String lastname,
    String epost, String phonenumber) {

    var qb = em.getCriteriaBuilder();
    var query = qb.createQuery(Customer.class);
    var root = query.from(Customer.class);
    query.select(root);

    if (!firstname.isEmpty())   query.where(qb.equal(root.get("cName"), firstName));
    if (!lastname.isEmpty())    query.where(qb.equal(root.get("cLastName"), lastname));
    if (!epost.isEmpty())       query.where(qb.equal(root.get("cEpost "), epost ));
    if (!phonenumber.isEmpty()) query.where(qb.equal(root.get("cPhonenumber "), phonenumber));


    return em.createQuery(query).getResultList();
}

... or if you don't strictly need to use JPQL you could also use a third party SQL builder like jOOQ:

public static List<Customer> searchCustomersByParameters(String firstname, String lastname,
    String epost, String phonenumber) {

    return
    ctx.selectFrom(CUSTOMER)
       .where(!firstname.isEmpty() ? CUSTOMER.CNAME.eq(firstname) : noCondition())
       .and(!lastname.isEmpty() ? CUSTOMER.CLASTNAME.eq(lastname) : noCondition())
       .and(!epost.isEmpty() ? CUSTOMER.CEPOST.eq(epost) : noCondition())
       .and(!phonenumber.isEmpty() ? CUSTOMER.CPHONENUMBER.eq(phonenumber) : noCondition())
       .fetchInto(Customer.class);
}

Disclaimer: I work for the company behind jOOQ

Sarkhan · Accepted Answer · 2016-12-19 12:13:34Z

use ? and set Parameters for preventing sql injection and in JPA you can use native sql as old way you do and also JPQL.Generate your sql by conditions and set your parameters.I use here where 1=1 condition to easy append next conditions by and.Otherwise you will have difficulties for appending "where" to your sql.

by native:

public static List<YourEntity> getFromTable(String name,String surname) {
        EntityManager em = PersistenceManager.instance().createEntityManager();

        try {
            String sql = " select * from table where 1=1 ";
            if(name!=null && !name.trim().isEmpty()){
                sql +=" and name = :name";
            }
            if(surname!=null && !surname.trim().isEmpty()){
                sql +=" and surname = :surname";
            }

            Query q = em.createNativeQuery(sql);
             if(name!=null && !name.trim().isEmpty()){
               q.setParameter("name", name);
            }
            if(surname!=null && !surname.trim().isEmpty()){
               q.setParameter("surname", surname);
            }

            List<YourEntity> l = q.getResultList();
            return l;
        } finally {
            em.close();
        }
    }

By jpql:

public static List<YourEntity> getFromTable(String name,String surname) {
        EntityManager em = PersistenceManager.instance().createEntityManager();

        try {
            String sql = " select e from YourEntity e where 1=1 ";
            if(name!=null && !name.trim().isEmpty()){
                sql +=" and e.name = :name";
            }
            if(surname!=null && !surname.trim().isEmpty()){
                sql +=" and e.surname = :surname";
            }

            Query q = em.createQuery(sql);
             if(name!=null && !name.trim().isEmpty()){
               q.setParameter("name", name);
            }
            if(surname!=null && !surname.trim().isEmpty()){
               q.setParameter("surname", surname);
            }

            List<YourEntity> l = q.getResultList();
            return l;
        } finally {
            em.close();
        }
    }

Collectives™ on Stack Overflow

sql Query builder in JPA query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related