JPA sql 查询

JPA 提供EntityManager entitymanager

sql 执行分为  entitymanager.createNativeQuery  和   entityManager.createQuery  

1.     单表查询时。TbAlarmReason 为对应表的pojo类

String sql = "select * from a where name = ?"; 

Query query = this.em.createNativeQuery(jpql, TbAlarmReason.class).setParameter(1,liming );

        List<TbAlarmReason> result = (List<TbAlarmReason>)query.getResultList();

多表查询时不传入pojo类，  query.getResultList()返回一个object[] 

String sql = "select * from a join b on a.id = b.id where a.name = ?";

Query query = entityManager.createNativeQuery(sql).setParameter(1,liming);

List<Object[]> result = query.getResultList();

for(Object[] b:result){

b[0].toString = 

b[1].toString = 

}

分页时

Query query = entityManager.createNativeQuery(sql).setParameter(1,liming);

//FirstResult means 从第(pageNum*pagesize+1)条开始取数据，传入pageNum=0 ，pagesize=10,就是从第1条开始取数据
        query.setFirstResult(pagenum*pagesize);
       //pageSize means 取多少条比如10条
        query.setMaxResults(PageSize); 

List<Object[]> result = query.getResultList();

2.更新时 ，以下使用jpql , 其中TbAlarm 是对应的pojo类

String sql = "update TbAlarm set a.age = ?,a.name =?  where a.id=?";

entityManager.createQuery(sql).setParameter(1, age18).setParameter(2,nameli).setParameter(3,id3).executeUpdate(); 

3.many to one ,one to many 

以下两个表，有父子级联关系，asset是子，customer是父级。当查询customer表时，自动生成cu.id = ae.customer_id 的asset 集合

-----------------------------表 tb_customer---------------------

@Table(name = "tb_customer")
public class TbCustomer {
    @Id
    @Column(nullable = false)
    private String id;

  @OneToMany(cascade = CascadeType.ALL, mappedBy = "customer")
    private Collection<TbAsset> assets;

-------------------------------表 tb_asset--------------------

@Table(name = "tb_asset")
public class TbAsset {
        @Id
        @Column(nullable = false)
        private String id;

private String customerId;

@ManyToOne
    @JoinColumn(name = "customer_id", referencedColumnName = "id")
    @JsonIgnore
    private TbCustomer customer;