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工具类实现增删改查@Repositorypublic 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.propertiesjdbcUrl  = jdbc:mysql:///newcssdriverClass = com.mysql.jdbc.Driverusername = rootpassword = 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);        }     }}