在JSP里有两种实现的办法,一种是用JNDI(Java Naming Directory Interface),这可能和应用服务器有关,如果是Resin,先在resin.conf里定义
java 代码
- <resource-ref>
- <res-ref-name>jdbc/oracle</res-ref-name>
- <res-type>javax.sql.DataSource</res-type>
- <init-param driver-name="oracle.jdbc.driver.OracleDriver"/>
- <init-param url="jdbc:oracle:thin:@192.168.1.1:1521:oracle"/>
- <init-param user="system"/>
- <init-param password="manager"/>
- <init-param max-connections="20"/>
- <init-param max-idle-time="30"/>
- </resource-ref>
- 如果为Tomcat,在Server.xml里面定义,有关的资料可以查文档,然后在jsp里这样用
- try{
- javax.naming.Context env = (Context)new InitialContext().lookup("java:comp/env");
- javax.sql.DataSource pool=(javax.sql.DataSource) env.lookup("jdbc/oracle");
- }catch(Exception e){System.err.println("Exception error:"+e.getMessage());}
- try {
- Connection conn = pool.getConnection();
- }catch(Exception e){System.out.println("Exception error:"+e.getMessage());}
- 通过这段代码,你就获得从连接池里获得了一个连接conn。如果想用普通的连接池,那只能用JavaBean了,先写一个ConnectionPool的java的类,然后直接从连接池中获得连接,下面是我一个连接池的JavaBean
- ConnectionPool.java如下:
- import java.io.PrintStream;
- import java.sql.Connection;
- import java.util.Vector;
- // Referenced classes of package com.ilovejsp.sql:
- // DataSource, PooledConnection
- public class ConnectionPool
- {
- private Vector pool;
- private int size;
- DataSource db;
- public ConnectionPool()
- {
- pool = null;
- size = 0;
- db = new DataSource();
- }
- public void setSize(int value)
- {
- if(value > 1)
- size = value;
- }
- public int getSize()
- {
- return size;
- }
- public synchronized void initPool()
- throws Exception
- {
- try
- {
- for(int x = 0; x < size; x++)
- {
- Connection conn = db.getConnection();
- if(conn != null)
- {
- PooledConnection pcon = new PooledConnection(conn);
- addConnection(pcon);
- }
- }
- }
- catch(Exception e)
- {
- System.err.println(e.getMessage());
- }
- }
- private void addConnection(PooledConnection pcon)
- {
- if(pool == null)
- pool = new Vector(size);
- pool.addElement(pcon);
- }
- public synchronized void releaseConnection(Connection conn)
- {
- int x = 0;
- do
- {
- if(x >= pool.size())
- break;
- PooledConnection pcon = (PooledConnection)pool.elementAt(x);
- if(pcon.getConnection() == conn)
- {
- System.err.println("Release Connection".concat(String.valueOf(String.valueOf(x))));
- pcon.setInUse(false);
- break;
- }
- x++;
- }
- while(true);
- }
- public synchronized Connection getConnection()
- throws Exception
- {
- PooledConnection pcon = null;
- for(int x = 0; x < pool.size(); x++)
- {
- pcon = (PooledConnection)pool.elementAt(x);
- if(!pcon.inUse())
- {
- pcon.setInUse(true);
- return pcon.getConnection();
- }
- }
- try
- {
- Connection conn = db.getConnection();
- pcon = new PooledConnection(conn);
- pcon.setInUse(true);
- pool.addElement(pcon);
- }
- catch(Exception e)
- {
- System.err.println("Exception error:".concat(String.valueOf(String.valueOf(e.getMessage()))));
- }
- return pcon.getConnection();
- }
- public synchronized void emptyPool()
- {
- for(int x = 0; x < pool.size(); x++)
- {
- System.err.println("Closing Jdbc Connection".concat(String.valueOf(String.valueOf(x))));
- PooledConnection pcon = (PooledConnection)pool.elementAt(x);
- if(!pcon.inUse())
- {
- pcon.close();
- continue;
- }
- try
- {
- Thread.sleep(3000L);
- pcon.close();
- }
- catch(Exception e)
- {
- System.out.println("Exception :".concat(String.valueOf(String.valueOf(e.getMessage()))));
- }
- }
- db.close();
- }
- }
- testpool.jsp内容如下:
- <%@ page language="java" contentType="text/html;charset=gb2312"%>
- <%@ page import="java.sql.*"%>
- <HTML>
- <HEAD>
- <TITLE>系统数据信息</TITLE>
- </HEAD>
- <BODY>
- <%ConnectionPool db=new ConnectionPool();
- Connection conn=db.getConnection();
- Statement stmt=conn.createStatement();
- String sql1="select * from pg_database ";
- ResultSet rs=stmt.executeQuery(sql1);
- %>
- <TABLE><TR><TD>系统数据库信息</TD></TR>
- <TR><TD>
- <%while(rs.next()) {
- %>
- <%=rs.getString(1)%>
- <%}
- rs.close();%>
- </TR></TD>
- <TABLE><TR><TD>系统字段信息</TD></TR>
- <TR><TD>
- <%String sql2="select * from pg_type";
- rs=stmt.executeQuery(sql2);
- while(rs.next()) {
- %>
- (<%=rs.getString(1)%>)
- <%}
- rs.close();
- db.close();%>
- </TR></TD>
- </BODY>
- </HTML>