|
1
41 package com.mvnforum.db.jdbc;
42
43 import java.sql.*;
44
45 import com.mvnforum.db.DAOFactory;
46 import com.mvnforum.db.MessageStatisticsDAO;
47 import net.myvietnam.mvncore.db.DBUtils;
48 import net.myvietnam.mvncore.exception.*;
49 import org.apache.commons.logging.Log;
50 import org.apache.commons.logging.LogFactory;
51
52 public class MessageStatisticsDAOImplJDBC implements MessageStatisticsDAO {
53
54 private static Log log = LogFactory.getLog(MessageStatisticsDAOImplJDBC.class);
55
56 private static boolean m_dirty = true;
58
59 public MessageStatisticsDAOImplJDBC() {
60 }
61
62 protected static boolean isDirty() {
63 return m_dirty;
64 }
65
66 protected static void setDirty(boolean dirty) {
67 m_dirty = dirty;
68 }
69
70
75 public void create(int fromID, int toID, Timestamp messageCreationDate,
76 int messageAttachCount, int messageType, int messageOption, int messageStatus)
77 throws CreateException, DatabaseException, ForeignKeyNotFoundException {
78
79 try {
80 DAOFactory.getMemberDAO().findByPrimaryKey(fromID);
83 } catch(ObjectNotFoundException e) {
84 throw new ForeignKeyNotFoundException("Foreign key [FromID] refers to table 'Member' does not exist. Cannot create new MessageStatistics.");
85 }
86
87 try {
88 DAOFactory.getMemberDAO().findByPrimaryKey(toID);
91 } catch(ObjectNotFoundException e) {
92 throw new ForeignKeyNotFoundException("Foreign key [ToID] refers to table 'Member' does not exist. Cannot create new MessageStatistics.");
93 }
94
95 Connection connection = null;
96 PreparedStatement statement = null;
97 StringBuffer  sql = new StringBuffer (512);
98 sql.append("INSERT INTO " + TABLE_NAME + " (FromID, ToID, MessageCreationDate, MessageAttachCount, MessageType, MessageOption, MessageStatus)");
99 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?)");
100 try {
101 connection = DBUtils.getConnection();
102 statement = connection.prepareStatement(sql.toString());
103
104 statement.setInt(1, fromID);
105 statement.setInt(2, toID);
106 statement.setTimestamp(3, messageCreationDate);
107 statement.setInt(4, messageAttachCount);
108 statement.setInt(5, messageType);
109 statement.setInt(6, messageOption);
110 statement.setInt(7, messageStatus);
111
112 if (statement.executeUpdate() != 1) {
113 throw new CreateException("Error adding a row into table 'MessageStatistics'.");
114 }
115 m_dirty = true;
116 } catch(SQLException sqle) {
117 log.error("Sql Execution Error!", sqle);
118 throw new DatabaseException("Error executing SQL in MessageStatisticsDAOImplJDBC.create.");
119 } finally {
120 DBUtils.closeStatement(statement);
121 DBUtils.closeConnection(connection);
122 }
123 }
124
125 public void delete_inMember(int memberID)
126 throws DatabaseException {
127
128 Connection connection = null;
129 PreparedStatement statement = null;
130 StringBuffer sql = new StringBuffer (512);
131 sql.append("DELETE FROM " + TABLE_NAME);
132 sql.append(" WHERE (FromID = ?) OR (ToID = ?) ");
133
134 try {
135 connection = DBUtils.getConnection();
136 statement = connection.prepareStatement(sql.toString());
137 statement.setInt(1, memberID);
138 statement.setInt(2, memberID);
139 statement.executeUpdate();
140 m_dirty = true;
141 } catch(SQLException sqle) {
142 log.error("Sql Execution Error!", sqle);
143 throw new DatabaseException("Error executing SQL in MessageStatisticsDAOImplJDBC.delete_inMember.");
144 } finally {
145 DBUtils.closeStatement(statement);
146 DBUtils.closeConnection(connection);
147 }
148 }
149
150 public int getNumberOfBeans_inFromID(int fromID)
151 throws AssertionException, DatabaseException {
152
153 Connection connection = null;
154 PreparedStatement statement = null;
155 ResultSet resultSet = null;
156 StringBuffer sql = new StringBuffer (512);
157 sql.append("SELECT Count(*)");
158 sql.append(" FROM " + TABLE_NAME);
159 sql.append(" WHERE FromID = ?");
160 try {
161 connection = DBUtils.getConnection();
162 statement = connection.prepareStatement(sql.toString());
163 statement.setInt(1, fromID);
164 resultSet = statement.executeQuery();
165 if (!resultSet.next()) {
166 throw new AssertionException("Assertion in MessageStatisticsDAOImplJDBC.getNumberOfBeans_inFromID.");
167 }
168 return resultSet.getInt(1);
169 } catch(SQLException sqle) {
170 log.error("Sql Execution Error!", sqle);
171 throw new DatabaseException("Error executing SQL in MessageStatisticsDAOImplJDBC.getNumberOfBeans_inFromID.");
172 } finally {
173 DBUtils.closeResultSet(resultSet);
174 DBUtils.closeStatement(statement);
175 DBUtils.closeConnection(connection);
176 }
177 }
178
179 public int getNumberOfBeans_inToID(int toID)
180 throws AssertionException, DatabaseException {
181
182 Connection connection = null;
183 PreparedStatement statement = null;
184 ResultSet resultSet = null;
185 StringBuffer sql = new StringBuffer (512);
186 sql.append("SELECT Count(*)");
187 sql.append(" FROM " + TABLE_NAME);
188 sql.append(" WHERE ToID = ?");
189 try {
190 connection = DBUtils.getConnection();
191 statement = connection.prepareStatement(sql.toString());
192 statement.setInt(1, toID);
193 resultSet = statement.executeQuery();
194 if (!resultSet.next()) {
195 throw new AssertionException("Assertion in MessageStatisticsDAOImplJDBC.getNumberOfBeans_inToID.");
196 }
197 return resultSet.getInt(1);
198 } catch(SQLException sqle) {
199 log.error("Sql Execution Error!", sqle);
200 throw new DatabaseException("Error executing SQL in MessageStatisticsDAOImplJDBC.getNumberOfBeans_inToID.");
201 } finally {
202 DBUtils.closeResultSet(resultSet);
203 DBUtils.closeStatement(statement);
204 DBUtils.closeConnection(connection);
205 }
206 }
207
208 public int getNumberOfBeans_inFromID_supportTimestamp(int fromID, Timestamp from, Timestamp to)
209 throws AssertionException, DatabaseException {
210
211 Connection connection = null;
212 PreparedStatement statement = null;
213 ResultSet resultSet = null;
214 StringBuffer sql = new StringBuffer (512);
215 sql.append("SELECT Count(*)");
216 sql.append(" FROM " + TABLE_NAME);
217 sql.append(" WHERE FromID = ?");
218 sql.append(" AND MessageCreationDate >= ?");
219 sql.append(" AND MessageCreationDate <= ?");
220
221 try {
222 connection = DBUtils.getConnection();
223 statement = connection.prepareStatement(sql.toString());
224 statement.setInt(1, fromID);
225 statement.setTimestamp(2, from);
226 statement.setTimestamp(3, to);
227 resultSet = statement.executeQuery();
228 if (!resultSet.next()) {
229 throw new AssertionException("Assertion in MessageStatisticsDAOImplJDBC.getNumberOfBeans_inFromID_supportTimestamp.");
230 }
231 return resultSet.getInt(1);
232 } catch(SQLException sqle) {
233 log.error("Sql Execution Error!", sqle);
234 throw new DatabaseException("Error executing SQL in MessageStatisticsDAOImplJDBC.getNumberOfBeans_inFromID_supportTimestamp.");
235 } finally {
236 DBUtils.closeResultSet(resultSet);
237 DBUtils.closeStatement(statement);
238 DBUtils.closeConnection(connection);
239 }
240 }
241
242 public int getNumberOfBeans_inToID_supportTimestamp(int toID, Timestamp from, Timestamp to)
243 throws AssertionException, DatabaseException {
244
245 Connection connection = null;
246 PreparedStatement statement = null;
247 ResultSet resultSet = null;
248 StringBuffer sql = new StringBuffer (512);
249 sql.append("SELECT Count(*)");
250 sql.append(" FROM " + TABLE_NAME);
251 sql.append(" WHERE ToID = ?");
252 sql.append(" AND MessageCreationDate >= ?");
253 sql.append(" AND MessageCreationDate <= ?");
254
255 try {
256 connection = DBUtils.getConnection();
257 statement = connection.prepareStatement(sql.toString());
258 statement.setInt(1, toID);
259 statement.setTimestamp(2, from);
260 statement.setTimestamp(3, to);
261 resultSet = statement.executeQuery();
262 if (!resultSet.next()) {
263 throw new AssertionException("Assertion in MessageStatisticsDAOImplJDBC.getNumberOfBeans_inToID_supportTimestamp.");
264 }
265 return resultSet.getInt(1);
266 } catch(SQLException sqle) {
267 log.error("Sql Execution Error!", sqle);
268 throw new DatabaseException("Error executing SQL in MessageStatisticsDAOImplJDBC.getNumberOfBeans_inToID_supportTimestamp.");
269 } finally {
270 DBUtils.closeResultSet(resultSet);
271 DBUtils.closeStatement(statement);
272 DBUtils.closeConnection(connection);
273 }
274 }
275
276 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.