c3p0 and hibernate user / password override

Question

I'm using spring 3.2 and hibernate 4.2. i have 2 beans: datasource (c3p0) and sessionFactory (LocalSessionFactoryBean) with property file (application.properties).

when i define the datasource using java configuration with explicit username and password all works good. during startup c3p0 logs its configuration. e.g. 'properties' property is:

properties -> {user=******, password=******}

that's the exact output

but when i define the same datasource in xml (also with user and password) instead of in java, the application behaves differently.

'properties' property shows

properties -> {java.runtime.name=xxx, line.separator=xxx, maven.home=xxx, ...}

and all environment variables but nothing about user or password. and it refuses to connect to database:

java.sql.SQLException: ORA-01017: invalid username/password; logon denied

but when in application.properties i add:

hibernate.connection.username=xxx
hibernate.connection.password=xxx

c3p0 again prints all the environment properties without user and password but connection succeeds. well, ok: pooling can be pushed to session factory but credentials must be in the datasource

so what's going on? i want to have datasource with credentials and pooling configuration and hibernate session factory with hibernate configuration.

in case it matters. below is my configuration:

<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="packagesToScan" value="xxx.model" />
        <property name="hibernateProperties">
            <util:properties location="classpath:/spring/application.properties" />
        </property>
    </bean>

xml ds:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"/>
        <property name="user" value="xxx"/>
        <property name="password" value="xxx"/>
        <property name="jdbcUrl" value="xxx"/>
    </bean>

and java ds:

@Bean
public DataSource dataSource() throws PropertyVetoException {
    ComboPooledDataSource ds = new ComboPooledDataSource();
    ds.setDriverClass("oracle.jdbc.driver.OracleDriver");
    ds.setUser("xxx");
    ds.setPassword("xxx");
    ds.setJdbcUrl("xxx");
    return ds;
}

Answer 1

So this is very weird.

My guess is this. c3p0's DriverManagerDataSource has a property called "properties", of type java.util.Properties.

Somewhere, something in your universe of middleware is configured to automatically set such a property to System.properties. That strikes me as a bad thing, but it seems to be what's happening. The best thing to do would be to turn this behavior off. It's not a c3p0 thing, but other than that, I'm not sure where you ought to look.

A suggestion would be to try setting the properties property [yuk] explicitly in your XML. Maybe that will prevent it from defaulting to System properties. See e.g. here for how to set a property of type Properties. The properties property should contain a key called "user" and a key called "password" (and there is no need to set user and password outside of these properties). Something like:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="jdbcUrl" value="xxx"/>
    <property name ="properties">
       <props>
          <prop key="user">xxx</prop>
          <prop key="paswords">xxx</prop>
       </props>
    </property>
</bean>

Good luck!

Answer 2

I faced a weird problem like this one last week, searching on internet i found this question and answer from @Steve Waldman https://stackoverflow.com/a/15787141/2033885 that helped me to solve this problem. I know this question is old, but i hope my answer may help others.

Below i describe the issue:

When you define "datasource" bean by using C3P0 com.mchange.v2.c3p0.ComboPooledDataSource in your ApplicationContext, properties 'user' & 'password' are seted explicitly or getting values from an Properties file, in my case "persistence.properties". Next my Data Source bean in Application Context:

<context:property-placeholder location="classpath*:config/persistence.properties" />

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <!-- access -->
        <property name="driverClass" value="${driverClass}" />
        <property name="jdbcUrl" value="${jdbcUrl}" />
        <property name="user" value="${user}" />
        <property name="password" value="${password}" />
</bean>

My mistake was use a property named "user", when i run my app in Windows its all just fine, but.. in Linux, there is already a System property named "user" that is overriding my "user" property in persistence.properties. The fix in my case was change property name for "user" to "jdbc.user", both in persistence.properties and application context:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <!-- access -->
        <property name="driverClass" value="${driverClass}" />
        <property name="jdbcUrl" value="${jdbcUrl}" />
        <property name="user" value="${jdbc.user}" />
        <property name="password" value="${jdbc.password}" />
</bean>

I have been developing using Windows, but testing and production environment is Linux. I thought that Tomcat is Tomcat and no worth take much care about underlying OS, but due to this issue my lesson learned is: Final deployment environment always matter, whatever which Application Server you are using :)