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