Datasource configuration - error even though values set

[mitab]

Hi

I am getting a strange error when I remove hardcoded values from the datasource configuration even though the datasource values are getting loaded from properties file
The details are :
My datasource was configured as
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverM anagerDataSource">
<property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@....."/>
<property name="username" value="user1"/>
<property name="password" value="user1"/>
<property name="connectionProperties">
<props><prop key="autoCommit">false</prop></props>
</property>
</bean>

The first class which uses this datasource is configured as follows

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >
<property name="dataSource" ref="dataSource"/>
</bean>

<bean id="dropUserObjects" class="com.adp.hwse.test.util.sql.DropUserObjectsR unnable">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>

The code of the class has

public class DropUserObjectsRunnable implements Runnable {

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}

public void run() {

try {
jdbcTemplate.query(
"select object_type, object_name from user_objects",
new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
String type = rs.getString("object_type");
String name = rs.getString("object_name");
if (SUFFIX.containsKey(type)) {
String drop = "drop " + type + " " + name + SUFFIX.get(type);
jdbcTemplate.execute(drop);
}
}
});


} catch (Exception e) {
throw new RuntimeException(e);
}
When the datasource values are hardcoded the code works fine.
To get the values for the datasource from a properties file I made the following changes
in the xml file where the datasource is configured I added the following before the datasource entry :
<bean class="org.springframework.beans.factory.config.Pr opertyPlaceholderConfigurer">
<property name="locations">
<value>classpath:./jdbc.properties</value>
</property>
</bean>

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverM anagerDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<property name="connectionProperties">
<props><prop key="autoCommit">false</prop></props>
</property>
</bean>

I have created a properties file called jdbc.properties with the appropriate values eg jdbc.driverClassName=oracle.jdbc.OracleDriver

Now when I have hardcoded values for the datasource properties everything runs as expected but once I replaced the hardcoded values I get a SQL exception -
"no suitable driver" found when the new RowCallbackHandler() {} is executed. The strange thing is that when I inspected jdbcTemplate on the line jdbcTemplate.query
I can see that it has a dataSource with all the properties set to the correct values including name of the driver. I am at a loss as to why changing from hardcoded strings
to loading values from properties should cause this as I have not changed anything else and I am using the same values to set up the datasource. and moreover
the jdbcTemplate has the dataSource values set.


Thanks for the help in advance

[trisberg]

The error "no suitable driver" could indicate a problem with the url in your properties file.

[mitab]

Hi

I checked the url is correct and is exactly the same one which I use as hardcoded values. As I said the values are the same (hard coded or properties file) and I in debug mode I can see that the jdbcTemplate's datasource has all the values set correctly.

Thanks

[trisberg]

Try adding this somewhere in your code to see what drivers you actually have loaded:

    Enumeration<Driver> drivers = DriverManager.getDrivers();
    while (drivers.hasMoreElements()) {
        Driver d = drivers.nextElement();
        try {
            System.out.println("->" + 
                d.getClass().getName() + " = " +
                d.acceptsURL("jdbc:oracle:thin:@....."));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

[mitab]

Hi

Thanks for the code snippet and suggestion. I tried it and found that the driver was not getting loaded. I looked at the properties file again; I think some unprintable characters were added as when I deleted the properties file and created it again , my code worked.

Many thanks for your help

[trisberg]

Glad you got it working. You should also consider using DBCP or C3P0 or some other connection pool unless this is simply for some non critical tests.