Java学习12.02

图书管理系统——dao层

dao.java

  1 import java.sql.Connection;
  2 import java.sql.PreparedStatement;
  3 import java.sql.ResultSet;
  4 import java.sql.SQLException;
  5 import java.sql.Statement;
  6 import java.util.ArrayList;
  7 import java.util.List;
  8 
  9 import bean.Book;
 10 import bean.DButil;
 11 
 12 
 13 
 14 public class dao {
 15     
 16     Book activity = new Book();
 17 
 18     public  ArrayList<Book> select(String nameOfhouseholder){//查找
 19         Connection con=null;
 20         PreparedStatement psts=null;
 21         ResultSet rs=null;
 22         ArrayList<Book> ll=new ArrayList<Book>();
 23         try {
 24             con=DButil.getCon();
 25             String sql="select * from book where nameBook=?";
 26             psts=con.prepareStatement(sql);
 27             psts.setString(1, nameOfhouseholder);
 28             rs=psts.executeQuery();
 29             if(rs.next()){
 30                 String nameBook= rs.getString("nameBook");
 31                 String idBook=rs.getString("idBook");
 32                 String namePress=rs.getString("namePress");
 33                 String nameWritter=rs.getString("nameWritter");
 34                 String number=rs.getString("number");
 35             
 36 
 37                 
 38                 Book activity=new Book(idBook,nameBook,nameWritter,namePress,number);
 39                 ll.add(activity);
 40             }
 41         } catch (SQLException e) {
 42             e.printStackTrace();
 43         }
 44         
 45         return ll;
 46     }
 47     
 48 
 49     //根据名称进行查询
 50         @SuppressWarnings("static-access")
 51         public static Book selectName(String name) {
 52             //String tablename = "population";
 53             System.out.println("select book where nameBook = "+name);
 54             Book Book=null;
 55             DButil db=new DButil();
 56             Connection con = db.getCon();
 57             try {
 58                 Statement stm = con.createStatement();
 59                 
 60                 ResultSet rs = stm.executeQuery("select * from book where nameBook='" + name + "'");
 61                 
 62                 if(rs.next())
 63                 {
 64                     Book = new Book();
 65                     System.out.println("select the info from mysql");
 66                     Book.setNameBook(rs.getString("nameBook"));
 67                     Book.setIdBook(rs.getString("idBook"));
 68                     Book.setNamePress(rs.getString("namePress"));
 69                     Book.setNameWritter(rs.getString("nameWritter"));
 70                     Book.setNumber(rs.getString("number"));
 71                 
 72                     
 73                     System.out.println("name of the Book is "+rs.getString("nameBook"));
 74                 }
 75                 rs.close();
 76                 stm.close();
 77                 con.close();
 78                 //db.close(rs,stm, con);
 79             }catch(Exception e) {
 80                 e.printStackTrace();
 81             }
 82             return Book;
 83         }
 84     
 85     public   int add(Book activity){
 86         Connection con=null;
 87         PreparedStatement psts=null;
 88         int a=0;
 89         try {
 90             con=DButil.getCon();
 91             String sql="insert into book(idBook,nameBook,nameWritter,namePress,number) values(?,?,?,?,?)";
 92             psts=con.prepareStatement(sql);
 93             psts.setString(1, activity.getIdBook());
 94             psts.setString(2, activity.getNameBook());
 95             psts.setString(3, activity.getNameWritter());
 96             psts.setString(4, activity.getNamePress());
 97             psts.setString(5, activity.getNumber());
 98         
 99             
100             a=psts.executeUpdate();
101             psts.close();
102             con.close();
103         } catch (SQLException e) {
104             e.printStackTrace();
105         }
106         return a;
107     
108     }
109 
110     
111     
112    /* @SuppressWarnings("static-access")
113     public boolean update(Book activity){
114         Connection con=null;
115         PreparedStatement psts=null;
116         int a=0;
117         //Connection con=null;
118         //PreparedStatement psts=null;
119         //db db=new db();
120         try {
121             con=DButil.getCon();
122             String sql="update studentInformation set IDnumber=?,sex=?,classroom=?,major=? where name=?";
123             
124             psts=con.prepareStatement(sql);
125             psts.setString(5, activity.getName());
126             psts.setString(1, activity.getIDnumber());
127             psts.setString(2, activity.getSex());
128             psts.setString(3, activity.getClassroom());
129             psts.setString(4, activity.getMajor());
130             System.out.print(sql);
131             psts.executeUpdate();
132             
133             a=psts.executeUpdate();
134             psts.close();
135             con.close();
136         }
137         catch(SQLException e)
138         {
139             e.printStackTrace();
140             //return false;
141         }
142         return true;
143     }*/
144     
145     public  boolean findNameBook(String name){//查找姓名
146         boolean flag=true;//找不到
147         Connection con=null;
148         PreparedStatement psts=null;
149         ResultSet rs=null;
150         try {
151             con=DButil.getCon();
152             String sql="select * from book where nameBook=?";
153             psts=con.prepareStatement(sql);
154             psts.setString(1, activity.getNameBook());
155             rs=psts.executeQuery();
156             while(rs.next()){
157                 flag=true;
158             }
159         } catch (SQLException e) {
160             e.printStackTrace();
161         }
162         
163         return flag;
164     }
165     
166     
167     public ArrayList<Book> getAllinformation(){
168         Connection con=null;
169         PreparedStatement psts=null;
170         ResultSet rs=null;
171         ArrayList<Book> ll=new ArrayList<Book>();
172         try {
173             con=DButil.getCon();
174             String sql="select * from book";
175             psts=con.prepareStatement(sql);
176             rs=psts.executeQuery();//记录返回的结果
177             while(rs.next()){
178             
179                 
180                 
181                 String nameBook= rs.getString("nameBook");
182                 String idBook=rs.getString("idBook");
183                 String namePress=rs.getString("namePress");
184                 String nameWritter=rs.getString("nameWritter");
185                 String number=rs.getString("number");
186                 Book activity=new Book(idBook,nameBook,nameWritter,namePress,number);
187                 
188                 ll.add(activity);
189             }
190         } catch (SQLException e) {
191             e.printStackTrace();
192         }
193         return ll;
194     }
195     
196     public static List<Book> loadview(String sql) {
197         Connection con=null;
198         PreparedStatement psts=null;
199 
200         System.out.println("loadview??sql:"+sql);
201         String sql1="select * from book "+ sql;
202         System.out.println("loadview??sql1:"+sql1);
203         List<Book> list =new ArrayList<Book>();
204         Connection conn = DButil.getCon();
205         Statement state = null;
206         ResultSet rs = null;
207         Book yi=null;
208             try 
209             {
210                 state = conn.createStatement();
211                 rs = state.executeQuery(sql1);
212                 while(rs.next()) 
213                 {
214                     
215             
216                     
217                     String nameBook= rs.getString("nameBook");
218                     String idBook=rs.getString("idBook");
219                     String namePress=rs.getString("namePress");
220                     String nameWritter=rs.getString("nameWritter");
221                     String number=rs.getString("number");
222                     
223                     
224                     System.out.println("namer"+nameBook);
225                 
226                     yi=new Book(idBook,nameBook,nameWritter,namePress,number);
227                     list.add(yi);
228                     System.out.println("name1"+yi.getNameBook());
229                 }
230             
231                 ///db.close(rs, state, conn);
232                 rs.close();
233                 state.close();
234                 conn.close();
235                 
236             } catch (SQLException e) {
237                 
238                 e.printStackTrace();
239             } 
240 
241         return list;
242     }
243     
244 }

