Java 没有运算符匹配给定的名称和参数类型。您可能需要添加显式类型转换。-- Netbeans、Postgresql 8.4 和 Glassfish

No operator matches the given name and argument type(s). You might need to add explicit type casts. -- Netbeans, Postgresql 8.4 and Glassfish

提问by Sam

I am trying to edit a table in Postgresql using JPA in Glassfish using EclipseLink. When I insert an entity, it runs fine. But, when I try to edit or remove the same entity, it fails with the following error. Any idea?

我正在尝试使用 EclipseLink 在 Glassfish 中使用 JPA 在 Postgresql 中编辑表。当我插入一个实体时，它运行良好。但是，当我尝试编辑或删除同一实体时，它失败并显示以下错误。任何的想法？

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.0.1.v20100213-r6600): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 38
Error Code: 0
        at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1422)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:799)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:867)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:587)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:530)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeCall(AbstractSession.java:914)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:205)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:191)
        at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.deleteObject(DatasourceCallQueryMechanism.java:182)
        at org.eclipse.persistence.internal.queries.StatementQueryMechanism.deleteObject(StatementQueryMechanism.java:101)
        at org.eclipse.persistence.queries.DeleteObjectQuery.executeDatabaseQuery(DeleteObjectQuery.java:167)
        at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:675)
        at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:589)
        at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:109)
        at org.eclipse.persistence.queries.DeleteObjectQuery.executeInUnitOfWorkObjectLevelModifyQuery(DeleteObjectQuery.java:112)
        at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:86)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2857)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1225)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1207)
        at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1167)
        at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:297)
        at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:256)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1406)
        at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:547)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1508)
        at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.issueSQLbeforeCompletion(UnitOfWorkImpl.java:3128)
        at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.issueSQLbeforeCompletion(RepeatableWriteUnitOfWork.java:268)
        at org.eclipse.persistence.transaction.AbstractSynchronizationListener.beforeCompletion(AbstractSynchronizationListener.java:157)
        at org.eclipse.persistence.transaction.JTASynchronizationListener.beforeCompletion(JTASynchronizationListener.java:68)
        at com.sun.enterprise.transaction.JavaEETransactionImpl.commit(JavaEETransactionImpl.java:412)
        ... 25 more
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  Position: 38
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:321)
        at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:108)
        at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:792)
        ... 53 more
Java Result: 1

回答by Frank Heikens

This the main error:

这是主要错误：

ERROR: operator does not exist: integer = character varying

错误：运算符不存在：整数 = 字符变化

You code is trying to match an integer and a string, that's not going to work. Fix your code, get the query that is involved to see if you fixed it. See also the PostgreSQL log files.

您的代码试图匹配一个整数和一个字符串，这是行不通的。修复您的代码，获取所涉及的查询以查看您是否修复了它。另请参阅 PostgreSQL 日志文件。

A workaround (NOT A SOLUTION!) is to do some casting. Check this article.

解决方法（不是解决方案！）是进行一些转换。检查这篇文章。

回答by Satheesh Cheveri

I had this issue, and solved. This was due to the WHERE clause contains String value instead of integer value.

我有这个问题，并解决了。这是由于 WHERE 子句包含字符串值而不是整数值。

回答by Martin Andersson

