Ошибка оператор не существует character varying bytea

Troubleshooting checklist :

#1 : Depending on the database type you are using you would want to find the the column
names and their respective data types using this SQL command :

SELECT
   table_name,
   column_name,
   data_type
FROM
   information_schema.columns
WHERE
   table_name = 'the-name-of-the-table-in-the-database';

Expected results would give you three columns; and more especially the ‘data_type’ column.

Make sure your Pojo class and the respective data types match appropriately.

Take note : bigint (data type) in the table inside the database can match with a Long seamlessly.
integer with int. character varying with String or a major java class, eg. a class storing Enums, and so on.

After confirming the above, do the next check -> troubleshooting :

#2 : The Main checks on this troubleshooting is to check that all the data types match
perfectly. And do pay attention to the parameters passed to the query.

Passing an enum
or or any other data type or an enum type that is not conforming to the SQL data types
could trigger the ‘is not mapped’ error(s) even if the pojo class matches perfectly with
the table structure in the database.

pojo example : UserAccountBalance.class

import io.swagger.v3.oas.annotations.media.Schema;
import lombok.*;

@Builder//Lombok
@AllArgsConstructor(access = AccessLevel.PRIVATE)
@NoArgsConstructor(access = AccessLevel.PUBLIC)
@Data//Lombok
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
@Schema
@Entity(name = "user_account_balance")
@Table(name = "user_account_balance")
public class UserAccountBalance {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private long id;

    @NotNull
    @Column(name = "username", nullable = false)
    private String userName;

    @NotNull
    @Column(name="currency_code", nullable = false)
    @Enumerated(EnumType.STRING)
    private CurrencyCode currencyCode;

    @NotNull
    @Column(name = "balance", nullable = false)
    private BigDecimal balance;

//Could be placed into and AuditModel class
    @Column(name = "datecreated", nullable = false, updatable = false)
    @JsonIgnore
    @DateCreated
    @CreationTimestamp
    private LocalDateTime dateCreated;

    @Column(name = "date_updated", nullable = false, updatable = false)
    @JsonIgnore
    @DateUpdated
    private LocalDateTime dateUpdated;

    @NotNull
    @Column(name = "active")
    @JsonIgnore
    private int active;

    @Column(name = "deleted")
    @JsonIgnore
    private int deleted;

}

Repository class :

//Option 1 : UserAccountBalanceRepository.class

@Repository
public abstract class UserAccountBalanceRepository implements CrudRepository<UserAccountBalance, Long> {

    private final EntityManager entityManager;

    public UserAccountBalanceRepository(@CurrentSession EntityManager entityManager){
        this.entityManager = entityManager;
    }

    @Transactional(readOnly = true)
    @Query(
            value="SELECT uab.*" +
                    " FROM public.user_account_balance uab" +
                    " WHERE (currency_code =cast(:currencyCode AS text)" +
                    " AND userName =:userName" +
                    " AND active =:active)",
            countQuery = "SELECT uab.*" +
                    " FROM public.user_account_balance uab" +
                    " WHERE (currency_code = cast(:currencyCode AS text)" +
                    " AND userName =:userName" +
                    " AND active =:active)",
            nativeQuery = true
    )
    public abstract Optional<UserAccountBalance> findByUserAccountBalance_UserName_And_CurrencyCode(
            String userName,
            CurrencyCode currencyCode,
            int active
    );

}

//Option 2 : UserAccountBalanceRepository.class

@Repository
public abstract class UserAccountBalanceRepository implements CrudRepository<UserAccountBalance, Long> {

    private final EntityManager entityManager;

    public UserAccountBalanceRepository(@CurrentSession EntityManager entityManager){
        this.entityManager = entityManager;
    }

