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