关于jpa与SQL结合的几种查询

最新推荐文章于 2024-10-26 23:02:12 发布

Mouzu

最新推荐文章于 2024-10-26 23:02:12 发布

阅读量2.1k

点赞数

分类专栏：数据库 JPA

本文链接：https://blog.csdn.net/Mouzu/article/details/81137932

版权

本文介绍了如何在Java中使用JPA结合SQL进行复杂查询。通过示例展示了查询结果映射到实体类的不同方法，包括直接结果映射和使用@SqlResultSetMapping注解进行列映射。

摘要由CSDN通过智能技术生成

1.查询结果为各个表各个字段（切记要查出primary key,以防后面要更新该数据）

@SuppressWarnings("unchecked")
   @Override
   public List<TPMSFine> getAlertFailFines(AlertFinishStatus failAlert) {
       String selectFields = "tf.id,tf.txnno,tpmsts.certno,tf.finePayDate,tf.operator,"
               + "tf.amount,tf.indictmentNo,tf.indictmentSendDate,tf.penaltyNoticeNo,tf.penaltyNoticeDate,"
               + "tf.penaltyNoticeNoSec,tf.penaltyNoticeDateSec";
       String sql = "SELECT " + selectFields + " FROM " + table(TPMSFine.class) + " tf"
               + " inner join tpmstransaction tpmsts on tpmsts.TRANSACTIONDETAILS_TXNNO = tf.txnno"
               + " where tf.ALERTFINISHSTATUS is not null and tf.ALERTFINISHSTATUS = :alertFinishStatus";
       Query query = em.createNativeQuery(sql);
       query.setParameter("alertFinishStatus", failAlert.ordinal());枚举类
       List<TPMSFine> tpmsFineList = new ArrayList<TPMSFine>();
       List<Object[]> resultList = query.getResultList();
       for (Object[] objects : resultList) {
           TPMSFine tpmsFine = new TPMSFine();
           tpmsFine.setId(Long.valueOf(objects[0].toString())); 结果映射，类似JDBC
           tpmsFine.setUniqueryCode((String)objects[1]);
           tpmsFine.setCertNo(Long.valueOf(objects[2].toString()));
           tpmsFine.setFinePayDate((java.util.Date)objects[3]);