Why
Using bind parameters is recommanded for performance. Moreover, bind parameters can prevent SQL injections.
References:
- Oracle Performance Survival Guide: A Systematic Approach to Database Optimization
- https://use-the-index-luke.com/sql/where-clause/bind-parameters
- https://dzone.com/articles/why-sql-bind-variables-are-important-for-performan
- https://blogs.oracle.com/sql/improve-sql-query-performance-by-using-bind-variables
- https://www.ibm.com/developerworks/library/se-bindvariables/index.html)
- ...
The role of @DisableQueriesWithoutBindParameters is to prevent the execution of requests without bind parameters. This annotation could be used whith a global scope, that is to say applied on each QuickPerf test.
@EnableQueriesWithoutBindParameters will cancel the behavior of @DisableQueriesWithoutBindParameters. @EnableQueriesWithoutBindParameters may be applied on a specific test method where some values can influence the execution plan (https://use-the-index-luke.com/sql/where-clause/bind-parameters).
Use cases
- With @DisableQueriesWithoutBindParameters, a test sending the following request to database must Not fail:
SELECT
*
FROM
book
WHERE
isbn = ?
AND title = ?"], Params:[(978-0134685991,Effective Java)]
Java code example generating this request:
EntityManager em = emf.createEntityManager();
String sql = "SELECT * FROM book WHERE isbn = :isbn AND title = :title";
Query nativeQuery = em.createNativeQuery(sql)
.setParameter("isbn", "978-0321356680")
.setParameter("title", "Effective Java");
nativeQuery.getResultList();
- With @DisableQueriesWithoutBindParameters, a test sending the following request to database must Not fail:
SELECT * FROM book
- With @DisableQueriesWithoutBindParameters, a test sending the following request to database must fail:
SELECT
*
FROM
book
WHERE
isbn = '978-0321356680'
AND title = 'Effective Java'
Java code example generating this request:
EntityManager em = emf.createEntityManager();
String sql = "SELECT * FROM book WHERE isbn = '978-0321356680' AND title = 'Effective Java'";
Query nativeQuery = em.createNativeQuery(sql);
nativeQuery.getResultList();
- With @DisableQueriesWithoutBindParameters, a test sending the following request to database must Not fail:
UPDATE
book
SET
isbn = ?,
title = ?
WHERE
id = ?"], Params:[(978-0321356680,Effective Java,40)]
Java code example generating this request:
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
String sql = "UPDATE book SET isbn = :isbn, title = :title WHERE id = :id";
Query nativeQuery = em.createNativeQuery(sql)
.setParameter("isbn", "978-0321356680")
.setParameter("title", "Effective Java")
.setParameter("id", 40);
nativeQuery.executeUpdate();
em.getTransaction().commit();
- With @DisableQueriesWithoutBindParameters, a test sending the following request to database must fail:
UPDATE
book
SET
isbn = '978-0321356680',
title = 'Effective Java'
WHERE
id = '40'
Java code example generating this request:
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
String sql = "UPDATE book SET isbn = '978-0321356680', title = 'Effective Java' WHERE id = '40'";
Query nativeQuery = em.createNativeQuery(sql);
nativeQuery.executeUpdate();
em.getTransaction().commit();
- With @DisableQueriesWithoutBindParameters, a test sending the following request to database must Not fail:
DELETE
FROM
book
WHERE
id = ?"], Params:[(40)
Java code example generating this request:
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
String sql = "DELETE FROM book WHERE id = :id";
Query nativeQuery = em.createNativeQuery(sql)
.setParameter("id", 40);
nativeQuery.executeUpdate();
em.getTransaction().commit();
- With @DisableQueriesWithoutBindParameters, a test sending the following request to database must fail:
DELETE
FROM
book
WHERE
id = '40'
Java code example generating this request:
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
String sql = "DELETE FROM book WHERE id = '40'";
Query nativeQuery = em.createNativeQuery(sql);
nativeQuery.executeUpdate();
em.getTransaction().commit();
Implementation
This documentation can help you to implement, in particular this part.
:sparkles: feature sql