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

ゝ一纸荒年。 2022-05-19 03:52 392阅读 0赞

这个是查询到实体类

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();
    	}
    }