Spring NamedParameterJdbcTemplate完成数据的增删改查

1、Spring配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" 
	xmlns:p="http://www.springframework.org/schema/p"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:aop="http://www.springframework.org/schema/aop" 
    xmlns:context="http://www.springframework.org/schema/context" 
    xmlns:jdbc="http://www.springframework.org/schema/jdbc" 
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:jpa="http://www.springframework.org/schema/data/jpa"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
        http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa.xsd"                                        
        default-lazy-init="true">
        
        <!-- 将com.spring.data包内的bean注入 -->
        <context:component-scan base-package="com.spring.data"></context:component-scan>
        <!-- 引入外部资源文件 -->
        <context:property-placeholder location="classpath:application.properties"/>
        <!-- 启用pring对jdbc的封装 -->
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        	<constructor-arg ref="dataSource"/>
        </bean>
        <!-- 配置数据源 -->
        <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
           	<!-- 数据驱动 -->
            <property name="driverClass">
                <value>${jdbc.driver}</value>
            </property>
            <!-- 数据库连接 -->
            <property name="jdbcUrl">
                <value>${jdbc.url}</value>
            </property>
            <!-- 数据库用户名 -->
            <property name="user">
                <value>${jdbc.username}</value>
            </property>
            <!-- 数据库密码 -->
            <property name="password">
                <value>${jdbc.password}</value>
            </property>
             <!--连接池中保留的最大连接数。Default: 15 -->    
            <property name="maxPoolSize" value="${c3p0.maxPoolSize}"/>  
            <!--连接池中保留的最小连接数-->    
            <property name="minPoolSize" value="${c3p0.minPoolSize}"/>  
            <!--初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default: 3 -->   
            <property name="initialPoolSize" value="${c3p0.initialPoolSize}"/>  
            <!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->    
            <property name="maxIdleTime" value="${c3p0.maxIdleTime}" />  
             <!--当连接池用完时客户端调用getConnection()后等待获取新连接的时间，超时后将抛出    
    			SQLException,如设为0则无限期等待。单位毫秒。Default: 0 -->    
            <property name="checkoutTimeout" value="${c3p0.checkoutTimeout}"/> 
            <!--两次连接中间隔时间，单位毫秒。Default: 1000 -->    
            <property name="acquireIncrement" value="${c3p0.acquireIncrement}"/>  
            <!--每60秒检查所有连接池中的空闲连接。Default: 0 -->    
            <property name="idleConnectionTestPeriod" value="${c3p0.idleConnectionTestPeriod}"/> 
            <!--JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements    
				    属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。    
				    如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default: 0-->    
            <property name="maxStatements" value="${c3p0.maxStatements}"/>   
        </bean>  
</beans>

2、持久类操作数据库代码

package com.spring.data;

import java.util.HashMap;
import java.util.Map;

import javax.annotation.Resource;

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.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;


@Repository
public class JdbcSpitterDao {
	
	@Resource
	private NamedParameterJdbcTemplate jdbcTemplate;
	//插入数据的sql
	private static final String ADD_SQL="insert into tbl_c3p0(name,address,age) values(:name,:address,:age)";
	//根据id获取user对象
	private static final String QUERY_SQL="select id,name,address,age from tbl_c3p0 where id=:id";
	//修改user对象
	private static final String UPDATE_SQL="update tbl_c3p0 set name=:name,address=:address,age=:age where id=:id";
	//根据id删除user对象
	private static final String DELETE_SQL="delete from tbl_c3p0 where id=:id";
	/**
	 * 插入数据
	 */
	public void add(User user){
	    Map<String,Object> paramMap = new HashMap<>();
	    paramMap.put("name", user.getName());
	    paramMap.put("address", user.getAddress());
	    paramMap.put("age", user.getAge());
		jdbcTemplate.update(ADD_SQL, paramMap);
	}
	/**
	 * 获取User对象
	 */
	public User queryById(int id){
	    Map<String,Integer> paramMap = new HashMap<>();
	    paramMap.put("id", id);
	    RowMapper<User> rm = ParameterizedBeanPropertyRowMapper.newInstance(User.class);
            return jdbcTemplate.queryForObject(QUERY_SQL, paramMap, rm);
	}
	/**
	 * 根据User对象进行查找
	 */
	public User queryByUser(User user){
		SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
		RowMapper<User> rm = ParameterizedBeanPropertyRowMapper.newInstance(User.class);
		return jdbcTemplate.queryForObject(QUERY_SQL, ps, rm);
	}
	/**
	 * 根据id修改user对象
	 */
	public void update(User user){
		SqlParameterSource ps=new BeanPropertySqlParameterSource(user);
		jdbcTemplate.update(UPDATE_SQL, ps);
	}
	/**
	 * 根据id删除user对象
	 */
	public void delete(int id){
		Map<String,Object> paramMap = new HashMap<>();
	    paramMap.put("id", id);
		jdbcTemplate.update(DELETE_SQL, paramMap);
	}
}

3、注意事项

在queryById方法中如果这样写：

public User queryById(int id){
		Map<String,Integer> paramMap = new HashMap<>();
	    paramMap.put("id", id);
		return jdbcTemplate.queryForObject(QUERY_SQL, paramMap, User.class);
	}

就会报：

这样的问题