|
1
16
17 package info.jtrac.hibernate;
18
19 import info.jtrac.JtracDao;
20 import info.jtrac.domain.AbstractItem;
21 import info.jtrac.domain.Attachment;
22 import info.jtrac.domain.Config;
23 import info.jtrac.domain.Field;
24 import info.jtrac.domain.Item;
25 import info.jtrac.domain.ItemSearch;
26 import info.jtrac.domain.Metadata;
27 import info.jtrac.domain.Space;
28 import info.jtrac.domain.SpaceSequence;
29 import info.jtrac.domain.State;
30 import info.jtrac.domain.User;
31 import info.jtrac.domain.Counts;
32 import info.jtrac.domain.CountsHolder;
33 import info.jtrac.domain.History;
34 import info.jtrac.domain.ItemItem;
35 import info.jtrac.domain.UserSpaceRole;
36 import java.util.Collection  ;
37
38 import java.util.List ;
39
40 import org.apache.commons.logging.Log;
41 import org.apache.commons.logging.LogFactory;
42 import org.hibernate.Criteria;
43 import org.hibernate.FetchMode;
44 import org.hibernate.Session;
45 import org.hibernate.criterion.DetachedCriteria;
46 import org.hibernate.criterion.Order;
47 import org.hibernate.criterion.Projections;
48 import org.hibernate.criterion.Restrictions;
49 import org.springframework.orm.hibernate3.HibernateCallback;
50 import org.springframework.orm.hibernate3.HibernateJdbcException;
51 import org.springframework.orm.hibernate3.HibernateTemplate;
52 import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
53
54
58 public class HibernateJtracDao extends HibernateDaoSupport implements JtracDao {
59
60 private final Log logger = LogFactory.getLog(getClass());
61
62 private SchemaHelper schemaHelper;
63
64 public void setSchemaHelper(SchemaHelper schemaHelper) {
65 this.schemaHelper = schemaHelper;
66 }
67
68 public void storeItem(Item item) {
69 getHibernateTemplate().merge(item);
70 }
71
72 public Item loadItem(long id) {
73 return (Item) getHibernateTemplate().get(Item.class, id);
74 }
75
76 public History loadHistory(long id) {
77 return (History) getHibernateTemplate().get(History.class, id);
78 }
79
80 public List <Item> findItems(long sequenceNum, String prefixCode) {
81 Object [] params = new Object [] { sequenceNum, prefixCode };
82 return getHibernateTemplate().find("from Item item where item.sequenceNum = ? and item.space.prefixCode = ?", params);
83 }
84
85 public List <Item> findItems(ItemSearch itemSearch) {
86 int pageSize = itemSearch.getPageSize();
87 if (pageSize == -1) {
88 List <Item> list = getHibernateTemplate().findByCriteria(itemSearch.getCriteria());
89 itemSearch.setResultCount(list.size());
90 return list;
91 } else {
92 int firstResult = pageSize * itemSearch.getCurrentPage();
94 List <Item> list = getHibernateTemplate().findByCriteria(itemSearch.getCriteria(), firstResult, pageSize);
95 DetachedCriteria criteria = itemSearch.getCriteriaForCount();
96 criteria.setProjection(Projections.rowCount());
97 Integer count = (Integer ) getHibernateTemplate().findByCriteria(criteria).get(0);
98 itemSearch.setResultCount(count);
99 return list;
100 }
101 }
102
103 public List <AbstractItem> findAllItems() {
104 return getHibernateTemplate().loadAll(AbstractItem.class);
105 }
106
107 public void removeItem(Item item) {
108 getHibernateTemplate().delete(item);
109 }
110
111 public void removeItemItem(ItemItem itemItem) {
112 getHibernateTemplate().delete(itemItem);
113 }
114
115 public void storeAttachment(Attachment attachment) {
116 getHibernateTemplate().merge(attachment);
117 }
118
119 public void storeMetadata(Metadata metadata) {
120 getHibernateTemplate().merge(metadata);
121 }
122
123 public Metadata loadMetadata(long id) {
124 return (Metadata) getHibernateTemplate().get(Metadata.class, id);
125 }
126
127 public void storeSpace(Space space) {
128 getHibernateTemplate().merge(space);
129 }
130
131 public Space loadSpace(long id) {
132 return (Space) getHibernateTemplate().get(Space.class, id);
133 }
134
135 public UserSpaceRole loadUserSpaceRole(long id) {
136 return (UserSpaceRole) getHibernateTemplate().get(UserSpaceRole.class, id);
137 }
138
139 public SpaceSequence loadSpaceSequence(long id) {
140 return (SpaceSequence) getHibernateTemplate().get(SpaceSequence.class, id);
141 }
142
143 public void storeSpaceSequence(SpaceSequence spaceSequence) {
144 getHibernateTemplate().merge(spaceSequence);
145 getHibernateTemplate().flush();
148 }
149
150 public List <Space> findSpacesByPrefixCode(String prefixCode) {
151 return getHibernateTemplate().find("from Space space where space.prefixCode = ?", prefixCode);
152 }
153
154 public List <Space> findAllSpaces() {
155 return getHibernateTemplate().find("from Space space order by space.prefixCode");
156 }
157
158 public List <Space> findSpacesWhereGuestAllowed() {
159 return getHibernateTemplate().find("from Space space where space.guestAllowed = true");
160 }
161
162 public void removeSpace(Space space) {
163 getHibernateTemplate().delete(space);
164 }
165
166 public void storeUser(User user) {
167 getHibernateTemplate().merge(user);
168 }
169
170 public User loadUser(long id) {
171 return (User) getHibernateTemplate().get(User.class, id);
172 }
173
174 public List <User> findAllUsers() {
175 return getHibernateTemplate().find("from User user order by user.name");
176 }
177
178 public List <User> findUsersByLoginName(final String loginName) {
179 return (List <User>) getHibernateTemplate().execute(new HibernateCallback() {
181 public Object doInHibernate(Session session) {
182 Criteria criteria = session.createCriteria(User.class);
183 criteria.setFetchMode("userSpaceRoles", FetchMode.JOIN);
184 criteria.add(Restrictions.eq("loginName", loginName));
185 return criteria.list();
186 }
187 });
188 }
189
190 public List <User> findUsersByEmail(final String email) {
191 return (List <User>) getHibernateTemplate().execute(new HibernateCallback() {
193 public Object doInHibernate(Session session) {
194 Criteria criteria = session.createCriteria(User.class);
195 criteria.setFetchMode("userSpaceRoles", FetchMode.JOIN);
196 criteria.add(Restrictions.eq("email", email));
197 return criteria.list();
198 }
199 });
200 }
201
202 public List <UserSpaceRole> findUserRolesForSpace(final long spaceId) {
203 return (List <UserSpaceRole>) getHibernateTemplate().execute(new HibernateCallback() {
209 public Object doInHibernate(Session session) {
210 Criteria criteria = session.createCriteria(UserSpaceRole.class);
211 criteria.setFetchMode("user", FetchMode.JOIN);
212 criteria.add(Restrictions.eq("space.id", spaceId));
213 return criteria.list();
214 }
215 });
216 }
217
218 public List <User> findUsersWithRoleForSpace(long spaceId, String roleKey) {
219 return getHibernateTemplate().find("from User user"
220 + " join user.userSpaceRoles as usr where usr.space.id = ?"
221 + " and usr.roleKey = ? order by user.name", new Object [] { spaceId, roleKey });
222 }
223
224
226 public CountsHolder loadCountsForUser(User user) {
227 Collection <Space> spaces = user.getSpaces();
228 if (spaces.size() == 0) {
229 return null;
230 }
231 StringBuffer sb = new StringBuffer ();
232 sb.append('(');
233 Space space = null; for (Space s : spaces) {
235 sb.append(s.getId());
236 sb.append(',');
237 space = s;
238 }
239 sb.setCharAt(sb.length() - 1, ')');
240 CountsHolder ch = new CountsHolder();
241 HibernateTemplate ht = getHibernateTemplate();
242 List <Object []> loggedByList = ht.find("select item.space.id, count(item) from Item item"
243 + " where item.loggedBy.id = ? group by item.space.id", user.getId());
244 List <Object []> assignedToList = ht.find("select item.space.id, count(item) from Item item"
245 + " where item.assignedTo.id = ? group by item.space.id", user.getId());
246 List <Object []> statusList = ht.find("select item.space.id, count(item) from Item item"
247 + " where item.space.id in " + sb.toString() + " group by item.space.id");
248 for(Object [] oa : loggedByList) {
249 ch.add((Long ) oa[0], Counts.LOGGED_BY_ME, 0, (Integer ) oa[1]);
250 }
251 for(Object [] oa : assignedToList) {
252 ch.add((Long ) oa[0], Counts.ASSIGNED_TO_ME, 0, (Integer ) oa[1]);
253 }
254 for(Object [] oa : statusList) {
255 ch.add((Long ) oa[0], Counts.TOTAL, 0, (Integer ) oa[1]);
256 }
257 return ch;
258 }
259
260 public Counts loadCountsForUserSpace(User user, Space space) {
261 HibernateTemplate ht = getHibernateTemplate();
262 List <Object []> loggedByList = ht.find("select status, count(item) from Item item"
263 + " where item.loggedBy.id = ? and item.space.id = ? group by item.status", new Object [] { user.getId(), space.getId() });
264 List <Object []> assignedToList = ht.find("select status, count(item) from Item item"
265 + " where item.assignedTo.id = ? and item.space.id = ? group by item.status", new Object [] { user.getId(), space.getId() });
266 List <Object []> statusList = ht.find("select status, count(item) from Item item"
267 + " where item.space.id = ? group by item.status", space.getId());
268 Counts c = new Counts();
269 for(Object [] oa : loggedByList) {
270 c.add(Counts.LOGGED_BY_ME, (Integer ) oa[0], (Integer ) oa[1]);
271 }
272 for(Object [] oa : assignedToList) {
273 c.add(Counts.ASSIGNED_TO_ME, (Integer ) oa[0], (Integer ) oa[1]);
274 }
275 for(Object [] oa : statusList) {
276 c.add(Counts.TOTAL, (Integer ) oa[0], (Integer ) oa[1]);
277 }
278 return c;
279 }
280
281
283 public List <User> findUsersForSpace(long spaceId) {
284 return getHibernateTemplate().find("select distinct user from User user join user.userSpaceRoles as usr"
285 + " where usr.space.id = ? order by user.name", spaceId);
286 }
287
288 public List <User> findUsersForSpaceSet(Collection <Space> spaces) {
289 Criteria criteria = getSession().createCriteria(User.class);
290 criteria.createCriteria("userSpaceRoles").add(Restrictions.in("space", spaces));
291 criteria.addOrder(Order.asc("name"));
292 return criteria.list();
293 }
294
295 public void removeUserSpaceRole(UserSpaceRole userSpaceRole) {
296 getHibernateTemplate().delete(userSpaceRole);
297 }
298
299 public List <Config> findAllConfig() {
300 return getHibernateTemplate().loadAll(Config.class);
301 }
302
303 public void storeConfig(Config config) {
304 getHibernateTemplate().merge(config);
305 }
306
307 public Config loadConfig(String param) {
308 return (Config) getHibernateTemplate().get(Config.class, param);
309 }
310
311 public int loadCountOfRecordsHavingFieldNotNull(Space space, Field field) {
312 Criteria criteria = getSession().createCriteria(Item.class);
313 criteria.add(Restrictions.eq("space", space));
314 criteria.add(Restrictions.isNotNull(field.getName().toString()));
315 criteria.setProjection(Projections.rowCount());
316 int itemCount = (Integer ) criteria.list().get(0);
317 criteria = getSession().createCriteria(History.class);
320 criteria.createCriteria("parent").add(Restrictions.eq("space", space));
321 criteria.add(Restrictions.isNotNull(field.getName().toString()));
322 criteria.setProjection(Projections.rowCount());
323 return itemCount + (Integer ) criteria.list().get(0);
324 }
325
326 public int bulkUpdateFieldToNull(Space space, Field field) {
327 int itemCount = getHibernateTemplate().bulkUpdate("update Item item set item." + field.getName() + " = null"
328 + " where item.space.id = ?", space.getId());
329 logger.info("no of Item rows where " + field.getName() + " set to null = " + itemCount);
330 int historyCount = getHibernateTemplate().bulkUpdate("update History history set history." + field.getName() + " = null"
331 + " where history.parent in ( from Item item where item.space.id = ? )", space.getId());
332 logger.info("no of History rows where " + field.getName() + " set to null = " + historyCount);
333 return itemCount;
334 }
335
336 public int loadCountOfRecordsHavingFieldWithValue(Space space, Field field, int optionKey) {
337 Criteria criteria = getSession().createCriteria(Item.class);
338 criteria.add(Restrictions.eq("space", space));
339 criteria.add(Restrictions.eq(field.getName().toString(), optionKey));
340 criteria.setProjection(Projections.rowCount());
341 int itemCount = (Integer ) criteria.list().get(0);
342 criteria = getSession().createCriteria(History.class);
345 criteria.createCriteria("parent").add(Restrictions.eq("space", space));
346 criteria.add(Restrictions.eq(field.getName().toString(), optionKey));
347 criteria.setProjection(Projections.rowCount());
348 return itemCount + (Integer ) criteria.list().get(0);
349 }
350
351 public int bulkUpdateFieldToNullForValue(Space space, Field field, int optionKey) {
352 int itemCount = getHibernateTemplate().bulkUpdate("update Item item set item." + field.getName() + " = null"
353 + " where item.space.id = ?"
354 + " and item." + field.getName() + " = ?", new Object [] { space.getId(), optionKey });
355 logger.info("no of Item rows where " + field.getName() + " value '" + optionKey + "' replaced with null = " + itemCount);
356 int historyCount = getHibernateTemplate().bulkUpdate("update History history set history." + field.getName() + " = null"
357 + " where history." + field.getName() + " = ?"
358 + " and history.parent in ( from Item item where item.space.id = ? )", new Object [] { optionKey, space.getId(), });
359 logger.info("no of History rows where " + field.getName() + " value '" + optionKey + "' replaced with null = " + historyCount);
360 return itemCount;
361 }
362
363 public int loadCountOfRecordsHavingStatus(Space space, int status) {
364 Criteria criteria = getSession().createCriteria(Item.class);
365 criteria.add(Restrictions.eq("space", space));
366 criteria.add(Restrictions.eq("status", status));
367 criteria.setProjection(Projections.rowCount());
368 int itemCount = (Integer ) criteria.list().get(0);
369 criteria = getSession().createCriteria(History.class);
372 criteria.createCriteria("parent").add(Restrictions.eq("space", space));
373 criteria.add(Restrictions.eq("status", status));
374 criteria.setProjection(Projections.rowCount());
375 return itemCount + (Integer ) criteria.list().get(0);
376 }
377
378 public int bulkUpdateStatusToOpen(Space space, int status) {
379 int itemCount = getHibernateTemplate().bulkUpdate("update Item item set item.status = " + State.OPEN
380 + " where item.status = ? and item.space.id = ?", new Object [] { status, space.getId() });
381 logger.info("no of Item rows where status changed from " + status + " to " + State.OPEN + " = " + itemCount);
382 int historyCount = getHibernateTemplate().bulkUpdate("update History history set history.status = " + State.OPEN
383 + " where history.status = ?"
384 + " and history.parent in ( from Item item where item.space.id = ? )", new Object [] { status, space.getId() });
385 logger.info("no of History rows where status changed from " + status + " to " + State.OPEN + " = " + historyCount);
386 return itemCount;
387 }
388
389 public int bulkUpdateRenameSpaceRole(Space space, String oldRoleKey, String newRoleKey) {
390 return getHibernateTemplate().bulkUpdate("update UserSpaceRole usr set usr.roleKey = ?"
391 + " where usr.roleKey = ? and usr.space.id = ?", new Object [] { newRoleKey, oldRoleKey, space.getId() });
392 }
393
394 public int bulkUpdateDeleteSpaceRole(Space space, String roleKey) {
395 if (roleKey == null) {
396 return getHibernateTemplate().bulkUpdate("delete UserSpaceRole usr where usr.space.id = ?", space.getId());
397 } else {
398 return getHibernateTemplate().bulkUpdate("delete UserSpaceRole usr"
399 + " where usr.space.id = ? and usr.roleKey = ?", new Object [] { space.getId(), roleKey });
400 }
401 }
402
403 public int bulkUpdateDeleteItemsForSpace(Space space) {
404 int historyCount = getHibernateTemplate().bulkUpdate("delete History history where history.parent in"
405 + " ( from Item item where item.space.id = ? )", space.getId());
406 logger.debug("deleted " + historyCount + " records from history");
407 int itemItemCount = getHibernateTemplate().bulkUpdate("delete ItemItem itemItem where itemItem.item in"
408 + " ( from Item item where item.space.id = ? )", space.getId());
409 logger.debug("deleted " + itemItemCount + " records from item_items");
410 int itemCount = getHibernateTemplate().bulkUpdate("delete Item item where item.space.id = ?", space.getId());
411 logger.debug("deleted " + itemCount + " records from items");
412 return historyCount + itemItemCount + itemCount;
413 }
414
415
417
421 public void createSchema() {
422 try {
423 getHibernateTemplate().find("from Item item where item.id = 1");
424 } catch (HibernateJdbcException e) {
425 logger.warn("database schema not found, proceeding to create");
426 schemaHelper.createSchema();
427 User admin = new User();
428 admin.setLoginName("admin");
429 admin.setName("Admin");
430 admin.setEmail("admin");
431 admin.setPassword("21232f297a57a5a743894a0e4a801fc3");
432 admin.addSpaceWithRole(null, "ROLE_ADMIN");
433 logger.info("inserting default admin user into database");
434 storeUser(admin);
435 logger.info("schema creation complete");
436 return;
437 }
438 logger.info("database schema exists, normal startup");
439 }
440
441
445 public void stopEmbeddedDb() throws Exception {
446 schemaHelper.stopEmbeddedDb();
447 }
448
449 }
450 |
Popular Tags |
Java API By Example, From Geeks To Geeks.