bookdaoimpl.java

使用JSP+MSSQL开发的在线通讯录系统.包含源码

package com.e98.addressbook.dao.impl;

import com.e98.addressbook.entity.*;
import com.e98.addressbook.common.*;

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

import com.e98.addressbook.dao.BookDao;
import com.e98.addressbook.entity.Book;

public class BookDaoImpl implements BookDao {

	Connection conn = null;

	ResultSet rs = null;

	PreparedStatement pst = null;

	public List<Book> selectAllBook(int userId) {
		List<Book> list = new ArrayList<Book>();
		conn = ConnectionManager.getConn();
		String sql = "select id,address,comAddress,company,comPhone,mobilePhone,name,phone,relation,userId,sex from Book where userId=? ";
		try {
			pst = conn.prepareStatement(sql);
			pst.setLong(1, userId);
			rs = pst.executeQuery();
			while (rs.next()) {
				Book book = new Book();
				book.setAddress(rs.getString("address"));
				book.setComAddress(rs.getString("comAddress"));
				book.setCompany(rs.getString("company"));
				book.setComPhone(rs.getString("comPhone"));
				book.setId(rs.getInt("id"));
				book.setMobilePhone(rs.getString("mobilePhone"));
				book.setName(rs.getString("name"));
				book.setSex(rs.getString("sex"));
				book.setPhone(rs.getString("phone"));
				book.setRelation(rs.getInt("relation"));
				book.setUserId(rs.getInt("userId"));
				list.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionManager.closeResultSet(rs);
			ConnectionManager.closePreparedStatement(pst);
			ConnectionManager.colseConnection(conn);
		}

		return list;
	}

	public boolean deleteBook(int id) {
		boolean flag = false;
		conn = ConnectionManager.getConn();
		String sql = "delete from book where id =?";
		try {
			pst = conn.prepareStatement(sql);
			pst.setLong(1, id);
			int result = pst.executeUpdate();
			if (result > 0)
				flag = true;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			ConnectionManager.closePreparedStatement(pst);
			ConnectionManager.colseConnection(conn);
		}
		return flag;
	}

	/**
	 * 新增联系人
	 */
	public boolean addBook(Book book) {
		boolean flag = false;

		conn = ConnectionManager.getConn();
		String sql = "insert into book(address,comAddress,company,comPhone,mobilePhone,name,phone,relation,sex,userId)values(?,?,?,?,?,?,?,?,?,?)";

		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, book.getAddress());
			pst.setString(2, book.getComAddress());
			pst.setString(3, book.getCompany());
			pst.setString(4, book.getComPhone());
			pst.setString(5, book.getMobilePhone());
			pst.setString(6, book.getName());
			pst.setString(7, book.getPhone());
			pst.setInt(8, book.getRelation());
			pst.setString(9, book.getSex());
			pst.setInt(10, book.getUserId());
			int result = pst.executeUpdate();
			if (result > 0)
				flag = true;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			ConnectionManager.closePreparedStatement(pst);
			ConnectionManager.colseConnection(conn);
		}
		return flag;
	}

	public List<Book> selectAllBookByRelation(int userId, int relationId) {
		List<Book> list = new ArrayList<Book>();
		conn = ConnectionManager.getConn();
		String sql = "select id,address,comAddress,company,comPhone,mobilePhone,name,phone,relation,userId,sex from Book where userId=? and relation=? ";
		try {
			pst = conn.prepareStatement(sql);
			pst.setLong(1, userId);
			pst.setLong(2, relationId);
			rs = pst.executeQuery();
			while (rs.next()) {
				Book book = new Book();
				book.setAddress(rs.getString("address"));
				book.setComAddress(rs.getString("comAddress"));
				book.setCompany(rs.getString("company"));
				book.setComPhone(rs.getString("comPhone"));
				book.setId(rs.getInt("id"));
				book.setMobilePhone(rs.getString("mobilePhone"));
				book.setName(rs.getString("name"));
				book.setSex(rs.getString("sex"));
				book.setPhone(rs.getString("phone"));
				book.setRelation(rs.getInt("relation"));
				book.setUserId(rs.getInt("userId"));
				list.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionManager.closeResultSet(rs);
			ConnectionManager.closePreparedStatement(pst);
			ConnectionManager.colseConnection(conn);
		}

		return list;
	}

}