@Entity and @Column seem not working while findNative by aggregate sql

[magicliang]

Expected behavior

query some object by annotation-driven mapping, have result object with properties populated.

Actual behavior

The result is like:

leadsWebActionDataCountAggregates: [{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{}]

Steps to reproduce

we are using ebean to mapped result from clickhouse:

the sql is like:

select
  effective_action_type,
  CAST(min_action_time2 AS DATE) as effective_action_time,
  count(*) as action_count
from
  (
    select
      effective_action_type,
      sign_id,
      min(action_time2) as min_action_time2
    from
      (
        select
          sign_id,
          action_time2,
          CASE
            WHEN final_action_type = 'wx_1166' THEN '405'
            ELSE final_action_type
          END AS effective_action_type
        from
          leads_web_action_data
        where
          action_time2 between '2022-12-13 00:00:00' and '2022-12-27 23:59:59'
        order by
          action_time2 desc
      )
    group by
      effective_action_type,
      sign_id
  )
group by
  effective_action_type,
  CAST(min_action_time2 AS DATE)

and the java bean is like:

@Entity
@Data
public class LeadsWebActionDataCountAggregate {

    @Column(name = "effective_action_time")
    private Date effectiveActionTime;

    @Column(name = "effective_action_type")
    private Long effectiveActionType;

    @Column(name = "action_count")
    private Integer count;
}

the api invocation is like:

        Query<LeadsWebActionDataCountAggregate> basicQuery =
                getServer().findNative(LeadsWebActionDataCountAggregate.class,
                                nativeSql)
                        .setParameter("actionTimeBegin", condition.getActionTimeBegin())
                        .setParameter("actionTimeEnd", condition.getActionTimeEnd());
        return basicQuery.findList();

leadsWebActionDataCountAggregates: [{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{}]

[rbygrave]

It would be good to know:

• The Database and version being used
• The JDBC Driver and version being used
• The Ebean version being used

This feature uses jdbc metadata and so it can be impacted by the database and jdbc driver.

If you can create a minimal test case that reproduces the issue perhaps based off https://github.com/ebean-orm-examples/example-minimal ?

Side note: Although there isn't anything wrong with the approach here the same sort of thing can be done using DtoQuery. See https://ebean.io/docs/query/dtoquery . Take particular note of the mapping with respect to constructors. For example, in this example if the dto was a java record or class where the constructor took all 3 parameters then that would be used to map the result into the dto class.

[magicliang]

The Database and version being used: a clickhouse as OLAP engine.
The JDBC Driver and version being used: com.clickhouse:clickhouse-jdbc:0.3.2-patch11
The Ebean version being used：sbt: io.ebean:ebean:11.33.2:jar and sbt: com.typesafe.play:play-ebean_2.12:5.0.2:jar.

minimal test will be created later.

[magicliang]

And another question to be asked: If DotQuery is used, how can I specifiy field mappings over convention(No hint was found in doc)? Take this for example: I have a column named camel_style, I want to map it to realCamelStyle member variable?

[magicliang]

It would be good to know:

• The Database and version being used
• The JDBC Driver and version being used
• The Ebean version being used

This feature uses jdbc metadata and so it can be impacted by the database and jdbc driver.

If you can create a minimal test case that reproduces the issue perhaps based off https://github.com/ebean-orm-examples/example-minimal ?

Side note: Although there isn't anything wrong with the approach here the same sort of thing can be done using DtoQuery. See https://ebean.io/docs/query/dtoquery . Take particular note of the mapping with respect to constructors. For example, in this example if the dto was a java record or class where the constructor took all 3 parameters then that would be used to map the result into the dto class.

As I tried, the @Column(name = "effective_action_type") column mapping wouldn't work either. If the query result has a type order like String, Date, Long, member variables should comply with that order. Otherwise I got an exception:

javax.persistence.PersistenceException: Query threw SQLException:Text '204' could not be parsed at index 0 Query was: select
  effective_action_type,
  CAST(min_action_time2 AS DATE) as effective_action_time,
  count(*) as action_count
from
  (
    select
      effective_action_type,
      sign_id,
      min(action_time2) as min_action_time2
    from
      (
        select
          sign_id,
          action_time2,
          CASE
            WHEN final_action_type = 'wx_1166' THEN '405'
            ELSE final_action_type
          END AS effective_action_type
        from
          leads_web_action_data
        where
          action_time2 between '2022-12-13 00:00:00' and '2022-12-27 23:59:59'
        order by
          action_time2 desc
      )
    group by
      effective_action_type,
      sign_id
  )
group by
  effective_action_type,
  CAST(min_action_time2 AS DATE)

So, what's the best practice to use findDto with column mapping?

[rbygrave]

Ok, apologies for the delay here.

Edit:
This following comment is not accurate, please ignore.

// IGNORE THIS, IT IS INCORRECT
Looking at the tests and then the code the mapping of columns to properties is not actually using the naming convention (like underscore to camel case) but instead matching column alias to the property name. So instead of ` as effective_action_time` it's expecting ` as effectiveActionTime`

Have you looked to create a failing test case yet?

Note that your exception is not clear to me in that it does not include enough of the stack trace to understand what is throwing the exception.

[rbygrave]

Did you create a minimal test case that reproduces the issue perhaps based off https://github.com/ebean-orm-examples/example-minimal ?

[rbygrave]

DtoQuery mapping

And another question to be asked: If DotQuery is used, how can I specifiy field mappings over convention(No hint was found in doc)? Take this for example: I have a column named camel_style, I want to map it to realCamelStyle member variable?

Use a sql column alias ... and it can be either realCamelStyle or real_camel_style and case does not matter.

e.g using as realCamelStyle:

select camel_style as realCamelStyle, other_column, ...

e.g using as real_camel_style:

select camel_style as real_camel_style, other_column, ...

case does not matter

The way that database columns are mapped to logical java properties is by reading the column labels from the jdbc metaData.

We can't guarantee the case (upper case/lower case) that the database dictionary returns the column labels but generally for most databases this is coming back in upper case. For this reason when mapping of database columns to bean properties ebean internally makes all the database columns/labels returned upper case for mapping purposes.

Internally the mapping works with everything in upper case (database column labels and bean properties).

camel vs snake _

For Dto mapping from column label to bean property, given a label of "REAL_CAMEL_STYLE" the mapping will:

1. first look for a property entry to match "REAL_CAMEL_STYLE".
2. check if the db column label starts with IS_ and try to match trimming off the IS_ prefix
3. Remove underscores ... try to match a property using "REALCAMELSTYLE"

[rbygrave]

Nested SQL

To be explicit, when mapping ebean is using the jdbc resultSet metaData and so any nested sql has no impact. Only the top most select clause impacts the mapping as it defines what the jdbc resultSet metaData returns in terms of column names/labels.

-- top most select clause, this controls the column names / labels jdbc metaData returns
select .... 
from (
    -- nested sql has NO impact on mapping
   select ... 
)

[rbygrave]

I'm thinking that you got busy with stuff. I'll close this for now and you can just re-open it when you get time to get back into this issue.

Cheers, Rob.

[magicliang]

Sorry for late reply, thanks for you patience.

Here is my basic example: LeadsWebActionDataDao

My initial description was misleading because we were using Clickhouse as datasource instead of MySql, so I changed the comment above to show correct drivers. The example is not a complete runnable application, because I don't have a clickhouse for this project to connect, which needs a docker container to hold whole stack. But I think the domain class explains my issue.

I made some comment above findNative() and findDto() to explain the issue, let me add something here:

issue1:

I supposed

@Column(name = "action_count")
private Integer count;

will help me to map a column result to a name which does not comply with camel conventional naming pattern, but I got empty record when using findNative api. As you can see my example above, I want to map "camel_style" in sql result to "realCamelStyle" in java, the extra "real" needs to be handled. I know I can modify the sql to use sql alias(so the sql column will be renamed as "real_camel_style"), but shouldn't the column mapping work as alias, too?

issue 2:

-- result will look like
--  204, '2022-11-10 00:00:00', 10
select  effective_action_type, effective_action_time, action_count .....

In java view, first column is String, the second column is Date, the third column is Integer.

Then I create a class in arbitrary member order:

public class LeadsWebActionDataCountAggregate2 {

    @Column(name = "effective_action_time")
    private Date effectiveActionTime;

    @Column(name = "effective_action_type")
    private String effectiveActionType;

    @Column(name = "action_count")
    private Integer actionCount;

}

will throw an exception, when first column in result is a normal integer like 204, which should be mapped to effectiveActionType, but ebean tries to map it to effectiveActionTime (which is first member of class) when using findDto() api, then I got an casting exception: Integer can not be mapped to Date.

Shouldn't @column deal with member sequence issue?

[magicliang]

I'm thinking that you got busy with stuff. I'll close this for now and you can just re-open it when you get time to get back into this issue.

I think if you as a collaborator closed this issue, I can't re-open it. Hope you can review this thread someday.

[rbygrave]

The goal would be to git clone the repo, mvn clean test and see the issue. There isn't a test that runs that DAO.

Are you able to change this into a test that runs and reproduces the issue? I realise the application is using clickhouse but we should be able to have a test use H2 that reproduces the issue?

Noting the sql in the DAO looks invalid - it ends with </code>.

[magicliang]

The goal would be to git clone the repo, mvn clean test and see the issue. There isn't a test that runs that DAO.

Are you able to change this into a test that runs and reproduces the issue? I realise the application is using clickhouse but we should be able to have a test use H2 that reproduces the issue?

Noting the sql in the DAO looks invalid - it ends with </code>.

Well, I don't know whether we can reproduce the issue with H2 jdbc connector(which I think causes different behavior), I will complete the test and get back to you later.