Spring2 JDBC 连接池

上一篇 《Spring2 JDBC》的升级版

环境 Spring2.0

建表语句：

create table t_user(
id number(6),
name varchar2(12),
age int
)

需要的jar 包

程序结构图：

注意estimantion_config.xml nt_action.xml nt_svc.xml 几个配置文件的位置。

先定义接口

model pojo对象

Service 类

public class UserServiceImp implements IUserService {private DbDao dao;public DbDao getDao() {return dao;}public void setDao(DbDao dao) {this.dao = dao;}public User find(int id) {// TODO Auto-generated method stubConnection conn = null;PreparedStatement psmt = null;ResultSet rs = null;String sql = "SELECT * FROM t_user WHERE id=?";try{conn = dao.getConnection();psmt = conn.prepareStatement(sql);psmt.setInt(1, id);rs = psmt.executeQuery();while(rs.next()){int uid = rs.getInt(1);String name = rs.getString(2);int age = rs.getInt(3);User user = new User();user.setId(uid);user.setName(name);user.setAge(age);return user;}}catch (Exception e) {e.printStackTrace();}finally{if(null != psmt){try {psmt.close();} catch (Exception e) {}}if(null != conn){try {conn.close();} catch (Exception e) {}}}return null;}public void insert(User user) {// TODO Auto-generated method stubint id = user.getId();String name = user.getName();int age = user.getAge();Connection conn = null;PreparedStatement psmt = null;String sql = "INSERT INTO t_user(id, name, age) VALUES(?,?,?)";try{conn = dao.getConnection();psmt = conn.prepareStatement(sql);psmt.setInt(1, id);psmt.setString(2, name);psmt.setInt(3, age);psmt.executeUpdate();}catch (Exception e) {e.printStackTrace();}finally{if(null != psmt){try {psmt.close();} catch (Exception e) {}}if(null != conn){try {conn.close();} catch (Exception e) {}}}}}

DAO 

package com.spring2.demo.dao;import java.sql.Connection;import javax.naming.Context;import javax.naming.InitialContext;import javax.sql.DataSource;public class DbDao {private DataSource dataSource;private DbDao(){}public DataSource getDataSource() {return dataSource;}public void setDataSource(DataSource dataSource) {this.dataSource = dataSource;}public Connection getConnection() throws Exception{Connection conn = null;Context initContext = new InitialContext();Context envContext = (Context)initContext.lookup("java:comp/env");dataSource = (DataSource)envContext.lookup("jdbc/demo");conn = dataSource.getConnection();return conn;}}

Action类

package com.spring2.demo.controller;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.springframework.web.servlet.ModelAndView;import org.springframework.web.servlet.mvc.multiaction.MultiActionController;import com.spring2.demo.service.IUserService;public class SdaoAction extends MultiActionController{private IUserService service;public IUserService getService() {return service;}public void setService(IUserService service) {this.service = service;}public ModelAndView getUserName(HttpServletRequest request,HttpServletResponse response){ModelAndView view = null;try{String name = service.find(1).getName();view = new ModelAndView("/index.jsp");request.setAttribute("name", name);System.out.println("name in action -->" + name);}catch(Exception e){e.printStackTrace();request.setAttribute("error", "系统错误!");request.setAttribute("exception", e);}return view;}}

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>context</servlet-name><servlet-class>org.springframework.web.context.ContextLoaderServlet</servlet-class><load-on-startup>1</load-on-startup>  </servlet>    <servlet><servlet-name>estimantion</servlet-name><servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class><init-param><param-name>contextConfigLocation</param-name><param-value>/WEB-INF/estimantion_config.xml</param-value></init-param><load-on-startup>2</load-on-startup></servlet><servlet-mapping><servlet-name>estimantion</servlet-name><url-pattern>*.do</url-pattern></servlet-mapping><filter><filter-name>encodingFilter</filter-name><filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class><init-param><param-name>encoding</param-name><param-value>GBK</param-value></init-param></filter></web-app>

applicationContext.xml  其实没有配置任何东西，但是没有这个文件 web Server在启动时会报错

<?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:jee="http://www.springframework.org/schema/jee"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd         http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.0.xsd"></beans>

estimantion_config.xml 主配置文件 指定了action 和 Service 的配置文件

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">    <!--  <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean"><property name="jndiName" value="jdbc/demo" /></bean>--><bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" /><bean id="dbDao" class="com.spring2.demo.dao.DbDao"><property name="dataSource" ref="dataSource" /></bean><bean id="userServiceImp" class="com.spring2.demo.service.UserServiceImp" ><property name="dao" ref="dbDao" /></bean><import resource="nt_action.xml"/><import resource="nt_svc.xml"/></beans>

* 注：

一开始想用 

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean"><property name="jndiName" value="jdbc/demo" /></bean>

但是测试过程中提示：

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in ServletContext resource [/WEB-INF/estimantion_config.xml]: Invocation of init method failed; nested exception is javax.naming.NameNotFoundException: Name jdbc is not bound in this Context

后来通过在网上搜索，在Dao的类中

增加代码:

Context initContext = new InitialContext();Context envContext = (Context)initContext.lookup("java:comp/env");dataSource = (DataSource)envContext.lookup("jdbc/demo");conn = dataSource.getConnection();

并在原配置文件修改为

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" />

*所以这里用到的连接池是Web Server的连接池 而 不是 Spring的连接池

nt_action.xml Action类配置文件

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd"><bean id="SdemoMapping"class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping"><property name="mappings"><props><prop key="Sdemo.do">SdaoAction</prop></props></property></bean><bean id="monitorMethodResolver"class="org.springframework.web.servlet.mvc.multiaction.ParameterMethodNameResolver"><property name="paramName"><value>method</value></property><property name="defaultMethodName"><value>getUserName</value></property></bean><bean id="SdaoAction" class="com.spring2.demo.controller.SdaoAction"><property name="methodNameResolver"><ref bean="monitorMethodResolver" /></property><property name="service"><ref bean="demoService" /></property></bean></beans>

nt_svc.xml Service类配置文件

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd"><bean id="demoService" class="com.spring2.demo.service.UserServiceImp"><property name="dao"><ref bean="dbDao" /></property></bean></beans>

dbDao 是在estimantion_config.xml 中定义过的bean

tomcat conf/servlet.xml 中连接池配置

<Context path="/bitest" docBase="E:\\SpringDaoDemo2\\WebRoot" reloadable="true"><Resource name="jdbc/demo" driverClassName="oracle.jdbc.driver.OracleDriver" maxActive="5" maxIdle="1" maxWait="5" password="密码" type="javax.sql.DataSource" url="jdbc:oracle:thin:@IP地址:1521:orcl" username="用户名" />           </Context>

View层 index.jsp

<%@ page contentType="text/html; charset=utf-8" %><%String nameStr = (String)request.getAttribute("name");%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>        <title>Spring Jsp</title><!--<link rel="stylesheet" type="text/css" href="styles.css">-->  </head>    <body>    <center>    <h2>Spring page</h2>    <%    out.println("Name-->" + nameStr);    %>        </center>  </body></html>

访问地址

http://localhost:8082/bitest/Sdemo.do