tomcat5.5 oracle连接池配置说明
ResultSet rs = null;
Connection conn = null;
Statement stmt = null;
try {
//1.注册驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到连接
conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:icss","lj","123");
//3.创建Statement
stmt = conn.createStatement();
//4.得到ResultSet
rs = stmt.executeQuery("select * from user_t");
//5.操作resultSet
while(rs.next()){
System.out.println(rs.getString("name"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
//6.释放资源
//6.1.关闭ResultSet
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//6.2 关闭Statement;
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//6.3.关闭Connection
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
tomcat5.5 &oracle连接池配置说明
一，将oracle驱动classes12.jar复制到tomcat安装路径下的“/common/lib”中去。
二，打开tomcat安装路径下/conf/中的server.xml文件加入一段代码（红色字体为添加内容）：
tomcat 5.5中使用数据库连接池时server.xml的配置：
在</Host>标签的上面加上以下内容：
例如工程名为：smalljdbc
<Context debug="0"
docBase="SmallJdbc"
path="/SmallJdbc" reloadable="true">
<Resource auth="Container"
driverClassName="oracle.jdbc.driver.OracleDriver"
logAbandoned="true" loginTimeout="300" maxActive="200"
maxIdle="20" maxWait="3000" minIdle="10" name="jdbc/smalljdbc"
password="数据库密码" removeAbandoned="true"
removeAbandonedTimeout="600" type="javax.sql.DataSource"
url="jdbc:oracle:thin:@127.0.0.1:1521:全局数据库名" username="数据库的用户名"/>
</Context>
</Host>
程序要连接数据库时用到下面代码时：
ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/smalljdbc");
这里要保持连接字符串中后两位的jdbc/smalljdbc和上面配置文件name="jdbc/smalljdbc"中的内容一致即可。
三，jsp测试页面dbsTest.jsp ：
<%@ page contentType="text/html; charset=gb2312"
language="java" import="java.sql.*" errorPage="" %>
<%@page import="javax.naming.*"%>
<%@page import="javax.sql.*"%>
<%
DataSource ds = null;
InitialContext ctx=new InitialContext();
ds=(DataSource)ctx.lookup("java:comp/env/jdbc/smalljdbc ");
Connection conn = ds.getConnection();
System.out.println("ds="+ds);
System.out.println("conn="+conn);
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>JNDI 测试</title>
</head>
<body>
ds=<%=ds%>
<br>
count=<%=conn%>
</body>
</html>
如果在ie浏览器和后台console都会显示
count=org.apache.tomcat.dbcp.dbcp.PoolableConnection@17af46e ds=org.apache.tomcat.dbcp.dbcp.BasicDataSource@8491b8
类似的字符，说明连接成功！！！
四，由于tomcat不同版本连接池的配置是有区别的，大家要注意自己安装的tomcat版本和安装方法的对应！
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test {
public static void main(String[] args){
try {
ResultSet rs = null;
Connection conn = null;
Statement stmt = null;
// PreparedStatement pstm=null;
Class.forName( "org.gjt.mm.mysql.Driver" );
conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/mysql","root","root");
stmt=conn.createStatement();
// stmt.execute("insert into test (name,other) values('asd','fgh')");
stmt.execute("update test set name='lj' where name='lee'");
// stmt.execute("delete from test where name='123'");
// String sql="insert into test (name,other) values('asd','fgh')";
// pstm=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
// stmt=(Statement) conn.createStatement();
//rs=(ResultSet) stmt.executeQuery("select * from test");
// pstm.executeQuery(sql);
// while(rs.next()){
// System.out.println(rs.getString("name"));
// }
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public PageNum queryAllScheme(int pagecurrent,String userid) {
ResultSet rs=null;
PreparedStatement pstm=null;
Connection conn=null;
conn=ConnectionFactory.getConnection();
PageNum pn=new PageNum();
pn.setPagecurrent(pagecurrent);
int pagesize=5; //a page show 5 records
//int pagecurrent; //current page 's num
int rsnum; //resultset's number
int pageall = 0; //need to divide how mang pages
ArrayList<Scheme> list=new ArrayList<Scheme>();
try {
String strQuery="select * from meet_scheme where scheme_user_id=?";
pstm = conn.prepareStatement(strQuery,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
pstm.setString(1,userid);
rs=pstm.executeQuery();
rs.last();
rsnum=rs.getRow(); //get the number of resultset
pageall=((rsnum-1)/pagesize)+1;
rs.beforeFirst();
if(pagecurrent!=1){
rs.absolute(pagesize*(pagecurrent-1));
}
while((--pagesize>=0)&& rs.next()){
Scheme scheme=new Scheme();
scheme.setSchemeId(rs.getString("scheme_id"));
scheme.setSchemeName(rs.getString("scheme_Name"));
scheme.setSchemeContent(rs.getString("scheme_Content"));
scheme.setSchemeStartDate(rs.getString("scheme_Start_Date"));
scheme.setSchemeEndDate(rs.getString("scheme_End_Date"));
scheme.setUserId(rs.getString("scheme_user_id"));
list.add(scheme);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DatabaseUtils.release(rs, pstm, conn);
}
pn.setList(list);
pn.setPageall(pageall);
return pn;
}
package com.icss.oa.utils;
import java.util.List;
public class PageNum {
private int pageall;
private List list;
private int pagecurrent;
public int getPagecurrent() {
return pagecurrent;
}
public void setPagecurrent(int pagecurrent) {
this.pagecurrent = pagecurrent;
}
public int getPageall() {
return pageall;
}
public void setPageall(int pageall) {
this.pageall = pageall;
}
public List getList() {
return list;
}
public void setList(List list) {
this.list = list;
}
}
ResultSet rs=null;
PreparedStatement pstm=null;
Connection conn=null;
conn=ConnectionFactory.getConnection();
try {
String strQuery="delete from meet_scheme where scheme_id=? and scheme_user_id=?";
pstm = conn.prepareStatement(strQuery,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
pstm.setString(1, schemeId);
pstm.setString(2, userid);
rs=pstm.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DatabaseUtils.release(rs, pstm, conn);
}
<%@ page pageEncoding="gbk" import="com.icss.oa.entity.Scheme,java.util.List,com.icss.oa.utils.PageNum" %>
<head>
<link href='<%=session.getAttribute("basepath")+"inc/basic.css"%>' rel="stylesheet" type="text/css">
</head>
<body>
<table border="1" id=PrintA class="table">
<tr>
<td>日程名称</td>
<td>日程内容</td>
<td>日程开始时间</td>
<td>日程结束时间</td>
<td>操 作</td>
<td>操 作</td>
</tr>
<%
PageNum pn=(PageNum)request.getAttribute("pn");
List list=pn.getList();
for(int i=0;i<list.size();i++){ %>
<tr>
<td><%=((Scheme)list.get(i)).getSchemeName()%></td>
<td><%=((Scheme)list.get(i)).getSchemeContent()%></td>
<td width="80"><%=((Scheme)list.get(i)).getSchemeStartDate()%></td>
<td width="80"><%=((Scheme)list.get(i)).getSchemeStartDate()%></td>
<td><a href="/meet/servlet/ActionServlet?actiontype=scheme&methodtype=delScheme&schemeId=<%=((Scheme)list.get(i)).getSchemeId()%>">注销</a></td>
<td><a href="/meet/servlet/ActionServlet?actiontype=scheme&methodtype=initScheme&schemeId=<%=((Scheme)list.get(i)).getSchemeId()%>">修改</a></td>
</tr>
<% }%>
</table>
<% for(int j=1;j<=pn.getPageall();j++){
if(pn.getPagecurrent()==j){%>
第<%=j%>页
<% }else{%>
<a href="/meet/servlet/ActionServlet?actiontype=scheme&methodtype=showscheme&pc=<%=j%>">第<%=j%>页</a>
<%
}
} %>
</body>