The JPA-SQL editor is a powerful tool that facilitates the formulation of your database queries in RapidClipse. JPA-SQL is a domain-specific, database-independent language (DSL). The syntax is deliberately similar to SQL so that JPA-SQL is easy to learn. However, JPA-SQL is not to be confused with native SQL. Queries formulated in JPA-SQL are never sent to a database. Instead, they are converted to Java code based on the JPA Criteria API. Thus, you can access all the benefits of the JPA Criteria API without having to write the JPA Criteria code.
At runtime, Hibernate generates the native SQL statements from the generated JPA Criteria code to match the connected database, and these are sent to the database. Thus JPA-SQL combines the simplicity of SQL with the numerous advantages of JPA Criteria API.
Benefits of JPA-SQL compared to the use of SQL strings:
Formatter
Inline refactoring and refactoring participants for JDT member renames and moves
Hovers
Linking (Ctrl+Click)
Outline view
Error/warning markers with quick fixes
Code templates
Integration in Eclipse build process
Benefits of JPA-SQL in comparison to the JPA Criteria API:
Enter an appropriate method name for the generated method, e.g. findAllCustomer.
findAllCustomer() { select * from } |
Queries - JPA-SQL code.
import com.company.test.entities.Customer findAllCustomer() { select * from Customer } |
Java - Generated Java code based on JPA Criteria API. The necessary imports are generated based on the query method.
/** * @queryMethod Do not edit, method is generated by editor! */ public List<Customer> findAllCustomer() { EntityManager entityManager = em(); CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class); Root<Customer> root = criteriaQuery.from(Customer.class); TypedQuery<Customer> query = entityManager.createQuery(criteriaQuery); return query.getResultList(); } |
Find all
findAllCustomer() { select * from Customer } |
Alternative spelling:
findAllCustomer() { from Customer } |
Where condition
findAllCustomerWhere() { select * from Customer where city = "London" } |
Where condition with parameter
findAllCustomerWhere(String city) { select * from Customer where city = :city } |
It is also possible to pass objects as parameters:
findAllCustomerWhere(Customer customer) { select * from Customer where customer = :customer } |
Like operator
findAllCustomerLike() { select * from Customer where city like "%L" } |
Concat function
findAllCustomerLike(String city) { select * from Customer where city like concat("%", :city) } |
Returns specific columns
findAllCustomerColumn() { select customerid, city, address from Customer } |
Saves the query result in a custom object
findAllCustomer() { select * from Customer into myCustomObject } |
Alias
findAllCustomerAs() { select * from Customer as c where c.city = "London" } |
Logical AND
findAllCustomerAnd() { select * from Customer where city = "London" and country = "UK" } |
Logical OR
findAllCustomerOr() { select * from Customer where city = "London" or city = "Berlin" } |
MAX function - Returns the highest unit price
getOrderDetailMaxPrice() { select max(unitprice) from Orderdetail result single } |
MIN function - Returns the lowest unit price
getOrderDetailMinPrice() { select min(unitprice) from Orderdetail result single } |
AVG function - Grouped by product name. Calculates the average order price of the orders within a group of products
getOrderDetailAvgPrice() { select avg(unitprice) from Orderdetail group by product.productname } |
getOrderDetailAvgPrice() { select avg(unitprice) as price, product.productname from Orderdetail group by product.productname } |
ORDER BY function
Descending order
getAllCustomerWithOrderByDesc() { select * from Customer order by city desc } |
Ascending order
getAllCustomerWithOrderByAsc() { select * from Customer order by city asc } |
Count function
getCountFromCustomerByCountry() { select count(customerid) from Customer group by country } |
Subselects
getOrderDetailsFromLondon() { select * from Orderdetail as detail where detail.`order`.orderid in (select orderid from Order where customer.city = "London") } |
Alternative to Subselects:
getOrderDetailsFromLondon() { select * from Orderdetail where `order`.customer.city = "London" } |
Returns all orders from London grouped by the sum of those orders
getOrderDetailsSumFromLondon() { select sum(detail.unitprice) from Orderdetail as detail where detail.`order`.orderid in (select orderid from Order where customer.city = "London") group by detail.`order`.customer.city result single } |
Possible short form
getOrderDetailsSumFromLondon() { select sum(unitprice) from Orderdetail where `order`.customer.city = "London" group by `order`.customer.city result single } |
Multiple query methods - Any number of query methods can be located in a DAO class.
import com.company.test.entities.Customer findAllCustomer() { select * from Customer } findCustomerByID() { select * from Customer where customerid = "BOLID" } |
JPA-SQL Language Specification