DataBase Pooling - Connecting To A Mysql Database In Spring3 MVC

Question

I am trying to connect to a mysql database on my first Spring Project and I seem to be overlooking something really simple.

I have this bean in my application-context.xml file which is commented out!

<bean id="dataSource"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource"
      p:driverClassName="${jdbc.driverClassName}"
      p:url="${jdbc.url}"
      p:username="${jdbc.username}"
      p:password="${jdbc.password}" />

I know this is going to sound stupid, but in order to create a class that connects to a Mysql database using JDBC and the or the Spring JDBCTemplate, what do I do from here?

I am confused about how to populate properties in the bean above, do I do it in a superclass like this and then extend my subclasses from it.

import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class JdbcDao {
     protected DriverManagerDataSource dataSource = new DriverManagerDataSource();
     JdbcDao(){
         dataSource.setDriverClassName("com.mysql.jdbc.Driver");
         dataSource.setUrl("jdbc:mysql://localhost/db_name");
         dataSource.setUsername("bcash");
         dataSource.setPassword("");        
     }    
}

I am confused so any help is truly apprecated,

Many Thanks

Michael · Accepted Answer · 2012-01-23 01:01:10Z

If you are using Tomcat as your application server you can do something more like this.

Define the connection with the user name/password in your server's context.xml file (as opposed to in the application):

<Resource name="jdbc/resourceNameToUse"
          auth="Container"
          type="javax.sql.DataSource"
          username="<UserName>"
          password="<Password>"
          driverName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/db_name"
          maxActive="100"
          maxIdle="5"
          validationQuery="Select 1"
          useCompression="true" />

Then when you configure Spring you create a data source that looks up the resource defined above using JNDI:

  <bean id="targetDataSource" 
        class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" 
              value="java:comp/env/jdbc/resourceNameToUse"/>
  </bean>
  <bean id="dataSource" 
        class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
    <property name="targetDataSource">
      <ref local="targetDataSource"/>
    </property>
  </bean>

At this point you can create an instance of your JdbcTemplate as a Spring bean that references your data source:

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

If you are using Spring 3 you can simply mark JdbcTemplate in your DAO as @AutoWired and Spring will match the variable name to the id of the bean you have defined and inject the JdbcTemplate for you. If you aren't using Spring 3 you can simply inject the JdbcTemplate when you define your DAO bean.

public class MyDao {
    @AutoWired
    private JdbcTemplate jdbcTemplate;
}

Dave Newton · Accepted Answer · 2012-01-22 23:45:03Z

1

See JdbcTemplate Best Practices for further info.

Nutshell: no, your DAO wouldn't create a new datasource via new; that defeats the purpose of using Spring. Implementations should be injected, not instantiated directly; roughly:

public class TheDaoImpl implements TheDao {

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    // JDBC-backed implementations of TheDao follow...
}

Then in your Spring config (if not using annotations), also roughly:

<bean id="theDao" class="com.bar.plugh.TheDaoImpl" p:dataSource-ref="dataSource" />

(This uses a setter; you could also use an annotation and skip the XML config.)

You could also use JdbcTemplate as your base class and save more energy.

I recommend going over the Data access with JDBC reference docs (if you're using JDBC), or the corresponding section that deals directly with your ORM choice.

edited Jan 22, 2012 at 23:45

answered Jan 22, 2012 at 23:38

Dave Newton

161k27 gold badges264 silver badges311 bronze badges

2 Comments

user866190 Over a year ago

Thanks Dave for the answer, I think what I might be asking is how does one configure the dataSource? is that done by filling in the vales in the context-xml file?

Dave Newton Over a year ago

@user866190 Yes, although it looks like it was expecting to use a PropertyPlaceholderConfigurator.

Collectives™ on Stack Overflow

DataBase Pooling - Connecting To A Mysql Database In Spring3 MVC

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related