package com.snailteam.reporting.query;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.ParameterExpression;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import com.snailteam.reporting.domain.Product;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;
import com.snailteam.reporting.domain.Users;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration( { "/META-INF/spring/applicationContext*" })
@Transactional
/**
*
* @author xiaofancn
*
*/
public class TestJPAQuery {
@PersistenceContext
EntityManager em;
@Test
public void testGetUsers(){
String name="jQuery";
//创建条件构建对象
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
//针对Product实体的条件查询
CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
//获取实体Product的属性集 Product.id Product.name
Root<Product> productRoot = criteriaQuery.from(Product.class); //HQL
//获取实体属性
Path<String> product_name = productRoot.get("name");
//条件构造
criteriaQuery.where(criteriaBuilder.equal(product_name,name));//HQL
TypedQuery<Product> queryResult = em.createQuery(criteriaQuery);
System.out.println(queryResult.getResultList());
}
@Test
public void testJpaQuery() {// from Users user where user.name like
// '%"+name+"'
// 创建条件查询Builder对象
CriteriaBuilder queryBuilder = em.getCriteriaBuilder();
// 针对Users实体的查询
CriteriaQuery<Users> usersQuery = queryBuilder.createQuery(Users.class);
// 获取实体Users的属性集 Users.id Users.name
Root<Users> userRoot = usersQuery.from(Users.class);
// 条件查询条件List对象
List<Predicate> criteria = new ArrayList<Predicate>();
String name = "xiaofancn";
// 设置查询的属性 select ....
usersQuery.select(userRoot);//
if (name != null && !"".equals(name)) {
// 相当于拼写HQL的Where属性 where a=1 and b=1
ParameterExpression<String> parameters = queryBuilder.parameter(
String.class, "name");
criteria.add(queryBuilder.like(userRoot.<String> get("name"),
parameters));
usersQuery.where(criteria.get(0));// 只有一个查询 条件 like name
}
// 上面构建一个hql空语句,下面填充属性,如果下面没有填充属性,关于属性的查询就不构造
TypedQuery<Users> queryResult = em.createQuery(usersQuery);
if (name != null && !"".equals(name)) {
// 具体的参数和值
queryResult.setParameter("name", name + "%");
}
queryResult.setFirstResult(0);
queryResult.setMaxResults(10);
System.out.println(queryResult.getResultList());
}
@Test
public void testJpaCount() {// from count(*) Users user where user.name like
// '%"+name+"'
CriteriaBuilder queryBuilder = em.getCriteriaBuilder();
CriteriaQuery<Long> usersQuery = queryBuilder.createQuery(Long.class);
Root<Users> userRoot = usersQuery.from(Users.class);
List<Predicate> criteria = new ArrayList<Predicate>();
String name = "1";
usersQuery.select(queryBuilder.count(userRoot));
if (name != null && !"".equals(name)) {
ParameterExpression<String> parameters = queryBuilder.parameter(
String.class, "name");
criteria.add(queryBuilder.like(userRoot.<String> get("name"),
parameters));
usersQuery.where(criteria.get(0));
}
TypedQuery<Long> queryResult = em.createQuery(usersQuery);
if (name != null && !"".equals(name)) {
queryResult.setParameter("name", name + "%");
}
System.out.println(queryResult.getSingleResult());
}
@Test
public void testJpaIn() {
String sql = "from Resources r where r.id in :array";
List<Long> list = new ArrayList<Long>();
list.add(1l);
System.out.println(em.createQuery(sql).setParameter("array", list)
.getResultList());
}
}
http://download.oracle.com/javaee/6/tutorial/doc/gjivm.html