亲宝软件园·资讯

展开

Spring Data Jpa返回自定义对象

我是坏人哦 人气:0

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,否则转换的时候会有问题,数据会丢失。 

总结

加载全部内容

相关教程
猜你喜欢
用户评论