JDBC 连接Oracle代码优化问题
以下代码是以前一同事写的，现在走了，发现conn rs stmt都没有关闭，又不知道从哪里下手，请各位指教！！
代码贴出来，高手们帮我看看！！
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Hashtable;
public class DBConnection {//用于数据库连接
public static Connection oracleConn()//建立连接ORACLE数据库
{
Hashtable hs = DataBaseInfo.getDBConfig();
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection((String)hs.get("ORACLE_URL"),(String)hs.get("ORACLE_UID"),(String)hs.get("ORACLE_PWD"));
return conn;
}catch(ClassNotFoundException e)
{
System.out.println(e.getMessage()+"类找不到");
return null;
}
catch(SQLException e)
{
System.out.println(e.getMessage());
return null;
}
catch(Exception e)
{
System.out.println("有错误："+e.getMessage());
return null;
}
}
}
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import duba.dbo.dbaccess.DBConnection;
public class DBOperate {//数据库操作类
public static boolean executeOperate(PreparedStatement pstmt) throws SQLException
{
if(pstmt.executeUpdate()!=0)
{
return true;
}
else
{
return false;
}
}
public static ResultSet executeGetResultSet(PreparedStatement pstmt) throws SQLException
{
return pstmt.executeQuery();
}
public static PreparedStatement getPreparedStatement(String sql) throws SQLException
{
return DBConnection.oracleConn().prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
}
public static int getRSsum(ResultSet rs) throws SQLException//获取该记录集里的记录数
{
rs.last();
int rowtotal = rs.getRow();
rs.beforeFirst();
return rowtotal;
}
}
public class ESorts_Operate {
public static boolean insertSort(E_Sorts sort)//添加类别
{
try
{
PreparedStatement pstmt = DBOperate.getPreparedStatement("insert into e_sorts(SORT_PID,SORT_NAME) values(?,?)");
pstmt.setInt(1, sort.getSort_pid());
pstmt.setString(2, sort.getSort_name());
return DBOperate.executeOperate(pstmt);
}
catch(Exception e)
{
System.out.println(e.getMessage());
return false;
}
}
public static boolean deleteSort(E_Sorts sort)//删除类别
{
try
{
PreparedStatement pstmt = DBOperate.getPreparedStatement("delete e_sorts where sort_id=?");
pstmt.setInt(1, sort.getSort_id());
return DBOperate.executeOperate(pstmt);
}
catch(Exception e)
{
System.out.println(e.getMessage());
return false;
}
}
public static boolean updateSort(E_Sorts sort)//修改类别
{
try
{
PreparedStatement pstmt = DBOperate.getPreparedStatement("update e_sorts set SORT_PID=?,SORT_NAME=? where sort_id=?");
pstmt.setInt(1, sort.getSort_pid());
pstmt.setString(2, sort.getSort_name());
pstmt.setInt(3, sort.getSort_id());
return DBOperate.executeOperate(pstmt);
}
catch(Exception e)
{
System.out.println(e.getMessage());
return false;
}
}
public static E_Sorts getESortByid(int sort_id)//根据sort_id取得该id的信息
{
try
{
PreparedStatement pstmt = DBOperate.getPreparedStatement("select * from e_sorts where SORT_ID=?");
pstmt.setInt(1, sort_id);
return getESort(DBOperate.executeGetResultSet(pstmt));
}
catch(Exception e)
{
System.out.println(e.getMessage());
return null;
}
}
private static E_Sorts getESort(ResultSet rs) throws SQLException
{
if(rs.next())
{
return fillESort(rs);
}
return null;
}
private static E_Sorts fillESort(ResultSet rs) throws SQLException
{
E_Sorts sort = new E_Sorts();
sort.setSort_id(rs.getInt("SORT_ID"));
sort.setSort_pid(rs.getInt("SORT_PID"));
sort.setSort_name(rs.getString("SORT_NAME"));
return sort;
}
}
[解决办法]
网上有很多
拿一个改一下就可以用了
package e_office.systemframework.pool;
/**
*编者:李国庆
*类的描述信息:此类为一个数据库连接池类，是一个单态的类，其中对数据库连接的信息
*都放在一个属性文件中，可以进行方便的设置***
*最后修改时间:2006-1-12 10:30*********
*/
import java.sql.*;
import java.io.*;
import java.util.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.ResourceBundle;
/**
* 这是一个数据库连接池
*访问属性文件：mypool.properties
*返回一个Connection
*
*/
public class ConnectionPool{
private int minConn; //最少连接数
private int maxConn; //最大连接数
private String user; //用户名
private String password; //数据库密码
private int connAmount; //现有连接个数
private Stack connStack; //使用Stack保存数据库连接
private String dry; //驱动
private String connString; //连接字符串
private static ConnectionPool connectionPool;
private int waitTime; //忙时的等告待时间
static ResourceBundle bundle=null; //属性文件读取
/**
*返回一个实例。如果是第一次调用此方法则个建一个实例
*/
public static synchronized ConnectionPool getInstance(){
if(connectionPool==null){
connectionPool = new ConnectionPool();
}
return connectionPool;
}
/**
*读属性文件得到数据库连接信息及连接池信息
*/
public void readProperties(){
try
{
bundle=ResourceBundle.getBundle("properties.mypool");
this.user=bundle.getString("user");
this.password = bundle.getString("password");
this.connString=bundle.getString("connString");
this.dry = bundle.getString("dry");
this.minConn=Integer.parseInt(bundle.getString("minConn"));
System.out.println("this mincon:"+this.minConn);
this.maxConn=Integer.parseInt(bundle.getString("maxConn"));
this.waitTime=Integer.parseInt(bundle.getString("waitTime"));
}
catch(Exception w) { w.toString(); }
System.out.println("初始化成功..........");
}
private ConnectionPool(){
readProperties();
this.connStack = new Stack();
System.out.println(this.dry);
try{
Class.forName(this.dry);
}catch(Exception e){
e.printStackTrace();
}
for(int i = 0;i<this.minConn;i++){
System.out.println("myPool:"+i);
connStack.push(newConnection());
}
}
/**
*从连接池得到连接
*/
public synchronized Connection getConnection(){
Connection conn = null;
System.out.println("user connection:"+this.connStack.size());
if(!this.connStack.empty()){
conn = (Connection)connStack.pop();
System.out.println("得到一个连接");
}else if(this.connAmount<this.maxConn){
conn = newConnection();
}else{
try{
wait(this.waitTime);
System.out.println("等待");
return getConnection();
}catch(Exception e){
e.printStackTrace();
}
}
return conn;
}
/**
/*释放连接
*/
public synchronized void freeConnection(Connection con){
this.connStack.push(con);
System.out.println("归还连接");
notifyAll();
}
/**
*创建新连接
*/
public Connection newConnection(){
Connection conn=null;
try{
conn = DriverManager.getConnection(this.connString,this.user,this.password);
this.connAmount++;
System.out.println("连接池创建一个连接"+conn.toString());
return conn;
}catch(Exception e){
e.printStackTrace();
return null;
}
}
}
[解决办法]
[解决办法]
你想做什么，配连接池吗？
你的server用的什么？大部分都支持连接池配置 不用自己做
[解决办法]