Spring JDBC Development

Spring is a one-stop framework with solutions for each layer of enterprise java application development, such as at the persistence layer, spring provides JDBC templates and ORM modules to integrate other persistence frameworks.

1. What is JDBC template.

JDBC template are one of the techniques spring provides to simplify JDBC development. Below list show you all the ORM persistence technology and related spring template class.

  1. JDBC : org.springframework.jdbc.core.JdbcTemplate
  2. Hibernate3.0 : org.springframework.orm.hibernate3.HibernateTemplate
  3. IBatis : org.springframework.orm.ibatis.SqlMapClientTemplate
    Support for IBatis has been removed from Spring4, using version 3 of the orm package if desired.
  4. JPA  : org.springframework.orm.jpa.JpaTemplate

2. Spring JDBC Template Usage.

2.1 Create Web Project, Import below jars hilighted.

spring jdbc template jar files

2.2 Create MySql Table.

create table account(
  id int primary key AUTO_INCREMENT,
  user_name varchar(20),
  password varchar(20)
);

2.3 Use JDBC Template.

import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;


public class JDBCTest {
    /**
     * Test Spring JDBC Template.
     */
    @Test
    public void testSpringJDBC() {
        // Create datasource object and connection pool
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        // This datasource use mysql jdbc driver.
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        // Set the mysql server jdbc url.
        dataSource.setUrl("jdbc:mysql://localhost:3306/test");
        // Set mysql server username.
        dataSource.setUsername("root");
        // Set mysql server password.
        dataSource.setPassword("123456");

        // Create Spring JDBC Template.
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        // Set above data source to the JDBC template.
        jdbcTemplate.setDataSource(dataSource);
        // Execute insert sql command use the JDBC template.
        jdbcTemplate.update("insert into account(id, name, money) values(?,?,?)", null, "Switch", 50000d);
    }
}

3. Spring Manage Connection Pools And Templates.

3.1 Configure Spring Built-In Data Source Bean.

<!-- Configure spring built-in 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/test"/>
    <property name="username" value="root" />
    <property name="password" value="123456"/>
</bean>

3.2 Configure JDBC Template Use Above Data Source Bean.

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

4. Configure DBCP’s Connection Pool In Spring Xml.

First add below jar file in spring project.

spring dbcp connection pool dependency jar file

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/test" />
    <property name="username" value="root" />
    <property name="password" value="123456" />
</bean>

5. Configure C3P0 Connection Pool In Spring Xml.

Add below jar file in spring project.

spring c3p0 dependency jar file

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="com.mysql.jdbc.Driver" />
    <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test" />
    <property name="user" value="root" />
    <property name="password" value="123456" />
</bean>

6. Save JDBC Connection Info In Properties File.

6.1 Create db.properties File.

Save below content in db.properties file.

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=123456

6.2 Introduce external properties file in Spring’s configuration file.

There are two methods to import db.properties file into spring context.

6.2.1 Use context tag.
<context:property-placeholder location="classpath:db.properties"/>
6.2.2 Use bean tag.
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="location" value="classpath:db.properties"/>
</bean>

6.3 Configure the connection pool with the key in the properties file.

Configure c3p0 data source use key in db.properties.

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="${jdbc.driverClass}" />
    <property name="jdbcUrl" value="${jdbc.url}" />
    <property name="user" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
</bean>