Spring Data Jpa返回自定义对象的三种方法

tasks表对应的Entity

@Entity
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "tasks")
@Data
public class Tasks extends BaseEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int taskId;
    private Integer websiteId;
    private String status;
    private String lastOperator;
    private String lastOperationTime;
    private String jobId;
    private int retryTimes;
}

websites表对应的Entity

@Entity
@Table(name = "websites")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Websites extends BaseEntity{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int websiteId;
    private String country;
    private String websiteName;
    private String baseUrl;
    private String smartphoneUrl;
    private String tabletUrl;
    private String smartdeviceUrl;
    private String websiteCategory;
    private String webtypeRefreshRate;
    private String urlRefreshRate;
    private String configTime;
    private String configDesc;
}

自定义对象

@Data
@NoArgsConstructor
@AllArgsConstructor
public class CustomizedDto implements Serializable {
    private static final long serialVersionUID = -7242005560621561106L;
    private String country;
    private String websiteName;
    private String baseUrl;
    private String status;
}

方法一、简单查询直接new对象

        使用hql，将结果返回到new出来的自定义对象中，注意，自定义对象中要有对应的构造函数。

@Query(value = "select new com.bigdata.mrcrawler.dto.CustomizedDto(w.country,w.websiteName,w.baseUrl,t.status) " +
            "from Websites w join Tasks t on w.id=t.websiteId " +
            "where t.status=?1")
    List<CustomizedDto> getByStatus1(String status);

        但是这个方法只使用于简单的查询语句，如果遇到复杂查询需要使用原生SQL（即nativeQuery=true）时， 此方法不适用，会抛出如下异常。

@Query(value = "select w.country as country,w.website_name as websiteName,w.base_url as baseUrl,t.status as status " +
            "from websites w join tasks t on w.website_id=t.website_id " +
            "where t.status=?1",nativeQuery = true)
    List<CustomizedDto> getByStatus2(String status);

No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [com.bigdata.mrcrawler.dto.CustomizedDto]

方法二、Service层使用EntityManager

        直接在Service层使用EntityManager进行查询，可以自由组装各种复杂sql。

    public List<CustomizedDto> t(String param) {
        String sql = "select w.country as country,w.website_name as websiteName,w.base_url as baseUrl,t.status as status " +
                "from websites w join tasks t on w.website_id=t.website_id " +
                "where t.status='" + param + "'";
        List<CustomizedDto> res = entityManager.createNativeQuery(sql).getResultList();
        return res;
    }

        但是会有sql注入问题，例如：param传入Running' or 1=1 --\t  上述查询会将查询整个数据表。解决方法如下，使用预编译防止sql注入问题。

    public List<CustomizedDto> t(String param) {
        String sql = "select w.country as country,w.website_name as websiteName,w.base_url as baseUrl,t.status as status " +
                "from websites w join tasks t on w.website_id=t.website_id " +
                "where t.status=:param" ;
        Query nativeQuery = entityManager.createNativeQuery(sql);
        nativeQuery.setParameter("param", param);
        List<CustomizedDto> res  = nativeQuery.getResultList();
        return res;
    }

        然而，个人很不喜欢这种代码中嵌入大片大片sql的写法，排查问题的时候看得头疼（别问，问就是被坑过/捂脸.jpg/）。所以方法二即便可行，我私心还是不想推荐。

方法三、Dao层使用Map接收自定义对象

        使用List<Map> 接收返回结果，无论是原生sql还是hql都支持，当然也支持分页，只需要把返回对象改为Page<Map>即可，其他操作与普通分页没有差别。

@Query(value = "select w.country as country,w.website_name as websiteName,w.base_url as baseUrl,t.status as status " +
            "from websites w join tasks t on w.website_id=t.website_id " +
            "where t.status=?1",nativeQuery = true)
    List<Map> getByStatus3(String status);

        Service层也需要用List<Map>进行接收。 

    public List<Map> t(String param) {
        return testRepository.getByStatus3(param);
    }

        如果后续还有其他操作，还是需要转成自定义对象怎么办，毕竟Map操作起来挺麻烦的。可以用如下解决方案，先用Object进行接收，然后强转成自定义对象List。

    public List<CustomizedDto> t(String param) {
        Object data = testRepository.getByStatus3(param);
        return  (List<CustomizedDto>)data;
    }

        强转需要注意自定义对象和数据库中字段类型的强一致性，如数据库中datetime类型，自定义对象对应的字段必须是Date，不能是String，否则转换的时候会有问题，数据会丢失。