dboperation.java

简单的流言板系统,用myeclipse进行编写的,采用简单的jstl+javabean+servlet+jsp,属于三层架构.

		try {
			con = ConnectDBUtil.getConnection();
			String sql = "select * from reply where msgID = ? order by replyID";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, msgID);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				reply = new ReplyBean();
				reply.setMsgID(msgID);
				reply.setReplyID(rs.getInt(1));
				reply.setUserName(this.getUserNameByID(rs.getInt(2)));
				reply.setReplyDate(rs.getString(4));

				reply.setContent(rs.getString(5));
				// add to list
				list.add(reply);
			}
			return list;
		} finally {
			ConnectDBUtil.closeConnection(con);
		}
	}

	/**
	 * get all messages for admin
	 * 
	 * @return
	 * @throws Exception
	 */
	public List<MessageBean> getAllMessages(String flag) throws Exception {
		List<MessageBean> list = new ArrayList<MessageBean>();
		MessageBean msg = null;
		Connection con = null;
		try {
			con = ConnectDBUtil.getConnection();
			String sql = null;
			if (flag == null) {
				sql = "select * from messages ";
			} else {
				sql = "select * from messages where msgReplyTimes = 0";
			}
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);

			while (rs.next()) {
				msg = new MessageBean();
				int uid = rs.getInt(1);
				msg.setMsgID(uid);
				msg.setMsgDate(rs.getString(2));
				msg.setMsgTitle(rs.getString(3));
				msg.setMsgContent(rs.getString(4));
				msg.setMsgReplies(rs.getInt(5));
				msg.setUserName(this.getUserNameByID(uid));
				list.add(msg);

			}

		} finally {
			ConnectDBUtil.closeConnection(con);
		}
		return list;
	}

	/**
	 * get list for paging page
	 * 
	 * @param begin
	 * @param items
	 * @return
	 * @throws Exception
	 */
	public List getMessagePageList(int begin, int items, boolean isAll)
			throws Exception {
		List list = new ArrayList();
		MessageBean msg = null;
		int ai = begin + items - 1;
		String sql;
		if (!isAll) {
			sql = "select * from (select top "
					+ items
					+ " * from (select top "
					+ ai
					+ " * from (select top "
					+ ai
					+ " * from messages where msgReplyTimes = 0) m order by msgID desc) mt) mmt order by msgID asc";

		} else {
			sql = "select * from (select top "
					+ items
					+ " * from (select top "
					+ ai
					+ " * from (select top "
					+ ai
					+ " * from messages) m order by msgID desc) mt) mmt order by msgID asc";
		}
		Connection con = null;
		try {
			con = ConnectDBUtil.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				msg = new MessageBean();
				msg.setMsgID(rs.getInt(1));
				msg.setMsgDate(rs.getString(2));
				msg.setMsgTitle(rs.getString(3));
				msg.setMsgContent(rs.getString(4));
				msg.setMsgReplies(rs.getInt(5));
				msg.setUserName(this.getUserNameByID(rs.getInt(6)));

				list.add(msg);
			}
		} finally {
			ConnectDBUtil.closeConnection(con);
		}
		return list;
	}

	/**
	 * get user list
	 * 
	 * @param begin
	 * @param items
	 * @return
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	public List getUserList(int begin, int items) throws ClassNotFoundException, SQLException {
		List list = new ArrayList();
		UserBean user= null;
		int ai = begin + items - 1;
		String 	sql = "select * from (select top "
					+ items
					+ " * from (select top "
					+ ai
					+ " * from (select top "
					+ ai
					+ " * from [user]) m order by userID desc) mt) mmt order by userID asc";
	
		Connection con = null;
		try {
			con = ConnectDBUtil.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				user = new UserBean();
				user.setUserID(rs.getInt(1));
				user.setUserName(rs.getString(2));
				user.setUserEmail(rs.getString(4));	

				list.add(user);
			}
		} finally {
			ConnectDBUtil.closeConnection(con);
		}
		return list;
	}

	/**
	 * @param isAll <true> select all the messages<false>select the messages that unreplied.
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public int getMsgCount(boolean isAll) throws ClassNotFoundException,
			SQLException {
		Connection con = null;
		String sql;
		if (isAll) {
			sql = "select count(*) from messages";
		} else {
			sql = "select count(*) from messages where msgReplyTimes = 0";
		}
		try {
			con = ConnectDBUtil.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			if (rs.next()) {
				return rs.getInt(1);
			}
		} finally {
			ConnectDBUtil.closeConnection(con);
		}
		return 0;
	}

	/**
	 * get the count of user in DB
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public int getUserCount() throws ClassNotFoundException, SQLException {
		Connection con = null;
		String sql = "select count(*) from [user]";
		try {
			con = ConnectDBUtil.getConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			if (rs.next()) {
				return rs.getInt(1);
			}
		} finally {
			ConnectDBUtil.closeConnection(con);
		}
		return 0;
	}

	/**
	 * reply message by ID
	 * 
	 * @param msgID
	 * @return
	 * @throws Exception
	 */
	public boolean replyMessage(int msgID, String name, String content)
			throws Exception {
		if (msgID == 0) {
			return false;
		}
		content = content.replaceAll("\n", "<br>").toString();
		Connection con = null;
		try {
			con = ConnectDBUtil.getConnection();
			String sql = "{call replyMsg (?,?,?)}";
			CallableStatement cs = con.prepareCall(sql);
			cs.setInt(1, msgID);
			int userID = this.getAdminIDByName(name);
			cs.setInt(2, userID);
			cs.setString(3, content);
			if (cs.executeUpdate() != 0) {
				return true;
			}

		} finally {
			ConnectDBUtil.closeConnection(con);
		}

		return false;
	}

	public int getAdminIDByName(String name) throws Exception {
		// test if the name is null
		if (name == null) {
			throw new Exception("paramter 'name' is null");
		}
		Connection con = null;
		String uName = name;
		try {
			con = ConnectDBUtil.getConnection();
			String sql = "select adminID from [admin] where adminName=?";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, uName);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}

		} finally {
			// close the connction
			ConnectDBUtil.closeConnection(con);
		}
		// when no this user
		return 0;
	}
	/**
	 * 
	 * @param userID
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	
	public boolean deleteUser(int userID) throws ClassNotFoundException, SQLException{
		Connection con = null;
		String sql = "{call deleteUser (?)}" ;
		try{
			con = ConnectDBUtil.getConnection();
			CallableStatement cs = con.prepareCall(sql);
			cs.setInt(1, userID);
			if(cs.executeUpdate() != 0){
				return true;
			}
			
		}finally{
			ConnectDBUtil.closeConnection(con);
		}
		return false;
	}
	/**
	 * 
	 * @param repID
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public boolean deleteReply(int repID,int msgID) throws ClassNotFoundException, SQLException{
		Connection con = null;
		String sql = "{call deleteReply (?,?)}" ;
		try{
			con = ConnectDBUtil.getConnection();
			CallableStatement cs = con.prepareCall(sql);
			cs.setInt(1, repID);
			cs.setInt(2, msgID);
			if(cs.executeUpdate() != 0){
				return true;
			}
			
		}finally{
			ConnectDBUtil.closeConnection(con);
		}
		return false;
	}
}