JPA CriteriaQuery Generated SQL with hardcoded parameter on nested entity id

Question

I've been studying SpringData JPA and came across with this weird behavior when using CriteriaQuery to find a given entity by its child entity's Id, I've noticed a hardcoded child_id parameter in the generated SQL:

Hibernate: select parent0_.parent_id as parent_i1_4_, parent0_.parent_name as parent_n2_4_, parent0_.parent_type_parent_type_id as parent_t3_4_ from parent parent0_ inner join child children1_ on parent0_.parent_id=children1_.parent_parent_id where children1_.child_id=1

Hibernate: select parent0_.parent_id as parent_i1_4_, parent0_.parent_name as parent_n2_4_, parent0_.parent_type_parent_type_id as parent_t3_4_ from parent parent0_ inner join child children1_ on parent0_.parent_id=children1_.parent_parent_id where children1_.child_id=?

The java code:

Parent

@Data
@Entity
public class Parent {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer parentId;

    private String parentName;

    @OneToMany(mappedBy = "parent")
    private List<Child> children;

}

Child

@Builder
@Data
@Entity
public class Child {

    @Id
    private Integer childId;

    private String childName;

    @ManyToOne
    private Parent parent;

}

ParentDAO

@Repository
public class ParentDAO {

    private EntityManager em;

    public ParentDAO(EntityManager em) {
        this.em = em;
    }

    public List<Parent> findByChildId(Integer childId) {

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Parent> cq = cb.createQuery(Parent.class);
        Root<Parent> root = cq.from(Parent.class);
        List<Predicate> predicates = new ArrayList<>();

        predicates.add(cb.equal(root.join("children").get("childId"), childId));
        cq.where(predicates.toArray(new Predicate[0]));

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

    public List<Parent> findByChild(Child child) {

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Parent> cq = cb.createQuery(Parent.class);
        Root<Parent> root = cq.from(Parent.class);
        List<Predicate> predicates = new ArrayList<>();

        predicates.add(cb.equal(root.join("children"), child));
        cq.where(predicates.toArray(new Predicate[0]));

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

}

SpringDataApplication

@SpringBootApplication
public class SpringDataApplication implements CommandLineRunner {

    private ParentDAO parentDAO;

    public SpringDataApplication(ParentDAO parentDAO) {
        this.parentDAO = parentDAO;
    }

    public static void main(String[] args) {
        SpringApplication.run(SpringDataApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        parentDAO.findByChildId(1);
        parentDAO.findByChild(Child.builder().childId(1).build());
    }
}

It's not a big deal since the goal could be achieved with the findByChild method, I'm just curious about this situation. Best regards!

Answer 1

Because Strings can contain SQL and Integers cannot, there is no need to bind from a security aspect point of view (SQL injection).

From hibernate documentation of literal_handling_mode:

This enum defines how literals are handled by JPA Criteria. By default (AUTO), Criteria queries uses bind parameters for any literal that is not a numeric value. However, to increase the likelihood of JDBC statement caching, you might want to use bind parameters for numeric values too. The BIND mode will use bind variables for any literal value. The INLINE mode will inline literal values as-is. To prevent SQL injection, never use INLINE with String variables. Always use constants with the INLINE mode.

In issue HHH-9576 a new parameter was added to fix this issue, applicable since version 5.2.12

<property name="hibernate.criteria.literal_handling_mode" value="bind"/>

or in spring boot application.properties you can use

spring.jpa.properties.hibernate.criteria.literal_handling_mode=bind

After adding configuration your both query will look the same with the bind parameter.

select parent0_.parent_id as parent_i1_2_, parent0_.parent_name as parent_n2_2_ from parent parent0_ inner join child children1_ on parent0_.parent_id=children1_.parent_parent_id where children1_.child_id=?
binding parameter [1] as [INTEGER] - [1]
select parent0_.parent_id as parent_i1_2_, parent0_.parent_name as parent_n2_2_ from parent parent0_ inner join child children1_ on parent0_.parent_id=children1_.parent_parent_id where children1_.child_id=?
binding parameter [1] as [INTEGER] - [1]