    @Transactional(readOnly = true)
    @Query(
            value="SELECT uab.*" +
                    " FROM public.user_account_balance uab" +
                    " WHERE (currency_code =:currencyCode" +
                    " AND userName =:userName" +
                    " AND active =:active)",
            countQuery = "SELECT uab.*" +
                    " FROM public.user_account_balance uab" +
                    " WHERE (currency_code = :currencyCode" +
                    " AND userName =:userName" +
                    " AND active =:active)",
            nativeQuery = true
    )
    public abstract Optional<UserAccountBalance> findByUserAccountBalance_UserName_And_CurrencyCode(
            String userName,
            String currencyCode,/*this is what truly worked out for me perfectly*/
            int active
    );

}

#3. Test and test again. If problem still persist please have patience and look through all
your variables and classes again.

#4. If troubleshooting using option #3 still does not help, consider taking a little walk, take
some little rest and have a fresh set of eyes to look at it all over from troubleshooting #1.

I hope this helps. Cheers and peace.

Источник

The error «ERROR: operator does not exist: character varying = bytea?» often occurs in Hibernate when trying to compare a character varying type column with a bytea type column in a database query. This error occurs because the database does not have a matching operator to compare these two types. In order to resolve this issue, there are several methods that can be used, which are listed below.

Method 1: Convert the bytea type column to character varying type

To fix the error «Hibernate: how to fix ERROR: operator does not exist: character varying = bytea?», you can convert the bytea type column to character varying type. Here are the steps to do it:

Create a new column with character varying type:

ALTER TABLE table_name ADD COLUMN new_column_name character varying;

Update the new column with the converted value:

UPDATE table_name SET new_column_name = encode(bytea_column_name, 'escape');

Drop the old bytea column:

ALTER TABLE table_name DROP COLUMN bytea_column_name;

Rename the new column to the original column name:

ALTER TABLE table_name RENAME COLUMN new_column_name TO bytea_column_name;

Here is the sample code in Java using Hibernate:

@Entity
@Table(name = "table_name")
public class MyEntity {
    
    @Column(name = "bytea_column_name", columnDefinition = "bytea")
    private byte[] byteaColumn;
    
    @Column(name = "new_column_name")
    private String newColumn;
    
    // getters and setters
    
    @PrePersist
    @PreUpdate
    private void convertByteaToVarchar() {
        if (byteaColumn != null) {
            newColumn = new String(org.postgresql.util.PGbytea.toHex(byteaColumn), StandardCharsets.UTF_8);
        }
    }
    
    @PostLoad
    private void convertVarcharToBytea() {
        if (newColumn != null) {
            byteaColumn = org.postgresql.util.PGbytea.toBytes(newColumn.getBytes(StandardCharsets.UTF_8));
        }
    }
}

Note that this code uses PostgreSQL-specific functions to convert bytea to varchar and vice versa. If you are using a different database, you may need to use different functions.

Method 2: Cast the character varying type column to bytea type

To fix the error «operator does not exist: character varying = bytea» in Hibernate, you can cast the character varying type column to bytea type. Here is how to do it in steps:

Open the entity class that maps to the table in question.
Locate the column that is causing the error and annotate it with the @Type annotation.

@Column(name = "column_name")
@Type(type = "org.hibernate.type.BinaryType")
private byte[] columnName;

In the above code, replace «column_name» with the name of the column causing the error and «columnName» with the name of the field in the entity class.
The @Type annotation specifies that the column should be treated as a binary type.
Save the changes and run the application again. The error should be resolved.

Here is another example of how to cast a character varying type column to bytea type in Hibernate:

@Column(name = "column_name")
@Basic(fetch = FetchType.LAZY)
@Lob
private byte[] columnName;

In the above code, the @Lob annotation specifies that the column is a large object, and the @Basic(fetch = FetchType.LAZY) annotation specifies that the column should be fetched lazily.

That’s it! By casting the character varying type column to bytea type, you should be able to fix the «operator does not exist: character varying = bytea» error in Hibernate.

Method 3: Use a Hibernate custom type

