18、(知识篇)Spring使用JDBCTemplate/JDBCDaoSupport/具名参数

测试类：

package com.spring.test;

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

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.spring.dao.UserDao;
import com.spring.dao.UserDao2;
import com.spring.vo.User;

public class Test {
	
	/**
	 * Spring JdbcTemplate/JdbcDaoSupport
	 *  建议使用JdbcTemplate，因为JdbcDaoSuport其实调用的还是jdbctemplate的方法
	 *  只是集成 JDBCDaoSupport 需要再次DI 它的DataSource或者jdbctemplate
	 *  步骤：
	 *  1、在spring的xml中，配置jdbctemplate，依赖于数据源，所以需要见一个数据源bean
	 *  <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"></property> </bean>
	 *  
	 *  2、通过注入的jdbctemplate做增删改查操作
	 *   2.1 增删改：jdbcTemplate.update(sql,args); (PS:批量增加使用 jdbcTemplate.update(sql,List<Object[]>);)
	 *   2.2 查找返回一个值 采用queryForObject(String sql, Class<?> requiredType) 例如返回几多条记录，会用到这个
	 *   2.3 查找返回一个对象  采用queryForObject(String sql, RowMapper<User> rowMapper) 需要传入RowMapper对象 new BeanPropertyRowMapper<>(Class<?>);
	 *   2.4 获取多个对象 采用query(String sql, RowMapper<Class<?>> rowMapper) 需要传入RowMapper对象 RowMapper<User> userMapper = new BeanPropertyRowMapper<>(User.class);
	 * 
	 *  3、使用JdbcDaoSupport
	 * 	 3.1 使用类集成JdbcDaoSupport
	 *   3.2 需要额外注入datasource或者jdbctemplate，可以参考userdao2;
	 *  
	 *  4、额外知识（使用具名参数 NamedParameterJdbcTemplate）
	 *   4.1 NamedParameterJdbcTemplate 配置bean时需要用构造方法传入datasource
	 *   4.2 将我们平时用？问号的占位符 改成冒号 （:字段名） 的形式做占位符
	 *   4.3 用map 键值的形式传入参数（这个日后用bean来设置将非常方便） 
	 * @param args
	 */
	
	public static void main(String[] args) {
		
		ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
		
		UserDao userDao = ctx.getBean(UserDao.class);
		UserDao2 userDao2 = ctx.getBean(UserDao2.class);
		
		//增加
		//int row = userDao.addUser(new User(0, "Flash", 2));
		//System.out.println("row == "+row);
		
		
		//修改
		int row = userDao.updateUser(new User(2, "Flash", 1));
		
		
		//返回一个列值
		long count = userDao.getUserCount();
		System.out.println("count == "+count);
		
		//查找一个对象
		User user = userDao.getUserById(1);
		System.out.println("user == "+user);
		
		//查找多个对象
		List<User> lists = userDao.getUsers();
		System.out.println("lists == "+lists);
		
		
		
		//======JdbcDaoSupport===========
		
		User user2 = userDao2.getUserById(2);
		System.out.println(user2);
		
		
		//==========NamedParameterJDBCTemplate=====================
		Map<String,Object> paramMap = new HashMap<>();
		paramMap.put("userId", 2);
		User user3 = userDao.getUserByNamedParameter(paramMap);
		System.out.println(user3);
	}
}
           

dao类（使用template）：

package com.spring.dao;


import java.util.List;
import java.util.Map;

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

import com.spring.vo.User;

@Repository
public class UserDao {
	
	@Autowired
	private JdbcTemplate template;
	
	/**
	 * 增加
	 * @param user
	 * @return
	 */
	public int addUser(User user){
		String sql = "insert into User(userName,sex) values(?,?)";
		
		return template.update(sql, user.getUserName(),user.getSex());
	}
	
	/**
	 * 更新
	 * @param user
	 * @return
	 */
	public int updateUser(User user){
		String sql = " update User set userName = ? ,sex = ? where id = ? ";
		return template.update(sql, user.getUserName(),user.getSex(),user.getId());
	}
	
	/**
	 *  查找返回一个值
	 * 采用queryForObject(String sql, Class<?> requiredType) 
	 * @return
	 */
	public long getUserCount(){
		String sql = " select count(1) from User ";
		return template.queryForObject(sql,long.class);
	}
	
	/**
	 * 查找返回一个对象
	 * 采用queryForObject(String sql, RowMapper<User> rowMapper)
	 * 需要传入RowMapper对象
	 * @param userId
	 * @return
	 */
	public User getUserById(int userId){
		String sql = " select * from User where id = ? ";
		RowMapper<User> userMapper = new BeanPropertyRowMapper<>(User.class);
		return template.queryForObject(sql, userMapper,userId);
	}
	
	/**
	 * 获取多个对象
	 * 采用query(String sql, RowMapper<User> rowMapper) 
	 * 需要传入RowMapper对象
	 * RowMapper<User> userMapper = new BeanPropertyRowMapper<>(User.class);
	 * @return
	 */
	public List<User> getUsers(){
		String sql = " select * from User ";
		RowMapper<User> userMapper = new BeanPropertyRowMapper<>(User.class);
		return template.query(sql, userMapper);
	}
	
	
	
	
	
	//==========使用具名参数========================
	
	@Autowired
	private NamedParameterJdbcTemplate nameTemplate;
	
	public User getUserByNamedParameter(Map<String,Object> paramMap){
		String sql = " select * from User where id = :userId ";
		
		return nameTemplate.queryForObject(sql, paramMap, new BeanPropertyRowMapper<>(User.class));
	}
	
	
}
           

package com.spring.dao;

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

import com.spring.vo.User;

@Repository
public class UserDao2 extends JdbcDaoSupport{

	@Autowired
	public void setTemplate(JdbcTemplate template) {
		setJdbcTemplate(template);
	}

	
	public User getUserById(int userId){
		String sql = " select * from User where id = ? ";
		return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<>(User.class), userId);
	}
	
}
           

实体类：

package com.spring.vo;

import org.springframework.stereotype.Component;

@Component
public class User {
	private int id;
	private String userName;
	private int sex;
	public User() {
		super();
		// TODO Auto-generated constructor stub
	}
	public User(int id, String userName, int sex) {
		super();
		this.id = id;
		this.userName = userName;
		this.sex = sex;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public int getSex() {
		return sex;
	}
	public void setSex(int sex) {
		this.sex = sex;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", userName=" + userName + ", sex=" + sex + "]";
	}
	
}
           

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"
	xmlns:util="http://www.springframework.org/schema/util"
	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.3.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.3.xsd">
	
	<context:component-scan base-package="com.spring"></context:component-scan>
	<!-- 指定数据源配置properties -->
	<context:property-placeholder location="classpath:db.properties"/>
	
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="${driverClass}" ></property>
		<property name="jdbcUrl" value="${url}" ></property>
		<property name="user" value="${userName}" ></property>
		<property name="password" value="${password}"></property>
	</bean>
	
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<!-- 让sql支持具名参数 -->
	<bean id="nameTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
		<constructor-arg name="dataSource" ref="dataSource" ></constructor-arg>
	</bean>
	
</beans>
           

db.properties

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/Test
userName=root
password=root