springboot jpa 实现返回结果自定义查询
目录
- jpa返回结果自定义查询
- 第一种方法
- 第二种方法
- 使用jpa两张表联查返回自定义实体
- 1、创建一个SpringBoot空白项目,引入pom依赖
- 2、application.yml配置文件
- 3、数据库(有两张表user/address)
- 4、User.java和Address.java
- 5、UserDaoRepository.java和AddressDaoRepository.java
- 6、UserAddressDto.java代码
- 7、TestController.java
jpa 返回结果自定义查询
这里自定义的实体是没有在数据映射的实体。可以返回聚合函数的值。(个人理解可以。。。。。。)
第一种方法
实体类。(这里本人使用的是idea)
@Data public class NameOnlyDto implements Serializable { private String firstName; private String lastName; private String sex ; }
repository类(这里不是 使用的继承jpa中的 的方式。而是使用的EntityManager)。代码中有详细的注释。
@Repository public class CustomEntity { // 实体管理器EntityManager是负责管理Entity的对象。对Entity的操作包括添加、删除、修改和查询,都是通过实体管理器来实现的。 @PersistenceContext private EntityManager entityManager; //EntityManagerFactory @Transactional public List<NameOnlyDto> listNameOnlyDto(){ String sql = "select p.last_name lastName, p.first_name firstName from PERSON p"; // hibernate 5.2 之前 // SQLQuery sqlQuery = entityManager.createNativeQuery(sql).unwrap(NativeQueryImpl.class); // hibernate 5.2 之后的 写法 // unwrap 让JPA的Query返回Map对象 javax.persistence.Query.unwrap // hibernate 或jpa 中使用 AliasToBeanResultTransformer 自定义类型转换ResultTransformer 下划线转驼峰 SQLQuery sqlQuery = entityManager.createNativeQuery(sql).unwrap(NativeQueryImpl.class); /*Query query = sqlQuery.setResultTransformer(Transformers.aliasToBean(NameOnlyDto.class)); List<NameOnlyDto> list = query.getResultList();//.list();*/ Query query = sqlQuery.setResultTransformer(Transformers.aliasToBean(NameOnlyDto.class)); List<NameOnlyDto> list =query.getResultList(); entityManager.clear(); return list; } }
OK。就这样就可以了。个人测试似乎在oracle数据库 不行。可能是sql查询结果不太一样
第二种方法
可以在特定的场合使用。
自定义类
public class NameOnlyDtoT implements Serializable { private String firstName; private String lastName; // private String address; private String sex; //构造函数 特殊注意 这里返回几个字段就要有几个字段的构造参数 感觉不太合适的地方 public NameOnlyDtoT(String firstName, String lastName) { this.firstName = firstName; this.lastName = lastName; } /* public NameOnlyDtoT(String firstName, String lastName, String sex) { this.firstName = firstName; this.lastName = lastName; this.sex = sex; }*/ }
这里是repository中的写法
// 切记不能使用@query.如果使用@query 个人感觉和上面那种方法是一样的逻辑 List<T> findByLastName(String lastname,Class<T> type); 使用(这里注意的是NameOnlyDtoT 中字段能赋值的只能是person中 含有的字段。这是个人觉得鸡肋的存在) public List<NameOnlyDtoT> testNameOnlyDtoTS(){ List<NameOnlyDtoT> nameOnlyDtoTS= personRepository.findByLastName("哈哈", NameOnlyDtoT.class); return nameOnlyDtoTS; }
使用jpa两张表联查返回自定义实体
在java开发中,用Jpa框架做连表查询时(需要返回两张表的各自部分字段),在返回对象的过程中感觉比较棘手,一直没有一个好的解决方案,网上也有各种版本的方法,下面的方法本人感觉最方便使用
1、创建一个SpringBoot空白项目,引入pom依赖
先看项目结构,为了简化,没有引入service层,直接使用controller调用dao层
pom.xml配置
<!-- web依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- lombok依赖 --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.8</version> </dependency> <!-- jpa依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <!-- mysql依赖 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>
2、application.yml配置文件
server: port: 13333 spring: datasource: url: jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull username: root password: 12345678 driver-class-name: com.mysql.jdbc.Driver jpa: show-sql: true hibernate: ddl-auto: none
3、数据库(有两张表user/address)
我们现在需要联查user和address表,address表中的user_id是和user表中id是做关联查询
4、User.java 和 Address.java
5、UserDaoRepository.java 和 AddressDaoRepository.java
附上UserDaoRepository.java的代码
package com.lss.jpa.dao; import com.lss.jpa.entity.dto.UserAddressDto; import com.lss.jpa.entity.po.User; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Query; import java.util.List; import java.util.Map; public interface UserDaoRepository extends JpaRepository<User, Integer> { @Query(value = "select \"title\" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id", nativeQuery = true) public List<UserAddressDto> findAllUserAddress(); @Query(value = "select \"title\" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id and u.id=1", nativeQuery = true) public UserAddressDto findAllUserAddressById(); @Query(value = "select \"title\" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id and u.id=1", nativeQuery = true) public Map<String, Object> findAllUserAddressByMap(); }
6、UserAddressDto.java代码
package com.lss.jpa.entity.dto; public interface UserAddressDto { Integer getId(); String getName(); String getAddressName(); Integer getAddressId(); String getCommon(); }
此处我们自定义了UserAdressDto来接收两张表返回的数据,注意:此时创建的是一个interface,并且里面的字段是用get的形式创建的接收参数
7、TestController.java
package com.lss.jpa.web; import com.lss.jpa.dao.UserDaoRepository; import com.lss.jpa.entity.dto.UserAddressDto; import com.lss.jpa.entity.po.User; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; import java.util.Map; import java.util.concurrent.locks.Lock; import java.util.concurrent.locks.ReentrantLock; @RestController @Slf4j public class TestController { @Autowired private UserDaoRepository userDaoRepository; @GetMapping("test") public String test(){ List<UserAddressDto> all = userDaoRepository.findAllUserAddress(); all.stream().forEach(dto -> { log.info("result: id:{}, name:{}, addressId:{}, addressName:{}, common:{}", dto.getId(), dto.getName(), dto.getAddressId(), dto.getAddressName(), dto.getCommon()); }); UserAddressDto dto = userDaoRepository.findAllUserAddressById(); log.info("result: id:{}, name:{}, addressId:{}, addressName:{}, common:{}", dto.getId(), dto.getName(), dto.getAddressId(), dto.getAddressName(), dto.getCommon()); Map<String, Object> map = userDaoRepository.findAllUserAddressByMap(); log.info("map:{}", map); List<User> userList = userDaoRepository.findAll(); log.info("userList:{}", userList); return "ok"; } }
最后,启动项目,调用/test接口
curl http://localhost:13333/test
看console里打印结果
Hibernate: select "title" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id
2020-02-23 13:14:33.293 INFO 2816 --- [io-13333-exec-3] com.lss.jpa.web.TestController : result: id:1, name:zhangsan , addressId:1, addressName:beijing, common:title
2020-02-23 13:14:33.293 INFO 2816 --- [io-13333-exec-3] com.lss.jpa.web.TestController : result: id:2, name:lisi, addressId:2, addressName:tianjin, common:title
Hibernate: select "title" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id and u.id=1
2020-02-23 13:14:33.296 INFO 2816 --- [io-13333-exec-3] com.lss.jpa.web.TestController : result: id:1, name:zhangsan , addressId:1, addressName:beijing, common:title
Hibernate: select "title" as common, u.id as id, u.name as name, a.id as addressId, a.address as addressName from user u, address a where u.id = a.user_id and u.id=1
2020-02-23 13:14:33.299 INFO 2816 --- [io-13333-exec-3] com.lss.jpa.web.TestController : map:org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap@72cce81
Hibernate: select user0_.id as id1_1_, user0_.name as name2_1_ from user user0_
2020-02-23 13:14:33.305 INFO 2816 --- [io-13333-exec-3] com.lss.jpa.web.TestController : userList:[User(id=1, name=zhangsan ), User(id=2, name=lisi), User(id=3, name=wangwu), User(id=4, name=zhaoliu)]
我们可以拷到输出的sql和联查出来的数据结果,都被dto完美接收
特别注意,接收的dto一定要是interface,里面的参数要写成get形式的方法体,这样jpa在查询到数据后,会自动映射到interface里,通过调用get的方法体相当于调用了参数值,这样就会把数据取出来
以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。