001: /*
002: * Copyright 2002-2005 the original author or authors.
003: *
004: * Licensed under the Apache License, Version 2.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016:
017: package info.jtrac.hibernate;
018:
019: import info.jtrac.JtracDao;
020: import info.jtrac.domain.AbstractItem;
021: import info.jtrac.domain.Attachment;
022: import info.jtrac.domain.Config;
023: import info.jtrac.domain.Field;
024: import info.jtrac.domain.Item;
025: import info.jtrac.domain.ItemSearch;
026: import info.jtrac.domain.Metadata;
027: import info.jtrac.domain.Space;
028: import info.jtrac.domain.SpaceSequence;
029: import info.jtrac.domain.State;
030: import info.jtrac.domain.User;
031: import info.jtrac.domain.Counts;
032: import info.jtrac.domain.CountsHolder;
033: import info.jtrac.domain.History;
034: import info.jtrac.domain.ItemItem;
035: import info.jtrac.domain.UserSpaceRole;
036: import java.util.Collection;
037:
038: import java.util.List;
039: import org.hibernate.Criteria;
040: import org.hibernate.FetchMode;
041: import org.hibernate.Session;
042: import org.hibernate.criterion.DetachedCriteria;
043: import org.hibernate.criterion.MatchMode;
044: import org.hibernate.criterion.Order;
045: import org.hibernate.criterion.Projections;
046: import org.hibernate.criterion.Restrictions;
047: import org.slf4j.Logger;
048: import org.slf4j.LoggerFactory;
049: import org.springframework.orm.hibernate3.HibernateCallback;
050: import org.springframework.orm.hibernate3.HibernateTemplate;
051: import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
052:
053: /**
054: * DAO Implementation using Spring Hibernate template
055: * note usage of the Spring "init-method" and "destroy-method" options
056: */
057: public class HibernateJtracDao extends HibernateDaoSupport implements
058: JtracDao {
059:
060: private final Logger logger = LoggerFactory.getLogger(getClass());
061:
062: private SchemaHelper schemaHelper;
063:
064: public void setSchemaHelper(SchemaHelper schemaHelper) {
065: this .schemaHelper = schemaHelper;
066: }
067:
068: public void storeItem(Item item) {
069: getHibernateTemplate().merge(item);
070: }
071:
072: public Item loadItem(long id) {
073: return (Item) getHibernateTemplate().get(Item.class, id);
074: }
075:
076: public void storeHistory(History history) {
077: getHibernateTemplate().merge(history);
078: }
079:
080: public History loadHistory(long id) {
081: return (History) getHibernateTemplate().get(History.class, id);
082: }
083:
084: public List<Item> findItems(long sequenceNum, String prefixCode) {
085: Object[] params = new Object[] { sequenceNum, prefixCode };
086: return getHibernateTemplate()
087: .find(
088: "from Item item where item.sequenceNum = ? and item.space.prefixCode = ?",
089: params);
090: }
091:
092: public List<Item> findItems(ItemSearch itemSearch) {
093: int pageSize = itemSearch.getPageSize();
094: if (pageSize == -1) {
095: List<Item> list = getHibernateTemplate().findByCriteria(
096: itemSearch.getCriteria());
097: itemSearch.setResultCount(list.size());
098: return list;
099: } else {
100: // pagination
101: int firstResult = pageSize * itemSearch.getCurrentPage();
102: List<Item> list = getHibernateTemplate().findByCriteria(
103: itemSearch.getCriteria(), firstResult, pageSize);
104: DetachedCriteria criteria = itemSearch
105: .getCriteriaForCount();
106: criteria.setProjection(Projections.rowCount());
107: Integer count = (Integer) getHibernateTemplate()
108: .findByCriteria(criteria).get(0);
109: itemSearch.setResultCount(count);
110: return list;
111: }
112: }
113:
114: public List<AbstractItem> findAllItems() {
115: // return getHibernateTemplate().loadAll(AbstractItem.class);
116: return (List<AbstractItem>) getHibernateTemplate().execute(
117: new HibernateCallback() {
118: public Object doInHibernate(Session session) {
119: Criteria criteria = session
120: .createCriteria(AbstractItem.class);
121: criteria.setFetchMode("space", FetchMode.JOIN);
122: return criteria.list();
123: }
124: });
125: }
126:
127: public void removeItem(Item item) {
128: getHibernateTemplate().delete(item);
129: }
130:
131: public void removeItemItem(ItemItem itemItem) {
132: getHibernateTemplate().delete(itemItem);
133: }
134:
135: public void storeAttachment(Attachment attachment) {
136: getHibernateTemplate().merge(attachment);
137: }
138:
139: public void storeMetadata(Metadata metadata) {
140: getHibernateTemplate().merge(metadata);
141: }
142:
143: public Metadata loadMetadata(long id) {
144: return (Metadata) getHibernateTemplate()
145: .get(Metadata.class, id);
146: }
147:
148: public void storeSpace(Space space) {
149: getHibernateTemplate().merge(space);
150: }
151:
152: public Space loadSpace(long id) {
153: return (Space) getHibernateTemplate().get(Space.class, id);
154: }
155:
156: public UserSpaceRole loadUserSpaceRole(long id) {
157: return (UserSpaceRole) getHibernateTemplate().get(
158: UserSpaceRole.class, id);
159: }
160:
161: public SpaceSequence loadSpaceSequence(long id) {
162: return (SpaceSequence) getHibernateTemplate().get(
163: SpaceSequence.class, id);
164: }
165:
166: public void storeSpaceSequence(SpaceSequence spaceSequence) {
167: getHibernateTemplate().saveOrUpdate(spaceSequence);
168: // important to prevent duplicate sequence numbers, see JtracImpl#storeItem()
169: getHibernateTemplate().flush();
170: }
171:
172: public List<Space> findSpacesByPrefixCode(String prefixCode) {
173: return getHibernateTemplate().find(
174: "from Space space where space.prefixCode = ?",
175: prefixCode);
176: }
177:
178: public List<Space> findAllSpaces() {
179: return getHibernateTemplate().find(
180: "from Space space order by space.prefixCode");
181: }
182:
183: public List<Space> findSpacesWhereGuestAllowed() {
184: return getHibernateTemplate()
185: .find(
186: "from Space space join fetch space.metadata where space.guestAllowed = true");
187: }
188:
189: public void removeSpace(Space space) {
190: getHibernateTemplate().delete(space);
191: }
192:
193: public void storeUser(User user) {
194: getHibernateTemplate().merge(user);
195: }
196:
197: public User loadUser(long id) {
198: return (User) getHibernateTemplate().get(User.class, id);
199: }
200:
201: public void removeUser(User user) {
202: getHibernateTemplate().delete(user);
203: }
204:
205: public List<User> findAllUsers() {
206: return getHibernateTemplate().find(
207: "from User user order by user.name");
208: }
209:
210: public List<User> findUsersMatching(final String searchText,
211: final String searchOn) {
212: return (List<User>) getHibernateTemplate().execute(
213: new HibernateCallback() {
214: public Object doInHibernate(Session session) {
215: Criteria criteria = session
216: .createCriteria(User.class);
217: criteria.add(Restrictions.ilike(searchOn,
218: searchText, MatchMode.ANYWHERE));
219: criteria.addOrder(Order.asc("name"));
220: return criteria.list();
221: }
222: });
223: }
224:
225: public List<User> findUsersByLoginName(String loginName) {
226: return getHibernateTemplate().find(
227: "from User user where user.loginName = ?", loginName);
228: }
229:
230: public List<User> findUsersByEmail(String email) {
231: return getHibernateTemplate().find(
232: "from User user where user.email = ?", email);
233: }
234:
235: public List<UserSpaceRole> findUserRolesForSpace(long spaceId) {
236: // join fetch for user object
237: return getHibernateTemplate()
238: .find(
239: "select usr from UserSpaceRole usr join fetch usr.user"
240: + " where usr.space.id = ? order by usr.user.name",
241: spaceId);
242: }
243:
244: public List<User> findUsersWithRoleForSpace(long spaceId,
245: String roleKey) {
246: return getHibernateTemplate()
247: .find(
248: "from User user"
249: + " join user.userSpaceRoles as usr where usr.space.id = ?"
250: + " and usr.roleKey = ? order by user.name",
251: new Object[] { spaceId, roleKey });
252: }
253:
254: public int loadCountOfHistoryInvolvingUser(User user) {
255: Long count = (Long) getHibernateTemplate()
256: .find(
257: "select count(history) from History history where "
258: + " history.loggedBy = ? or history.assignedTo = ?",
259: new Object[] { user, user }).get(0);
260: return count.intValue();
261: }
262:
263: //==========================================================================
264:
265: public CountsHolder loadCountsForUser(User user) {
266: Collection<Space> spaces = user.getSpaces();
267: if (spaces.size() == 0) {
268: return null;
269: }
270: CountsHolder ch = new CountsHolder();
271: HibernateTemplate ht = getHibernateTemplate();
272: List<Object[]> loggedByList = ht
273: .find(
274: "select item.space.id, count(item) from Item item"
275: + " where item.loggedBy.id = ? group by item.space.id",
276: user.getId());
277: List<Object[]> assignedToList = ht
278: .find(
279: "select item.space.id, count(item) from Item item"
280: + " where item.assignedTo.id = ? group by item.space.id",
281: user.getId());
282: List<Object[]> statusList = ht
283: .findByNamedParam(
284: "select item.space.id, count(item) from Item item"
285: + " where item.space in (:spaces) group by item.space.id",
286: "spaces", spaces);
287: for (Object[] oa : loggedByList) {
288: ch.addLoggedByMe((Long) oa[0], (Long) oa[1]);
289: }
290: for (Object[] oa : assignedToList) {
291: ch.addAssignedToMe((Long) oa[0], (Long) oa[1]);
292: }
293: for (Object[] oa : statusList) {
294: ch.addTotal((Long) oa[0], (Long) oa[1]);
295: }
296: return ch;
297: }
298:
299: public Counts loadCountsForUserSpace(User user, Space space) {
300: HibernateTemplate ht = getHibernateTemplate();
301: List<Object[]> loggedByList = ht
302: .find(
303: "select status, count(item) from Item item"
304: + " where item.loggedBy.id = ? and item.space.id = ? group by item.status",
305: new Object[] { user.getId(), space.getId() });
306: List<Object[]> assignedToList = ht
307: .find(
308: "select status, count(item) from Item item"
309: + " where item.assignedTo.id = ? and item.space.id = ? group by item.status",
310: new Object[] { user.getId(), space.getId() });
311: List<Object[]> statusList = ht
312: .find(
313: "select status, count(item) from Item item"
314: + " where item.space.id = ? group by item.status",
315: space.getId());
316: Counts c = new Counts(true);
317: for (Object[] oa : loggedByList) {
318: c.addLoggedByMe((Integer) oa[0], (Long) oa[1]);
319: }
320: for (Object[] oa : assignedToList) {
321: c.addAssignedToMe((Integer) oa[0], (Long) oa[1]);
322: }
323: for (Object[] oa : statusList) {
324: c.addTotal((Integer) oa[0], (Long) oa[1]);
325: }
326: return c;
327: }
328:
329: //==========================================================================
330:
331: public List<User> findUsersForSpace(long spaceId) {
332: return getHibernateTemplate().find(
333: "select distinct u from User u join u.userSpaceRoles usr"
334: + " where usr.space.id = ? order by u.name",
335: spaceId);
336: }
337:
338: public List<User> findUsersForSpaceSet(Collection<Space> spaces) {
339: return getHibernateTemplate()
340: .findByNamedParam(
341: "select u from User u join u.userSpaceRoles usr"
342: + " where usr.space in (:spaces) order by u.name",
343: "spaces", spaces);
344: }
345:
346: public void removeUserSpaceRole(UserSpaceRole userSpaceRole) {
347: getHibernateTemplate().delete(userSpaceRole);
348: }
349:
350: public List<Config> findAllConfig() {
351: return getHibernateTemplate().loadAll(Config.class);
352: }
353:
354: public void storeConfig(Config config) {
355: getHibernateTemplate().merge(config);
356: }
357:
358: public Config loadConfig(String param) {
359: return (Config) getHibernateTemplate().get(Config.class, param);
360: }
361:
362: public int loadCountOfRecordsHavingFieldNotNull(Space space,
363: Field field) {
364: Criteria criteria = getSession().createCriteria(Item.class);
365: criteria.add(Restrictions.eq("space", space));
366: criteria
367: .add(Restrictions.isNotNull(field.getName().toString()));
368: criteria.setProjection(Projections.rowCount());
369: int itemCount = (Integer) criteria.list().get(0);
370: // even when no item has this field not null currently, items may have history with this field not null
371: // because of the "parent" difference, cannot use AbstractItem and have to do a separate Criteria query
372: criteria = getSession().createCriteria(History.class);
373: criteria.createCriteria("parent").add(
374: Restrictions.eq("space", space));
375: criteria
376: .add(Restrictions.isNotNull(field.getName().toString()));
377: criteria.setProjection(Projections.rowCount());
378: return itemCount + (Integer) criteria.list().get(0);
379: }
380:
381: public int bulkUpdateFieldToNull(Space space, Field field) {
382: int itemCount = getHibernateTemplate().bulkUpdate(
383: "update Item item set item." + field.getName()
384: + " = null" + " where item.space.id = ?",
385: space.getId());
386: logger.info("no of Item rows where " + field.getName()
387: + " set to null = " + itemCount);
388: int historyCount = getHibernateTemplate()
389: .bulkUpdate(
390: "update History history set history."
391: + field.getName()
392: + " = null"
393: + " where history.parent in ( from Item item where item.space.id = ? )",
394: space.getId());
395: logger.info("no of History rows where " + field.getName()
396: + " set to null = " + historyCount);
397: return itemCount;
398: }
399:
400: public int loadCountOfRecordsHavingFieldWithValue(Space space,
401: Field field, int optionKey) {
402: Criteria criteria = getSession().createCriteria(Item.class);
403: criteria.add(Restrictions.eq("space", space));
404: criteria.add(Restrictions.eq(field.getName().toString(),
405: optionKey));
406: criteria.setProjection(Projections.rowCount());
407: int itemCount = (Integer) criteria.list().get(0);
408: // even when no item has this field value currently, items may have history with this field value
409: // because of the "parent" difference, cannot use AbstractItem and have to do a separate Criteria query
410: criteria = getSession().createCriteria(History.class);
411: criteria.createCriteria("parent").add(
412: Restrictions.eq("space", space));
413: criteria.add(Restrictions.eq(field.getName().toString(),
414: optionKey));
415: criteria.setProjection(Projections.rowCount());
416: return itemCount + (Integer) criteria.list().get(0);
417: }
418:
419: public int bulkUpdateFieldToNullForValue(Space space, Field field,
420: int optionKey) {
421: int itemCount = getHibernateTemplate().bulkUpdate(
422: "update Item item set item." + field.getName()
423: + " = null" + " where item.space.id = ?"
424: + " and item." + field.getName() + " = ?",
425: new Object[] { space.getId(), optionKey });
426: logger.info("no of Item rows where " + field.getName()
427: + " value '" + optionKey + "' replaced with null = "
428: + itemCount);
429: int historyCount = getHibernateTemplate()
430: .bulkUpdate(
431: "update History history set history."
432: + field.getName()
433: + " = null"
434: + " where history."
435: + field.getName()
436: + " = ?"
437: + " and history.parent in ( from Item item where item.space.id = ? )",
438: new Object[] { optionKey, space.getId() });
439: logger.info("no of History rows where " + field.getName()
440: + " value '" + optionKey + "' replaced with null = "
441: + historyCount);
442: return itemCount;
443: }
444:
445: public int loadCountOfRecordsHavingStatus(Space space, int status) {
446: Criteria criteria = getSession().createCriteria(Item.class);
447: criteria.add(Restrictions.eq("space", space));
448: criteria.add(Restrictions.eq("status", status));
449: criteria.setProjection(Projections.rowCount());
450: int itemCount = (Integer) criteria.list().get(0);
451: // even when no item has this status currently, items may have history with this status
452: // because of the "parent" difference, cannot use AbstractItem and have to do a separate Criteria query
453: criteria = getSession().createCriteria(History.class);
454: criteria.createCriteria("parent").add(
455: Restrictions.eq("space", space));
456: criteria.add(Restrictions.eq("status", status));
457: criteria.setProjection(Projections.rowCount());
458: return itemCount + (Integer) criteria.list().get(0);
459: }
460:
461: public int bulkUpdateStatusToOpen(Space space, int status) {
462: int itemCount = getHibernateTemplate()
463: .bulkUpdate(
464: "update Item item set item.status = "
465: + State.OPEN
466: + " where item.status = ? and item.space.id = ?",
467: new Object[] { status, space.getId() });
468: logger.info("no of Item rows where status changed from "
469: + status + " to " + State.OPEN + " = " + itemCount);
470: int historyCount = getHibernateTemplate()
471: .bulkUpdate(
472: "update History history set history.status = "
473: + State.OPEN
474: + " where history.status = ?"
475: + " and history.parent in ( from Item item where item.space.id = ? )",
476: new Object[] { status, space.getId() });
477: logger.info("no of History rows where status changed from "
478: + status + " to " + State.OPEN + " = " + historyCount);
479: return itemCount;
480: }
481:
482: public int bulkUpdateRenameSpaceRole(Space space,
483: String oldRoleKey, String newRoleKey) {
484: return getHibernateTemplate()
485: .bulkUpdate(
486: "update UserSpaceRole usr set usr.roleKey = ?"
487: + " where usr.roleKey = ? and usr.space.id = ?",
488: new Object[] { newRoleKey, oldRoleKey,
489: space.getId() });
490: }
491:
492: public int bulkUpdateDeleteSpaceRole(Space space, String roleKey) {
493: if (roleKey == null) {
494: return getHibernateTemplate().bulkUpdate(
495: "delete UserSpaceRole usr where usr.space.id = ?",
496: space.getId());
497: } else {
498: return getHibernateTemplate()
499: .bulkUpdate(
500: "delete UserSpaceRole usr"
501: + " where usr.space.id = ? and usr.roleKey = ?",
502: new Object[] { space.getId(), roleKey });
503: }
504: }
505:
506: public int bulkUpdateDeleteItemsForSpace(Space space) {
507: int historyCount = getHibernateTemplate()
508: .bulkUpdate(
509: "delete History history where history.parent in"
510: + " ( from Item item where item.space.id = ? )",
511: space.getId());
512: logger.debug("deleted " + historyCount
513: + " records from history");
514: int itemItemCount = getHibernateTemplate()
515: .bulkUpdate(
516: "delete ItemItem itemItem where itemItem.item in"
517: + " ( from Item item where item.space.id = ? )",
518: space.getId());
519: logger.debug("deleted " + itemItemCount
520: + " records from item_items");
521: int itemCount = getHibernateTemplate().bulkUpdate(
522: "delete Item item where item.space.id = ?",
523: space.getId());
524: logger.debug("deleted " + itemCount + " records from items");
525: return historyCount + itemItemCount + itemCount;
526: }
527:
528: //==========================================================================
529:
530: /**
531: * note that this is automatically configured to run on startup
532: * as a spring bean "init-method"
533: */
534: public void createSchema() {
535: try {
536: getHibernateTemplate().find(
537: "from Item item where item.id = 1");
538: } catch (Exception e) {
539: logger
540: .warn("expected database schema does not exist, will create. Error is: "
541: + e.getMessage());
542: schemaHelper.createSchema();
543: User admin = new User();
544: admin.setLoginName("admin");
545: admin.setName("Admin");
546: admin.setEmail("admin");
547: admin.setPassword("21232f297a57a5a743894a0e4a801fc3");
548: admin.addSpaceWithRole(null, "ROLE_ADMIN");
549: logger.info("inserting default admin user into database");
550: storeUser(admin);
551: logger.info("schema creation complete");
552: return;
553: }
554: logger.info("database schema exists, normal startup");
555: }
556:
557: }
|