package bholder;
 import java.sql.*;
 import java.util.*;
 import jdbc.*;

public class HolderDAO {
    public HolderDAO() {
    }
    /**
   * 查詢寵物所有人的相關信息
   * @return Collection
   */
//  public Collection holde_select(){
//      Collection ownerList = null;
//      Statement stat = null;
//      ResultSet rs = null;
//      try {
//          String sql = "select * from holder_info";
//          stat = JDBC.getConnection().createStatement();
//          rs = stat.executeQuery(sql);
//          if(rs.next()){
//              ownerList = new ArrayList();
//              do{
//                 HolderVO hvo = new HolderVO();
//                  hvo.setId(rs.getInt(1));
//                  hvo.setName(rs.getString(2));
//                  hvo.setSex(rs.getString(3));
//                  hvo.setAge(rs.getInt(4));
//                  hvo.setAddress(rs.getString(5));
//                  hvo.setTime(rs.getString(6));
//                  ownerList.add(hvo);
//              }while(rs.next());
//          }
//      } catch (SQLException ex) {
//          ex.printStackTrace();
//      }
//      finally{
//          if(rs != null){
//              try {
//                  rs.close();
//              } catch (SQLException ex1) {
//              }
//              rs = null;
//          }
//          if(stat != null){
//              try {
//                  stat.close();
//              } catch (SQLException ex2) {
//              }
//              stat = null;
//          }
//      }
//      return ownerList;
//  }

    public Collection holde_select(){
        ArrayList al =new ArrayList(10);
        try{
            Statement stmt=JDBC.getConnection().createStatement();
            ResultSet rs=stmt.executeQuery("select * from holder_info");
            while(rs.next()){
                HolderVO hvo = new HolderVO();
                hvo.setId(rs.getInt(1));
                hvo.setName(rs.getString(2));
                hvo.setSex(rs.getString(3));
                hvo.setAge(rs.getInt(4));
                hvo.setAddress(rs.getString(5));
                hvo.setTime(rs.getString(6));
                al.add(hvo);
            }
            rs.close();
            stmt.close();
        }catch(SQLException ex){
            ex.printStackTrace();
        }
        return al;
    }

    public Collection holder_name(String name){
           Collection caseNameList = null;
           Statement stat = null;
           ResultSet rs = null;
           try {
               String sql = "select * from holder_info where holdername like'%" + name +"%'";
               stat = JDBC.getConnection().createStatement();
               rs = stat.executeQuery(sql);
               if(rs.next()){
                   caseNameList = new ArrayList();
                   do{
                       HolderVO vo = new HolderVO();
                       vo.setId(rs.getInt(1));
                       vo.setName(rs.getString(2));
                       vo.setSex(rs.getString(3));
                       vo.setAge(rs.getInt(4));
                       vo.setAddress(rs.getString(5));
                       vo.setTime(rs.getString(6));
                       caseNameList.add(vo);
                   }while(rs.next());
               }
           } catch (SQLException ex) {
               ex.printStackTrace();
           }
           finally{
               if(rs != null){
                   try {
                       rs.close();
                   } catch (SQLException ex1) {
                   }
                   rs = null;
               }
               if(stat != null){
                   try {
                       stat.close();
                   } catch (SQLException ex2) {
                   }
                   stat = null;
               }
           }
           return caseNameList;
       }

