很多时候,我们在使用spring boot jpa JpaRepository时需要多表关联查询获取多表字段,但是又不想在实体类对应数据库中实现这个字段(因为实体类中存在,数据库中不存在时会抛出sql异常),更不想写hql,那么可以实现一个取巧的方法。
示例 :实体类
注意:实体类中没有标记表名称
@Entity
@Data
@NoArgsConstructor
@DynamicInsert
public class ChargingJournaliseForCount implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", length = 19, unique = true, nullable = false, columnDefinition = "bigint(19) unsigned comment '主键'")
private Long id;
@Column(name = "count", columnDefinition = "bigint(5) NOT NULL default '0' comment '计数器,每次上报持久化一下,以防缓存数据丢失'")
private Integer count;
@Column(name = "order_no", columnDefinition = "varchar(32) DEFAULT '' COMMENT '订单号'")
private String orderNo;
//其他表字段,可以写成自定义名称
@Column(name = "pile_property")
private Integer pilePropertye;
//其他表字段,可以写成自定义名称
@Column(name = "pile_capacity")
private BigDecimal pileCapacity;
}
Repository
@Repository
public interface ChargingJournaliseForCountRepository extends JpaRepository<ChargingJournaliseForCount, Long>, JpaSpecificationExecutor<ChargingJournaliseForCount> {
@Query(nativeQuery = true, value = "SELECT * FROM journalise j " +
" LEFT JOIN rchives a ON j.pile_inter_no = a.pile_inter_no" +
" WHERE a.data_platform_id = ?1 AND date_format( j.update_time, '%Y-%m-%d' ) = ?2")
List<ChargingJournaliseForCount> getJournaliseAndPileTypeByDataPlatformId(Integer dataPlatformId, String date);
}