1
2
3
4 package sk.stuba.fiit.foo07.genex.dao;
5
6 import java.sql.Connection;
7 import java.sql.PreparedStatement;
8 import java.sql.ResultSet;
9 import java.sql.SQLException;
10 import java.sql.Statement;
11 import java.util.ArrayList;
12
13 import sk.stuba.fiit.foo07.genex.beans.Test;
14 import sk.stuba.fiit.foo07.genex.exceptions.QuestionPointsExistsException;
15
16
17
18
19
20 public class TestDaoDerby extends BasicDao implements TestDao {
21
22 private ResultSet rs;
23 private PreparedStatement pst;
24 private Test test;
25 private ArrayList<Test> testList;
26 private QuestionPointsDao qpDao;
27
28 private static final String sqlAdd = "INSERT INTO TEST VALUES (? , ? , CURRENT_TIMESTAMP , DEFAULT, ? , ? )";
29 private static final String sqlAddRoot = "INSERT INTO TEST VALUES (? , ? , CURRENT_TIMESTAMP , DEFAULT, ? , NULL )";
30 private static final String sqlDeleteQuestions = "DELETE FROM TESTQUESTION WHERE TESTID = ?";
31 private static final String sqlDelete = "DELETE FROM TEST WHERE TESTID = ?";
32 private static final String sqlGetById = "SELECT * FROM TEST WHERE TESTID = ?";
33 private static final String sqlGetByCategoryId = "SELECT * FROM TEST WHERE CATEGORYID = ?";
34 private static final String sqlGetByCategoryIdRoot = "SELECT * FROM TEST WHERE CATEGORYID IS NULL";
35 private static final String sqlUpdate = "UPDATE TEST SET NAME = ? , SUBJECT = ? , GENERATED = ? , USERID = ?, CATEGORYID = ? WHERE TESTID = ?";
36 private static final String sqlUpdateRoot = "UPDATE TEST SET NAME = ? , SUBJECT = ? , GENERATED = ? , USERID = ?, CATEGORYID = NULL WHERE TESTID = ?";
37
38 public TestDaoDerby() throws Exception {
39 super();
40 }
41
42 public TestDaoDerby(Connection con) {
43 super(con);
44 qpDao = new QuestionPointsDaoDerby(con);
45 }
46
47 @Override
48 public void addTest(Integer testCategoryID, Test toAdd) throws SQLException {
49 try {
50 if (testCategoryID == null)
51 pst = con.prepareStatement(sqlAddRoot,
52 Statement.RETURN_GENERATED_KEYS);
53 else
54 pst = con.prepareStatement(sqlAdd,
55 Statement.RETURN_GENERATED_KEYS);
56
57 pst.setString(1, toAdd.getName());
58 pst.setString(2, toAdd.getSubject());
59 pst.setInt(3, toAdd.getUserID());
60 if (testCategoryID != null)
61 pst.setInt(4, testCategoryID);
62 pst.execute();
63
64 rs = pst.getGeneratedKeys();
65 rs.next();
66
67 toAdd.setTestID(rs.getInt(1));
68
69 qpDao.addQuestionsPoints(toAdd.getTestID(), toAdd
70 .getQuestionPoints());
71
72 } catch (QuestionPointsExistsException e) {
73 qpDao.updateQuestionPoints(toAdd.getTestID(), toAdd
74 .getQuestionPoints());
75 } catch (SQLException sqle) {
76 pst = null;
77 throw sqle;
78 }
79 }
80
81 @Override
82 public void addTests(Integer testCategoryID, ArrayList<Test> toAdd)
83 throws SQLException {
84
85 for (Test t : toAdd) {
86 this.addTest(testCategoryID, t);
87 }
88
89 }
90
91 @Override
92 public void deleteTest(Test toDelete) throws SQLException {
93 try {
94 pst = con.prepareStatement(sqlDeleteQuestions);
95 pst.setInt(1, toDelete.getTestID());
96 pst.execute();
97
98 pst = con.prepareStatement(sqlDelete);
99 pst.setInt(1, toDelete.getTestID());
100 pst.execute();
101
102 } catch (SQLException sqle) {
103 pst = null;
104 throw sqle;
105 }
106 }
107
108 @Override
109 public void deleteTests(ArrayList<Test> toDelete) throws SQLException {
110
111 for (Test t : toDelete) {
112 this.deleteTest(t);
113 }
114
115 }
116
117 @Override
118 public Test getTestByID(Integer testID) throws SQLException {
119 try {
120 pst = con.prepareStatement(sqlGetById);
121 pst.setInt(1, testID);
122 rs = pst.executeQuery();
123 rs.next();
124
125 test = new Test();
126 test.setName(rs.getString("NAME"));
127 test.setSubject(rs.getString("SUBJECT"));
128 test.setGenerated(rs.getTimestamp("GENERATED"));
129 test.setTestID(testID);
130 test.setUserID(rs.getInt("USERID"));
131 test.setCategoryID(rs.getInt("CATEGORYID"));
132
133 test.setQuestionPoints(qpDao.getQuestionPointsByTestID(test
134 .getTestID()));
135
136 rs.close();
137
138 return test;
139
140 } catch (SQLException sqle) {
141 rs = null;
142 pst = null;
143 test = null;
144 throw sqle;
145 }
146 }
147
148 @Override
149 public ArrayList<Test> getTestsByCategoryID(Integer categoryID)
150 throws SQLException {
151 try {
152 if (categoryID == null)
153 pst = con.prepareStatement(sqlGetByCategoryIdRoot,
154 ResultSet.TYPE_SCROLL_INSENSITIVE,
155 ResultSet.CONCUR_UPDATABLE);
156 else {
157 pst = con.prepareStatement(sqlGetByCategoryId,
158 ResultSet.TYPE_SCROLL_INSENSITIVE,
159 ResultSet.CONCUR_UPDATABLE);
160 pst.setInt(1, categoryID);
161 }
162
163 rs = pst.executeQuery();
164 rs.last();
165 testList = new ArrayList<Test>(rs.getRow());
166 rs.beforeFirst();
167
168 while (rs.next()) {
169 test = new Test();
170 test.setName(rs.getString("NAME"));
171 test.setSubject(rs.getString("SUBJECT"));
172 test.setGenerated(rs.getTimestamp("GENERATED"));
173 test.setTestID(rs.getInt("TESTID"));
174 test.setUserID(rs.getInt("USERID"));
175 test.setCategoryID(rs.getInt("CATEGORYID"));
176
177 test.setQuestionPoints(qpDao.getQuestionPointsByTestID(test
178 .getTestID()));
179
180 testList.add(test);
181 }
182
183 rs.close();
184
185 return testList;
186
187 } catch (SQLException sqle) {
188 rs = null;
189 pst = null;
190 test = null;
191 testList = null;
192 throw sqle;
193 }
194 }
195
196 @Override
197 public void updateTest(Test toUpdate) throws SQLException {
198 try {
199
200 if (toUpdate.getCategoryID() == null)
201 pst = con.prepareStatement(sqlUpdateRoot);
202 else
203 pst = con.prepareStatement(sqlUpdate);
204
205 pst.setString(1, toUpdate.getName());
206 pst.setString(2, toUpdate.getSubject());
207 pst.setTimestamp(3, toUpdate.getGenerated());
208 pst.setInt(4, toUpdate.getUserID());
209 if (toUpdate.getCategoryID() == null)
210 pst.setInt(5, toUpdate.getTestID());
211 else {
212 pst.setInt(5, toUpdate.getCategoryID());
213 pst.setInt(6, toUpdate.getTestID());
214 }
215 pst.execute();
216
217 qpDao.updateQuestionPoints(toUpdate.getTestID(), toUpdate
218 .getQuestionPoints());
219
220 } catch (SQLException sqle) {
221 pst = null;
222 throw sqle;
223 }
224 }
225 }