daomybean.java

这个QQ通信系统项目是我之前发过类似的一个大的整合

package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import rule.MyBean;
import rule.Message;

public class DAOMyBean implements DAO {
	public int insert(MyBean mybean) {
		String sql = "insert into Account values(?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;

		int n = 0;
		try {
			ps = DBConnection.getConn().prepareStatement(sql);
			ps.setString(1, mybean.getNickName());
			ps.setInt(2, mybean.getQq());
			ps.setString(3, mybean.getPassword());
			ps.setString(4, mybean.getPortrait());
			ps.setString(5, "off");
			ps.setString(6, mybean.getName());
			ps.setString(7, mybean.getSex());
			ps.setString(8, mybean.getAge());
			ps.setFloat(9, mybean.getOnlineTime());
			ps.setString(10, mybean.getInfo());
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(ps);
		}
		return n;
	}

	public int delete(int qq) {
		String sql = "delete Message where mqq = ?";
		PreparedStatement ps = null;

		int n = 0;
		try {
			ps = DBConnection.getConn().prepareStatement(sql);
			ps.setFloat(1, qq);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(ps);
		}
		return n;
	}

	public int update(String sql) {
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = DBConnection.getConn().prepareStatement(sql);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(ps);
		}
		return n;
	}

	public List<MyBean> queryAll() {
		List<MyBean> list = new ArrayList<MyBean>();
		String sql = "select * from Account";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = DBConnection.getConn().prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				MyBean myBean = new MyBean();
				myBean.setNickName(rs.getString("nickName"));
				myBean.setQq(rs.getInt("qq"));
				myBean.setPassword(rs.getString("password"));
				myBean.setPortrait(rs.getString("portrait"));
				myBean.setState(rs.getString("state"));
				myBean.setName(rs.getString("name"));
				myBean.setSex(rs.getString("sex"));
				myBean.setAge(rs.getString("age"));
				myBean.setOnlineTime(rs.getFloat("onlineTime"));
				myBean.setInfo(rs.getString("info"));
				list.add(myBean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}

		return list;
	}

	public List<MyBean> queryFriend(int qq) {
		List<MyBean> list = new ArrayList<MyBean>();
		String sql = "select * from Account where qq in(select fqq from Friend where mqq = ?)";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = DBConnection.getConn().prepareStatement(sql);
			ps.setInt(1, qq);
			rs = ps.executeQuery();
			while (rs.next()) {
				MyBean myBean = new MyBean();
				myBean.setNickName(rs.getString("nickName"));
				myBean.setQq(rs.getInt("qq")); // 要按查询出的顺序结果读取数据
				myBean.setPassword(rs.getString("password"));
				myBean.setPortrait(rs.getString("portrait"));
				myBean.setState(rs.getString("state"));
				myBean.setName(rs.getString("name"));
				myBean.setSex(rs.getString("sex"));
				myBean.setAge(rs.getString("age"));
				myBean.setOnlineTime(rs.getFloat("onlineTime"));
				myBean.setInfo(rs.getString("info"));

				list.add(myBean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}

		return list;
	}

	public MyBean queryInfo(int qq) {
		MyBean myBean = null;
		String sql = "select * from Account where qq = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = DBConnection.getConn().prepareStatement(sql);
			ps.setInt(1, qq);
			rs = ps.executeQuery();

			if (rs.next()) {
				myBean = new MyBean();
				myBean.setNickName(rs.getString("nickName"));
				myBean.setQq(rs.getInt("qq"));
				myBean.setPassword(rs.getString("password"));
				myBean.setPortrait(rs.getString("portrait"));
				myBean.setState(rs.getString("state"));
				myBean.setName(rs.getString("name"));
				myBean.setSex(rs.getString("sex"));
				myBean.setAge(rs.getString("age"));
				myBean.setOnlineTime(rs.getFloat("onlineTime"));
				myBean.setInfo(rs.getString("info"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}

		return myBean;
	}

	public int insertFriend(int mqq, int fqq) {
		String sql = "insert into Friend values(?,?)";
		PreparedStatement ps = null;
		int n = 0;
		try {
			ps = DBConnection.getConn().prepareStatement(sql);
			ps.setInt(1, mqq);
			ps.setInt(2, fqq);
			n = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(ps);
		}
		return n;
	}

	public boolean queryCheckLogin(int qq, String password) {

		boolean b = false;
		String sql = "select count(*) from Account where qq = ? and password = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = DBConnection.getConn().prepareStatement(sql);
			ps.setInt(1, qq);
			ps.setString(2, password);
			rs = ps.executeQuery();
			while (rs.next()) {
				if (rs.getInt(1) == 1) {
					b = true;
				}
			}

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}

		return b;
	}

	public boolean queryAddFriend(int mqq, int fqq) {

		boolean b = true;
		String sql = "select count(*) from friend where mqq = ? and fqq = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = DBConnection.getConn().prepareStatement(sql);
			ps.setInt(1, mqq);
			ps.setInt(2, fqq);
			rs = ps.executeQuery();
			if (rs.next()) {
				if (rs.getInt(1) != 0) {
					b = false;// != 0 必须这样！
				}
			}

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}

		return b;
	}

	public List<Message> queryMsg(int qq) {
		List<Message> list = new ArrayList<Message>();
		String sql = "select * from Message where mqq = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = DBConnection.getConn().prepareStatement(sql);
			ps.setInt(1, qq);
			rs = ps.executeQuery();
			while (rs.next()) {
				Message message = new Message(rs.getString("type"), rs
						.getInt("source"), rs.getString("times"));
				list.add(message);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps);
		}
		return list;
	}
}