错误：运算符不存在：character varieting=bytea

Question

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" )
)

这个表的hibernate映射如下所述

<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>

在loginDetails.java中，我将id字段声明为long，userName和password字段声明为string。当我尝试执行以下命令时

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

我得到

我不明白哪里出了问题。如有任何帮助，不胜感激。

Answer 1

故障排除检查表：

#1:根据使用的数据库类型，您需要使用以下SQL命令查找列名及其各自的数据类型：

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

预期结果将给出三列；尤其是'data_type'列。

pojo示例：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;

}

存储库类：

//选项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
    );

}

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

}