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