用Tomcater实现连接池。但如何做一个JavaBean来分别获取数据集和执行Delete语句？(50分)

  • 主题发起人 主题发起人 nzfsoft
  • 开始时间 开始时间
N

nzfsoft

Unregistered / Unconfirmed
GUEST, unregistred user!
我的环境Apache2.0.42+Tomcat4.1.24+Win2Kpro
为了后人方便，我公布一下Tomcater的链接池的方法：
创建链接池：
在在"</Host>"之前,"</Context>"之后添加：
<Context path="/oa" debug="5"do
cBase="E:/Application/OA" reloadable="true" crossContext="true">
这一句已经在前面添加了。如有不符，以此句为准。
<Logger className="org.apache.catalina.logger.FileLogger"
prefix="localhost_OA_log." suffix=".txt" timestamp="true"/>
<Resource name="jdbc/TestDB"
auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/TestDB">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<!-- Maximum number of dB connections in pool. Make sure you
configure your mysqld max_connections large enough to handle
all of your db connections. Set to 0 for no limit.
-->
<parameter>
<name>maxActive</name>
<value>100</value>
</parameter>
<!-- Maximum number of idle dB connections to retain in pool.
Set to 0 for no limit.
-->
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<!-- Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
-->
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<!-- MySQL dB username and password for dB connections -->
<parameter>
<name>username</name>
<value>sa</value>
</parameter>
<parameter>
<name>password</name>
<value>nrcrd</value>
</parameter>
<!-- Class name for mm.mysql JDBC driver -->
<parameter>
<name>driverClassName</name>
<value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
</parameter>
<!-- The JDBC connection url for connecting to your MySQL dB.
The autoReconnect=true argument to the url makes sure that the
mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
connection. mysqld by default closes idle connections after 8 hours.
-->
<parameter>
<name>url</name>
<value>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=NJGH</value>
<!--<value>jdbc:mysql://localhost:3306/javatest?autoReconnect=true</value>-->

</parameter>
</ResourceParams>
再建一个DBTest.java;
package foo;
import javax.naming.*;
import javax.sql.*;
import java.sql.*;
public class DBTest {
String foo = "Not Connected";
String bar = "Not Bar";

public void init() {
try{
Context ctx = new InitialContext();
if(ctx == null )
throw new Exception("Boom - No Context");
DataSource ds =
(DataSource)ctx.lookup(
"java:comp/env/jdbc/TestDB");
if (ds != null) {
Connection conn = ds.getConnection();

if(conn != null) {
foo = "Got Connection "+conn.toString();
Statement stmt = conn.createStatement();
ResultSet rst =
stmt.executeQuery(
"select * from news");
if(rst.next()) {
foo=rst.getString(2);
bar=rst.getString(1);
}
conn.close();
}
}
}catch(Exception e) {
e.printStackTrace();
}
}
public String getFoo() { return foo;
}
public String getBar() { return bar;}
}
再建 一个测试文件test.jsp在OA/目录下
<%@ page contentType="text/html;charset=gb2312"%>
<html>
<head>
<title>DB Test</title>
</head>
<body>
<%
foo.DBTest tst = new foo.DBTest();
tst.init();
%>
<h2>Results</h2>
Foo <%= tst.getFoo() %><br/>
Bar <%= tst.getBar() %>
</body>
</html>
运行后可以看到结果。
或者新建一个文件 testdb.jsp
<%@ page contentType="text/html;
charset=gb2312" language="java" errorPage="" %>
<%@ page import= "javax.sql.* "%>
<%@ page import= "java.sql.* "%>
<%@ page import= "javax.naming.* "%>
<%try{
Context initCtx = new InitialContext();
Context ctx = (Context) initCtx.lookup("java:comp/env");
//获取连接池对象
Object obj = (Object) ctx.lookup("jdbc/TestDB");

//类型转换
javax.sql.DataSource ds = (javax.sql.DataSource)obj;

Connection conn = ds.getConnection();

Statement stmt = conn.createStatement();

String strSql = " insert into test(id,name) values('00001','holen')";

stmt.executeUpdate(strSql);

strSql = " select id,name from test ";

ResultSet rs = stmt.executeQuery(strSql);
if(rs.next()){
out.println(rs.getString(1));
out.println(rs.getString(2));
}
}
catch(Exception ex){
ex.printStackTrace();
throw new SQLException("cannot get Connection pool.");
}
%>
 
