Weird JSON output from Entity class with @ManyToOne field.

[maxlam79]

Say if I have an Entity class GeoCountryEntity

@Entity
@Cacheable
@Table( name = "t_geo_country" )
public class GeoCountryEntity extends PanacheEntityBase {

    @Id
    @Column( name = "country_code" )
    public String countryCode;

    @Column( name = "country_name" )
    public String countryName;

    public GeoCountryEntity( ) {
    }

    public GeoCountryEntity( String countryCode, String countryName ) {
        this.countryCode = countryCode;
        this.countryName = countryName;
    }

    // ---------- Other Associations ----------
    @OneToMany( mappedBy = "geoCountryEntity", fetch = FetchType.LAZY )
    @JsonbTransient
    public List< GeoStateEntity > geoStateEntityList;
}

and another entity class GeoStateEntity:

Do note the geoCountryEntity field below which is annotated @JsonbTransient

@Entity
@Cacheable
@Table( name = "t_geo_state" )
public class GeoStateEntity extends AbstractEntity {

    @Id
    @Column( name = "uuid" )
    public String uuid;

    @JoinColumn( name = "country_code", referencedColumnName = "country_code" )
    @ManyToOne( fetch = FetchType.LAZY )
    @JsonbTransient
    public GeoCountryEntity geoCountryEntity;

    @Column( name = "state_code" )
    public String stateCode;

    @Column( name = "state_name" )
    public String stateName;

    public GeoStateEntity( ) {
    }

    public GeoStateEntity( String uuid, GeoCountryEntity geoCountryEntity, String stateCode, String stateName ) {
        this.uuid = uuid;
        this.geoCountryEntity = geoCountryEntity;
        this.stateCode = stateCode;
        this.stateName = stateName;
    }
}

A simple PanacheRepository class:

@ApplicationScoped
public class GeoStateQuery implements PanacheRepository< GeoStateEntity > {

    public List< GeoStateEntity > listByGeoCountryCode( String geoCountryCode ) {

        String jpql = "from GeoStateEntity gs "
                + "join gs.geoCountryEntity gc "
                + "where gc.countryCode = :geoCountryCode ";

        return list( jpql, Sort.by( "gs.stateCode" ), new HashMap< String, Object >( ) {{
            put( "geoCountryCode", geoCountryCode );
        }} );
    }
}

A REST Resource class:

@Path( "/geo-states" )
public class GeoStatesResource {

    @Inject
    GeoStateQuery geoStateQuery;

    @GET
    @Produces( MediaType.APPLICATION_JSON )
    public void listGeoStatesByGeoCountryCode(
            @QueryParam( "geoCountryCode" ) String geoCountryCode,
            @Suspended final AsyncResponse resp ) {

            List< GeoStateEntity > geoStateEntityList = geoStateQuery.listByGeoCountryCode( geoCountryCode );

            resumeSuspendedAsyncResponse( resp,
                    Response.Status.OK,
                    geoStateEntityList );
    }
}

I get very weird results like:

[
    [
        {
            "stateCode": "MY-01",
            "stateName": "Johor",
            "uuid": "b507ace7-41dc-4aa8-99ed-1f2a3b49d369"
        },
        {
            "countryCode": "MY",
            "countryName": "Malaysia",
            "geoStateEntityList": [
                {
                    "stateCode": "MY-01",
                    "stateName": "Johor",
                    "uuid": "b507ace7-41dc-4aa8-99ed-1f2a3b49d369"
                },
                {
                    "stateCode": "MY-02",
                    "stateName": "Kedah",
                    "uuid": "d2d59643-4a6b-465a-abec-6c1c04a4cac3"
                },
                {
                    "stateCode": "MY-03",
                    "stateName": "Kelantan",
                    "uuid": "19898257-e170-499c-a992-d21fa03469c3"
                },
                {
                    "stateCode": "MY-04",
                    "stateName": "Melaka",
                    "uuid": "da79ec40-f76c-469e-9f0e-87a1abd7b286"
                },
                {
                    "stateCode": "MY-05",
                    "stateName": "Negeri Sembilan",
                    "uuid": "9208d88f-2e9e-4d3a-84bb-50894a75a135"
                },
                {
                    "stateCode": "MY-06",
                    "stateName": "Pahang",
                    "uuid": "6438b45f-afd5-413a-916d-5da1d9cca822"
                },
                {
                    "stateCode": "MY-07",
                    "stateName": "Pulau Pinang",
                    "uuid": "bd8cb863-b498-4804-837e-7cbe61ca4b7d"
                },
                {
                    "stateCode": "MY-08",
                    "stateName": "Perak",
                    "uuid": "a3c1c7fd-3fa7-4aa4-ab35-9617d1c64b89"
                },
                {
                    "stateCode": "MY-09",
                    "stateName": "Perlis",
                    "uuid": "dada5187-dbb1-4c58-a7a0-8e840dd07829"
                },
                {
                    "stateCode": "MY-10",
                    "stateName": "Selangor",
                    "uuid": "b59338ac-d960-4e3f-ad73-0574fa12afa8"
                },
                {
                    "stateCode": "MY-11",
                    "stateName": "Terengganu",
                    "uuid": "b61201b8-26f2-40d4-9956-6bc0bdca64ed"
                },
                {
                    "stateCode": "MY-12",
                    "stateName": "Sabah",
                    "uuid": "26f846bc-c831-493e-a5b9-5cb94234ebc1"
                },
                {
                    "stateCode": "MY-13",
                    "stateName": "Sarawak",
                    "uuid": "11efc7f7-4e9e-4e04-b78f-6472c1c73356"
                },
                {
                    "stateCode": "MY-14",
                    "stateName": "Wilayah Persekutuan Kuala Lumpur",
                    "uuid": "3a341a7c-dc21-476c-ba31-0bd07f244443"
                },
                {
                    "stateCode": "MY-15",
                    "stateName": "Wilayah Persekutuan Labuan",
                    "uuid": "50128f46-71dd-49b0-bf86-d9f4d68a2e76"
                },
                {
                    "stateCode": "MY-16",
                    "stateName": "Wilayah Persekutuan Putrajaya",
                    "uuid": "5e870a00-0be6-458e-8eef-92631b2d0e8a"
                }
            ]
        }
    ],
    .......
]

