spring4 - 9 - JdbcTemplate

·····································JdbcTemplate··································

为了使 JDBC 更加易于使用, Spring 在 JDBC API 上定义了一个抽象层, 以此建立一个 JDBC 存取框架.
作为 Spring JDBC 框架的核心, JDBC 模板的设计目的是为不同类型的 JDBC 操作提供模板方法. 
每个模板方法都能控制整个过程, 并允许覆盖过程中的特定任务. 通过这种方式, 可以在尽可能保留灵活性的情况下, 
将数据库存取的工作量降到最低.

············································code····································

（jdbctest.java)····································································jdbctest.java

class jdbcTest {
	private ApplicationContext app = null;
	private JdbcTemplate tem = null;
	{
		app = new ClassPathXmlApplicationContext("ApplicationContext.xml");
		tem = (JdbcTemplate) app.getBean("jdbcTemplate");
	}

	/*
	 *   执行 insert update delete
	 */
	@Test
	public void testUpdate() {
		String sql = "UPDATE apartments SET name = ? WHERE id = ?";
		tem.update(sql, "jjjjjj",1);
	}
	
	/*
	 * 执行批量insert update delete
	 * 最后一个参数值Object【】类型的List：因为修改一条 记录需要一个Object数组
	 */
	@Test
	public void testBatchUpdate() {
		String sql = "INSERT INTO apartments(name, id) VALUES(?,?)";
		List<Object[]> batchArgs = new ArrayList<>();
		batchArgs.add(new Object[] {"aaa",6});
		batchArgs.add(new Object[] {"bbb",2});
		batchArgs.add(new Object[] {"ccc",3});
		batchArgs.add(new Object[] {"ddd",4});
		batchArgs.add(new Object[] {"eee",5});
		tem.batchUpdate(sql, batchArgs);
	}
	
	/*
	 * 从数据库中获取一条记录，实际得到对应的一个对象： queryForObject(String sql, RowMapper<newTable> rowMapper, Object... args)
	 * 1.其中的Row Mapper指定如何去映射结果集的行，常用的实现类为BeanPropertyRowMapper
	 * 2.使用SQL 中的列的别名完成列名于类的属性名的映射，例如：name name
	 * 3.不支持级联属性，jdbctemplate到底是一个JDBC小框架，而不是ORM框架
	 */
	@Test
	public void testQueryForObject() {
		String sql = "SELECT id, name name, apart FROM new_table WHERE id = ?";
		RowMapper<newTable> rowMapper = new BeanPropertyRowMapper<>(newTable.class);
		newTable  newtable = tem.queryForObject(sql, rowMapper, 1);
		
		System.out.println("yes");
		System.out.println(newtable);
	}
	
	/*
	 * 获取单列的值，或做统计查询
	 * 使用queryForObject(String sql, Class<Long> requiredType)方法
	 */
	@Test
	public void testQueryForObject2() {
		String sql = "SELECT count(id) FROM new_table";
		long count = tem.queryForObject(sql, Long.class);
		System.out.println(count);
	}
	
	/*
	 * 查询实体类的集合（查询多个数据）
	 * 注意调用的不是queryforlist方法
	 */
	@Test
	public void testQueryForList() {
		String sql = "SELECT id, name name, apart apart FROM new_table WHERE id > ?";
		RowMapper rowMapper = new BeanPropertyRowMapper<>(newTable.class);
		List<newTable> tab = tem.query(sql, rowMapper, 1);
		System.out.println(tab);
	}
	
	//打印连接状态
	@Test
	void test() throws SQLException {
		DataSource datasource = (DataSource) app.getBean("dataSource");
		
		System.out.println(datasource.getConnection());
	}
}



（db.properties)··························································db.properties
jdbc.user = root
jdbc.password = root
jdbc.driverClass = com.mysql.jdbc.Driver
jdbc.jdbcUrl = jdbc:mysql:///spring4
jdbc.initPoolSize = 5
jdbc.maxPoolSize = 10




（applicationContext.xml)················································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"
	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="driverClass" value = "${jdbc.driverClass}"></property>
		<property name="jdbcUrl" value = "${jdbc.jdbcUrl}"></property>
		<property name="initialPoolSize" value = "${jdbc.initPoolSize}"></property>
		<property name="maxPoolSize" value = "${jdbc.maxPoolSize}"></property>
	</bean>
	
	<!-- 配置spring 的JdbcTemplate -->
	<bean id = "jdbcTemplate" class = "org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref = "dataSource"></property>
	</bean>
</beans>



（newTable.java)·······················································newTable.java

package jdbc;
public class newTable {
	private int id;
	private String name;
	private int apart;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getApart() {
		return apart;
	}
	public void setApart(int apart) {
		this.apart = apart;
	}
	@Override
	public String toString() {
		return "newTable [id=" + id + ", name=" + name + ", apart=" + apart + "]";
	}
}