ifrienddaojdbcimpl.java

通讯录一个供大家参考

package com.lideedu.huang.addressBook.dao.friend;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import com.lideedu.huang.addressBook.exceptions.DataAccessException;
import com.lideedu.huang.addressBook.pojos.Friend;
import com.lideedu.huang.addressBook.pojos.ValueObject;
import com.lideedu.huang.addressBook.utils.PageBean;
import com.lideedu.huang.addressBook.dao.DBUtils;

public class IFriendDaoJDBCImpl implements IFriendDao {

	private static final String INSERT_STR = "insert into addressbook values(?,?,?)";
	private static final String FINDALL_STR = "select * from addressbook ";
	private static final String FIND_BY_PK = "select * from addressbook where phone=?";
	private static final String DELETE_BY_PHONE = "delete addressbook where phone=?";
	private static final String UPDATE_STR = "update addressbook set name=?,phone=?,address=? where phone=?";
	private static final String FIND_COUNT = "select count(*) from addressbook ";

	Connection con = null;

	PreparedStatement psta = null;

	ResultSet rs = null;

	// 检测电话号码是否已经存在
	public boolean findByPhone(String phone) throws DataAccessException {

		try {
			con = DBUtils.getConnection();
			psta = con.prepareStatement(FIND_BY_PK);
			psta.setString(1, phone);
			rs = psta.executeQuery();
			if (rs.next())
				return true;
			else
				return false;
		} catch (SQLException e) {
			throw new DataAccessException("主键查询失败！", e);
		} finally {
			DBUtils.releaseDBRes(con, psta, rs);
		}
	}
	
	// 按号码查询
	public ValueObject findByPK(String primaryKey) throws DataAccessException {

		Friend friend = new Friend();

		try {
			con = DBUtils.getConnection();
			psta = con.prepareStatement(FIND_BY_PK);
			psta.setString(1, primaryKey);
			rs = psta.executeQuery();
			if (rs.next()) {
				friend.setName(rs.getString(1));
				friend.setPhone(rs.getString(2));
				friend.setAddress(rs.getString(3));
				return friend;
			} else {
				return null;
			}

		} catch (SQLException e) {
			throw new DataAccessException("查询失败！", e);
		} finally {
			DBUtils.releaseDBRes(con, psta, rs);
		}
	}

	// 增加同学信息
	public void insert(ValueObject insertRecord) throws DataAccessException {

		Friend friend = (Friend) insertRecord;

		try {
			con = DBUtils.getConnection();
			psta = con.prepareStatement(INSERT_STR);
			psta.setString(1, friend.getName());
			psta.setString(2, friend.getPhone());
			psta.setString(3, friend.getAddress());
			psta.executeUpdate();
		} catch (SQLException e) {
			throw new DataAccessException("信息写入失败！对象为" + friend, e);
		} finally {
			DBUtils.releaseDBRes(con, psta, null);
		}
	}

	// 修改同学信息
	public void update(String oldPhone, ValueObject updateRecord)
			throws DataAccessException {

		Friend friend = (Friend) updateRecord;

		try {
			con = DBUtils.getConnection();
			psta = con.prepareStatement(UPDATE_STR);
			psta.setString(1, friend.getName());
			psta.setString(2, friend.getPhone());
			psta.setString(3, friend.getAddress());
			psta.setString(4, oldPhone);
			psta.executeUpdate();
		} catch (SQLException e) {
			throw new DataAccessException("修改通讯录信息失败！", e);
		} finally {

			DBUtils.releaseDBRes(con, psta, null);
		}
	}

	// 删除学生信息
	public void deleteByPhone(String phone) throws DataAccessException {

		try {
			con = DBUtils.getConnection();
			psta = con.prepareStatement(DELETE_BY_PHONE);
			psta.setString(1, phone);
			psta.executeUpdate();
		} catch (SQLException e) {
			throw new DataAccessException("删除数据失败！", e);
		} finally {
			DBUtils.releaseDBRes(con, psta, null);
		}
	}

	// 查询通讯录信息
	public PageBean findAll(String queryCondition,String queryKeyword,PageBean pageBean)
			throws DataAccessException {

		int currentPage=pageBean.getCurrentPage();
		Collection friends = new ArrayList();		

		try {
			con = DBUtils.getConnection();

			if (queryCondition.equals("queryAll")) {
				psta = con.prepareStatement(FIND_COUNT);
				rs = psta.executeQuery();
				if (rs.next())
					pageBean.setTotalRecords(rs.getInt(1));
				rs.close();
				psta.close();

				psta = con.prepareStatement(FINDALL_STR,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			} else {
				psta = con.prepareStatement(FIND_COUNT + "where "+queryCondition+" like '%"+queryKeyword+"%'");
				rs = psta.executeQuery();
				if (rs.next())
					pageBean.setTotalRecords(rs.getInt(1));
				rs.close();
				psta.close();

				psta = con.prepareStatement(FINDALL_STR + "where "+queryCondition+" like '%"+queryKeyword+"%'",
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			}
			rs = psta.executeQuery();
			if(currentPage<1||currentPage>pageBean.getTotalPages())
				pageBean.setCurrentPage(pageBean.getTotalPages());
			rs.absolute(pageBean.getPageSize()* (pageBean.getCurrentPage() - 1));
			int count = pageBean.getPageSize();

			while (rs.next()) {
				if (count <= 0) {
					pageBean.setPageContent(friends);
					return pageBean;
				}
				Friend friend = new Friend();
				friend.setName(rs.getString(1).trim());
				friend.setPhone(rs.getString(2).trim());
				friend.setAddress(rs.getString(3).trim());
				friends.add(friend);
				count--;
			}
			pageBean.setPageContent(friends);
			return pageBean;
		} catch (SQLException e) {
			throw new DataAccessException("查找通讯录失败！", e);
		} finally {
			DBUtils.releaseDBRes(con, psta, rs);
		}

	}

	public void update(ValueObject updateRecord) throws DataAccessException {
		// TODO Auto-generated method stub

	}

	public void delete(ValueObject deleteRecord) throws DataAccessException {
		// TODO Auto-generated method stub

	}

}