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 the JDBC template.

1. JDBC template is one of the techniques spring provides to simplify JDBC development.
2. The below list shows you all the ORM persistence technology and related spring template class.
3. JDBC: org.springframework.jdbc.core.JdbcTemplate
4. Hibernate3.0: org.springframework.orm.hibernate3.HibernateTemplate
5. IBatis: org.springframework.orm.ibatis.SqlMapClientTemplate, this template that supports for IBatis has been removed from Spring4, using version 3 of the ORM package if desired.
6. JPA: org.springframework.orm.jpa.JpaTemplate

2. Spring JDBC Template Usage.

2.1 Create Web Project, Import below jars.

1. mysql-connector-java-5.1.7-bin.jar
2. spring-jdbc-4.2.4.RELEASE.jar
3. spring-tx-4.2.4.RELEASE.jar

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.

1. First, add the below jar files to the spring project.

   com.springsource.org.apache.commons.dbcp-1.2.2.osgi.jar
   
   com.springsource.org.apache.commons.pool-1.5.3.jar

2. Below is the configuration XML data.

   <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.

1. Add below jar file in spring project.

   com.springsource.com.mchange.v2.c3p0-0.9.1.2.jar

2. Below is the configuration XML data.

   <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.

1. Save below content in the 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 the db.properties file into the 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.

1. 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>