Now everything is okey. I should added alies for AVG function. :(
вторник, 23 марта 2021 г. в 13:48:55 UTC+3, Виталий Пунько:
>
> 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 findAvgDate() {
> LOGGER.debug("find all avg(Date): "); return
> namedParameterJdbcTemplate.query(findAllAvgDateSQL, rowMapper);}*
>
> *And my test method:*
>
>
>
>
>
>
> *@Testpublic void findAvgDateTest() { List 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.
>
>
> *[image: Снимок экрана от 2021-03-23 13-42-47.png]*
> вторник, 23 марта 2021 г. в 06:23:46 UTC+3, and...@manticore-projects.com:
>
>
>> 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
>>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/eb327314-3baa-42b0-975d-07532fe98966n%40googlegroups.com.
