英文:
Spring Boot custom query group by and count
问题
以下是您要翻译的内容:
"I'm having a problem with accessing data from a custom query.
Here is the POJO:
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "run_list")
public class RunList {
@Id
@Column(name = "id")
private Long id;
@Column(name = "control_run_name")
private String serverName;
@Column(name = "control_run_date")
private Date controlRunDate
<.. bunch of other fields .. >
Here is the repository:
public interface RunListRepository extends JpaRepository<RunList, Long> {
@Query("SELECT u.serverName,count(u) as controlRunCount from RunList u where u.controlRunDate < :lastUploadDate group by u.serverName")
List<RunList> findAllControlRunAfterDate(@Param("lastUploadDate") Date lastUploadDate);
In the controller I invoke the repository method like so:
Date date = new SimpleDateFormat("yyyy-MM-dd").parse("2020-03-01");
model.addAttribute("runList", runListRepository.findAllControlRunAfterDate(date);
The binding to the date in the query works ok I get a first result of the group by so that is solved. The thing is I get an error while runing this :
Failed to convert from type[java.lang.Object[]] to type [@org.springframework.data.jpa.Query my.package.name.RunList} for value '{server1,14}';
When I use the SQL query against the database from it's CLI I get a nice grouping of the serverName and the count.
I'm guessing the problem is in the conversion of the count field that is popping out of nowhere from the repository method and Spring doesn't know what to link this too.
I've tried to use a RunListDTO here that has only a String serverName and an Integer controlRunCount with no luck - the repository interface didn't like me using a DTO in the output of a method used in a interface created with <RunList,Long>.
Is there a way to make this a custom Array/Object in flight when the repository does the count?
Regards,
Jarek."
英文:
I'm having a problem with accessing data from a custom query.
Here is the POJO:
@Getter
@Setter
@NoArgsContructor
@AllArgsConstructor
@Entity
@Table(name = "run_list")
public class RunList {
@Id
@Column(name = "id")
private Long id;
@Column(name = "control_run_name"
private String serverName;
@Column(name = "control_run_date"
private Date controlRunDate
<.. bunch of other fields .. >
Here is the repository:
public interface RunListRepository extends JpaRepository<RunList, Long> {
@Query("SELECT u.serverName,count(u) as controlRunCount from RunList u where u.controlRunDate < :lastUploadDate group by u.serverName")
List<RunList> findAllControlRunAfterDate(@Param("lastUploadDate") Date lastUploadDate);
In the controller I invoke the repository method like so:
Date date = new SimpleDateFormat("yyyy-MM-dd").parse("2020-03-01");
model.addAttribute("runList",runListRepository.findAllControlRunAfterDate(date);
The binding to the date in the query works ok I get a first result of the group by so that is solved. The thing is I get an error while runing this :
Failed to convert from type[java.lang.Object[]] to type [@org.springframework.data.jpa.Query my.package.name.RunList} for value '{server1,14}';
When I use the SQL query against the database from it's CLI I get a nice grouping of the serverName and the count.
I'm guessing the problem is in the conversion of the count field that is popping out of nowhere from the repository method and Spring doesn't know what to link this too.
I've tried to use a RunListDTO here that has only a String serverName and an Ingeter controlRunCount with no luck - the repository interface didn't like me using a DTO in the output of a method used in a interface created with <RunList,Long>.
Is there a way to make this a custom Array/Object in flight when the repository does the count?
Regards,
Jarek.
答案1
得分: 5
由于您正在选择一个字段和一个计数,因此无法将其映射到您的实体,因此此查询返回了您指定的值的数组,就像您在异常中看到的:'{server1,14}'。
public interface RunListRepository extends JpaRepository<RunList, Long> {
@Query("SELECT u.serverName,count(u) as controlRunCount from RunList u where u.controlRunDate < :lastUploadDate group by u.serverName")
List<Object[]> findAllControlRunAfterDate(@Param("lastUploadDate") Date lastUploadDate);
}
因此,在您的服务中,您可以像这样处理这些数据:
List<Object[]> list = runListRepository.findAllControlRunAfterDate(yourDate);
for (Object[] ob : list){
String serverName = (String)ob[0];
Integer count = (Integer)ob[1];
}
英文:
Since you are selecting a field and a count there is no way it can be mapped to your entity, so this query returns an array of values you specified, like you can see in the exception: '{server1,14}'.
public interface RunListRepository extends JpaRepository<RunList, Long> {
@Query("SELECT u.serverName,count(u) as controlRunCount from RunList u where u.controlRunDate < :lastUploadDate group by u.serverName")
List<Object[]> findAllControlRunAfterDate(@Param("lastUploadDate") Date lastUploadDate);
So in your service you can work with this data like:
List<Object[]> list = runListRepository.findAllControlRunAfterDate(yourDate);
for (Object[] ob : list){
String serverName = (String)ob[0];
Integer count = (Integer)ob[1];
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论