Spring通过dataSource获取数据库的连接测试

配置文件：
bean-properties.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:p="http://www.springframework.org/schema/p"    xmlns:util="http://www.springframework.org/schema/util"    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        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd"><!-- 导入属性文件 --><context:property-placeholder location="classpath:db.properties"/><bean id="DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"><property name="user" value="${user}"></property><property name="password" value="${password}"></property><property name="driverClass" value="${driverclass}"></property><property name="jdbcUrl" value="${jdbcurl}"></property></bean><!--UserDao -->    <bean id="UserDao" class="com.wh.spring.helloworld.properties.UserDao">        <property name="dataSource">            <ref bean="DataSource" />        </property>    </bean></beans>

UserDao.java文件

package com.wh.spring.helloworld.properties;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import javax.sql.DataSource;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import com.mchange.v2.c3p0.DriverManagerDataSource;/** *  result:14---吴神兵15---汪泽生16---朱小狗17---巫阿平18---admin19---李四20---张三21---王晓丽22---赵晓红23---汪小雨24---汪明杰25---谷小志26---汪芯蕊27---江梦妍28---赵强29---汪鹏30---Jones31---胡明32---王老五33---赵四34---王老七35---许杰36---汪鹏煊37---汪天雪38---汪皓宇39---汪忆妍40---李磊41---wpfsb42---汪小菲43---汪狗44--- *  *  * */public class UserDao {        DriverManagerDataSource dataSource2;        DataSource dataSource;        /**         * @return the dataSource         */        public DataSource getDataSource() {            return dataSource;        }        /**         * @param dataSource the dataSource to set         */        public void setDataSource(DataSource dataSource) {            this.dataSource = dataSource;        }        public List<User> queryAll() {            Connection conn = null;            PreparedStatement ppst = null;            ResultSet rs = null;            try {                conn = dataSource.getConnection();                ppst = conn.prepareStatement("select * from user");                rs = ppst.executeQuery();                List<User> list = new ArrayList<>();                while (rs.next()) {                    User user = new User(rs.getInt(1), rs.getString(2),rs.getString(3),//                            rs.getInt(4), rs.getString(5),rs.getString(6),//                            rs.getString(7), rs.getString(8),rs.getInt(9),//                            rs.getInt(10), rs.getString(11),rs.getString(12));                    list.add(user);                }                return list;            } catch (SQLException e) {                e.printStackTrace();            } finally {                try {                    rs.close();                    ppst.close();                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }            return null;        }        public static void main(String[] args) throws SQLException {            ApplicationContext context = new ClassPathXmlApplicationContext("bean-properties.xml");            UserDao dao = (UserDao) context.getBean("UserDao");            List<User> list = dao.queryAll();            for (User user : list) {                System.out.println(user.getId() + "---" + user.getUsername());            }        }}

User.java

package com.wh.spring.helloworld.properties;public class User {    private int id;    private String username;    private String password;    private int gender;    private String remark;    private String bookmarker;    private String study;    private String interest;    private int clazz;    private int experience;    private  String labelMarker;    private String recentRead;    public User(int id, String username, String password, int gender, String remark, String bookmarker, String study,            String interest, int clazz, int experience, String labelMarker, String recentRead) {        super();        this.id = id;        this.username = username;        this.password = password;        this.gender = gender;        this.remark = remark;        this.bookmarker = bookmarker;        this.study = study;        this.interest = interest;        this.clazz = clazz;        this.experience = experience;        this.labelMarker = labelMarker;        this.recentRead = recentRead;    }    /* (non-Javadoc)     * @see java.lang.Object#toString()     */    @Override    public String toString() {        return "User [id=" + id + ", username=" + username + ", password=" + password + ", gender=" + gender                + ", remark=" + remark + ", bookmarker=" + bookmarker + ", study=" + study + ", interest=" + interest                + ", clazz=" + clazz + ", experience=" + experience + ", labelMarker=" + labelMarker + ", recentRead="                + recentRead + "]";    }    /**     * @return the id     */    public int getId() {        return id;    }    /**     * @param id the id to set     */    public void setId(int id) {        this.id = id;    }    /**     * @return the username     */    public String getUsername() {        return username;    }    /**     * @param username the username to set     */    public void setUsername(String username) {        this.username = username;    }    /**     * @return the password     */    public String getPassword() {        return password;    }    /**     * @param password the password to set     */    public void setPassword(String password) {        this.password = password;    }    /**     * @return the gender     */    public int getGender() {        return gender;    }    /**     * @param gender the gender to set     */    public void setGender(int gender) {        this.gender = gender;    }    /**     * @return the remark     */    public String getRemark() {        return remark;    }    /**     * @param remark the remark to set     */    public void setRemark(String remark) {        this.remark = remark;    }    /**     * @return the bookmarker     */    public String getBookmarker() {        return bookmarker;    }    /**     * @param bookmarker the bookmarker to set     */    public void setBookmarker(String bookmarker) {        this.bookmarker = bookmarker;    }    /**     * @return the study     */    public String getStudy() {        return study;    }    /**     * @param study the study to set     */    public void setStudy(String study) {        this.study = study;    }    /**     * @return the interest     */    public String getInterest() {        return interest;    }    /**     * @param interest the interest to set     */    public void setInterest(String interest) {        this.interest = interest;    }    /**     * @return the clazz     */    public int getClazz() {        return clazz;    }    /**     * @param clazz the clazz to set     */    public void setClazz(int clazz) {        this.clazz = clazz;    }    /**     * @return the experience     */    public int getExperience() {        return experience;    }    /**     * @param experience the experience to set     */    public void setExperience(int experience) {        this.experience = experience;    }    /**     * @return the labelMarker     */    public String getLabelMarker() {        return labelMarker;    }    /**     * @param labelMarker the labelMarker to set     */    public void setLabelMarker(String labelMarker) {        this.labelMarker = labelMarker;    }    /**     * @return the recentRead     */    public String getRecentRead() {        return recentRead;    }    /**     * @param recentRead the recentRead to set     */    public void setRecentRead(String recentRead) {        this.recentRead = recentRead;    }}

db.properties

user=rootpassword=rootdriverclass=com.mysql.jdbc.Driverjdbcurl=jdbc:mysql://localhost:3306/knowledge

所引用的jar包