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 import java.util.ArrayList;
9
10 import sk.stuba.fiit.foo07.genex.beans.Answer;
11 import sk.stuba.fiit.foo07.genex.beans.Category;
12 import sk.stuba.fiit.foo07.genex.beans.Picture;
13 import sk.stuba.fiit.foo07.genex.beans.Question;
14 import sk.stuba.fiit.foo07.genex.exceptions.QuestionInTestException;
15
16
17
18
19
20
21
22
23
24
25 public class QuestionDaoDerby extends BasicDao implements QuestionDao {
26
27 private static final String sqlAddQuestion = "INSERT INTO Question VALUES(?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,DEFAULT, ?, ?, ?)";
28 private static final String sqlAddCategoryQuestion = "INSERT INTO CategoryQuestion VALUES (?, ?)";
29 private static final String sqlDeleteCategoryQuestion = "DELETE FROM CategoryQuestion WHERE questionID = ?";
30 private static final String sqlDeleteQuestionByID = "DELETE FROM Question WHERE questionID = ?";
31 private static final String sqlGetQuestionByID = "SELECT * FROM Question WHERE questionID = ?";
32 private static final String sqlGetQuestionsByCategoryID = "SELECT * FROM Question, CategoryQuestion WHERE Question.questionID = CategoryQuestion.questionID AND CategoryQuestion.categoryID =?";
33 private static final String sqlGetQuestionsInRootCategory = "SELECT * FROM Question, CategoryQuestion WHERE Question.questionID = CategoryQuestion.questionID AND CategoryQuestion.categoryID IS NULL";
34 private static final String sqlGetQuestionByTestID = "SELECT * FROM Question, TestQuestion WHERE TestQuestion.QuestionID = Question.questionID AND TestQuestion.testID = ?";
35 private static final String sqlUpdateQuestion = "UPDATE Question SET Text = ?, Difficulty = ?, LastUpdate=CURRENT_TIMESTAMP, userID = ?, questionTypeID = ?, derivedFromID = ? WHERE questionID = ?";
36 private static final String sqlMoveQuestion = "UPDATE TestQuestion SET testID = ? WHERE testID = ? and questionID = ?";
37 private static final String sqlIsQuestionInTest = "SELECT * FROM TestQuestion WHERE questionID = ?";
38 private static final String sqlGetQuestionCount = "SELECT COUNT(*) FROM CategoryQuestion WHERE CategoryQuestion.categoryID = ?";
39 private static final String sqlDeleteQuestionFromCategory = "DELETE FROM CategoryQuestion WHERE questionID = ? AND categoryID = ?";
40
41 protected ResultSet rs;
42 protected PreparedStatement st;
43 protected Question q;
44 protected ArrayList<Question> q_list;
45 protected ArrayList<Integer> idList;
46
47 public QuestionDaoDerby(Connection con) {
48 super(con);
49 }
50
51 public QuestionDaoDerby() throws Exception {
52 super();
53 }
54
55 @Override
56 public void createQuestion(Question toCreate) throws SQLException {
57 try {
58 st = con.prepareStatement(sqlAddQuestion,
59 Statement.RETURN_GENERATED_KEYS);
60
61 st.setString(1, toCreate.getText());
62 st.setInt(2, toCreate.getDifficulty());
63 st.setInt(3, toCreate.getUserID());
64 st.setInt(4, toCreate.getQuestionTypeID());
65 Integer derivedFrom = toCreate.getDerivedFromID();
66 if (derivedFrom == null) {
67 st.setNull(5, java.sql.Types.INTEGER);
68 } else
69 st.setInt(5, derivedFrom);
70
71 st.execute();
72
73 ResultSet rs = st.getGeneratedKeys();
74 rs.next();
75
76 toCreate.setQuestionID(rs.getInt(1));
77 } catch (SQLException ex) {
78 st = null;
79 throw ex;
80 }
81 }
82
83 @Override
84 public void addQuestion(Integer categoryID, Question toAdd)
85 throws SQLException {
86 try {
87 createQuestion(toAdd);
88
89 st = con.prepareStatement(sqlAddCategoryQuestion);
90 if (categoryID == null) {
91 st.setNull(1, java.sql.Types.INTEGER);
92 } else {
93 st.setInt(1, categoryID);
94 }
95
96 st.setInt(2, toAdd.getQuestionID());
97
98 st.executeUpdate();
99
100 } catch (SQLException e) {
101 st = null;
102 throw e;
103 }
104 }
105
106 @Override
107 public void addQuestions(Integer categoryID, ArrayList<Question> toAdd)
108 throws SQLException {
109
110 for (Question q : toAdd) {
111 addQuestion(categoryID, q);
112 }
113 }
114
115 private boolean isQuestionInTest(int questionID) throws SQLException {
116 st = con.prepareStatement(sqlIsQuestionInTest);
117 st.setInt(1, questionID);
118 rs = st.executeQuery();
119
120 if (rs.next()) {
121 return true;
122 }
123 return false;
124 }
125
126 @Override
127 public void deleteQuestion(Question toDelete)
128 throws QuestionInTestException, SQLException {
129 try {
130
131 if (isQuestionInTest(toDelete.getQuestionID())) {
132 throw new QuestionInTestException(
133 "Unable to delete: question is in test");
134 }
135 con.setAutoCommit(false);
136 con.setSavepoint();
137
138 st = con.prepareStatement(sqlDeleteCategoryQuestion);
139 st.setInt(1, toDelete.getQuestionID());
140
141 st.executeUpdate();
142
143 AnswerDao aDao = new AnswerDaoDerby(con);
144 ArrayList<Answer> ans = aDao.getAnswersByQuestionID(toDelete
145 .getQuestionID());
146 for (Answer a : ans) {
147 aDao.deleteAnswer(a);
148 }
149
150 PictureDao picDao = new PictureDaoDerby(con);
151 for (Picture p : picDao.getPicturesByQuestionID(toDelete
152 .getQuestionID())) {
153 picDao.deletePictureFromQuestion(p.getPictureID(), toDelete
154 .getQuestionID());
155
156 }
157
158 KeywordDao kDao = new KeywordDaoDerby(con);
159 kDao.deleteAllKeywordsFromQuestion(toDelete.getQuestionID());
160
161 st = con.prepareStatement(sqlDeleteQuestionByID);
162 st.setInt(1, toDelete.getQuestionID());
163
164
165 st.executeUpdate();
166 con.setAutoCommit(true);
167 } catch (SQLException e) {
168 con.rollback();
169 con.setAutoCommit(true);
170 st = null;
171 if ("23503".equals(e.getSQLState()))
172 throw new QuestionInTestException(e);
173 else
174 throw e;
175
176 }
177
178 }
179
180 @Override
181 public void deleteQuestions(ArrayList<Question> toDelete)
182 throws QuestionInTestException, SQLException {
183
184 for (Question q : toDelete) {
185 deleteQuestion(q);
186 }
187
188 }
189
190 @Override
191 public Question getQuestionByID(Integer questionID) throws SQLException {
192 try {
193
194 st = con.prepareStatement(sqlGetQuestionByID);
195
196 st.setInt(1, questionID);
197 rs = st.executeQuery();
198
199 rs.next();
200 q = new Question();
201 resultSetToQuestion();
202 rs.close();
203
204 } catch (SQLException sqle) {
205
206 q = null;
207 st = null;
208 rs = null;
209 throw sqle;
210 }
211
212 return q;
213 }
214
215 @Override
216 public ArrayList<Question> getQuestionsByIDs(ArrayList<Integer> questionIDs)
217 throws SQLException {
218
219 try {
220 q_list = new ArrayList<Question>(questionIDs.size());
221
222 for (Integer i : questionIDs) {
223 q_list.add(getQuestionByID(i));
224 }
225 } catch (SQLException sqle) {
226 q = null;
227 st = null;
228 rs = null;
229 q_list = null;
230 throw sqle;
231 }
232
233 return q_list;
234 }
235
236
237
238
239
240
241 private void resultSetToQuestion() throws SQLException {
242 q.setText(rs.getString("Text"));
243 q.setDifficulty(rs.getInt("Difficulty"));
244 q.setCreated(rs.getTimestamp("Created"));
245 q.setLastUpdate(rs.getTimestamp("LastUpdate"));
246 q.setQuestionID(rs.getInt("questionID"));
247 q.setUserID(rs.getInt("userID"));
248 q.setQuestionTypeID(rs.getInt("questionTypeID"));
249 Integer i = rs.getInt("derivedFromID");
250 if (rs.wasNull())
251 q.setDerivedFromID(null);
252 else
253 q.setDerivedFromID(i);
254 }
255
256 @Override
257 public ArrayList<Question> getQuestionsByCategoryID(Integer categoryID)
258 throws SQLException {
259 try {
260 if (categoryID == null) {
261 st = con.prepareStatement(sqlGetQuestionsInRootCategory,
262 ResultSet.TYPE_SCROLL_INSENSITIVE,
263 ResultSet.CONCUR_UPDATABLE);
264 } else {
265 st = con.prepareStatement(sqlGetQuestionsByCategoryID,
266 ResultSet.TYPE_SCROLL_INSENSITIVE,
267 ResultSet.CONCUR_UPDATABLE);
268
269 st.setInt(1, categoryID);
270 }
271
272 rs = st.executeQuery();
273
274 rs.last();
275 q_list = new ArrayList<Question>(rs.getRow());
276 rs.beforeFirst();
277
278 while (rs.next()) {
279 q = new Question();
280 resultSetToQuestion();
281
282 q_list.add(q);
283 }
284 rs.close();
285
286 } catch (SQLException e) {
287 q = null;
288 st = null;
289 rs = null;
290 throw e;
291 }
292 return q_list;
293 }
294
295 @Override
296 public ArrayList<Question> getQuestionsByTestID(Integer testID)
297 throws SQLException {
298 try {
299
300 st = con.prepareStatement(sqlGetQuestionByTestID,
301 ResultSet.TYPE_SCROLL_INSENSITIVE,
302 ResultSet.CONCUR_UPDATABLE);
303 st.setInt(1, testID);
304
305 rs = st.executeQuery();
306
307 rs.last();
308 q_list = new ArrayList<Question>(rs.getRow());
309 rs.beforeFirst();
310
311 while (rs.next()) {
312 q = new Question();
313 resultSetToQuestion();
314
315 q_list.add(q);
316 }
317 rs.close();
318
319 } catch (SQLException e) {
320 q = null;
321 st = null;
322 rs = null;
323 throw e;
324 }
325 return q_list;
326
327 }
328
329 @Override
330 public void updateQuestion(Integer questionID, Question toUpdate)
331 throws SQLException {
332 try {
333 st = con.prepareStatement(sqlUpdateQuestion);
334
335 st.setString(1, toUpdate.getText());
336 st.setInt(2, toUpdate.getDifficulty());
337 st.setInt(3, toUpdate.getUserID());
338 st.setInt(4, toUpdate.getQuestionTypeID());
339 if (toUpdate.getDerivedFromID() == null) {
340 st.setNull(5, java.sql.Types.INTEGER);
341 } else
342 st.setInt(5, toUpdate.getDerivedFromID());
343 st.setInt(6, toUpdate.getQuestionID());
344
345 st.executeUpdate();
346
347 } catch (SQLException e) {
348 st = null;
349 throw e;
350 }
351 }
352
353
354
355
356
357
358
359
360 private String buildQuery(int categoryIdCount, int keywordIdCount,
361 int questionTypeCount, boolean containsPictures, Integer difficulty)
362 throws SQLException {
363
364 int i = 0;
365
366 StringBuilder query = new StringBuilder(
367 "SELECT DISTINCT Question.questionID AS questionID ");
368
369 StringBuilder buildFromClause = new StringBuilder(
370 " FROM Question, CategoryQuestion ");
371 StringBuilder buildWhereClause = new StringBuilder(
372 " WHERE Question.questionID = CategoryQuestion.questionID AND ( ");
373
374 for (i = 1; i < categoryIdCount; i++) {
375 buildWhereClause.append(" CategoryQuestion.CategoryID = ? OR ");
376 }
377 buildWhereClause.append(" CategoryQuestion.CategoryID = ? ) ");
378
379 if (keywordIdCount != 0) {
380 buildFromClause.append(" , KeywordQuestion ");
381 buildWhereClause
382 .append(" AND Question.QuestionID = KeywordQuestion.QuestionID AND ( ");
383 for (i = 1; i < keywordIdCount; i++) {
384 buildWhereClause.append(" KeywordQuestion.KeywordID = ? OR ");
385 }
386 buildWhereClause.append(" KeywordQuestion.KeywordID = ? ) ");
387 }
388
389 if (questionTypeCount != 0) {
390 buildWhereClause.append(" AND ( ");
391 for (i = 1; i < questionTypeCount; i++) {
392 buildWhereClause.append(" Question.QuestionTypeID = ? OR ");
393 }
394 buildWhereClause.append(" Question.QuestionTypeID = ? ) ");
395 }
396
397 if (!containsPictures) {
398 buildFromClause.append(" , PictureQuestion ");
399 buildWhereClause
400 .append(" AND ( Question.QuestionID NOT IN ( SELECT DISTINCT PictureQuestion.QuestionID FROM PictureQuestion ) ) ");
401 }
402
403 if (difficulty != null) {
404 buildWhereClause
405 .append("AND (Question.Difficulty = ?) ORDER BY CATEGORYQUESTION.CATEGORYID");
406 }
407
408 query.append(buildFromClause);
409 query.append(buildWhereClause);
410 System.out.println(query.toString());
411 return query.toString();
412 }
413
414
415
416
417
418
419
420 private PreparedStatement prepareQuery(String query,
421 ArrayList<Integer> questionCategoryIDs,
422 ArrayList<Integer> keywordIDs, ArrayList<Integer> questionTypeIDs,
423 boolean containsPictures, Integer difficulty) throws SQLException {
424 PreparedStatement prep;
425 int parameterCounter = 1;
426
427 prep = con.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE,
428 ResultSet.CONCUR_UPDATABLE);
429
430 for (Integer i : questionCategoryIDs) {
431 prep.setInt(parameterCounter, i);
432 parameterCounter++;
433 }
434
435 for (Integer i : keywordIDs) {
436 prep.setInt(parameterCounter, i);
437 parameterCounter++;
438 }
439
440 for (Integer i : questionTypeIDs) {
441 prep.setInt(parameterCounter, i);
442 parameterCounter++;
443 }
444
445 if (difficulty != null) {
446 prep.setInt(parameterCounter, difficulty);
447 }
448
449 return prep;
450 }
451
452 @Override
453 public ArrayList<Integer> getQuestionsForTestGenerator(
454 ArrayList<Integer> questionCategoryIDs,
455 ArrayList<Integer> keywordIDs, ArrayList<Integer> questionTypeIDs,
456 boolean containsPictures, Integer difficulty) throws SQLException {
457
458 try {
459 String query = buildQuery(questionCategoryIDs.size(), keywordIDs
460 .size(), questionTypeIDs.size(), containsPictures,
461 difficulty);
462 st = prepareQuery(query, questionCategoryIDs, keywordIDs,
463 questionTypeIDs, containsPictures, difficulty);
464
465 rs = st.executeQuery();
466
467 rs.last();
468 idList = new ArrayList<Integer>(rs.getRow());
469 rs.beforeFirst();
470
471 while (rs.next()) {
472 idList.add(rs.getInt("QuestionId"));
473 }
474
475 rs.close();
476
477 return idList;
478
479 } catch (SQLException e) {
480 q = null;
481 st = null;
482 rs = null;
483 q_list = null;
484 throw e;
485 }
486
487 }
488
489 @Override
490 public void moveQuestion(Question q, Integer fromCategoryID,
491 Integer toCategoryID) throws SQLException {
492 try {
493 st = con.prepareStatement(sqlMoveQuestion);
494 st.setInt(1, q.getQuestionID());
495 st.setInt(2, fromCategoryID);
496 st.setInt(3, toCategoryID);
497
498 st.executeUpdate();
499 } catch (SQLException e) {
500 q = null;
501 st = null;
502 rs = null;
503 throw e;
504 }
505
506 }
507
508 @Override
509 public void copyQuestion(int questionID, Integer toCategoryID)
510 throws SQLException {
511 try {
512 st = con.prepareStatement(sqlAddCategoryQuestion);
513
514 if (toCategoryID == null) {
515 st.setNull(1, java.sql.Types.INTEGER);
516 } else {
517 st.setInt(1, toCategoryID);
518 }
519
520 st.setInt(2, questionID);
521
522 st.executeUpdate();
523 } catch (SQLException e) {
524 q = null;
525 st = null;
526 rs = null;
527 throw e;
528 }
529 }
530
531 @Override
532 public int getQuestionCount(Integer categoryID) throws SQLException {
533
534 st = con.prepareStatement(sqlGetQuestionCount,
535 ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
536 if (categoryID == null)
537 st.setNull(1, java.sql.Types.INTEGER);
538 else
539 st.setInt(1, categoryID);
540 rs = st.executeQuery();
541 rs.first();
542 int ret = rs.getInt(1);
543 rs.close();
544
545 return ret;
546 }
547
548 @Override
549 public void deleteQuestionFromCategory(Question toDelete, Category from)
550 throws SQLException {
551 try {
552 st = con.prepareStatement(sqlDeleteQuestionFromCategory);
553 st.setInt(1, toDelete.getQuestionID());
554 st.setInt(2, from.getCategoryID());
555
556 st.execute();
557 } catch (SQLException ex) {
558 st = null;
559 }
560 }
561
562 }