|
1
41 package com.mvnforum.db.jdbc;
42
43 import java.sql.*;
44 import java.util.ArrayList  ;
45 import java.util.Collection ;
46
47 import com.mvnforum.db.RankBean;
48 import com.mvnforum.db.RankDAO;
49 import net.myvietnam.mvncore.db.DBUtils;
50 import net.myvietnam.mvncore.exception.*;
51 import org.apache.commons.logging.Log;
52 import org.apache.commons.logging.LogFactory;
53
54 public class RankDAOImplJDBC implements RankDAO {
55
56 private static Log log = LogFactory.getLog(RankDAOImplJDBC.class);
57
58 private static boolean m_dirty = true;
60
61 public RankDAOImplJDBC() {
62 }
63
64 public static boolean isDirty() {
65 return m_dirty;
66 }
67
68 public static void setDirty(boolean dirty) {
69 m_dirty = dirty;
70 }
71
72 public void findByAlternateKey_RankTitle(String rankTitle)
73 throws ObjectNotFoundException, DatabaseException {
74
75 Connection connection = null;
76 PreparedStatement statement = null;
77 ResultSet resultSet = null;
78 StringBuffer sql = new StringBuffer (512);
79 sql.append("SELECT RankTitle");
80 sql.append(" FROM " + TABLE_NAME);
81 sql.append(" WHERE RankTitle = ?");
82 try {
83 connection = DBUtils.getConnection();
84 statement = connection.prepareStatement(sql.toString());
85 statement.setString(1, rankTitle);
86 resultSet = statement.executeQuery();
87 if (!resultSet.next()) {
88 throw new ObjectNotFoundException("Cannot find the alternate key [RankTitle] (" + rankTitle + ") in table 'Rank'.");
89 }
90 } catch(SQLException sqle) {
91 log.error("Sql Execution Error!", sqle);
92 throw new DatabaseException("Error executing SQL in RankDAOImplJDBC.findByAlternateKey_RankTitle.");
93 } finally {
94 DBUtils.closeResultSet(resultSet);
95 DBUtils.closeStatement(statement);
96 DBUtils.closeConnection(connection);
97 }
98 }
99
100 public void findByAlternateKey_RankMinPosts(int rankMinPosts)
101 throws ObjectNotFoundException, DatabaseException {
102
103 Connection connection = null;
104 PreparedStatement statement = null;
105 ResultSet resultSet = null;
106 StringBuffer sql = new StringBuffer (512);
107 sql.append("SELECT RankMinPosts");
108 sql.append(" FROM " + TABLE_NAME);
109 sql.append(" WHERE RankMinPosts = ?");
110 try {
111 connection = DBUtils.getConnection();
112 statement = connection.prepareStatement(sql.toString());
113 statement.setInt(1, rankMinPosts);
114 resultSet = statement.executeQuery();
115 if (!resultSet.next()) {
116 throw new ObjectNotFoundException("Cannot find the alternate key [RankMinPosts] (" + rankMinPosts + ") in table 'Rank'.");
117 }
118 } catch(SQLException sqle) {
119 log.error("Sql Execution Error!", sqle);
120 throw new DatabaseException("Error executing SQL in RankDAOImplJDBC.findByAlternateKey_RankMinPosts.");
121 } finally {
122 DBUtils.closeResultSet(resultSet);
123 DBUtils.closeStatement(statement);
124 DBUtils.closeConnection(connection);
125 }
126 }
127
128
133 public void create(int rankMinPosts, int rankLevel, String rankTitle,
134 String rankImage, int rankType, int rankOption)
135 throws CreateException, DatabaseException, DuplicateKeyException {
136
137 try {
140 findByAlternateKey_RankTitle(rankTitle);
142 throw new DuplicateKeyException("Alternate key already exists. Cannot create new Rank with the same [RankTitle] (" + rankTitle + ").");
144 } catch(ObjectNotFoundException e) {
145 }
147
148 try {
151 findByAlternateKey_RankMinPosts(rankMinPosts);
153 throw new DuplicateKeyException("Alternate key already exists. Cannot create new Rank with the same [RankMinPosts] (" + rankMinPosts + ").");
155 } catch(ObjectNotFoundException e) {
156 }
158
159 Connection connection = null;
160 PreparedStatement statement = null;
161 StringBuffer sql = new StringBuffer (512);
162 sql.append("INSERT INTO " + TABLE_NAME + " (RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption)");
163 sql.append(" VALUES (?, ?, ?, ?, ?, ?)");
164 try {
165 connection = DBUtils.getConnection();
166 statement = connection.prepareStatement(sql.toString());
167
168 statement.setInt(1, rankMinPosts);
169 statement.setInt(2, rankLevel);
170 statement.setString(3, rankTitle);
171 statement.setString(4, rankImage);
172 statement.setInt(5, rankType);
173 statement.setInt(6, rankOption);
174
175 if (statement.executeUpdate() != 1) {
176 throw new CreateException("Error adding a row into table 'Rank'.");
177 }
178 m_dirty = true;
179 } catch(SQLException sqle) {
180 log.error("Sql Execution Error!", sqle);
181 throw new DatabaseException("Error executing SQL in RankDAOImplJDBC.create.");
182 } finally {
183 DBUtils.closeStatement(statement);
184 DBUtils.closeConnection(connection);
185 }
186 }
187
188
193 public void update(int rankID, int rankMinPosts, int rankLevel, String rankTitle,
195 String rankImage, int rankType, int rankOption)
196 throws ObjectNotFoundException, DatabaseException, DuplicateKeyException {
197
198 RankBean bean = getRank(rankID);
199
200 if ( !rankTitle.equals(bean.getRankTitle()) ) {
201 try {
203 findByAlternateKey_RankTitle(rankTitle);
204 throw new DuplicateKeyException("Alternate key [RankTitle] (" + rankTitle + ")already exists. Cannot update Rank.");
205 } catch(ObjectNotFoundException e) {
206 }
208 }
209
210 if ( (rankMinPosts != bean.getRankMinPosts()) ) {
211 try {
213 findByAlternateKey_RankMinPosts(rankMinPosts);
214 throw new DuplicateKeyException("Alternate key [RankMinPosts] (" + rankMinPosts + ")already exists. Cannot update Rank.");
215 } catch(ObjectNotFoundException e) {
216 }
218 }
219
220 Connection connection = null;
221 PreparedStatement statement = null;
222 StringBuffer sql = new StringBuffer (512);
223 sql.append("UPDATE " + TABLE_NAME + " SET RankMinPosts = ?, RankLevel = ?, RankTitle = ?, RankImage = ?, RankType = ?, RankOption = ?");
224 sql.append(" WHERE RankID = ?");
225 try {
226 connection = DBUtils.getConnection();
227 statement = connection.prepareStatement(sql.toString());
228
229 statement.setInt(1, rankMinPosts);
231 statement.setInt(2, rankLevel);
232 statement.setString(3, rankTitle);
233 statement.setString(4, rankImage);
234 statement.setInt(5, rankType);
235 statement.setInt(6, rankOption);
236
237 statement.setInt(7, rankID);
239
240 if (statement.executeUpdate() != 1) {
241 throw new ObjectNotFoundException("Cannot update table Rank where primary key = (" + rankID + ").");
242 }
243 m_dirty = true;
244 } catch(SQLException sqle) {
245 log.error("Sql Execution Error!", sqle);
246 throw new DatabaseException("Error executing SQL in RankDAOImplJDBC.update.");
247 } finally {
248 DBUtils.closeStatement(statement);
249 DBUtils.closeConnection(connection);
250 }
251 }
252
253 public void delete(int rankID)
254 throws DatabaseException, ObjectNotFoundException {
255
256 Connection connection = null;
257 PreparedStatement statement = null;
258 StringBuffer sql = new StringBuffer (512);
259 sql.append("DELETE FROM " + TABLE_NAME);
260 sql.append(" WHERE RankID = ?");
261
262 try {
263 connection = DBUtils.getConnection();
264 statement = connection.prepareStatement(sql.toString());
265 statement.setInt(1, rankID);
266 if (statement.executeUpdate() != 1) {
267 throw new ObjectNotFoundException("Cannot delete a row in table Rank where primary key = (" + rankID + ").");
268 }
269 m_dirty = true;
270 } catch(SQLException sqle) {
271 log.error("Sql Execution Error!", sqle);
272 throw new DatabaseException("Error executing SQL in RankDAOImplJDBC.delete.");
273 } finally {
274 DBUtils.closeStatement(statement);
275 DBUtils.closeConnection(connection);
276 }
277 }
278
279
284 public RankBean getRank(int rankID)
285 throws ObjectNotFoundException, DatabaseException {
286
287 Connection connection = null;
288 PreparedStatement statement = null;
289 ResultSet resultSet = null;
290 StringBuffer sql = new StringBuffer (512);
291 sql.append("SELECT RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption");
292 sql.append(" FROM " + TABLE_NAME);
293 sql.append(" WHERE RankID = ?");
294 try {
295 connection = DBUtils.getConnection();
296 statement = connection.prepareStatement(sql.toString());
297 statement.setInt(1, rankID);
298 resultSet = statement.executeQuery();
299 if(!resultSet.next()) {
300 throw new ObjectNotFoundException("Cannot find the row in table Rank where primary key = (" + rankID + ").");
301 }
302
303 RankBean bean = new RankBean();
304 bean.setRankID(rankID);
306 bean.setRankMinPosts(resultSet.getInt("RankMinPosts"));
307 bean.setRankLevel(resultSet.getInt("RankLevel"));
308 bean.setRankTitle(resultSet.getString("RankTitle"));
309 bean.setRankImage(resultSet.getString("RankImage"));
310 bean.setRankType(resultSet.getInt("RankType"));
311 bean.setRankOption(resultSet.getInt("RankOption"));
312 return bean;
313 } catch(SQLException sqle) {
314 log.error("Sql Execution Error!", sqle);
315 throw new DatabaseException("Error executing SQL in RankDAOImplJDBC.getRank(pk).");
316 } finally {
317 DBUtils.closeResultSet(resultSet);
318 DBUtils.closeStatement(statement);
319 DBUtils.closeConnection(connection);
320 }
321 }
322
323
328 public Collection getRanks()
329 throws DatabaseException {
330
331 Connection connection = null;
332 PreparedStatement statement = null;
333 ResultSet resultSet = null;
334 Collection retValue = new ArrayList ();
335 StringBuffer sql = new StringBuffer (512);
336 sql.append("SELECT RankID, RankMinPosts, RankLevel, RankTitle, RankImage, RankType, RankOption");
337 sql.append(" FROM " + TABLE_NAME);
338 sql.append(" ORDER BY RankMinPosts ASC ");
340 try {
341 connection = DBUtils.getConnection();
342 statement = connection.prepareStatement(sql.toString());
343 resultSet = statement.executeQuery();
344 while (resultSet.next()) {
345 RankBean bean = new RankBean();
346 bean.setRankID(resultSet.getInt("RankID"));
347 bean.setRankMinPosts(resultSet.getInt("RankMinPosts"));
348 bean.setRankLevel(resultSet.getInt("RankLevel"));
349 bean.setRankTitle(resultSet.getString("RankTitle"));
350 bean.setRankImage(resultSet.getString("RankImage"));
351 bean.setRankType(resultSet.getInt("RankType"));
352 bean.setRankOption(resultSet.getInt("RankOption"));
353 retValue.add(bean);
354 }
355 return retValue;
356 } catch(SQLException sqle) {
357 log.error("Sql Execution Error!", sqle);
358 throw new DatabaseException("Error executing SQL in RankDAOImplJDBC.getRanks.");
359 } finally {
360 DBUtils.closeResultSet(resultSet);
361 DBUtils.closeStatement(statement);
362 DBUtils.closeConnection(connection);
363 }
364 }
365
366 public int getRankIDFromRankTitle(String rankTitle)
367 throws ObjectNotFoundException, DatabaseException {
368
369 Connection connection = null;
370 PreparedStatement statement = null;
371 ResultSet resultSet = null;
372 String sql = "SELECT RankID FROM " + TABLE_NAME +
373 " WHERE RankTitle = ?";
374 try {
375 connection = DBUtils.getConnection();
376 statement = connection.prepareStatement(sql);
377 statement.setString(1, rankTitle);
378 resultSet = statement.executeQuery();
379 if (!resultSet.next()) {
380 throw new ObjectNotFoundException("Cannot find the alternate key [RankTitle] (" + rankTitle + ") in table 'Rank'.");
381 }
382 return resultSet.getInt(1);
383 } catch (SQLException sqle) {
384 log.error("Sql Execution Error!", sqle);
385 throw new DatabaseException("Error executing SQL in RankDAOImplJDBC.getRankIDFromRankTitle.");
386 } finally {
387 DBUtils.closeResultSet(resultSet);
388 DBUtils.closeStatement(statement);
389 DBUtils.closeConnection(connection);
390 }
391 }
392
393 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.