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