To fix the «Hibernate: how to fix ERROR: operator does not exist: character varying = bytea?» issue, you can use a Hibernate custom type. Here are the steps:

Create a custom type by implementing the org.hibernate.usertype.UserType interface.

public class ByteArrayToStringType implements UserType {
    // implementation of methods
}

Implement the sqlTypes() method to return the SQL types that correspond to the Java type.

@Override
public int[] sqlTypes() {
    return new int[]{Types.VARCHAR};
}

Implement the returnedClass() method to return the Java type that the custom type represents.

@Override
public Class returnedClass() {
    return byte[].class;
}

Implement the nullSafeGet() method to convert the database value to the Java type.

@Override
public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws SQLException {
    String value = rs.getString(names[0]);
    if (rs.wasNull()) {
        return null;
    }
    return DatatypeConverter.parseHexBinary(value);
}

Implement the nullSafeSet() method to convert the Java type to the database value.

@Override
public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws SQLException {
    if (value == null) {
        st.setNull(index, Types.VARCHAR);
    } else {
        byte[] bytes = (byte[]) value;
        st.setString(index, DatatypeConverter.printHexBinary(bytes));
    }
}

Implement the equals() and hashCode() methods to ensure that Hibernate can compare values of the custom type correctly.

@Override
public boolean equals(Object x, Object y) throws HibernateException {
    if (x == y) {
        return true;
    }
    if (x == null || y == null) {
        return false;
    }
    return Arrays.equals((byte[]) x, (byte[]) y);
}

@Override
public int hashCode(Object x) throws HibernateException {
    return Arrays.hashCode((byte[]) x);
}

Use the custom type in your entity mapping by adding the @Type annotation to the field.

@Type(type = "com.example.ByteArrayToStringType")
@Column(name = "my_column")
private byte[] myField;

By following these steps, you can use a Hibernate custom type to fix the «Hibernate: how to fix ERROR: operator does not exist: character varying = bytea?» issue.

Источник

Solution 1

I think you should check that your variable «userName» is not null. I experienced this message in cases like that.

Solution 2

It seems that Hibernate is for some reason sending the type-parameter as bytea (or rather, probably java.sql.Types.BLOB), instead of leaving it for the server to infer or setting it to text (java.sql.Types.STRING).
Here is similar issue with solution JPA lower() function on parameter

Solution 3

The question is old but still sharing my solution I’ve used in Spring Boot if anyone needed.

You can use the below WHERE conditions for handling NULL values or making the parameters optional in the Postgres Query.

SELECT * FROM table 
WHERE 
          (?1 is null OR column1 = cast(?1 AS text)) 
          AND 
          (?2 is null OR column2 = cast(?2 AS text))

Here whole WHERE condition will be TRUE if column1 and column2 are passed as NULL.
column1 and column2 will be considered in the query if not NULL.

?1 is null OR column1 = ?1 : will check if passed value is null, then whole where condition will be true (will not check for second condition [column1 = null] if null — PG Optimization)

cast(?1 AS text) : can be useful if for some reason value is passed as bytea. If the passed value is real null, it will still give the «character varying bytea» error if not casted.

Solution 4

For Native Query we can use

SELECT * FROM table 
WHERE 
          (:paramName is null OR column1 = cast(:paramName AS text))

and then

query.setParamter(paramName,value);

Comments

I have a table in postgres with the following structure

CREATE TABLE rpaul."HK_LOGIN_DETAILS"
(
  "HK_LOGIN_DETAILS_ID" bigint NOT NULL,
  "HK_LOGIN_DETAILS_USERNAME" character varying(10) NOT NULL,
  "HK_LOGIN_DETAILS_PASSWORD" character varying(50) NOT NULL,
  CONSTRAINT "HK_LOGIN_DETAILS_PK" PRIMARY KEY ("HK_LOGIN_DETAILS_ID" ),
  CONSTRAINT "HK_LOGIN_DETAILS_UK" UNIQUE ("HK_LOGIN_DETAILS_USERNAME" )
)

