• 使用JPA
  • 虽然JPA是一个标准，但spring中一般就是使用hibernate实现的
  • 使用JPA（Java Persistence API，Java持久化API，是个规范，其实是借助Hibernate实现的）操作数据库，使用其CrudRepository接口。
  • 数据库建好后，表会在使用时自动创建。
  • 依赖是通过Gradle的dependencies中加入compile('org.springframework.boot:spring-boot-starter-data-jpa')，会简介引用一些其他的包如spring.rabbit-1.7.8.RELEASE.jar等
    • 也可能是通过引入上层的配置
  • application.properties中配置数据库信息，如果是Cloud Foundry等平台，app部署后的环境变量中如果有同名参数则会覆盖properties文件

# postgresql
spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/amp
spring.datasource.username=postgres
spring.datasource.password=deb.deb
spring.jpa.database=POSTGRESQL
spring.jpa.database-platform=org.hibernate.dialect.PostgresPlusDialect
spring.jpa.show-sql=false
spring.jpa.hibernate.ddl-auto=update

* 定义用户POJO（算是数据库表的映射类）
    * 没有表、索引等时，会自动创建
    * 会用到一些数据库相关的注解，如@Entity(name = "assignment")、@Table、@Index、@NoArgsConstructor、@Id、@GeneratedValue(strategy = GenerationType.AUTO)、@Column(name = "tenant_id", nullable = false)、、

@Entity(name = "assignment")
@Table(indexes = {
        @Index(name = AMPShiftCalendarConstants.IDX_TENANT_ID_ASSET_ID_ASSIGNMENT_START_DATE, columnList = "tenant_id, asset_id, assignment_start_date"),
        @Index(name = AMPShiftCalendarConstants.IDX_TENANT_ID_ASSIGNMENT_START_DATE, columnList = "tenant_id, assignment_start_date") })
@NoArgsConstructor
@Getter
@Setter
public class Assignment {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "assignment_id")
    private Long assignmentId;

    @Column(name = "tenant_id", nullable = false)
    private String tenantId;

    @Column(name = "asset_id", nullable = false)
    private String assetId;

    @Column(name = "content", columnDefinition = "TEXT")
    private String content;

    @Column(name = "assignment_start_date", columnDefinition = "timestamp with time zone", nullable = false)
    private Timestamp assignmentStartDate;

    @Column(name = "inserted_date", columnDefinition = "timestamp with time zone", nullable = false)
    private Timestamp insertedDate;

    @Enumerated(EnumType.STRING)
    @Column(name = "status", nullable = false)
    private AssignmentStatus status;

    public Assignment(String tenantId, String assetId, ShiftCalendar content, Timestamp assignmentStartDate, Timestamp insertedDate, AssignmentStatus status) {
        this.tenantId = tenantId;
        this.assetId = assetId;
        this.setShiftCalendar(content);
        this.assignmentStartDate = assignmentStartDate;
        this.insertedDate = insertedDate;
        this.status = status;
    }

    public ShiftCalendar getShiftCalendar() {
        return content != null ? Misc.deserializeSafeJson(content, ShiftCalendar.class) : null;
    }

    public void setShiftCalendar(ShiftCalendar template) {
        Check.notNull(template, "shiftCalendar");

        this.content = Misc.serializeSafeJson(template);
    }

    @Override
    public String toString() {
        return "Assignment [assignment_id=" + assignmentId + ", asset=" + assetId + ", assignmentStatus=" + status + ", content=" + content
                + ", assignment_start_date=" + assignmentStartDate + "]";
    }
}

* 自定义Repository类
    * 基于表及其映射类，也可以直接用CrudRepository
    * 用于数据库操作，有点类似于.NET的EF的DBContext类，基类已实现了部分通用简单的基于数据库表的操作，复杂的还是要自己拼sql（用xml或动态sql或者如下的方式）。
    * 其实还有其他更具体的功能更强的子类可以用，比如可以分页什么的。
    * getAllByTenantIdAndAssetId这种其实是利用了JPA的命名查询功能，可以指定通过哪些列来查询，还可以使用like功能。
    * @Query注解其实是JPA提供查询语言的JPQL，与Hibernate提供的HQL十分接近。
    * JPA（Hibernate）其实慢慢被mybatis替代了？？

@Repository
public interface AssignmentRepository extends CrudRepository<Assignment, Long> {

    List<Assignment> getAllByTenantId(String tenantId);

    List<Assignment> getAllByTenantIdAndAssetId(String tenantId, String assetId);

    @Query(value = "" +
            "  SELECT " +
            "DISTINCT a.asset_id AS asset_id" +
            "    FROM assignment a " +
            "   WHERE a.tenant_id = :tenant_id" +
            "     AND a.assignment_start_date < :end_date" +
            "     AND a.status='ACTIVE'",
            nativeQuery = true)
    List<String> getAllAssetsWithActiveWorkplan(
            @Param("tenant_id") String tenantId,
            @Param("end_date") Timestamp endDate);

}

public interface MissingWorkPieceRepository extends CrudRepository<MissingWorkPiece, Long> {

    List<MissingWorkPiece> getAllByAssetIdAndTenantIdAndDateBetweenOrderByDateDesc(String assetId, String tenantId, Timestamp from, Timestamp to);
    
    Optional<MissingWorkPiece> getByAssetIdAndTenantIdAndDate(String assetId, String tenantId, Timestamp date);
    
    @Transactional  
    void deleteByAssetIdAndTenantIdAndDate(String assetId, String tenantId, Timestamp date);
    
}