JPA-SQL

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:

  • Clearer code structure
  • Type safe
  • Any order of statements
  • IDE support
    • Code Completion - detection 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 (JPA Criteria Code is generated)
  • Query method is automatically generated in DAO
  • Database independent

Benefits of JPA-SQL in comparison to the JPA Criteria API:

  • SQL-like syntax and code structure
  • Significantly lower complexity
  • Cleaner code
  • Easy to learn


  1. Under Project Management > Data Access click the DAO with which you want to perform a database query, e.g. CustomerDAO.java.
  2. Press Strg + Space and select query: create new query in the following autocomplete window.
  3. Enter an appropriate method name for the generated method, e.g. findAllCustomer.

    findAllCustomer()
    {
    	select * from 
    }
  4. After the keyword from, enter C, press Strg + Space and select Customer.
  5. Click Save.


Result:
  • 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();
    }
Examples:
  • 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 different class

    findAllCustomer()
    {
    	select customerid, contactname, city 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 - 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"
    }
  • Complex queries
    • 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
      }
Note:
  • JPA-SQL functions - JPA-SQL has been developed to simplify the use of the JPA Criteria API. The range of JPA-SQL functions is limited by the range of the JPA Criteria API functions.
  • Edit generated code - The generation of the Java JPA Criteria codes is unidirectional. This means that changes are possible only in the JPA-SQL code. The generated Java JPA Criteria code cannot be edited.
  • 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

XDEV Software Corp. - One Embarcadero Center, San Francisco, CA 94111, US
Copyright © 2015. XDEV Software Corp. All rights reserved.