Should I define datasources in application or in app server?

Question

I've developed applications (running on Jboss server) with two different teams. One team had datasource configuration inside of the application WAR file and another one had it inside of the application server's standalone.xml. And I'm not sure which approach is better.

So, here are some advantages that I've found in defining datasource inside of the server's standalone.xml.

• Defining datasource in server's standalone.xml is more secure than in war file. If the database connection credentials stored in the server's standalone.xml, which is almost never modified, it is safer than having the password inside of the war file, which is often transferred from developer's machines to the server and database configuration is spread by all developers computers.
• By having datasource in standalone.xml, we can make war file smaller, as JDBC driver could be installed as a module and can be removed from war file. In addition, loading JDBC as module is more efficient as from classpath.
• We can put datasource inside of the standalone.xml if we don't want the application development team be aware of database connection settings.

By having datasource configurations in application WAR file, I see the following advantages, which are:

• Development team doesn't have permission to change Jboss configuration files in environment where Jboss is running. So DB connection only can be defined in application.
• It is useful in development state, when developers often need to switch between different database connections. For example, the developer can specify connection when building a WAR file.

So I'd like to know if there are another advantages in both approaches. And on your opinion which approach is better?

Answer 1

In addition to the points noted in the question, another advantage in having the datasources outside the application is that it allows for using the same war file in different regions. This would allow the team to have different databases for different environments, like Test, Perf and Prod while using the same war file.

You can do a deployment once and then the war file which has been tested by your QA team can be promoted to the production region. This would make sure that no untested code goes into higher regions while avoiding the need for SCM forks and code freezes.

Answer 2

I favour having the datasource exposed by the application server with a caveat.. You need your development team to at least know their way around with your application server or to have at least access to the jboss console to review the configuration or change it.. Reason being that for example they need to monitor the connection usage of the datasource connection pool.. Since you're speaking about jboss, I don't know if the "live" bean for the datasource with jboss AS exposes the same information natively of for example oracle ucp (ucp .getStatistics is a godSend for more than one reason..).

Consider that EVEN if you internalize the datasource in xml, using the concept of profiles you can make some field of the xml being "filled" with some specific value in one or another property file based on the profile the application is loaded with..

e.g with spring you can surely do

    <beans profile="local">
    <bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
        <property name="URL" value="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA"/>
        <property name="user" value="myuser_DCI"/>
        <property name="password" value="mypassword_DCI"/>
        <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleConnectionPoolDataSource"/>
        <property name="connectionPoolName" value="${name.connection.pool}"/>
        <property name="minPoolSize" value="5"/>
        <property name="maxPoolSize" value="1000"/>
        <property name="maxIdleTime" value="3000"/>
        <property name="maxStatements" value="3000"/>
        <property name="validateConnectionOnBorrow" value="true" />
        <property name="inactiveConnectionTimeout" value="3000" />
        <property name="connectionWaitTimeout" value="3000"/>
        <property name="abandonedConnectionTimeout" value="3000"/>
        <qualifier value ="dataSourceDCI" />
    </bean>
    <orcl:pooling-datasource id="dataAltSource"
        url="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA" username="some_OWN" password="some_OWN"/>
        <util:properties id="flyway.prop" location="classpath:config_local.properties"/>
</beans>

meaning on local profile load the properties from the config_loca.properties file inside the classpath

and have as well

    <beans profile="qa">
    <bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
        <property name="URL" value="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA"/>
        <property name="user" value="myuserQA_DCI"/>
        <property name="password" value="myuserQA_DCI"/>
        <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleConnectionPoolDataSource"/>
        <property name="connectionPoolName" value="${name.connection.pool}"/>
        <property name="minPoolSize" value="5"/>
        <property name="maxPoolSize" value="1000"/>
        <property name="maxIdleTime" value="3000"/>
        <property name="maxStatements" value="3000"/>
        <property name="validateConnectionOnBorrow" value="true" />
        <property name="inactiveConnectionTimeout" value="3000" />
        <property name="connectionWaitTimeout" value="3000"/>
        <property name="abandonedConnectionTimeout" value="3000"/>
        <qualifier value ="dataSourceDCI" />
    </bean>
    <bean id="dataAltSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
        <property name="URL" value="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA"/>
        <property name="user" value="myuserQA_OWN"/>
        <property name="password" value="myuserQA_OWN"/>
        <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleConnectionPoolDataSource"/>
        <property name="connectionPoolName" value="${name.connection.pool}"/>
        <property name="minPoolSize" value="5"/>
        <property name="maxPoolSize" value="1000"/>
        <property name="maxIdleTime" value="3000"/>
        <property name="maxStatements" value="3000"/>
        <property name="validateConnectionOnBorrow" value="true" />
        <property name="inactiveConnectionTimeout" value="3000" />
        <property name="connectionWaitTimeout" value="3000"/>
        <property name="abandonedConnectionTimeout" value="3000"/>
    </bean>     
    <util:properties id="flyway.prop" location="file:///${prefix.iam.dir}/${filecert.iam.path}/external.properties"/>
</beans>

thus in your QA environment or other non-dev environment you instead refer to the external xml file and not to the one integrated in the war.. You can even include username and password to be "filled" via the internal//external properties file to enhance the security if you're concerned.