daoindex.java

 1 import java.sql.Connection;
 2 import java.sql.PreparedStatement;
 3 import java.sql.ResultSet;
 4 import java.sql.SQLException;
 5 import java.sql.Statement;
 6 import java.util.ArrayList;
 7 import java.util.List;
 8 
 9 import bean.user;
10 import bean.DButil;
11 
12 
13 
14 public class daoindex {
15     
16     studentInfo activity = new studentInfo();
17 
18     public  boolean judge(user activity){
19         boolean flag=false;//找不到
20         Connection con=null;
21         PreparedStatement psts=null;
22         PreparedStatement psts2=null;
23         ResultSet rs=null;
24         ResultSet rs2=null;
25         try {
26             con=DButil.getCon();
27             String sql="select * from user where username=?";
28             String sql2="select * from user where password=?";
29             psts=con.prepareStatement(sql);
30             psts2=con.prepareStatement(sql2);
31             psts.setString(1, activity.getUsername());
32             psts2.setString(2, activity.getPassword());
33             rs=psts.executeQuery();
34             rs2=psts2.executeQuery();
35             while(rs.next()&&rs2.next()){
36                 flag=true;
37             }
38         } catch (SQLException e) {
39             e.printStackTrace();
40         }
41         
42         return flag;
43     }
44 
45     public  boolean finduser(String name){//查找姓名
46         boolean flag=false;//找不到
47         Connection con=null;
48         PreparedStatement psts=null;
49         ResultSet rs=null;
50         try {
51             con=DButil.getCon();
52             String sql="select * from user where username=?";
53             psts=con.prepareStatement(sql);
54             psts.setString(1, name);
55             rs=psts.executeQuery();
56             while(rs.next()){
57                 flag=true;
58             }
59         } catch (SQLException e) {
60             e.printStackTrace();
61         }
62         
63         return flag;
64     }
65     
66     
67     
68 }

