Spring Data JPA Mapping VO and DTO Object Mode

Directory Spring Data JPA Mapping VO/DTO Object HQL Mode Native SQL Form Spring Data Jpa Custom repository to DTO

Spring Data JPA Mapping VO/DTO Objects

In project development, it is often necessary to map VO/DTO objects according to business requirements (the understanding of these two concepts is vague-. -), this article will briefly introduce how to handle entity class mapping in the way of Spring Data JPA

HQL mode

public interface MusicTypeRepository extends JpaRepository<MusicType,Integer> {
     @Query("select new cn.srblog.springbootcurd.vo.StudentTypeInfoVo(count(s.id),m.name) " +
             "FROM MusicType m  left JOIN Student s on s.musicTypeId = m.id group by m.id ")
     List<StudentTypeInfoVo> getTypeInfo();
}

Fill in the entity class path, the order of construction parameters should be 1, and the field name should be the attribute in the entity class The on s. musicTypeId = m. id statement can be omitted if mapping of entity class attributes is configured

VO Entity Class

@Value
public class StudentTypeInfoVo  {
    private Long count;
    private String name;
}

Use @ Value annotation of Lombok

Default generates parametric constructors By default, getter decorations are added to member variables, and only getter () methods are provided

The form of native SQL

Interface form

public interface CoursePlanRepository extends JpaRepository<CoursePlan,Integer> {
       @Query(nativeQuery = true,value = "SELECT " +
               " c.id as id," +
               "DAYOFWEEK(c.start_time) as week," +
               "m.name as musicType," +
               "t.name as teacherName," +
               "c.start_time as startTime," +
               "c.end_time as endTime " +
               " FROM t_courseplan c,t_musictype m , t_teacher t " +
               " WHERE DATE(c.start_time) < DATE_ADD(CURDATE(), INTERVAL 7 DAY ) AND CURDATE() <= DATE(c.start_time) " +
               " and t.id=c.tea_id and c.music_type_id = m.id order by c.start_time ")
       List<CoursePlanVos> getWeekList();
}

nativeQuery = true indicates that the native SQL query is opened The query field alias needs to correspond to field 11 in the entity class The function of this method is to query the data after 1 week

函数	说明
DAYOFWEEK()	DAYOFWEEK函数返回日期的工作日索引值，即星期日为1，星期1为2，星期6为7。例:DAYOFWEEK('2019-05-09') 返回 5
DATE()	提取日期或日期/时间表达式的日期部分,格式'YYYY-MM-DD'或者'YYYYMMDD'
DATE_ADD(date,INTERVAL expr unit)	给日期添加指定的时间间隔。date 参数是合法的日期表达式,expr 参数是您希望添加的时间间隔,type 参数可以是MySQL支持的时间日期相关类型值
CURDATE()	返回当前日期 例:'2019-05-09'

VO Entity Class (Interface Form)

public interface CoursePlanVos{
    Integer getId();
    Integer getWeek();
    String getMusicType();
    String getTeacherName();
    Date getStartTime() ;
    Date getEndTime();
}

Result set form

@Query(value = "select count(s.id) as count,m.name as name " +
             " FROM t_musictype m  left JOIN t_student s on s.music_type_id = m.id group by m.id ",nativeQuery = true)
    List<Object[]> listType1();

Compared with the first method, using native SQL will return Object array by default

Spring Data Jpa Custom repository to DTO

In the recent project, we need to associate several tables and then turn out the fields. Record the following here. I feel that what I write on the Internet is not standardized and clear.

@Entity
@SqlResultSetMapping(
        name="TestMapping",
        entities = {
                @EntityResult(
                        entityClass = com.xxx.xx.data.model.TestEntity.class,
                        fields = {
                                @FieldResult(name="id",column="id"),
                                @FieldResult(name="localTime",column="time"),
                                @FieldResult(name="maximumAppointment",column="maxAppointment"),
                        }
                )
        }
)
@NamedNativeQuery(name="getTestQuery",
        query="select tableC.id as id,tableB.time,tableC.maximumAppointment as maxAppointment from tableB " +
                "                              inner join tableA on tableA.id = tableB.tableAId " +
                "                              inner join tableC on tableB.id = tableC.tableBId " +
                "                              inner join custom on custom.id = tableA.customId " +
                "where " +
                "   tableA.locationId = :locationId" +
                "   and custom.id = :customId" +
                "   and tableB.deleted = false ", resultSetMapping="TestMapping")
@Data
public class TestEntity {
    @Id
    private String id;
    private LocalTime localTime;
    private Integer maximumAppointment;
}

Interfaces need to be declared:

@Repository
public interface TestEntityRepository extends JpaRepository<TestEntity,String> {
    @Query(name="getTestQuery")
    List<TestEntity> getTestQuery(String locationId, String customId);
}

If you don't want to declare an interface, you can use EntityManager to implement it.

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();