如何有效使用PreparedStatement? [英] How to use PreparedStatement efficiently?

问题描述

我喜欢使用DAO模式，并具有一个类，该类对特定表和JPA实体执行我的所有SQL请求.例如，我有类似的东西:

I like to use the DAO pattern and have a class which do all my SQL request for a particular table and JPA entity. I have for example something like:

public class MyDao {

    @PersistenceContext(name = "mycontext")
    private EntityManager entityManager;

    public List<MyEntity> find(String code)  {

        return getEntityManager()
            .createQuery("FROM MyEntity e WHERE e.code = :code")
            .setParameter("code", code)
            .getResultList();

    }

}

但是我也知道我们可以使用静态方法直接在实体类上使用命名查询(我不喜欢这种方式):

But I also know we can use named query directly on the entity class with a static method (I don't like this way):

@Entity
@Table
@NamedQueries({
    @NamedQuery(name = "find", query = "FROM MyEntity e WHERE e.code = :code")
})
public class MyEntity {

    ...

    public static List<MyEntity> find(EntityManager entityManager, String code) {

        return entityManager.createNamedQuery("find", MyEntity.class)
            .setParameter("code", code)
            .getResultList();

    }

}

其中一种方法是否比另一种更好?如果我想在同一事务中执行数千次相同的SQL查询，这两种方法是否都将JPA内存(或其他位置)保留在准备好的语句中?在这种情况下，这似乎是一个好习惯.我认为第二种方法可以做到这一点，因为它是静态的，但不是第一种.我错了吗 ?

Is one of those method better than the other one ? If I want to execute the same SQL query thousands of times in the same transaction, is both methods keep in JPA memory (or somewhere else) the prepared statement ? Which seems to be a good practise in this case. I would think the second method does it because it's static but not the first one. Am I wrong ?

推荐答案

通过@NamedQuery进行声明定义的查询的优点是它们将被预编译，可以在辅助缓存中进行缓存，并且在启动时在语法上进行验证(如果在其中启用)使用JPA非休眠特定API的persistence.xml .

The advantage for declaritively defined queries via @NamedQuery is that they will be precompiled, can be cached within the secondary cache and syntactically validated on startup if you enable it within the persistence.xml using the JPA non-hibernate specific API.

因此，如果您打算仅使用JPA执行查询，通常最好使用NamedQuery并缓存查询.

So if you plan on executing a query, using only JPA, often it is probably best to use NamedQuery and cache the query.

因此，对于使用休眠模式的JPA，您可以执行以下操作:

So for JPA using hibernate you could do something like this:

            @NamedQuery(name="AbstractBaseQuestion.findAllInstancesByGroupID", query="SELECT q FROM AbstractBaseQuestion q WHERE q.isTemplate = FALSE", hints={@QueryHint(name="org.hibernate.cacheable", value="true"),@QueryHint(name="org.hibernate.cacheMode", value="NORMAL"),}),

在用于休眠的persistence.xml中，您可以在启动时验证以下@NamedQueries:

Within your persistence.xml for hibernate you can validate these @NamedQueries on startup:

      <property name="hibernate.hbm2ddl.auto" value="validate"/>

现在，如果您使用的是Hibernate Session API，则您建议的 first 方法可以进行缓存和预编译.我想在EclipseLink和其他ORM上有等效的功能，但此时您正在使用非JPA功能，这可能会使从一种JPA实现过渡到另一种JPA实现变得困难.

Now the first method you suggested can be cached and precompiled if you use the Hibernate Session API. I imagine there are equivalents on EclipseLink and other ORM's but at this point you are using non-JPA features which could make moving from one JPA implementation to another difficult.

如果您不执行额外的特定于实现的工作，那么您的查询将不会被缓存，您将为此付出性能上的损失.

If you don't do that extra implementation specific work your query is going to not be cached and you will pay a performance penalty.

我希望能帮上忙.

这篇关于如何有效使用PreparedStatement?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！