CODEWITHSUNDEEP
javasql-serverspring
JonnyRaa
JonnyRaa

Reputation: 8038

using SQLServerConnectionPoolDataSource in spring

I've got something that looks like this for connecting to oracle:

<bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource" lazy-init="true" >
    <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource"/>
    <property name="user" value="${database.username}" />
    <property name="password" value="${database.password}" />
    <property name="URL" value="${database.url}" />
    <property name="connectionWaitTimeout" value="30" />
    <property name="minPoolSize" value="1"/>
    <property name="maxPoolSize" value="10"/>
    <property name="inactiveConnectionTimeout" value="3600"/>
    <property name="validateConnectionOnBorrow" value="true"/>
    <property name="maxStatements" value="10"/>
    <property name="connectionProperties">
        <props merge="default">
            <prop key="AutoCommit">false</prop>
        </props>
    </property>
</bean>

which is used by something like this:

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    ....
</bean>

I now want to configure another datasource for sql server. The situation is we either have one or the other. Looking around I've only seen examples which do something like this:

<bean id="sqlServerDataSource" destroy-method="close"
      class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="url" value="[...]"/>
    <property name="username" value="[...]" />
    <property name="password" value="[...]" />
</bean>

but it looks like SQLServerConnectionPoolDataSource probably does connection pooling aswell. It seems like it'd be the better option here as I can more easily pass custom parameters around + I'd rather not depend on yet another random library (the list of dependencies in the project is ridiculusly long already and it's been a source of serious pain)!

Does anyone know if the above class works with spring + how to use it? How can I find out this sort of information without using stackoverflow?!

I'm pretty new to spring and orms and all this stuff and have just about got to the point where I can read the config files and roughly see what they are doing but writing this sort of voodoo isn't something I know how to do yet.

Upvotes: 1

Views: 2890

Answers (2)

JonnyRaa
JonnyRaa

Reputation: 8038

I started using the basic data source mentioned above but found it doesn't let you make connections with credentials you supply by hand so had to abandon that and revisit this problem.

I'm now using the SQLConnectionPoolDataSource - I can't confirm this is working 100% or really what it does or why it works but my bit of xml is the following:

<bean id="sqlServerDataSource"
      class="com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource">
    <property name="URL" value="jdbc:sqlserver://${database.server};databaseName=${database.databaseName};"/>
    <property name="user" value="#{DatabaseSetup.username}" />
    <property name="password" value="#{DatabaseSetup.password}" />
</bean>

I'm switching between the two datasources (this happens at configuration/startup time) by doing the following:

<property name="dataSource" ref="#{DatabaseSetup.dataSourceBean}" />

where datasourcebean is just a string which matches the relevant bean name - it either says "sqlServerDataSource" or "oracleDataSource" based on some build time properties.

The hash there lets you refer to properties on other beans.

Upvotes: 1

ganaraj
ganaraj

Reputation: 420

You could try using a routing datasource, where the datasource to be loaded can be configured using your custom implementation InstanceRoutingDataSource, and you can predefine any number of datasource and implement your logic to get the required connection. InstanceRoutingDataSource

public class InstanceRoutingDataSource extends AbstractRoutingDataSource {

  @Override
  protected Object determineCurrentLookupKey() {
  if(oracle required){
    return "oracle";
  }else if(sql required){
    return "sql";
  }else{
    return "default";
  }
  }
  }

Persistance context

<beans:bean id="routingDataSource" `<beans:bean id="routingDataSource"     class="com.july11.framework.entity.InstanceRoutingDataSource">
  <beans:property name="targetDataSources">
  <beans:map key-type="java.lang.String">
  <beans:entry key="oracle" value-ref="oracleDataSource"/>
  <beans:entry key="sql" value-ref="oracle2DataSource"/>
  <beans:entry key="default" value-ref="oracle2DataSource"/>
  </beans:map>
  </beans:property>
</beans:bean>
<beans:bean id="entityManagerFactory"
   class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
   p:dataSource-ref="routingDataSource">
  <beans:property name="persistenceUnitName" value="persistanceUnit" />
  <beans:property name="jpaPropertyMap">
  <map><entry key="hibernate.dialect" value="${hibernate.dialect}" />
   <entry key="hibernate.hbm2ddl.auto" value="${hibernate.hbm2ddl.auto}" />
   <entry key="hibernate.format_sql" value="${hibernate.format_sql}" />
            <entry key="javax.persistence.validation.factory" value-ref="validator" />
        </map>
    </beans:property>
</beans:bean>

`

Upvotes: 0

Related Questions