Spring provides a powerful template class -- JDBC template to simplify JDBC operations. The data source DataSource object and the template JDBC template object can be defined in the configuration file in the form of Bean, giving full play to the power of dependency injection.

Case: use JDBC template to query all books

1. Introduce jar package

<!-- MySQLjar -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>


 <!--spring jdbc-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.2.0.RELEASE</version>
        </dependency>

2. create class

3. Configure xml file

3.1 configure data source

The first matching method

<! -- data source -- >
    <!--
        1.spring default configuration
        2.dbcp register data source
        3.c3p0 registered data source
        4.druid registration data source
    -->
    <! -- spring's own data source -- >
   <bean id="dataSources" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
       <property name="driverClassName" value="${jdbc.driver}"/>
       <property name="url" value="${jdbc.url}"/>
       <property name="username" value="${jdbc.username}"/>
       <property name="password" value="${jdbc.password}"/>
   </bean>

The second method

<!--dbcp Register data source-->
    <bean id="dataSources" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

The third method

<!--c3p0 Configure data sources-->
    <bean id="dataSources" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

The fourth method

<!--druid Configure data sources-->
    <bean id="dataSources" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

Note: in addition to the first method, several other methods need to introduce dependency

<!--dbcp-->
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>

<!--c3p0-->
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>

<!--druid Ali, it's popular now-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.29</version>
        </dependency>

3.2 identify jdbc.properties file

<!--Distinguish jdbc.properties file-->
    <!--One way-->
    <context:property-placeholder location="classpath:jdbc.properties"/>

    <!--Mode two-->
    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location" value="classpath:jdbc.properties"></property>
    </bean>

3.3 binding data source

<!--Bind data source-->
    <bean id="jdbcTemplates" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSources"/>
    </bean>

3.4 binding DAO layer implementation class

<!--DAO-->
    <bean id="BookDao" class="cn.happy.JDBCTemplate.dao.BookDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplates"/>
    </bean>

3.5 binding Service layer implementation class

<!--service-->
    <bean id="BookService" class="cn.happy.JDBCTemplate.service.BookServiceImpl">
<! -- create an object in serviceImpl and encapsulate the get set method -- >
         <property name="dao" ref="BookDao"/> 
    </bean>

4. Writing method

import cn.happy.JDBCTemplate.entity.Book;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by Administrator on 2018/3/13.
 */
//Inherit the JdbcDaoSupport class
public class BookDaoImpl extends JdbcDaoSupport implements IBookDao {
    public List<Book> getAll() {
　　　　　//Writing SQL
        String sql="select * from book";
　　　　　//Call the get method of the JdbcTemplate property of the JdbcDaoSupport class
        List<Book> query = this.getJdbcTemplate().query(sql, new RowMapper<Book>() {
            public Book mapRow(ResultSet rs, int i) throws SQLException {
                /*
                rs Reader
                i  Indexes
                 */
　　　　　　　　　　//create object
                Book book = new Book();
　　　　　　　　　　//Render data
                book.setBookId(rs.getInt("bookId"));
                book.setBookName(rs.getString("bookName"));
                book.setBookPrice(rs.getInt("bookPrice"));
                return book;
            }
        });
        return query;
    }
}

5. test class

//JDBCTemplate
    @Test
    public void Spring(){
        ApplicationContext ctx=new ClassPathXmlApplicationContext("JDBCTemplate.xml");
        BookService service=(BookService)ctx.getBean("BookService");
        List<Book> all = service.getAll();
        for (Book item:all) {
            System.out.println(item.getBookName());
        }
    }

Result: