Merge branch 'development' of
[staff/due1/sed-hs15-srs-purple.git] / src / main / java / ch / bfh / ti / srs / core / ExcelController.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
5555b609
M
9public class ExcelController implements DataController{\r
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
23 public ExcelController(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
44 \r
45 private void updateUserList() throws Exception\r
46 {\r
47 Connection conn=null;\r
48 Statement stmt=null;\r
49 String sql="";\r
50 ResultSet rs=null;\r
51 \r
52 userList.clear();\r
53 \r
54 try {\r
55 conn=DriverManager.getConnection("jdbc:odbc:excel","","");\r
56 stmt=conn.createStatement();\r
57 sql="select * from [User$]";\r
58 rs=stmt.executeQuery(sql);\r
59\r
60 while(rs.next()){\r
61 User user = new User(rs.getString("USERNAME"),\r
62 rs.getString("PASSWORD"),\r
63 rs.getString("MAIL"),\r
64 rs.getString("NAME"),\r
65 rs.getString("LASTNAME"));\r
66 userList.add(user);\r
67 }\r
68 } \r
69 catch (Exception e){\r
70 System.err.println(e);\r
71 } \r
72 finally {\r
73 try{\r
74 rs.close();\r
75 stmt.close();\r
76 conn.close();\r
77 rs=null;\r
78 stmt=null;\r
79 conn=null; \r
80 }\r
81 catch(Exception e){}\r
82 }\r
83 }\r
84\r
85 private void insertNewUser(User user) throws Exception{\r
86 Connection conn=null;\r
87 Statement stmt=null;\r
88 String sql="";\r
89 ResultSet rs=null;\r
90 \r
91 sql = "insert into [User$] values (" + user.getUserName() + "," +\r
92 user.getEncryptedPassword() + "," +\r
93 user.getMail() + "," +\r
94 user.getName() + "," +\r
95 user.getLastname() + ")";\r
96 \r
97 try {\r
98 conn=DriverManager.getConnection("jdbc:odbc:excel","","");\r
99 stmt=conn.createStatement();\r
100 stmt.executeQuery(sql);\r
101 } \r
102 catch (Exception e){\r
103 System.err.println(e);\r
104 } \r
105 finally {\r
106 try{\r
107 rs.close();\r
108 stmt.close();\r
109 conn.close();\r
110 rs=null;\r
111 stmt=null;\r
112 conn=null; \r
113 }\r
114 catch(Exception e){}\r
115 }\r
116 }\r
5555b609 117}\r