spring-JdbcTemplate

Spring JDBC模板，1:用具名参数的NamedParameterJdbcTemplate

                              2:非具名参数的JdbcTemplate

db.properties:

user=root
password=zeng
driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/cwhtest?useUnicode=true&characterEncoding=UTF-8

maxPoolSize=20
initialPoolSize=5

applicationContext.xml:

<?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-4.0.xsd">

    <context:component-scan base-package="com.cwh.spring.jdbc"></context:component-scan>

    <context:property-placeholder location="classpath:db.properties" />
    <!-- c3p0数据源 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="user" value="${user}"></property>
        <property name="password" value="${password}"></property>
        <property name="driverClass" value="${driverClass}"></property>
        <property name="jdbcUrl" value="${jdbcUrl}"></property>

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

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

    <!-- 配置 NamedParameterJdbcTemplate ,该类没有无参构造器，所以必须传一个datasource.比起上面那个，该对象可以使用具名参数-->
    <bean id="nameJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="dataSource"></constructor-arg>
    </bean>
</beans>

实体类：

package com.cwh.spring.jdbc;

public class Goods {

    private Integer id;
    private String type;
    private String name;
    private String weigh;
    private String season;

    public String getSeason() {
        return season;
    }
    public void setSeason(String season) {
        this.season = season;
    }
    public String getType() {
        return type;
    }
    public void setType(String type) {
        this.type = type;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getWeigh() {
        return weigh;
    }
    public void setWeigh(String weigh) {
        this.weigh = weigh;
    }
    [@Override](https://my.oschina.net/u/1162528)
    public String toString() {
        return "Goods [id=" + id + ", type=" + type + ", name=" + name + ", weigh=" + weigh + ", season=" + season
                + "]";
    }
}

实际开发常用的DAO：

package com.cwh.spring.jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

[@Repository](https://my.oschina.net/u/3055569)
public class GoodsDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public Goods getObject(int id){
        String sql = "select * from Goods where id=?";
        RowMapper<Goods> rowMapper = new BeanPropertyRowMapper<>(Goods.class);
        Goods goods = jdbcTemplate.queryForObject(sql, rowMapper, id);

        return goods;
    }
}

测试类：

package com.cwh.spring.jdbc;

import static org.junit.Assert.*;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.print.attribute.HashAttributeSet;
import javax.sql.DataSource;

import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
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 org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Component;

//@Component
public class JdbcTest {

    private ApplicationContext context = null;
    private JdbcTemplate jdbcTemplate = null;
    private GoodsDao goodsDao = null;
    private NamedParameterJdbcTemplate namedJdbcTemplate = null;
//  @Autowired 此处为测试类，单单运行一个方法，无法让这里的注解生效（猜测）
//  private GoodsDao goodsDao;
//  @Autowired
//  private JdbcTemplate jdbcTemplate;
    {
        context = new ClassPathXmlApplicationContext("applicationContext.xml");
        jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");
        goodsDao = (GoodsDao) context.getBean("goodsDao");
        namedJdbcTemplate = context.getBean(NamedParameterJdbcTemplate.class);
    }

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

    @Test
    public void test2() {
        //更新
//      String sql1 = "update goods set type=? where id=?";
//      System.out.println(template.update(sql1, "apple", 2));
        //插入
//      String sql2 = "insert into goods values(?,?,?,?,?)";
//      System.out.println(template.update(sql2, 3, "snake3", "bigger", "100", "summer"));
        //批量插入
        String sql3 = "insert into goods(type, name, weigh, season) values(?,?,?,?)";
        List<Object[]> list = new ArrayList<>();
        list.add(new Object[]{"snake3", "bigger", "140", "summer"});
        list.add(new Object[]{"snake4", "bigger", "130", "summer"});
        list.add(new Object[]{"snake5", "bigger", "120", "summer"});
        list.add(new Object[]{"snake6", "bigger", "110", "summer"});
        System.out.println(jdbcTemplate.batchUpdate(sql3, list));
    }

    /**
     * 查询：JDBCTemplate只是一个jdbc的一个工具，不是ORM框架，
     * 不支持级联查询
     */
    @Test
    public void test3(){
        //查询,返回的是一个对象
        String sql = "select * from goods where id = ?";
        RowMapper<Goods> rowMapper = new BeanPropertyRowMapper<>(Goods.class);
        Goods goods = jdbcTemplate.queryForObject(sql, rowMapper, 2);
        System.out.println(goods);
    }

    @Test
    public void test4(){
        //批量查询
        String sql = "select * from goods where id > ?";
        RowMapper<Goods> rowMapper = new BeanPropertyRowMapper<>(Goods.class);
        List<Goods> goods = jdbcTemplate.query(sql, rowMapper,9);
        System.out.println(goods);
    }

    @Test
    public void test5(){
        //查询单个属性或者统计查询
        String sql = "select count(id) from goods";
        int count = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(count);
    }

    @Test
    public void test6(){
        //实际开发环境的DAO层常用的方式
        System.out.println(goodsDao.getObject(5));
    }

    @Test
    public void test7(){
        //namedJdbcTemplate提供的具参语句
        String sql = "insert into Goods(type, name, weigh, season) values(:type, :name, :weigh, :season)";
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("type", "snake7");
        paramMap.put("name", "small");
        paramMap.put("weigh", "112");
        paramMap.put("season", "autumn");
        int a = namedJdbcTemplate.update(sql, paramMap);
        System.out.println(a);
    }

    @Test
    public void test8(){
        //类似hibernate的save方法入参为一整个对象
        String sql = "insert into Goods(type, name, weigh, season) values(:type, :name, :weigh, :season)";
        Goods goods = new Goods();
        goods.setType("snake8");
        goods.setName("small2");
        goods.setWeigh("113");
        goods.setSeason("winter");
        SqlParameterSource paramSource = new BeanPropertySqlParameterSource(goods);
        int a = namedJdbcTemplate.update(sql, paramSource);
        System.out.println("插入数目：" + a);
    }
}

转载于:https://my.oschina.net/u/3780366/blog/1833614