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.