Don't get AVG(DareDiff) field in java and H2

[Виталий Пунько]

Hi. I need help. 
I have a table with two columns Date in H2 db.

And I have the same class in Java. With one add field for avgFunction.

But this field is always null. I dont know what to do. 
SQL query is correct i think. Becaise. I checked in DBeaver,  And I have one test for get AVG(DareDiff) by Id. And this test return me correct value. 

[Andreas Reichel]

Vitali,

while I am not a H2 developer, it appears from your e-mail that your challenge is related to your persistence framework but not to the H2 database itself.

You wrote that you can run your query against the JDBC data source and would get the correct result for AVG().

If the persistence frame work returns AVG() == NULL then I would double check first for the actual selected rows before aggregation.

Aggregate functions will return NULL when no rows have been selected.

Also it might worth replacing H2 with another RDBMS (Derby, Postgres) temporarily just for the sake of narrowing down the issue.

Right now I can only advise:

1) send your questions to the persistence framework as long as your query works correctly (or unless you come up with a DDL and a query which fails directly on H2)

2) send (short, simplified, reproducible) code examples instead of screen shots, because nobody will be able to walk through the code using screenshots only

Best regards

Andreas 

[Виталий Пунько]

Thanks fo your answer. 

I still have a problem. 

This is my entity class:

public class ResidentDto {
private Integer residentId;
private String firstName;
private String lastName;
private String email;
private Long avgTime;
public ResidentDto() {}
public ResidentDto(String firstName, String lastName, String email) {
this.firstName = firstName;
this.lastName = lastName;
this.email = email;     }  getters and setters

This is property:

resident.avg.diff.time=SELECT RESIDENT_ID, FIRSTNAME, LASTNAME, EMAIL,  AVG(DATEDIFF('DAY',  ARRIVAL_TIME, DEPARTURE_TIME,)) FROM RESIDENT GROUP BY RESIDENT_ID, FIRSTNAME, LASTNAME, EMAIL

My method:

public class ResidentDtoDaoJdbc implements ResidentDaoDto {
@Value("${resident.avg.diff.time}")
private String findAllAvgDateSQL;

private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private RowMapper rowMapper = BeanPropertyRowMapper.newInstance(ResidentDto.class);

private static final Logger LOGGER = LoggerFactory.getLogger(ResidentDaoJdbc.class);

public ResidentDtoDaoJdbc(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
}
public List<ResidentDto> findAvgDate() {
LOGGER.debug("find all avg(Date): ");
return namedParameterJdbcTemplate.query(findAllAvgDateSQL, rowMapper);}

And my test method:

@Test
public void findAvgDateTest() {
List<ResidentDto> residentList = residentDaoDto.findAvgDate();
Assertions.assertNotNull(residentList);
Assertions.assertTrue(residentList.size() > 0);
}

I have list's value like this: 

ResidentDto{residentId=1, firstName='Stephen', lastName='King', email='steph...@test.com', avgTime=null}

ResidentDto{residentId=2, firstName='Margaret', lastName='Mitchell', email='margaret...@test.com', avgTime=null}

ResidentDto{residentId=3, firstName='Den', lastName='Brown', email='denb...@test.com', avgTime=null}

ResidentDto{residentId=4, firstName='Erih', lastName='Remark', email='rem...@test.com', avgTime=null}

Only one scrin, that I have correct output  in DBeaver .

Also, I use H2 in Intellij from memory. 

вторник, 23 марта 2021 г. в 06:23:46 UTC+3, and...@manticore-projects.com:

[Виталий Пунько]

Now everything is okey. I should added alies for AVG function. :(

вторник, 23 марта 2021 г. в 13:48:55 UTC+3, Виталий Пунько: