oracle自带的连接池的使用以及连接SQL2K数据库的测试

类别:Java 点击:0 评论:0 推荐:

分为三个文件:第一个文件是引用ORACLE自带的连接池的类,第二个文件是连接SQL2K的类文件,第三个是一个JSP文件,用于显示结果

******************
OraPooledSQL.java
******************
______________________________________________________________________
/*
 * Created on 2004-7-9
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
package myclass.DBconn;

/**
 * @author Ericsson
 *
 * To change the template for this generated type comment go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.pool.*;

public class 
{
private PooledConnection dbpool;

/**
@param ConnectionURL
连接名 如:jdbc:oracle:thin:@192.168.1.232:1521:orcl
@param UserID 用户名
@param PassWord 用户密码
*/
public OraPooledSQL(String ConnectionURL,String UserID,String PassWord)
{

try{

OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource();

ocpds.setURL(ConnectionURL);

ocpds.setUser(UserID);

ocpds.setPassword(PassWord);

dbpool = ocpds.getPooledConnection();

System.out.println("connect_test Success");
}
catch(Exception ex)
{
System.err.println("Error in PooledSQL-construct : ");
ex.printStackTrace(System.err);
}

}//end OraPooledSQL

//close dbpool
protected void finalize()
{
if( dbpool != null )
{
try
{
dbpool.close();
}
catch(Exception ex)
{
}
}
}

/**
用于更新、添加或删除的SQL语句
@param SQL SQL语句字串,如:insert into tablename values(id,......)
*/
public int Update(String SQL)
{
Statement stmt = null;
int rc = 0;

Connection connection = null;

try
{
connection = dbpool.getConnection();
stmt = connection.createStatement();
rc = stmt.executeUpdate(SQL);
}

catch( Exception ex )
{
System.err.println("Error in Update - OraPooledSQL : ");
ex.printStackTrace(System.err);
}

return rc;

} //end Update()


/**
用于查询的SQL语句
@param SQL SQL语句字串,如:select * from tablename
*/
public ResultSet Query(String SQL)
{
Statement stmt = null;
ResultSet rs = null;
Connection connection = null;

try
{
connection = dbpool.getConnection();
stmt = connection.createStatement();
rs = stmt.executeQuery(SQL);
}

catch( Exception ex )
{
System.err.println("Error in Query - SQLBean : ");
ex.printStackTrace(System.err);
}

return rs;

} //end Query

} //end Class

______________________________________________________________________

***************
DBconn.java
***************
______________________________________________________________________
/*
 * Created on 2004-7-8
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
package myclass.DBconn;

import java.sql.*;
/**
 * @author Ericsson
 *
 * To change the template for this generated type comment go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
public class DBconn {
 String url = "jdbc:odbc:lmt";
 String DBDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
 public static Connection con = null;
 ResultSet rs = null;
 Statement stmt;

  public DBconn() {
 try{

   Class.forName(DBDriver);//加载数据库驱动程序

 }//try
 catch(java.lang.ClassNotFoundException e){//出错情况的处理
   System.out.println("DBconn():" + e.getMessage());
   }

  }//DBconn()

  /**
   * executeQuery操作,用于数据查询,主要是Select
   * @param sql 查询字段
   * @return 数据集
   * @throws SQLException 捕捉错误
   */
   public ResultSet executeQuery(String sql){
  rs = null;
  try {
    con = DriverManager.getConnection(url,"sa","");
    stmt = con.createStatement();
    rs = stmt.executeQuery(sql);
    }//try
  catch(SQLException ex){//出错情况的处理
   System.err.println("aq.executequery:" + ex.getMessage());
  }
  return rs;
   }//executeQuery();
     /**
   * executeUpdate操作,用于数据更新,主要是Update,Insert
   * @param sql 查询字段
   * @throws SQLException 捕捉错误
   */
  public void executeUpdate(String sql) throws SQLException {
 
    try {
     stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
     stmt.executeUpdate(sql);
    }
    catch (SQLException ex) {
     ex.printStackTrace();
     System.out.println("dbTrans.executeUpdate:"+ex.getMessage());
     throw ex;
    }
  }

 

}//class
______________________________________________________________________

***************
db_test.jsp
***************
______________________________________________________________________

<%@ page contentType="text/html; charset=GBK" %>
<%@ page import = "java.net.URL"%>
<%@ page import = "java.sql.*"%>
<%@ page import = "myclass.DBconn.OraPooledSQL" %>

<html>
<head>
<title>数据库连接测试</title>
</head>
<jsp:useBean id="DBconn" scope="session" class="myclass.DBconn.DBconn" />
<jsp:setProperty name="DBconn" property="*" />

<body>
<%
 java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("yyyy年MM月dd日");  
 java.util.Date currentTime_1 = new java.util.Date(); 
 out.print(formatter.format(currentTime_1)+"<br>"); 
 
 OraPooledSQL orcl = new OraPooledSQL("jdbc:oracle:thin:@192.168.1.232:1521:orcl","webstat","khkj");
 ResultSet orcl_rs = orcl.Query("SELECT * FROM s_table");
 
 ResultSet Rs = DBconn.executeQuery("SELECT * FROM test");
 
%>


<table width="180" border="1" align="left" cellpadding="0" cellspacing="0" bordercolorlight="#C0C0C0" bordercolordark="#FFFFFF">
    <tr align="center" bgcolor="#99CCFF"><td colspan="2">连接SQL2K测试</td></tr>
    <%while(Rs.next()){  //循环显示查询结果 %>
    <tr>
      <td><%out.print(Rs.getString("name"));%></td>
      <td><%out.print(Rs.getString("sex"));%></td>
    </tr>
    <%}
    Rs.close();%>
  </table>
 
<table width="180" border="1" align="left" cellpadding="0" cellspacing="0" bordercolorlight="#C0C0C0" bordercolordark="#FFFFFF">
    <tr align="center" bgcolor="#99CCFF"><td colspan="2">oracle连接池测试</td></tr>
    <%while(orcl_rs.next()){  //循环显示查询结果 %>
    <tr>
      <td><%out.print(orcl_rs.getString("name"));%></td>
      <td><%out.print(orcl_rs.getString("caption"));%></td>
    </tr>
    <%}
    orcl_rs.close();%>
  </table> 
 
</body>
</html>
______________________________________________________________________

本文地址:http://com.8s8s.com/it/it14639.htm