（九）spring JDBC

    为了使JDBC 更加易于使用，Spring 在JDBC API 上封装了一层模板JdbcTemplate。 JdbcTemplate 使用起来很简单：

1. 配置数据源, 如c3p0

2. 注册JdbcTemplate Bean：

3. 为Dao 注入添加JdbcTemplate 属性

<?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.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd"><!-- 配置注解自动扫描包 --><context:component-scan base-package="org.zgf.spring.dao"/><!-- 指定数据库配置文件位置 --><context:property-placeholder location="classpath:jdbc.properties"/><!-- 配置c3p0 数据源   --><bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"><property name="jdbcUrl" value="${jdbc.jdbcUrl}"/><property name="driverClass" value="${jdbc.driverClass}"/><property name="user" value="${jdbc.user}"/><property name="password" value="${jdbc.password}"/><property name="initialPoolSize" value="${jdbc.initialPoolSize}"/><property name="maxPoolSize" value="${jdbc.maxPoolSize}"/></bean><!-- 配置JdbcTemplate --><bean class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"></property></bean></beans>

UserJTDao

/** *  JdbcTemplate *  1. JdbcTemplate 是Spring 框架对jdbc 的一层封装 *  2. JdbcTemplate 查询时 只支持位置参数，不支持命名参数 *  3. JdbcTemplate 可以对结果集进行封装，但是不支持级联属性 */@Repositorypublic class UserJTDao {@Autowiredprivate JdbcTemplate jdbcTemplate;private final String tableName = " spring_jdbc_user ";/** 执行 insert , update , delete 语句   */public void addUser(User user){String  sql = "insert into " + tableName + " values(null,?,?,?)";this.jdbcTemplate.update(sql, user.getUsername(),user.getPassword(),user.getAge());}/*** 批量执行：insert, update, delete 语句   */public void addBatchUser(List<User> users){String  sql = "insert into  " + tableName + "  values(null,?,?,?)";List<Object[]> batchArgs = new ArrayList<Object[]>();for(int i=0; i<users.size();  i++){Object[] objectArray = new Object[]{users.get(i).getUsername(), users.get(i).getPassword(), users.get(i).getAge()};batchArgs.add(objectArray);} this.jdbcTemplate.batchUpdate(sql, batchArgs);}/** 查询单个对象,进行结果集封装  */public User getUserById(Integer id){String sql = "select * from " + tableName + " where id = ? ";//结果集映射规则RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);return this.jdbcTemplate.queryForObject(sql, rowMapper, id);}/**  查询列表   */public List<User> getUsersByAge(int age){String sql = "select * from " + tableName + " where age = ?" ;//结果集映射方式RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);return this.jdbcTemplate.query(sql, rowMapper, age);}/**  查询某一字段   */public String getUserName(int id){String sql = "select username from " + tableName + " where id = ? ";return this.jdbcTemplate.queryForObject(sql, String.class, id);}/** 查询某一字段数组   */public List<String> getUserNames(int age){String sql = "select username from " + tableName + " where age = ?" ;//结果集映射方式return this.jdbcTemplate.queryForList(sql, String.class, age);}}

测试用例

@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations="classpath:org/zgf/spring/dao/applications-template.xml")public class Test_UserDao {@Autowiredprivate UserJTDao userJTDao;@Testpublic void test_addUser(){User user = new User("zong","gaofeng",20);this.userJTDao.addUser(user);}@Testpublic void test_addBatch(){List<User> users = new ArrayList<>();users.add(new User("zong_01","zong_01",30));users.add(new User("zong_02","zong_03",30));users.add(new User("zong_03","zong_03",30));this.userJTDao.addBatchUser(users);System.out.println("成功添加了" + users.size() + " 条记录");}@Testpublic void test_getById(){int id = 1;User user = this.userJTDao.getUserById(id);System.out.println(user);}@Testpublic void test_getUsersByAge(){List<User> userList = this.userJTDao.getUsersByAge(30);for (User user : userList) {System.out.println(user);}}@Testpublic void test_getUsernameById(){int id = 10;String username = this.userJTDao.getUserName(id);System.out.println(username);}@Testpublic void test_getUsernames(){int age = 30;List<String> usernames = this.userJTDao.getUserNames(age);System.err.println("usernames:" + usernames);}}