这儿有一个数据连接,大家看这儿有哪些问题

03-06-05 genner

package tools;

import javax.naming.*;
import javax.sql.*;
import java.sql.*;
import oracle.jdbc.pool.*;

public class Conn{
private Connection conn = null;
private Statement st = null;
private Statement st2 = null;

private ResultSet rs = null;
private PreparedStatement pst = null;

public Conn() throws Exception{
//'查找连接池并连接
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@192.168.0.1:1521:oracle");
ods.setUser("username");
ods.setPassword("password");
conn = ods.getConnection();
}

public void close() throws Exception{
if(st != null){
st.close();
st = null;
}
if(pst != null){
st.close();
st = null;
}
conn.close();
}

public void createStatement() throws Exception{
st = conn.createStatement();
}
public void createStatement2() throws Exception{
st2 = conn.createStatement();
}
public void createStatementB() throws Exception{
st = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);
}

public void setAutoCommit(boolean bl) throws Exception{
conn.setAutoCommit(bl);
}

public Connection getConnection() throws Exception{
return conn;
}

public void execute(String sql) throws Exception{
setAutoCommit(true);
pst = conn.prepareStatement(sql);
pst.executeUpdate();
conn.commit();
pst.close();
pst = null;
}

public ResultSet executeQuery(String s) throws Exception {
createStatement();
rs = st.executeQuery(s);
return rs;
}

public ResultSet executeQuery() throws Exception {
rs = pst.executeQuery();
return rs;
}

public ResultSet executeQueryB(String s) throws Exception {
createStatementB();
rs = st.executeQuery(s);
return rs;
}

public void insert(String s) throws Exception {
setAutoCommit(true);
pst = conn.prepareStatement(s);
}

public void executeInsert() throws SQLException {
pst.executeUpdate();
}

public void executeUpdate(String s) throws Exception {
setAutoCommit(true);
st = conn.createStatement();
st.executeUpdate(s);
}

public int getCount(String table,String condition) throws Exception{
String _condition = "";
int RowCount;
if(condition.length() != 0)
_condition = " where "+condition;
createStatement();
String sql = "select count(*) as RC from "+table+_condition;
rs = st.executeQuery(sql);
rs.next();
RowCount = rs.getInt("RC");
return RowCount;
}

public String[][] SelectData(
String Table,
String[] Column,
String[] Column_Attr,
String Condition,
String Order) throws Exception{

createStatement();

//求出个数
int count = getCount(Table,Condition);
String[][] Results = new String[count][Column.length];

String _Column = "";
for(int i = 0; i < Column.length; i++)
{
_Column+=Column+",";
}
_Column = _Column.substring(0,_Column.length()-1);

String _Condition = "";
if(Condition.length() != 0)
_Condition = " where "+Condition;

String sql = "select "+_Column+" from "+Table+_Condition+" "+Order;
rs = st.executeQuery(sql);
int m=0;
while(rs.next()) {
for(int i = 0; i < Column.length; i++){
if(Column_Attr.equalsIgnoreCase("String"))
Results[m] = rs.getString(Column);
else if(Column_Attr.equalsIgnoreCase("Int"))
Results[m] = String.valueOf(rs.getInt(Column));
else if(Column_Attr.equalsIgnoreCase("Date"))
if(rs.getDate(Column) != null)
Results[m] = rs.getDate(Column).toString();
else
Results[m] = "";
else if (Column_Attr.equalsIgnoreCase("Time"))
if(rs.getDate(Column) != null)
Results[m] = rs.getDate(Column).toString()+" "+rs.getTime(Column).toString();
else
Results[m] = "";
}
m++;
}
return Results;
}

public void setString(int i,String s) throws SQLException {
pst.setString(i, s);
}

public void setInt(int i, int j) throws SQLException{
pst.setInt(i,j);
}

public void setFloat(int i,float f) throws SQLException{
pst.setFloat(i,f);
}

public void setDouble(int i ,double d) throws SQLException{
pst.setDouble(i,d);
}

public void setLong(int i,long l) throws SQLException {
pst.setLong(i,l);
}

public void setDate(int i ,Date d) throws SQLException {
pst.setDate(i,d);
}

public void setBoolean(int i,boolean b) throws SQLException{
pst.setBoolean(i,b);
}

public void setTimestamp(int i,Timestamp t) throws SQLException{
pst.setTimestamp(i,t);
}

}
1。在执行关闭操作时,特别是执行insert操作时会有异常!
2。这个程序在写法上有哪些不足,在思想上应有哪些值得改进!
3、用这个执行查询操作时,数据库有近10万记录时很慢,并通过常有内存不足的提示。如果查询人数多的时候给导致服务器崩掉!
  请教各位高手赐教!我在这儿先谢了!

efan1
2003-06-05 17:51
大概是你使用了connection后不要关闭Connection
应为connection是要返回到缓冲池中重新利用的。
不对的地方,请大家指正。

genner
2003-06-06 15:31
谢谢楼上的意见!
 但我认为关闭Conection,是没有问题的!实际上它并没有关闭连接池同数据库的连接,如果不关闭的话,连接池最大连接数是有限的。用完之后,可能会造成死锁!
  关键是在数据记录达到近10万条时,会要求很多的内存,并导玫数据库死掉!这一点我不是很明白!

efan1
2003-06-06 19:54
你可以上ibm网站查一下使用数据库连接池的文章,上面好像提到过这个问题的解决方法。

猜你喜欢