petserviceimpl.java

爱心”宠物诊所的职员需要使用系统提供的如下功能： 浏览诊所的兽医以及他们的专业特长； 浏览宠物的主人（即诊所的客户）的相关信息； 更新宠物的主人的相关信息； 向系统中增加一个新客户； 浏览宠

package com.wzxy.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wzxy.exception.DBException;
import com.wzxy.exception.LoginFailedException;
import com.wzxy.exception.PetOrOwnerNotExistsException;
import com.wzxy.exception.VetNotExistsException;
import com.wzxy.exception.VisitHistoryNotExist;
import com.wzxy.pojos.EmpDTO;
import com.wzxy.pojos.Fenye;
import com.wzxy.pojos.PetDTO;
import com.wzxy.pojos.PetOwnerDTO;
import com.wzxy.pojos.PetVisitDTO;
import com.wzxy.pojos.VetDTO;
import com.wzxy.utils.DBUtils;

public class PetServiceImpl implements IPetService {
	Connection con = null;

	PreparedStatement pstmt = null;

	ResultSet rset = null;

	public EmpDTO LoginVerify(String name, String pwd)
			throws LoginFailedException, DBException {
		EmpDTO emp = null;
		String sql = "select * from employee where name='" + name
				+ "' and password='" + pwd + "'";
		con = new DBUtils().getCon();

		try {
			pstmt = con.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if (rset.next()) {
				emp = new EmpDTO();
				emp.setEmp_name(rset.getString("name"));
				emp.setEmp_pwd(rset.getString("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException("database operation failed");
		} finally {
			new DBUtils().reaseSource(con, pstmt, rset);
		}
		if (emp == null) {
			throw new LoginFailedException("login Failed!");
		}
		System.out.println("sql is " + sql);
		return emp;
	}

	public List VetSearch(String name, String vetSpecName) throws DBException,
			VetNotExistsException {
		String sql = "select v.id, v.name,s.name,s.id from vets v,specialties s,vet_specialties vs "
				+ " where v.name like '%"
				+ name
				+ "%' and v.id=vs.vet_id and vs.specialty_id=s.id "
				+ " and s.name like '%"
				+ vetSpecName
				+ "%' and v.id=vs.vet_id and vs.specialty_id=s.id";
		ArrayList list = new ArrayList();
		con = new DBUtils().getCon();
		try {
			pstmt = con.prepareStatement(sql);
			rset = pstmt.executeQuery();
			while (rset.next()) {
				VetDTO vet = new VetDTO();
				vet.setVetId(rset.getInt(1));
				vet.setVetName(rset.getString(2));
				vet.setSpecName(rset.getString(3));
				vet.setSpecId(rset.getInt(4));
				list.add(vet);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DBException("database operation failed");
		}
		if (list.isEmpty())
			throw new VetNotExistsException("vet is not exists!");
		System.out.println("sql is " + sql + "  list is :" + list);
		return list;
	}

	public List PetSearch(String petName, String ownerName, Fenye fy)
			throws PetOrOwnerNotExistsException, DBException {
		/*
		 * String sql = "select p.id, p.name as
		 * petName,p.birth_date,p.type_id,p.owner_id,o.name as
		 * ownerName,t.name,o.address,o.city,o.telephone" + " from pets p,owners
		 * o,types t where p.name like '%" + petName + "%' and o.name like '%" +
		 * ownerName + "%'" + " and p.owner_id=o.id and p.type_id=t.id";
		 */
		int n = fy.getShowlist();
		int m = (fy.getShowpage() - 1) * n;
		String sql = "select top " + n + " p.id, p.name ,p.owner_id,o.name "

		+ " from pets p,owners o where p.name like '%" + petName
				+ "%' and o.name like '%" + ownerName + "%'"
				+ " and p.owner_id=o.id and p.id>all(select top " + m
				+ " p.id from pets p,owners o where p.name like '%" + petName
				+ "%' and o.name like '%" + ownerName + "%'"
				+ " and p.owner_id=o.id order by p.id)order by p.id";
		ArrayList list = new ArrayList();
		con = new DBUtils().getCon();
		try {
			pstmt = con.prepareStatement(sql);
			rset = pstmt.executeQuery();
			while (rset.next()) {
				PetDTO pet = new PetDTO();
				pet.setPetID(rset.getInt(1));
				pet.setPetName(rset.getString(2));
				
				pet.setPetOwnerID(rset.getInt(3));
				pet.setPetOwnerName(rset.getString(4));
				
				list.add(pet);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new DBException("database operation failed");
		} finally {
			new DBUtils().reaseSource(con, pstmt, rset);
		}
		if (list.isEmpty())
			throw new PetOrOwnerNotExistsException(
					"pet or owner is net existsException");
		return list;
	}

	public PetDTO selectPetById(int petId) throws DBException {
		String sql = "select p.id, p.name,p.birth_date,p.type_id,p.owner_id,o.name,t.name from pets p,owners o,types t"
				+ " where p.id="
				+ petId
				+ " and p.owner_id=o.id and p.type_id=t.id";
		con = new DBUtils().getCon();
		PetDTO pet = null;
		try {
			pstmt = con.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if (rset.next()) {
				pet = new PetDTO();
				pet.setPetID(rset.getInt(1));
				pet.setPetName(rset.getString(2));
				pet.setPetBirthDate(rset.getDate(3));
				pet.setPetTypeID(rset.getInt(4));
				pet.setPetOwnerID(rset.getInt(5));
				pet.setPetOwnerName(rset.getString(6));
				pet.setPetTypeName(rset.getString(7));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new DBException("database operation failed");
		} finally {
			new DBUtils().reaseSource(con, pstmt, rset);
		}
		System.out.println(sql);
		return pet;
	}

	public void updatePet(PetDTO pet) throws DBException {
		// Date petBirthDate=pet.getPetBirthDate();
		String sql = "update pets set type_id=" + pet.getPetTypeID()
				+ ",owner_id=" + pet.getPetOwnerID() + ",name='"
				+ pet.getPetName() + "',birth_date='"
				+ new java.sql.Date(pet.getPetBirthDate().getTime())
				+ "' where id=" + pet.getPetID();
		con = new DBUtils().getCon();

		int i = 0;
		try {
			pstmt = con.prepareStatement(sql);
			i = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			new DBUtils().reaseSource(con, pstmt, rset);
		}
		if (i == 0)
			throw new DBException("pet is not update");
		System.out.println(sql);

	}

	public List selectPetTypes() throws DBException {
		String sql = "select * from types";
		con = new DBUtils().getCon();
		ArrayList list = new ArrayList();
		try {
			pstmt = con.prepareStatement(sql);
			rset = pstmt.executeQuery();
			while (rset.next()) {
				PetDTO pet = new PetDTO();
				pet.setPetTypeID(rset.getInt("id"));
				pet.setPetTypeName(rset.getString("name"));
				list.add(pet);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new DBException("database operation failed");
		} finally {
			new DBUtils().reaseSource(con, pstmt, rset);
		}

		return list;
	}

	public List selectPetOwners() throws DBException {
		String sql = "select * from owners";
		con = new DBUtils().getCon();
		ArrayList list = new ArrayList();
		try {
			pstmt = con.prepareStatement(sql);
			rset = pstmt.executeQuery();
			while (rset.next()) {
				PetOwnerDTO owner = new PetOwnerDTO();
				owner.setPetOwnerId(rset.getInt("id"));
				owner.setPetOwnerName(rset.getString("name"));
				owner.setPetOwnerAddress(rset.getString("address"));
				owner.setPetOwnerCity(rset.getString("city"));
				owner.setPetOwnerTelNo(rset.getString("telephone"));
				list.add(owner);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new DBException("database operation failed");
		} finally {
			new DBUtils().reaseSource(con, pstmt, rset);
		}

		return list;
	}

	public void addPet(PetDTO pet) throws DBException {
		String sql = "insert into pets(name,birth_date,type_id,owner_id) values(?,?,?,?)";
		con = new DBUtils().getCon();
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, pet.getPetName());
			pstmt
					.setDate(2, new java.sql.Date(pet.getPetBirthDate()
							.getTime()));
			pstmt.setInt(3, pet.getPetTypeID());
			pstmt.setInt(4, pet.getPetOwnerID());
			int i = pstmt.executeUpdate();
			if (i == 0)
				throw new DBException("ADD FAILED");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			new DBUtils().reaseSource(con, pstmt, rset);
		}

		System.out.println(sql);
	}

	public void addPetOwner(PetOwnerDTO petOwner) throws DBException {

		String sql = "insert into owners(name,address,city,telephone,hobbies,sex,demo,regDate) values(?,?,?,?,?,?,?,?)";
		con = new DBUtils().getCon();
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, petOwner.getPetOwnerName());
			pstmt.setString(2, petOwner.getPetOwnerAddress());
			pstmt.setString(3, petOwner.getPetOwnerCity());
			pstmt.setString(4, petOwner.getPetOwnerTelNo());
			pstmt.setString(5, transToKenString(petOwner.getHobbies(), "|"));
			pstmt.setString(6, petOwner.getSex());
			pstmt.setString(7, petOwner.getDemo());
			pstmt
					.setDate(8, new java.sql.Date(petOwner.getRegDate()
							.getTime()));
			int i = pstmt.executeUpdate();
			if (i == 0)
				throw new DBException("add petOwner is woring");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			new DBUtils().reaseSource(con, pstmt, rset);
		}
		System.out.println(sql);
	}

	public void updatePetOwner(PetOwnerDTO petOwner) throws DBException {
		String sql = "update owners set name='" + petOwner.getPetOwnerName()
				+ "',address='" + petOwner.getPetOwnerAddress() + "',city='"
				+ petOwner.getPetOwnerCity() + "',telephone='"
				+ petOwner.getPetOwnerTelNo() + "',hobbies='"
				+ transToKenString(petOwner.getHobbies(), "|") + "',sex='"
				+ petOwner.getSex() + "',demo='" + petOwner.getDemo()
				+ "' where id=" + petOwner.getPetOwnerId();
		con = new DBUtils().getCon();
		System.out.println(sql);
		try {
			pstmt = con.prepareStatement(sql);
			int i = pstmt.executeUpdate();
			if (i == 0)
				throw new DBException("update petOwner is woring");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			new DBUtils().reaseSource(con, pstmt, rset);
		}
		System.out.println(sql);
	}

	private String transToKenString(String[] strArr, String token) {

		StringBuffer hobby = new StringBuffer();
		for (int i = 0; i < strArr.length; i++) {
			hobby.append(strArr[i]);
			if (i != strArr.length - 1)
				hobby.append(token);
		}
		return hobby.toString();

	}

	public PetOwnerDTO viewPetOwner(int petOwnerId) throws DBException {
		String sql = "select id,name,address,city,telephone,hobbies,sex,demo,regDate from owners where id="
				+ petOwnerId;
		con = new DBUtils().getCon();
		PetOwnerDTO owner = null;
		System.out.println("hello12: " + sql);
		try {
			pstmt = con.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if (rset.next()) {
				owner = new PetOwnerDTO();
				owner.setPetOwnerId(rset.getInt(1));
				owner.setPetOwnerName(rset.getString(2));
				owner.setPetOwnerAddress(rset.getString(3));
				owner.setPetOwnerCity(rset.getString(4));
				owner.setPetOwnerTelNo(rset.getString(5));
				owner.setHobbies(rset.getString(6).split("\\|"));
				owner.setSex(rset.getString(7));
				owner.setDemo(rset.getString(8));
				owner.setRegDate(rset.getDate(9));

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

		} finally {
			new DBUtils().reaseSource(con, pstmt, rset);
		}

		if (owner == null)
			throw new DBException("selct owner is worong!");
		System.out.println("hello2: " + sql);
		return owner;
	}

	public List searchPetVisitHistory(int petId) throws DBException,
			VisitHistoryNotExist {
		String sql = "select visit_date,description from visits where pet_id="
				+ petId;
		con = new DBUtils().getCon();
		ArrayList list = new ArrayList();
		try {
			pstmt = con.prepareStatement(sql);
			rset = pstmt.executeQuery();
			while (rset.next()) {
				PetVisitDTO visit = new PetVisitDTO();
				visit.setPetVisitDate(rset.getDate(1));
				visit.setPetVisitDescription(rset.getString(2));
				list.add(visit);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (list.isEmpty()) {
			throw new VisitHistoryNotExist("VisitHistory is not Exist");
		}
		return list;
	}

	public void addVisitHistory(PetVisitDTO visit) throws DBException {
		String sql = "insert into visits(pet_id,visit_date,description) values(?,?,?)";
		con = new DBUtils().getCon();
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, visit.getPetId());
			pstmt.setDate(2, new java.sql.Date(visit.getPetVisitDate()
					.getTime()));
			pstmt.setString(3, visit.getPetVisitDescription());
			int i = pstmt.executeUpdate();
			System.out.println("hellos::" + visit.getPetId() + "  "
					+ visit.getPetVisitDescription());
			if (i == 0)
				throw new DBException("add petVisitHistory is woring");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}