Difference between select and select new in jpql

In JPQL (Java Persistence Query Language), SELECT and SELECT NEW are two different ways to retrieve data from a database.

SELECT

The SELECT clause is used to retrieve existing objects from the database. When you use SELECT, you are retrieving objects that already exist in the database. The query returns a list of objects that match the criteria specified in the WHERE clause.

Example:

SELECT e FROM Employee e WHERE e.department = 'Sales'

This query retrieves a list of Employee objects that have a department named 'Sales'.

SELECT NEW

The SELECT NEW clause is used to create new objects from the query results. When you use SELECT NEW, you are creating new objects that are not yet stored in the database. The query returns a list of new objects that are created based on the query results.

Example:

SELECT NEW com.example.Employee(e.name, e.email) FROM Employee e WHERE e.department = 'Sales'

This query creates new Employee objects with the name and email properties set to the corresponding values from the Employee table, and returns a list of these new objects.

Key differences:

• SELECT retrieves existing objects, while SELECT NEW creates new objects.
• SELECT returns a list of existing objects, while SELECT NEW returns a list of new objects.
• SELECT NEW is used when you need to create new objects based on query results, such as when you need to perform complex calculations or transformations on the data.

When to use each:

• Use SELECT when you need to retrieve existing objects from the database.
• Use SELECT NEW when you need to create new objects based on query results, such as when you need to perform complex calculations or transformations on the data.

Note that SELECT NEW is only available in JPA 2.0 and later versions. In earlier versions of JPA, you can use a constructor expression with the SELECT clause to achieve similar results.