Java的家庭记账本程序（B）

日期：2019.2.3
博客期：029
星期日
　　看看今天想先完成jsp的连接操作，所以首先意识到自己的程序中，管理员可以对成员进行冻结、解封操作，所以先回去补了一下数据库的内容！成员的内容里多了一项isfrozen用于判定当前人物是否被冻结，之后又去改了JavaBean属性、DOC层、servlet，这几项改动确实挺累人的，一改就都要改！唉~难受！好在现在改好了！今天就这样吧！
  1 package sql.mysql;
  2 
  3 import java.sql.Connection;
  4 import java.sql.DriverManager;
  5 import java.sql.PreparedStatement;
  6 import java.sql.ResultSet;
  7 import java.sql.SQLException;
  8 import java.sql.Statement;
  9 
 10 import basic.people.Member;
 11 
 12 public class MySqlForMembers {
 13     //=======================================================================================【数据区】
 14     //JDBC 驱动名
 15     private final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
 16     //数据库 URL
 17     private final String DB_URL = "jdbc:mysql://localhost:3306/familycash?useSSL=false";
 18     //用户名
 19     private final String USER = "root";
 20     //密码
 21     private final String PASS = "123456";
 22     //构造器
 23     private Connection conn = null;
 24     private Statement stmt = null;
 25     private PreparedStatement pstmt = null;
 26     private ResultSet rs = null;
 27     //=======================================================================================【方法区】
 28     //-------《返回是否存在名称为name的数据》
 29     public boolean exist(String name){
 30         ReSetResult("SELECT name from member");
 31         try {
 32             while(rs.next())
 33             {
 34                 String t = rs.getString("name");
 35                 if(name.compareTo(t)==0)
 36                     return true;
 37             }
 38         } catch (SQLException e) {
 39             System.out.println("爷爷！你的数据库连接出现问题啦！");
 40         }
 41         return false;
 42     }
 43     //-------《返回是否存在型号为n的数据》
 44     public boolean exist(int code){
 45         ReSetResult("SELECT code from member");
 46         try {
 47             while(rs.next())
 48             {
 49                 String t = rs.getString("code");
 50                 if(code==Integer.parseInt(t))
 51                     return true;
 52             }
 53         } catch (SQLException e) {
 54             System.out.println("爷爷！你的数据库连接出现问题啦！");
 55         }
 56         return false;
 57     }
 58     //-------《重设rs》
 59     public void ReSetResult(String sql){
 60         try {
 61             stmt = conn.createStatement();
 62             rs = stmt.executeQuery(sql);
 63         } catch (SQLException e) {
 64             System.out.println("爷爷！你的数据库连接出现问题啦！");
 65         }
 66     }
 67     //-------《增删改查》
 68     //添加一个用户的数据
 69     public void Add(Member x){
 70         try {
 71             pstmt = (PreparedStatement) conn.prepareStatement("insert into member values (?,?,?,?,?)");
 72             pstmt.setInt(1, x.getCode());
 73             pstmt.setString(2, x.getName());
 74             pstmt.setString(3, x.getPassword());
 75             pstmt.setString(4, x.getCreatdate());
 76             pstmt.setInt(5, x.getIsFrozen());
 77             pstmt.executeUpdate();
 78             pstmt.close();
 79             } catch (SQLException e) {
 80                 System.out.println("爷爷！你的数据库连接出现问题啦！");    
 81         }
 82     }
 83     //删除一个用户的数据
 84     public void Delete(String Name){
 85         try {
 86             pstmt = (PreparedStatement) conn.prepareStatement("delete from member where name=?");
 87             pstmt.setString(1,Name);
 88             pstmt.executeUpdate();
 89         } catch (SQLException e) {
 90             System.out.println("爷爷！你的数据库连接出现问题啦！");
 91         }
 92     }
 93     public void Delete(int code){
 94         try {
 95             pstmt = (PreparedStatement) conn.prepareStatement("delete from member where code=?");
 96             pstmt.setInt(1,code);
 97             pstmt.executeUpdate();
 98         } catch (SQLException e) {
 99             System.out.println("爷爷！你的数据库连接出现问题啦！");
100         }
101     }
102     //更新一个用户的数据
103     public void Renew(String Name,Member x){
104         try {
105             Class.forName(JDBC_DRIVER);
106             conn = DriverManager.getConnection(DB_URL,USER,PASS);
107             stmt = conn.createStatement();
108             pstmt = (PreparedStatement) conn.prepareStatement("update member set code=?, name=?, password=?, creatdate=? , isfrozen=? where name=?");
109             pstmt.setInt(1, x.getCode());
110             pstmt.setString(2, x.getName());
111             pstmt.setString(3, x.getPassword());
112             pstmt.setString(4, x.getCreatdate());
113             pstmt.setInt(5, x.getIsFrozen());
114             pstmt.setString(6,Name);
115             pstmt.executeUpdate();
116             pstmt.close();
117         } catch (SQLException e) {
118             System.out.println("爷爷！你的数据库连接出现问题啦！");
119         } catch (ClassNotFoundException e) {
120             System.out.println("爷爷！你的Class驱动加载出现问题啦！");
121         }
122     }
123     public void Renew(int code,Member x){
124         try {
125             Class.forName(JDBC_DRIVER);
126             conn = DriverManager.getConnection(DB_URL,USER,PASS);
127             stmt = conn.createStatement();
128             pstmt = (PreparedStatement) conn.prepareStatement("update member set code=?, name=?, password=?, creatdate=? , isfrozen=? where code=?");
129             pstmt.setInt(1, x.getCode());
130             pstmt.setString(2, x.getName());
131             pstmt.setString(3, x.getPassword());
132             pstmt.setString(4, x.getCreatdate());
133             pstmt.setInt(5, x.getIsFrozen());
134             pstmt.setInt(6,code);
135             pstmt.executeUpdate();
136             pstmt.close();
137         } catch (SQLException e) {
138             System.out.println("爷爷！你的数据库连接出现问题啦！");
139         } catch (ClassNotFoundException e) {
140             System.out.println("爷爷！你的Class驱动加载出现问题啦！");
141         }
142     }
143     //查询第几个用户的数据
144     public Member GetFromSQL(int number){
145         if(number <=0||number>Length())
146             return null;
147         ReSetResult("SELECT code, name, password, creatdate, isfrozen FROM member");
148         try {
149             int i = 1;
150             while(rs.next()&&number>=i)
151             {
152                 int Code = Integer.parseInt(rs.getString("code"));
153                 String Name = rs.getString("name");
154                 String Password = rs.getString("password");
155                 String Creatdate = rs.getString("creatdate");
156                 int IsFrozen = Integer.parseInt(rs.getString("isfrozen"));
157                 if(number==i)
158                     return new Member(Code,Name,Password,Creatdate,IsFrozen);
159                 ++i;
160             }
161         } catch (SQLException e) {
162             System.out.println("爷爷！你的数据库连接出现问题啦！");
163         }
164         return null;
165     }
166     //查询一个用户的数据
167     public Member Refer(String name_s){
168         ReSetResult("SELECT code, name, password, creatdate, isfrozen FROM member");
169         Member s = null;
170         try {
171             while(rs.next())
172             {
173                 int Code = Integer.parseInt(rs.getString("code"));
174                 String Name = rs.getString("name");
175                 String Password = rs.getString("password");
176                 String Creatdate = rs.getString("creatdate");
177                 int IsFrozen = Integer.parseInt(rs.getString("isfrozen"));
178                 if(name_s.compareTo(Name)==0)
179                     return new Member(Code,Name,Password,Creatdate,IsFrozen);    
180             }
181         } catch (SQLException e) {
182             System.out.println("爷爷！你的数据库连接出现问题啦！");
183         }
184         return s;
185     }
186     public Member Refer(int code_s){
187         ReSetResult("SELECT code, name, password, creatdate, isfrozen FROM member");
188         Member s = null;
189         try {
190             while(rs.next())
191             {
192                 int Code = Integer.parseInt(rs.getString("code"));
193                 String Name = rs.getString("name");
194                 String Password = rs.getString("password");
195                 String Creatdate = rs.getString("creatdate");
196                 int IsFrozen = Integer.parseInt(rs.getString("isfrozen"));
197                 if(code_s==Code)
198                     return new Member(Code,Name,Password,Creatdate,IsFrozen);    
199             }
200         } catch (SQLException e) {
201             System.out.println("爷爷！你的数据库连接出现问题啦！");
202         }
203         return s;
204     }
205     //-------《数据数》
206     public int Length(){
207         ReSetResult("SELECT code, name, password, creatdate, isfrozen FROM member");
208         int l = 0;
209         try {
210             while(rs.next())
211             {
212                 ++l;
213             }
214         } catch (SQLException e) {
215             System.out.println("爷爷！你的数据库连接出现问题啦！");
216         }
217         return l;
218     }
219     //-------《最大的编号值》
220     public int MaxCode(){
221         ReSetResult("SELECT code FROM member");
222         int l = 0;
223         try {
224             while(rs.next())
225             {
226                 int Code = Integer.parseInt(rs.getString("code"));
227                 if(l<Code)
228                     l = Code;
229             }
230         } catch (SQLException e) {
231             System.out.println("爷爷！你的数据库连接出现问题啦！");
232         }
233         return l;
234     }
235     //-------《释放》
236     public void free(){
237         try {
238             if(pstmt!=null)
239                 pstmt.close();
240             if(stmt!=null)
241                 stmt.close();
242             if(conn!=null)
243                 conn.close();
244         } catch (SQLException e) {
245             System.out.println("爷爷！你的数据库连接出现问题啦！");
246         }
247     }
248     //-------《构造方法》
249     public MySqlForMembers(){
250         try {
251             Class.forName(JDBC_DRIVER);
252             conn = DriverManager.getConnection(DB_URL,USER,PASS);
253         } catch (ClassNotFoundException e1) {
254             System.out.println("爷爷！你的Class驱动加载出现问题啦！");
255         } catch (SQLException e) {
256             System.out.println("爷爷！你的数据库连接出现问题啦！");
257         }
258     }
259     //-------《主方法》
260     public static void main(String[] args){
261         MySqlForMembers m = new MySqlForMembers();
262         System.out.println();
263         for(int i=1;i<=m.Length();++i)
264         {
265             m.GetFromSQL(i).display();
266         }
267         m.free();
268     }
269 }
MySqlForMember
本代码由于涉及本人的部分个人信息——已于2019.6.20修改，代码仍然可用，请大家放心