1
2
3
4 package sk.stuba.fiit.foo07.genex.dao;
5
6 import java.sql.Blob;
7 import java.sql.Connection;
8 import java.sql.PreparedStatement;
9 import java.sql.ResultSet;
10 import java.sql.SQLException;
11 import java.sql.Statement;
12 import java.util.ArrayList;
13
14 import javax.sql.rowset.serial.SerialBlob;
15
16 import sk.stuba.fiit.foo07.genex.beans.Picture;
17 import sk.stuba.fiit.foo07.genex.exceptions.PictureInPictureQuestionException;
18
19
20
21
22
23
24 public class PictureDaoDerby extends BasicDao implements PictureDao {
25
26 protected SerialBlob sblob;
27 protected Picture pic;
28 protected ResultSet rs;
29 protected PreparedStatement pstmt;
30 protected ArrayList<Picture> p_list;
31 private static final String sqlDelPicFromQuestion = "DELETE FROM PICTUREQUESTION WHERE pictureID=? AND questionID=?";
32 private static final String sqlAddPicToQuestion = "INSERT INTO PICTUREQUESTION VALUES (?,?)";
33 private static final String sqlGetByQuestion = "SELECT pictureID FROM PictureQuestion WHERE QuestionId=?";
34 private static final String sqlGetAll = "SELECT pictureId FROM Picture";
35 private static final String sqlGetByTest = "SELECT PictureQuestion.pictureId FROM PictureQuestion, TestQuestion WHERE TestQuestion.questionID=PictureQuestion.questionID AND TestQuestion.testId=?";
36 private static final String sqlGet = "SELECT * FROM Picture WHERE pictureID=?";
37 private static final String sqlDeleteAll = "DELETE FROM Picture WHERE PictureID=?";
38 private static final String sqlDelete = "DELETE FROM Picture WHERE PictureID=?";
39 private static final String sqlAddAll = "INSERT INTO PICTURE VALUES (?,?,?,?,?,DEFAULT )";
40 private static final String sqlAdd = "INSERT INTO PICTURE VALUES (?,?,?,?,?,DEFAULT )";
41 private static final String sqlUpdate = "UPDATE PICTURE SET name=?, description = ?, settings = ?, mimetype = ?, content = ? WHERE pictureID = ?";
42 private static final String sqlExistsName = "SELECT * FROM Picture WHERE name=?";
43
44
45
46
47 public PictureDaoDerby(Connection con) {
48 super(con);
49
50 }
51
52 @Override
53 public void addPicture(Picture toAdd) throws SQLException {
54 try {
55
56 sblob = new SerialBlob(toAdd.getContent());
57 pstmt = con.prepareStatement(sqlAdd,
58 Statement.RETURN_GENERATED_KEYS);
59 pstmt.setString(1, toAdd.getName());
60 pstmt.setString(2, toAdd.getDescription());
61 pstmt.setString(3, toAdd.getSettings());
62 pstmt.setString(4, toAdd.getMimeType());
63 pstmt.setBlob(5, sblob.getBinaryStream());
64 pstmt.execute();
65 rs = pstmt.getGeneratedKeys();
66 rs.next();
67 toAdd.setPictureID(rs.getInt(1));
68 rs.close();
69
70 } catch (SQLException e) {
71 pstmt = null;
72 sblob = null;
73 rs = null;
74 throw e;
75 }
76 }
77
78 @Override
79 public void addPictures(ArrayList<Picture> toAdd) throws SQLException {
80 try {
81
82 pstmt = con.prepareStatement(sqlAddAll,
83 Statement.RETURN_GENERATED_KEYS);
84 int len = toAdd.size();
85 for (int i = 0; i < len; i++) {
86 sblob = new SerialBlob(toAdd.get(i).getContent());
87 pstmt.setString(1, toAdd.get(i).getName());
88 pstmt.setString(2, toAdd.get(i).getDescription());
89 pstmt.setString(3, toAdd.get(i).getSettings());
90 pstmt.setString(4, toAdd.get(i).getMimeType());
91 pstmt.setBlob(5, sblob.getBinaryStream());
92 pstmt.execute();
93 rs = pstmt.getGeneratedKeys();
94 rs.next();
95 toAdd.get(i).setPictureID(rs.getInt(1));
96 rs.close();
97 }
98 } catch (SQLException e) {
99 pstmt = null;
100 sblob = null;
101 rs = null;
102 throw e;
103 }
104 }
105
106 @Override
107 public void deletePicture(Picture toDelete)
108 throws PictureInPictureQuestionException, SQLException {
109 try {
110
111 pstmt = con.prepareStatement(sqlDelete);
112 pstmt.setInt(1, toDelete.getPictureID());
113 pstmt.execute();
114
115 } catch (SQLException e) {
116 pstmt = null;
117 if ("23503".equals(e.getSQLState()))
118 throw new PictureInPictureQuestionException(e);
119 else
120 throw e;
121
122 }
123 }
124
125 @Override
126 public void deletePictures(ArrayList<Picture> toDelete)
127 throws PictureInPictureQuestionException, SQLException {
128 try {
129
130 pstmt = con.prepareStatement(sqlDeleteAll);
131
132 int len = toDelete.size();
133 for (int i = 0; i < len; i++) {
134 pstmt.setInt(1, toDelete.get(i).getPictureID());
135 pstmt.execute();
136 }
137
138 } catch (SQLException e) {
139 pstmt = null;
140
141 if ("23503".equals(e.getSQLState()))
142 throw new PictureInPictureQuestionException(e);
143 else
144 throw e;
145
146 }
147
148 }
149
150 @Override
151 public Picture getPictureByID(Integer pictureID) throws SQLException {
152 try {
153 pic = new Picture();
154
155 pstmt = con.prepareStatement(sqlGet);
156 pstmt.setInt(1, pictureID);
157 rs = pstmt.executeQuery();
158
159 rs.next();
160 pic.setName(rs.getString("Name"));
161 pic.setDescription(rs.getString("Description"));
162 pic.setSettings(rs.getString("Settings"));
163 pic.setMimeType(rs.getString("MimeType"));
164 Blob a = rs.getBlob("Content");
165 pic.setContent(a.getBytes(1, (int) a.length()));
166 pic.setPictureID(rs.getInt("pictureID"));
167 rs.close();
168
169 } catch (SQLException e) {
170 pic = null;
171 pstmt = null;
172 rs = null;
173 throw e;
174 }
175 return pic;
176 }
177
178 @Override
179 public boolean existsPictureByName(String name) throws SQLException {
180 boolean flag = true;
181
182 try {
183 pstmt = con.prepareStatement(sqlExistsName);
184 pstmt.setString(1, name);
185 rs = pstmt.executeQuery();
186 flag = rs.next();
187
188 } catch (SQLException e) {
189 pstmt = null;
190 rs = null;
191 flag = true;
192 throw e;
193 }
194 return flag;
195 }
196
197 @Override
198 public ArrayList<Picture> getPicturesByTestID(Integer testID)
199 throws SQLException {
200 try {
201
202 pstmt = con.prepareStatement(sqlGetByTest,
203 ResultSet.TYPE_SCROLL_INSENSITIVE,
204 ResultSet.CONCUR_UPDATABLE);
205 pstmt.setInt(1, testID);
206 ResultSet rsLocal = pstmt.executeQuery();
207
208 rsLocal.last();
209 p_list = new ArrayList<Picture>(rsLocal.getRow());
210 rsLocal.beforeFirst();
211 while (rsLocal.next()) {
212 pic = getPictureByID(rsLocal.getInt("pictureID"));
213 p_list.add(pic);
214 }
215 rsLocal.close();
216
217 } catch (SQLException e) {
218 pic = null;
219 pstmt = null;
220 rs = null;
221 throw e;
222 }
223 return p_list;
224 }
225
226 public ArrayList<Picture> getAllPictures() throws SQLException {
227 try {
228 ;
229 pstmt = con.prepareStatement(sqlGetAll,
230 ResultSet.TYPE_SCROLL_INSENSITIVE,
231 ResultSet.CONCUR_UPDATABLE);
232 ResultSet rsLocal = pstmt.executeQuery();
233
234 rsLocal.last();
235 p_list = new ArrayList<Picture>(rsLocal.getRow());
236 rsLocal.beforeFirst();
237 while (rsLocal.next()) {
238 pic = getPictureByID(rsLocal.getInt("pictureID"));
239 p_list.add(pic);
240 }
241 rsLocal.close();
242
243 } catch (SQLException e) {
244
245 pic = null;
246 pstmt = null;
247 rs = null;
248 throw e;
249 }
250 return p_list;
251 }
252
253 @Override
254 public ArrayList<Picture> getPicturesByQuestionID(Integer QuestionID)
255 throws SQLException {
256 try {
257
258 pstmt = con.prepareStatement(sqlGetByQuestion,
259 ResultSet.TYPE_SCROLL_INSENSITIVE,
260 ResultSet.CONCUR_UPDATABLE);
261 pstmt.setInt(1, QuestionID);
262 ResultSet rsLocal = pstmt.executeQuery();
263 rsLocal.last();
264 p_list = new ArrayList<Picture>(rsLocal.getRow());
265 rsLocal.beforeFirst();
266 while (rsLocal.next()) {
267 pic = getPictureByID(rsLocal.getInt("pictureID"));
268 p_list.add(pic);
269 }
270 rsLocal.close();
271
272 } catch (SQLException e) {
273 pic = null;
274 pstmt = null;
275 rs = null;
276 throw e;
277 }
278 return p_list;
279 }
280
281 public void addPictureToQuestion(Integer pictureId, Integer questionId)
282 throws SQLException {
283 try {
284
285 pstmt = con.prepareStatement(sqlAddPicToQuestion);
286
287 pstmt.setInt(1, pictureId);
288 pstmt.setInt(2, questionId);
289 pstmt.execute();
290
291 } catch (SQLException e) {
292 pstmt = null;
293
294 throw e;
295 }
296
297 }
298
299 public void deletePictureFromQuestion(Integer pictureId, Integer questionId)
300 throws SQLException {
301 try {
302 pstmt = con.prepareStatement(sqlDelPicFromQuestion);
303
304 pstmt.setInt(1, pictureId);
305 pstmt.setInt(2, questionId);
306 pstmt.execute();
307
308 } catch (SQLException e) {
309 pstmt = null;
310
311 throw e;
312 }
313 }
314
315 @Override
316 public void updatePicture(Picture toUpdate) throws SQLException {
317 try {
318
319 sblob = new SerialBlob(toUpdate.getContent());
320 pstmt = con.prepareStatement(sqlUpdate);
321 pstmt.setString(1, toUpdate.getName());
322 pstmt.setString(2, toUpdate.getDescription());
323 pstmt.setString(3, toUpdate.getSettings());
324 pstmt.setString(4, toUpdate.getMimeType());
325 pstmt.setBlob(5, sblob.getBinaryStream());
326 pstmt.setInt(6, toUpdate.getPictureID());
327 pstmt.execute();
328
329 } catch (SQLException e) {
330 pstmt = null;
331 sblob = null;
332 rs = null;
333 throw e;
334 }
335 }
336
337 }