spring2配置proxool连接池 jtds驱动 spring2 jdbcTemplate配置

spring2+proxool 配置
String2+proxool 配置
proxool+ jtds 配置
spring+jdbcTemplate配置
web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
    version="2.5">
    <display-name>webManageSystem</display-name>
    <filter>
        <filter-name>encodingFilter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
        <init-param>
            <param-name>encoding</param-name>
            <param-value>UTF-8</param-value>
        </init-param>
    </filter>
    <filter-mapping>
        <filter-name>encodingFilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>

    <filter>
        <filter-name>struts2</filter-name>
        <filter-class>org.apache.struts2.dispatcher.FilterDispatcher</filter-class>
    </filter>
    <filter-mapping>
        <filter-name>struts2</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>

    <servlet>
        <servlet-name>ServletConfigurator</servlet-name>
        <servlet-class>
            org.logicalcobwebs.proxool.configuration.ServletConfigurator</servlet-class>
        <init-param>
            <param-name>propertyFile</param-name>
            <param-value>WEB-INF\classes\proxool.properties </param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>
    <servlet>
        <servlet-name>Admin</servlet-name>
        <servlet-class>org.logicalcobwebs.proxool.admin.servlet.AdminServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>Admin</servlet-name>
        <url-pattern>/proxool.admin</url-pattern>
    </servlet-mapping>

    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>/WEB-INF/applicationContext.xml</param-value>
    </context-param>
</web-app>

 proxool.properties 这里对应web.xml的放置位置

jdbc-0.proxool.alias=DBPool
jdbc-0.proxool.driver-url=jdbc:jtds:sqlserver://127.0.0.1:1433;DatabaseName=webManageSystem
jdbc-0.proxool.driver-class=net.sourceforge.jtds.jdbc.Driver
jdbc-0.user=sa
jdbc-0.password=123789
jdbc-0.proxool.house-keeping-sleep-time=40000
jdbc-0.proxool.house-keeping-test-sql=SELECT ''
jdbc-0.proxool.maximum-connection-count=50
jdbc-0.proxool.minimum-connection-count=15
jdbc-0.proxool.maximum-connection-lifetime=18000000
jdbc-0.proxool.simultaneous-build-throttle=5
jdbc-0.proxool.recently-started-threshold=40000
jdbc-0.proxool.overload-without-refusal-lifetime=50000
jdbc-0.proxool.maximum-active-time=60000
jdbc-0.proxool.verbose=true
jdbc-0.proxool.trace=true
jdbc-0.proxool.fatal-sql-exception=Fatal error
jdbc-0.proxool.prototype-count=2
jdbc-0.proxool.statistics-log-level=ERROR

 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:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
                http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd">
    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName">
            <value>org.logicalcobwebs.proxool.ProxoolDriver</value>
        </property>
        <property name="url">
            <value>proxool.DBPool</value>
        </property>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <bean id="adminUserDao" class="web.manage.admin.user.dao.impl.UserDao">
        <property name="jdbcTemplate">
            <ref bean="jdbcTemplate"></ref>
        </property>
    </bean>
</beans>

 

web.manage.admin.user.dao.impl.UserDao类
package web.manage.admin.user.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import web.manage.admin.user.dao.interfaces.IUserDao;
import web.manage.admin.user.form.UserForm;

public class UserDao implements IUserDao {
    private JdbcTemplate jdbcTemplate;

    /**
    * @param jdbcTemplate
    *            the 获取数据源 to set
    */
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /*
    * (non-Javadoc)
    *
    * @see web.manage.admin.user.dao.interfaces.IUserDao#deleteUser(long)
    */
    @Override
    public boolean deleteUser(long id) {
        int i = jdbcTemplate.update(
                "delete from webSys_adminUser where id = ?",
                new Object[] { id });
        if (i == 0)
            return false;
        else
            return true;
    }

    /*
    * (non-Javadoc)
    *
    * @see web.manage.admin.user.dao.interfaces.IUserDao#getUser(long)
    */
    @Override
    public UserForm getUser(long id) {
        String sql = "select * from webSys_adminUser where id=?";
        final UserForm u = new UserForm();
        final Object[] params = new Object[] { id };
        jdbcTemplate.query(sql, params, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                u.setId(rs.getLong("id"));
                u.setUserName(rs.getString("userName"));
                u.setPassword(rs.getString("password"));
                u.setPopedom(rs.getString("popedom"));
            }
        });
        return u;
    }

    /*
    * (non-Javadoc)
    *
    * @see
    * web.manage.admin.user.dao.interfaces.IUserDao#getUser(java.lang.String)
    */
    @Override
    public UserForm getUser(String username) {
        String sql = "select * from webSys_adminUser where username=?";
        final UserForm u = new UserForm();
        final Object[] params = new Object[] { username };
        jdbcTemplate.query(sql, params, new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                u.setId(rs.getLong("id"));
                u.setUserName(rs.getString("userName"));
                u.setPassword(rs.getString("password"));
                u.setPopedom(rs.getString("popedom"));
            }
        });
        return u;
    }

    /*
    * (non-Javadoc)
    *
    * @see
    * web.manage.admin.user.dao.interfaces.IUserDao#getUserList(java.lang.String
    * )
    */
    @Override
    public List<UserForm> getUserList(String sql) {
        List<UserForm> list;
        list = jdbcTemplate.query(sql, new UserRowMap());
        return list;
    }

    /*
    * (non-Javadoc)
    *
    * @see
    * web.manage.admin.user.dao.interfaces.IUserDao#saveUser(web.manage.admin
    * .user.form.UserForm)
    */
    @Override
    public boolean saveUser(UserForm user) {
        String sql = "insert into webSys_adminUser(userName,password,popedom,) VALUES(?,?,?)";
        int i = jdbcTemplate.update(sql, new Object[] { user.getUserName(),
                user.getPassword(), user.getPopedom() });
        return i != 0;
    }

    /*
    * (non-Javadoc)
    *
    * @see web.manage.admin.user.dao.interfaces.IUserDao#updatePasswd(long,
    * java.lang.String)
    */
    @Override
    public boolean updatePasswd(long id, String password) {
        String sql = "UPDATE webSys_adminUser set password=? where id=?";
        int i = jdbcTemplate.update(sql, new Object[] { password, id });
        return i != 0;
    }

    /*
    * (non-Javadoc)
    *
    * @see
    * web.manage.admin.user.dao.interfaces.IUserDao#updateUser(web.manage.admin
    * .user.form.UserForm)
    */
    @Override
    public boolean updateUser(UserForm user) {
        String sql = "UPDATE webSys_adminUser set userName=?,password=?,popedom=?";
        int i = jdbcTemplate.update(sql, new Object[] { user.getUserName(),
                user.getPassword(), user.getPopedom() });
        return i != 0;
    }

}

 需要的UserRowMap 类

package web.manage.admin.user.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import web.manage.admin.user.form.UserForm;

public class UserRowMap implements RowMapper {

    /*
    * (non-Javadoc)
    *
    * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet,
    * int)
    */
    @Override
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        UserForm u = new UserForm();
        u.setUserName(rs.getString("userName"));
        u.setPassword(rs.getString("password"));
        u.setPopedom(rs.getString("popedom"));
        return u;
    }

}