SpringJDBCTemplate

配置依赖：

<!--数据库-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.6</version>
</dependency>
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.2</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.3.14.RELEASE</version>
</dependency>

外部文件：

user=root
password=123456
url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8&useSSL=true&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
driver=com.mysql.jdbc.Driver

initPoolSize=5
maxPoolSize=10

配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

    <!--导入资源文件-->
    <context:property-placeholder location="db.properties"/>

    <!--配置c3p0数据源-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${driver}"/>
        <property name="jdbcUrl" value="${url}"/>
        <property name="user" value="${user}"/>
        <property name="password" value="${password}"/>

        <property name="initialPoolSize" value="${initPoolSize}"/>
        <property name="maxPoolSize" value="${maxPoolSize}"/>
    </bean>

    <!--配置Spring的jdbcTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>


</beans>

测试类：

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author chenpeng
 * @date 2018/6/4 10:45
 */
public class JDBCTest {
    private ApplicationContext context = null;
    private JdbcTemplate jdbcTemplate;
    {
        context = new ClassPathXmlApplicationContext("applicationContext.xml");
        jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");

    }

    /**
     * 执行INSERT,UPDATE-----
     */
    @Test
    public void testUpdate(){
        String str = "update t_department set d_name=? where id=?";
        jdbcTemplate.update(str,"实习部",2);
    }

    /**
     * 批量执行INSERT,UPDATE-----
     * 最后一个参数是Object[ ]的 List类型，因为修改一条记录需要一个Object数组，多条就需要多个
     *
     * @throws SQLException
     *
     */
    @Test
    public void updateBatch(){
        String str = "update t_department set d_name=? where id=?";

        List<Object[]> batchArgs = new ArrayList<Object[]>();
        batchArgs.add(new Object[]{"aa",1});
        batchArgs.add(new Object[]{"bb",2});
        jdbcTemplate.batchUpdate(str,batchArgs);
    }

    /**
     * RowMapper:指定如何去映射结果集的行，常用的实现类为BeanPropertyRowMapper
     * 并且使SQL中列的别名完成列名和类属性名的映射，如d_name name
     * 不支持级联属性，到底jdbcTemplate是一个小工具不是ORM框架
     */
    @Test
    public void query(){
        String str = "select id,d_name name from t_department where id=? ";
        RowMapper mapper = new BeanPropertyRowMapper(Department.class);
        List<Department> department = jdbcTemplate.query(str,mapper,1);
        System.out.println(department);
    }



    @Test
    public void testDataSource() throws SQLException {
        DataSource dataSource = (DataSource) context.getBean("dataSource");
        System.out.println(dataSource.getConnection());
    }
}

关于NamedParameterJdbcTemplate

<!--配置NamedParameterJdbcTemplate,该对象可以使用具名参数，其没有无参构造器，所以必须为其构造器指定参数-->
<bean id="NamedParameterJdbcTemplate"
      class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg ref="dataSource"/>
</bean>

/**
 * 可以为参数取名字
 * 好处：有多个参数不必再去对应位置
 * 缺点：麻烦
 */
@Test
public void testNamedParameterJdbcTemplate(){
    String str = "insert into t_department(d_name) values(:name)";
    Map paraMap = new HashMap();
    paraMap.put("name","123456");
    namedParameterJdbcTemplate.update(str,paraMap);
}