整合spring、ibatis、proxool

1、proxool连接池配置文件proxool.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:aop="http://www.springframework.org/schema/aop" 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.5.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-2.5.xsd
       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
       http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
    <bean id="dataSource" class="org.logicalcobwebs.proxool.ProxoolDataSource" destroy-method="close">
        <!--驱动类-->
        <property name="driver">
            <value>com.mysql.jdbc.Driver</value>
        </property>
        <!--url连接串-->
        <property name="driverUrl">
            <value>jdbc:mysql://192.168.1.17:3306/ibatisTest</value>
        </property>
        <!--用户名-->
        <property name="user">
            <value>root</value>
        </property>
        <!--密码-->
        <property name="password">
            <value>root</value>
        </property>
        <!--数据源的别名-->
        <property name="alias">
            <value>bst</value>
        </property>
        <!--最少保持的空闲连接数(默认 2 个)-->
        <property name="prototypeCount">
            <value>1</value>
        </property>
        <!--最大连接数(默认 5 个),超过了这个连接数,再有请求时,就排在队列中等候,最大
            的等待请求数由 maximum-new-connections 决定 -->
        <property name="maximumConnectionCount">
            <value>2</value>
        </property>
        <!--最小连接数(默认 2 个)-->
        <property name="minimumConnectionCount">
            <value>1</value>
        </property>
        <!--如果housekeeper(毫秒) 检测到某个线程的活动时间大于这个数值.它将会杀掉这个线程.
            所以确认一下你的服务器的带宽.然后定一个合适的值.默认是5分钟.-->
        <property name="maximumActiveTime">
            <value>60000</value>
        </property>
<!--        <property name="houseKeepingSleepTime">
            <value>9999</value>
        </property>-->
        <!--如果为true,那么每个被执行的SQL语句将会在执行期被log记录(DEBUG LEVEL).
            你也可以注册一个ConnectionListener (参看ProxoolFacade)得到这些信息.-->
        <property name="trace">
            <value>true</value>
        </property>
        <property name="verbose">
            <value>true</value>
        </property>
    </bean>
    <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
        <!--引入ibatis关系对象映射文件-->
        <property name="configLocation" value="ibatisConfig.xml"/>
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

 2、ibatis开源框架配置文件ibatisConfig.xml <?xml version = "1.0" encoding = "UTF-8" ?>

<!DOCTYPE sqlMapConfig
    PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
    <sqlMap  resource  = "model/User.xml"   />
</sqlMapConfig>

 3、User.java

 

package model;

import java.io.Serializable;

public class User implements Serializable {
    private static final long serialVersionUID = 1L;
    private Integer id;
    private String name_s;
    private Integer sex;

    public User() {
    }

    public Integer getId() {

        return this.id;

    }

    public void setId(Integer id) {

        this.id = id;

    }

    public String getName_s() {
        return name_s;
    }

    public void setName_s(String name_s) {
        this.name_s = name_s;
    }

   

    public Integer getSex() {

        return this.sex;

    }

    public void setSex(Integer sex) {

        this.sex = sex;

    }
}

 4、User.xml

 

<?xml version = "1.0" encoding = "UTF-8"  ?>
<!DOCTYPE sqlMap
    PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap  namespace  = "User" >
    <typeAlias  alias  = "user"  type  = "model.User"   />
    <select  id  = "User.getUser"  parameterClass  = "java.lang.String"
        resultClass  = "user" >
         select  name_s,sex   from t_useR   where name_s = #name#
    </select>
    <select  id  = "getAllUser"  resultClass  = "user" >
              select  id,  name_s from t_useR
    </select>
    <update  id  = "updateUser"  parameterClass  = "user" >
          UPDATE t_useR  SET name_s=#name_s#, sex=#sex# WHERE id = #id#
    </update>
    <insert  id  = "insertUser"  parameterClass  = "user" >
        INSERT INTO t_useR ( name_s, sex) VALUES ( #name_s#, #sex# )
    </insert>
    <delete  id  = "deleteUser"  parameterClass  = "java.lang.String" >
        delete from t_useR where id=#value#
    </delete>
</sqlMap>

 5、Main.java public class Main{

public static void main(String[] args){
ApplicationContext context = new ClassPathXmlApplicationContext("proxool3.xml");
        SqlMapClient sqlMapClient = (SqlMapClient) context.getBean("sqlMapClient");
        User u = (User) sqlMapClient.queryForObject("User.getUser", "3");
        System.out.println("sex:" + u.getSex());
}
}

 6、web.xml

 

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" 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">
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
    <servlet>
        <servlet-name>proxooladmin</servlet-name>
        <servlet-class>org.logicalcobwebs.proxool.admin.servlet.AdminServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>proxooladmin</servlet-name>
        <url-pattern>/proxooladmin</url-pattern>
    </servlet-mapping>
<!-- 配置受保护域，只有Tomcat管理员才能察看连接池的信息 -->
    <security-constraint>
        <web-resource-collection>
            <web-resource-name>proxool</web-resource-name>
            <url-pattern>/proxooladmin</url-pattern>
        </web-resource-collection>
        <auth-constraint>
            <role-name>manager</role-name>
        </auth-constraint>
    </security-constraint>
    <login-config>
        <auth-method>BASIC</auth-method>
        <realm-name>proxool manager Application</realm-name>
    </login-config>
    <security-role>
        <description>The role that is required to log in to the Manager Application</description>
        <role-name>manager</role-name>
    </security-role>
    <error-page>
        <error-code>401</error-code>
        <location>/error/401.jsp</location>
    </error-page>
</web-app>

 7、401.jsp

 

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <%
                    response.setHeader("WWW-Authenticate", "Basic realm=\"Tomcat Manager Application\"");
        %>
    </body>
</html>

 所需jar包：commons-logging-api-1.1.jar、ibatis-2.3.3.720.jar、log4j-1.2.11.jar、mysql-connector-java-5.1.9.jar、proxool-0.9.1.jar、proxool-cglib.jar、spring.jar