1 package ejava.jpa.examples.tuning.dao;
2
3 import java.text.SimpleDateFormat;
4 import java.util.Collection;
5 import java.util.Date;
6 import java.util.HashMap;
7 import java.util.HashSet;
8 import java.util.List;
9 import java.util.Map;
10 import java.util.Map.Entry;
11
12
13 import javax.persistence.EntityManager;
14 import javax.persistence.TemporalType;
15 import javax.persistence.TypedQuery;
16 import javax.persistence.criteria.AbstractQuery;
17 import javax.persistence.criteria.CriteriaBuilder;
18 import javax.persistence.criteria.CriteriaQuery;
19 import javax.persistence.criteria.Join;
20 import javax.persistence.criteria.JoinType;
21 import javax.persistence.criteria.Path;
22 import javax.persistence.criteria.Root;
23 import javax.persistence.criteria.Subquery;
24
25 import org.slf4j.Logger;
26 import org.slf4j.LoggerFactory;
27
28 import ejava.jpa.examples.tuning.bo.Actor;
29 import ejava.jpa.examples.tuning.bo.Movie;
30 import ejava.jpa.examples.tuning.bo.MovieRating;
31 import ejava.jpa.examples.tuning.bo.MovieRole;
32 import ejava.jpa.examples.tuning.bo.Person;
33
34 public class MovieDAOImpl {
35 private static final Logger log = LoggerFactory.getLogger(MovieDAOImpl.class);
36 private EntityManager em;
37
38 public void setEntityManager(EntityManager em) {
39 this.em = em;
40 }
41
42 private class DateParam {
43 public final Date date;
44 public final TemporalType ttype;
45 public DateParam(Date date, TemporalType ttype) {
46 this.date = date;
47 this.ttype = ttype;
48 }
49 @Override
50 public String toString() {
51 if (date==null) { return null; }
52 switch (ttype) {
53 case DATE:
54 return new SimpleDateFormat("yyyy-MM-dd").format(date);
55 case TIME:
56 return new SimpleDateFormat("HH:mm:ss.SSS").format(date);
57 case TIMESTAMP:
58 return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(date);
59 }
60 return null;
61 }
62 }
63 private class QueryLogger<T> {
64 private Class<T> resultType;
65 private String jpaql;
66 private String orderBy;
67 private Integer offset;
68 private Integer limit;
69 private Map<String, Object> params = new HashMap<String, Object>();
70
71 public QueryLogger(String jpaql, Class<T> resultType) {
72 this.resultType = resultType;
73 this.jpaql = jpaql;
74 }
75 public QueryLogger<T> setParameter(String key, Object value) {
76 params.put(key, value);
77 return this;
78 }
79 public QueryLogger<T> setParameter(String key, Date value, TemporalType ttype) {
80 params.put(key, new DateParam(value, ttype));
81 return this;
82 }
83 public QueryLogger<T> setFirstResult(int offset) {
84 this.offset = offset;
85 return this;
86 }
87 public QueryLogger<T> setMaxResults(int limit) {
88 this.limit=limit;
89 return this;
90 }
91 public void setOrderBy(String orderBy) {
92 this.orderBy = orderBy;
93 }
94 public T getSingleResult() {
95 log.info(toString());
96 return makeQuery().getSingleResult();
97 }
98 public List<T> getResultList() {
99 log.info(toString());
100 return makeQuery().getResultList();
101 }
102 protected TypedQuery<T> makeQuery() {
103 String queryString = orderBy==null ? jpaql : jpaql + " order by " + orderBy;
104 TypedQuery<T> query = em.createQuery(queryString, resultType);
105 for (Entry<String, Object> param: params.entrySet()) {
106 if (param.getValue() instanceof DateParam) {
107 DateParam dparam = (DateParam)param.getValue();
108 query.setParameter(param.getKey(), dparam.date, dparam.ttype);
109 } else {
110 query.setParameter(param.getKey(), param.getValue());
111 }
112 }
113 if (offset != null) {
114 query.setFirstResult(offset);
115 }
116 if (limit != null) {
117 query.setMaxResults(limit);
118 }
119 return query;
120 }
121 public String toString() {
122 StringBuilder text = new StringBuilder();
123 text.append("\"").append(jpaql);
124 if (orderBy != null) {
125 text.append(" order by ").append(orderBy);
126 }
127 text.append("\"");
128 if (!params.isEmpty()) {
129 text.append(", params=").append(params);
130 }
131 if (offset != null) {
132 text.append(", offset=").append(offset);
133 }
134 if (limit != null) {
135 text.append(", limit=").append(limit);
136 }
137 return text.toString();
138 }
139 }
140
141
142
143
144
145
146
147 protected <T> QueryLogger<T> createQuery(String jpaql, Class<T> resultClass) {
148 return new QueryLogger<T>(jpaql, resultClass);
149 }
150
151
152
153
154
155
156
157
158 protected <T> QueryLogger<T> withPaging(QueryLogger<T> query, Integer offset, Integer limit, String orderBy) {
159 if (offset != null && offset > 0) {
160 query.setFirstResult(offset);
161 }
162 if (limit != null && limit > 0) {
163 query.setMaxResults(limit);
164 }
165 if (orderBy != null) {
166 query.setOrderBy(orderBy);
167 }
168 return query;
169 }
170
171
172
173
174
175
176 public Person getKevinBacon() {
177 return createQuery(
178 "select r.actor.person " +
179 "from MovieRole r " +
180 "where r.movie.title = 'Tremors' and " +
181 "r.actor.person.lastName='Bacon' and " +
182 "r.actor.person.firstName='Kevin'", Person.class)
183 .getSingleResult();
184 }
185
186 @SuppressWarnings("unchecked")
187 public List<String> getKevinBaconMovieIds(Integer offset, Integer limit, String orderBy) {
188 TypedQuery<String> query = (TypedQuery<String>) em.createNativeQuery(
189 String.format(
190 "select m.id from JPATUNE_MOVIE m " +
191 "join JPATUNE_MOVIEROLE role on role.movie_id = m.id " +
192 "join JPATUNE_ACTOR a on a.person_id = role.actor_id " +
193 "join JPATUNE_PERSON p on p.id = a.person_id " +
194 "where p.last_name='Bacon' and p.first_name='Kevin' and m.plot is not null " +
195 "%s", orderBy==null ? "" : " order by " + orderBy)
196 );
197 if (offset!=null) {
198 query.setFirstResult(offset);
199 }
200 if (limit!=null) {
201 query.setMaxResults(limit);
202 }
203 return query.getResultList();
204 }
205
206
207
208
209
210
211
212
213 public List<Person> oneStepFromPerson0(Person p, Integer offset, Integer limit) {
214 return withPaging(createQuery(
215 "select a.person from Actor a " +
216 "join a.roles ar " +
217 "join a.person ap " +
218 "where ar.movie in (select m from Movie m " +
219 "inner join m.cast mr " +
220 "inner join mr.actor ma " +
221 "inner join ma.person mp " +
222 "where mp.id = :id))" +
223 "and ap.id not = :id", Person.class)
224 .setParameter("id", p.getId()), offset, limit, null)
225 .getResultList();
226 }
227
228
229
230
231
232
233
234
235
236 public Collection<Person> oneStepFromPersonByDAO(Person p) {
237 Collection<Person> result = new HashSet<Person>();
238
239 List<String> movieIds = createQuery(
240 "select role.movie.id from MovieRole role " +
241 "where role.actor.person.id=:personId", String.class)
242 .setParameter("personId", p.getId())
243 .getResultList();
244
245
246 for (String mid: movieIds) {
247 List<Person> people = createQuery(
248 "select role.actor.person from MovieRole role " +
249 "where role.movie.id=:movieId", Person.class)
250 .setParameter("movieId", mid)
251 .getResultList();
252 result.addAll(people);
253 }
254 return result;
255 }
256 public Collection<Person> oneStepFromPersonByDAO(Person p, Integer offset, Integer limit, String orderBy) {
257 Collection<Person> result = new HashSet<Person>();
258
259 List<String> movieIds = createQuery(
260 "select role.movie.id from MovieRole role " +
261 "where role.actor.person.id=:personId", String.class)
262 .setParameter("personId", p.getId())
263 .getResultList();
264
265
266 int pos=0;
267 for (String mid: movieIds) {
268 List<Person> people = createQuery(
269 "select role.actor.person from MovieRole role " +
270 "where role.movie.id=:movieId", Person.class)
271 .setParameter("movieId", mid)
272 .getResultList();
273 if (offset==null || (offset!=null && pos+people.size() > offset)) {
274 for(Person pp: people) {
275 if (offset==null || pos>=offset) {
276 result.add(pp);
277 if (limit!=null && result.size() >= limit) { break; }
278 }
279 pos+=1;
280 }
281 } else {
282 pos+=people.size();
283 }
284 if (limit!=null && result.size() >= limit) { break; }
285 }
286 return result;
287 }
288
289
290
291
292
293
294
295
296
297 public List<Person> oneStepFromPersonByDB(Person p) {
298 return createQuery(
299 "select distinct role.actor.person from MovieRole role " +
300 "where role.movie.id in (" +
301 "select m.id from Movie m " +
302 "join m.cast role2 " +
303 "where role2.actor.person.id=:id)", Person.class)
304 .setParameter("id", p.getId())
305 .getResultList();
306 }
307 public List<Person> oneStepFromPersonByDB(Person p, Integer offset, Integer limit, String orderBy) {
308 return withPaging(createQuery(
309 "select distinct role.actor.person from MovieRole role " +
310 "where role.movie.id in (" +
311 "select m.id from Movie m " +
312 "join m.cast role2 " +
313 "where role2.actor.person.id=:id)", Person.class), offset, limit, orderBy)
314 .setParameter("id", p.getId())
315 .getResultList();
316 }
317
318
319
320 protected static class Pair<T,U> {
321 public final T query;
322 public final U queryTerm;
323 public Pair(T first, U second) { this.query=first; this.queryTerm=second; }
324 }
325
326
327 protected Pair<Subquery<Movie>,Void> getMoviesForPerson(AbstractQuery<String> parentQuery, Person person) {
328 CriteriaBuilder cb = em.getCriteriaBuilder();
329 Subquery<Movie> qdef = parentQuery.subquery(Movie.class);
330 Root<Movie> m = qdef.from(Movie.class);
331 Join<Movie, MovieRole> role = m.join("cast", JoinType.INNER);
332 Join<MovieRole, Actor> a = role.join("actor", JoinType.INNER);
333 Join<Actor, Person> p = a.join("person", JoinType.INNER);
334 qdef.select(m)
335 .where(cb.equal(p.get("id"), person.getId()));
336 return new Pair<Subquery<Movie>,Void>(qdef,null);
337 }
338
339
340 protected Pair<Subquery<Movie>, Join<Actor, Person>> getMoviesForPersonIds(AbstractQuery<String> parentQuery) {
341 Subquery<Movie> qdef = parentQuery.subquery(Movie.class);
342 Root<Movie> m = qdef.from(Movie.class);
343 Join<Movie, MovieRole> role = m.join("cast", JoinType.INNER);
344 Join<MovieRole, Actor> a = role.join("actor", JoinType.INNER);
345 Join<Actor, Person> p = a.join("person", JoinType.INNER);
346 qdef.select(m).distinct(true);
347
348
349 return new Pair<Subquery<Movie>,Join<Actor, Person>>(qdef, p);
350 }
351
352
353 protected Pair<Subquery<String>,Join<MovieRole, Movie>> getPersonIdsInMovie(AbstractQuery<?> parentQuery) {
354 Subquery<String> qdef = parentQuery.subquery(String.class);
355 Root<MovieRole> role = qdef.from(MovieRole.class);
356 Join<MovieRole, Movie> m = role.join("movie", JoinType.INNER);
357 Join<MovieRole, Actor> a = role.join("actor", JoinType.INNER);
358 Join<Actor, Person> p = a.join("person", JoinType.INNER);
359 qdef.select(p.<String>get("id")).distinct(true);
360
361
362 return new Pair<Subquery<String>,Join<MovieRole, Movie>>(qdef,m);
363 }
364
365 protected Pair<Subquery<String>,Join<MovieRole, Movie>> nthRemoved(
366 AbstractQuery<String> parentQuery, Path<Movie> parentTerm, Person person) {
367 CriteriaBuilder cb = em.getCriteriaBuilder();
368
369
370 Pair<Subquery<Movie>,Join<Actor, Person>> mq = getMoviesForPersonIds(parentQuery);
371 parentQuery.where(cb.in(parentTerm).value(mq.query));
372
373
374 Pair<Subquery<String>,Join<MovieRole, Movie>> pq = getPersonIdsInMovie(mq.query);
375 mq.query.where(cb.in(mq.queryTerm.get("id")).value(pq.query));
376
377 return pq;
378 }
379
380 protected CriteriaQuery<Person> getPeopleQuery2(Person person, int steps) {
381 CriteriaBuilder cb = em.getCriteriaBuilder();
382 CriteriaQuery<Person> rootq = cb.createQuery(Person.class);
383 Root<Person> p = rootq.from(Person.class);
384 rootq.select(p);
385
386
387 Pair<Subquery<String>,Join<MovieRole, Movie>> associateIds=getPersonIdsInMovie(rootq);
388 rootq.where(cb.in(p.get("id")).value(associateIds.query));
389
390
391 Pair<Subquery<String>,Join<MovieRole, Movie>> nextParent=associateIds;
392 for (int i=0; i<steps-1; i++) {
393 nextParent = nthRemoved(nextParent.query, nextParent.queryTerm, person);
394 }
395
396
397 Pair<Subquery<Movie>, Void> targetMovies = getMoviesForPerson(nextParent.query, person);
398 nextParent.query.where(cb.in(nextParent.queryTerm).value(targetMovies.query));
399
400 return rootq;
401 }
402
403
404
405 protected CriteriaQuery<Person> getPeopleQuery(Person person, int steps) {
406 CriteriaBuilder cb = em.getCriteriaBuilder();
407 CriteriaQuery<Person> rootq = cb.createQuery(Person.class);
408 Root<Person> p = rootq.from(Person.class);
409 rootq.select(p);
410
411
412
413 Pair<Subquery<String>,Join<MovieRole, Movie>> pq3 = getPersonIdsInMovie(rootq);
414 rootq.where(cb.in(p.get("id")).value(pq3.query));
415
416
417 Pair<Subquery<Movie>,Join<Actor, Person>> mq2 = getMoviesForPersonIds(pq3.query);
418 pq3.query.where(cb.in(pq3.queryTerm).value(mq2.query));
419
420
421
422 Pair<Subquery<String>,Join<MovieRole, Movie>> pq2 = getPersonIdsInMovie(mq2.query);
423 mq2.query.where(cb.in(mq2.queryTerm.get("id")).value(pq2.query));
424
425
426 Pair<Subquery<Movie>,Join<Actor, Person>> mq1 = getMoviesForPersonIds(pq2.query);
427 pq2.query.where(cb.in(pq2.queryTerm).value(mq1.query));
428
429
430
431 Pair<Subquery<String>,Join<MovieRole, Movie>> pq1 = getPersonIdsInMovie(mq1.query);
432 mq1.query.where(cb.in(mq1.queryTerm.get("id")).value(pq1.query));
433
434
435 Pair<Subquery<Movie>, Void> tq = getMoviesForPerson(pq1.query, person);
436 pq1.query.where(cb.in(pq1.queryTerm).value(tq.query));
437
438 return rootq;
439 }
440
441 public List<Person> stepsFromPerson(Person person, int steps, Integer offset, Integer limit) {
442 CriteriaQuery<Person> qdef= getPeopleQuery2(person, steps);
443 TypedQuery<Person> query = em.createQuery(qdef);
444 if (offset!=null) { query.setFirstResult(offset); }
445 if (limit!=null) { query.setMaxResults(limit); }
446 return query.getResultList();
447 }
448
449
450
451
452
453
454
455
456
457 public List<Movie> getMovies(Integer offset, Integer limit, String orderBy) {
458 return withPaging(createQuery(
459 "select m from Movie m", Movie.class),
460 offset, limit, orderBy).getResultList();
461 }
462
463
464
465
466
467
468
469
470
471
472 public List<Movie> getMoviesByRatingUpperFunction(MovieRating rating, Integer offset, Integer limit) {
473 return withPaging(createQuery(
474 "select m from Movie m " +
475 "where upper(m.rating) = :rating", Movie.class)
476 .setParameter("rating", rating.mpaa().toUpperCase()),
477 offset, limit, null).getResultList();
478 }
479
480
481
482
483
484
485
486
487
488
489 public List<Movie> getMoviesByRatingLowerFunction(MovieRating rating, Integer offset, Integer limit) {
490 return withPaging(createQuery(
491 "select m from Movie m " +
492 "where lower(m.rating) = :rating", Movie.class)
493 .setParameter("rating", rating.mpaa().toLowerCase()),
494 offset, limit, null).getResultList();
495 }
496
497
498
499
500
501
502
503
504
505
506
507 public List<Movie> getMoviesByRatingValue(MovieRating rating, Integer offset, Integer limit, String orderBy) {
508 return withPaging(createQuery(
509 "select m from Movie m " +
510 "where m.rating = :rating", Movie.class)
511 .setParameter("rating", rating.mpaa()),
512 offset, limit, orderBy).getResultList();
513 }
514
515
516
517
518
519
520
521
522
523
524
525 public List<Movie> getMoviesLikeTitle(String title, Integer offset, Integer limit, String orderBy) {
526 return withPaging(createQuery(
527 "select m from Movie m " +
528 "where m.title like :title", Movie.class)
529 .setParameter("title", title),
530 offset, limit, orderBy).getResultList();
531 }
532
533
534
535
536
537
538
539
540 public List<Movie> getMoviesEqualsTitle(String title, Integer offset, Integer limit) {
541 return withPaging(createQuery(
542 "select m from Movie m " +
543 "where m.title = :title", Movie.class)
544 .setParameter("title", title),
545 offset, limit, null).getResultList();
546 }
547
548
549
550
551
552
553
554
555
556 public List<String> getRatingsByTitle(String title, Integer offset, Integer limit, String orderBy) {
557 return withPaging(createQuery(
558 "select m.rating from Movie m " +
559 "where m.title = :title", String.class)
560 .setParameter("title", title),
561 offset, limit, orderBy).getResultList();
562 }
563
564
565
566
567
568
569
570
571
572 public List<String> getRatingsLikeTitle(String title, Integer offset, Integer limit, String orderBy) {
573 return withPaging(createQuery(
574 "select m.rating from Movie m " +
575 "where m.title like :title", String.class)
576 .setParameter("title", title),
577 offset, limit, orderBy).getResultList();
578 }
579
580
581
582
583
584
585
586
587
588
589 public List<String> getTitlesByRating(MovieRating rating, Integer offset, Integer limit) {
590 return withPaging(createQuery(
591 "select m.title from Movie m " +
592 "where m.rating = :rating", String.class)
593 .setParameter("rating", rating.mpaa()),
594 offset, limit, null).getResultList();
595 }
596
597
598 public List<Movie> getMoviesByTitleAndReleaseDate(String title, Date releaseDate, Integer offset, Integer limit) {
599 return withPaging(createQuery(
600 "select m from Movie m " +
601 "where m.title = :title and m.releaseDate = :releaseDate", Movie.class)
602 .setParameter("title", title)
603 .setParameter("releaseDate", releaseDate, TemporalType.DATE),
604 offset, limit, null).getResultList();
605 }
606
607 public List<Movie> getMoviesByReleaseDateAndTitle(String title, Date releaseDate, Integer offset, Integer limit) {
608 return withPaging(createQuery(
609 "select m from Movie m " +
610 "where m.releaseDate = :releaseDate and m.title = :title", Movie.class)
611 .setParameter("title", title)
612 .setParameter("releaseDate", releaseDate, TemporalType.DATE),
613 offset, limit, null).getResultList();
614 }
615
616 public List<Movie> getMoviesByTitleAndReleaseDateAndRating(String title, Date releaseDate, MovieRating rating, Integer offset, Integer limit) {
617 return withPaging(createQuery(
618 "select m from Movie m " +
619 "where m.title = :title and m.releaseDate = :releaseDate and m.rating=:rating", Movie.class)
620 .setParameter("title", title)
621 .setParameter("releaseDate", releaseDate, TemporalType.DATE)
622 .setParameter("rating", rating.mpaa()),
623 offset, limit, null).getResultList();
624 }
625
626 public List<Movie> getMoviesByReleaseDate(Date releaseDate, Integer offset, Integer limit) {
627 return withPaging(createQuery(
628 "select m from Movie m " +
629 "where m.releaseDate = :releaseDate", Movie.class)
630 .setParameter("releaseDate", releaseDate, TemporalType.DATE),
631 offset, limit, null).getResultList();
632 }
633
634 public List<Movie> getMoviesByReleaseDateAndRating(Date releaseDate, MovieRating rating, Integer offset, Integer limit) {
635 return withPaging(createQuery(
636 "select m from Movie m " +
637 "where m.releaseDate = :releaseDate and m.rating=:rating", Movie.class)
638 .setParameter("releaseDate", releaseDate, TemporalType.DATE)
639 .setParameter("rating", rating.mpaa()),
640 offset, limit, null).getResultList();
641 }
642
643 public List<Movie> getMoviesByRole(String role, Integer offset, Integer limit, String orderBy) {
644 return withPaging(createQuery(
645 "select m from Movie m " +
646 "join m.cast as r " +
647 "where r.role=:role", Movie.class)
648 .setParameter("role", role),
649 offset, limit, orderBy).getResultList();
650 }
651
652 public List<Movie> getMoviesByLikeRole(String role, Integer offset, Integer limit, String orderBy) {
653 return withPaging(createQuery(
654 "select m from Movie m " +
655 "join m.cast as r " +
656 "where r.role like :role", Movie.class)
657 .setParameter("role", role),
658 offset, limit, orderBy).getResultList();
659 }
660
661 public List<MovieRole> getRolesByMovie(String title, Date releaseDate, Integer offset, Integer limit, String orderBy) {
662 return withPaging(createQuery(
663 "select r from MovieRole r " +
664 "join r.movie m " +
665 "where m.title=:title and m.releaseDate=:releaseDate", MovieRole.class)
666 .setParameter("title", title)
667 .setParameter("releaseDate", releaseDate, TemporalType.DATE),
668 offset, limit, orderBy).getResultList();
669 }
670
671 public Movie getMovieById(String id) {
672 return em.find(Movie.class, id);
673 }
674
675 public Movie getMovieByIdUnfetched(String id) {
676 List<Movie> movies=createQuery(
677 String.format("select new %s(m.id, m.minutes, m.rating, m.releaseDate, m.title) ", Movie.class.getName()) +
678 "from Movie m " +
679 "where id=:id", Movie.class)
680 .setParameter("id", id)
681 .getResultList();
682 return movies.isEmpty() ? null : movies.get(0);
683 }
684
685 public Movie getMovieFetchedByIdFetched(String id) {
686 List<Movie> movies = createQuery(
687 "select m from Movie m " +
688 "left join fetch m.genres " +
689 "left join fetch m.director d " +
690 "left join fetch d.person " +
691 "left join fetch m.cast role " +
692 "left join fetch role.actor a " +
693 "left join fetch a.person " +
694 "where m.id=:id", Movie.class)
695 .setParameter("id", id)
696 .getResultList();
697 return movies.isEmpty() ? null : movies.get(0);
698 }
699
700 public int getMovieCastCountByDAORelation(String movieId) {
701 Movie m = em.find(Movie.class, movieId);
702 return m==null ? 0 : m.getCast().size();
703 }
704
705 public int getMovieCastCountByDAO(String movieId) {
706 return createQuery(
707 "select role " +
708 "from Movie m " +
709 "join m.cast role " +
710 "where m.id=:id", MovieRole.class)
711 .setParameter("id", movieId)
712 .getResultList().size();
713 }
714
715 public int getMovieCastCountByDB(String movieId) {
716 return createQuery(
717 "select count(role) " +
718 "from Movie m " +
719 "join m.cast role " +
720 "where m.id=:id", Number.class)
721 .setParameter("id", movieId)
722 .getSingleResult().intValue();
723 }
724
725 public int getCastCountForMovie(String movieId) {
726 return createQuery(
727 "select count(*) " +
728 "from MovieRole role " +
729 "where role.movie.id=:id", Number.class)
730 .setParameter("id", movieId)
731 .getSingleResult().intValue();
732 }
733
734 public List<Object[]> getMovieAndDirector(Integer offset, Integer limit, String orderBy) {
735 return withPaging(createQuery(
736 "select m.title, p.firstName, p.lastName " +
737 "from Movie m " +
738 "join m.director d " +
739 "join d.person p", Object[].class),
740 offset, limit, orderBy)
741 .getResultList();
742 }
743
744
745
746
747 }