java - Why doesn't INET_NTOA work in ColumnTransformer? - Stack Overflow

admin管理员组

文章数量:1345187

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

ip	port	nickname
181012304	9100	PRLMA213
181012370	9100	PRLMA212
181012398	9100	PRLMA211
181012559	9100	PRLMA210

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

ip	port	nickname
181012304	9100	PRLMA213
181012370	9100	PRLMA212
181012398	9100	PRLMA211
181012559	9100	PRLMA210

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

Share Improve this question Follow edited 13 hours ago Shadow 34.3k1010 gold badges6565 silver badges7575 bronze badges asked 22 hours ago Cardinal SystemCardinal System 3,44144 gold badges2828 silver badges5050 bronze badges 0

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

@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();

本文标签： javaWhy doesn39t INETNTOA work in ColumnTransformerStack Overflow