What is a named query?

I have read its definition but not able to underst开发者_Go百科and fully.

Let me guess: you've stumbled upon concept of named queries and you wonder where it fits in SQL.

Well, from my knowledge, named queries haven't got anything to do with "pure" SQL, but they're a concept found in various ORM (object relational mapping) frameworks, ala Java Persistence API.

Basically, a named query is one way to map a name to a query that might be called several times in your code at different places.

So, instead of using

"SELECT i FROM Item i WHERE i.product.categoryID LIKE :cID"

as a string at various places in your code, you use this:

@NamedQuery( 
  name="MyEntity.getItemsPerProductCategory", 
  query="SELECT i FROM Item i WHERE i.product.categoryID LIKE :cID"
)

and afterwards you refer to this query using MyEntity.getItemsPerProductCategory.

Example taken from this site.

You might wonder why this is useful?

A JPA implementation like Hibernate can check validity for named queries at startup, so in one way, you've got safe type checking. This will help reduce errors at runtime.

I believe you are talking about Hibernate.

In simple terms, a named query is a query that can be identified by a name. You could define a named query as below and use it by its name.

@NamedQuery name="findAllUsers" query="SELECT u FROM Users u"
findByNamedQuery("findAllUsers")

You have more options and can pass in parameters to it as well.

Named query is the static query expressed in metadata.Query names are scoped to persistence unit. The following is an example of the definition of a named query in the Java Persistence query language:

@NamedQuery(
        name="findAllCustomersWithName",
        query="SELECT c FROM Customer c WHERE c.name LIKE :custName"
)

The following is an example of the use of a named query:

@PersistenceContext
public EntityManager em;
...
customers = em.createNamedQuery("findAllCustomersWithName")
        .setParameter("custName", "Smith")
        .getResultList();

There are 2 ways to define queries in JPA, Dynamically and Statically. Named queries are the latter (i.e. static). You would define the query in an XML metadata file or on an actual entity directly. Note that these queries have global scope (i.e. across the entire persistence unit), i.e. no matter where they are defined the names must be unique, e.g. if you define a named query on and Entity "Employee" and the named query is called "findAll" and you have another named query called "findAll" defined on an Entity called "Department", then these queries will clash.

That is generally why named queries' names are prefix with the Entity name to which they apply, for example:

    @NamedQueries({@NamedQuery(name="Employee.findAll", query="select e from Employee e"),
               @NamedQuery(name="Employee.findByName", query="select e from Employee e where e.name = :name")})
    @Entity
    public class Employee ... {
    ....

As a general best practice, any queries that do not need to be built up dynamically (e.g. a query that has an indeterminate amount of and clauses at compile time since these are determined by user defined filters at runtime would need to be built up dynamically) should be done through a native query since they are processed / converted to SQL once on app startup as opposed to every time the query is used. So they are more efficient than dynamic queries.

According to http://www.objectdb.com/java/jpa/query/named A named query is a statically defined query with a predefined unchangeable query string. Using named queries instead of dynamic queries may improve code organization by separating the JPQL query strings from the Java code. It also enforces the use of query parameters rather than embedding literals dynamically into the query string and results in more efficient queries.

Here is an Example, You should import

import javax.persistence.NamedQueries; import javax.persistence.NamedQuery;

@NamedQueries({
    @NamedQuery(name = "Tenantdata.findAll", query = "SELECT c FROM Tenantdata c"),
    @NamedQuery(name = "Tenantdata.findById", query = "SELECT c FROM Tenantdata c WHERE c.id = :id")
}    
)

A named query is a SQL expression represented as a table. In a named query, you can specify an SQL expression to select rows and columns returned from one or more tables in one or more data sources. A named query is like any other table in a data source view (DSV) with rows and relationships, except that the named query is based on an expression.

A named query lets you extend the relational schema of existing tables in DSV without modifying the underlying data source. For example, a series of named queries can be used to split up a complex dimension table into smaller, simpler dimension tables for use in database dimensions. A named query can also be used to join multiple database tables from one or more data sources into a single data source view table.

^{(TechNet: Define Named Queries in a Data Source View (Analysis Services))}

@Entity
@Table(name = "users")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Users.findAll", query = "SELECT u FROM Users u")
    , @NamedQuery(name = "Users.findById", query = "SELECT u FROM Users u WHERE u.id = :id")
    , @NamedQuery(name = "Users.findByUsername", query = "SELECT u FROM Users u WHERE u.username = :username")
    , @NamedQuery(name = "Users.findByPassword", query = "SELECT u FROM Users u WHERE u.password = :password")
    , @NamedQuery(name = "Users.findByStatus", query = "SELECT u FROM Users u WHERE u.status = :status")
    , @NamedQuery(name = "Users.findByDateCreated", query = "SELECT u FROM Users u WHERE u.dateCreated = :dateCreated")})
public class Users implements Serializable {