Search in PostgreSQL table

Question

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

String searchString = "32";

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

I get error

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?

Answer 1

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)