1 package sk.stuba.fiit.foo07.genex.dao;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.sql.Statement;
8
9 import sk.stuba.fiit.foo07.genex.beans.TUser;
10
11
12
13
14
15 public class UserDaoDerby extends BasicDao implements UserDao {
16
17 private static final String sqlAddTUser = "INSERT INTO TUser VALUES(?, ?, ?, ?, ?, ?, DEFAULT)";
18 private static final String sqldeleteTUserByID = "DELETE FROM TUser WHERE userID = ?";
19 private static final String sqlGetTUserByID = "SELECT * FROM TUser WHERE userID = ?";
20 private static final String sqlUpdateTUser = "UPDATE Question SET Name = ?, Surname = ?,Login = ?, Password= ?, Email= ?, Comment= ? WHERE userID = ?";
21 private static final String sqlGetDefaultTUser = "SELECT * FROM TUser WHERE userID = 1";
22
23 protected ResultSet rs;
24 protected PreparedStatement st;
25 protected TUser tu;
26
27 public UserDaoDerby(Connection con) {
28 super(con);
29 }
30
31 private void resultSetToTUser() throws SQLException {
32 tu.setName(rs.getString("Name"));
33 tu.setSurname(rs.getString("Surname"));
34 tu.setLogin(rs.getString("Login"));
35 tu.setPassword(rs.getString("Password"));
36 tu.setEmail(rs.getString("Email"));
37 tu.setComment(rs.getString("Comment"));
38 }
39
40 @Override
41 public void addTUser(TUser toAdd) throws SQLException {
42 try {
43 st = con.prepareStatement(sqlAddTUser,
44 Statement.RETURN_GENERATED_KEYS);
45
46 st.setString(1, toAdd.getName());
47 st.setString(2, toAdd.getSurname());
48 st.setString(3, toAdd.getLogin());
49 st.setString(4, toAdd.getPassword());
50 st.setString(5, toAdd.getEmail());
51 st.setString(6, toAdd.getComment());
52
53 st.execute();
54
55 ResultSet rs = st.getGeneratedKeys();
56 rs.next();
57
58 toAdd.setUserID(rs.getInt(1));
59
60 } catch (SQLException e) {
61 st = null;
62 throw e;
63 }
64
65 }
66
67 @Override
68 public void deleteTUser(TUser toDelete) throws SQLException {
69 try {
70
71 st = con.prepareStatement(sqldeleteTUserByID);
72 st.setInt(1, toDelete.getUserID());
73
74 st.executeUpdate();
75
76 } catch (SQLException e) {
77 st = null;
78 throw e;
79 }
80
81 }
82
83 @Override
84 public TUser getDefaultTUser() throws SQLException {
85 try {
86
87 st = con.prepareStatement(sqlGetDefaultTUser);
88 rs = st.executeQuery();
89
90 rs.next();
91 tu = new TUser();
92 tu.setUserID(1);
93 resultSetToTUser();
94 rs.close();
95
96 } catch (SQLException e) {
97 tu = null;
98 st = null;
99 rs = null;
100 throw e;
101 }
102
103 return tu;
104 }
105
106 @Override
107 public TUser getTUserByID(Integer userID) throws SQLException {
108 try {
109
110 st = con.prepareStatement(sqlGetTUserByID);
111
112 st.setInt(1, userID);
113 rs = st.executeQuery();
114
115 rs.next();
116 tu = new TUser();
117 tu.setUserID(userID);
118 resultSetToTUser();
119 rs.close();
120
121 } catch (SQLException e) {
122 tu = null;
123 st = null;
124 rs = null;
125 throw e;
126 }
127
128 return tu;
129 }
130
131 @Override
132 public void updateTUser(TUser toUpdate) throws SQLException {
133 try {
134 st = con.prepareStatement(sqlUpdateTUser);
135
136 st.setString(1, toUpdate.getName());
137 st.setString(2, toUpdate.getSurname());
138 st.setString(3, toUpdate.getLogin());
139 st.setString(4, toUpdate.getPassword());
140 st.setString(5, toUpdate.getEmail());
141 st.setString(6, toUpdate.getComment());
142 st.setInt(7, toUpdate.getUserID());
143
144 st.executeUpdate();
145
146 } catch (SQLException e) {
147 st = null;
148 throw e;
149 }
150 }
151
152 }