And hibernate mapping for this table is as mentioned below

<hibernate-mapping package="net.rpaul.projects.homekeeping.domain.login">
    <class name="LoginDetails" table="`HK_LOGIN_DETAILS`">
        <id name="id" column="`HK_LOGIN_DETAILS_ID`" type="long">
            <generator class="assigned" />
        </id>
        <property name="userName" type="string" column="`HK_LOGIN_DETAILS_USERNAME`" not-null="true" />
        <property name="password" type="string" column="`HK_LOGIN_DETAILS_PASSWORD`" not-null="true" />
    </class>
</hibernate-mapping>

In the LoginDetails.java, I have declared id field as long, userName and password fields as String. Still when I try to execute the following

List list =  getHibernateTemplate().find("from LoginDetails ld where ld.userName = ?", userName);

I get

ERROR: operator does not exist: character varying = bytea

I am not getting what has went wrong. Any help would be appreciated.

It can be null in my case. What to do if it is the case?
precheck for null wherever you are querying usingthe variable
I got this problem once and I solved setting the variable as empty string(») before querying, in case it was null.
This approach works only for nativeQuery=false
Thank you very much @GuilhermeAlencar, I was struggling to make it work. That’s not right though, null doesn’t work, empty string works like it’s null. Strange.

Recents

Источник

I have a JPA UNION query and In GET API swagger three field is there institution(mandatory) ,txBookingLocation(mandatory) customerID(optional) working fine .If i remove ‘AND (COALESCE(:customerID…..)’ .both the select statement .without removing how it will work ?

ERROR: operator does not exist: character varying = bytea,No operator matches the given name and argument types

@Query(nativeQuery = true,value= «SELECT «

                       + " customerid AS customerID,                                                                                          "
                       + " stepid AS stepID,                                                                                                  "
                  .....................
                       + "institution AS institution,                                                                                         "
                       + "limit_groupid AS limitGroupID,                                                                                      "
                       + "checkerid AS checkerID,                                                                                             "
                       + "checker_timestamp AS checkerTimestamp,                                                                              "
                       + "step_action AS stepAction,                                                                                          "
                       + "step_status AS stepStatus,                                                                                          "
                       + "tx_booking_location AS txBookingLocation,                                                                           "
                                      ...............                       
                      + "customer_name AS customerName                                                                                        "
                      +  "FROM  txrh_bcaterms where step_status IN ('SAV', 'REJ') " + " AND institution = :institution                        "
                      + " AND (COALESCE(:txBookingLocation, null) is null or (tx_booking_location IN :txBookingLocation))                     "
                      + " AND (COALESCE(:customerID, null) is null or (customerid = :customerID))    "
                     
                                                                                                                            
            + "UNION                                                                                                                           "

            + "SELECT                                                                                                                          "
                       + "customerid AS customerID,                                                                                            "
                       + "null AS stepID,                                                                                                      "
                      ...........
                       + "finance_type AS financeType,                                                                                         "
                   ....................
                       + "institution AS institution,                                                                                          "
                       + "limit_groupid AS limitGroupID,                                                                                       "
                       + "null AS checkerID,                                                                                                   "
                       + "null AS checkerTimestamp,                                                                                            "
                       + "null AS stepAction,                                                                                                  "
                       + " 'RLS' AS stepStatus,                                                                                                "
                       + "tx_booking_location AS txBookingLocation,                                                                            "
                              ............................
                      
                       + "customer_name AS customerName                                                                                        "
                       + "FROM  txrm_bcaterms where (institution, tx_booking_location,customerid) NOT IN                                                  "  
                       + " (select institution, tx_booking_location,customerid from txrh_bcaterms  where step_status IN ('SAV', 'REJ', 'PNR') "
                       + " AND (COALESCE(:txBookingLocation, null) is null or ( tx_booking_location IN :txBookingLocation))"
                       + " AND (COALESCE(:customerID, null) is null or (customerid = :customerID))    "
                       + " AND institution = :institution ) "
                       + " AND institution = :institution "
                       + " AND (COALESCE(:txBookingLocation, null) is null OR tx_booking_location IN :txBookingLocation)" 
                       + " AND (COALESCE(:customerID, null) is null or customerid = :customerID)" ,
                        
                    countQuery = " SELECT count(*) FROM ("
                            ....................
                            + ")as cnt")                       
Page<ITXBCATermsHistoryBookingLocationAndInstitutionDTO> historyAndMaster(
        @Param("institution") String institution,
        @Param("txBookingLocation")  List<String> txBookingLocation,
        @Param("customerID") String customerID,
        Pageable page);

}

