|
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.*;
48 import net.myvietnam.mvncore.db.DBUtils;
49 import net.myvietnam.mvncore.exception.*;
50 import org.apache.commons.logging.Log;
51 import org.apache.commons.logging.LogFactory;
52
53 public class MemberCompanyDAOImplJDBC implements MemberCompanyDAO {
54
55 private static Log log = LogFactory.getLog(MemberCompanyDAOImplJDBC.class);
56
57 private static boolean m_dirty = true;
59
60 public MemberCompanyDAOImplJDBC() {
61 }
62
63 protected static boolean isDirty() {
64 return m_dirty;
65 }
66
67 protected static void setDirty(boolean dirty) {
68 m_dirty = dirty;
69 }
70
71 public void findByPrimaryKey(int memberID)
72 throws ObjectNotFoundException, DatabaseException {
73
74 Connection connection = null;
75 PreparedStatement statement = null;
76 ResultSet resultSet = null;
77 StringBuffer sql = new StringBuffer (512);
78 sql.append("SELECT MemberID");
79 sql.append(" FROM " + TABLE_NAME);
80 sql.append(" WHERE MemberID = ?");
81 try {
82 connection = DBUtils.getConnection();
83 statement = connection.prepareStatement(sql.toString());
84 statement.setInt(1, memberID);
85 resultSet = statement.executeQuery();
86 if (!resultSet.next()) {
87 throw new ObjectNotFoundException("Cannot find the primary key (" + memberID + ") in table 'MemberCompany'.");
88 }
89 } catch(SQLException sqle) {
90 log.error("Sql Execution Error!", sqle);
91 throw new DatabaseException("Error executing SQL in MemberCompanyDAOImplJDBC.findByPrimaryKey.");
92 } finally {
93 DBUtils.closeResultSet(resultSet);
94 DBUtils.closeStatement(statement);
95 DBUtils.closeConnection(connection);
96 }
97 }
98
99
104 public void create(int memberID, String memberName, int companyID,
105 Timestamp creationDate, Timestamp expireDate, int isActive,
106 int relationType, int relationOption, int relationStatus)
107 throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException {
108
109 try {
113 findByPrimaryKey(memberID);
115 throw new DuplicateKeyException("Primary key already exists. Cannot create new MemberCompany with the same [MemberID] (" + memberID + ").");
117 } catch(ObjectNotFoundException e) {
118 }
120
121 try {
122 DAOFactory.getMemberDAO().findByPrimaryKey2(memberID, memberName);
125 } catch(ObjectNotFoundException e) {
126 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new MemberCompany.");
127 }
128
129 try {
130 DAOFactory.getCompanyDAO().findByPrimaryKey(companyID);
133 } catch(ObjectNotFoundException e) {
134 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Company' does not exist. Cannot create new MemberCompany.");
135 }
136
137 Connection connection = null;
138 PreparedStatement statement = null;
139 StringBuffer sql = new StringBuffer (512);
140 sql.append("INSERT INTO " + TABLE_NAME + " (MemberID, MemberName, CompanyID, CreationDate, ExpireDate, IsActive, RelationType, RelationOption, RelationStatus)");
141 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
142 try {
143 connection = DBUtils.getConnection();
144 statement = connection.prepareStatement(sql.toString());
145
146 statement.setInt(1, memberID);
147 statement.setString(2, memberName);
148 statement.setInt(3, companyID);
149 statement.setTimestamp(4, creationDate);
150 statement.setTimestamp(5, expireDate);
151 statement.setInt(6, isActive);
152 statement.setInt(7, relationType);
153 statement.setInt(8, relationOption);
154 statement.setInt(9, relationStatus);
155
156 if (statement.executeUpdate() != 1) {
157 throw new CreateException("Error adding a row into table 'MemberCompany'.");
158 }
159 m_dirty = true;
160 } catch(SQLException sqle) {
161 log.error("Sql Execution Error!", sqle);
162 throw new DatabaseException("Error executing SQL in MemberCompanyDAOImplJDBC.create.");
163 } finally {
164 DBUtils.closeStatement(statement);
165 DBUtils.closeConnection(connection);
166 }
167 }
168
169 public void delete(int memberID, int companyID)
170 throws DatabaseException, ObjectNotFoundException {
171
172 Connection connection = null;
173 PreparedStatement statement = null;
174 StringBuffer sql = new StringBuffer (512);
175 sql.append("DELETE FROM " + TABLE_NAME);
176 sql.append(" WHERE MemberID = ? AND CompanyID = ?");
177
178 try {
179 connection = DBUtils.getConnection();
180 statement = connection.prepareStatement(sql.toString());
181 statement.setInt(1, memberID);
182 statement.setInt(2, companyID);
183 if (statement.executeUpdate() != 1) {
184 throw new ObjectNotFoundException("Cannot delete a row in table MemberCompany where primary key = (" + memberID + ").");
185 }
186 m_dirty = true;
187 } catch(SQLException sqle) {
188 log.error("Sql Execution Error!", sqle);
189 throw new DatabaseException("Error executing SQL in MemberCompanyDAOImplJDBC.delete.");
190 } finally {
191 DBUtils.closeStatement(statement);
192 DBUtils.closeConnection(connection);
193 }
194 }
195
196 public void delete_inMember(int memberID)
197 throws DatabaseException {
198
199 Connection connection = null;
200 PreparedStatement statement = null;
201 StringBuffer sql = new StringBuffer (512);
202 sql.append("DELETE FROM " + TABLE_NAME);
203 sql.append(" WHERE MemberID = ?");
204
205 try {
206 connection = DBUtils.getConnection();
207 statement = connection.prepareStatement(sql.toString());
208 statement.setInt(1, memberID);
209 statement.executeUpdate();
210 m_dirty = true;
211 } catch(SQLException sqle) {
212 log.error("Sql Execution Error!", sqle);
213 throw new DatabaseException("Error executing SQL in MemberCompanyDAOImplJDBC.delete_inMember.");
214 } finally {
215 DBUtils.closeStatement(statement);
216 DBUtils.closeConnection(connection);
217 }
218 }
219
220 public void delete_inCompany(int companyID)
221 throws DatabaseException {
222
223 Connection connection = null;
224 PreparedStatement statement = null;
225 StringBuffer sql = new StringBuffer (512);
226 sql.append("DELETE FROM " + TABLE_NAME);
227 sql.append(" WHERE CompanyID = ?");
228
229 try {
230 connection = DBUtils.getConnection();
231 statement = connection.prepareStatement(sql.toString());
232 statement.setInt(1, companyID);
233 statement.executeUpdate();
234 m_dirty = true;
235 } catch(SQLException sqle) {
236 log.error("Sql Execution Error!", sqle);
237 throw new DatabaseException("Error executing SQL in MemberCompanyDAOImplJDBC.delete_inCompany.");
238 } finally {
239 DBUtils.closeStatement(statement);
240 DBUtils.closeConnection(connection);
241 }
242 }
243
244
249 public int getCompanyIDFromMemberID(int memberID)
250 throws ObjectNotFoundException, DatabaseException {
251
252 Connection connection = null;
253 PreparedStatement statement = null;
254 ResultSet resultSet = null;
255 StringBuffer sql = new StringBuffer (512);
256 sql.append("SELECT CompanyID");
257 sql.append(" FROM " + TABLE_NAME);
258 sql.append(" WHERE MemberID = ?");
259 try {
260 connection = DBUtils.getConnection();
261 statement = connection.prepareStatement(sql.toString());
262 statement.setInt(1, memberID);
263 resultSet = statement.executeQuery();
264 if(!resultSet.next()) {
265 throw new ObjectNotFoundException("Cannot find the row in table MemberCompany where primary key = (" + memberID + ").");
266 }
267
268 return resultSet.getInt("CompanyID");
269 } catch(SQLException sqle) {
270 log.error("Sql Execution Error!", sqle);
271 throw new DatabaseException("Error executing SQL in MemberCompanyDAOImplJDBC.getCompanyIDFromMemberID(pk).");
272 } finally {
273 DBUtils.closeResultSet(resultSet);
274 DBUtils.closeStatement(statement);
275 DBUtils.closeConnection(connection);
276 }
277 }
278
279
284 public Collection getBeans_inCompany_limit(int companyID, int offset, int rowsToReturn)
285 throws IllegalArgumentException , DatabaseException {
286 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed.");
287 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed.");
288
289 Connection connection = null;
290 PreparedStatement statement = null;
291 ResultSet resultSet = null;
292 Collection retValue = new ArrayList ();
293 StringBuffer sql = new StringBuffer (512);
294 sql.append("SELECT MemberID, MemberName, CompanyID, CreationDate, ExpireDate, IsActive, RelationType, RelationOption, RelationStatus");
295 sql.append(" FROM " + TABLE_NAME);
296 sql.append(" WHERE CompanyID = ?");
297 sql.append(" LIMIT ?, ?");
299 try {
300 connection = DBUtils.getConnection();
301 statement = connection.prepareStatement(sql.toString());
302 statement.setInt(1, companyID);
303 statement.setInt(2, offset);
304 statement.setInt(3, rowsToReturn);
305 resultSet = statement.executeQuery();
306 while (resultSet.next()) {
307 MemberCompanyBean bean = new MemberCompanyBean();
308 bean.setMemberID(resultSet.getInt("MemberID"));
309 bean.setMemberName(resultSet.getString("MemberName"));
310 bean.setCompanyID(resultSet.getInt("CompanyID"));
311 bean.setCreationDate(resultSet.getTimestamp("CreationDate"));
312 bean.setExpireDate(resultSet.getTimestamp("ExpireDate"));
313 bean.setIsActive(resultSet.getInt("IsActive"));
314 bean.setRelationType(resultSet.getInt("RelationType"));
315 bean.setRelationOption(resultSet.getInt("RelationOption"));
316 bean.setRelationStatus(resultSet.getInt("RelationStatus"));
317 retValue.add(bean);
318 }
319 return retValue;
320 } catch(SQLException sqle) {
321 log.error("Sql Execution Error!", sqle);
322 throw new DatabaseException("Error executing SQL in MemberCompanyDAOImplJDBC.getBeans_inCompany_limit.");
323 } finally {
324 DBUtils.closeResultSet(resultSet);
325 DBUtils.closeStatement(statement);
326 DBUtils.closeConnection(connection);
327 }
328 }
329
330 public int getNumberOfBeans_inCompany(int companyID)
331 throws AssertionException, DatabaseException {
332
333 Connection connection = null;
334 PreparedStatement statement = null;
335 ResultSet resultSet = null;
336 StringBuffer sql = new StringBuffer (512);
337 sql.append("SELECT Count(*)");
338 sql.append(" FROM " + TABLE_NAME);
339 sql.append(" WHERE CompanyID = ?");
340 try {
341 connection = DBUtils.getConnection();
342 statement = connection.prepareStatement(sql.toString());
343 statement.setInt(1, companyID);
344 resultSet = statement.executeQuery();
345 if (!resultSet.next()) {
346 throw new AssertionException("Assertion in MemberCompanyDAOImplJDBC.getNumberOfBeans_inCompany.");
347 }
348 return resultSet.getInt(1);
349 } catch(SQLException sqle) {
350 log.error("Sql Execution Error!", sqle);
351 throw new DatabaseException("Error executing SQL in MemberCompanyDAOImplJDBC.getNumberOfBeans_inCompany.");
352 } finally {
353 DBUtils.closeResultSet(resultSet);
354 DBUtils.closeStatement(statement);
355 DBUtils.closeConnection(connection);
356 }
357 }
358 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.