Spring JPA JPQLの紹介
13952 ワード
JPQL ， SQL ， JPQL ， 、 ， hibernate HQL 。
JPQL : 。 。 。
：“ : + ”
：
Query query = em.createQuery("select p from Person p where p.personid=:Id ");
query.setParameter("Id",new Integer(1));
“ ?+ ”
：
Query query = em.createQuery("select p from Person p where p.personid=?1 ");
query.setParameter(1,new Integer(1));
java.util.Date java.util.Calendar ， setParameter() ， setParameter ：
public interface Query
{
// ， java.util.Date
Query setParameter(String name, java.util.Date value, TemporalType temporalType);
// ， java.util.Calendar
Query setParameter(String name, Calendar value, TemporalType temporalType);
// ， java.util.Date
Query setParameter(int position, Date value, TemporalType temporalType);
// ， java.util.Calendar
Query setParameter(int position, Calendar value, TemporalType temporalType);
}
Date Calendar 、 . Query ， javax.persistence.TemporalType setParameter ， java.util.Date java.util.Calendar SQL 。
JPQL ， entity Bean Person, Order, OrderItem ， ： Person Order, Order OrderItem 。
JPQL ： Java ， 。 ， SeLeCT sELEct SELECT ， com.foshanshop.ejb3.bean.Person com.foshanshop.ejb3.bean.PERSon ， person.name person.NAME 。
bean @NamedQuery or @NamedQueries ， BUG 。 。
：
@NamedQuery (name= "getPerson" , query= "FROM Person WHERE personid=?1" )
@Entity
public class Person implements Serializable{
， @javax.persistence.NamedQueries @NamedQuery ：
@NamedQueries ({
@NamedQuery (name= "getPerson" , query= "FROM Person WHERE personid=?1" ),
@NamedQuery (name= "getPersonList" , query= "FROM Person WHERE age>?1" )
})
@Entity
public class Person implements Serializable{
， 。 ：
Query query = em . createNamedQuery("getPerson") ;
query.setParameter(1, 1);
(order by)
"ASC" "DESC" ， JPQL asc
：
// ，
Query query = em.createQuery("select p from Person p order by p.age desc, p.birthday asc ");
， Entity ， Entity 。J P QL ， Entity 。 Entity ，
：
// ( )
Query query=em.createQuery("select p.personid, p.name from Person p order by p.personid desc ");
// Person, Object[]
List result = query.getResultList();
if (result!=null){
Iterator iterator = result.iterator();
while( iterator.hasNext() ){
Object[] row = ( Object[]) iterator.next();
int personid = Integer.parseInt(row[0].toString());
String PersonName = row[1].toString();
。。。。
}
}
(Constructor)
JPQL java class ， 。 person entity bean name and personid ， object[]， 。 。
：
public class SimplePerson {
private Integer personid;
private String name ;
。。。。
public SimplePerson() {
}
public SimplePerson(Integer personid, String name) {
this . name = name;
this . personid = personid;
}
}
：
// SimplePerson ， new 。
Query query = em.createQuery(" select new com.foshanshop.ejb3.bean.SimplePerson(p. personid, p.name) from Person p order by p.personid desc ");
// SimplePerson
List result = query.getResultList();
if (result!=null){
Iterator iterator = result.iterator();
while( iterator.hasNext() ){
SimplePerson simpleperson = (SimplePerson) iterator.next();
。。。。
}
}
(Aggregation)
JPQL ：
1. AVG()
2. SUM()
3. COUNT() ， Long ， count(*) hibernate ， toplink
4. MAX()
5. MIN()
：
//
Query query = em.createQuery("select max(p.age) from Person p");
Object result = query.getSingleResult();
String maxAge = result.toString();
//
query = em.createQuery("select avg(p.age) from Person p");
//
query = em.createQuery("select min(p.age) from Person p");
//
query = em.createQuery("select count(p) from Person p");
//
query = em.createQuery("select sum(p.age) from Person p");
select...from ， "GROUP BY" 。 "GROUP BY" select 。
：
//
Query query = em.createQuery(" select p.sex, count(p) from Person p group by p.sex ");
// Person, Object[]
List result = query.getResultList();
， "HAVING" "WHERE"
：
// 1
Query query = em.createQuery("select p.sex, count(p) from Person p group by p.sex having count(*) >?1");
//
query.setParameter(1, new Long(1));
// Person, Object[]
List result = query.getResultList();
(join)
JPQL SQL ：
left out join/left join
inner join
left join fetch/inner join fetch
left out join/left join ， Entiies （ left join/left outer join 。 ）
：
// 26 , Order OrderItem
select o from Order o left join o.orderItems where o.ower.age=26 order by o.orderid
inner join Entities 。
：
// 26 ,Order OrderItem
select o from Order o inner join o.orderItems where o.ower.age=26 order by o.orderid
！！ ： ， Entity ， ( lazy-load ) 。 ， left fetch/left out fetch/inner join fetch 。
：
private String QueryInnerJoinLazyLoad(){
// (orderItems)
Query query = em.createQuery("select o from Order o inner join o.orderItems where o.ower.age=26 order by o.orderid");
List result = query.getResultList();
if (result!=null && result.size()>0){
// Order orderItems( )
Order order = (Order) result.get(0);
// ， EJB3 Runtime SQL Order
//OrderItems
Set<OrderItem> list = order.getOrderItems ();
Iterator<OrderItem> iterator = list.iterator();
if (iterator.hasNext()){
OrderItem orderItem =iterator.next();
System.out.println (" ： "+ orderItem.getProductname());
}
}
"select o from Order o inner join o.orderItems where o.ower.age=26 order by o.orderid" SQL （ (orderItems) ） ：
select order0_.orderid as orderid6_, order0_.amount as amount6_, order0_.person_id as
person4_6_, order0_.createdate as createdate6_ from Orders order0_ inner join OrderItems
orderitems1_ on order0_.orderid=orderitems1_.order_id, Person person2_ where
order0_.person_id=person2_.personid and person2_.age=26 order by order0_.orderid
Set<OrderItem> list = order.getOrderItems(); SQL Order OrderItems ， SQL ：
select orderitems0_.order_id as order4_1_, orderitems0_.id as id1_, orderitems0_.id as id7_0_,
orderitems0_.order_id as order4_7_0_, orderitems0_.productname as productn2_7_0_,
orderitems0_.price as price7_0_ from OrderItems orderitems0_ where orderitems0_.order_id=?
order by orderitems0_.id ASC
。 N Order ， SQL Order ， N Order orderItems 。 N+1 ， join fetch SQL Order
// 26 ,Order OrderItem
Query query = em.createQuery("select o from Order o inner join fetch o.orderItems where
o.ower.age=26 order by o.orderid");
HPQL SQL ：
select order0_.orderid as orderid18_0_, orderitems1_.id as id19_1_, order0_.amount as
amount18_0_,order0_.person_id as person4_18_0_, order0_.createdate as createdate18_0_,
orderitems1_.order_id as order4_19_1_, orderitems1_.productname as productn2_19_1_,
orderitems1_.price as price19_1_, orderitems1_.order_id as order4_0__, orderitems1_.id as id0__
from Orders order0_ inner join OrderItems orderitems1_ on
order0_.orderid=orderitems1_.order_id, Person person2_ where
order0_.person_id=person2_.personid and person2_.age=26 order by order0_.orderid,
orderitems1_.id ASC
fetch, SQL ， N+1 SQL
DISTINCT
， ， ：
" select o from Order o inner join fetch o.orderItems order by o.orderid "
N orderItem N Order, Order ， DISTINCT 。
：
select DISTINCT o from Order o inner join fetch o.orderItems order by o.orderid
Entity
， String, ( int, double ) ( Integer, Double ), Entity 。
：
//
Query query = em.createQuery("select o from Order o where o.ower =?1 order by o.orderid");
Person person = new Person();
person.setPersonid(new Integer(1));
//
query.setParameter(1,person);
(Batch Update)
HPQL
：
// 10
Query query = em.createQuery(" update Order as o set o.amount=o.amount+10 ");
//update
int result = query. executeUpdate ();
(Batch Remove)
：
// 100 , ,
Query query = em .createQuery( "delete from OrderItem item where item.order in(from Order as o where o.amount<100)" );
query. executeUpdate();
query = em .createQuery( "delete from Order as o where o.amount<100" );
query.executeUpdate(); //delete
NOT
//
Query query = em.createQuery("select o from Order o where not(o.ower =?1) order by o.orderid");
Person person = new Person();
person.setPersonid(new Integer(2));
//
query.setParameter(1,person);
BETWEEN
select o from Order as o where o.amount between 300 and 1000
IN
// 26,21 Person
select p from Person as p where p.age in(26,21)
LIKE
// "li" Person
select p from Person as p where p.name like 'li%'
IS NULL
// Order
select o from Order as o where o.ower is [not] null
IS EMPTY
IS EMPTY (Collection) 。 NOT 。 ： Mysql IS EMPTY
// Order
select o from Order as o where o.orderItems is [not] empty
EXISTS
[NOT]EXISTS 。 ： Mysql EXISTS
// 1 ， OrderItem
select oi from OrderItem as oi where exists (select o from Order o where o.orderid=1)
// 10 ， id 1 OrderItem
select oi from OrderItem as oi where oi.id=1 and not exists (select o from Order o where o.orderid=10)
JPQL 。 SQL 。 ：
1. CONCAT
2. SUBSTRING
3. TRIM
4. LOWER
5. UPPER
6. LENGTH
7. LOCATE
：
// ， "_foshan"
select p.personid, concat(p.name, '_foshan') from Person as p
// ,
select p.personid, substring (p.name,1,3) from Person as p
HPQL ：
ABS
SQRT
MOD
SIZE
：
// Order
select o.orderid, size (o.orderItems) from Order as o group by o.orderid
// Order /10
select o.orderid, mod (o.amount, 10) from Order as o
WHERE HAVING
：
// 26 Order
select o from Order as o where o.ower in(select p from Person as p where p.age =26)
， 。 ， QueryAPI ： setMaxResults( ) setFirstResult( ) 。
setMaxResults
setFirstResult （ 3 ， ， 0 ， 0 ， 1 ， 2 ）
：
public List getPersonList( int max, int whichpage) {
try {
int index = (whichpage-1) * max;
Query query = em .createQuery( "from Person p order by personid asc" );
List list = query. setMaxResults(max) .
setFirstResult(index) .
getResultList();
em .clear(); // EntityManager bean， VM
return list;
}