Spring MVC 4 学习5: 配置数据源（proxool连接池）及使用例子

最新推荐文章于 2021-02-11 10:32:46 发布

OK_boom

最新推荐文章于 2021-02-11 10:32:46 发布

阅读量5.2k

点赞数

分类专栏： Spring 大杂烩文章标签： spring mvc

本文链接：https://blog.csdn.net/rocklee/article/details/50541057

版权

Spring 大杂烩专栏收录该内容

95 篇文章 0 订阅

订阅专栏

proxool是一个非常优秀的数据库连接池，比postgresql自带的强太多了，下面简单讲下如何将proxool作为spring的连接池。

首先在WEB-INF下面建立proxool的配置参数文件，这里我用的是xml格式，当然还有其他格式，比如profile格式的，自己google下。

proxool.xml内容：

<?xml version="1.0" encoding="UTF-8"?>
<proxool> 
      <alias>bmsdbv2</alias>
       <!-- <driver-url>jdbc:postgresql://128.30.14.221:5432/bmsdb0823</driver-url>
        --> 
       <driver-url>jdbc:postgresql://128.30.14.220:5432/bmsdb</driver-url>
      
      <driver-class>org.postgresql.Driver</driver-class> 
      <driver-properties> 
             <property name="user" value="postgres" /> 
             <property name="password" value="password" /> 
      </driver-properties> 
        <!--最少保持的空闲连接数(默认2个)-->  
      <prototype-count>2</prototype-count>
      <minimum-connection-count>2</minimum-connection-count> 
      <maximum-connection-count>40</maximum-connection-count> 
        <!--没有空闲连接可以分配而在队列中等候的最大请求数,超过这个请求数的用户连接就不会被接受-->  
      <simultaneous-build-throttle>20</simultaneous-build-throttle> 
	<!--proxool自动侦察各个连接状态的时间间隔(毫秒),侦察到空闲的连接就马上回收,超时的销毁 默认30秒-->  
        <house-keeping-sleep-time>90000</house-keeping-sleep-time>  
        <!--在使用之前测试-->  
        <test-before-use>true</test-before-use>  
        <house-keeping-test-sql>select CURRENT_DATE</house-keeping-test-sql>      
</proxool>

上面的参数不用多说，看属性就该知道一二，这里使用的是免费又强大的Postgresql数据库。需要注意的是这个配置文件好像对utf-8编码支持有问题，建议将其编码设置为gbk, windows格式。

第二步，配置web.xml，让WEB APP启动时就加载连接池管理器，打开web.xml，加入如下servlet配置：

<!--管理proxool配置文件-->
	<servlet>
	    <servlet-name>proxoolServletConfigurator</servlet-name>
	    <servlet-class>org.logicalcobwebs.proxool.configuration.ServletConfigurator
	    </servlet-class>
	    <init-param>
	        <param-name>xmlFile</param-name>
	        <param-value>/WEB-INF/proxool.xml</param-value>
	    </init-param>
	    <load-on-startup>1</load-on-startup>
	</servlet>

要注意的是load-on-startup设为最高级别1，最先运行...

第三步，修改spring装配配置文件,这里我用的是java源代码位置下的spring-beans.xml,在<beans>里面装配一个dataSource对象：

<!-- 配置数据源 -->
	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName">
			<value>org.logicalcobwebs.proxool.ProxoolDriver</value>
		</property>
		<property name="url">
			<value>proxool.bmsdbv2</value>
		</property>
	</bean>

url参数指向听bmsdbv2就是proxool.xml里面的alias参数。

好了，现在来测试下这个dataSource如何使用，随便建立一个hellocontroll：

package com.freestyle.test.controller;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

import com.freestyle.db.DBUtil;

@Controller
public class HelloController {
	@RequestMapping("/greeting")
	public ModelAndView greeting(
			@RequestParam(value = "name", defaultValue = "World") String name)
			throws SQLException {
		System.out.println("Hello " + name);
		Map<String, Object> map = new HashMap<String, Object>();
		// map.put("userName", name);
		ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext(
				"spring-beans.xml");
		DataSource ds = (DataSource) w_context.getBean("dataSource");
		Connection w_con = ds.getConnection();
		Statement w_stmt = null;
		ResultSet w_rs = null;
		try {
			w_con.setAutoCommit(false);
			w_stmt = w_con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			w_rs = DBUtil
					.X61executeQuery(w_stmt,
							"select * from ta_user where fa_login='dgmislrh' order by fa_login");
			if (w_rs.next()) {
				map.put("userName", name + "," + w_rs.getString("fa_name"));
			} else {
				map.put("userName", "no found");
			}
		} finally {
			DBUtil.X63closeConn(w_con, w_stmt, w_rs);
		}
		if (ds != null) {
			return new ModelAndView("/hello", map);
		}
		return null;
	}

	@RequestMapping("/greeting1")
	public ModelAndView greeting1(
			@RequestParam(value = "name", defaultValue = "World") HttpServletRequest p_request,
			HttpServletResponse p_response, String name) {
		System.out.println("Hello " + name);
		Map<String, Object> map = p_request.getParameterMap();
		map.put("userName", name);
		return new ModelAndView("/hello", map);
	}

}

然后在浏览器地址栏输入：http://localhost/Spring4MVCHelloWorld/greeting?name=EEE ，运行正常会显示hello.jsp的模块内容，如果运行出错，多半是jar没准备好，这时候应该看看pom.xml配置是否有缺少什么依赖，请参考下面的pom.xml配置：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.jdon.web</groupId>
	<artifactId>Spring4MVCHelloWorld</artifactId>
	<packaging>war</packaging>
	<version>1.0-SNAPSHOT</version>
	<name>Spring4MVCHelloWorld Maven Webapp</name>
	<url>http://maven.apache.org</url>
	<properties>
		<spring.version>4.1.9.RELEASE</spring.version>
		<aspectj.version>1.8.7</aspectj.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>

		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>3.8.1</version>
			<scope>test</scope>
		</dependency>
		<!-- Spring dependencies -->


		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>${spring.version}</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjrt</artifactId>
			<version>${aspectj.version}</version>
		</dependency>
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjweaver</artifactId>
			<version>${aspectj.version}</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>com.cloudhopper.proxool</groupId>
			<artifactId>proxool</artifactId>
			<version>0.9.1</version>
		</dependency>
		<dependency>
			<groupId>postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<version>9.1-901.jdbc4</version>
		</dependency>

		<dependency>
			<groupId>commons-fileupload</groupId>
			<artifactId>commons-fileupload</artifactId>
			<version>1.3.1</version>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-core</artifactId>
			<version>2.7.0</version>
		</dependency>
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.7.0</version>
		</dependency>

	</dependencies>
	<build>
		<finalName>Spring4MVCHelloWorld</finalName>
	</build>
</project>

Eclipse自带的maven很傻，有时候改了java代码，需要手动运行maven install才能同步到target。