How can i make a @resultsetmapping for a @onetomany only happen if the foreign key is not null?

Hibernate ORM
1

Hi! Long time listener, first time caller. Using Hibernate 5.2.17.Final

I have an Entity with a one-to-many association. like this:

public class Location {
......
@ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH}, fetch = FetchType.LAZY)
@JoinColumn(name = "ledgerId")
protected LedgerValue ledgerValue;

The SQL to fetch is kind of complex, so i have a native query with a resultsetmapping, like this:

@ConstructorResult(
        targetClass = LedgerValue.class,
        columns = {
            @ColumnResult(name = "lvid"),
            @ColumnResult(name = "mainc", type = Long.class),
            @ColumnResult(name = "emailonly", type = Boolean.class),
            @ColumnResult(name = "copy", type = Boolean.class),
        }
    )

This all works fine, the Locations are loaded with data as expected. However, the ‘LedgerValue’ onetomany-object is always created, with null values if there are no values, since not all Locations have a LedgerValue.

This all works, my Location class gets populated with a LedgerValue entity. However, in the cases where there is no LedgerValue associated with a Location, i still get a LedgerValue object with only null values.

I would, rather, that the LedgerValue object itself is null if the “lvid” identifier is null. I have looked around but haven’t been able to find a way to do this, except for doing the entire sql myself, or a ResultSetExtractor.

I there a (hopefully simplish) way to accomplish this?

Pointers much appreciated.

2

First of all, this is not a one-to-many, but a many-to-one association. Or are you maybe talking about something that is not shown in the code samples?

This all works fine, the Locations are loaded with data as expected. However, the ‘LedgerValue’ onetomany-object is always created, with null values if there are no values, since not all Locations have a LedgerValue.

Please show us how you fetch the data. The mentioning of one-to-many makes me think that there is something going on that the code samples don’t show. Also, the use of ConstructorResult for an association seems wrong to me. If you want an entity to be fetched with a certain query, you usually have to use @Subselect on that entity class.

Is the ledgerId column in the Location row also null? If not, you might have to use @NotFound(IGNORE)

3

Beikov, many thanks for responding so fast.

You’re right, it’s a many-to-one like in the code, typo in the header on my part.

I’ll try to explain better:

My issue is that when loading these locations, i get an additional query for the (lazy) loading of the LedgerValue for each Location. Since I am using a native query, i’m having trouble getting the data of that query to populate the Ledgervalue relation entity so that the additional queries do not happen. I’ve tried to make this happen with various SqlResultSetMappings, but haven’t gotten it to work 100%

code:

public class Location {
    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH}, fetch = FetchType.LAZY)
    @JoinColumn(name = "ledgerId")
    protected LedgerValue ledgerValue;

