SpringDataJPA-子查询
子查询也是一种视图查询
在数据库实际使用的时候,为了一些业务的设计,有些时候我们需要映射视图到实体,这个时候就可以使用Subselect注解来标注一个视图类
@Data
@Entity
@Subselect("select d.id as id, d.name as dog_name, c.name as cat_name from dog_tb d left join cat_tb c on d.id=c.id")
@Synchronize({"dog_tb", "cat_tb"})
public class SubSelectEntity implements Serializable {
private static final long serialVersionUID = -3795682088296075408L;
@Id
private Long id;
private String dogName;
private String catName;
}
@Subselect
子查询的注解,里面是原生的sql语句
@Synchronize
需要同步的表,如果表变动了,查询视图会更新这个数据
备注:可以使用@Immutable 来标注这个类不可以修改,因为视图是可读不可写的,修改数据需要修改对应表的数据
和普通的实体类构建repository接口一样
public interface SubSelectRepository extends JpaRepository<SubSelectEntity, Long>, JpaSpecificationExecutor<SubSelectEntity>, Serializable {
}
使用也和普通的实体类的使用方法一致,就不详细写了,写两个方法测试一下
@Slf4j
@RestController
@RequestMapping("/chapter/five")
public class ChapterFiveController {
@Autowired
private SubSelectRepository subSelectRepository;
@ApiOperation(value = "findAll", httpMethod = "GET")
@GetMapping("/find")
public List<SubSelectEntity> findAll() {
return subSelectRepository.findAll();
}
@ApiOperation(value = "findPage", httpMethod = "GET")
@GetMapping("/page")
public Page<SubSelectEntity> findPage(@RequestParam int pageSize, @RequestParam int pageNum) {
Pageable pageable = PageRequest.of(pageNum, pageSize);
return subSelectRepository.findAll(pageable);
}
}