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
12
13
14
15
16
17 public class AnswerDaoDerby extends BasicDao implements AnswerDao {
18
19 private static final String addAnswerSQL = "INSERT INTO Answer VALUES(?, ?,DEFAULT, ?)";
20 private static final String printAnswers = "SELECT * FROM Answer";
21 private static final String delete = "DELETE FROM Answer WHERE answerID=?";
22 private static final String getAnswer = "SELECT * FROM Answer WHERE answerID=?";
23 private static final String getAnswersByQuestion = "SELECT * FROM Answer WHERE questionID=?";
24 private static final String update = "UPDATE Answer SET Text=?,IsCorrect=?,questionID=? WHERE AnswerID=?";
25 protected ResultSet rs;
26 protected PreparedStatement pst;
27 protected Answer a;
28 protected ArrayList<Answer> a_list;
29
30 public AnswerDaoDerby(Connection con) {
31 super(con);
32 }
33
34 public AnswerDaoDerby() throws Exception {
35 super();
36 }
37
38 public void printAnswers() {
39 try {
40 a_list = new ArrayList<Answer>();
41 pst = con.prepareStatement(printAnswers);
42 rs = pst.executeQuery();
43
44 while (rs.next()) {
45 a = new Answer();
46 a.setAnswerID(rs.getInt("answerID"));
47 a.setIsCorrect(rs.getBoolean(("IsCorrect")));
48 a.setQuestionID(rs.getInt("QuestionID"));
49 a.setText(rs.getString("Text"));
50 System.out.println("odpoved: " + a.getText() + " "
51 + a.getIsCorrect() + " " + a.getQuestionID() + " "
52 + a.getAnswerID());
53 }
54 rs.close();
55
56 } catch (SQLException e) {
57 a_list = null;
58 a = null;
59 pst = null;
60 rs = null;
61 return;
62 }
63
64 }
65
66 @Override
67 public void addAnswerToQuestion(Integer questionID, Answer toAdd)
68 throws SQLException {
69 try {
70 PreparedStatement st = con.prepareStatement(addAnswerSQL,
71 Statement.RETURN_GENERATED_KEYS);
72
73 st.setString(1, toAdd.getText());
74 st.setBoolean(2, toAdd.getIsCorrect());
75 st.setInt(3, questionID);
76
77 st.execute();
78 ResultSet rs = st.getGeneratedKeys();
79 rs.next();
80
81 toAdd.setAnswerID(rs.getInt(1));
82 rs.close();
83
84 } catch (SQLException e) {
85 pst = null;
86 rs = null;
87 throw e;
88 }
89 }
90
91 @Override
92 public void addAnswersToQuestion(Integer questionID,
93 ArrayList<Answer> answers) throws SQLException {
94
95 for (Answer a : answers) {
96 addAnswerToQuestion(questionID, a);
97 }
98 }
99
100 @Override
101 public void deleteAnswer(Answer toDelete) {
102 try {
103 pst = con.prepareStatement(delete);
104 pst.setInt(1, toDelete.getAnswerID());
105 pst.execute();
106
107 } catch (SQLException e) {
108 pst = null;
109 return;
110 }
111 }
112
113 @Override
114 public Answer getAnswerByID(Integer answerID) throws SQLException {
115
116 try {
117 a = new Answer();
118 pst = con.prepareStatement(getAnswer);
119 pst.setInt(1, answerID);
120 rs = pst.executeQuery();
121
122 rs.next();
123 a.setAnswerID(answerID);
124 a.setIsCorrect(rs.getBoolean(("IsCorrect")));
125 a.setQuestionID(rs.getInt("QuestionID"));
126 a.setText(rs.getString("Text"));
127 rs.close();
128
129 } catch (SQLException e) {
130 a = null;
131 pst = null;
132 rs = null;
133 throw e;
134 }
135 return a;
136 }
137
138 @Override
139 public ArrayList<Answer> getAnswersByQuestionID(Integer questionID)
140 throws SQLException {
141 try {
142
143 pst = con.prepareStatement(getAnswersByQuestion,
144 ResultSet.TYPE_SCROLL_INSENSITIVE,
145 ResultSet.CONCUR_UPDATABLE);
146 pst.setInt(1, questionID);
147 rs = pst.executeQuery();
148
149 rs.last();
150 a_list = new ArrayList<Answer>(rs.getRow());
151 rs.beforeFirst();
152
153 while (rs.next()) {
154 a = new Answer();
155 a.setAnswerID(rs.getInt("answerID"));
156 a.setIsCorrect(rs.getBoolean(("IsCorrect")));
157 a.setQuestionID(rs.getInt("QuestionID"));
158 a.setText(rs.getString("Text"));
159 a_list.add(a);
160 }
161 rs.close();
162
163 } catch (SQLException e) {
164 a = null;
165 pst = null;
166 rs = null;
167 throw e;
168 }
169 return a_list;
170 }
171
172 @Override
173 public void updateAnswer(Answer toUpdate) throws SQLException {
174 try {
175 pst = con.prepareStatement(update);
176
177 Short a;
178 if (toUpdate.getIsCorrect())
179 a = 1;
180 else
181 a = 0;
182 pst.setString(1, toUpdate.getText());
183 pst.setShort(2, a);
184 pst.setInt(3, toUpdate.getQuestionID());
185 pst.setInt(4, toUpdate.getAnswerID());
186 pst.executeUpdate();
187
188 } catch (SQLException e) {
189 pst = null;
190 throw e;
191 }
192 }
193 }