packaging updated
[staff/due1/sed-hs15-srs-purple.git] / src / main / java / ch / bfh / ti / srs / noTest / DataHandlerForExcel.java
... / ...
CommitLineData
1package ch.bfh.ti.srs.noTest;\r
2\r
3import java.sql.Connection;\r
4import java.sql.DriverManager;\r
5import java.sql.ResultSet;\r
6import java.sql.Statement;\r
7import java.util.HashSet;\r
8import java.util.Set;\r
9\r
10import ch.bfh.ti.srs.datahandler.DataHandler;\r
11import ch.bfh.ti.srs.dataschema.User;\r
12/**\r
13 * @author Michael Grossen\r
14 */\r
15public class DataHandlerForExcel implements DataHandler{\r
16 String filePath;\r
17 Set<User> userList = new HashSet<User>();\r
18 \r
19 static\r
20 {\r
21 try { \r
22 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");\r
23 } \r
24 catch (Exception e) {\r
25 System.err.println(e);\r
26 }\r
27 }\r
28 \r
29 public DataHandlerForExcel(String filePath) throws Exception{\r
30 this.filePath = filePath;\r
31 updateUserList();\r
32 }\r
33 \r
34 @Override\r
35 public User findUser(String userName) {\r
36 if(userName == null) throw new IllegalArgumentException("Username must not be null");\r
37 for (User user : userList) {\r
38 if(user.getUserName().equals(userName)) return user;\r
39 }\r
40 return null;\r
41 }\r
42\r
43 @Override\r
44 public void addUser(User user) throws Exception {\r
45 if(findUser(user.getUserName()) != null) throw new Exception("User already exists!");\r
46 insertNewUser(user);\r
47 updateUserList();\r
48 }\r
49\r
50 private void updateUserList() throws Exception\r
51 {\r
52 Connection conn=null;\r
53 Statement stmt=null;\r
54 String sql="";\r
55 ResultSet rs=null;\r
56 \r
57 userList.clear();\r
58 \r
59 try {\r
60 conn=DriverManager.getConnection("jdbc:odbc:excel","","");\r
61 stmt=conn.createStatement();\r
62 sql="select * from [User$]";\r
63 rs=stmt.executeQuery(sql);\r
64\r
65 while(rs.next()){\r
66 User user = new User(rs.getString("USERNAME"),\r
67 rs.getString("PASSWORD"),\r
68 rs.getString("MAIL"),\r
69 rs.getString("NAME"),\r
70 rs.getString("LASTNAME"));\r
71 userList.add(user);\r
72 }\r
73 } \r
74 catch (Exception e){\r
75 System.err.println(e);\r
76 } \r
77 finally {\r
78 try{\r
79 rs.close();\r
80 stmt.close();\r
81 conn.close();\r
82 rs=null;\r
83 stmt=null;\r
84 conn=null; \r
85 }\r
86 catch(Exception e){}\r
87 }\r
88 }\r
89\r
90 private void insertNewUser(User user) throws Exception{\r
91 Connection conn=null;\r
92 Statement stmt=null;\r
93 String sql="";\r
94 ResultSet rs=null;\r
95 \r
96 sql = "insert into [User$] values (" + user.getUserName() + "," +\r
97 user.getEncryptedPassword() + "," +\r
98 user.getMail() + "," +\r
99 user.getName() + "," +\r
100 user.getLastname() + ")";\r
101 \r
102 try {\r
103 conn=DriverManager.getConnection("jdbc:odbc:excel","","");\r
104 stmt=conn.createStatement();\r
105 stmt.executeQuery(sql);\r
106 } \r
107 catch (Exception e){\r
108 System.err.println(e);\r
109 } \r
110 finally {\r
111 try{\r
112 rs.close();\r
113 stmt.close();\r
114 conn.close();\r
115 rs=null;\r
116 stmt=null;\r
117 conn=null; \r
118 }\r
119 catch(Exception e){}\r
120 }\r
121 }\r
122}\r