Spring 配置数据源 利用JdbcTemplate工具类实现增删改查

POJO类封装数据
public class User {
    private Integer id;
    private String name;
    private int age;

    public User() {
    }

    public User(Integer id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "[User: id=" + id + ", name=" + name + ", age=" + age + "]";
    }

}

通过注入的JdbcTemplate工具类实现增删改查
@Repository
public class UserDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 保存
     * @param user
     */
    public void save(final User user) {
        /*
         * jdbcTemplate.execute(new ConnectionCallback() {
         * 
         * @Override public Object doInConnection(java.sql.Connection conn)
         * throws SQLException, DataAccessException { String sql =
         * "insert into t_user(name, age) values (?, ?)";
         * java.sql.PreparedStatement ps = conn.prepareStatement(sql);
         * ps.setString(1, user.getName()); // 第1个参数的索引是1 ps.setInt(2,
         * user.getAge()); ps.execute(); ps.close(); return null; } });
         */
        String sql = "insert into t_user(name, age) values (?, ?)";
        jdbcTemplate
                .update(sql, new Object[] { user.getName(), user.getAge() });
    }

    /**
     * 删除
     * @param id
     */
    public void delete(Integer id) {
        String sql = "delete from t_user where id=?";
        jdbcTemplate.update(sql, new Object[] { id });
    }

    /**
     * 更新
     * @param user
     */
    public void update(User user) {
        String sql = "update t_user set name=?, age=? where id=?";
        jdbcTemplate.update(sql, new Object[] { user.getName(), user.getAge(),
                user.getId() });
    }

    /**
     * 根据id查询一个数据
     * 
     * @param id
     * @return
     */
    public User getById(final Integer id) {
        String sql = "select name,age from t_user where id=?";
        return (User) jdbcTemplate.queryForObject(sql, new Object[] { id },
                new RowMapper() {
                    public Object mapRow(ResultSet rs, int rowNum)
                            throws SQLException {
                        String name = rs.getString(1);
                        int age = rs.getInt(2);
                        return new User(id, name, age);
                    }
                });
    }

    @Transactional(isolation = Isolation.READ_COMMITTED)
    public void testGet(int id) {
        User user = getById(id);
        System.out.println(user);

        user = getById(id);
        System.out.println(user);
    }

    /**
     * 查询总数量
     * 
     * @return
     */
    public int getCount() {
        String sql = "select count(*) from t_user";
        return jdbcTemplate.queryForInt(sql);
    }

    /**
     * 查询所有
     * 
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<User> findAll() {
        String sql = "select * from t_user";
        return jdbcTemplate.query(sql, new RowMapper() {

            @Override
            public Object mapRow(ResultSet rs, int arg1) throws SQLException {
                int id = rs.getInt(1);
                String name = rs.getString(2);
                int age = rs.getInt(3);
                return new User(id, name, age);
            }
        });
    }

    /**
     * 查询所有（分页）
     * 
     * @param firstResult
     *            从哪一条开始
     * @param maxResult
     *            查询几条
     * @return
     */
    public QueryResult findAll(int firstResult, int maxResult) {
        int count = jdbcTemplate.queryForInt("select count(*) from t_user");
        String sql = "select * from t_user limit ?,?";
        List list = jdbcTemplate.query(sql, new Object[] { firstResult,
                maxResult }, new RowMapper() {

            @Override
            public Object mapRow(ResultSet as, int arg1) throws SQLException {
                int id = as.getInt(1);
                String name = as.getString(2);
                int age = as.getInt(3);

                return new User(id, name, age);
            }
        });
        return new QueryResult(count,list);
    }

}

用于分页的数据存储
public class QueryResult {
    private int count;
    private List list;

    public QueryResult(int count, List list) {
        this.count = count;
        this.list = list;
    }

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }

    public List getList() {
        return list;
    }

    public void setList(List list) {
        this.list = list;
    }

}

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"
    xmlns:tx="http://www.springframework.org/schema/tx"
    default-lazy-init="true"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
                http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
                http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
    <!--  
    <context:property-placeholder location="classpath:cn/com/spring/k_property/jdbc.properties"/>
    -->
    <!-- 自动扫描与装配bean -->
    <context:component-scan base-package="cn.com.spring.m_jdbc"></context:component-scan>
    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:cn/com/spring/m_jdbc/jdbc.properties</value>
            </list>
        </property>
    </bean>

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="jdbcUrl" value="${jdbcUrl}"></property>
        <property name="driverClass" value="${driverClass}"></property>
        <property name="user" value="${username}"></property>
        <property name="password" value="${password}"></property>
        <!-- 一些管理的配置 -->
        <!--初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
        <property name="initialPoolSize" value="3"></property>
        <!--连接池中保留的最小连接数。Default: 3 -->
        <property name="minPoolSize" value="3"></property>
        <!--连接池中保留的最大连接数。Default: 15 -->
        <property name="maxPoolSize" value="5"></property>
        <!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
        <property name="acquireIncrement" value="3"></property>
        <!--最大空闲时间，1800秒内未使用则连接被丢弃，若为0则永不丢弃。Default: 0 -->
        <property name="maxIdleTime" value="1800"></property>
    </bean>

    <!-- 二、配置JdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

</beans>

jdbc.properties

jdbcUrl  = jdbc:mysql:///newcss
driverClass = com.mysql.jdbc.Driver
username = root
password = root

测试类
public class UserDaoTest {

    private ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml", getClass());
    private UserDao userDao = (UserDao) ac.getBean("userDao");

    @Test
    public void testSave_1() {
        User user = new User();
        user.setName("李四1");
        user.setAge(25);

        userDao.save(user);
    }

    @Test
    public void testSave_25() {
        for (int i = 1; i <= 25; i++) {
            User user = new User();
            user.setName("李四_" + i);
            user.setAge(i);

            userDao.save(user);
        }
    }

    @Test
    public void testDelete() {
        userDao.delete(1);
    }

    @Test
    public void testUpdate() {
        User user = new User();
        user.setId(2); // 模拟一条记录
        user.setName("李四222");
        user.setAge(25);

        userDao.update(user);
    }

    @Test
    public void testGetById() {
        User user = userDao.getById(2);
        System.out.println(user);
    }

    @Test
    public void testGetCount() {
        int count = userDao.getCount();
        System.out.println(count);
    }

    @Test
    public void testFindAll() {
        List<User> list = userDao.findAll();
        for (User user : list) {
            System.out.println(user);
        }
    }

    @Test
    public void testFindAllIntInt() {
        // 查询
         QueryResult qr = userDao.findAll(0, 10); // 第1页，每页10条
//       QueryResult qr = userDao.findAll(10, 10); // 第2页，每页10条
//      QueryResult qr = userDao.findAll(20, 10); // 第3页，每页10条

        // 显示结果
        System.out.println("总结果数：" + qr.getCount());
        for (User user : (List<User>) qr.getList()) {
            System.out.println(user);
        } 
    }
}