ERROR: operator does not exist: character varying = bytea

I am not getting what has went wrong. Any help would be appreciated.

Источник

postgresql
hibernate

07-06-2021

Question

I have a table in postgres with the following structure

CREATE TABLE rpaul."HK_LOGIN_DETAILS"
(
  "HK_LOGIN_DETAILS_ID" bigint NOT NULL,
  "HK_LOGIN_DETAILS_USERNAME" character varying(10) NOT NULL,
  "HK_LOGIN_DETAILS_PASSWORD" character varying(50) NOT NULL,
  CONSTRAINT "HK_LOGIN_DETAILS_PK" PRIMARY KEY ("HK_LOGIN_DETAILS_ID" ),
  CONSTRAINT "HK_LOGIN_DETAILS_UK" UNIQUE ("HK_LOGIN_DETAILS_USERNAME" )
)

And hibernate mapping for this table is as mentioned below

<hibernate-mapping package="net.rpaul.projects.homekeeping.domain.login">
    <class name="LoginDetails" table="`HK_LOGIN_DETAILS`">
        <id name="id" column="`HK_LOGIN_DETAILS_ID`" type="long">
            <generator class="assigned" />
        </id>
        <property name="userName" type="string" column="`HK_LOGIN_DETAILS_USERNAME`" not-null="true" />
        <property name="password" type="string" column="`HK_LOGIN_DETAILS_PASSWORD`" not-null="true" />
    </class>
</hibernate-mapping>

In the LoginDetails.java, I have declared id field as long, userName and password fields as String. Still when I try to execute the following

List list =  getHibernateTemplate().find("from LoginDetails ld where ld.userName = ?", userName);

I get

ERROR: operator does not exist: character varying = bytea

I am not getting what has went wrong. Any help would be appreciated.

No correct solution

OTHER TIPS

I think you should check that your variable «userName» is not null. I experienced this message in cases like that.

The question is old but still sharing my solution I’ve used in Spring Boot if anyone needed.

You can use the below WHERE conditions for handling NULL values or making the parameters optional in the Postgres Query.

SELECT * FROM table 
WHERE 
          (?1 is null OR column1 = cast(?1 AS text)) 
          AND 
          (?2 is null OR column2 = cast(?2 AS text))

Here whole WHERE condition will be TRUE if column1 and column2 are passed as NULL.
column1 and column2 will be considered in the query if not NULL.

?1 is null OR column1 = ?1 : will check if passed value is null, then whole where condition will be true (will not check for second condition [column1 = null] if null — PG Optimization)

cast(?1 AS text) : can be useful if for some reason value is passed as bytea. If the passed value is real null, it will still give the «character varying bytea» error if not casted.

For Native Query we can use

SELECT * FROM table 
WHERE 
          (:paramName is null OR column1 = cast(:paramName AS text))

and then

query.setParamter(paramName,value);

Источник

Method 1: Convert the bytea type column to character varying type

Method 2: Cast the character varying type column to bytea type

Method 3: Use a Hibernate custom type

Solution 1

Solution 2

Solution 3

Solution 4

Related videos on Youtube

Comments

Recents

Related

Question

No correct solution

OTHER TIPS

Не пропустите эти материалы по теме: