Spring + c3p0

Pessoal estou passando por um problema aqui na empresea gostaria de saber se alguém já passou pelo mesmo caso.

Tenho que disparar uma Stored Procedure no banco Oracle, a conexão é feita via hibernate mas a busca por esse caso em especifico é feito via :

view plainprint?

Session session = this.getSessionFactory().getCurrentSession();
CallableStatement call = session.connection().prepareCall(sql);

Session session = this.getSessionFactory().getCurrentSession(); CallableStatement call = session.connection().prepareCall(sql);

O probelma é o seguinte, para disparar uma procedure simples faz normalmente, porém, quan eu preciso que me seja retornado dados o pool de conexão nao me da assistência, esta configurado no AplicationContext assim:
view plainprint?

<bean id=“dataSource” class=“com.mchange.v2.c3p0.ComboPooledDataSource” destroy-method=“close”>

&lt;property name="driverClass" value="OracleDriver"/&gt;

&lt;property name="jdbcUrl" value="orades"/&gt;

&lt;property name="user" value="Projeto"/&gt;

&lt;property name="password" value="*****"/&gt;

</bean>
<bean id=“sessionFactory” class=“org.springframework.orm.hibernate3.LocalSessionFactoryBean”>
<property name=“mappingDirectoryLocations”>
<list>

&lt;value&gt;classpath:*****&lt;/value&gt;

&lt;value&gt;classpath:*****&lt;/value&gt;

&lt;value&gt;classpath:******&lt;/value&gt;

</list>
</property>
<property name=“hibernateProperties”>
<props>

&lt;prop key="hibernate.c3p0.max_size"&gt;500&lt;/prop&gt;

&lt;prop key="hibernate.c3p0.min_size"&gt;20&lt;/prop&gt;

&lt;prop key="hibernate.c3p0.timeout"&gt;50000&lt;/prop&gt;

&lt;prop key="hibernate.c3p0.max_statements"&gt;500&lt;/prop&gt;

&lt;prop key="hibernate.c3p0.idle_test_period"&gt;30000&lt;/prop&gt;

&lt;prop key="hibernate.c3p0.acquire_increment"&gt;20&lt;/prop&gt;

&lt;prop key="hibernate.generate_statistics"&gt;true&lt;/prop&gt;

&lt;prop key="hibernate.use_sql_comments"&gt;false&lt;/prop&gt;

&lt;prop key="hibernate.c3p0.initial_pool_size"&gt;4&lt;/prop&gt;

&lt;prop key="hibernate.c3p0.min_pool_size"&gt;2&lt;/prop&gt;

&lt;prop key="hibernate.c3p0.max_pool_size"&gt;25&lt;/prop&gt;

&lt;!--&lt;prop key="hibernate.hbm2ddl.auto"&gt;none&lt;/prop&gt;--&gt;

&lt;prop key="hibernate.dialect"&gt;org.hibernate.dialect.Oracle9Dialect&lt;/prop&gt;

&lt;prop key="hibernate.show_sql"&gt;true&lt;/prop&gt;

&lt;prop key="hibernate.cglib.use_reflection_optimizer"&gt;true&lt;/prop&gt;

&lt;prop key="hibernate.cache.provider_class"&gt;org.hibernate.cache.HashtableCacheProvider&lt;/prop&gt;

</props>
</property>
<property name=“dataSource”>
<ref bean=“dataSource”/>
</property>
</bean>

&lt;bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"&gt;

&lt;property name="nestedTransactionAllowed"&gt;

```
&lt;value&gt;true&lt;/value&gt;
```
```
&lt;/property&gt;
```

&lt;property name="sessionFactory"&gt;&lt;ref local="sessionFactory"/&gt;&lt;/property&gt;

&lt;property name="jdbcExceptionTranslator"&gt;&lt;ref bean="jdbcExceptionTranslator"/&gt;&lt;/property&gt;

```
&lt;/bean&gt;
```

&lt;bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate"&gt;

&lt;property name="transactionManager"&gt;&lt;ref bean="transactionManager"/&gt;&lt;/property&gt;

```
&lt;/bean&gt;
```

&lt;bean id="errorCode" class="exception.MySqlErrorCode" /&gt;

&lt;bean id="constantes" class="ConstantesOracle" /&gt;

&lt;bean id="jdbcExceptionTranslator" class="exception.MySQLErrorCodesTranslator"&gt;

&lt;property name="arquivoMensagens"&gt;&lt;value&gt;mensagensCce.properties&lt;/value&gt;&lt;/property&gt;

&lt;property name="constantes"&gt;&lt;ref bean="constantes"/&gt;&lt;/property&gt;

&lt;property name="dataSource"&gt;&lt;ref bean="dataSource"/&gt;&lt;/property&gt;

&lt;property name="sqlErrorCodes"&gt;&lt;ref bean="errorCode"/&gt;&lt;/property&gt;

```
&lt;/bean&gt;
```

&lt;bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate"&gt;

<property name=“sessionFactory”><ref bean=“sessionFactory”/></property>
<property name=“jdbcExceptionTranslator”><ref bean=“jdbcExceptionTranslator”/></property>
</bean>
<bean name=“mensagemSucesso” class=“java.lang.String”><constructor-arg><value>Operação realizada com sucesso.</value></constructor-arg></bean>
<bean name=“arquivoMensagens” class=“java.lang.String”><constructor-arg><value>Projeto</value></constructor-arg></bean>
<bean name=“email” class=“java.lang.String”><constructor-arg><value>mail.properties</value></constructor-arg></bean>

<bean id=“dataSource” class=“com.mchange.v2.c3p0.ComboPooledDataSource” destroy-method=“close”> <property name=“driverClass” value=“OracleDriver”/> <property name=“jdbcUrl” value=“orades”/> <property name=“user” value=“Projeto”/> <property name=“password” value=“"/> </bean> <bean id=“sessionFactory” class=“org.springframework.orm.hibernate3.LocalSessionFactoryBean”> <property name=“mappingDirectoryLocations”> <list> <value>classpath:</value> <value>classpath:</value> <value>classpath:*</value> </list> </property> <property name=“hibernateProperties”> <props> <prop key=“hibernate.c3p0.max_size”>500</prop> <prop key=“hibernate.c3p0.min_size”>20</prop> <prop key=“hibernate.c3p0.timeout”>50000</prop> <prop key=“hibernate.c3p0.max_statements”>500</prop> <prop key=“hibernate.c3p0.idle_test_period”>30000</prop> <prop key=“hibernate.c3p0.acquire_increment”>20</prop> <prop key=“hibernate.generate_statistics”>true</prop> <prop key=“hibernate.use_sql_comments”>false</prop> <prop key=“hibernate.c3p0.initial_pool_size”>4</prop> <prop key=“hibernate.c3p0.min_pool_size”>2</prop> <prop key=“hibernate.c3p0.max_pool_size”>25</prop> <!–<prop key=“hibernate.hbm2ddl.auto”>none</prop>–> <prop key=“hibernate.dialect”>org.hibernate.dialect.Oracle9Dialect</prop> <prop key=“hibernate.show_sql”>true</prop> <prop key=“hibernate.cglib.use_reflection_optimizer”>true</prop> <prop key=“hibernate.cache.provider_class”>org.hibernate.cache.HashtableCacheProvider</prop> </props> </property> <property name=“dataSource”> <ref bean=“dataSource”/> </property> </bean> <bean id=“transactionManager” class=“org.springframework.orm.hibernate3.HibernateTransactionManager”> <property name=“nestedTransactionAllowed”> <value>true</value> </property> <property name=“sessionFactory”><ref local=“sessionFactory”/></property> <property name=“jdbcExceptionTranslator”><ref bean=“jdbcExceptionTranslator”/></property> </bean> <bean id=“transactionTemplate” class=“org.springframework.transaction.support.TransactionTemplate”> <property name=“transactionManager”><ref bean=“transactionManager”/></property> </bean> <bean id=“errorCode” class=“exception.MySqlErrorCode” /> <bean id=“constantes” class=“ConstantesOracle” />  <bean id=“jdbcExceptionTranslator” class=“exception.MySQLErrorCodesTranslator”> <property name=“arquivoMensagens”><value>mensagensCce.properties</value></property> <property name=“constantes”><ref bean=“constantes”/></property> <property name=“dataSource”><ref bean=“dataSource”/></property> <property name=“sqlErrorCodes”><ref bean=“errorCode”/></property> </bean> <bean id=“hibernateTemplate” class=“org.springframework.orm.hibernate3.HibernateTemplate”> <property name=“sessionFactory”><ref bean=“sessionFactory”/></property> <property name=“jdbcExceptionTranslator”><ref bean=“jdbcExceptionTranslator”/></property> </bean> <bean name=“mensagemSucesso” class=“java.lang.String”><constructor-arg><value>Operação realizada com sucesso.</value></constructor-arg></bean> <bean name=“arquivoMensagens” class=“java.lang.String”><constructor-arg><value>Projeto</value></constructor-arg></bean> <bean name=“email” class=“java.lang.String”><constructor-arg><value>mail.properties</value></constructor-arg></bean>

Se alguém passou por esse problema poderia me ajudar por favor?

rodrigo_gomes:
Olá,

o que vc quer dizer com "pool de conexão nao me da assistência"?

A chamada é para esse método o problema ocorre quando executa a linha:

//cria um cursor para o Oracle
((OracleConnection) call.getConnection()).setCreateStatementAsRefCursor(true);

public List executeProcedure(String sql, Object[] parametros, boolean returnType) throws BDException, AcessoException{

List lista = null;

try{

Session session = this.getSessionFactory().getCurrentSession();

CallableStatement call = session.connection().prepareCall(sql);

if(parametros != null){

//percorre o array de parâmetros inserindo os mesmos na

//procedure

for(int i = 0; i < parametros.length; i++){

//campo que referência um resultSet

if(parametros[i] == null){

call.registerOutParameter(i+1, -10);

//insere um tipo INTEGER

}else if(parametros[i] instanceof Integer){

call.setInt(i+1, ((Integer)parametros[i]).intValue());

//insere um tipo LONG

}else if(parametros[i] instanceof Long){

call.setLong(i+1, ((Long)parametros[i]).longValue());

//insere um tipo DOUBLE

}else if(parametros[i] instanceof Double){

call.setDouble(i+1, ((Double)parametros[i]).doubleValue());

//insere um tipo FLOAT

}else if(parametros[i] instanceof Float){

call.setFloat(i+1, ((Float)parametros[i]).floatValue());

//insere um tipo STRING

}else if(parametros[i] instanceof String){

call.setString(i+1, (String)parametros[i]);

//insere um tipo BYTE

}else if(parametros[i] instanceof Byte){

call.setByte(i+1, ((Byte)parametros[i]).byteValue());

}

}

//cria um cursor  para o Oracle

((OracleConnection) call.getConnection()).setCreateStatementAsRefCursor(true);

//executa a procedura

call.execute();

if(returnType){

//converte o ResultSet em uma Lista de MAP’S com os itens da Procedure

lista = this.converteResultSet((ResultSet)call.getObject(1), null);

}

call.close();

}else{

if(returnType){

lista = this.converteResultSet(call.executeQuery(), null);

}else {

call.execute();

}

call.close();

}

}catch (Exception e) {

throw new AcessoException(e);

}

return lista;

}

ERRO:

.persistencia.hibernate.DAOHibernate.executeProcedure() - com.mchange.v2.c3p0.impl.NewProxyConnection
java.lang.ClassCastException: com.mchange.v2.c3p0.impl.NewProxyConnection
persistencia.hibernate.DAOHibernate.executeProcedure(DAOHibernate.java:117)

Linha 117 = ((OracleConnection) call.getConnection()).setCreateStatementAsRefCursor(true);

2 Respostas

Topicos relacionados