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.Keyword;
11
12 public class KeywordDaoDerby extends BasicDao implements KeywordDao {
13
14 private ResultSet rs;
15 private Keyword keyword;
16 private ArrayList<Keyword> keywordList;
17 private PreparedStatement pst;
18
19 private static final String sqlAdd = "INSERT INTO KEYWORD VALUES( ? , DEFAULT )";
20 private static final String sqlAddKeywordQuestion = "INSERT INTO KEYWORDQUESTION VALUES ( ? , ? )";
21 private static final String sqlDelete = "DELETE FROM KEYWORD WHERE KEYWORDID = ?";
22 private static final String sqlDeleteKeywordQuestion = "DELETE FROM KEYWORDQUESTION WHERE KEYWORDID = ?";
23 private static final String sqlGetById = "SELECT * FROM KEYWORD WHERE KEYWORDID = ?";
24 private static final String sqlGetIdsByQuestionId = "SELECT KEYWORDID FROM KEYWORDQUESTION WHERE QUESTIONID = ?";
25 private static final String sqlUpdate = "UPDATE KEYWORD SET TEXT = ? WHERE KEYWORDID = ?";
26 private static final String sqlGetAllKeywords = "SELECT * FROM KEYWORD";
27 private static final String sqlDeleteKeywordsFromQuestion = "DELETE FROM KEYWORDQUESTION WHERE QUESTIONID = ?";
28
29 public KeywordDaoDerby() throws Exception {
30 super();
31 }
32
33 public KeywordDaoDerby(Connection con) throws SQLException {
34 super(con);
35 }
36
37 @Override
38 public void addKeyword(Integer questionID, Keyword toAdd)
39 throws SQLException {
40 try {
41
42
43
44 if (toAdd.getKeywordID() == null) {
45 pst = con.prepareStatement(sqlAdd,
46 Statement.RETURN_GENERATED_KEYS);
47 pst.setString(1, toAdd.getText());
48 pst.execute();
49
50 rs = pst.getGeneratedKeys();
51 rs.next();
52
53 int keywordId = rs.getInt(1);
54 toAdd.setKeywordID(keywordId);
55 rs.close();
56 }
57 pst = con.prepareStatement(sqlAddKeywordQuestion,
58 Statement.RETURN_GENERATED_KEYS);
59 pst.setInt(1, toAdd.getKeywordID());
60 pst.setInt(2, questionID);
61 pst.execute();
62
63 } catch (SQLException sqle) {
64 pst = null;
65 rs = null;
66 throw sqle;
67 }
68 }
69
70 @Override
71 public void addKeywords(Integer questionID, ArrayList<Keyword> toAdd)
72 throws SQLException {
73
74 for (Keyword k : toAdd) {
75 this.addKeyword(questionID, k);
76 }
77
78 }
79
80 @Override
81 public void deleteKeyword(Keyword toDelete) throws SQLException {
82 try {
83 pst = con.prepareStatement(sqlDeleteKeywordQuestion);
84 pst.setInt(1, toDelete.getKeywordID());
85 pst.execute();
86
87 pst = con.prepareStatement(sqlDelete);
88 pst.setInt(1, toDelete.getKeywordID());
89 pst.execute();
90
91 } catch (SQLException sqle) {
92 pst = null;
93 throw sqle;
94 }
95 }
96
97 @Override
98 public void deleteAllKeywordsFromQuestion(Integer questionID)
99 throws SQLException {
100 try {
101 pst = con.prepareStatement(sqlDeleteKeywordsFromQuestion);
102 pst.setInt(1, questionID);
103
104 pst.execute();
105
106 } catch (SQLException sqle) {
107 pst = null;
108 throw sqle;
109 }
110 }
111
112 @Override
113 public void deleteKeywords(ArrayList<Keyword> toDelete) throws SQLException {
114 for (Keyword k : toDelete) {
115 this.deleteKeyword(k);
116 }
117 }
118
119 @Override
120 public Keyword getKeywordByID(Integer keywordID) throws SQLException {
121 try {
122 pst = con.prepareStatement(sqlGetById);
123 pst.setInt(1, keywordID);
124 rs = pst.executeQuery();
125 rs.next();
126
127 keyword = new Keyword();
128 keyword.setText(rs.getString("TEXT"));
129 keyword.setKeywordID(rs.getInt("KEYWORDID"));
130
131 rs.close();
132
133 return keyword;
134
135 } catch (SQLException sqle) {
136 rs = null;
137 pst = null;
138 keyword = null;
139 throw sqle;
140 }
141 }
142
143 @Override
144 public ArrayList<Keyword> getAllKeywords() throws SQLException {
145
146 try {
147 pst = con.prepareStatement(sqlGetAllKeywords,
148 ResultSet.TYPE_SCROLL_INSENSITIVE,
149 ResultSet.CONCUR_UPDATABLE);
150
151 rs = pst.executeQuery();
152 rs.last();
153 keywordList = new ArrayList<Keyword>(rs.getRow());
154 rs.beforeFirst();
155 while (rs.next()) {
156 keyword = new Keyword();
157 keyword.setText(rs.getString("TEXT"));
158 keyword.setKeywordID(rs.getInt("KEYWORDID"));
159 keywordList.add(keyword);
160 }
161 rs.close();
162
163 return keywordList;
164
165 } catch (SQLException sqle) {
166 rs = null;
167 pst = null;
168 keyword = null;
169 throw sqle;
170 }
171 }
172
173 @Override
174 public ArrayList<Keyword> getKeywordsByQuestionID(Integer questionID)
175 throws SQLException {
176 try {
177 pst = con.prepareStatement(sqlGetIdsByQuestionId,
178 ResultSet.TYPE_SCROLL_INSENSITIVE,
179 ResultSet.CONCUR_UPDATABLE);
180 pst.setInt(1, questionID);
181 rs = pst.executeQuery();
182 rs.last();
183 ArrayList<Integer> keywordIds = new ArrayList<Integer>(rs.getRow());
184 rs.beforeFirst();
185
186 while (rs.next()) {
187 keywordIds.add(rs.getInt("KEYWORDID"));
188 }
189 rs.close();
190
191 keywordList = new ArrayList<Keyword>();
192 for (Integer id : keywordIds) {
193 keywordList.add(this.getKeywordByID(id));
194 }
195
196 return keywordList;
197
198 } catch (SQLException sqle) {
199 pst = null;
200 rs = null;
201 keyword = null;
202 keywordList = null;
203 throw sqle;
204 }
205 }
206
207 @Override
208 public void updateKeyword(Keyword toUpdate) throws SQLException {
209 try {
210 pst = con.prepareStatement(sqlUpdate);
211 pst.setString(1, toUpdate.getText());
212 pst.setInt(2, toUpdate.getKeywordID());
213 pst.execute();
214
215 } catch (SQLException sqle) {
216 pst = null;
217 throw sqle;
218 }
219 }
220
221 }