Shulou(Shulou.com)06/02 Report--

This article mainly introduces how to deal with some fields in the return table of springboot jpa. It is very detailed and has a certain reference value. Friends who are interested must read it!

Springboot jpa returns some fields in the table

Using springboot jpa to operate the database can speed up our development efficiency, for simple crud operations, using jpa to develop is not too cool, but to be honest, it is not so cool for some complex databases to operate jpa.

In the development of many times we may want to return only a part of the database table or a class of fields, this if you use mybatis, then it is very simple, directly in the sql select field is good, the specification point on the data transfer class to pick up, then directly use map to get lazy. But such a small operation can not be so direct in jpa.

A lot of nonsense (⊙ _ ⊙) the following is how I deal with some of the returned fields in jpa.

/ * time statistics of booked rooms * / public interface RoomOrderTimeRepository extends JpaRepository {@ Query ("select new com.ddzrh.dto.RoomOrderTimeOutPut (r.orderTime count (r.orderTime))" + "from RoomOrderTime as r" + "where r.roomTypeId =: roomId" + "GROUP BY r.orderTime") List queryRoomOrderTime (@ Param ("roomId") Integer roomId);}

After reading the above code, you can guess, yes, there is no good way, the data I am about to return are encapsulated in the RoomOrderTimeOutPut class.

@ Datapublic class RoomOrderTimeOutPut {private Date orderTime; private Long orderNum; public RoomOrderTimeOutPut (Date orderTime, Long orderNum) {this.orderTime = orderTime; this.orderNum = orderNum;}}

Like the above code, I want to query the number of reservations for a room at a certain time, so I encapsulate the scheduled time and number I want to return into RoomOrderTimeOutPut and write a constructor that takes these two fields as input parameters, which must be written. See that sql in Query has a new RoomOrderTimeOutPut operation, and here the constructor is called to pass in the values found in the database according to the input parameters of the constructor.

JPA Custom return Field

Entity class: User.java

@ Data@Accessors (chain = true) @ EqualsAndHashCode (callSuper = true) @ Entity@Table (name = "user") public class User extends AbstractEntity {@ Column (unique = true) private String username; private String password; private String nickname; private String email; @ Column (name = "org_id") private Long orgId; @ Column (name = "org_name") private String orgName;}

DTO class: UserDTO.java

Import lombok.Value; / * * @ author wu qilong * @ date 2019-4-11 * @ Description: custom return value. The field name should be the same as that in the User entity class, plus lombok.@Value annotation. * / @ Valuepublic class UserDTO {private String username; private String nickname; private String email;}

Repository class: UserRepository.java

/ * @ author Wu Qilong * @ date 2019-4-11 * @ Description: * / public interface UserRepository extends JpaRepository {/ * * user name query * * @ param username * @ return * / Optional findByUsername (String username); / * * user name query * @ param username * @ return * / Optional findByUsername (String username, Class type);}

Test:

@ RunWith (SpringRunner.class) @ SpringBootTestpublic class UoServerApplicationTests {@ Autowired UserRepository userRepository; @ Testpublic void contextLoads () {UserDTO userDTO = userRepository.findByUsername ("wuqilong", UserDTO.class). Get (); Console.log (userDTO);}}

Note: the returned DTO attribute name needs to be the same as the field name in the entity class, and the @ Value annotation in the lombok package is convenient for fields that only need to return part of the table. If you need to customize the returned field name, you can refer to the following method:

Demand: count the number of registrants

Define a return object

Query using @ Query (value = "select new com.wd.cloud.uoserver.dto.TjVO (u.orgId, count (u.orgId)) from User u group by orgId").

@ Valuepublic class TjVO {Long orgId; Long registerCount;} / * Registration by institution * @ param pageable * @ return * / @ Query (value = "select new com.wd.cloud.uoserver.dto.TjVO (u.orgId, count (u.orgId)) from User u group by orgId") Page tjByOrgId (Pageable pageable)

Or you can use the following methods:

Define an interface and use getXxx to define the return field. Xxx needs to be the same as the alias of the returned field.

If inconsistent, you can use @ Value in the org.springframework.beans.factory.annotation package for mapping

Public interface TjDTO {/ * Organization * @ return * / Long getOrgId (); / * * Registration time * @ return * / String getRegisterDate (); / * * number of registrations * @ return * / String getRegisterCount () / * number of administrators when the alias does not match the getXXX name, you can use this annotation to adjust * @ return * / @ Value ("# {target.adminCount}") Long getManagerCount ();}

Repository class: UserRepository.java add method tjByOrgId (), which returns orgId and registerCount

/ * * Registration by day * @ return * / @ Query (value = "select DATE_FORMAT (gmt_create,\"% Y-%m-%d\ ") as registerDate,count (*) as registerCount from user group by registerDate", nativeQuery = true) List tj () / * * Statistics of registrations by institution * @ param pageable pagination * @ return * / @ Query (value = "select org_id as orgId,count (*) as registerCount from user group by orgId", countQuery = "select count (*) from user group by org_id", nativeQuery = true) Page tjByOrgId (Pageable pageable)

Test:

@ RunWith (SpringRunner.class) @ SpringBootTestpublic class UoServerApplicationTests {@ Autowired UserRepository userRepository; @ Testpublic void contextLoads () {List tjDTOList = userRepository.tj (); tjDTOList.forEach (tjDTO-> {Console.log ("registerDate= {}, registerCount= {}", tjDTO.getRegisterDate (), tjDTO.getRegisterCount ());});})

Result log:

Hibernate: select DATE_FORMAT (gmt_create, "% Y-%m-%d") as registerDate,count (*) > as registerCount from user group by registerDate

RegisterDate=2019-01-2828 registerCountkeeper 7393

RegisterDate=2019-03-07 registerCountkeeper 1

Demand: according to the organization group, the total number of statistics institutions and the number of users with type 2

@ Componentpublic class SpecBuilder {@ PersistenceContext private EntityManager entityManager; public List tj (Long orgId) {CriteriaBuilder cb = entityManager.getCriteriaBuilder (); CriteriaQuery query = cb.createQuery (Object [] .class); Root root = query.from (User.class); / / stitching where condition List predicateList = new ArrayList () If (orgId! = null) {predicateList.add (cb.equal (root.get ("orgId"), orgId));} / / plus where condition query.where (ArrayUtil.toArray (predicateList, Predicate.class)) Query.multiselect (root.get ("orgId"), cb.count (root), cb.sum (cb.selectCase (). When (cb.equal (root.get ("userType"), 2), 1). Otherwise (0)); query.groupBy (root.get ("orgId")) / / final sql: select org_id,count (id), sum (case when user_type=2 then 1 else 0 end) from user where org_id=?1 group by org_id; TypedQuery typedQuery = entityManager.createQuery (query); return typedQuery.getResultList ();}}

Test:

@ RunWith (SpringRunner.class) @ SpringBootTestpublic class UoServerApplicationTests {@ Autowired SpecBuilder specBuilder; @ Testpublic void contextLoads () {List tjDTOList1 = specBuilder.tj (169L); tjDTOList1.forEach (tjDTO-> {Console.log ("orgId= {}, total number = {}, administrators = {}", tjDTO [0], tjDTO [1], tjDTO [2]);});}}

The above is all the content of the article "how to deal with some fields in the return table of springboot jpa". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.