Mysql + Tomcat Datasource best practice

[pg]

This is a very basic question but I cannot seem to find a definitive answer.

What is the best way to configure a single datasource in Spring to a single mysql database (located on another machine) with tomcat.

In my test setup I have mysql database running on the same machine and have just a standard datasource setup in my app-servlet.xml as follows:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName"><value>com.mysql.jdbc.Drive r</value></property>
<property name="url"><value>jdbc:mysql://localhost:3306/app</value></property>
<property name="username"><value>user</value></property>
<property name="password"><value>passwd</value></property>
</bean>

and it works fine.

Will this be good enough for a production environment?

[cmgharris]

If you find you have performance issues, you could try using a datasource with pooled connections.
See http://forum.springframework.org/showthread.php?t=10772

[pg]

Thanks Chris,

This works fine also, I will probably use this on the production machine.

I assume there is no need to configure the datasource external to the app-servlet.xml as was required in later versions of struts though (i.e. placing the database connection in $CATALINA_HOME/conf/server.xml).

[cmgharris]

If you have the datasource configured in the app-servlet.xml, it will only be available to applications that have access to that spring context.
If this one app is the only one that needs this datasource, that should be fine.

I use jboss/tomcat, so I'm not familiar with the configuration of just tomcat. I'm guessing that 'placing the database connection in $CATALINA_HOME/conf/server.xml' makes it available to any tomcat app. If that's the case, it would depend on whether you need that functionality. And in that case, I don't know how you'd make a pooled datasource generally available.

[pg]

It's only required by this particular spring app.

Thanks Chris

[irbouho]

$CATALINA_HOME/conf/server.xml supports configuring both application level datasource (inside a <Context>) and server wide datasource (as a global resource)
HTH

[pg]

I am interested in reliability / performance, from this stand point, is there any disadvantage in setting the datasource up in the app-servlet.xml file.

[Rod Johnson]

I am interested in reliability / performance, from this stand point, is there any disadvantage in setting the datasource up in the app-servlet.xml file.

You mean setting up a local datasource such as a Commons DBCP datasource, rather than a reference (also in your Spring application context) to a container-managed JNDI datasource?

If you aren't using a full application server, but just a servlet engine like Tomcat, using a JNDI DataSource really buys you only consistent J2EE conventions for looking it up, and a container-wide place to define datasources. If you're using Spring you don't need to write the lookup code, so the first point doesn't matter much; the second one is no big deal either.

In the case of a true app server, using a container datasource is necessary if you want global transactions managed using JTA (and driven by either EJB CMT, JTA usage or Spring transaction management).

If you're not using JTA (and with Spring you don't need to unless you need transactions distributed across multiple transactional resources), there's no real reliability/performance advantage in using a datasource such as Commons DBCP configured fully in your Spring context. Commons DBCP does pool connections, as do C3PO, Proxol and other competing products.

[pg]

Thanks Rod, and everyone else who responded.