在PostgreSQL表中搜索

本文介绍了在PostgreSQL表中搜索的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个要在其中实现搜索过滤器的表。

I have this table in which I would like to implement search filter.

CREATE TABLE ACCOUNT( ID INTEGER NOT NULL, USER_NAME TEXT, PASSWD TEXT, FIRST_NAME TEXT, LAST_NAME TEXT, LAST_LOGIN DATE, DATE_REGISTERED DATE, ROLE INTEGER, CAN_LOGIN INTEGER ) ; -- ADD KEYS FOR TABLE ACCOUNT ALTER TABLE ACCOUNT ADD CONSTRAINT KEY1 PRIMARY KEY (ID) ;

字符串searchString = 32;

String searchString = "32";

SELECT * FROM ACCOUNT WHERE " + searchString + " IN (ID, USER_NAME, FIRST_NAME, LAST_NAME) ORDER BY %S %S offset ? limit ?;

我遇到错误

serverError: class java.lang.IndexOutOfBoundsException Index: 0, Size: 0

我也不想指定要在其中搜索的每个表列。默认情况下，有没有一种方法可以在所有列中实现搜索？并仅指定我不想在其中搜索的一列？

Also I don't want to specify every table column in which I want to search. Is there a way to implement search in all columns by default? And just specify one column in which I don't want to search?

推荐答案

您在 IN 列表。这些都必须是相同的类型，因此它们将转换为要比较的类型。并且，无法将字符串转换为整数。

You have a type conversion problem with the IN list. These all have to be the same type, so they are converted to the type of what is being compared. And, there is a failure to convert strings to ints.

如果包含单引号，则查询应该起作用：

If you include single quotes, then your query should work:

WHERE '" + searchString + "' IN (ID, USER_NAME, FIRST_NAME, LAST_NAME)