java - JPA 查询中的 sql 查询构建器？

Question

我用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   + "' ";

如果所有这些参数都有值，则输出:

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

这样我可以通过填写更多数据来获得更好的结果，但我仍然可以选择不这样做..我需要为此提供 JPA 解决方案..有什么建议吗？

谢谢!

编辑:最终结果基于以下答案:

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();


}

Answer 1

当然可以使用字符串连接创建动态 SQL as suggested in this answer ，一种类型更安全、风险更小的(就 SQL 注入(inject)而言)方法是使用 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();
}

...或者如果您并不严格需要使用 JPQL，您也可以使用 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);
}

免责声明:我在 jOOQ 背后的公司工作