I have a MySQL table that stores information about label printers:

I have a MySQL table that stores information about label printers:

There is a corresponding JPA entity for this table in my application:

@Entity
@Table(name = "printers", catalog = "gage_requests")
public class PrinterEntity {

    @Id
    @Column(name = "ip")
    @ColumnTransformer(read = "INET_NTOA(ip)", write = "INET_ATON(?)")
    private String address;
    private int port;
    private String nickname;

    public String getAddress() {
        return this.address;
    }

    public int getPort() {
        return this.port;
    }

    public String getNickname() {
        return this.nickname;
    }

}

When I use CriteriaQuery to retrieve the list of printers, the IP address is not properly converted and remains an integer:

public List<PrinterEntity> findPrinters() {
    CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
    CriteriaQuery<PrinterEntity> criteriaQuery = criteriaBuilder.createQuery(PrinterEntity.class);

    Root<PrinterEntity> root = criteriaQuery.from(PrinterEntity.class);
    criteriaQuery.select(root);

    TypedQuery<PrinterEntity> query = this.entityManager.createQuery(criteriaQuery);
    return query.getResultStream().peek(this.entityManager::detach)
            .collect(Collectors.toCollection(LinkedList::new));
}

Returned list:

[
    {
        "address": "181012304",
        "nickname": "PRLMA213",
        "port": 9100
    },
    {
        "address": "181012370",
        "nickname": "PRLMA212",
        "port": 9100
    },
    {
        "address": "181012398",
        "nickname": "PRLMA211",
        "port": 9100
    },
    {
        "address": "181012559",
        "nickname": "PRLMA210",
        "port": 9100
    }
]

With hibernate.show_sql set to true, the following SQL is logged:

select printerent0_.ip as ip1_5_, printerent0_.nickname as nickname2_5_, printerent0_.port as port3_5_ from gage_requests.printers printerent0_

This is clearly missing the INET_NTOA function used in my @ColumnTransformer annotation, so it appears that the annotation is not being applied.

Why is this?

• java
• hibernate
• jpa

2 Answers 2

@ColumnTransformer not supported at @Id fields

Hibernate's @ColumnTransformer might not be supported for a primary-key field as given by the annotation @Id here.

This was rejected in feature-request HHH-9808 by the core-maintainer Steve Ebersole in 2016:

@ColumnTransformer is not applied on @Id members

Ok then, until this gets some votes or something else changes, going to reject this.

For a detailed explanation see: Why can't we apply @ColumnTransformer to @Id field (primary key)? .

Related

This restriction was also be the issue in this case: @ColumnTransformer isn't used in select . The solution was to use a different column as primary-key with @Id.

Workarounds

To get the transformation for the IP-address working, I could think of these workarounds:

1. Fix the source: Reformat the persisted IP-addresses in the source-table, e.g. ensure that the column-type is VARCHAR and execute SQL UPDATE TABLE gage_requests.printers SET ip = INET_NTOA(ip). Then you need no transformation in Java but a validation on the address field, to ensure the correct format.
2. Transform in Java: Port this internet-address/IP transform-functions INET_NTOA and INET_ATON into the Java getters/setters for field address.
3. Change primary-key: Use a different column as primary-key, e.g. move @Id to nickname

From your problem, I think I have two main causes that might produce the IP address not properly converting, remaining an integer:

• From my experience with Hibernate, some versions may not fully support the @ColumnTransformer annotation for certain queries. I researched a bit on the topic, and it seems like Hibernate 5.4+ fully supports this annotation, according to this documentation: https://docs.jboss./hibernate/orm/5.4/javadocs//hibernate/annotations/ColumnTransformer. Here, it describes the usage of @ColumnTransformer annotation in Hibernate 5.4, resulting that it supports it.

• Native queries tend to work better with this kind of annotation. I suggest also trying to create your query something like:

Query query = entityManager.createNativeQuery("SELECT INET_NTOA(ip) as address, port, nickname FROM printers", PrinterEntity.class);
List<PrinterEntity> printers = query.getResultList();