|
1
40 package com.mvnforum.db.jdbc;
41
42 import java.sql.*;
43 import java.util.*;
44
45 import com.mvnforum.db.*;
46 import net.myvietnam.mvncore.db.DBUtils;
47 import net.myvietnam.mvncore.exception.*;
48 import net.myvietnam.mvncore.util.DateUtil;
49 import org.apache.commons.logging.Log;
50 import org.apache.commons.logging.LogFactory;
51
52 public class CompanyDAOImplJDBC implements CompanyDAO {
53
54 private static Log log = LogFactory.getLog(CompanyDAOImplJDBC.class);
55
56 private static boolean m_dirty = true;
58
59 public CompanyDAOImplJDBC() {
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 public void findByPrimaryKey(int companyID)
71 throws ObjectNotFoundException, DatabaseException {
72
73 Connection connection = null;
74 PreparedStatement statement = null;
75 ResultSet resultSet = null;
76 StringBuffer  sql = new StringBuffer (512);
77 sql.append("SELECT CompanyID");
78 sql.append(" FROM " + TABLE_NAME);
79 sql.append(" WHERE CompanyID = ?");
80 try {
81 connection = DBUtils.getConnection();
82 statement = connection.prepareStatement(sql.toString());
83 statement.setInt(1, companyID);
84 resultSet = statement.executeQuery();
85 if (!resultSet.next()) {
86 throw new ObjectNotFoundException("Cannot find the primary key (" + companyID + ") in table 'Company'.");
87 }
88 } catch(SQLException sqle) {
89 log.error("Sql Execution Error!", sqle);
90 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.findByPrimaryKey.");
91 } finally {
92 DBUtils.closeResultSet(resultSet);
93 DBUtils.closeStatement(statement);
94 DBUtils.closeConnection(connection);
95 }
96 }
97
98 public void findByAlternateKey_GroupID(int groupID)
99 throws ObjectNotFoundException, DatabaseException {
100
101 Connection connection = null;
102 PreparedStatement statement = null;
103 ResultSet resultSet = null;
104 StringBuffer sql = new StringBuffer (512);
105 sql.append("SELECT GroupID");
106 sql.append(" FROM " + TABLE_NAME);
107 sql.append(" WHERE GroupID = ?");
108 try {
109 connection = DBUtils.getConnection();
110 statement = connection.prepareStatement(sql.toString());
111 statement.setInt(1, groupID);
112 resultSet = statement.executeQuery();
113 if (!resultSet.next()) {
114 throw new ObjectNotFoundException("Cannot find the alternate key [GroupID] (" + groupID + ") in table 'Company'.");
115 }
116 } catch(SQLException sqle) {
117 log.error("Sql Execution Error!", sqle);
118 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.findByAlternateKey_GroupID.");
119 } finally {
120 DBUtils.closeResultSet(resultSet);
121 DBUtils.closeStatement(statement);
122 DBUtils.closeConnection(connection);
123 }
124 }
125
126 public void findByAlternateKey_CompanyName(String companyName)
127 throws ObjectNotFoundException, DatabaseException {
128
129 Connection connection = null;
130 PreparedStatement statement = null;
131 ResultSet resultSet = null;
132 StringBuffer sql = new StringBuffer (512);
133 sql.append("SELECT CompanyName");
134 sql.append(" FROM " + TABLE_NAME);
135 sql.append(" WHERE CompanyName = ?");
136 try {
137 connection = DBUtils.getConnection();
138 statement = connection.prepareStatement(sql.toString());
139 statement.setString(1, companyName);
140 resultSet = statement.executeQuery();
141 if (!resultSet.next()) {
142 throw new ObjectNotFoundException("Cannot find the alternate key [CompanyName] (" + companyName + ") in table 'Company'.");
143 }
144 } catch(SQLException sqle) {
145 log.error("Sql Execution Error!", sqle);
146 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.findByAlternateKey_CompanyName.");
147 } finally {
148 DBUtils.closeResultSet(resultSet);
149 DBUtils.closeStatement(statement);
150 DBUtils.closeConnection(connection);
151 }
152 }
153
154 public void findByAlternateKey_CompanyEmail(String companyEmail)
155 throws ObjectNotFoundException, DatabaseException {
156
157 Connection connection = null;
158 PreparedStatement statement = null;
159 ResultSet resultSet = null;
160 StringBuffer sql = new StringBuffer (512);
161 sql.append("SELECT CompanyEmail");
162 sql.append(" FROM " + TABLE_NAME);
163 sql.append(" WHERE CompanyEmail = ?");
164 try {
165 connection = DBUtils.getConnection();
166 statement = connection.prepareStatement(sql.toString());
167 statement.setString(1, companyEmail);
168 resultSet = statement.executeQuery();
169 if (!resultSet.next()) {
170 throw new ObjectNotFoundException("Cannot find the alternate key [CompanyEmail] (" + companyEmail + ") in table 'Company'.");
171 }
172 } catch(SQLException sqle) {
173 log.error("Sql Execution Error!", sqle);
174 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.findByAlternateKey_CompanyEmail.");
175 } finally {
176 DBUtils.closeResultSet(resultSet);
177 DBUtils.closeStatement(statement);
178 DBUtils.closeConnection(connection);
179 }
180 }
181
182 public void findByAlternateKey_CompanySpaceName(String companySpaceName)
183 throws ObjectNotFoundException, DatabaseException {
184
185 Connection connection = null;
186 PreparedStatement statement = null;
187 ResultSet resultSet = null;
188 StringBuffer sql = new StringBuffer (512);
189 sql.append("SELECT CompanySpaceName");
190 sql.append(" FROM " + TABLE_NAME);
191 sql.append(" WHERE CompanySpaceName = ?");
192 try {
193 connection = DBUtils.getConnection();
194 statement = connection.prepareStatement(sql.toString());
195 statement.setString(1, companySpaceName);
196 resultSet = statement.executeQuery();
197 if (!resultSet.next()) {
198 throw new ObjectNotFoundException("Cannot find the alternate key [CompanySpaceName] (" + companySpaceName + ") in table 'Company'.");
199 }
200 } catch(SQLException sqle) {
201 log.error("Sql Execution Error!", sqle);
202 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.findByAlternateKey_CompanySpaceName.");
203 } finally {
204 DBUtils.closeResultSet(resultSet);
205 DBUtils.closeStatement(statement);
206 DBUtils.closeConnection(connection);
207 }
208 }
209
210
217 public void create(int groupID, String companyName, String companyAddress,
218 String companyCity, String companyCAP, String companyProvince,
219 String companyRegion, String companyPhone, String companyFax,
220 String companyWebsite, String companyEmail, String companySpaceName,
221 String companySpaceHeader, String companySpaceFooter, String companyVATNumber,
222 String companyLogo, String companyCss, Timestamp companyCreationDate,
223 Timestamp companyModifiedDate)
224 throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException {
225
226 try {
229 findByAlternateKey_GroupID(groupID);
231 throw new DuplicateKeyException("Alternate key already exists. Cannot create new Company with the same [GroupID] (" + groupID + ").");
233 } catch(ObjectNotFoundException e) {
234 }
236
237 try {
240 findByAlternateKey_CompanyName(companyName);
242 throw new DuplicateKeyException("Alternate key already exists. Cannot create new Company with the same [CompanyName] (" + companyName + ").");
244 } catch(ObjectNotFoundException e) {
245 }
247
248 try {
251 findByAlternateKey_CompanyEmail(companyEmail);
253 throw new DuplicateKeyException("Alternate key already exists. Cannot create new Company with the same [CompanyEmail] (" + companyEmail + ").");
255 } catch(ObjectNotFoundException e) {
256 }
258
259 try {
262 findByAlternateKey_CompanySpaceName(companySpaceName);
264 throw new DuplicateKeyException("Alternate key already exists. Cannot create new Company with the same [CompanySpaceName] (" + companySpaceName + ").");
266 } catch(ObjectNotFoundException e) {
267 }
269
270 try {
271 DAOFactory.getGroupsDAO().findByPrimaryKey(groupID);
274 } catch(ObjectNotFoundException e) {
275 throw new ForeignKeyNotFoundException("Foreign key refers to table 'Groups' does not exist. Cannot create new Company.");
276 }
277
278 Connection connection = null;
279 PreparedStatement statement = null;
280 StringBuffer sql = new StringBuffer (512);
281 sql.append("INSERT INTO " + TABLE_NAME + " (GroupID, CompanyName, CompanyAddress, CompanyCity, CompanyCAP, CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax, CompanyWebsite, CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter, CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate)");
282 sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
283 try {
284 connection = DBUtils.getConnection();
285 statement = connection.prepareStatement(sql.toString());
286
287 statement.setInt(1, groupID);
288 statement.setString(2, companyName);
289 statement.setString(3, companyAddress);
290 statement.setString(4, companyCity);
291 statement.setString(5, companyCAP);
292 statement.setString(6, companyProvince);
293 statement.setString(7, companyRegion);
294 statement.setString(8, companyPhone);
295 statement.setString(9, companyFax);
296 statement.setString(10, companyWebsite);
297 statement.setString(11, companyEmail);
298 statement.setString(12, companySpaceName);
299 statement.setString(13, companySpaceHeader);
300 statement.setString(14, companySpaceFooter);
301 statement.setString(15, companyVATNumber);
302 statement.setString(16, companyLogo);
303 statement.setString(17, companyCss);
304 statement.setTimestamp(18, companyCreationDate);
305 statement.setTimestamp(19, companyModifiedDate);
306
307 if (statement.executeUpdate() != 1) {
308 throw new CreateException("Error adding a row into table 'Company'.");
309 }
310 m_dirty = true;
311 } catch(SQLException sqle) {
312 log.error("Sql Execution Error!", sqle);
313 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.create.");
314 } finally {
315 DBUtils.closeStatement(statement);
316 DBUtils.closeConnection(connection);
317 }
318 }
319
320 public void delete(int companyID)
321 throws DatabaseException, ObjectNotFoundException {
322
323 Connection connection = null;
324 PreparedStatement statement = null;
325 StringBuffer sql = new StringBuffer (512);
326 sql.append("DELETE FROM " + TABLE_NAME);
327 sql.append(" WHERE CompanyID = ?");
328
329 try {
330 connection = DBUtils.getConnection();
331 statement = connection.prepareStatement(sql.toString());
332 statement.setInt(1, companyID);
333 if (statement.executeUpdate() != 1) {
334 throw new ObjectNotFoundException("Cannot delete a row in table Company where primary key = (" + companyID + ").");
335 }
336 m_dirty = true;
337 } catch(SQLException sqle) {
338 log.error("Sql Execution Error!", sqle);
339 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.delete.");
340 } finally {
341 DBUtils.closeStatement(statement);
342 DBUtils.closeConnection(connection);
343 }
344 }
345
346
352 public void updateCompanyInfo(int companyID, String companyName, String companyAddress, String companyCity,
354 String companyCAP, String companyProvince, String companyRegion,
355 String companyPhone, String companyFax, String companyWebsite,
356 String companyEmail, String companySpaceName, String companySpaceHeader,
357 String companySpaceFooter, String companyVATNumber, Timestamp companyModifiedDate)
358 throws ObjectNotFoundException, DatabaseException, DuplicateKeyException {
359
360 CompanyBean bean = getCompany(companyID);
361
362 if ( !companyName.equals(bean.getCompanyName()) ) {
363 try {
365 findByAlternateKey_CompanyName(companyName);
366 throw new DuplicateKeyException("Alternate key [CompanyName] (" + companyName + ")already exists. Cannot update Company.");
367 } catch(ObjectNotFoundException e) {
368 }
370 }
371
372 if ( !companyEmail.equals(bean.getCompanyEmail()) ) {
373 try {
375 findByAlternateKey_CompanyEmail(companyEmail);
376 throw new DuplicateKeyException("Alternate key [CompanyEmail] (" + companyEmail + ")already exists. Cannot update Company.");
377 } catch(ObjectNotFoundException e) {
378 }
380 }
381
382 if ( !companySpaceName.equals(bean.getCompanySpaceName()) ) {
383 try {
385 findByAlternateKey_CompanySpaceName(companySpaceName);
386 throw new DuplicateKeyException("Alternate key [CompanySpaceName] (" + companySpaceName + ")already exists. Cannot update Company.");
387 } catch(ObjectNotFoundException e) {
388 }
390 }
391
392 Connection connection = null;
393 PreparedStatement statement = null;
394 StringBuffer sql = new StringBuffer (512);
395 sql.append("UPDATE " + TABLE_NAME + " SET CompanyName = ?, CompanyAddress = ?, CompanyCity = ?, CompanyCAP = ?, CompanyProvince = ?, CompanyRegion = ?, CompanyPhone = ?, CompanyFax = ?, CompanyWebsite = ?, CompanyEmail = ?, CompanySpaceName = ?, CompanySpaceHeader = ?, CompanySpaceFooter = ?, CompanyVATNumber = ?, CompanyModifiedDate = ?");
396 sql.append(" WHERE CompanyID = ?");
397 try {
398 connection = DBUtils.getConnection();
399 statement = connection.prepareStatement(sql.toString());
400
401 statement.setString(1, companyName);
403 statement.setString(2, companyAddress);
404 statement.setString(3, companyCity);
405 statement.setString(4, companyCAP);
406 statement.setString(5, companyProvince);
407 statement.setString(6, companyRegion);
408 statement.setString(7, companyPhone);
409 statement.setString(8, companyFax);
410 statement.setString(9, companyWebsite);
411 statement.setString(10, companyEmail);
412 statement.setString(11, companySpaceName);
413 statement.setString(12, companySpaceHeader);
414 statement.setString(13, companySpaceFooter);
415 statement.setString(14, companyVATNumber);
416 statement.setTimestamp(15, companyModifiedDate);
417
418 statement.setInt(16, companyID);
420
421 if (statement.executeUpdate() != 1) {
422 throw new ObjectNotFoundException("Cannot update table Company where primary key = (" + companyID + ").");
423 }
424 m_dirty = true;
425 } catch(SQLException sqle) {
426 log.error("Sql Execution Error!", sqle);
427 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.updateCompanyInfo.");
428 } finally {
429 DBUtils.closeStatement(statement);
430 DBUtils.closeConnection(connection);
431 }
432 }
433
434 public void updateCompanyLogo(int companyID, String companyLogo)
436 throws ObjectNotFoundException, DatabaseException {
437
438 Connection connection = null;
439 PreparedStatement statement = null;
440 StringBuffer sql = new StringBuffer (512);
441 sql.append("UPDATE " + TABLE_NAME + " SET CompanyLogo = ?");
442 sql.append(" WHERE CompanyID = ?");
443 try {
444 connection = DBUtils.getConnection();
445 statement = connection.prepareStatement(sql.toString());
446
447 statement.setString(1, companyLogo);
449
450 statement.setInt(2, companyID);
452
453 if (statement.executeUpdate() != 1) {
454 throw new ObjectNotFoundException("Cannot update table Company where primary key = (" + companyID + ").");
455 }
456 m_dirty = true;
457 } catch(SQLException sqle) {
458 log.error("Sql Execution Error!", sqle);
459 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.updateCompanyLogo.");
460 } finally {
461 DBUtils.closeStatement(statement);
462 DBUtils.closeConnection(connection);
463 }
464 }
465
466 public void updateCompanyCss(int companyID, String companyCss)
468 throws ObjectNotFoundException, DatabaseException {
469
470 Connection connection = null;
471 PreparedStatement statement = null;
472 StringBuffer sql = new StringBuffer (512);
473 sql.append("UPDATE " + TABLE_NAME + " SET CompanyCss = ?");
474 sql.append(" WHERE CompanyID = ?");
475 try {
476 connection = DBUtils.getConnection();
477 statement = connection.prepareStatement(sql.toString());
478
479 statement.setString(1, companyCss);
481
482 statement.setInt(2, companyID);
484
485 if (statement.executeUpdate() != 1) {
486 throw new ObjectNotFoundException("Cannot update table Company where primary key = (" + companyID + ").");
487 }
488 m_dirty = true;
489 } catch(SQLException sqle) {
490 log.error("Sql Execution Error!", sqle);
491 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.updateCompanyCss.");
492 } finally {
493 DBUtils.closeStatement(statement);
494 DBUtils.closeConnection(connection);
495 }
496 }
497
498 public int getCompanyIDFromGroupID(int groupID)
499 throws ObjectNotFoundException, DatabaseException {
500
501 Connection connection = null;
502 PreparedStatement statement = null;
503 ResultSet resultSet = null;
504 StringBuffer sql = new StringBuffer (512);
505 sql.append("SELECT CompanyID");
506 sql.append(" FROM " + TABLE_NAME);
507 sql.append(" WHERE GroupID = ?");
508 try {
509 connection = DBUtils.getConnection();
510 statement = connection.prepareStatement(sql.toString());
511 statement.setInt(1, groupID);
512 resultSet = statement.executeQuery();
513 if(!resultSet.next()) {
514 throw new ObjectNotFoundException("Cannot find the row in table Company where alternate key [GroupID] = (" + groupID + ").");
515 }
516
517 return resultSet.getInt("CompanyID");
518 } catch(SQLException sqle) {
519 log.error("Sql Execution Error!", sqle);
520 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompanyIDFromGroupID(ak).");
521 } finally {
522 DBUtils.closeResultSet(resultSet);
523 DBUtils.closeStatement(statement);
524 DBUtils.closeConnection(connection);
525 }
526 }
527
528 public int getCompanyIDFromCompanyEmail(String companyEmail)
529 throws ObjectNotFoundException, DatabaseException {
530
531 Connection connection = null;
532 PreparedStatement statement = null;
533 ResultSet resultSet = null;
534 StringBuffer sql = new StringBuffer (512);
535 sql.append("SELECT CompanyID");
536 sql.append(" FROM " + TABLE_NAME);
537 sql.append(" WHERE CompanyEmail = ?");
538 try {
539 connection = DBUtils.getConnection();
540 statement = connection.prepareStatement(sql.toString());
541 statement.setString(1, companyEmail);
542 resultSet = statement.executeQuery();
543 if(!resultSet.next()) {
544 throw new ObjectNotFoundException("Cannot find the row in table Company where alternate key [CompanyEmail] = (" + companyEmail + ").");
545 }
546
547 return resultSet.getInt("CompanyID");
548 } catch(SQLException sqle) {
549 log.error("Sql Execution Error!", sqle);
550 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompanyIDFromCompanyEmail(ak).");
551 } finally {
552 DBUtils.closeResultSet(resultSet);
553 DBUtils.closeStatement(statement);
554 DBUtils.closeConnection(connection);
555 }
556 }
557
558 public int getCompanyIDFromCompanySpaceName(String companySpaceName)
559 throws ObjectNotFoundException, DatabaseException {
560
561 Connection connection = null;
562 PreparedStatement statement = null;
563 ResultSet resultSet = null;
564 StringBuffer sql = new StringBuffer (512);
565 sql.append("SELECT CompanyID");
566 sql.append(" FROM " + TABLE_NAME);
567 sql.append(" WHERE CompanySpaceName = ?");
568 try {
569 connection = DBUtils.getConnection();
570 statement = connection.prepareStatement(sql.toString());
571 statement.setString(1, companySpaceName);
572 resultSet = statement.executeQuery();
573 if(!resultSet.next()) {
574 throw new ObjectNotFoundException("Cannot find the row in table Company where alternate key [CompanySpaceName] = (" + companySpaceName + ").");
575 }
576
577 return resultSet.getInt("CompanyID");
578 } catch(SQLException sqle) {
579 log.error("Sql Execution Error!", sqle);
580 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompanyIDFromCompanySpaceName(ak).");
581 } finally {
582 DBUtils.closeResultSet(resultSet);
583 DBUtils.closeStatement(statement);
584 DBUtils.closeConnection(connection);
585 }
586 }
587
588 public int getCompanyIDFromCompanyCreationDate(java.util.Date companyCreationDate)
589 throws ObjectNotFoundException, DatabaseException {
590
591 int totalCompanies = 0;
592 int ret = 0;
593
594 try {
595 totalCompanies = getNumberOfCompanies();
596 } catch (Exception err) {}
597
598 Collection collectionCompany = getCompanies_withSortSupport_limit(0, totalCompanies, "CompanyCreationDate", "ASC");
599 Iterator iteratorCompany = collectionCompany.iterator();
600 while (iteratorCompany.hasNext()) {
601 CompanyBean companyBean = (CompanyBean) iteratorCompany.next();
602 java.util.Date companyCreationDateDB = new java.util.Date (companyBean.getCompanyCreationDate().getTime());
603 String dateFormat = DateUtil.getDateDDMMYYYY(companyCreationDate);
604 String dateFormatDB = DateUtil.getDateDDMMYYYY(companyCreationDateDB);
605 if (dateFormat.equals(dateFormatDB)) {
606 ret = companyBean.getCompanyID();
607 break;
608 }
609 }
610 if (ret == 0) {
611 throw new ObjectNotFoundException("Cannot get company ID from company date.");
612 }
613 return ret;
614 }
615
616
623 public CompanyBean getCompany(int companyID)
624 throws ObjectNotFoundException, DatabaseException {
625
626 Connection connection = null;
627 PreparedStatement statement = null;
628 ResultSet resultSet = null;
629 StringBuffer sql = new StringBuffer (512);
630 sql.append("SELECT CompanyID, GroupID, CompanyName, CompanyAddress, CompanyCity, CompanyCAP, CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax, CompanyWebsite, CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter, CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate");
631 sql.append(" FROM " + TABLE_NAME);
632 sql.append(" WHERE CompanyID = ?");
633 try {
634 connection = DBUtils.getConnection();
635 statement = connection.prepareStatement(sql.toString());
636 statement.setInt(1, companyID);
637 resultSet = statement.executeQuery();
638 if(!resultSet.next()) {
639 throw new ObjectNotFoundException("Cannot find the row in table Company where primary key = (" + companyID + ").");
640 }
641
642 CompanyBean bean = new CompanyBean();
643 bean.setCompanyID(resultSet.getInt("CompanyID"));
646 bean.setGroupID(resultSet.getInt("GroupID"));
647 bean.setCompanyName(resultSet.getString("CompanyName"));
648 bean.setCompanyAddress(resultSet.getString("CompanyAddress"));
649 bean.setCompanyCity(resultSet.getString("CompanyCity"));
650 bean.setCompanyCAP(resultSet.getString("CompanyCAP"));
651 bean.setCompanyProvince(resultSet.getString("CompanyProvince"));
652 bean.setCompanyRegion(resultSet.getString("CompanyRegion"));
653 bean.setCompanyPhone(resultSet.getString("CompanyPhone"));
654 bean.setCompanyFax(resultSet.getString("CompanyFax"));
655 bean.setCompanyWebsite(resultSet.getString("CompanyWebsite"));
656 bean.setCompanyEmail(resultSet.getString("CompanyEmail"));
657 bean.setCompanySpaceName(resultSet.getString("CompanySpaceName"));
658 bean.setCompanySpaceHeader(resultSet.getString("CompanySpaceHeader"));
659 bean.setCompanySpaceFooter(resultSet.getString("CompanySpaceFooter"));
660 bean.setCompanyVATNumber(resultSet.getString("CompanyVATNumber"));
661 bean.setCompanyLogo(resultSet.getString("CompanyLogo"));
662 bean.setCompanyCss(resultSet.getString("CompanyCss"));
663 bean.setCompanyCreationDate(resultSet.getTimestamp("CompanyCreationDate"));
664 bean.setCompanyModifiedDate(resultSet.getTimestamp("CompanyModifiedDate"));
665 return bean;
666 } catch(SQLException sqle) {
667 log.error("Sql Execution Error!", sqle);
668 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompany(pk).");
669 } finally {
670 DBUtils.closeResultSet(resultSet);
671 DBUtils.closeStatement(statement);
672 DBUtils.closeConnection(connection);
673 }
674 }
675
676 public Collection getCompanies_withSortSupport_limit(int offset, int rowsToReturn, String sort, String order)
677 throws IllegalArgumentException , DatabaseException {
678 if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
679 return getBeans_withSortSupport_limit_mysql(offset, rowsToReturn, sort, order);
680 } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
681 return getBeans_withSortSupport_limit_noscroll(offset, rowsToReturn, sort, order);
682 }
683 return getBeans_withSortSupport_limit_general(offset, rowsToReturn, sort, order);
684 }
685
686
692
695
696 private Collection getBeans_withSortSupport_limit_mysql(int offset, int rowsToReturn, String sort, String order)
697 throws IllegalArgumentException , DatabaseException {
698 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed.");
699 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed.");
700
704 if ((!sort.equals("CompanyID")) &&
705 (!sort.equals("CompanyName")) &&
706 (!sort.equals("CompanyAddress")) &&
707 (!sort.equals("CompanyCreationDate"))) {
708 throw new IllegalArgumentException ("Cannot sort, reason: don't understand the criteria '" + sort + "'.");
709 }
710
711 if ((!order.equals("ASC")) &&
712 (!order.equals("DESC")) ) {
713 throw new IllegalArgumentException ("Cannot sort, reason: don't understand the order '" + order + "'.");
714 }
715
716 Connection connection = null;
717 PreparedStatement statement = null;
718 ResultSet resultSet = null;
719 Collection retValue = new ArrayList();
720 StringBuffer sql = new StringBuffer (512);
721 sql.append("SELECT *");
722 sql.append(" FROM " + TABLE_NAME);
723 sql.append(" ORDER BY " + sort + " " + order); if (rowsToReturn != 1) {
725 sql.append(" LIMIT ?, ?");
726 }
727
728 try {
729 connection = DBUtils.getConnection();
730 statement = connection.prepareStatement(sql.toString());
731 if (rowsToReturn != 1) {
732 statement.setInt(1, offset);
733 statement.setInt(2, rowsToReturn);
734 }
735 resultSet = statement.executeQuery();
736 while (resultSet.next()) {
737 CompanyBean bean = new CompanyBean();
738 bean.setCompanyID(resultSet.getInt("CompanyID"));
739 bean.setGroupID(resultSet.getInt("GroupID"));
740 bean.setCompanyName(resultSet.getString("CompanyName"));
741 bean.setCompanyAddress(resultSet.getString("CompanyAddress"));
742 bean.setCompanyCity(resultSet.getString("CompanyCity"));
743 bean.setCompanyCAP(resultSet.getString("CompanyCAP"));
744 bean.setCompanyProvince(resultSet.getString("CompanyProvince"));
745 bean.setCompanyRegion(resultSet.getString("CompanyRegion"));
746 bean.setCompanyPhone(resultSet.getString("CompanyPhone"));
747 bean.setCompanyFax(resultSet.getString("CompanyFax"));
748 bean.setCompanyWebsite(resultSet.getString("CompanyWebsite"));
749 bean.setCompanyEmail(resultSet.getString("CompanyEmail"));
750 bean.setCompanySpaceName(resultSet.getString("CompanySpaceName"));
751 bean.setCompanySpaceHeader(resultSet.getString("CompanySpaceHeader"));
752 bean.setCompanySpaceFooter(resultSet.getString("CompanySpaceFooter"));
753 bean.setCompanyVATNumber(resultSet.getString("CompanyVATNumber"));
754 bean.setCompanyLogo(resultSet.getString("CompanyLogo"));
755 bean.setCompanyCss(resultSet.getString("CompanyCss"));
756 bean.setCompanyCreationDate(resultSet.getTimestamp("CompanyCreationDate"));
757 bean.setCompanyModifiedDate(resultSet.getTimestamp("CompanyModifiedDate"));
758 retValue.add(bean);
759 }
760 return retValue;
761 } catch(SQLException sqle) {
762 log.error("Sql Execution Error!", sqle);
763 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getBeans_withSortSupport_limit_mysql.");
764 } finally {
765 DBUtils.closeResultSet(resultSet);
766 DBUtils.closeStatement(statement);
767 DBUtils.closeConnection(connection);
768 }
769 }
770
771
777
780
781 private Collection getBeans_withSortSupport_limit_noscroll(int offset, int rowsToReturn, String sort, String order)
782 throws IllegalArgumentException , DatabaseException {
783 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed.");
784 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed.");
785
789 if ((!sort.equals("CompanyID")) &&
790 (!sort.equals("CompanyName")) &&
791 (!sort.equals("CompanyAddress")) &&
792 (!sort.equals("CompanyCreationDate"))) {
793 throw new IllegalArgumentException ("Cannot sort, reason: don't understand the criteria '" + sort + "'.");
794 }
795
796 if ((!order.equals("ASC")) &&
797 (!order.equals("DESC")) ) {
798 throw new IllegalArgumentException ("Cannot sort, reason: don't understand the order '" + order + "'.");
799 }
800
801 Connection connection = null;
802 PreparedStatement statement = null;
803 ResultSet resultSet = null;
804 Collection retValue = new ArrayList();
805 StringBuffer sql = new StringBuffer (512);
806 sql.append("SELECT *");
807 sql.append(" FROM " + TABLE_NAME);
808 sql.append(" ORDER BY " + sort + " " + order);
810 try {
811 connection = DBUtils.getConnection();
812 statement = connection.prepareStatement(sql.toString());
813 if (rowsToReturn != 1) {
814 statement.setMaxRows(offset + rowsToReturn);
815 }
816 resultSet = statement.executeQuery();
817 int rowIndex = -1;
818 while (resultSet.next()) {
819 rowIndex++;
820 if(rowsToReturn !=1) {
821 if (rowIndex < offset) continue;
822 }
823 CompanyBean bean = new CompanyBean();
824 bean.setCompanyID(resultSet.getInt("CompanyID"));
825 bean.setGroupID(resultSet.getInt("GroupID"));
826 bean.setCompanyName(resultSet.getString("CompanyName"));
827 bean.setCompanyAddress(resultSet.getString("CompanyAddress"));
828 bean.setCompanyCity(resultSet.getString("CompanyCity"));
829 bean.setCompanyCAP(resultSet.getString("CompanyCAP"));
830 bean.setCompanyProvince(resultSet.getString("CompanyProvince"));
831 bean.setCompanyRegion(resultSet.getString("CompanyRegion"));
832 bean.setCompanyPhone(resultSet.getString("CompanyPhone"));
833 bean.setCompanyFax(resultSet.getString("CompanyFax"));
834 bean.setCompanyWebsite(resultSet.getString("CompanyWebsite"));
835 bean.setCompanyEmail(resultSet.getString("CompanyEmail"));
836 bean.setCompanySpaceName(resultSet.getString("CompanySpaceName"));
837 bean.setCompanySpaceHeader(resultSet.getString("CompanySpaceHeader"));
838 bean.setCompanySpaceFooter(resultSet.getString("CompanySpaceFooter"));
839 bean.setCompanyVATNumber(resultSet.getString("CompanyVATNumber"));
840 bean.setCompanyLogo(resultSet.getString("CompanyLogo"));
841 bean.setCompanyCss(resultSet.getString("CompanyCss"));
842 bean.setCompanyCreationDate(resultSet.getTimestamp("CompanyCreationDate"));
843 bean.setCompanyModifiedDate(resultSet.getTimestamp("CompanyModifiedDate"));
844 retValue.add(bean);
845 if (retValue.size() == rowsToReturn) break; }
847 return retValue;
848 } catch(SQLException sqle) {
849 log.error("Sql Execution Error!", sqle);
850 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getBeans_withSortSupport_limit_noscroll.");
851 } finally {
852 DBUtils.closeResultSet(resultSet);
853 DBUtils.resetStatement(statement);
854 DBUtils.closeStatement(statement);
855 DBUtils.closeConnection(connection);
856 }
857 }
858
859
865
868
869
870 private Collection getBeans_withSortSupport_limit_general(int offset, int rowsToReturn, String sort, String order)
871 throws IllegalArgumentException , DatabaseException {
872 if (offset < 0) throw new IllegalArgumentException ("The offset < 0 is not allowed.");
873 if (rowsToReturn <= 0) throw new IllegalArgumentException ("The rowsToReturn <= 0 is not allowed.");
874
878 if ((!sort.equals("CompanyID")) &&
879 (!sort.equals("CompanyName")) &&
880 (!sort.equals("CompanyAddress")) &&
881 (!sort.equals("CompanyCreationDate"))) {
882 throw new IllegalArgumentException ("Cannot sort, reason: don't understand the criteria '" + sort + "'.");
883 }
884
885 if ((!order.equals("ASC")) &&
886 (!order.equals("DESC")) ) {
887 throw new IllegalArgumentException ("Cannot sort, reason: don't understand the order '" + order + "'.");
888 }
889
890 Connection connection = null;
891 PreparedStatement statement = null;
892 ResultSet resultSet = null;
893 Collection retValue = new ArrayList();
894 StringBuffer sql = new StringBuffer (512);
895 sql.append("SELECT *");
896 sql.append(" FROM " + TABLE_NAME);
897 sql.append(" ORDER BY " + sort + " " + order);
899 try {
900 connection = DBUtils.getConnection();
901 statement = connection.prepareStatement(sql.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
902 if (rowsToReturn != 1) {
903 statement.setMaxRows(offset + rowsToReturn);
904 }
905 try {
906 statement.setFetchSize(rowsToReturn);
907 } catch (SQLException sqle) {
908 }
910 resultSet = statement.executeQuery();
911 boolean loop = resultSet.absolute(offset + 1); while (loop) {
913 CompanyBean bean = new CompanyBean();
914 bean.setCompanyID(resultSet.getInt("CompanyID"));
915 bean.setGroupID(resultSet.getInt("GroupID"));
916 bean.setCompanyName(resultSet.getString("CompanyName"));
917 bean.setCompanyAddress(resultSet.getString("CompanyAddress"));
918 bean.setCompanyCity(resultSet.getString("CompanyCity"));
919 bean.setCompanyCAP(resultSet.getString("CompanyCAP"));
920 bean.setCompanyProvince(resultSet.getString("CompanyProvince"));
921 bean.setCompanyRegion(resultSet.getString("CompanyRegion"));
922 bean.setCompanyPhone(resultSet.getString("CompanyPhone"));
923 bean.setCompanyFax(resultSet.getString("CompanyFax"));
924 bean.setCompanyWebsite(resultSet.getString("CompanyWebsite"));
925 bean.setCompanyEmail(resultSet.getString("CompanyEmail"));
926 bean.setCompanySpaceName(resultSet.getString("CompanySpaceName"));
927 bean.setCompanySpaceHeader(resultSet.getString("CompanySpaceHeader"));
928 bean.setCompanySpaceFooter(resultSet.getString("CompanySpaceFooter"));
929 bean.setCompanyVATNumber(resultSet.getString("CompanyVATNumber"));
930 bean.setCompanyLogo(resultSet.getString("CompanyLogo"));
931 bean.setCompanyCss(resultSet.getString("CompanyCss"));
932 bean.setCompanyCreationDate(resultSet.getTimestamp("CompanyCreationDate"));
933 bean.setCompanyModifiedDate(resultSet.getTimestamp("CompanyModifiedDate"));
934 retValue.add(bean);
935 if(rowsToReturn !=1) {
936 if (retValue.size() == rowsToReturn) break; }
938 loop = resultSet.next();
939 }
940 return retValue;
941 } catch(SQLException sqle) {
942 log.error("Sql Execution Error!", sqle);
943 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getBeans_withSortSupport_limit_general.");
944 } finally {
945 DBUtils.closeResultSet(resultSet);
946 DBUtils.resetStatement(statement);
947 DBUtils.closeStatement(statement);
948 DBUtils.closeConnection(connection);
949 }
950 }
951
952 public int getNumberOfCompanies()
953 throws AssertionException, DatabaseException {
954
955 Connection connection = null;
956 PreparedStatement statement = null;
957 ResultSet resultSet = null;
958 StringBuffer sql = new StringBuffer (512);
959 sql.append("SELECT Count(*)");
960 sql.append(" FROM " + TABLE_NAME);
961 try {
963 connection = DBUtils.getConnection();
964 statement = connection.prepareStatement(sql.toString());
965 resultSet = statement.executeQuery();
966 if (!resultSet.next()) {
967 throw new AssertionException("Assertion in CompanyDAOImplJDBC.getNumberOfCompanies.");
968 }
969 return resultSet.getInt(1);
970 } catch(SQLException sqle) {
971 log.error("Sql Execution Error!", sqle);
972 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getNumberOfCompanies.");
973 } finally {
974 DBUtils.closeResultSet(resultSet);
975 DBUtils.closeStatement(statement);
976 DBUtils.closeConnection(connection);
977 }
978 }
979
980
987 public Collection getCompanies()
988 throws DatabaseException {
989
990 Connection connection = null;
991 PreparedStatement statement = null;
992 ResultSet resultSet = null;
993 Collection retValue = new ArrayList();
994 StringBuffer sql = new StringBuffer (512);
995 sql.append("SELECT CompanyID, GroupID, CompanyName, CompanyAddress, CompanyCity, CompanyCAP, CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax, CompanyWebsite, CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter, CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate");
996 sql.append(" FROM " + TABLE_NAME);
997 try {
1000 connection = DBUtils.getConnection();
1001 statement = connection.prepareStatement(sql.toString());
1002 resultSet = statement.executeQuery();
1003 while (resultSet.next()) {
1004 CompanyBean bean = new CompanyBean();
1005 bean.setCompanyID(resultSet.getInt("CompanyID"));
1006 bean.setGroupID(resultSet.getInt("GroupID"));
1007 bean.setCompanyName(resultSet.getString("CompanyName"));
1008 bean.setCompanyAddress(resultSet.getString("CompanyAddress"));
1009 bean.setCompanyCity(resultSet.getString("CompanyCity"));
1010 bean.setCompanyCAP(resultSet.getString("CompanyCAP"));
1011 bean.setCompanyProvince(resultSet.getString("CompanyProvince"));
1012 bean.setCompanyRegion(resultSet.getString("CompanyRegion"));
1013 bean.setCompanyPhone(resultSet.getString("CompanyPhone"));
1014 bean.setCompanyFax(resultSet.getString("CompanyFax"));
1015 bean.setCompanyWebsite(resultSet.getString("CompanyWebsite"));
1016 bean.setCompanyEmail(resultSet.getString("CompanyEmail"));
1017 bean.setCompanySpaceName(resultSet.getString("CompanySpaceName"));
1018 bean.setCompanySpaceHeader(resultSet.getString("CompanySpaceHeader"));
1019 bean.setCompanySpaceFooter(resultSet.getString("CompanySpaceFooter"));
1020 bean.setCompanyVATNumber(resultSet.getString("CompanyVATNumber"));
1021 bean.setCompanyLogo(resultSet.getString("CompanyLogo"));
1022 bean.setCompanyCss(resultSet.getString("CompanyCss"));
1023 bean.setCompanyCreationDate(resultSet.getTimestamp("CompanyCreationDate"));
1024 bean.setCompanyModifiedDate(resultSet.getTimestamp("CompanyModifiedDate"));
1025 retValue.add(bean);
1026 }
1027 return retValue;
1028 } catch(SQLException sqle) {
1029 log.error("Sql Execution Error!", sqle);
1030 throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompanies.");
1031 } finally {
1032 DBUtils.closeResultSet(resultSet);
1033 DBUtils.closeStatement(statement);
1034 DBUtils.closeConnection(connection);
1035 }
1036 }
1037
1038} |
Popular Tags |
Java API By Example, From Geeks To Geeks.