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;