在Spring应用程序上下文中配置c3p0（intSQL和时区） [英] Configure c3p0 in spring application context (intSQL and timezone)

问题描述

如何在spring应用程序上下文中配置c3p0？

我正在运行mybatis + spring + c3p0 + Oracle 11g。

扩展名的

c3p0文档说：

扩展名默认值：空java.util.Map java.util.Map（原始类型）
包含任何用户定义的配置扩展名$ b的值为该数据源定义的$ b。

c3p0文档，用于配置的用户扩展说：

 <扩展名> 
< property name = initSql> SET SCHEMA‘foo’< / property> 
< property name = timezone> PDT< / property> 
< / extensions> 
  

因此，我将spring应用程序上下文配置为：

 < bean id = dataSource class = com.mchange.v2.c3p0.ComboPooledDataSource destroy-method = close> ; 
<属性名称= driverClass value = oracle.jdbc.driver.OracleDriver /> 
<属性名称= jdbcUrl value = jdbc：oracle：thin：@ // databasehost：1527 / servicename /> 
<属性名称= user ref = database.user /> 
<属性名称=密码 ref = database.password /> 
<属性名称= extensions> 
< map> 
< entry key = initSql value = ALTER SESSION SET CURRENT_SCHEMA = MY_SCHEMA /> 
< entry key = timezone value = UTC /> 
< / map> 
< / property> 
< / bean> 
  

但是什么也没发生，它不会抛出错误，但不会表现出预期的行为。

解决方案

您所做的回答不足以使其起作用。



如果您调查mysql日志，您会发现 timezone 设置永远不会生效（例如，永远不会执行 set time_zone ...语句）。 / p>

唯一有效的答案是： preferredTestQuery ，您将其设置为更改会话集current_schema = MY_SCHEMA 。



这意味着每次签入（发生在您执行的几乎所有查询之前-即TOO MUCH）还会调用更改会话集current_schema = MY_SCHEMA ，这是非常糟糕的性能做法...

如果要在获取连接时执行某些SQL，则需要使用 ConnectionCustomizer 与您创建的扩展名地图结合使用。 （您可以在他们的文档此处）中看到它）

示例：

 公共类ExampleConnectionCustomizer扩展AbstractConnectionCustomizer {
 public ExampleConnectionCustomizer（）{
} 
 
私有字符串getInitSql（String parentDataSourceIdentityToken）{
 return（String）this.extensionsForToken（parentDataSourceIdentityToken）.get（ initSql）; 
} 
 
 public void onAcquire（Connection c，String pdsIdt）{
 String initSql = this.getInitSql（parentDataSourceIdentityToken）; 
 if（initSql！= null）{
语句stmt = null; 
 try {
 stmt = c.createStatement（）; 
 stmt.executeUpdate（initSql）; 
}最终{
 if（stmt！= null）{
 stmt.close（）; 
} 
} 
} 
} 
 
} 
  

 < bean id = dataSource class = com.mchange .v2.c3p0.ComboPooledDataSource destroy-method = close> 
...。其余的属性... 
<属性名称= preferredTestQuery value = SELECT 1 /> <！-效率更高-> 
<属性名称= connectionCustomizerClassName值= yourpackage.ExampleConnectionCustomizer /> 
<！-扩展名-> 
<属性名称= extensions> 
< map> 
< entry key = initSql value =更改会话集current_schema = MY_SCHEMA /> 
< / map> 
< / property> 
< / bean> 
  

How to configure c3p0 in spring application context?

I am running mybatis + spring + c3p0 + Oracle 11g.

c3p0 documentation for extensions says:

extensions Default: an empty java.util.Map A java.util.Map (raw type) containing the values of any user-defined configuration extensions defined for this DataSource.

c3p0 documentation for user extensions to configurations says:

<extensions>
  <property name="initSql">SET SCHEMA 'foo'</property>
  <property name="timezone">PDT</property>
</extensions>

Therefore, I configured my spring application context as:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="oracle.jdbc.driver.OracleDriver" />
    <property name="jdbcUrl" value="jdbc:oracle:thin:@//databasehost:1527/servicename" />
    <property name="user" ref="database.user" />
    <property name="password" ref="database.password" />
    <property name="extensions">
        <map>
            <entry key="initSql" value="ALTER SESSION SET CURRENT_SCHEMA = MY_SCHEMA" />
            <entry key="timezone" value="UTC" />
        </map>
    </property>
</bean>

However nothing happens, it doesn't throw error but doesn't behave as expected.

解决方案

What you did in your answer is not enough to make it work.

If you investigate your mysql log you will see that the timezone setting never takes effect for example (there will be no "set time_zone..." statement executing ever).

The only thing that takes effect in your answer is: preferredTestQuery which you have set to be alter session set current_schema=MY_SCHEMA.

This means that every Checkin (which happens before almost every query you execute - i.e TOO MUCH) will also call alter session set current_schema=MY_SCHEMA which is a very bad performance practice...

If you want to execute some SQL when the connection is aquired, you need to use a ConnectionCustomizer in combination with the extensions Map you have created. (you can see it written in their documentation here)

Example:

public class ExampleConnectionCustomizer extends AbstractConnectionCustomizer {
    public ExampleConnectionCustomizer () {
    }

    private String getInitSql(String parentDataSourceIdentityToken) {
        return (String)this.extensionsForToken(parentDataSourceIdentityToken).get("initSql");
    }

    public void onAcquire(Connection c, String pdsIdt) {
        String initSql = this.getInitSql(parentDataSourceIdentityToken);
        if(initSql != null) {
            Statement stmt = null;
            try {
                stmt = c.createStatement();
                stmt.executeUpdate(initSql);
            } finally {
                if(stmt != null) {
                    stmt.close();
                }
            }
        }
    }

}  

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    ....The rest of your properties...
    <property name="preferredTestQuery" value="SELECT 1" /> <!--Much more efficient-->
    <property name="connectionCustomizerClassName" value="yourpackage.ExampleConnectionCustomizer" />
    <!-- extensions -->         
    <property name="extensions">
        <map>
            <entry key="initSql" value="alter session set current_schema=MY_SCHEMA" />
        </map>
    </property>
</bean>

这篇关于在Spring应用程序上下文中配置c3p0（intSQL和时区）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！