petdao.java~5~

宠物医院 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;
        }

    }
}