JPA-SQL

RapidClipse offers the JPA-SQL Editor, a powerful tool for formulating your database queries. JPA-SQL is a domain-specific, database independent language (DSL). The syntax has been specifically adapted from SQL, so that JPA-SQL is easy to learn. JPA-SQL is not to be confused with native SQL. Queries formulated in JPA-SQL are never sent to a database, but are converted into Java code based on the JPA Criteria API. This gives you all the benefits of the JPA Criteria API, but without having to write JPA Criteria code.

Only at runtime are native SQL statements generated from the generated JPA Criteria code by Hibernate, suitable for the corresponding connected database, and sent to the database. JPA-SQL thus combines the simplicity of SQL with the numerous advantages of the JPA Criteria API.

In Project Management > Data Access, click the DAO you want to use to perform a database query, e.g. CustomerDAO.java.
Press Ctrl + Space and select query - create new query in the following autocomplete.
Specify a suitable method name for the generated method, e.g. findAllCustomer.

findAllCustomer()
{
    select * from
}

After the keyword, type from > C, press Ctrl + Space and select Customer.
Click Save.

Advantages of JPA-SQL compared to using SQL strings:.

Clearer code structure
Type safe
Any order of statements
IDE support
- Code Completion - recognition of keywords, operators and entities
- Syntax highlighting
- Code Folding
- 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
- debuggable (generated JPA criteria code)
Query method in DAO is generated automatically
Database independent

Advantages of JPA-SQL compared to the JPA Criteria API:

SQL-like syntax and code structure
Significantly less complexity
Clearer code
Easy to learn

Result:

Queries - JPA-SQL Code.

import com.company.test.entities.Customer

findAllCustomer()
{
    select * from Customer
}

Java - Generated Java code based on the JPA Criteria API. The necessary imports are also generated for 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();
}

Examples:

Find all

findAllCustomer()
{
    select * from Customer
}

Alternatively allowed notation:

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
}

Objects can also be passed 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)
}

Return of certain columns

findAllCustomerColumn()
{
    select customerid, city, address from Customer
}

Saves the query result to another class

findAllCustomer()
{
    select customerid, city, address from Customer
    into FlatCustomer
}

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 - Groups by the product name and calculates the average order value of the order details.

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 sort

getAllCustomerWithOrderByDesc()
{
    select * from Customer order by city desc
}

Ascending sort

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"
}

Complex Queries
- Returns all orders from London and groups them by the sum of all orders from London.

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
}

JPA-SQL Functional Scope- JPA-SQL was developed to simplify the use of the JPA Criteria API. As a result, the functional scope of JPA-SQL is limited to the functional scope of the JPA Criteria API.
Edit generated code - The generation of the Java JPA Criteria code is unidirectional. I.e., changes may only be made in the JPA-SQL code. The generated Java JPA Criteria code must not be edited.
Multiple query methods - There can be any number of query methods 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