dbreply.java

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

package frm;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DbReply {
	
	public static boolean updateAttach(Reply 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_reply set att_file=?,att_type=? where rec_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 save(Reply c, Connection con) throws Exception {
		if(c==null || con==null) return false;
		String sql="";
		System.out.println("--Save:"+c+",User="+c.getUser());
	 
		c.setId((int)getNext(con)); 
		sql="insert into frm_reply (topic_id,rec_id,title,content,att_file,"+
		    "att_type,pub_user,status,pub_date) values (?,?,?,?,?,?,?,"+
		    Topic.ST_NORAML+",sysdate)";
		 
		System.out.println(sql);
		PreparedStatement st=con.prepareStatement(sql);
		st.setLong(1,c.getTopicID());
		st.setLong(2,c.getId());
		st.setString(3,c.getTitle());
		st.setString(4,c.getContent());
		st.setString(5,c.getFile());
		st.setString(6,c.getType());
		st.setString(7,c.getUser()); 
		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 Reply getReply(ResultSet rs,boolean name) throws SQLException {
		Reply t=new Reply();
		t.setId(rs.getLong("rec_id"));
		t.setTopicID(rs.getInt("topic_id"));
		t.setTitle(rs.getString("title"));
		t.setContent(rs.getString("content"));
		t.setUser(rs.getString("pub_user")); 
		t.setDate(rs.getString("pub_date"));
		t.setStatus(rs.getInt("status")); 
		t.setFile(rs.getString("att_file"));
		t.setType(rs.getString("att_type"));		 
		 
		if(name){
			t.setUserName(rs.getString("userName"));
		}
		return t;
	}
	
	 
	
	public static boolean listReplyBySQL(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;
			}
		}
		Reply u=null;
		int from=(p.getPageNo()-1)*p.getPageLen();
		int to=from+p.getPageLen();
		int total=0;
		ArrayList<Reply> data=new ArrayList<Reply>();
		while(rs.next()){
			if(total<from || total>to){
				total++;
				continue;
			}
			u=getReply(rs,userName);
			data.add(u);
			total++;
		}
		p.setData(data, total, cols);
		rs.close();
		st.close();
		return true;
	}
	
	public static Reply getReplyBySQL(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;
			}
		}
		Reply u=null;
		if(rs.next()){		 
			u=getReply(rs,userName); 
		}
		rs.close();
		st.close();
		return u;
	}
}