JPA中各种查询的用法（@NamedQuery、createNativeQuery原生SQL、Order By、Having、Group By、左外连接、子查询）

这个是查询到实体类

package com.jpa.helloworld;

import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.TableGenerator;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.persistence.Transient;

import org.hibernate.annotations.Type;
import org.joda.time.DateTime;
import org.joda.time.LocalDate;

@NamedQuery(name="testNamedQuery",query="select c from Customer c")
@Table(name = "JPA_CUSTOMER")
@Entity
public class Customer {

	private Integer id;

	private String name;

	private String email;

	private int age;

	private Date birth;

	private Set<Order> orders = new HashSet<>();

	private DateTime createDate;

	private LocalDate lastUpdateDate;

	private String tran;

	@TableGenerator(name = "ID_GENDERATOR", pkColumnName = "PK_NAME", pkColumnValue = "CUSTOMER_ID", valueColumnName = "PK_VALUE", allocationSize = 5)
	@GeneratedValue(strategy = GenerationType.TABLE, generator = "ID_GENDERATOR")
	@Id
	@Column(name = "C_ID")
	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}
	
	//这里的mappedBy是指定由Order的customemr属性维护关联关系
	//注意！ 在使用mappedBy属性时，不能同时使用@JoinColumn注解
//	@JoinColumn(name = "CUSTOMER_ID")
	@OneToMany(cascade={CascadeType.REMOVE},mappedBy="customer")
	public Set<Order> getOrders() {
		return orders;
	}

	public void setOrders(Set<Order> orders) {
		this.orders = orders;
	}

	@Column(name = "LAST_NAME", unique = true, length = 20, nullable = false)
	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@Column(name = "EMAIL", length = 50)
	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	@Column(name = "AGE", length = 3)
	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	@Transient
	public String getTran() {
		return tran;
	}

	public void setTran(String tran) {
		this.tran = tran;
	}

	@Column(name = "BIRTH")
	@Temporal(TemporalType.DATE)
	public Date getBirth() {
		return birth;
	}

	public void setBirth(Date birth) {
		this.birth = birth;
	}

	@Column(name = "CREATEDATE")
	@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime")
	public DateTime getCreateDate() {
		return createDate;
	}

	public void setCreateDate(DateTime createDate) {
		this.createDate = createDate;
	}

	@Column(name = "LASTUPDATEDATE")
	@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentLocalDate")
	public LocalDate getLastUpdateDate() {
		return lastUpdateDate;
	}

	public void setLastUpdateDate(LocalDate lastUpdateDate) {
		this.lastUpdateDate = lastUpdateDate;
	}

	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", age=" + age + ", birth=" + birth
				+ ", orders=" + orders + ", createDate=" + createDate + ", lastUpdateDate=" + lastUpdateDate + ", tran="
				+ tran + "]";
	}

}

下面是具体的用法

package com.jpa.test;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.jpa.helloworld.Customer;
import com.jpa.helloworld.Order;

public class JPQLTest {

	private EntityManagerFactory entityManagerFactory;
	private EntityManager em;
	private EntityTransaction transaction;

	@Before
	public void init() {
		entityManagerFactory = Persistence.createEntityManagerFactory("jpa-1");
		em = entityManagerFactory.createEntityManager();
		transaction = em.getTransaction();
		transaction.begin();
	}

	@After
	public void destory() {
		transaction.commit();
		em.close();
		entityManagerFactory.close();
	}

	@SuppressWarnings("unchecked")
	@Test
	public void testNamedQuery() {
		Query query = em.createNamedQuery("testNamedQuery");
		List<Customer> cus = query.getResultList();
		cus.forEach(c -> System.out.println(c));
	}
	
	//利用@NamedQuery注解写在javabean中的sql语句
	@Test
	public void testQuery() {
		String jpql = "select c from Customer c where c.id = ?";
		Query query = em.createQuery(jpql).setParameter(0, 1);
		Customer customer = (Customer) query.getSingleResult();
		System.out.println(customer);
	}
	
	//原生的sql查询
	@Test
	public void testNativeQuery() {
		String jpql = "select last_name from jpa_customer where c_id = ?";
		Query query = em.createNativeQuery(jpql).setParameter(1, 1);
		Object customer = query.getSingleResult();
		System.out.println(customer);
	}
	
	//使用Order By、Having、Group By
	@Test
	public void testOrderBy() {
		String jpql = "select o from Order o group by o.customer.id having count(o) > 1 order by o.customer.id asc ";
		Query query = em.createQuery(jpql);
		List<Order> orders = query.getResultList();
		System.out.println("length:" + orders.size());
		orders.forEach(o -> System.out.println(o.getCustomer().getId()));
	}
	
	//JPA左外连接
	@Test
	public void testLeftOutJoinFetch() {
		String jpql = "select c from Customer c left outer join fetch c.orders where c.id = ?";
		Query query = em.createQuery(jpql).setParameter(0, 7);
		Customer customer = (Customer) query.getSingleResult();
		System.out.println(customer.getName());
		System.out.println(customer.getOrders().size());
	}
	
	//JPA子查询
	@SuppressWarnings("unchecked")
	@Test
	public void testSubQuery() {
		String jpql = "select o from Order o where o.customer = (select c from Customer c where c.id = ?)";
		Query query = em.createQuery(jpql).setParameter(0, 1);
		List<Order> orders = query.getResultList();
		orders.forEach(o -> System.out.println(o.getoName()));
	}
	
	@Test
	public void testUpdate() {
		String jpql = "update Customer c set c.name = ? where c.name = ?";
		Query query = em.createQuery(jpql).setParameter(0, "C-FF").setParameter(1, "C-AA");
		query.executeUpdate();
	}
}