Hibernate发送多余的查询到数据库 [英] Hibernate sending superfluous queries to the database

问题描述

我有一个奇怪的问题，Hibernate运行的查询比我所要求的要多，而且看不到需要。

这是我的控制器：

  @Autowired UserService用户; 
 
 @RequestMapping（/ test）
 @ResponseBody 
 public String test（）{
 User user = users.findUser（1L）; 
 returnFound user：+ user.getEmail（）; 
} 
  

以下是 UserService ：

  @Component 
 public class UserService {
 
 @ javax.persistence.PersistenceUnit private EntityManagerFactory emf; 
 
 private JpaTemplate getJpaTemplate（）{
 return new JpaTemplate（emf）; 
} 
 
 public User findUser（long id）{
 long start = System.currentTimeMillis（）; 
 JpaTemplate jpaTemplate = getJpaTemplate（）; 
 User user = jpaTemplate.find（User.class，id）; 
 System.out.println（System.currentTimeMillis（） -  start）; 
返回用户; 
 
 
 $ / code> 

调用findUser（）大约需要140ms ..相当莫名其妙。该数据库对其他查询执行得很好，包括这个在一些处理程序中（我怀疑它不是第一个查询运行时）。

$ JP
$ b

JProfiler建议每当它所谓的四个查询被发送到数据库（不一定按照这个顺序）：

pre
1）[5ms] select user ...（实际查询）

2）[7ms] SHOW COLLATION
$ b 3）[14ms] / * mysql-connector-java-5.1.7（Revision：$ {svn。修改}）* / SELECT @@ session.auto_increment_increment
$ b $ 4）[70ms] / * mysql-connector-java-5.1.7（修订版：$ {svn.Revision}）* / SHOW VARIABLES WHERE变量名='字符集'或变量名='字符集'或变量名='字符集'或变量名='字符集集'或变量名='语言'或变量名='net_write_timeout'或变量名='interactive_timeout'或变量名='wait_timeout'或变量名='character_set_client' 'tx_isolation'或者Variable_name = 'transaction_isolation'OR Variable_name ='character_set_results'OR Variable_name ='timezone'OR Variable_name ='time_zone'OR Variable_name ='system_time_zone'OR Variable_name ='lower_case_table_names'OR Variable_name ='max_allowed_pa​​cket'OR Variable_name ='net_buffer_length'OR Variable_name ='sql_mode 'OR Variable_name ='query_cache_type'OR Variable_name ='query_cache_size'OR Variable_name ='init_connect'

清楚地表明实际查询不需要时间在所有的时间里，大部分时间都花在那第四次。我能做些什么呢？它不会在hibernate日志输出中显示，只有第一个实际查询会显示。顺便说一句，所有的时间都是在调用getJpaTemplate（）之后花费的，​​即实际上在jpa.find（）方法中。

任何想法？

更新：我已经发现它是hibernate多次与数据库进行某种初始连接，因为有人发布了相同的一组查询（< a href =http://ondra.zizka.cz/stranky/programovani/java/hibernate_netbeans_howto_tutorial.texy =nofollow noreferrer> http://ondra.zizka.cz/stranky/programovani/java/hibernate_netbeans_howto_tutorial.texy ）。为什么hibernate会重复初始化连接，是不是使用连接池 - 我该如何检查？

我解决了它。我有一个非共用数据源：



 < bean name =dataSourceclass =org.springframework.jdbc.datasource .DriverManagerDataSource> 
  

来自Javadoc： http://static.springsource.org/spring/docs/2.0.x/api/org/ springframework / jdbc / datasource / DriverManagerDataSource.html



标准JDBC DataSource接口的简单实现，配置普通的JDBC DriverManager通过bean属性，并从每个getConnection调用中返回一个新的Connection。
注意：这个类不是实际的连接池;它实际上不会连接连接。它只是简单地替代一个完整的连接池，实现相同的标准接口，但在每次调用时创建新的连接。

所以我现在用这个替换了：



 < bean name =dataSourceclass =com.mchange.v2 .c3p0.ComboPooledDataSource> 
< property name =driverClassvalue =com.mysql.jdbc.Driver/> 
< property name =jdbcUrlvalue =jdbc：mysql：//server.domain/database/> 
< property name =uservalue =theUsername/> 
< property name =passwordvalue =thePassword/> 
< / bean> 
  

我也必须抛出c3p0-0.9.1.2.jar，因为它使用了连接池。


I have an odd problem where Hibernate is running more queries than I've asked for, and can't see the need for.

Here is my controller:

@Autowired UserService users;

@RequestMapping("/test")
@ResponseBody
public String test() {
    User user = users.findUser(1L);
    return "Found user: "+user.getEmail();
}

Here is the UserService:

@Component
public class UserService {

    @javax.persistence.PersistenceUnit private EntityManagerFactory emf;

    private JpaTemplate getJpaTemplate() {
        return new JpaTemplate(emf);
    }

    public User findUser(long id) {
        long start = System.currentTimeMillis();
        JpaTemplate jpaTemplate = getJpaTemplate();
        User user = jpaTemplate.find(User.class, id);
        System.out.println(System.currentTimeMillis() - start);
        return user;
    }
}

The call to findUser() takes about 140ms... Rather baffling. The database performs just fine for other queries, including this one in some handlers (I suspect when it is not the first query run).

JProfiler suggests that every time it is called, four queries are sent to the database (not necessarily in this order):

1) [5ms] select user... (the actual query)

2) [7ms] SHOW COLLATION

3) [14ms] /* mysql-connector-java-5.1.7 ( Revision: ${svn.Revision} ) */SELECT @@session.auto_increment_increment

4) [70ms] /* mysql-connector-java-5.1.7 ( Revision: ${svn.Revision} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'

Its clear that the actual query takes no time at all, and most of the time is spend in that fourth one. What can I do about this? It doesn't show up in the hibernate log output, only the first, actual query does. Incidentally, all the time is spent after the call to getJpaTemplate() - i.e actually in the jpa.find() method.

Any ideas?

UPDATE: I've worked out that it is hibernate doing some sort of initial connection to the database multiple times, since somebody else posted the same set of queries (http://ondra.zizka.cz/stranky/programovani/java/hibernate_netbeans_howto_tutorial.texy). Why would hibernate make its initial connection repeatedly, isn't it using a connection pool - how can I check?

解决方案

I've solved it. I had a non-pooled datasource:

<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

From the Javadoc at: http://static.springsource.org/spring/docs/2.0.x/api/org/springframework/jdbc/datasource/DriverManagerDataSource.html

Simple implementation of the standard JDBC DataSource interface, configuring the plain old JDBC DriverManager via bean properties, and returning a new Connection from every getConnection call. NOTE: This class is not an actual connection pool; it does not actually pool Connections. It just serves as simple replacement for a full-blown connection pool, implementing the same standard interface, but creating new Connections on every call.

So I've now replaced this with:

<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="com.mysql.jdbc.Driver"/> 
    <property name="jdbcUrl" value="jdbc:mysql://server.domain/database"/> 
    <property name="user" value="theUsername"/> 
    <property name="password" value="thePassword"/> 
</bean>

I had to throw in c3p0-0.9.1.2.jar too, since it uses that connection pool.

这篇关于Hibernate发送多余的查询到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！