petdao.java~4~

宠物医院 JSP项目 我是一个初学者 请大家多多指教

package da0;

import java.util.*;
import java.sql.*;
import dto.*;

public class Petdao {
    PreparedStatement ps = null;
    ResultSet rs = null;
    Connection con = null;

    public Petdao() {
    }

    public ArrayList search(String petname, String ownername) {
        ArrayList arr = new ArrayList();
        DBcon dbcon = new DBcon();
        try {
            con = dbcon.GetConnection();
            String sql = "select * from view_pets_owners where 1=1";
            if (!petname.equals("")) {
                sql = sql + " and pname like '%" + petname + "%'";
            }
            if (!ownername.equals("")) {
                sql = sql + " and oname like '%" + ownername + "%'";
            }
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {

                Ownersdto ownersdto = new Ownersdto();
                ownersdto.setPid(rs.getInt("pid"));
                ownersdto.setOid(rs.getInt("oid"));

                ownersdto.setPname(rs.getString("pname"));
                ownersdto.setOname(rs.getString("oname"));
                arr.add(ownersdto);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            dbcon.CloseRs(rs);
            dbcon.ClosePs(ps);
            dbcon.CloseCon(con);
            return arr;
        }
    }


    public Ownersdto search(int pid) {
        Ownersdto ownersdto = new Ownersdto();
        DBcon dbcon = new DBcon();
        try {
            con = dbcon.GetConnection();
            String sql = "select pid,tname,oname,pname,birth_date from types,owners,pets where pets.type_id = types.tid and pets.owner_id = owners.oid and pets.pid =?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, pid);

            rs = ps.executeQuery();
            if (rs.next()) {
                ownersdto.setPid(rs.getInt("pid"));
                ownersdto.setTname(rs.getString("tname"));
                ownersdto.setOname(rs.getString("oname"));
                ownersdto.setPname(rs.getString("pname"));
                ownersdto.setBirth_date(rs.getString("birth_date"));

            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            dbcon.CloseRs(rs);
            dbcon.ClosePs(ps);
            dbcon.CloseCon(con);
            return ownersdto;
        }
    }
    public boolean UpdatePets(int pid, String pname, String birth_date,
                                int type_id, int owner_id) {
        boolean flag = false;
        DBcon dbcon = new DBcon();
        try {
            con = dbcon.GetConnection();
            String sql =
                    "update pets set pname=?,birth_date=?,type_id=?,owner_id=? where pid=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, pname);
            ps.setString(2, birth_date);
            ps.setInt(3, type_id);
            ps.setInt(4, owner_id);
            ps.setInt(5, pid);

          int i = ps.executeUpdate();
            if(i>0){
                flag=true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            dbcon.CloseRs(rs);
            dbcon.ClosePs(ps);
            dbcon.CloseCon(con);
            return flag;
        }
    }

    public ArrayList SearchVisit(int pid) {
           ArrayList arr = new ArrayList();
           DBcon dbcon = new DBcon();
           try {
               con = dbcon.GetConnection();
               String sql = "select * from visits where pet_id=?";
               ps = con.prepareStatement(sql);
               ps.setInt(1, pid);

               rs = ps.executeQuery();
               if (rs.next()) {
                   PetVisitdto petvisitdto = new PetVisitdto();
                   petvisitdto.setSid(rs.getInt(1));
                   petvisitdto.setPet_id(rs.getInt(2));
                   petvisitdto.setVisit_date(rs.getString(3));
                   petvisitdto.setSdescription(rs.getString(4));
                   arr.add(petvisitdto);
               }
           } catch (Exception ex) {
               ex.printStackTrace();
           } finally {
               dbcon.CloseRs(rs);
               dbcon.ClosePs(ps);
               dbcon.CloseCon(con);
               return arr;
           }

    }
    public boolean AddVisit(int pid,String date,String sdescription) {
         boolean flag = false;
          DBcon dbcon = new DBcon();
          try {
              con = dbcon.GetConnection();
              String sql =
                      "insert into visits(pet_id,visit_date,sdescription) values(?,?,?)";
              ps = con.prepareStatement(sql);
              ps.setInt(1, pid);
              ps.setString(2, date);
              ps.setString(3,sdescription);
              int i = ps.executeUpdate();
              if(i>0){
                  flag=true;
              }
          } catch (Exception ex) {
              ex.printStackTrace();
          } finally {
              dbcon.CloseRs(rs);
              dbcon.ClosePs(ps);
              dbcon.CloseCon(con);
              return flag;
          }

}