Bro, I had the same problem. Thing is I built a query builder, quite an complex one that build his predicates dynamically pending on what parameters had been set and cached the queries. Anyways, before I built my query builder, I had a non object oriented procedural code build the same thing (except of course he didn't cache queries and use parameters) that worked flawless. Now when my builder tried to do the very same thing, my PostgreSQL threw this fwored up error that you received too. I examined my generated SQL code and found no errors. Strange indeed.

兄弟，我遇到了同样的问题。事情是我构建了一个查询构建器，这是一个非常复杂的构建器，它动态地构建他的谓词，等待已设置的参数并缓存了查询。无论如何，在我构建我的查询构建器之前，我有一个非面向对象的程序代码构建了同样的东西（当然他没有缓存查询和使用参数），它完美无缺。现在，当我的构建器尝试做同样的事情时，我的 PostgreSQL 抛出了你也收到的这个该死的错误。我检查了生成的 SQL 代码并没有发现错误。确实奇怪。

My search soon proved that it was one particular predicate in the WHERE clause that caused this error. Yet this predicate was built by code that looked like, well almost, exactlyas how the procedural code looked like before this exception started to appear out of nowhere.

我的搜索很快证明是 WHERE 子句中的一个特定谓词导致了这个错误。然而，这种断言是由内置的代码，看起来像，以及差不多，正是因为程序代码是如何看起来像在此之前的异常开始出现无章可循的。

But I saw one thing I had done differently in my builder as opposed to what the procedural code did previously. It was the orderof the predicates he put in the WHERE clause! So I started to move this predicate around and soon discovered that indeed the order of predicates had much to say. If I had this predicate all alone, my query worked (but returned an erroneous result-match of course), if I put him with just one or the other predicate it worked sometimes, didn't work other times. Moreover, mimicking the previous order of the procedural code didn't work either. What finally worked was to put this demonic predicate at the start of my WHERE clause, as the first predicate added! So again if I haven't made myself clear, the order my predicates where added to the WHERE method/clause was creating this exception.

但是我看到我在我的构建器中做的一件事与之前程序代码所做的不同。这是他放在 WHERE 子句中的谓词顺序！所以我开始移动这个谓词，很快发现谓词的顺序确实有很多意义。如果我单独使用这个谓词，我的查询就可以工作（但当然返回了错误的结果匹配），如果我只给他一个或另一个谓词，它有时会起作用，而其他时候则不起作用。此外，模仿程序代码的先前顺序也不起作用。最终起作用的是将这个恶魔谓词放在我的 WHERE 子句的开头，因为第一个谓词添加了！所以如果我还没有说清楚的话，

回答by TondaCZE

I guess this can be due to many things. In my case it was having "WHERE id IN" condition in my query and I was setting IDs separated by dash as a string using setString method on PreparedStatement.

我想这可能是由很多原因造成的。在我的情况下，它在我的查询中有“WHERE id IN”条件，我正在使用 PreparedStatement 上的 setString 方法将用破折号分隔的 ID 设置为字符串。

Not sure if there is better way to do this but I just added placeholder in my statement and replaced it by values on my own.

不确定是否有更好的方法来做到这一点，但我只是在我的语句中添加了占位符并用我自己的值替换了它。

回答by Jason Oviedo

If anyone is having this exception and is building the query using Scala multi-line strings:

如果有人遇到此异常并且正在使用 Scala 多行字符串构建查询：

Looks like there is a problem with some JPA drivers in this situation. I'm not sure what is the character Scala uses for LINE END, but when you have a parameter right at the end of the line, the LINE END character seems to be attached to the parameter and so when the driver parses the query, this error comes up. A simple work around is to leave an empty space right after the param at the end:

在这种情况下，某些 JPA 驱动程序似乎存在问题。我不确定 Scala 用于 LINE END 的字符是什么，但是当您在行尾有一个参数时，LINE END 字符似乎附加到参数上，因此当驱动程序解析查询时，这出现错误。一个简单的解决方法是在最后的参数后面留下一个空白空间：

SELECT * FROM some_table a
WHERE a.col = ?param
AND a.col2 = ?param2

So, just make sure to leave an empty space after param (and param2, if you have a line break there).

所以，只要确保在 param 后面留一个空格（如果你在那里有换行符，还有 param2）。

回答by Ricardo Anjos

If you are using Primefaces, you should insert inside the the .xhtml file so it converts correctly to java integer. For example:

如果您使用 Primefaces，您应该在 .xhtml 文件中插入，以便它正确转换为 java 整数。例如：

<p:selectCheckboxMenu 
    id="frameSelect"
    widgetVar="frameSelectBox"
    filter="true"
    filterMatchMode="contains"
    label="#{messages['frame']}"
    value="#{platform.frameBean.selectedFramesTypesList}"
    converter="javax.faces.Integer">
    <f:selectItems
        value="#{platform.frameBean.framesTypesList}"
        var="area"
        itemLabel="#{area}"
        itemValue="#{area}" />
</p:selectCheckboxMenu>

回答by A. Smith

Doesn't look like you got an answer but this problem can also creep up if you're passing null ID's into your JPA Predicate.

看起来您没有得到答案，但是如果您将空 ID 传递到 JPA 谓词中，这个问题也可能会加剧。

For instance.

例如。

If I did a query on Cats to get back a list. Which returns 3 results.

如果我对 Cats 进行查询以获取列表。返回 3 个结果。

List catList;

列出 catList;

I then iterate over that List of cats and store a foriegn key of cat perhaps leashTypeId in another list.

然后我遍历该猫列表并将 cat 的外键可能是 leashTypeId 存储在另一个列表中。

List<Integer> leashTypeIds= new ArrayList<>();

for(Cats c : catList){
    leashTypeIds.add(c.getLeashTypeId);
}

jpaController().findLeashes(leashTypeIds);

If any of the Cats in catList have a null leashTypeId it will throw this error when you try to query your DB.

如果 catList 中的任何 Cats 的 leashTypeId 为空，则当您尝试查询数据库时，它会抛出此错误。

(Just realized I am posting on a 5 year old thread, perhaps someone will find this useful)

（刚刚意识到我在一个 5 年前的帖子上发帖，也许有人会发现这很有用）

回答by Asad Shakeel

In query where you are passing query parameters, typecast parameter to an integer

在您传递查询参数的查询中，将参数类型转换为整数

e.g. in the case of PostgreSQL, it might be

例如在 PostgreSQL 的情况下，它可能是

where table_name.column_name_with_integer_type = (:named_parameter_of_character_type)::integer

::integerwill convert the parameter value into an integer.

::integer将参数值转换为整数。

回答by WEBjuju

I had this issue in a very simple DELETE statement, and it is now solved.

我在一个非常简单的 DELETE 语句中遇到了这个问题，现在已经解决了。

My issue was due to using backticks around the column (this column was named "id").

我的问题是由于在列周围使用反引号（此列名为“id”）。

This query DID NOT WORKand resulted in "No operator matches the given name and argument type(s)"

此查询无效并导致“ No operator matches the given name and argument type(s)”

DELETE FROM mytable WHERE **\`id\`** = 3      -- DO NOT USE BACKTICKS

Coming from mysql, in dynamic queries, I always `backtick` columns.

来自 mysql，在动态查询中，我总是“反引号”列。

The following query DID WORK(with backticks removed):

以下查询确实有效（删除了反引号）：

DELETE FROM mytable WHERE **id** = 3

回答by NorbiPeti

In my case, I used a keyword as a column name, which resulted in ERROR: operator does not exist: name = bigint

就我而言，我使用关键字作为列名，结果是 ERROR: operator does not exist: name = bigint

The solution was to use double quotes around the column name.

解决方案是在列名周围使用双引号。