Setup database connection with tomcat 7

Question

I have a working programm with MySQL, Spring and a tomcat server, but the database connection is in spring-database.xml:

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

    <bean id="baseController" class="com.afterguard.sailplanner.controller.BaseController">
        <property name="dao" ref="daoImpl" />
    </bean>

    <bean id="daoImpl" class="com.afterguard.sailplanner.dao.DaoImpl">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">

        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/sailplanner" />
        <property name="username" value="sailplanner" />
        <property name="password" value="sailplanner2" />
    </bean>

</beans>

In order to deploy this program on a server now, I would need bring this database connection to the server, e.g. the context.xml oder server.xml.

How do I do I setup my tomcat 7 server to connect to the database and tell my application, that the database connection is setup in the tomcat?

What you want its not clear ,Please clarify your requirement. — Pawan Pandey, Dec 21 '15 at 14:29
What do you mean by bringing the database connection to the server? Create a database connection on it or just deploy the XML file with your application? — Kalle Richter, Dec 21 '15 at 14:31
what ever i have understood from your question you want to use spring-database.xml with your applicationContext.xml file if yes then you should write this code in applicationContext.xml file — Pawan Pandey, Dec 21 '15 at 14:36
Create a database connection on the server. Currently the database connection is in the spring-server.xml of the WEB-INF folder But when deploying the application on the server, I need a connection to a different database and database connection should be kept in the server and not in the application anyhow. — DonMarco, Dec 21 '15 at 14:36
@Padwan Pandey: There is no applicationContext.xml on the server. — DonMarco, Dec 21 '15 at 14:38
the database connection properties you should put in a property file and and depending upon your requirement you can change values in property file — Pawan Pandey, Dec 21 '15 at 14:41
Possible duplicate of [How to use JNDI DataSource provided by Tomcat in Spring?](http://stackoverflow.com/questions/9183321/how-to-use-jndi-datasource-provided-by-tomcat-in-spring) — Bacteria, Dec 21 '15 at 15:38

score 2 · Accepted Answer · answered Dec 21 '15 at 15:03

You have to create a dataSource with JNDI :

First, in your spring bean configuration (spring-database.xml) add this :

 <beans:bean id="dbDataSource"  class="org.springframework.jndi.JndiObjectFactoryBean">
    <beans:property name="jndiName" value="java:comp/env/jdbc/myDB"/>
</beans:bean>

Second, create the jndi resource in the file apache-tomcat/conf/server.xml :

<Resource name="jdbc/globalDB"
  global="jdbc/globalDB"
  auth="Container"
  type="javax.sql.DataSource"
  driverClassName="com.mysql.jdbc.Driver"
  url="jdbc:mysql://localhost:3306/sailplanner"
  username="sailplanner"
  password="sailplanner2"/>

Finally, create the resource link in the file apache-tomcat/conf/context.xml :

<ResourceLink name="jdbc/myDB"
                global="jdbc/globalDB"
                auth="Container"
                type="javax.sql.DataSource" />

Thanks. Exactly what I was looking for. Worked out of the box. — DonMarco, Dec 22 '15 at 00:07

score 1 · Answer 2 · edited Dec 28 '15 at 07:54

For Sql Server I do the following configuration:

You must be using Tomcat to manage connections. To do so we will be adding data sources to the TOMCAT_HOME/conf/context.xml file

<Context>
<Resource
    name="jdbc/Oltp1"
    auth="Container"
    type="javax.sql.DataSource"
    driverClassName="net.sourceforge.jtds.jdbc.Driver"
    url="jdbc:jtds:sqlserver://localhost:3306/sailplanner"
    username=user
    password=password
    testOnBorrow="true"
    testOnConnect="true"
    validationQuery="SELECT 1"
    removeAbandoned="true"
    removeAbandonedTimeout="30"
    />
</Context>

SQL Server JDBC driver (jtds-version.jar) must be copied to the TOMCAT_HOME/lib folder (if it is not there already)
If there are multiple data sources (OLTP1, OLTP2, etc) we will be adding multiple <Resource/> tags

You will be using the following JNDI names to name our data sources:

jdbc/Oltp1 – main OLTP database
jdbc/Oltp2 – secondary database

You will be configuring data sources so connection pool will know how to test connections and what to do with closed connections:

testOnBorrow="true"
    testOnConnect="true"
    validationQuery="SELECT 1"
    removeAbandoned="true"
    removeAbandonedTimeout="30"

Tomcat must be restarted when new resource is added or existing resource is modified.

application.properties file

Important! This file is deployed as a part of the classpath (src/main/resources/application.properties), not the external application.properties with externalized properties deployed to the TOMCAT_HOME/conf

You will be added the following property to this file:

spring.datasource.jndi-name=java:comp/env/jdbc/Oltp1

You need to remove all other spring.datasource properties from the application.properties if any exist
Important! You had to remove all spring.datasource properties from the external configuration file (application.properties deployed to the COMCAT_HOME/conf)

score 0 · Answer 3 · answered Dec 21 '15 at 14:45

the database connection properties you should put in a property file and and depending upon your requirement you can change values in property file

        <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClass" value="${jdbc.driverClassName}" />
        <property name="jdbcUrl" value="${jdbc.databaseurl}" />
        <property name="user" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />

    </bean>

the property file

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.databaseurl=jdbc:mysql://localhost:3306/sailplanner

jdbc.username=root
jdbc.password=root

and to include this file in xml use

<bean id="propertyConfigurer"
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="ignoreUnresolvablePlaceholders" value="true" />
        <property name="locations">
            <list>
                <value>classpath:applications.properties</value> 

            </list>
        </property>
    </bean>

As I am using OpenShift I like to put the database connection into the context.xml of the tomcat and not have it in the application. — DonMarco, Dec 21 '15 at 14:57

Setup database connection with tomcat 7

3 Answers3