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