dboperation.java

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

package me.work.services;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import me.work.beans.MessageBean;
import me.work.beans.ReplyBean;
import me.work.beans.UserBean;
import me.work.utils.ConnectDBUtil;

public class DBOperation {
	/**
	 * process user login
	 * 
	 * @param userType
	 *            the type of user such as admin or common user
	 * @return true or false
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public boolean userLogin(String userType, String uName, String uPassword)
			throws ClassNotFoundException, SQLException {
		// check paramters
		if (uName == null || uPassword == null) {
			return false;
		}
		if ("".equals(uName.trim()) || "".equals(uPassword.trim())) {
			return false;
		}
		Connection con = null;
		try {
			con = ConnectDBUtil.getConnection();
			String sql = null;
			// test the type of user who is loggin
			if (userType != null && userType.equals("Admin")) {
				sql = "select * from [admin] where adminName = ? and adminPassword=?";
			} else {
				sql = "select * from [user] where userName = ? and userPassword= ?";
			}
			// create a PreparedStatement
			PreparedStatement ps = con.prepareStatement(sql);
			// set the paramters
			ps.setString(1, uName);
			ps.setString(2, uPassword);
			// excute quary
			ResultSet rs = ps.executeQuery();
			// if there is at least one item
			if (rs.next()) {
				// return true
				return true;
			}
		} finally {
			// close the connection
			ConnectDBUtil.closeConnection(con);
		}

		return false;
	}

	/**
	 * get the userid by name
	 * 
	 * @param name
	 * @return userid 0 means no this user!
	 * @throws Exception
	 */
	public int getUserIDByName(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 userID from [user] where userName=?";
			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 Exception
	 */
	public String getUserNameByID(int userID) throws Exception {
		// test if the name is null
		if (userID == 0) {
			throw new Exception("paramter 'name' is null");
		}
		Connection con = null;
		int uID = userID;
		try {
			con = ConnectDBUtil.getConnection();
			String sql = "select userName from [user] where userID=?";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, uID);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getString(1);
			}

		} finally {
			// close the connction
			ConnectDBUtil.closeConnection(con);
		}
		return null;
	}

	/**
	 * add msg to DB
	 * 
	 * @param title
	 *            msg title
	 * @param content
	 *            msg content
	 * @param userName
	 *            the name of someone who leave the message
	 * @return true mean add successfully
	 * @throws Exception
	 */
	public boolean addMessage(MessageBean msg, String userName)
			throws Exception {
		// check paramters
		if (msg == null || userName == null || "".equals(userName.trim())) {
			return false;
		}
		Connection con = null;
		try {
			con = ConnectDBUtil.getConnection();
			String sql = "insert messages (msgDate,msgTitle,msgContent,msgReplyTimes,userID) values(getDate(),?,?,0,?)";

			String content = msg.getMsgContent().replaceAll("\n", "<br>").toString();
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, msg.getMsgTitle());
			ps.setString(2, content);
			int id = this.getUserIDByName(userName);

			if (id != 0) {
				ps.setInt(3, id);
			} else {
				return false;
			}
			if (ps.executeUpdate() == 1) {
				return true;
			}
		} finally {
			ConnectDBUtil.closeConnection(con);
		}
		return false;
	}

	/**
	 * 
	 * @param userName
	 * @return
	 * @throws Exception
	 */
	public List<MessageBean> getMsgList(String userName) throws Exception {
		List<MessageBean> list = new ArrayList<MessageBean>();
		MessageBean msg = null;
		Connection con = null;
		int userID = this.getUserIDByName(userName);
		try {
			con = ConnectDBUtil.getConnection();
			String sql = "select * from messages where userID =?";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, userID);
			ResultSet rs = ps.executeQuery();
			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));
				list.add(msg);

			}

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

	/**
	 * register user in DB
	 * 
	 * @param user
	 *            userBean represent the user
	 * @return true register successfully false failed
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public boolean registerUser(UserBean user) throws ClassNotFoundException,
			SQLException {
		// check paramter
		if (user == null) {
			return false;
		}
		Connection con = null;
		try {
			con = ConnectDBUtil.getConnection();
			// create sql sentence
			String sql = "insert [user] (userName,userPassword,userEmail) values(?,?,?) ";
			// get a prepared statement
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setString(1, user.getUserName());
			ps.setString(2, user.getUserPassword());
			ps.setString(3, user.getUserEmail());
			// excute inssert
			if (ps.executeUpdate() == 1) {
				return true;
			}

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

	/**
	 * delete the user message
	 * 
	 * @param msgID
	 *            message id
	 * @return true delete successfully
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public boolean deleteMsgByID(int msgID) throws ClassNotFoundException,
			SQLException {
		if (msgID == 0) {
			return false;
		}
		Connection con = null;
		try {
			con = ConnectDBUtil.getConnection();

			String sql = "{call deleteMsg (?)}";
			CallableStatement cs = con.prepareCall(sql);
			cs.setInt(1, msgID);

			if (cs.executeUpdate() != 0) {
				return true;
			}
		} finally {
			ConnectDBUtil.closeConnection(con);
		}

		return false;
	}

	/**
	 * get message entity referenced by msgid
	 * 
	 * @param msgID
	 * @return MessageBean
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public MessageBean getMessageByID(int msgID) throws ClassNotFoundException,
			SQLException {
		if (msgID == 0) {
			return null;
		}
		MessageBean msg = new MessageBean();
		Connection con = null;
		try {
			con = ConnectDBUtil.getConnection();
			String sql = "select * from messages where msgID = ?";
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, msgID);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				msg.setMsgID(msgID);
				msg.setMsgDate(rs.getString(2));
				msg.setMsgTitle(rs.getString(3));
				msg.setMsgContent(rs.getString(4));
				msg.setMsgReplies(rs.getInt(5));
			}
			return msg;
		} finally {
			ConnectDBUtil.closeConnection(con);
		}
	}

	/**
	 * 
	 * @param msgID
	 * @return
	 * @throws Exception
	 */
	public List<ReplyBean> getReplyListOfSpecifiedMessage(int msgID)
			throws Exception {
		if (msgID == 0) {
			return null;
		}
		List<ReplyBean> list = new ArrayList<ReplyBean>();
		ReplyBean reply = null;
		Connection con = null;