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