dbchild.java

一个基于struts框架开发的论坛系统,适合学习

package frm;

import java.sql.*;
import java.util.*;

public class DbChild {
	
	public static boolean save(Child c, Connection con) throws Exception {
		if(c==null || con==null) return false;
		String sql="";
		System.out.println("--Save:"+c);
		if(c.getId()==0 || c.getNew()){ 
			if(c.getId()==0){
				c.setId((int)getNext(con));
			}			
			sql="insert into frm_child (name,descf,status,f_id) values (?,?,?,?)";
		}else{
			sql="update frm_child set name=?,descf=?,status=? where f_id=?";
		}
		System.out.println(sql);
		PreparedStatement st=con.prepareStatement(sql);
		st.setString(1,c.getName());
		st.setString(2,c.getDesc());
		st.setInt(3,c.getStatus());
		st.setInt(4,c.getId());
		int i=st.executeUpdate();
		st.close();
		if(i>0){
			c.setNew(false);
			return true;
		}		 
		return false;
	}
	
	public static long getNext(Connection con) throws Exception{
		Statement st=con.createStatement();
		ResultSet rs=st.executeQuery("select frm_sequence.nextval from dual");
		long l=0;
		if(rs.next()){
			l=rs.getLong(1);
		}
		rs.close();
		st.close();
		return l;
	}
	
	public static Child getChildByID(int id, Connection con) throws Exception {
		 
		String sql="select * from frm_child where f_id="+String.valueOf(id);
		Statement st=con.createStatement();
		ResultSet rs=st.executeQuery(sql);
		Child c=null;
		if(rs.next()){
			c=new Child();
			c.setNew(false);
			c.setId(rs.getInt("f_id"));
			c.setName(rs.getString("NAME"));
			c.setDesc(rs.getString("descf"));
			c.setStatus(rs.getInt("status"));
		}
		return c;  
	}
	
	public static Collection getChildList(){	
		Collection ret=new ArrayList();
		Connection conn=null;
		try{
			conn=DataConnection.getConnection(); 
			ret=getChildList(conn);
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(conn!=null){
				try{
					conn.close();
				}catch(Exception e){}
			}
		}
		return ret;		 
	}
	
	public static Collection getChildList(Connection conn){
		Collection ret=new ArrayList();
		try{
			Statement stmt=conn.createStatement();
		    ResultSet rs=stmt.executeQuery("select * from frm_child order by f_id");
		    while(rs.next()){
		    	Child c=new Child();
		    	c.setNew(false);
				c.setId(rs.getInt("f_id"));
				c.setName(rs.getString("NAME"));
				c.setDesc(rs.getString("descf"));
				c.setStatus(rs.getInt("status"));
		        ret.add(c);
		     }
		    rs.close();
		    stmt.close(); 
		}catch(Exception e){
			e.printStackTrace();
		}
		return ret;		 
	}
	
	public static Collection getForumList(Connection conn){
		ArrayList<Child> ret=new ArrayList<Child>();
		//Collection ret=
		try{
			Statement stmt=conn.createStatement();
		    ResultSet rs=stmt.executeQuery("select * from frm_child where status="+Child.NORMAL+" order by f_id");
		    while(rs.next()){
		    	Child c=new Child();
		    	c.setNew(false);
				c.setId(rs.getInt("f_id"));
				c.setName(rs.getString("NAME"));
				c.setDesc(rs.getString("descf"));
				c.setStatus(rs.getInt("status"));
		        ret.add(c);
		     }
		    rs.close();
		    stmt.close(); 
		}catch(Exception e){
			e.printStackTrace();
		}
		for(int i=0;i<ret.size();i++){
			Child c=(Child)ret.get(i);
			c.setManager(getManager(conn,c.getId()));
			c.setLastDate(getLastDate(conn,c.getId()));
			c.setTopicNum(getTopicNum(conn,c.getId()));
		}
		//读取其他信息
		return ret;	
	}
	
	public static String getManager(Connection conn,int id){
		String ret="";
		try{
			Statement stmt=conn.createStatement();
		    ResultSet rs=stmt.executeQuery("select u_id,name from frm_user where FROUM_ID="+String.valueOf(id));
		    while(rs.next()){
		    	if(ret.length()>0) ret+=";";
		    	ret+=rs.getString("name")+"/"+rs.getString("u_id");
		     }
		    rs.close();
		    stmt.close(); 
		}catch(Exception e){
			e.printStackTrace();
		}
		return ret;
	}
	
	public static String getLastDate(Connection conn,int id){
		String ret="";
		try{
			Statement stmt=conn.createStatement();
		    ResultSet rs=stmt.executeQuery("select to_char(pub_date,'yyyy-mm-dd hh24:mm:ss') as ld from frm_topic where f_ID="+String.valueOf(id)+" and rownum<2 order by pub_date desc ");
		    if(rs.next()){		     
		    	ret+=rs.getString(1);
		     }
		    rs.close();
		    stmt.close(); 
		}catch(Exception e){
			e.printStackTrace();
		}
		return ret;
	}
	
	public static int getTopicNum(Connection conn,int id){
		int num=0;
		try{
			Statement stmt=conn.createStatement();
		    ResultSet rs=stmt.executeQuery("select count(*) from frm_topic where f_id="+id+" and status="+Topic.ST_NORAML);
		    if(rs.next()){		     
		    	num=rs.getInt(1);
		     }
		    rs.close();
		    stmt.close(); 
		}catch(Exception e){
			e.printStackTrace();
		}
		return num;
	}
	
}