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