JPA之JPQL（三）

1 什么是JPQL
JPQL是Java Persistence Query Language的简称，可以看成是JPA中的HQL， JPQL支持各种复杂查询。

2 检索单个对象
@Test
public void querySingleObject1() {
Query query = em.createQuery("select * from Course where id=?1");
query.setParameter(1,47);
Course course = (Course)query.getSingleResult();
System.out.println(course);

}

抛异常：
java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: * near line 1, column 8

@Test
public void querySingleObject2() {
Query query = em.createQuery("select id,name from Course where id=?1");
query.setParameter(1,47);
Course course = (Course)query.getSingleResult();
System.out.println(course);

}
抛异常：
java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to org.frank1234.jpa.helloworld.Course

正确的方式是：
@Test
public void querySingleObject3() {
Query query = em.createQuery("select c from Course c where id=?1");
query.setParameter(1,47);
Course course = (Course)query.getSingleResult();
System.out.println(course);
}

或者：
@Test
public void querySingleObject4() {
Query query = em.createQuery(" from Course where id=?1");
query.setParameter(1,47);
Course course = (Course)query.getSingleResult();
System.out.println(course);
}


3 绑定参数
1）拼字符串方式
@Test
public void bindparameter1() {
int id = 47;
Query query = em.createQuery("select c from Course c where id='"+id+"'");
Course course = (Course)query.getSingleResult();
System.out.println(course);
}


2）按参数名字绑定

@Test
public void bindparameter2() {
Query query = em.createQuery("select c from Course c where id=:id");
query.setParameter("id",47);
Course course = (Course)query.getSingleResult();
System.out.println(course);
}


3)按参数位置绑定

@Test
public void bindparameter3() {
Query query = em.createQuery("select c from Course c where id=?1");
query.setParameter(1,47);
Course course = (Course)query.getSingleResult();
System.out.println(course);
}


4 排序
@Test
public void orderby() {
Query query = em.createQuery("select c from Course c order by id desc");
List<Course> courses = query.getResultList();
for(Course course:courses){
System.out.println(course);
}
}


同SQL语法一致。

5 分页
@Test
public void pagging() {
Query query = em.createQuery("select c from Course c order by id desc");
query.setFirstResult(0).setMaxResults(10);//从第一个开始取，取10个

List<Course> courses = query.getResultList();
for(Course course:courses){
System.out.println(course);
}
}


同hibernate一样，仍然支持方法链编程风格。


6 命名查询
<hibernate-mapping>
...
<query name="getCourse">
from Course where id=?1
</query>
</hibernate-mapping>


@Test
public void namedQuery() {
Query query = em.createNamedQuery("getCourse");
query.setParameter(1, 47);
Course course = (Course)query.getSingleResult();
System.out.println(course);
}


7 投影查询
@Test
public void shadowQuery() {
Query query = em.createQuery("select name from Course where id=?1");
query.setParameter(1, 47);
String courseName = (String)query.getSingleResult();
System.out.println(courseName);
}


8 聚合函数
JPQL中可以使用count avg sum min max 这些聚合函数。
例如：
@Test
public void count1() {
Query query = em.createQuery("select count(*) from Course ");
Long count = (Long)query.getSingleResult();
System.out.println(count);
}

@Test
public void count2() {
Query query = em.createQuery("select max(name),min(name) from Course ");
Object[] count = (Object[])query.getSingleResult();
System.out.println(count[0]+","+count[1]);
}


9 分组
示例：
@Test
public void groupby() {
Query query = em.createQuery("select name,count(*) from Course group by name");
Iterator it = query.getResultList().iterator();
while(it.hasNext()){
Object[] obj = (Object[])it.next();
System.out.println("obj[0]="+obj[0]+",obj[1]="+obj[1]);
}
}

10 关联查询
Clazz对象和Student对象是1：N的双向关联关系，类的源码及表结构请参见：[url]http://frank1234.iteye.com/blog/2172205 [/url]

@Test
public void join1() {
Query query = em.createQuery("from Clazz a , Student b where a.id= b.clazz");
List list = query.getResultList();
Iterator it = list.iterator();
while(it.hasNext()){
Object[] obj = (Object[])it.next();
Clazz clazz = (Clazz) obj[0];
System.out.println("clazz="+clazz);
Student student = (Student)obj[1];
System.out.println("student="+student);
}
}


或者：
@Test
public void join2() {
Query query = em.createQuery("from Clazz a join a.students");
List list = query.getResultList();
Iterator it = list.iterator();
while(it.hasNext()){
Object[] obj = (Object[])it.next();
Clazz clazz = (Clazz) obj[0];
System.out.println("clazz="+clazz);
Student student = (Student)obj[1];
System.out.println("student="+student);
}
}


11 批量更新
@Test
public void batchupdate() {
em.getTransaction().begin();
try {
Query query = em.createQuery("update Course set name='batchupdate' where id > 50");
query.executeUpdate();
em.getTransaction().commit();
}catch (Exception e){
e.printStackTrace();
em.getTransaction().rollback();
}
}

12.获取Connection
Connection conn = ((HibernateEntityManager)em).getSession().connection();


13.悲观锁
@Test
public void lock() {
em.getTransaction().begin();
Query query = em.createQuery("from Course c where id=?1");
query.setParameter(1,47);
// query.setLockMode(LockModeType.WRITE);
query.setLockMode(LockModeType.PESSIMISTIC_WRITE);//这个和下面的放开任何一个。
query.setLockMode(LockModeType.PESSIMISTIC_READ);
Course course = (Course)query.getSingleResult();
System.out.println(course);
em.getTransaction().commit();//提交之前，其他事务的写会被阻塞。
}