Spring 使用 jdbcTemplate 和 jdbcDaoSupport

1.编写db.properties文件

jdbc.user=root
jdbc.password=123456
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql:///web08

jdbc.initPoolSize=5
jdbc.maxPoolSize=10

2.导入资源文件

<context:property-placeholder location="classpath:db.properties"/>

3. 配置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>

4.配置Spring 的jdbcTemplate

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
	<property name="dataSource" ref="dataSource"></property>
</bean>

5.执行 CRUD 操作

• 执行INSERT,UPDATE,DELETE

  调用update(String sql, Object... args)方法 
  

public void testInsert() {
	String sql="INSERT INTO  tbl_user(uname,upassword) VALUES(?,?)";
	jdbcTemplate.update(sql,"Jerry", "Jerry");
}

public void testUpdate() {
	String sql="UPDATE tbl_user set uname = ? where uid = ?";
	jdbcTemplate.update(sql,"Jack", 5);
}

public void testDelete() {
	String sql="DELETE FROM tbl_user where uid = ?";
	jdbcTemplate.update(sql,3);
}

• 执行批量更新： 批量的INSERET, UPDATE, DELETE

  调用batchUpdate(String sql, List<Object[]> batchArgs)方法 
  

// 最后一个参数是Object[] 的list类型：因为修改一条记录需要一个Object的数组，那么多条不就需要多个Object的数组吗
public void testBatchUpdate() {
	String sql = "INSERT INTO tbl_user(uname,upassword) values(?,?)";

	List<Object[]> batchArgs = new ArrayList<>();
	
	batchArgs.add(new Object[] {"xiaoming","ming"});
	batchArgs.add(new Object[] {"xiaohong","hong"});
	batchArgs.add(new Object[] {"xiaozhang","zhang"});
	
	jdbcTemplate.batchUpdate(sql, batchArgs);
}

• 从数据库中获取一条记录，实际得到对应的一个对象

  1.调用queryForObject(String sql, RowMapper<tbl_user> rowMapper, Object... args)方法，而不是调用queryForObject(String sql, Class<tbl_user> requiredType, Object... args)
  2.其中的RowMpper 指定如何去 映射结果集的行，常用的实现类为BeanPropertyRowMapper
  3.使用sql 中列的别名完成列名和类的属性名的映射，例如uname name
  4.不支持级联属性，jdbcTemplate 到底是一个jdbc 的小工具，而不是ORM 框架
  

	public void testQueryForObject() {
		String sql = "SELECT uid,uname name,upassword FROM tbl_user where uid=?";
		//tbl_user user = jdbcTemplate.queryForObject(sql, tbl_user.class,1);
		RowMapper<tbl_user> rowMapper = new BeanPropertyRowMapper<>(tbl_user.class);
		tbl_user user = jdbcTemplate.queryForObject(sql, rowMapper, 1);
		System.out.println(user);
	}

• 查到实体类的集合

  注意调用的是query(String sql, RowMapper<tbl_user> rowMapper, Object... args)方法不是queryForList 方法
  

	public void testQueryForList() {
		String sql = "select uid,uname name,upassword from tbl_user where uid>?";
		RowMapper<tbl_user> rowMapper = new BeanPropertyRowMapper<>(tbl_user.class);
		List<tbl_user> users = jdbcTemplate.query(sql, rowMapper,5);
		System.out.println(users);
	}

• 获取单个列的值，或做统计查询

  调用queryForObject(String sql, Class<Long> requiredType)方法
  

public void testQueryForObject2() {
	String sql = "select count(*) from tbl_user";
	long count = jdbcTemplate.queryForObject(sql, Long.class);
	System.out.println(count);
}

---

实际开发中应用

直接使用jdbcTemplate 作为Dao 类的成员变量

@Repository
public class UserDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public tbl_user get(Integer id) {
		String sql = "SELECT uid,uname name,upassword FROM tbl_user where uid=?";
		RowMapper<tbl_user> rowMapper = new BeanPropertyRowMapper<>(tbl_user.class);
		tbl_user user = jdbcTemplate.queryForObject(sql, rowMapper, id);
		return user;
	}
}

或者是使用jdbcDaoSupport,但不推荐！！！！

@Repository
public class AddressDao extends JdbcDaoSupport{
	
	@Autowired
	public void setDataSource2(DataSource dataSource) {
		setDataSource(dataSource);
	}
	
	public Address get(Integer id) {
		String sql = "select id aid, name aname from address where id=?";
		RowMapper rowMapper = new BeanPropertyRowMapper<>(Address.class);
		return (Address) getJdbcTemplate().queryForObject(sql, rowMapper, id);
	}
}