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

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网站查一下使用数据库连接池的文章,上面好像提到过这个问题的解决方法。