bookbo.java

用j2ee开发的b/s通讯录. 欢迎各位使用

package y2javaee.xmal1.operation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

import y2javaee.xmal1.common.DBConnection;
import y2javaee.xmal1.entity.Book;

public class BookBo {

	private Connection conn;//用于保存数据库连接对象

	private Statement stm;//用于执行SQL语句

	private PreparedStatement ps;//用于执行SQL语句（预处理）

	private ResultSet rs;//用于保存查询的结果集
	/**
	 * 根据用户的ID查询用户相关的通讯录信息
	 * @param userId
	 * @return 通讯录信息列表
	 */
	public List selectAllBook(int userId) {
		List list = new ArrayList();
		String sql = "select * from book where userId=" + userId;
		try {

			conn = DBConnection.getConnection();
			stm = conn.createStatement();
			rs = stm.executeQuery(sql);
			while (rs.next()) {
				Book book = new Book();
				book.setId(rs.getInt("id"));
				book.setName(rs.getString("name"));
				book.setSex(rs.getString("sex"));
				book.setPhone(rs.getString("phone"));
				book.setAddress(rs.getString("address"));
				book.setMobilePhone(rs.getString("mobilePhone"));
				book.setCompany(rs.getString("company"));
				book.setComPhone(rs.getString("comPhone"));
				book.setComAddress(rs.getString("comAddress"));

				list.add(book);

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeResultSet(rs);
			DBConnection.closeStatement(stm);
			DBConnection.closeConnection();
		}
		return list;
	}
	/**
	 * 根据用户的ID和联系人与用户关系，查询通讯录信息
	 * @param userId
	 * @param relation
	 * @return	通讯录信息列表
	 */
	public List selectBookByRelation(int userId,String relation) {
		List list = new ArrayList();
		String sql = "select * from book where userId=? and relation= ? ";
		try {

			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setString(2, relation);
			rs = ps.executeQuery();

			while (rs.next()) {
				Book book = new Book();
				book.setId(rs.getInt("id"));
				book.setName(rs.getString("name"));
				book.setSex(rs.getString("sex"));
				book.setPhone(rs.getString("phone"));
				book.setAddress(rs.getString("address"));
				book.setMobilePhone(rs.getString("mobilePhone"));
				book.setCompany(rs.getString("company"));
				book.setComPhone(rs.getString("comPhone"));
				book.setComAddress(rs.getString("comAddress"));

				list.add(book);

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeResultSet(rs);
			DBConnection.closeStatement(stm);
			DBConnection.closeConnection();
		}
		return list;
	}
	/**
	 * 根据通讯录对象，操作数据库的添加功能
	 * @param book
	 * @return  影响数据行数
	 */
	public int insertBook(Book book) {
		String sql = "insert into book values(?,?,?,?,?,?,?,?,?,?) ";
		int count = 0;
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, book.getName());
			ps.setString(2, book.getSex());
			ps.setString(3, book.getPhone());
			ps.setString(4, book.getAddress());
			ps.setString(5, book.getMobilePhone());
			ps.setString(6, book.getCompany());
			ps.setString(7, book.getComPhone());
			ps.setString(8, book.getComAddress());
			ps.setString(9, book.getRelation());
			ps.setInt(10, book.getUserId());
			count = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeStatement(ps);
			DBConnection.closeConnection();
		}
		return count;

	}
	/**
	 * 根据通讯录ID删除相关信息
	 * @param id
	 * @return 影响数据库行数
	 */
	public int deleteBookById(String id) {
		String sql = "delete from book where id= ? ";
		int count = 0;
		try {
			conn = DBConnection.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, id);
			count = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeStatement(ps);
			DBConnection.closeConnection();
		}
		return count;
	}

}