16.Spring_使用JdbcTemplate

使用JdbcTemplate来操作数据库。

javaBean

ublic class Admin {
	
	private String  adminNumber;
	
	private String adminPassword;

	
	public String getAdminNumber() {
		return adminNumber;
	}

	public void setAdminNumber(String adminNumber) {
		this.adminNumber = adminNumber;
	}

	public String getAdminPassword() {
		return adminPassword;
	}

	public void setAdminPassword(String adminPassword) {
		this.adminPassword = adminPassword;
	}

	@Override
	public String toString() {
		return "admin [adminNum=" + adminNumber + ", adminPassword="
				+ adminPassword + "]";
	}
	
	
}

资源文件：（使用derby数据库和c3p0数据库连接池）

jdbc.user=a23432745
jdbc.password=a210083
jdbc.driverClass=org.apache.derby.jdbc.ClientDriver
jdbc.jdbcUrl=jdbc:derby://localhost:1527/d:/\syllabusDB

jdbc.initPoolSize=5
jdbc.maxPoolSize=10

Spring配置文件：

<?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:property-placeholder  location="classpath:db.properties" />

<!-- 配置c3p0 -->
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> 
		<property name="user" value="${jdbc.user}"></property>
		<property name="password" value="${jdbc.password}"></property>
		<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
		<property name="driverClass" value="${jdbc.driverClass}"></property>
		
		<property name="initialPoolSize" value="${jdbc.initPoolSize}"></property>
		<property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
	</bean>
	<!-- 配置JdbcTemplate  -->
	<bean id="jdbcTemplate" 
		class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	
	<!-- 配置 NamedParameterJdbcTemplate 对象，该对象可以使用具名参数，其没有无参构造器，所以必须传递参数-->
	<bean id="NamedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
		<constructor-arg ref="dataSource"></constructor-arg>
	 </bean>
</beans>

测试：

public class JDBCTest {
	
	private ApplicationContext ctx= null;
	private JdbcTemplate jdbcTemplate= null;
	
	{
		ctx= new ClassPathXmlApplicationContext("applicationContext.xml");
		jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
		
	}
	}
	
	<span style="font-family: Arial, Helvetica, sans-serif;">/**</span>

	 *  从数据库中获取一条记录，实际得到对应的一个对象
	 *  
	 *  其中RowMapper指定如何去映射结果集的行，
	 *  使用sql中列的别名完成列名和类的属性名的映射
	 *  ,如下admin_password与对象中的adminPassword映射、
	 *  不支持级联属性
	 */
	@Test
	public void testQueryForObject() {
		String sqlString="select admin_number ,admin_password from t_admin where admin_number= ?"; 
		BeanPropertyRowMapper<Admin> rowMapper= new BeanPropertyRowMapper<>(Admin.class);
		Admin admin=jdbcTemplate.queryForObject(sqlString, rowMapper,"a2181745");
		System.out.println(admin);
	}
	/*
	 * 获取单个值，或统计查询
	 * 使用如下方法：
	 * queryForObject(String sql, Class<Long> requiredType)
	 */
	@Test
	public void testQueryForBoject2() {
		String sql="select count(admin_number) from t_admin";
		long count =jdbcTemplate.queryForObject(sql, Long.class);
		System.out.println(count);
		
	}
	
	
	/**
	 * 执行数据库操作
	 */
	@Test
	public void testUpdate() {
		String sqlString = "update T_ADMIN set ADMIN_PASSWORD = ? where  ADMIN_NUMBER= ?";
	//	jdbcTemplate.update(sqlString, "a","a");
		jdbcTemplate.update(sqlString,"11111","a2181745");
	}
	/**
	 * 得到实体类的集合
	 */
	@Test
	public void testQueryForList() {
		 String sql = "select admin_number ,admin_password from t_admin";
		 BeanPropertyRowMapper<Admin> rowMapper=new BeanPropertyRowMapper<>(Admin.class);
		 List<Admin> admins= jdbcTemplate.query(sql, rowMapper);
		 System.out.println(admins);
	}
	
	/**
	 * 批量操作：
	 * 最后一个参数：是Object[]数组的List
	 */
	@Test
	public void testBatchUpdate() {
		String sqlString="insert  into t_admin(admin_number,admin_password) values(?,?)";
		List<Object[]> batchArgs=new ArrayList<Object[]>();
		batchArgs.add(new Object[] {"AA","123"});
		batchArgs.add(new Object[] {"AA2","123"});
		batchArgs.add(new Object[] {"AA3","123"});
		batchArgs.add(new Object[] {"AA4","123"});
		jdbcTemplate.batchUpdate(sqlString, batchArgs);
	}
	
	
	@Test
	public void testDataSource() throws SQLException {
		DataSource dataSource =ctx.getBean(DataSource.class);
		System.out.println(dataSource.getConnection());
	}
}