以下这个JavaBean已经实现了返还值。
package foo;
import javax.naming.*;
import javax.sql.*;
import java.sql.*;
public class DBTest {
String foo = "Not Connected";
String bar = "Not Bar";

public void init() {
try{
Context ctx = new InitialContext();
if(ctx == null )
throw new Exception("Boom - No Context");
DataSource ds =
(DataSource)ctx.lookup(
"java:comp/env/jdbc/TestDB");
if (ds != null) {
Connection conn = ds.getConnection();

if(conn != null) {
foo = "Got Connection "+conn.toString();
Statement stmt = conn.createStatement();
ResultSet rst =
stmt.executeQuery(
"select * from news");
if(rst.next()) {
foo=rst.getString(2);
bar=rst.getString(1);
}
conn.close();
}
}
}catch(Exception e) {
e.printStackTrace();
}
}
public String getFoo() { return foo;
}
public String getBar() { return bar;}
}
 
我想写成这样的结构。
Public void init() {}用来初始化链接。
Public ResultSet Open(String sql){}用来获取数据集
Public void Excute(String sql){}用来执行Update语句。
我该如何改写？
 
不用这么麻烦啊
直接在Tomcat设置（Web控制台）Datasource，程序中使用这个Datasource就可以了
拿到连接后就有一大堆解决方案了，我现在是用Hibernate
推荐用Tomcat4.1.24
 
我就是用Tomcat4.1.24.
楼上，你帮我看看呢。我那个JavaBean如何改写呀？
 
自己搞定了。为了后来同胞查，公布如下：
package foo;
import javax.naming.*;
import javax.sql.*;
import java.sql.*;
public class DBTest {
Connection conn = null;
ResultSet rst = null;
Statement stmt=null;
String ConStr="Not Connect";
Context ctx=null;
DataSource ds=null;


public void init() {
try{
ctx = new InitialContext();
if(ctx == null )
throw new Exception("Boom - No Context");
ds =(DataSource)ctx.lookup("java:comp/env/jdbc/TestDB");
if (ds != null) //{
conn = ds.getConnection();

}
catch(Exception e)
{
e.printStackTrace();
}
}
public ResultSet Open(String sql)
{
try
{
if(conn != null) {//ConStr = "Got Connection "+conn.toString();

stmt = conn.createStatement();
rst = stmt.executeQuery(sql);
//return rst;
}
}
catch(Exception e)
{
e.printStackTrace();
}
return rst;
}

public int Excute(String sql) throws SQLException,java.io.UnsupportedEncodingException
{ int i=0;
try
{
if(conn != null)
{
conn.setAutoCommit(false);
stmt = conn.createStatement();
i=stmt.executeUpdate(sql);

conn.commit();

stmt.close();

conn.close();
}
}
catch(SQLException e)
{ conn.rollback();
e.printStackTrace();
}
return i ;
}
}
 
测试用的JSP文件。
<%@ page contentType="text/html;
charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<jsp:useBean id="dataset" scope="request" class="foo.DBTest"/>
<html>
<head>
<title>DB Pool Test</title>
</head>
<body>
<%
int t;
int mtotal;
int size;
t=0;

dataset.init() ;
//初始化,无返回值。
String sql="select * from news";
ResultSet rs=dataset.Open(sql);
while(rs.next()) {%>
您的第1字段：<%=rs.getString(1)%>
您的第2字段：<%=rs.getString(2)+"<br>"%>
<%}%>
</body>
</html>
 
