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<ResidentDto> findAvgDate() {
> LOGGER.debug("find all avg(Date): "); return
> namedParameterJdbcTemplate.query(findAllAvgDateSQL, rowMapper);}*
>
> *And my test method:*
>
>
>
>
>
>
> *@Testpublic 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='[email protected]', avgTime=null}*
>
> *ResidentDto{residentId=2, firstName='Margaret', lastName='Mitchell',
> email='[email protected]', avgTime=null}*
>
> *ResidentDto{residentId=3, firstName='Den', lastName='Brown',
> email='[email protected]', avgTime=null}*
>
> *ResidentDto{residentId=4, firstName='Erih', lastName='Remark',
> email='[email protected]', 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, [email protected]:
>
>
>> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/eb327314-3baa-42b0-975d-07532fe98966n%40googlegroups.com.
