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 |