1 package test;  2   3 /**  4  * DAO模式  5  * 第一步 辅助类  6  *   7  * @author acer  8  *  9  */ 10 import java.io.File; 11 import java.sql.Connection; 12 import java.sql.DriverManager; 13 import java.sql.PreparedStatement; 14 import java.sql.ResultSet; 15 import java.sql.SQLException; 16 import java.util.Iterator; 17 import java.util.List; 18  19 import org.dom4j.Document; 20 import org.dom4j.Element; 21 import org.dom4j.io.SAXReader; 22  23 /** 24  *  25  * JDBC编程步骤： 26  * 1  加载驱动  Class.forName("字符串") 27  * 2 建立Connection对象 28  * 3 通过Connetion创建PreparedStatement对象 29  * 4 执行SQL语句 30  * 5 处理结果 31  * 6 释放资源 32  * @author acer 33  * 34  */ 35 public class JdbcUtil { 36  37     private static String driver;// 驱动 38     private static String url;// 连接字符串 39     private static String user;// 用户名 40     private static String password;// 密码 41  42     private static Connection conn;// Connection对象 43     private static PreparedStatement ptmt;// PreparedStatement对象 44     private static ResultSet rs;// ResultSet对象 45      46     /* 加载数据库驱动 */ 47     static { 48         try { 49             loadDatabase(); 50         } catch (Exception e1) { 51             e1.printStackTrace(); 52         } 53         try { 54             Class.forName(driver); 55         } catch (ClassNotFoundException e) { 56             throw new RuntimeException(e); 57         } 58     } 59  60     private JdbcUtil() { 61  62     } 63  64     /** 65      * 读取配置文件 66      *  67      * @throws Exception 68      */ 69  70     private static void loadDatabase() throws Exception { 71         // 创建SAXReader对象 72         SAXReader reader = new SAXReader(); 73         // 创建Document对象 74         Document document = reader.read(new File("src/test/database.xml")); 75         List list = document.selectNodes("/database/driver"); 76         // 迭代 77         Iterator it = list.iterator(); 78         while (it.hasNext()) { 79             Element item = (Element) it.next(); 80             driver = item.getText(); 81         } 82  83         list = document.selectNodes("/database/url"); 84         it = list.iterator(); 85         while (it.hasNext()) { 86             Element item = (Element) it.next(); 87             url = item.getText(); 88         } 89  90         list = document.selectNodes("/database/user"); 91         it = list.iterator(); 92         while (it.hasNext()) { 93             Element item = (Element) it.next(); 94             user = item.getText(); 95         } 96  97         list = document.selectNodes("/database/password"); 98         it = list.iterator(); 99         while (it.hasNext()) {100             Element item = (Element) it.next();101             password = item.getText();102         }103     }104 105     /**106      * 获取数据库连接107      * 108      * @return 连接对象109      */110     public static Connection getConnection() {111         try {112             return conn = DriverManager.getConnection(url, user, password);113         } catch (SQLException e) {114             throw new RuntimeException(e);115         }116     }117 118     /**119      * 释放ResultSet资源120      * 121      * @param rs122      */123     public static void close(ResultSet rs) {124         try {125             if (null != rs)126                 rs.close();127         } catch (SQLException e) {128             throw new RuntimeException(e);129         }130     }131 132     /**133      * 释放PreparedStatement资源134      * 135      * @param ptmt136      */137     public static void close(PreparedStatement ptmt) {138         try {139             if (null != ptmt)140                 ptmt.close();141         } catch (SQLException e) {142             throw new RuntimeException(e);143         }144     }145 146     /**147      * 释放Connection资源148      * 149      * @param conn150      */151     public static void close(Connection conn) {152         try {153             if (null != conn)154                 conn.close();155         } catch (SQLException e) {156             throw new RuntimeException(e);157         }158     }159 160     /**161      * 释放所有资源162      */163     public static void close() {164         try {165             if (null != rs && !rs.isClosed()) {166                 rs.close();167             }168             rs = null;169         } catch (SQLException e) {170             throw new RuntimeException(e);171         } finally {172             try {173                 if (null != ptmt && !ptmt.isClosed()) {174                     ptmt.close();175                 }176                 ptmt = null;177             } catch (SQLException e) {178                 throw new RuntimeException(e);179             } finally {180                 try {181                     System.out.println(conn);182                     if (null != conn && !conn.isClosed()) {183                         conn.close();184                     }185                     conn = null;186                 } catch (SQLException e) {187                     throw new RuntimeException(e);188                 }189             }190         }191     }192 193     /**194      * 执行增删改195      * 196      * @param sql197      *            待执行的SQL语句198      * @param params199      *            SQL语句中?占位符的参数200      * @return 受影响的行数201      */202     public static int executeUpdate(String sql, Object... params) {203         try {204             if (null == conn)205                 getConnection();206             ptmt = conn.prepareStatement(sql);207             if (null != params) { // 为SQL语句中?占位符设置参数208                 for (int i = 0; i < params.length; i++) {209                     ptmt.setObject(i + 1, params[i]);210                 }211             }212             int cnt = ptmt.executeUpdate();213             return cnt;214         } catch (SQLException e) {215             throw new RuntimeException(e);216         }217     }218 219     /**220      * 执行查询操作221      * 222      * @param sql223      * @param params224      * @return 查询出来的结果集225      */226     public static ResultSet executeQuery(String sql, Object... params) {227         try {228             ptmt = conn.prepareStatement(sql);229             if (null != params) {230                 for (int i = 0; i < params.length; i++) { // 为每个?占位符设置参数231                     ptmt.setObject(i + 1, params[i]);232                 }233             }234             return rs = ptmt.executeQuery();235         } catch (SQLException e) {236             throw new RuntimeException("执行查询操作失败", e);237         }238     }239 240     /**241      * 查询242      */243     public static ResultSet executeQuery(String sql) {244 245         try {246             ptmt = conn.prepareStatement(sql);247         } catch (SQLException e1) {248             e1.printStackTrace();249         }250         try {251             return rs = ptmt.executeQuery();252         } catch (SQLException e) {253             e.printStackTrace();254         }255         return rs;256 257     }258 259 }

  1 package test;  2   3 import java.sql.ResultSet;  4 import java.util.Scanner;  5   6 import test.dao.PetDao;  7 import test.dao.impl.PetDaoMySQLImpl;  8 import test.entity.Master;  9 import test.entity.Pet; 10  11 /** 12  * 用户的能力 13  * @author acer 14  * 15  */ 16 public class MasterManger { 17     /** 18      * 登录主菜单 19      * @throws Exception 20      */ 21     public void frist() throws Exception{ 22         Scanner input = new Scanner(System.in); 23         System.out.println("欢迎来到宠物上世界！"); 24         System.out.println("请选择。。。。。\n1 登录    2 注册   3退出"); 25         int choose = input.nextInt(); 26         switch(choose){ 27         case 1: 28             login(); 29             break; 30         case 2: 31             register(); 32             break; 33         case 3: 34             System.exit(0); 35         } 36     } 37     /** 38      *  39      * 新用户注册 40      * @throws Exception  41      */ 42     public void register() throws Exception{ 43         Scanner input=new Scanner(System.in); 44         System.out.print("请注册你的用户名："); 45         String username = input.next(); 46         System.out.print("请注册你的密码："); 47         String password = input.next(); 48         System.out.print("请注册你的名字："); 49         String name = input.next(); 50         System.out.print("请注册你的地址："); 51         String address = input.next(); 52         System.out.print("请注册你的电话号码："); 53         int phone = input.nextInt(); 54         Master master=new Master(username,password,name,address,phone); 55         //保存到数据库中 56         PetDao dao=new PetDaoMySQLImpl(); 57         int cnt=dao.save(master);         58         System.out.println("注册成功！\n您可以登录了！！！");     59         login(); 60     } 61      62      63     /** 64      * 主人登录 65      * @throws Exception  66      */ 67     public void login() throws Exception{ 68         Scanner input = new Scanner(System.in); 69         System.out.print("请输入你的用户名："); 70         String username= input.next(); 71         System.out.print("请输入你的密码："); 72         String password = input.next(); 73         String sql="select * from master where username=? and password=?";         74         JdbcUtil.getConnection();         75         //执行查询 76         ResultSet rs=JdbcUtil.executeQuery(sql,username,password); 77         if(rs.next()){ 78             System.out.println("主人登录成功！"); 79             System.out.println("主人的基本信息："); 80             System.out.println("编码："+rs.getInt("id")+"\n姓名："+rs.getString("name")+ 81                     "\n地址："+rs.getString("address")+"\n电话："+rs.getInt("phone"));    82             menu(); 83         } 84         else{ 85             System.out.println("1 用户名或密码错误"); 86             System.out.println("2 还是您还没有注册呢！"); 87             int start=input.nextInt(); 88             if(start==1){ 89                  login() ; 90             } 91             else if(start==2){ 92                 register(); 93             } 94              95         }            96     } 97      98     /** 99      * 4 根据id号查询宠物信息100      * @throws Exception 101      */102     103    public void findPetById() throws Exception{104        Scanner input=new Scanner(System.in);105        System.out.println("请输入你查询的宠物id号：");106        int id=input.nextInt();107        String sql="select * from pet where id=?";108        JdbcUtil.getConnection();109        ResultSet rs=JdbcUtil.executeQuery(sql, id);110        System.out.println("这是你查找的宠物信息！！");111        if(rs.next()){112            if(rs.getString("gender").equals("无")){113                  System.out.println("宠物狗");114                  System.out.println("编号："+rs.getInt("id")+"\t名字："+rs.getString("name")+"\t亲密值："115              +rs.getInt("love")+"\t健康值："+rs.getInt("health")+"\t品种："+rs.getString("varietiess"));116              }117              else{118                  System.out.println("\n宠物企鹅");119                  System.out.println("编号："+rs.getInt("id")+"\t名字："+rs.getString("name")+"\t亲密值："120                          +rs.getInt("love")+"\t健康值："+rs.getInt("health")+"\t性别："+rs.getString("gender"));             121              }122        }123         124        125        126    } 127     128    /**129     * 1 查询所有没有被领养的宠物130     * @throws Exception131     */132     133     public void findAllNoMaster() throws Exception{134         String sql="select * from pet where masterId=0";135         JdbcUtil.getConnection();136         ResultSet rs=JdbcUtil.executeQuery(sql);137          System.out.println("这些是还没有被领养的宠物");138     while(rs.next()){139          if(rs.getString("gender").equals("无")){140              System.out.println("宠物狗");141              System.out.println("编号："+rs.getInt("id")+"\t名字："+rs.getString("name")+"\t亲密值："142          +rs.getInt("love")+"\t健康值："+rs.getInt("health")+"\t品种："+rs.getString("varietiess"));143          }144          else{145              System.out.println("\n宠物企鹅");146              System.out.println("编号："+rs.getInt("id")+"\t名字："+rs.getString("name")+"\t\t亲密值："147                      +rs.getInt("love")+"\t健康值："+rs.getInt("health")+"\t性别："+rs.getString("gender"));             148          }149      }150     151     152     }153     154     /**155      * 2 查询自己所养的宠物156      * @throws Exception  157      */158     159     public void myPet() throws Exception{160         Scanner input = new Scanner(System.in);161         System.out.println("请输入你的ID号：");162         int id=input.nextInt();163         String sql="select * from pet where masterId=?";        164         JdbcUtil.getConnection();165         ResultSet rs = JdbcUtil.executeQuery(sql,id);166          System.out.println("这是您的宠物信息！！");167          while(rs.next()){168              if(rs.getString("gender").equals("无")){169                  System.out.println("宠物狗");170                  System.out.println("编号："+rs.getInt("id")+"\t名字："+rs.getString("name")+"\t亲密值："171              +rs.getInt("love")+"\t健康值："+rs.getInt("health")+"\t品种："+rs.getString("varietiess"));172              }173              else{174                  System.out.println("宠物企鹅");175                  System.out.println("编号："+rs.getInt("id")+"\t名字："+rs.getString("name")+"\t\t亲密值："176                          +rs.getInt("love")+"\t健康值："+rs.getInt("health")+"\t性别："+rs.getString("gender"));             177              }178          }179     }180     181     182     /**183      * 领养宠物  184      */185     public void adoptPet(){ 186         187         Scanner input = new Scanner(System.in);188         System.out.println("请输入你的编号：");189         int id=input.nextInt();190         System.out.println("请输入你要领养宠物的编号：");191         int num=input.nextInt();192         Pet pet=new Pet(id,num);        193         PetDao dao=new PetDaoMySQLImpl();194         int cnt=dao.adoptPet(pet);195         196         if(cnt==1){197         System.out.println("领养成功！！"); 198         }else{199             System.out.println("领养失败！");200         }201     }202     203     /**204      * 弃养自己的宠物205      */206     207     public void updatePet(){208         Scanner input = new Scanner(System.in);209         System.out.println("请输入你要弃养宠物的名字：");210         String name=input.next();211         System.out.println("请确认你所弃养的宠物编号：");212         int id=input.nextInt();213         Pet pet=new Pet(name,id);214         PetDao dao=new PetDaoMySQLImpl();215         int cnt=dao.updatePet(pet);216         if(cnt==1){217         System.out.println("就这样放弃了您的宠物！！！"); 218         }else{219             System.out.println("弃养宠物失败！");220         }221         222     }223     224   /**225    * 226    * 功能主菜单227  * @throws Exception 228    */229     public void menu() throws Exception{230         Scanner input =new Scanner(System.in);231         System.out.println("\n请选择。。。");232         while (true){233         System.out.println("1 查看当前系统中未被领养的宠物\n2 查看自己已领养的所有宠物的信息\n3"234                 + " 根据编号查询宠物信息\n4 弃养自己已领养的宠物\n5 可以领养宠物\n6 退出系统");235         int choose=input.nextInt();236         switch(choose){237         case 1:           238             findAllNoMaster();239             break;240         case 2:241              myPet();242             break;243         case 3:            244             findPetById();245             break;246         case 4:247              myPet();248              updatePet();249             break;250         case 5:251             findAllNoMaster();252             adoptPet();253             break;254         case 6:255             System.exit(0);256         }257         }258     }259     260     261   262     263 }

测试类

1 package test;2 3 public class Test {4   public static void main(String[] args) throws Exception {5       MasterManger u=new MasterManger();6        u.frist();7   }8 }

 1 package test.entity; 2 /** 3  * 第二步  实体 4  * 宠物类 5  * @author acer 6  * 7  */ 8 public class Pet { 9 10     private int id;//编号11     private String name;//名称12     private int love;//亲密值13     private int health;//健康值14     private int masterId;//所属主人编号15     public Pet() {16         super();17     }18     public Pet(int id, String name, int love, int health, int masterId) {19         super();20         this.id = id;21         this.name = name;22         this.love = love;23         this.health = health;24         this.masterId = masterId;25     }26     27     public Pet(int masterId) {28         super();29         this.masterId = masterId;30     }31     32     public Pet(String name) {33         super();34         this.name = name;35     }36     37     public Pet(int masterId, int id ) {38         super();39         this.id = id;40         this.masterId = masterId;41     }42     public int getId() {43         return id;44     }45     public void setId(int id) {46         this.id = id;47     }48     public String getName() {49         return name;50     }51     public void setName(String name) {52         this.name = name;53     }54     public int getLove() {55         return love;56     }57     public void setLove(int love) {58         this.love = love;59     }60     public int getHealth() {61         return health;62     }63     public void setHealth(int health) {64         this.health = health;65     }66     public int getMasterId() {67         return masterId;68     }69     public void setMasterId(int masterId) {70         this.masterId = masterId;71     }72     73     74     public Pet(String name,int id ) {75         super();76         this.id = id;77         this.name = name;78     }79     @Override80     public String toString() {81         return "Pet [id=" + id + ", name=" + name + ", love=" + love82                 + ", health=" + health + ", masterId=" + masterId + "]";83     }84     85 86 }

 1 package test.entity; 2 /** 3  * 宠物狗 4  * @author acer 5  * 6  */ 7 public class Dogs extends Pet { 8     public String varieties;//品种 9 10     public Dogs(){11     }12 13     public Dogs(int id, String name, int love, int health, int masterId,14             String varieties) {15         super(id, name, love, health, masterId);16         this.varieties = varieties;17     }18 19     public String getVarieties() {20         return varieties;21     }22 23     public void setVarieties(String varieties) {24         this.varieties = varieties;25     }26 27     @Override28     public String toString() {29         return "Dogs [varieties=" + varieties + "+ super.toString() + ";30     }31     32 33 }

  1 package test.entity;  2 /**  3  * 主人类  4  * @author acer  5  *  6  */  7 public class Master {  8       9     private int id; 10     private String username; 11     private String password; 12     private String name; 13     private String address; 14     private int phone; 15      16     public Master(int id) { 17         super(); 18         this.id = id; 19     } 20  21     public Master(String username, String password, String name, 22             String address, int phone) { 23         super(); 24         this.username = username; 25         this.password = password; 26         this.name = name; 27         this.address = address; 28         this.phone = phone; 29     } 30  31     public Master(int id, String username, String password, String name, 32             String address, int phone) { 33         super(); 34         this.id = id; 35         this.username = username; 36         this.password = password; 37         this.name = name; 38         this.address = address; 39         this.phone = phone; 40     } 41  42     public int getId() { 43         return id; 44     } 45  46     public void setId(int id) { 47         this.id = id; 48     } 49  50     public String getUsername() { 51         return username; 52     } 53  54     public void setUsername(String username) { 55         this.username = username; 56     } 57  58     public String getPassword() { 59         return password; 60     } 61  62     public void setPassword(String password) { 63         this.password = password; 64     } 65  66     public String getName() { 67         return name; 68     } 69  70     public void setName(String name) { 71         this.name = name; 72     } 73  74     public String getAddress() { 75         return address; 76     } 77  78     public void setAddress(String address) { 79         this.address = address; 80     } 81  82     public int getPhone() { 83         return phone; 84     } 85  86     public void setPhone(int phone) { 87         this.phone = phone; 88     } 89  90     @Override 91     public String toString() { 92         return "Master [id=" + id + ", username=" + username + ", password=" 93                 + password + ", name=" + name + ", address=" + address 94                 + ", phone=" + phone + "]"; 95     } 96  97      98  99     100     101 102 }

 1 package test.entity; 2 /** 3  * 企鹅类 4  * @author acer 5  * 6  */ 7 public class Penguin extends Pet{ 8     private String gender; 9     public Penguin(){10         11     }12     public Penguin(int id, String name, int love, int health, int masterId,13             String gender) {14         super(id, name, love, health, masterId);15         this.gender = gender;16     }17     public String getGender() {18         return gender;19     }20     public void setGender(String gender) {21         this.gender = gender;22     }23     @Override24     public String toString() {25         return "Penguin [gender=" + gender + "" + super.toString()26                 + "]";27     }28     29 30 }

 1 package test.dao; 2 /** 3  * 第三步： DAO接口 4  */ 5 import java.sql.ResultSet; 6 import test.entity.Master; 7 import test.entity.Pet; 8  9 public interface PetDao {10     /**11      * 用于注册新的用户12      * @param master13      * @return14      */15     16     int save(Master master);17     /**18      * 2 查看没有被领养的宠物19      *20      * @return 宠物21      */22     ResultSet noMasterPet();23     24     /**25      * 根据编号查询宠物信息26      */27     ResultSet getPetById(int id);28     /**29      * 查看自己的宠物  30      */31     ResultSet myPet(int id);32     /**33      * 领养宠物34      */35     int adoptPet(Pet pet);36     /**37      * 38      * 弃养自己的宠物39      */40     int updatePet(Pet pet);41      }

 1 package test.dao.impl; 2  3 /** 4  * 第四步  DAO接口实现类 5  *  6  */ 7 import java.sql.ResultSet; 8  9 import test.JdbcUtil;10 import test.dao.PetDao;11 import test.entity.Master;12 import test.entity.Pet;13 14 public class PetDaoMySQLImpl implements PetDao {15     /**16      * 注册 成功17      */18     @Override19     public int save(Master master) {20         // 1 SQL语句21         String sql = "insert into master(username,password,name,address,phone) values (?,?,?,?,?)";22         // 2 建立连接23         JdbcUtil.getConnection();24         // 3 执行操作25         int cnt = JdbcUtil.executeUpdate(sql, master.getUsername(),26                 master.getPassword(), master.getName(), master.getAddress(),27                 master.getPhone());28         // 4 释放资源29         JdbcUtil.close();30         return cnt;31     }32 33     /**34      * 没有被领养的宠物 成功35      */36     @Override37     public ResultSet noMasterPet() {38         String sql = "select * from pet where masterId =0";39         JdbcUtil.getConnection();40         ResultSet rs = JdbcUtil.executeQuery(sql);41         JdbcUtil.close();42         return rs;43     }44 45     /**46      * 根据id号查找宠物 成功47      */48     @Override49     public ResultSet getPetById(int id) {50         String sql = "select * form pet where id=?";51         JdbcUtil.getConnection();52         ResultSet rs = JdbcUtil.executeQuery(sql, id);53         JdbcUtil.close();54         return rs;55     }56 57     /**58      * 查询自己的宠物 成功59      *60      */61     @Override62     public ResultSet myPet(int id) {63         String sql = "select * from pet where masterId=?";64         JdbcUtil.getConnection();65         ResultSet rs = JdbcUtil.executeQuery(sql, id);66         JdbcUtil.close();67         return rs;68 69     }70 71     /**72      * 领养宠物73      * 74      */75     @Override76     public int adoptPet(Pet pet) {77         String sql = "update pet set masterId=? where id=?";78         JdbcUtil.getConnection();79         int cnt = JdbcUtil.executeUpdate(sql, pet.getMasterId(), pet.getId());80         JdbcUtil.close();81         return cnt;82     }83 84     /**85      * 86      * 弃养自己的宠物87      */88     @Override89     public int updatePet(Pet pet) {90         String sql = "update pet set masterId=0 WHERE name=? and id=?";91         JdbcUtil.getConnection();92         int cnt = JdbcUtil.executeUpdate(sql, pet.getName(), pet.getId());93         JdbcUtil.close();94         return cnt;95     }96 97 }

 1 create table pet( 2 id int not null primary key auto_increment, 3 name varchar(50) not null, 4 love int not null, 5 health  int not null, 6 varietiess varchar(20), 7 gender varchar(10), 8 masterId int  9 );10 11 create table master(12 id int not null primary key auto_increment,13 username varchar(50) not null,14 password varchar(50) not null,15 name varchar(50) not null,16 address varchar(100) not null,17 phone int not null18 );19 20 insert into pet values (null,‘小狗one‘,90,90,‘牧羊犬‘,‘无‘,1);21 insert into pet values (null,‘小狗two‘,90,90,‘牧羊犬‘,‘无‘,2);22 insert into pet values (null,‘小狗three‘,90,90,‘牧羊犬‘,‘无‘,3);23 insert into pet values (null,‘小狗four‘,90,90,‘牧羊犬‘,‘无‘,0);24 insert into pet values (null,‘小狗five‘,90,90,‘牧羊犬‘,‘无‘,0);25 insert into pet values (null,‘小狗six‘,90,90,‘牧羊犬‘,‘无‘,0);26 27 insert into pet values (null,‘企鹅1‘,89,89,‘无‘,‘男‘,1);28 insert into pet values (null,‘企鹅2‘,89,89,‘无‘,‘女‘,0);29 insert into pet values (null,‘企鹅3‘,89,89,‘无‘,‘男‘,0);30 insert into pet values (null,‘企鹅4‘,89,89,‘无‘,‘女‘,0);31 insert into pet values (null,‘企鹅5‘,89,89,‘无‘,‘男‘,2);32 33 insert into master values (null,‘one‘,‘one1‘,‘张三‘,‘四川成都‘,4914676);34 insert into master values (null,‘two‘,‘two2‘,‘李四‘,‘四川成都‘,4914671);35 insert into master values (null,‘three‘,‘three3‘,‘王五‘,‘四川成都‘,4914672);36 insert into master values (null,‘four‘,‘four4‘,‘小明‘,‘四川成都‘,4914673);37 insert into master values (null,‘five‘,‘five5‘,‘小鬼‘,‘四川成都‘,4914674);