public class LedgerValue  {
    @Id
    private String id;

sql:

@NamedNativeQuery(name = "Location.findEnabledByGroups",
                query = "select l.*, lv.id as lvid, lv.mainc, lv.emailonly, lv.copy from location l inner join grouplocation gl on l.id=gl.locationid inner join groupreporter gr on gr.groupid=gl.groupid inner join usergroup g on g.id = gl.groupid " +
                "left join lvalue lv on l.ledgerid=lv.id where gr.reporterid=:repid and g.spaceid=:spaceid and l.spaceid=:spaceid and l.enabled=1 group by l.id order by l.name"
                , resultClass = Location.class, resultSetMapping = "lValueMapping"),

EDIT: this is the resultsetmapping:

@SqlResultSetMapping(
    name = "lValueMapping",
    entities = {@EntityResult(entityClass = Location.class),
        @EntityResult(
            entityClass = LedgerValue.class,
            fields = {
                @FieldResult(name = "id", column = "lvid"),
                @FieldResult(name = "mainC", column = "mainc"),
                @FieldResult(name = "emailOnly", column = "emailonly"),
                @FieldResult(name = "copyable", column = "copy")})
    }
)

As you can see, i fetch the inidividual columns from the LedgerValue table in the sql - this was to try and make this work with SqlResultSetMappings.

I’ve gotten the entities to populate with resultsetmappings, but what happens is that each position the resulting ArrayList has 2 items, the Location and the LedgerValue. I would like a list of just Locations, with the LedgerValue object populated within each Location.

If i have to work with the tuples - i’d have to iterate through the tuple list, do tons of costly class castings to create a new List with the Location objects at tuple[0] and return that. Not great.

To summarize - i can’t get the locations with their ledgervalues to be populated properly with my native sql without hibernate then firing off additional sql queries to populate the LedgerValues - or with alot of mappings that result in the returned list contains multiple objects on each position.

4

JPA has no way to define fetches of associations for native queries, but when you select both entity results(like you did), you should see the association initialized, so you’d only have to extract the first element from the tuple, for which you can use a ResultTransformer. It’s not perfect, but probably the easiest way to achieve your goal. It’s also quite efficient, as the transformer runs for each row before adding to the result list.

Another alternative is to make use of the Hibernate NativeQuery API which allows you to define fetches i.e.

NativeQuery<Location> q = session.createNativeQuery("select {l.*}, {lv.*} from location l left join lvalue lv on ...", Location.class);
q.addEntity("l", Location.class);
q.addFetch("lv", "l", "ledgerValue");
List<Location> list = q.getResultList();
5

Hi again beikov. Thanks for your help.

I did the resulttransformer and it works fine. too bad there wasn’t an easier way. In an optimal world, there was some sort of auto-matching between the columns in the resultset and the LedgerValue, or at least that i could say i only wanted the one list, but oh well.

I had to unwrap the JPA query to get the Hibernate one, which also feels a bit unoptimal, but you can’t have all you wish for :slight_smile: It is also deprecated for some reason (i saw they have redesigned the API slightly in 6.0 so i guess that might be why)

If someone else wonders, this is the resulttransformer i did to return a list of objects:

Query q = getEntityManager().createNamedQuery("Location.findByGroups");
        q.setParameter("spaceid", spaceId);
        q.setParameter("repid", reporterId);
        q.unwrap(org.hibernate.query.Query.class).setResultTransformer(new ResultTransformer() {
            @Override
            public Object transformTuple(Object[] tuple, String[] aliases) {
                return tuple[0];
            }
            @Override
            public List transformList(List result) {
                return result;
            }
        });
        return q.getResultList();

Thanks again for your valuable input!

6

Beikov, i have a follow-up question related to this that i can’t get my head around, hoping for some input from a mega-expert such as yourself. :slight_smile:

As i said, i’ve gotten it to work according to our previous discussion, but i have discovered an edgecase that doesn’t work.

The ledgerId value is entered by users and matched by SQL. However, in rare circumstances they
might be populated in different cases. The foreign key restriction accepts it in the database. So it currently ends up in different case in the two tables.

-Say the ledgerId in the location table is ‘a’, but the id in the lvalue table is ‘A’.

If i do it myself, the sql loads all values properly regardless of case.

But with the solution we created last week, the LedgerValue relation entity isn’t populated even though the resultset contains the objects, and instead extra sql queries are shot off by hibernate to load the relation (lvalue),
with the still non-case-matching as id, and THEN it is loaded and populated…

This is very strange to me, and i’m wondering what i can do about it. I can’t understand why hibernate doesn’t populate the object when it has all the columns.

Pointers much appreciated.

7

That’s happening because Hibernate compares and looks up String objects via equals/hashCode and obviously, "a" is not equal to "A" and these objects also have different hash codes. Your database on the other hand is probably using a case insensitive collation (I guess you use MySQL?) and will compare strings based on that collation. There is no way to configure Hibernate to do the same, and I would also strongly recommend that you stop rely on something like that by default. Case insensitive comparison should IMO be something that you only do when absolutely necessary, and in that case, do it explicitly.

So my advice is, change the collation of your database to something like utf8mb4_0900_as_cs (in case of MySQL). Note that you might have to change the collation of every column in every table, not sure how changing collations works exactly.

If you can’t change the collation for some reason, at least make sure that the cases match to avoid the issues you are facing, by e.g. creating triggers that do lower(col) or upper(col) on the FK and/or primary key column. I would ask myself though, how it is possible that you have a FK value with a different case than the PK value. Seems to me, that there might be a bug hiding or someone simply inserted this entry by hand.

8

Right, yeah we’re using mariaDB so i kind of already gathered that it’s the collation. I just got confused since the column is actually returned in the sql data and can be viewed when i debug so i didn’t get why it didn’t end up getting populated in the object.

As for your question about how it ended up like this: This ledgerid is a thing requred by law in Sweden to log time with the authorities. Different clients can collaborade on a project, so they will create a location with the same ledger id in their respective accounts.

My code checks in the database if the value exists, if it does, it uses it. Since the “findbyledger” returns not null, i am using what was entered by the user to put in the table. But, of course, if 2 clients enter then with different cases, they will still match with my sql, but have different cases.

I simply forgot about this, in hindsight i should have obviously done a to-upper-case in my java code before inserting - or as a trigger as you suggest - but you know, shoulda coulda woulda. :slight_smile:

I have added the appropriate java code and will run a sql script to change all to uppercase in the DB, since i can’t change the collation (and wouldn’t dare to anyway, to be honest… ) or configure it away in hibernate or something like that.

Again, huge thanks for input! You’re really great and if i ever run into any hibernate issues, i know where to go. Have a good one.

1 Like