Expected Result Should Be

[ 
   { 
      "stateCode":"MY-01",
      "stateName":"Johor",
      "uuid":"b507ace7-41dc-4aa8-99ed-1f2a3b49d369"
   },
   { 
      "stateCode":"MY-02",
      "stateName":"Kedah",
      "uuid":"d2d59643-4a6b-465a-abec-6c1c04a4cac3"
   },
   ....
]

Environment:

Quarkus Version: 0.19.1
Java Version: openjdk version "11.0.4" 2019-07-16 LTS
OpenJDK Runtime Environment Zulu11.33+15-CA (build 11.0.4+11-LTS)
OpenJDK 64-Bit Server VM Zulu11.33+15-CA (build 11.0.4+11-LTS, mixed mode)

[gsmet]

@wargun02 could you put together a reproducer? It will be easier to debug.

Thanks!

[maxlam79]

@gsmet I'm new to this project. Could you suggest how I can put together a reproducer, as in do I start a GitHub project, upload it? or attached a zip file containing all the codes?

[gsmet]

As you prefer really. A GitHub project allows to discuss more easily I think but it's really no obligation.

Just create an empty Quarkus project, add the class necessary to reproduce your issue and push it.

[maxlam79]

@gsmet Here it is: https://github.com/wargun02/quarkus_issue_3408

[maxlam79]

I have changed the title to: "Weird JSON output from Entity class with @manytoone field."

[geoand]

@wargun02 thanks for the reproducer!

Here is the SQL that Hibernate is generating based on your query:

    select
        geostateen0_.uuid as uuid1_1_0_,
        geocountry1_.country_code as country_1_0_1_,
        geostateen0_.country_code as country_4_1_0_,
        geostateen0_.state_code as state_co2_1_0_,
        geostateen0_.state_name as state_na3_1_0_,
        geocountry1_.country_name as country_2_0_1_ 
    from
        t_geo_state geostateen0_ 
    inner join
        t_geo_country geocountry1_ 
            on geostateen0_.country_code=geocountry1_.country_code 
    where
        geocountry1_.country_code=? 
    order by
        geostateen0_.state_code limit ?

which seems to me that you are not executing the query you intended

[maxlam79]

Changing the query in the repository class to:

        String jpql = "select gs from GeoStateEntity gs "
                + "join gs.geoCountryEntity gc "
                + "where gc.countryCode = :geoCountryCode ";

        return list( jpql, Sort.by( "gs.stateCode" ), new HashMap< String, Object >( ) {{
            put( "geoCountryCode", geoCountryCode );
        }} );

with the "select gs from GeoStateEntity gs..." seems to work (instead of just "from GeoStateEntity gs...").

Anyway, I'm just following some example jpql from the documentation on Panache, thought I could save a few "keyboard typings".

If this is not a bug and it is intended to work at such, please close this issue.

[maxlam79]

But having the repository method returning "List< GeoStateEntity >", seems odd to me that the JSON output doesn't quite conform to what is in "GeoStateEntity".

[geoand]

Yes, which is why I think (from the little I looked at it) that you are not really returning GeoStateEntity from the query (due the join).

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you!
We are doing this automatically to ensure out-of-date issues does not stay around indefinitely.
If you believe this issue is still relevant please put a comment on it on why and if it truly needs to stay request or add 'pinned' label.