EJB Query Language: Understanding EJB QL and Its Features - Prof. Ye Wu, Study notes of Engineering

Download EJB Query Language: Understanding EJB QL and Its Features - Prof. Ye Wu and more Study notes Engineering in PDF only on Docsity! 1 EJB Entity Beans EJB Query Language Ye Wu http://www.ise.gmu.edu/~wuye SWE 645 Component-based Software Development 2008-3-24 © Dr. Ye Wu 2 Why EJB QL String query = “Select firstName, lastName from Customer where” + “customerid = ” + customerid; • Performance – convert to SQL • Security – what if the customer id is “0 OR true” • Not object oriented 2008-3-24 © Dr. Ye Wu 3 EJB QL • What is EJB QL? EJB QL is a query language designed to work with entities that could be portably compiled to any SQL dialect • How is EJB QL different then SQL? 2008-3-24 © Dr. Ye Wu 4 EJB QL - history • First appeared in EJB 2.0 and enhanced in EJB 2.1 • Enhancement in EJB 3.0 •Single and multiple value result types •Aggregate functions, with sorting and grouping clauses •More natural join syntax, support both inner and outer joins •Conditional expressions involving subqueries •Result projection into non-persistent classes 2008-3-24 © Dr. Ye Wu 5 EJB QL – First Example Query query = entityManager.createQuery( "from Customer c where c.firstName=:firstName and c.lastName=:lastName"); Query.setMaxResults(10); query.setParameter("firstName", firstName); query.setParameter("lastName", lastName); List result = query.getResultList(); 2008-3-24 © Dr. Ye Wu 6 EJB QL • Parameters • :parameter - named parameters Query query = entityManager.createQuery( … … c.firstName = :first …) query.setParameter(“first”,firstName); • ?parameter – position parameters Query query = entityManager.createQuery( … … c.firstName = ?1 …) query.setParameter(1, value) 2 2008-3-24 © Dr. Ye Wu 7 EJB QL • Date Parameters Query query = … … where c.birthDate > ?1 query.setParameter(1, userDefinedData, TemporalType.DATE) setParameter(String, Date, TemporalType) setParameter(String, Caledar, TemporalType) setParameter(int, Date, TemporalType) setParameter(int, Caledar, TemporalType) 2008-3-24 © Dr. Ye Wu 8 EJB QL • Paging results •What if your query returns too many results? •Query.setMaxResults(max). setFirstResult(index). getResultList(); Note, these methods should not be used with queries that join across collection relationships. (one-to-many and many-to-many) 2008-3-24 © Dr. Ye Wu 9 EJB QL • Hints • Vendor-specific • Ignore hints that are not understandable Query query = … … query.setHint(“org.hibernate.timeout”,1000) @NamedQuery(name =“aaa”, query=“… …”. hints={@QueryHint(name=“”,value=“”)}) 2008-3-24 © Dr. Ye Wu 10 EJB QL • Selecting Entity and relationship Properties Select Object(c) from Customer AS customer Select Object(c) from Customer customer Select c.address.city from customer c Note, it is illegal to navigate across a collection- based relationship field X - select c.reservations.cruise from Customer AS c 2008-3-24 © Dr. Ye Wu 11 EJB QL • Projection • Constructor Expressions Create plain POJO from EJB QL Public class Name { private String first; private String last; public Name(String first, String last) { this.first = first; this.last = last; } … … Select new Name(c.firstName, c.lastName) FROM customer c 2008-3-24 © Dr. Ye Wu 12 EJB QL • IN Operator and INNER JOIN select r from Customer AS c, IN (c.reservations) r ↨ select r from Customer AS c INNER JOIN c.reservations r ↨ select c.reservations from Customer AS c select r.cruise from Customer AS c, IN (c.reservations) r ↨ select r.cruise from Customer AS c INNER JOIN c.reservations r X select c.reservations.cruise from Customer AS c 5 2008-3-24 © Dr. Ye Wu 25 Entity Callbacks and Listeners • Callbacks @Entity Public class … { @PostPersist void afterInsert() {} } • Callback annotations @PrePersist, @PostPersist, @PostLoad, @PreUpdate, @PostUpdate, @PreRemove, @PostRemove 2008-3-24 © Dr. Ye Wu 26 Entity Callbacks and Listeners • Entity Listener Entity listener are classes that can generically intercept entity callback events. They are not entity class, but they can be attached to an entity class through a binding annotation or XML @Entity Public class TestEntityListener { @PostPersist void afterInsert() {} } @Entity @EntityListener(TestEntityListener.class) 2008-3-24 © Dr. Ye Wu 27 EJB QL • Performance – convert to SQL • Security – what if the customer id is “0 OR true” • Not object oriented 2008-3-24 © Dr. Ye Wu 28 Complex Query – Traditional Approach 1 • UI – search by first name and/or last name if (firstname != null) { if (firstClause) { query = query + " where "; } else { query = query + " and "; query += " s.date >= '" + startDate + "'"; } // And so on... 2008-3-24 © Dr. Ye Wu 29 Complex Query – Traditional Approach 2 • UI – search by first name and/or last name Step 1: prepare query string Map parameters = new HashMap(); StringBuffer queryBuf = new StringBuffer("from Customer c "); boolean firstClause = true; if (startDate != null) { queryBuf.append(firstClause ? " where " : " and "); queryBuf.append("s.firstname = :firstname"); parameters.put(" firstname ", firstname); firstClause = false; } if (endDate != null) { queryBuf.append(firstClause ? " where " : " and "); queryBuf.append("s.lastname <= :lastname"); parameters.put("lastname", lastname); firstClause = false; } 2008-3-24 © Dr. Ye Wu 30 Complex Query – Traditional Approach 2 Step 2: Populate Value String hqlQuery = queryBuf.toString(); Query query = session.createQuery(hqlQuery); Iterator iter = parameters.keySet().iterator(); while (iter.hasNext()) { String name = (String) iter.next(); Object value = map.get(name); query.setParameter(name,value); } List results = query.list(); 6 2008-3-24 © Dr. Ye Wu 31 Query-by-Criteria/Query-by-Example • UI – search by first name and/or last name Criteria criteria = session.createCriteria(Customer.class); if (firstname != null) { criteria.add(Expression.eq("firstname",firstname); } if (endDate != null) { criteria.add(Expression.ep("lasttname",lasttname); } List results = criteria.list(); 2008-3-24 © Dr. Ye Wu 32 EJB 3 vs. Hibernate • EJB3 follows JPA spec • Hibernate is a superset of the JPA • Hibernate extension •Annotation extension •Criteria Queries •Validator