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.util.ArrayList;
8
9 import sk.stuba.fiit.foo07.genex.beans.QuestionPoints;
10 import sk.stuba.fiit.foo07.genex.exceptions.QuestionPointsExistsException;
11
12 public class QuestionPointsDaoDerby extends BasicDao implements
13 QuestionPointsDao {
14
15 private static final String sqlAddQuestionPoints = "INSERT INTO TESTQUESTION(Points, \"Order\", testID, questionID) VALUES(?, ?, ?, ?)";
16 private static final String sqlGetQuestionsPointsByTestID = "SELECT questionID, Points FROM TestQuestion WHERE testID = ? ORDER BY \"Order\" ASC";
17 private static final String sqlUpdateQuestionPoints = "UPDATE TESTQUESTION SET Points = ?, \"Order\" =? WHERE testID = ? AND questionID = ?";
18
19 PreparedStatement st = null;
20 ResultSet rs = null;
21
22 public QuestionPointsDaoDerby(Connection con) {
23 super(con);
24 }
25
26 @Override
27 public void addQuestionsPoints(Integer testID, ArrayList<QuestionPoints> qps)
28 throws QuestionPointsExistsException, SQLException {
29 try {
30 int order = 0;
31 st = con.prepareStatement(sqlAddQuestionPoints);
32 for (QuestionPoints qp : qps) {
33 st.setFloat(1, qp.getPoints());
34 st.setInt(2, order);
35 st.setInt(3, testID);
36 st.setInt(4, qp.getQuestionId());
37 st.executeUpdate();
38 order++;
39 }
40 } catch (SQLException e) {
41 if (e.getSQLState().equals("23505")) {
42 throw new QuestionPointsExistsException();
43 } else
44 throw e;
45 }
46
47 }
48
49 @Override
50 public ArrayList<QuestionPoints> getQuestionPointsByTestID(Integer testID)
51 throws SQLException {
52 ArrayList<QuestionPoints> arr = null;
53 try {
54 st = con.prepareStatement(sqlGetQuestionsPointsByTestID,
55 ResultSet.TYPE_SCROLL_INSENSITIVE,
56 ResultSet.CONCUR_UPDATABLE);
57 st.setInt(1, testID);
58
59 rs = st.executeQuery();
60 rs.last();
61 arr = new ArrayList<QuestionPoints>(rs.getRow());
62 rs.beforeFirst();
63
64 while (rs.next()) {
65 arr.add(new QuestionPoints(rs.getInt("questionID"), rs
66 .getFloat("Points")));
67 }
68 rs.close();
69 return arr;
70 } catch (SQLException e) {
71 st = null;
72 throw e;
73 }
74 }
75
76 @Override
77 public void updateQuestionPoints(Integer testID,
78 ArrayList<QuestionPoints> qps) throws SQLException {
79 int order = 0;
80 st = con.prepareStatement(sqlUpdateQuestionPoints);
81 st.setInt(3, testID);
82 for (QuestionPoints qp : qps) {
83 st.setFloat(1, qp.getPoints());
84 st.setInt(2, order++);
85 st.setInt(4, qp.getQuestionId());
86 st.executeUpdate();
87 }
88 st = null;
89 }
90
91 }