spring data jpa中的DTO查询

昨天收到一封邮件，问了我一个问题，问题如下：

SELECT COUNT(user.id),user_name FROM USER where user_name like "%?%" GROUP BY user_name,id类似这种的sql用spring data jpa怎么写？

下面就这个问题一起来探讨下，怎么解决？

1、新建Entity

package com.chhliu.springboot.jpa.entity; import java.io.Serializable; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; @Entity @Table(name="t_user") public class User implements Serializable{ /** * */ private static final long serialVersionUID = 1L; @Id() @GeneratedValue(strategy = GenerationType.AUTO) private int id; private String name; private String address; private int age; ……省略getter、setter方法……  }2、编写DTO

package com.chhliu.springboot.jpa.dto; import java.io.Serializable; public class UserDto implements Serializable{ /** * */ private static final long serialVersionUID = 1L; private Long num; private String name; public UserDto() { super(); } public UserDto(Long num, String name) { super(); this.num = num; this.name = name; } ……省略getter、setter方法…… /** * attention: * Details：TODO * @author chhliu */ @Override public String toString() { return "UserDto [num=" + num + ", name=" + name + "]"; } } 方案一：使用@Query

1、编写Repository接口

package com.chhliu.springboot.jpa.repository; import java.util.List; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.Query; import com.chhliu.springboot.jpa.dto.UserDto; import com.chhliu.springboot.jpa.entity.User; public interface UserRepository extends JpaRepository<User, Integer> { @Query("select new com.chhliu.springboot.jpa.dto.UserDto(count(u.id), u.name) from User u where u.name like %:name% group by u.name, u.id)")     public List<UserDto> findByAndGroupByHql(@Param("name") final String name); } 2、测试类

List<UserDto> list1 = repository.findByAndGroupByHql("chh");         if(null != list1 && !list1.isEmpty()){             for(UserDto list:list1){                 System.out.println(list);             }         }3、测试结果

Hibernate: select count(user0_.id) as col_0_0_, user0_.name as col_1_0_ from t_user user0_ where user0_.name like '%chh%' group by user0_.name , user0_.id UserDto [num=1, name=chhliu] UserDto [num=1, name=chhliuxyh] 方案二：使用原生的EntityManager

1、编写Repository管理类

package com.chhliu.springboot.jpa.repository; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import javax.persistence.criteria.CriteriaBuilder; import javax.persistence.criteria.CriteriaQuery; import javax.persistence.criteria.Predicate; import javax.persistence.criteria.Root; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.jpa.domain.Specification; import org.springframework.stereotype.Service; import com.chhliu.springboot.jpa.dto.NumAndAddressDto; import com.chhliu.springboot.jpa.dto.UserDto; import com.chhliu.springboot.jpa.entity.User; @Service public class UserRepositoryManager { @Autowired @PersistenceContext private EntityManager entityManager;// 注入EntityManager实例 public List<UserDto> findByAndGroup(final String name){ List<UserDto> list = entityManager .createQuery("select new com.chhliu.springboot.jpa.dto.UserDto(count(u.id), u.name) from User u where u.name like '%"+name+"%' group by u.name, u.id") .getResultList(); return list; } } 测试结果同上

方案三：使用CriteriaQuery查询

public List<UserDto> findAndReturnDto(final String address) { /* * 查询出地址，数量并根据地址分组，同时分组的数量大于2的所有结果集 * 下面的Criteria对应的完整sql语句如下： * select count(u.id) as num, u.address as address from t_user u where u.address = address group by u.address having count(u.id)>2 */ CriteriaBuilder builder = entityManager.getCriteriaBuilder(); // 获取builder CriteriaQuery<UserDto> query = builder.createQuery(UserDto.class);// 创建查询 Root<User> root = query.from(User.class);// 构建Root query.multiselect(builder.count(root.get("id")), root.get("address"))// 多条件查询 .where(builder.equal(root.get("address"), address))// where子句 .groupBy(root.get("address")).having(builder.gt(builder.count(root.get("id")),2)); return entityManager.createQuery(query).getResultList(); }测试结果如下：

UserDto [num=3, name=北京]