人事管理系统——数据库操作类

package PersonSystem; 2 3 import java.sql.*; 4 /** 5 * 6 * 连接数据库的类 7 * 8 */ 9 public class Database 10 { 11 private Statement stmt = null; 12 ResultSet rs = null; 13 private Connection conn = null; 14 String sql; 15 String strurl = "jdbc:odbc:HrMS"; 16 17 public Database(){ 18 } 19 /** 20 * 打开数据库连接 21 */ 22 public void OpenConn() throws Exception 23 { 24 try{ 25 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 26 conn=DriverManager.getConnection(strurl); 27 } 28 catch(Exception e){ 29 System.err.println("OpenConn:"+e.getMessage()); 30 } 31 } 32 /** 33 * 执行SQL语句，返回结果集rs 34 */ 35 public ResultSet executeQuery(String sql) 36 { 37 stmt = null; 38 rs = null; 39 try{ 40 stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); 41 rs=stmt.executeQuery(sql); 42 } 43 catch(SQLException e){ 44 System.err.println("executeQuery:"+e.getMessage()); 45 } 46 return rs; 47 } 48 /** 49 * 执行SQL语句 50 */ 51 public void executeUpdate(String sql) 52 { 53 stmt = null; 54 rs = null; 55 try{ 56 stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); 57 rs=stmt.executeQuery(sql); 58 conn.commit(); 59 } 60 catch(SQLException e){ 61 System.err.println("executeUpdate:"+e.getMessage()); 62 } 63 } 64 public void closeStmt() 65 { 66 try{ 67 stmt.close(); 68 } 69 catch(SQLException e){ 70 System.err.println("closeStmt:"+e.getMessage()); 71 } 72 } 73 /** 74 * 关闭数据库连接 75 */ 76 public void closeConn(){ 77 try{ 78 conn.close(); 79 } 80 catch(SQLException ex){ 81 System.err.println("aq.closeConn:"+ex.getMessage()); 82 } 83 } 84 /** 85 * 转换编码 86 */ 87 public static String toGBK(String str) 88 { 89 try{ 90 if(str==null) 91 str = ""; 92 else 93 str = new String(str.getBytes("ISO-8859-1"),"GBK"); 94 } 95 catch(Exception e){ 96 System.out.println(e); 97 } 98 return str; 99 } 100 } 1

 有关人员信息数据库操作的类主要代码：

  1 package PersonSystem;
  2 
  3 //import java.util.*;
  4 import java.sql.*;
  5 import javax.swing.*;
  6 /**
  7  * 
  8  * 有关人员信息数据库操作的类
  9  *
 10  */
 11 public class PersonBean 
 12 {
 13     String sql;
 14     ResultSet rs = null;
 15     
 16     String field1;   //PersonID
 17     String field2;   //Name
 18     String field3;   //Sex
 19     String field4;   //Birth
 20     String field5;   //Nat
 21     String field6;   //Address
 22     String field7;   //DeptID
 23     String field8;   //Salary
 24     String field9;   //Assess
 25     String field10;  //Other
 26     
 27     String colName;  //列名
 28     String colValue;  //列值
 29     String colValue2;  //列值
 30     
 31     /**
 32      * 添加信息
 33      */
 34     public void add(String f1,String f2,String f3,String f4,String f5,
 35                     String f6,String f7,String f8,String f9,String f10)
 36     {
 37         Database DB = new Database();
 38         
 39         this.field1 = f1;
 40         this.field2 = f2;
 41         this.field3 = f3;
 42         this.field4 = f4;
 43         this.field5 = f5;
 44         this.field6 = f6;
 45         this.field7 = f7;
 46         this.field8 = f8;
 47         this.field9 = f9;
 48         this.field10 = f10;
 49         
 50         if(field2 == null||field2.equals("")){
 51             JOptionPane.showMessageDialog(null, "请输入员工姓名","错误",JOptionPane.ERROR_MESSAGE);
 52             return;
 53         }
 54         else if(field3 == null||field3.equals("")){
 55             JOptionPane.showMessageDialog(null, "请输入性别","错误",JOptionPane.ERROR_MESSAGE);
 56             return;
 57         }
 58         else if(field4 == null||field4.equals("")){
 59             JOptionPane.showMessageDialog(null, "请输入出生年月","错误",JOptionPane.ERROR_MESSAGE);
 60             return;
 61         }
 62         else if(field5 == null||field5.equals("")){
 63             JOptionPane.showMessageDialog(null, "请输入民族","错误",JOptionPane.ERROR_MESSAGE);
 64             return;
 65         }
 66         else if(field6 == null||field6.equals("")){
 67             JOptionPane.showMessageDialog(null, "请输入地址","错误",JOptionPane.ERROR_MESSAGE);
 68             return;
 69         }
 70         else if(field7 == null||field7.equals("")){
 71             JOptionPane.showMessageDialog(null, "请输入部门","错误",JOptionPane.ERROR_MESSAGE);
 72             return;
 73         }
 74         else if(field8 == null||field8.equals("")){
 75             JOptionPane.showMessageDialog(null, "请输入薪酬","错误",JOptionPane.ERROR_MESSAGE);
 76             return;
 77         }
 78         else{
 79             sql = "insert into Person(PersonID,Name,Sex,Birth,Nat,Address,DeptID,Salary,Assess,Other)"
 80                 +"values(‘"+field1+"‘,‘"+field2+"‘,‘"+field3+"‘,‘"+field4+"‘,‘"+field5+"‘,"
 81                 + "‘"+field6+"‘,‘"+field7+"‘,‘"+field8+"‘,‘"+field9+"‘,‘"+field10+"‘)";
 82             try{
 83                 DB.OpenConn();
 84                 DB.executeUpdate(sql);
 85                 JOptionPane.showMessageDialog(null, "成功添加一条记录！");
 86             }
 87             catch(Exception e){
 88                 System.out.println(e);
 89                 JOptionPane.showMessageDialog(null, "保存失败","错误",JOptionPane.ERROR_MESSAGE);
 90             }
 91             finally{
 92                 DB.closeStmt();
 93                 DB.closeConn();
 94             }
 95         }
 96     }
 97     
 98     /**
 99      * 修改信息
100      */
101     public void modify(String f1,String f2,String f3,String f4,String f5,
102                     String f6,String f7,String f8,String f9,String f10)
103     {
104         Database DB = new Database();
105         
106         this.field1 = f1;
107         this.field2 = f2;
108         this.field3 = f3;
109         this.field4 = f4;
110         this.field5 = f5;
111         this.field6 = f6;
112         this.field7 = f7;
113         this.field8 = f8;
114         this.field9 = f9;
115         this.field10 = f10;
116         
117         if(field2 == null||field2.equals("")){
118             JOptionPane.showMessageDialog(null, "请输入员工姓名","错误",JOptionPane.ERROR_MESSAGE);
119             return;
120         }
121         else if(field3 == null||field3.equals("")){
122             JOptionPane.showMessageDialog(null, "请输入性别","错误",JOptionPane.ERROR_MESSAGE);
123             return;
124         }
125         else if(field4 == null||field4.equals("")){
126             JOptionPane.showMessageDialog(null, "请输入出生年月","错误",JOptionPane.ERROR_MESSAGE);
127             return;
128         }
129         else if(field5 == null||field5.equals("")){
130             JOptionPane.showMessageDialog(null, "请输入民族","错误",JOptionPane.ERROR_MESSAGE);
131             return;
132         }
133         else if(field6 == null||field6.equals("")){
134             JOptionPane.showMessageDialog(null, "请输入地址","错误",JOptionPane.ERROR_MESSAGE);
135             return;
136         }
137         else if(field7 == null||field7.equals("")){
138             JOptionPane.showMessageDialog(null, "请输入部门","错误",JOptionPane.ERROR_MESSAGE);
139             return;
140         }
141         else if(field8 == null||field8.equals("")){
142             JOptionPane.showMessageDialog(null, "请输入薪酬","错误",JOptionPane.ERROR_MESSAGE);
143             return;
144         }
145         else{
146             sql = "update Person set Name= ‘"+field2+"‘,"+"Sex=‘"+field3+"‘,‘"+"Birth=‘"+field4+"‘,‘"
147                  +"Nat=‘"+field5+"‘,‘"+"Address=‘"+field6+"‘,‘"+"‘"+"DeptID=‘"+field7+"‘,‘"+"Salary=‘"+field8+"‘,‘"
148                     +"Assess=‘"+field9+"‘,‘"+"Other=‘"+field10+"‘,‘"+"where PersonID=‘"+field1+"";
149             try{
150                 DB.OpenConn();
151                 DB.executeUpdate(sql);
152                 JOptionPane.showMessageDialog(null, "成功修改一条新的记录！");
153             }
154             catch(Exception e){
155                 System.out.println(e);
156                 JOptionPane.showMessageDialog(null, "更新失败","错误",JOptionPane.ERROR_MESSAGE);
157             }
158             finally{
159                 DB.closeStmt();
160                 DB.closeConn();
161             }
162         }
163     }
164     /**
165      * 删除信息
166      */
167     public void delete(String f1)
168     {
169         Database DB = new Database();
170         this.field1 = f1;
171         
172         sql = "delect from Person where PersonID ="+field1+"";
173         try{
174             DB.OpenConn();
175             DB.executeUpdate(sql);
176             JOptionPane.showMessageDialog(null, "成功删除一条记录！");
177         }
178         catch(Exception e){
179             System.out.println(e);
180             JOptionPane.showMessageDialog(null, "删除失败","错误",JOptionPane.ERROR_MESSAGE);
181         }
182         finally{
183             DB.closeStmt();
184             DB.closeConn();
185         }
186     }
187     /**
188      * 根据编号查询信息
189      */
190     public String[] search(String f1)
191     {
192         Database DB = new Database();
193         this.field1 = f1;
194         String[] s = new String[10];
195         sql = "select * from Person where PersonID = "+field1+"";
196         try{
197             DB.OpenConn();
198             DB.executeUpdate(sql);
199             if(rs.next()){
200                 s[0] = rs.getString("PersonID");
201                 s[1] = rs.getString("Name");
202                 s[2] = rs.getString("Sex");
203                 s[3] = rs.getString("Birth");
204                 s[4] = rs.getString("Nat");
205                 s[5] = rs.getString("Address");
206                 s[6] = rs.getString("DeptID");
207                 s[7] = rs.getString("Salary");
208                 s[8] = rs.getString("Assess");
209                 s[9] = rs.getString("Other");
210             }
211             else
212                 s = null;
213         }
214         catch(Exception e){
215         }
216         finally{
217             DB.closeStmt();
218             DB.closeConn();
219         }
220         return s;
221     }
222     /**
223      * 人员记录综合查询（查询所有记录）
224      */
225     public String[][] searchAllForNode()
226     {
227         Database DB = new Database();
228         String[][] sn = null;
229         int row = 0;
230         int i = 0;
231         sql = "select PersonID,Name,Sex,Dept.DeptID as DeptID,B_Dept,S_Dept,Salary,Assess "
232                 + "from Dept,Person where Dept.DeptID = Person.DeptID order by PersonID";
233         try{
234             DB.OpenConn();
235             rs = DB.executeQuery(sql);
236             if(rs.last()){
237                 row = rs.getRow();
238             }
239             if(row==0){
240                 sn = new String[1][6];
241                 sn[0][0] = " ";
242                 sn[0][1] = " ";
243                 sn[0][2] = " ";
244                 sn[0][3] = " ";
245                 sn[0][4] = " ";
246                 sn[0][5] = " ";
247             }
248             else{
249                 sn = new String[row][6];
250                 rs.first();
251                 rs.previous();
252                 while(rs.next()){
253                     sn[i][0] = rs.getString("PersonID");
254                     sn[i][1] = rs.getString("Name");
255                     sn[i][2] = rs.getString("Sex");
256                     sn[i][3] = rs.getString("B_Dept")+"-"+rs.getString("S_Dept");
257                     sn[i][4] = rs.getString("Salary");
258                     sn[i][5] = rs.getString("Assess");
259                     i++;
260                 }
261             }
262         }
263         catch(Exception e){
264             
265         }
266         finally{
267             DB.closeStmt();
268             DB.closeConn();
269         }
270         return sn;
271     }
272     /**
273      * 修改信息
274      */
275     public void updateDept(String f1,String f7)
276     {
277         Database DB = new Database();
278         this.field1 = f1;
279         this.field7 = f7;
280         
281         sql = "update Person set DeptID = "+field7+"where PersonID ="+field1;
282         try{
283             DB.OpenConn();
284             DB.executeUpdate(sql);
285             JOptionPane.showMessageDialog(null, "人员调动成功！");
286         }
287         catch(Exception e){
288             System.out.println(e);
289             JOptionPane.showMessageDialog(null, "更新失败","错误",JOptionPane.ERROR_MESSAGE);
290         }
291         finally{
292             DB.closeStmt();
293             DB.closeConn();
294         }
295     }
296     /**
297      * 修改信息
298      */
299     public void updateSalary(String f1,String f8)
300     {
301         Database DB = new Database();
302         this.field1 = f1;
303         this.field8 = f8;
304         
305         sql = "update Person set Salary =‘"+field8+"‘where PersonID ="+field1;
306         try{
307             DB.OpenConn();
308             DB.executeUpdate(sql);
309             JOptionPane.showMessageDialog(null, "劳资更改成功！");
310         }
311         catch(Exception e){
312             System.out.println(e);
313             JOptionPane.showMessageDialog(null, "更新失败","错误",JOptionPane.ERROR_MESSAGE);
314         }
315         finally{
316             DB.closeStmt();
317             DB.closeConn();
318         }
319     }
320     /**
321      * 修改信息
322      */
323     public void updateAssess(String f1,String f9)
324     {
325         Database DB = new Database();
326         this.field1 = f1;
327         this.field8 = f9;
328         
329         sql = "update Person set Assess =‘"+field9+"‘where PersonID ="+field1;
330         try{
331             DB.OpenConn();
332             DB.executeUpdate(sql);
333             JOptionPane.showMessageDialog(null, "人员考核成功！");
334         }
335         catch(Exception e){
336             System.out.println(e);
337             JOptionPane.showMessageDialog(null, "更新失败","错误",JOptionPane.ERROR_MESSAGE);
338         }
339         finally{
340             DB.closeStmt();
341             DB.closeConn();
342         }
343     }
344     /**
345      * 人员信息综合查询
346      */
347     public String[][] searchAll()
348     {
349         Database DB = new Database();
350         String[][] sn = null;
351         int row = 0;
352         int i = 0;
353         sql = "select * from Person order by PersonID";
354         try{
355             DB.OpenConn();
356             rs = DB.executeQuery(sql);
357             if(rs.last()){
358                 row = rs.getRow();
359             }
360             if(row==0){
361                 sn = new String[1][6];
362                 sn[0][0] = " ";
363                 sn[0][1] = " ";
364                 sn[0][2] = " ";
365                 sn[0][3] = " ";
366                 sn[0][4] = " ";
367                 sn[0][5] = " ";
368             }
369             else{
370                 sn = new String[row][6];
371                 rs.first();
372                 rs.previous();
373                 while(rs.next()){
374                     sn[i][0] = rs.getString("PersonID");
375                     sn[i][1] = rs.getString("Name");
376                     sn[i][2] = rs.getString("Sex");
377                     sn[i][3] = rs.getString("Nat");
378                     sn[i][4] = rs.getString("Address");
379                     DeptBean dp = new DeptBean();
380                     sn[i][5] = dp.getDeptStr(rs.getString("DeptID"));
381                     i++;
382                 }
383             }
384         }
385         catch(Exception e){
386             
387         }
388         finally{
389             DB.closeStmt();
390             DB.closeConn();
391         }
392         return sn;
393     }
394     /**
395      * 获得新的ID
396      */
397     public int getId()
398     {
399         Database DB = new Database();
400         int ID = 1;
401         sql = "select max(PersonID) from Person";
402         try{
403             DB.OpenConn();
404             rs = DB.executeQuery(sql);
405             if(rs.next()){
406                 ID = rs.getInt(1) + 1;
407             }
408             else
409                 ID = 1;
410         }
411         catch(Exception e){
412             
413         }
414         finally{
415             DB.closeStmt();
416             DB.closeConn();
417         }
418         return ID;
419     }
420     /**
421      * 取得DeptID
422      */
423     public String getDeptId(String f1)
424     {
425         Database DB = new Database();
426         sql = "select DeptID from Person where personID = "+f1;
427         String deptid = null;
428         try{
429             DB.OpenConn();
430             rs = DB.executeQuery(sql);
431             if(rs.next()){
432                 deptid = rs.getString("DeptID");
433             }
434             els