为知道为什么，我无法成功执行Excute(sql);
每次都是Excute false ；
(曾经成功过。但大部分时间不成功)
如下：
<%@ page contentType="text/html;
charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<jsp:useBean id="dataset" scope="request" class="foo.DBTest"/>
<html>
<head>
<title>DB Pool Test</title>
</head>
<body>
<%
int t;
int mtotal;
int size;
t=88;
String sql;
dataset.init() ;
//初始化,无返回值。
sql="select * from members";
ResultSet rs=dataset.Open(sql);
while(rs.next()) {%>
您的第1字段：<%=rs.getString(1)%>
您的第2字段：<%=rs.getString(2)%>
您的第3字段：<%=rs.getString(3)+"<br>"%>

<%}%>
<%
sql="insert members (xm,mm) values('33','44')";
t=dataset.Excute(sql);
if (t>0)
out.println("Updated="+t);
else

out.println("Excute false");
dataset.Close();
%>
</body>
</html>
 
package foo;
import javax.naming.*;
import javax.sql.*;
import java.sql.*;
public class DBTest {
Connection conn = null;
ResultSet rst = null;
Statement stmt=null;
String ConStr="Not Connect";
Context ctx=null;
DataSource ds=null;

public void init() {
try{
ctx = new InitialContext();
if(ctx == null )
throw new Exception("Boom - No Context");
ds =(DataSource)ctx.lookup("java:comp/env/jdbc/TestDB");
if (ds != null) //{
conn = ds.getConnection();


}
catch(Exception e)
{
e.printStackTrace();
}
}
public ResultSet Open(String sql)
{
try
{
if(conn != null) {//ConStr = "Got Connection "+conn.toString();

stmt = conn.createStatement();
rst = stmt.executeQuery(sql);
//return rst;
}
}
catch(Exception e)
{
e.printStackTrace();
}
return rst;

}

public int Excute(String sql) throws SQLException,java.io.UnsupportedEncodingException
{ int i=0;
try
{
if(conn != null)
{
conn.setAutoCommit(false);
if (stmt==null)
{
stmt=conn.createStatement();
System.out.println("Stmt is new");
}
else
{ System.out.println("Stmt had be created");
}
i=stmt.executeUpdate(sql);
conn.commit();
}
}
catch(SQLException e)
{ conn.rollback();
e.printStackTrace();
}
return i ;
}
public boolean insert(String sql) {
try { //conn.setAutoCommit(false);
stmt=conn.createStatement();
stmt.executeUpdate(sql);
conn.commit();
stmt.close();
conn.close();
} catch (Exception e) {
try
{conn.rollback();}
catch(Exception e1)
{}
System.err.println(e.getMessage());
return false;
}
return true;
}
public void Close(){
try{
if (rst!=null) rst.close();
if (stmt!=null) stmt.close();
if (conn !=null) conn.close();}
catch(SQLException e)
{ e.printStackTrace();
}
}
}
/*测试Bean的JSP文件
<%@ page contentType="text/html;
charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<jsp:useBean id="dataset" scope="request" class="foo.DBTest"/>
<html>
<HEAD>

</HEAD>
<body>
<%
int t;
int mtotal;
int size;
boolean b;
t=88;
String sql;
dataset.init() ;
//初始化,无返回值。
sql="select * from members";
ResultSet rs=dataset.Open(sql);
//查询后返回一个结果集，用一个ResultSet的变量rst去接受，注意要定义。
//dataset.Excute(sql);
//更新操作，无返回值。
while(rs.next()) {%>
您的第1字段：<%=rs.getString(1)%>
您的第2字段：<%=rs.getString(2)%>
您的第3字段：<%=rs.getString(3)+"<br>"%>
<%}%>
<%out.print("数据库操作成功，恭喜你");%>
<%
out.println("<HR>");
sql="delete FROM members WHERE (id <= 335)";
t=dataset.Excute(sql);
if (t>0 )
out.println("Delte OK="+t);
else

out.println("Delete false");
//************************************************
out.println("<HR>");
sql="insert members (xm,mm) values('33','44')";
t=dataset.Excute(sql);
if (t>0 )
out.println("insert OK="+t);
else

out.println("insert false");

dataset.Close();
%>
</body>
</html>
*/
 
问题搞定了。
 
世上无英雄.遂使狗熊得分...唉.
 
您必须登录或注册才可回复。

Similar threads

蓝
请教我为何在jb7下不能使用连接池(100分)
回复
2
查看
418
thtfsyh
T
后退
顶部