SQL 什么是命名查询？

What is a named query?

提问by aloksapru

I have read its definition but not able to understand fully.

我已经阅读了它的定义，但无法完全理解。

回答by darioo

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

让我猜猜：您偶然发现了命名查询的概念，并且想知道它在 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.

好吧，据我所知，命名查询与“纯”SQL 没有任何关系，但它们是各种 ORM（对象关系映射）框架中的一个概念，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.

然后您使用MyEntity.getItemsPerProductCategory.

Example taken from this site.

示例取自该站点。

You might wonder why this is useful?

你可能想知道为什么这很有用？

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

像Hibernate这样的 JPA 实现可以在启动时检查命名查询的有效性，因此在某种程度上，您可以进行安全的类型检查。这将有助于减少运行时的错误。

回答by Jinesh Parekh

I believe you are talking about Hibernate.

我相信你在谈论 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.

您有更多选择，也可以将参数传递给它。

回答by fsdfsf

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:

命名查询是元数据中表达的静态查询。查询名称的范围是持久化单元。以下是 Java Persistence 查询语言中命名查询的定义示例：

@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();

回答by brent777

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.

在 JPA 中有两种定义查询的方法，动态和静态。命名查询是后者（即静态）。您可以直接在 XML 元数据文件或实际实体上定义查询。请注意，这些查询具有全局范围（即跨越整个持久性单元），即无论它们在哪里定义，名称都必须是唯一的，例如，如果您在实体“Employee”上定义命名查询并且命名查询称为“findAll” "并且您在名为“部门”的实体上定义了另一个名为“findAll”的命名查询，那么这些查询将发生冲突。

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.

作为一般的最佳实践，任何不需要动态构建的查询（例如，在编译时具有不确定数量的 and 子句的查询，因为这些是由用户定义的过滤器在运行时确定的，需要动态构建）应该通过本机查询完成，因为它们在应用程序启动时被处理/转换为 SQL 一次，而不是每次使用查询时。因此它们比动态查询更有效。

回答by Muyinda Rogers

According to http://www.objectdb.com/java/jpa/query/namedA 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.

根据http://www.objectdb.com/java/jpa/query/named命名查询是静态定义的查询，具有预定义的不可更改的查询字符串。使用命名查询代替动态查询可以通过将 JPQL 查询字符串与 Java 代码分开来改进代码组织。它还强制使用查询参数，而不是将文字动态嵌入到查询字符串中，从而实现更高效的查询。

Here is an Example, You should import

这是一个例子，你应该导入

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

导入 javax.persistence.NamedQueries; 导入 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")
}    
)

回答by Muyinda Rogers

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.

命名查询是表示为表的 SQL 表达式。在命名查询中，您可以指定 SQL 表达式来选择从一个或多个数据源中的一个或多个表返回的行和列。命名查询类似于数据源视图 (DSV) 中具有行和关系的任何其他表，只是命名查询基于表达式。

命名查询允许您扩展 DSV 中现有表的关系模式，而无需修改基础数据源。例如，一系列命名查询可用于将复杂的维度表拆分为更小、更简单的维度表，以用于数据库维度。命名查询还可用于将来自一个或多个数据源的多个数据库表连接到单个数据源视图表中。

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

^{（TechNet：在数据源视图中定义命名查询（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 {