dbtopic.java

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

package frm;


import java.sql.*;
import java.util.*;
//import java.text.*;


public class DbTopic {
	
	public static boolean updateAttach(Topic c,Connection con)throws Exception{
		if(c==null || con==null) return false;
		String sql="";
		System.out.println("--Update_attach,File="+c.getFile()+",type="+c.getType()+",id="+c.getId());
	 	sql="update frm_topic set att_file=?,att_type=? where topic_id=?";		 
		System.out.println(sql);
		PreparedStatement st=con.prepareStatement(sql);	  
		st.setString(1,c.getFile());
		st.setString(2,c.getType()); 	
		st.setLong(3,c.getId()); 
		int i=st.executeUpdate();
		st.close();
		if(i>0){
			return true;
		}
		return false;
	}
	
	public static boolean updateReplyDate(Connection con,int topic)throws Exception{
		if(con==null) return false;
		String sql="";		 
	 	sql="update frm_topic set reply_date=sysdate,order_date=sysdate where topic_id="+topic;	 
		Statement st=con.createStatement(); 		 
		int i=st.executeUpdate(sql);
		st.close();
		if(i>0){
			return true;
		}
		return false;
	}
	
	public static boolean save(Topic c, Connection con) throws Exception {
		if(c==null || con==null) return false;
		String sql="";
		System.out.println("--Save:"+c.getTitle()+",len="+c.getTitle().getBytes().length+",User="+c.getUser());
	 
		c.setId((int)getNext(con)); 
		sql="insert into frm_topic (f_id,title,content,att_file,"+
		    "att_type,pub_user,topic_id,status,pub_date,read_num,"+
		    "reply_num,reply_date,is_top,order_date) values (?,?,?,?,?,?,?,"+
		    Topic.ST_NORAML+",sysdate,0,0,null,"+Topic.TOP_NOT+",sysdate)";
		 
		System.out.println(sql);
		PreparedStatement st=con.prepareStatement(sql);
		st.setInt(1,c.getChild());
		st.setString(2,c.getTitle());
		st.setString(3,c.getContent());
		st.setString(4,c.getFile());
		st.setString(5,c.getType());
		st.setString(6,c.getUser());		
		st.setLong(7,c.getId()); 
		int i=st.executeUpdate();
		st.close();
		if(i>0){
			return true;
		}else{
			c.setId(0);
		}
		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;
	}
	
	private static Topic getTopic(ResultSet rs,boolean name) throws SQLException {
		Topic t=new Topic();
		t.setId(rs.getLong("topic_id"));
		t.setChild(rs.getInt("f_id"));
		t.setTitle(rs.getString("title"));
		t.setContent(rs.getString("content"));
		t.setUser(rs.getString("pub_user"));
		t.setReadNum(rs.getInt("read_num"));
		t.setReplyNum(rs.getInt("reply_num")); 
 		t.setReplyTime(rs.getString("reply_date"));
		t.setDate(rs.getString("pub_date"));
		t.setStatus(rs.getInt("status"));
		t.setTop(rs.getInt("is_top"));
		t.setFile(rs.getString("att_file"));
		t.setType(rs.getString("att_type"));	 
		if(t.getReplyTime()==null){
			t.setReplyTime("");
		}
		if(name){
			t.setUserName(rs.getString("userName"));
		}
		return t;
	}
	
	 
	
	public static boolean listTopicBySQL(Connection con,String sql,Page p) throws SQLException {
		Statement st=con.createStatement();
		ResultSet rs=st.executeQuery(sql);
		ResultSetMetaData mt=rs.getMetaData(); 		
		int cols=mt.getColumnCount();
		boolean userName=false;
		for(int i=0;i<cols;i++){
			if("username".equalsIgnoreCase(mt.getColumnLabel(i+1))){
				userName=true;
				break;
			}
		}
		Topic u=null;
		int from=(p.getPageNo()-1)*p.getPageLen();
		int to=from+p.getPageLen();
		int total=0;
		ArrayList<Topic> data=new ArrayList<Topic>();
		while(rs.next()){
			if(total<from || total>to){
				total++;
				continue;
			}
			u=getTopic(rs,userName);
			data.add(u);
			total++;
		}
		p.setData(data, total, cols);
		rs.close();
		st.close();
		return true;
	}
	
	public static Topic getTopicBySQL(Connection con,String sql) throws SQLException { 
		Statement st=con.createStatement();
		ResultSet rs=st.executeQuery(sql);
		ResultSetMetaData mt=rs.getMetaData(); 		
		int cols=mt.getColumnCount();
		boolean userName=false;
		for(int i=0;i<cols;i++){
			if("username".equalsIgnoreCase(mt.getColumnLabel(i+1))){
				userName=true;
				break;
			}
		}
		Topic u=null;
		if(rs.next()){		 
			u=getTopic(rs,userName); 
		}
		rs.close();
		st.close();
		return u;
	}
}