condb.java

要实现功能有：发帖

package dao;
/**
 * 连接数据库的类
 * */
import java.sql.*;
import java.sql.Date;
import java.util.*;

import bean.*;
public class ConDB {
	private Connection con;
	private Statement sta;
	private PreparedStatement pres;
	private ResultSet res;
	//连接
	public ConDB(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bbs","root","mysql");
			sta = con.createStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}
	}
	//验证用户身份 
	/** @return 如果是普通的合法用户返回 1 合法的管理员返回 0 
	 *   如果是非法用户 返回 -1
	 * */
	public int testUser(String name,String pass) {
		String sql = "select grade from user where uname=? and upass=?";
		try {
			pres = con.prepareStatement( sql );
			pres.setString(1, name);
			pres.setString(2, pass);
			res  = pres.executeQuery();
			if ( res.next() ) {
				return res.getInt("grade");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return -1;
	}
	
	//查询所有帖子的简略表 生成框架左侧的列表
	public List view(){
		String sql = "select * from bbs order by bdate desc";
		List list = null;
		try {
			pres = con.prepareStatement( sql );
			res = pres.executeQuery();
			list= new ArrayList();
			while( res.next() ) {
				Bbs bbs = new Bbs();
				bbs.setBid( res.getInt("bid") );
				bbs.setBtitle( res.getString("btitle") );
				bbs.setAuthor( res.getString("author") );
				bbs.setBdate( res.getTimestamp("bdate") );
				list.add( bbs );
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return list;
	}
	//发帖 向Bbs表中添加纪录，成功之后调用回帖方法向Bdetail表中添加详细内容
	public boolean addMessge(Bbs bbs,Bdetail detail) {
		String sql = "insert into bbs(btitle,author,bdate) values(?,?,?)";
		String maxid = "select max(bid) from bbs";
		try {
			con.setAutoCommit(false);
			pres = con.prepareStatement( sql );
			pres.setString(1, bbs.getBtitle() );
			pres.setString(2, bbs.getAuthor());
			pres.setTimestamp(3, bbs.getBdate());
			pres.executeUpdate();
			pres = con.prepareStatement( maxid );
			res = pres.executeQuery();
			if ( res.next() ) {
				detail.setBid( res.getInt(1) );
				this.addResponse(detail);
				con.commit();
			}
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		return false;
	}
	//回帖
	public boolean addResponse(Bdetail detail){
		String sql = "insert into bdetail(bid,detail,resp,bdate) values(?,?,?,?)";
		boolean b = false;
		try {
			pres = con.prepareStatement( sql );
			pres.setInt(1, detail.getBid());
			pres.setString(2, detail.getDetail());
			pres.setString(3, detail.getResp());
			pres.setTimestamp(4, detail.getBdate());
			b = ( pres.executeUpdate() > 0 )? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return b;
	}
	//改帖 详细信息的主键作为条件进行修改 修改之前应判断是否有权限修改
	public boolean updateMessge(Bdetail detail) {
		boolean b = false;
		try {
			String sql = "update bdetail set detail=? where id=?";
			pres = con.prepareStatement( sql );
			pres.setString(1, detail.getDetail());
			pres.setInt(2, detail.getId());
			int yes = pres.executeUpdate();
			b = ( yes != 0 )? true : false; 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return b;
	}
	
	//查询帖子的详细内容
	public List getDetail(String sql){
		List list = null;
		try {
			sta = con.createStatement();
			res = sta.executeQuery( sql );
			list = new ArrayList();
			while( res.next() ) {
				Bdetail bdetail = new Bdetail();
				bdetail.setId( res.getInt("id") );
				bdetail.setBid( res.getInt("bid") );
				bdetail.setDetail( res.getString("detail") );
				bdetail.setResp( res.getString("resp") );
				bdetail.setBdate( res.getTimestamp("bdate") );
				list.add( bdetail );
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return list;
	}
	//得到符合条件的记录数（分页时用）
	public int getRecordCount(String sql){
		int num = 0;
		try {
			sta = con.createStatement();
			res = sta.executeQuery( sql );
			if( res.next() ) {
				num = res.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}
	
	//获得某一页的数据 
	//注意：得到的数据应该是一个列表而返回的是一个PageBean对象，而这类中又有一个代表列表记录的变量
	public List listData(String sql){
		List list = new ArrayList();
		try {
			sta = con.createStatement();
			res = sta.executeQuery( sql );
			while ( res.next() ) {
				Bdetail detail = new Bdetail();
				detail.setId( res.getInt("id") );
				detail.setBid(res.getInt("bid"));
				detail.setDetail(res.getString("detail"));
				detail.setResp(res.getString("resp"));
				detail.setBdate(res.getTimestamp("bdate"));
				list.add(detail);
			}	
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public int deleteMessge(Bdetail detail) {
		int num = -1;
		try {
			String sql = "select min(bdate) from bdetail where bid=?";
			pres = con.prepareStatement( sql );
			pres.setInt(1, detail.getBid());
			res = pres.executeQuery();
			if( res.next() ) {
				java.sql.Timestamp time = res.getTimestamp(1);
				int i = detail.getBdate().compareTo(time);
				String sqlone = null;
				if( i == 0) {
					sqlone = "delete from bdetail where bid=" + detail.getBid();
					String sqlbbs = "delete from bbs where bid=" + detail.getBid();
					pres = con.prepareStatement( sqlbbs );
					num = ( pres.executeUpdate() > 0 )? 0 : -1;
				} else {
					sqlone = "delete from bdetail where bdate='" + detail.getBdate()+"'";
					pres = con.prepareStatement( sqlone );
					num = ( pres.executeUpdate() > 0 )? 1 : -1;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return num;
	}
	
	//关闭数据库，释放资源
	public  void close() {
		if(res != null){
			try {
				res.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(sta!= null){
			try {
				sta.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(pres != null){
			try {
				pres.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(con != null){
			try {
				con.close();
				con = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}