daopeople.java

  1 import java.sql.Connection;
  2 import java.sql.PreparedStatement;
  3 import java.sql.ResultSet;
  4 import java.sql.SQLException;
  5 import java.sql.Statement;
  6 import java.util.ArrayList;
  7 import java.util.List;
  8 
  9 import bean.studentInfo;
 10 import bean.DButil;
 11 
 12 
 13 
 14 public class daopeople {
 15     
 16     studentInfo activity = new studentInfo();
 17 
 18     public  ArrayList<studentInfo> select(String nameOfhouseholder){//查找
 19         Connection con=null;
 20         PreparedStatement psts=null;
 21         ResultSet rs=null;
 22         ArrayList<studentInfo> ll=new ArrayList<studentInfo>();
 23         try {
 24             con=DButil.getCon();
 25             String sql="select * from user where name=?";
 26             psts=con.prepareStatement(sql);
 27             psts.setString(1, nameOfhouseholder);
 28             rs=psts.executeQuery();
 29             if(rs.next()){
 30                 String name= rs.getString("name");
 31                 String IDnumber=rs.getString("IDnumber");
 32                 String sex=rs.getString("sex");
 33                 
 34                 String major=rs.getString("major");
 35 
 36                 
 37                 studentInfo activity=new studentInfo(name,IDnumber,sex,major);
 38                 ll.add(activity);
 39             }
 40         } catch (SQLException e) {
 41             e.printStackTrace();
 42         }
 43         
 44         return ll;
 45     }
 46     
 47 
 48     //根据名称进行查询
 49         @SuppressWarnings("static-access")
 50         public static studentInfo selectName(String name) {
 51             //String tablename = "population";
 52             System.out.println("select studentInfo where name = "+name);
 53             studentInfo studentInfo=null;
 54             DButil db=new DButil();
 55             Connection con = db.getCon();
 56             try {
 57                 Statement stm = con.createStatement();
 58                 
 59                 ResultSet rs = stm.executeQuery("select * from user where name='" + name + "'");
 60                 
 61                 if(rs.next())
 62                 {
 63                     studentInfo = new studentInfo();
 64                     System.out.println("select the info from mysql");
 65                     
 66                     studentInfo.setName(rs.getString("name"));
 67                     studentInfo.setIDnumber(rs.getString("IDnumber"));
 68                     studentInfo.setSex(rs.getString("sex"));
 69                 
 70                     studentInfo.setMajor(rs.getString("major"));
 71                 
 72                     
 73                     System.out.println("name of the studentInfo is "+rs.getString("name"));
 74                 }
 75                 rs.close();
 76                 stm.close();
 77                 con.close();
 78                 //db.close(rs,stm, con);
 79             }catch(Exception e) {
 80                 e.printStackTrace();
 81             }
 82             return studentInfo;
 83         }
 84     
 85     public   int add(studentInfo activity){
 86         Connection con=null;
 87         PreparedStatement psts=null;
 88         int a=0;
 89         try {
 90             con=DButil.getCon();
 91             String sql="insert into user(name,IDnumber,sex,major) values(?,?,?,?)";
 92             psts=con.prepareStatement(sql);
 93             psts.setString(1, activity.getName());
 94             psts.setString(2, activity.getIDnumber());
 95             psts.setString(3, activity.getSex());        
 96             psts.setString(4, activity.getMajor());
 97         
 98             a=psts.executeUpdate();
 99             psts.close();
100             con.close();
101         } catch (SQLException e) {
102             e.printStackTrace();
103         }
104         return a;
105     
106     }
107 
108     
109     @SuppressWarnings("static-access")
110     public boolean update(studentInfo activity){
111         Connection con=null;
112         PreparedStatement psts=null;
113         int a=0;
114         //Connection con=null;
115         //PreparedStatement psts=null;
116         //db db=new db();
117         try {
118             con=DButil.getCon();
119             String sql="update user set IDnumber=?,sex=?,major=? where name=?";
120             
121             psts=con.prepareStatement(sql);
122             psts.setString(4, activity.getName());
123             psts.setString(1, activity.getIDnumber());
124             psts.setString(2, activity.getSex());
125         
126             psts.setString(3, activity.getMajor());
127             System.out.print(sql);
128             psts.executeUpdate();
129             
130             a=psts.executeUpdate();
131             psts.close();
132             con.close();
133         }
134         catch(SQLException e)
135         {
136             e.printStackTrace();
137             //return false;
138         }
139         return true;
140     }
141     
142     public  boolean findNamepeople(String name){//查找姓名
143         boolean flag=false;//找不到
144         Connection con=null;
145         PreparedStatement psts=null;
146         ResultSet rs=null;
147         try {
148             con=DButil.getCon();
149             String sql="select * from user where name=?";
150             psts=con.prepareStatement(sql);
151             psts.setString(1, name);
152             rs=psts.executeQuery();
153             while(rs.next()){
154                 flag=true;
155             }
156         } catch (SQLException e) {
157             e.printStackTrace();
158         }
159         
160         return flag;
161     }
162     public ArrayList<studentInfo> getAllinformation(){
163         Connection con=null;
164         PreparedStatement psts=null;
165         ResultSet rs=null;
166         ArrayList<studentInfo> ll=new ArrayList<studentInfo>();
167         try {
168             con=DButil.getCon();
169             String sql="select * from user";
170             psts=con.prepareStatement(sql);
171             rs=psts.executeQuery();//记录返回的结果
172             while(rs.next()){
173             
174                 String name= rs.getString("name");
175                 String IDnumber=rs.getString("IDnumber");
176                 String sex=rs.getString("sex");
177             
178                 String major=rs.getString("major");
179                 
180                 
181                 studentInfo activity = new studentInfo(name,IDnumber,sex,major);
182                 ll.add(activity);
183             }
184         } catch (SQLException e) {
185             e.printStackTrace();
186         }
187         return ll;
188     }
189     
190     public static List<studentInfo> loadview(String sql) {
191         Connection con=null;
192         PreparedStatement psts=null;
193 
194         System.out.println("loadview??sql:"+sql);
195         String sql1="select * from user "+ sql;
196         System.out.println("loadview??sql1:"+sql1);
197         List<studentInfo> list =new ArrayList<studentInfo>();
198         Connection conn = DButil.getCon();
199         Statement state = null;
200         ResultSet rs = null;
201         studentInfo yi=null;
202             try 
203             {
204                 state = conn.createStatement();
205                 rs = state.executeQuery(sql1);
206                 while(rs.next()) 
207                 {
208                     
209                     String name= rs.getString("name");
210                     String IDnumber=rs.getString("IDnumber");
211                     String sex=rs.getString("sex");
212                     
213                     String major=rs.getString("major");
214                     
215                     
216                     
217                     
218                     System.out.println("namer"+name);
219                  
220                     yi=new studentInfo(name,IDnumber,sex,major);
221                     list.add(yi);
222                     System.out.println("name1"+yi.getName());
223                 }
224             
225                 ///db.close(rs, state, conn);
226                 rs.close();
227                 state.close();
228                 conn.close();
229                 
230             } catch (SQLException e) {
231                 
232                 e.printStackTrace();
233             } 
234 
235         return list;
236     }
237     
238 }

明天计划：

明天练习DBUtil层

posted on 2020-12-02 22:39 桑榆非晚柠月如风阅读(38) 评论(0) 编辑收藏举报