JdbcTemplate的使用

JdbcTemplate的使用

 

JdbcTemplate和NamedParameterJdbcTemplate

package com.hous.test;

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

import javax.sql.DataSource;

import org.junit.Ignore;
import org.junit.Test;
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 com.alibaba.fastjson.JSON;
import com.hous.bean.UserInfo;
import com.hous.respository.UserInfoDao;

public class JdbcTest {
	
	private ClassPathXmlApplicationContext ctx;
	private JdbcTemplate jdbcTemplate;
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	private UserInfoDao userInfoDao;
	
	{
		ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
		jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
		namedParameterJdbcTemplate = (NamedParameterJdbcTemplate) ctx.getBean("namedParameterJdbcTemplate");
		userInfoDao = (UserInfoDao) ctx.getBean("userInfoDao");
	}
	
	@Test
	public void testUserInfoDao() {
		UserInfo userInfo = userInfoDao.get("105005");
		System.out.println(userInfo);
	}
	
	/**
	 * 执行INSET,UPDATE,DELETE
	 */
	@Test
	@Ignore
	public void testUpdate() throws SQLException {
		String sql = "UPDATE userinfo SET email=? WHERE userid=?";
		jdbcTemplate.update(sql, "[e-mail]", "105002");
	}
	
	/**
	 * 执行INSET,UPDATE,DELETE,使用具名参数
	 */
	@Test
	public void testNamedParameterJdbcTemplate() throws SQLException {
		String sql = "INSERT INTO userinfo(userid, NAME, PASSWORD, email) VALUES(:userid,:name,:password,:email)";
		Map<String, Object> params = new HashMap<>();
		params.put("userid", "105055");
		params.put("name", "王五");
		params.put("password", "555");
		params.put("email", "[e-mail]");
		
		int result = namedParameterJdbcTemplate.update(sql, params);
		System.out.println(result);
	}
	
	/**
	 * 执行INSET,UPDATE,DELETE,使用具名参数,传入对象
	 * 1)sql参数名和对象属性一直
	 */
	@Test
	public void testNamedParameterJdbcTemplate2() throws SQLException {
		String sql = "INSERT INTO userinfo(userid, NAME, PASSWORD, email) VALUES(:userid,:name,:password,:email)";
		UserInfo userInfo = new UserInfo();
		userInfo.setUserid("105066");
		userInfo.setName("赵六");
		userInfo.setPassword("666");
		userInfo.setEmail("[e-mail]");
		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(userInfo);
		int result = namedParameterJdbcTemplate.update(sql, paramSource);
		System.out.println(result);
	}
	
	/**
	 * 执行批量更新:批量的INSET,UPDATE,DELETE
	 * 修改一条记录需要一个Object数组，多天记录就需要多个Object数组
	 */
	@Test
	public void testBatchUpdate() {
		String sql = "INSERT INTO userinfo(userid, NAME, PASSWORD, email) VALUES(?,?,?,?)";
		List<Object[]> batchArgs = new ArrayList<>();
		
		batchArgs.add(new Object[]{"105077", "小七", "777", "[e-mail]"});
		batchArgs.add(new Object[]{"105088", "小七", "888", "[e-mail]"});
		batchArgs.add(new Object[]{"105099", "小七", "999", "[e-mail]"});
		
		jdbcTemplate.batchUpdate(sql, batchArgs);
	}
	
	/**
	 * 从数据库中查询一条记录，返回bean对象
	 * 1）rowMapper为映射的结果行
	 * 2）sql中列名要和属性名映射如user_id
	 * 3）不支持级联的属性查询
	 */
	@Test@Ignore
	public void testQueryForObject(){
		String sql = "SELECT userid, NAME, PASSWORD, email, role FROM userinfo WHERE userid=?";
		RowMapper<UserInfo> rowMapper = new BeanPropertyRowMapper<>(UserInfo.class);
		UserInfo userInfo = jdbcTemplate.queryForObject(sql, rowMapper, "105002");
		System.out.println(userInfo.toString());
	}
	
	/**
	 * 获取单个列的值或统计方法
	 */
	@Test@Ignore
	public void testQueryForObject2(){
		String sql = "SELECT count(*) FROM userinfo";
		Long count = jdbcTemplate.queryForObject(sql, Long.class);
		System.out.println(count);
	}
	
	/**
	 * 查询实体类的集合
	 */
	@Test@Ignore
	public void testQueryForList(){
		String sql = "SELECT userid, NAME, PASSWORD, email, role FROM userinfo WHERE userid like '%11'";
		RowMapper<UserInfo> rowMapper = new BeanPropertyRowMapper<>(UserInfo.class);
		List<UserInfo> userInfos = jdbcTemplate.query(sql, rowMapper);
		System.out.println(userInfos.toString());
	}
	
	@Test@Ignore
	public void testDataSource() throws SQLException {
		DataSource dataSource = ctx.getBean(DataSource.class);
		System.out.println(dataSource.getConnection());
	}
	
}

 

<?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:p="http://www.springframework.org/schema/p"
	xmlns:util="http://www.springframework.org/schema/util"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.2.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd">
	
	<context:property-placeholder location="db.properties" />
	
	<context:component-scan base-package="com.hous" />
	
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="${jdbc.driver}" />
		<property name="jdbcUrl" value="${jdbc.url}" />
		<property name="user" value="${jdbc.user}" />
		<property name="password" value="${jdbc.password}" />
	</bean>
	
	<!-- 配置spring的jdbcTemplate -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<!-- 配置namedParemeterJdbcTemplate，该对象可以使用具名构造参数，其没有无参构造器，必须为构造器指定参数 -->
	<bean id="namedParameterJdbcTemplate" 
		class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
		<constructor-arg ref="dataSource"></constructor-arg>
	</bean>
	
</beans>