JPA中各种查询的用法

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



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


    @Query(value = "select a.* from v_bin_lot_stock a " +
            "inner join " +
            " (select warehouse,item_no,status,min(lot_no) as lot_no " +
            " from v_bin_lot_stock " +
            " where warehouse=:warehouseCode and item_no in(:itemNo) and status in(:status)" +
            " and qty>0 and lot_no<>''" +
            " group by warehouse,item_no,status) b" +
            " on a.warehouse=b.warehouse and a.item_no=b.item_no and a.status=b.status and a.lot_no=b.lot_no" +
            " where a.qty>0",nativeQuery = true)
    List<BinLotStock> getEarliestWarehouseLotStock(String warehouseCode, List<String> itemNo, List<String> status);


    @Query(value = "SELECT * FROM v_hu a " +
            "WHERE a.bin_code = ?1 " +
            "AND (?2 = '' OR a.item_no = ?2) " +
            "AND (?3 = '' OR a.hu_status = ?3) " +
            "AND (a.lot_no = '' OR a.lot_no IS NULL) AND a.is_part_hu = 1 AND a.valid_flag = 1 AND a.qty <> 0" +
            "ORDER BY a.item_no",
            countQuery = "SELECT COUNT(a.id) FROM v_hu a " +
                    "WHERE a.bin_code = ?1 " +
                    "AND (?2 = '' OR a.item_no = ?2) " +
                    "AND (?3 = '' OR a.hu_status = ?3) " +
                    "AND (a.lot_no = '' OR a.lot_no IS NULL) AND a.is_part_hu = 1 AND a.valid_flag = 1 AND a.qty <> 0 ",
            nativeQuery = true)
    Page<Hu> listLooseHuInBinPage(String binCode,
                                  String itemNo,
                                  String status,
                                  Pageable pageable);


    /**
     * 更新箱的二维码信息
     *
     * @param huId           箱id
     * @param qrCode         箱二维码
     * @param qrCodeSimplify 箱二维码简化
     */
    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query(value = "UPDATE t_hu SET qr_code = :qrCode, qr_code_simplify = :qrCodeSimplify WHERE id = :id", nativeQuery = true)
    void updateQrCodeByHuId(@Param("id") Long huId, @Param("qrCode") String qrCode, @Param("qrCodeSimplify") String qrCodeSimplify);