Spring Boot自定义查询,按条件分组并计数。

huangapple go评论78阅读模式
英文:

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 = &quot;run_list&quot;)    
public class RunList {
    @Id
    @Column(name = &quot;id&quot;)
    private Long id;
    
    @Column(name = &quot;control_run_name&quot;
    private String serverName;
    
    @Column(name = &quot;control_run_date&quot;
    private Date controlRunDate

&lt;.. bunch of other fields .. &gt;

Here is the repository:

public interface RunListRepository extends JpaRepository&lt;RunList, Long&gt; {

    @Query(&quot;SELECT u.serverName,count(u) as controlRunCount from RunList u where u.controlRunDate &lt; :lastUploadDate group by u.serverName&quot;)
    List&lt;RunList&gt; findAllControlRunAfterDate(@Param(&quot;lastUploadDate&quot;) Date lastUploadDate);

In the controller I invoke the repository method like so:

Date date = new SimpleDateFormat(&quot;yyyy-MM-dd&quot;).parse(&quot;2020-03-01&quot;);
model.addAttribute(&quot;runList&quot;,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 &#39;{server1,14}&#39;;

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&lt;RunList, Long&gt; {

    @Query(&quot;SELECT u.serverName,count(u) as controlRunCount from RunList u where u.controlRunDate &lt; :lastUploadDate group by u.serverName&quot;)
    List&lt;Object[]&gt; findAllControlRunAfterDate(@Param(&quot;lastUploadDate&quot;) Date lastUploadDate);

So in your service you can work with this data like:

List&lt;Object[]&gt; list = runListRepository.findAllControlRunAfterDate(yourDate);
for (Object[] ob : list){
    String serverName = (String)ob[0];
    Integer count = (Integer)ob[1];
}

huangapple
  • 本文由 发表于 2020年4月6日 20:00:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/61059344.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定