/* 判断该类别下面是否有子类别 */public boolean HasSon(int parent) throws SQLException, NamingException
{
Connection conn=null;
PreparedStatement pstmt = null;
ResultSet rs=null;
int i=0;
try
{
String sql="select count(*) total from procat where parent=?";
conn = pool.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt .setInt(1,parent);
rs=pstmt.executeQuery();
if(rs.next()){
i = rs.getInt("total");
}
}
finally{
pool.cleanup(conn,pstmt,rs);
}
return i>0;
}/*按照深度得到当前类别下面所有类别,递归调用 */public void getCatalog(ArrayList list, int parent,int depth) throws SQLException,NamingException
{
Connection conn=null;
PreparedStatement pstmt = null;
ResultSet rs=null;
String sql="select * from procat where parent=? order by pos desc,catid desc";
try{
conn=pool.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1,parent);
rs = pstmt.executeQuery();
while (rs.next()){
CatalogMsg m=new CatalogMsg();
m.setCatID(rs.getInt("catID"));
m.setCatName(rs.getString("catName"));
m.setPos(depth);
list.add(m);
boolean hasson = HasSon(rs.getInt("catID"));
if(hasson){
getCatalog(list,rs.getInt("catID"),depth+1);
}
}
}finally
{
pool.cleanup(conn,pstmt,rs);
}
}
/* cleanup 释放连接 */
public void cleanup(Connection databaseConnection,
Statement statement1,ResultSet resultSet) throws SQLException {
try {
// Close the database connection and statement
if (statement1 != null) {
statement1.close();
}
if (resultSet !=null)
{
resultSet.close();
}
}
finally {
// Make sure we always try to close the connection, even
// if something went wrong trying to close a statement
if (databaseConnection != null) {
databaseConnection.close();
}
}
}
数据库是oralce9i,用SELECT sql_text,COUNT(*) FROM v$sql s , v$session se WHERE se.prev_hash_value =s.hash_value GROUP BY sql_text,检查连接发现 SQL_text count
select count(*) total from procat where parent=:1 6count 会持续增加,最高曾达到90,导致连接数过多,增加oracle连接数指标不治本,请问如何修改程序?
{
Connection conn=null;
PreparedStatement pstmt = null;
ResultSet rs=null;
int i=0;
try
{
String sql="select count(*) total from procat where parent=?";
conn = pool.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt .setInt(1,parent);
rs=pstmt.executeQuery();
if(rs.next()){
i = rs.getInt("total");
}
}
finally{
pool.cleanup(conn,pstmt,rs);
}
return i>0;
}/*按照深度得到当前类别下面所有类别,递归调用 */public void getCatalog(ArrayList list, int parent,int depth) throws SQLException,NamingException
{
Connection conn=null;
PreparedStatement pstmt = null;
ResultSet rs=null;
String sql="select * from procat where parent=? order by pos desc,catid desc";
try{
conn=pool.getConnection();
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1,parent);
rs = pstmt.executeQuery();
while (rs.next()){
CatalogMsg m=new CatalogMsg();
m.setCatID(rs.getInt("catID"));
m.setCatName(rs.getString("catName"));
m.setPos(depth);
list.add(m);
boolean hasson = HasSon(rs.getInt("catID"));
if(hasson){
getCatalog(list,rs.getInt("catID"),depth+1);
}
}
}finally
{
pool.cleanup(conn,pstmt,rs);
}
}
/* cleanup 释放连接 */
public void cleanup(Connection databaseConnection,
Statement statement1,ResultSet resultSet) throws SQLException {
try {
// Close the database connection and statement
if (statement1 != null) {
statement1.close();
}
if (resultSet !=null)
{
resultSet.close();
}
}
finally {
// Make sure we always try to close the connection, even
// if something went wrong trying to close a statement
if (databaseConnection != null) {
databaseConnection.close();
}
}
}
数据库是oralce9i,用SELECT sql_text,COUNT(*) FROM v$sql s , v$session se WHERE se.prev_hash_value =s.hash_value GROUP BY sql_text,检查连接发现 SQL_text count
select count(*) total from procat where parent=:1 6count 会持续增加,最高曾达到90,导致连接数过多,增加oracle连接数指标不治本,请问如何修改程序?
HasSon(Connection conn,int parent)
循环中创建是非常要不地的