       public boolean holder_add(HolderVO vo){
           try{
               PreparedStatement stmt = JDBC.getConnection().prepareStatement(
                             "insert into holder_info values(?,?,?,?,default)");
                     stmt.setString(1,vo.getName());
                     stmt.setString(2,vo.getSex());
                     stmt.setInt(3,vo.getAge());
                     stmt.setString(4,vo.getAddress());
                     int i=stmt.executeUpdate();
                     stmt.close();
                     if(i>0)
                         return true;
                     else
                         return false;
            } catch (SQLException ex) {
                ex.printStackTrace();
                return false;
            }
        }

//    public Map holder_name(String name ){
//        HolderVO vo=null;
//        Statement stmt=null;
//        ResultSet rs=null;
//        Map map=null;
//        try{
//            stmt=JDBC.getConnection().createStatement();
//            String sql ="select * from holder_info where holdername like'%" + name +"%'";
//            rs=stmt.executeQuery(sql);
//            if(rs.next()){
//                map=new HashMap();
//                do{
//                    vo = new HolderVO();
//                    //                vo = new HolderVO();
//                vo.setId(rs.getInt(1));
//                vo.setName(rs.getString(2));
//                vo.setSex(rs.getString(3));
//                vo.setAge(rs.getInt(4));
//                vo.setAddress(rs.getString(5));
//                vo.setTime(rs.getString(6));
//                    map.put(rs.getObject(1),vo);
//                }while(rs.next());
//            }
//        }catch(SQLException ex){
//            ex.printStackTrace();
//        }finally{
//            if(rs!=null){
//                try{
//                    rs.close();
//                }catch(SQLException ex){
//                }
//                rs=null;
//            }
//            if(stmt!=null){
//                try{
//                    stmt.close();
//                }catch(SQLException ex2){
//                }
//                stmt=null;
//            }
//        }
//        return map;
//    }

//  //按姓名查詢，但每次只能查詢出一條信息
//    public HolderVO holder_name(String name){
//        HolderVO vo = null;
//        Statement stmt = null;
//        ResultSet rs = null;
//        try {
//            stmt = JDBC.getConnection().createStatement();
//            String sql = "select * from holder_info where holdername like'%" + name +"%'";
//            rs = stmt.executeQuery(sql);
//            if (rs.next()) {
//                vo = new HolderVO();
//                vo.setId(rs.getInt(1));
//                vo.setName(rs.getString(2));
//                vo.setSex(rs.getString(3));
//                vo.setAge(rs.getInt(4));
//                vo.setAddress(rs.getString(5));
//                vo.setTime(rs.getString(6));
//            }
//        } catch (SQLException ex) {
//            ex.printStackTrace();
//        } finally {
//            if (rs != null) {
//                try {
//                    rs.close();
//                } catch (SQLException ex1) {
//                }
//                rs = null;
//            }
//            if (stmt != null) {
//                try {
//                    stmt.close();
//                } catch (SQLException ex2) {
//                }
//                stmt = null;
//            }
//        }
//        return vo;
//    }

//    public Map holde_select() {
//        Map map = null;
//        Statement stmt = null;
//        ResultSet rs = null;
//        try{
//            stmt = JDBC.getConnection().createStatement();
//            rs = stmt.executeQuery("select * from Holder_info");
//            if (rs.next()){
//                map = new HashMap();
//                do{
//                    HolderVO hvo=new HolderVO();
//                    hvo.setId(rs.getInt(1));
//                    hvo.setName(rs.getString(2));
//                    hvo.setSex(rs.getString(3));
//                    hvo.setAge(rs.getInt(4));
//                    hvo.setAddress(rs.getString(5));
//                    hvo.setTime(rs.getString(6));
//                    map.put(rs.getObject(1),hvo);
//                }while(rs.next());
//            }
//        }catch(SQLException ex){
//            ex.printStackTrace();
//        }finally{
//            if(stmt!=null){
//                try{
//                    stmt.close();
//                }catch(SQLException ex1){
//                }
//            }
//            if(rs!=null){
//                try{
//                    rs.close();
//                }catch(SQLException ex2){
//                }
//            }
//        }
//        return map;
//    }
    public static void main(String[] args){
        System.out.println("sdfgdfsgdfg.........");
        HolderDAO dao=new HolderDAO();
        //HolderVO vo=dao.holder_name("");
      // if(vo!=null)
        //System.out.println(vo.getAddress());
//           HolderVO vo=new HolderVO();
//           vo.setname(ewe);
//           vo.setsex("ewe");
//           vo.setage(56);
//           vo.setaddress("eweqwe");
//
//         if(dao.holder_add(vo)){
//         out.println("添加成功！！！");
//      }else{
//           out.print("注冊失?。。?！");
//       }
    }

}