I have a table with a text column and I would like to select all rows that match the list of search parameters that were provided by the user:

select * from value where value.text in ('Mary', 'Steve', 'Walter');

In addition, I want to notify the user if any of his search terms could not be found. Let's say 'Steve' does not exist in the value.text column, how can I write a query that will show 'Steve'? As that information does not exist in any table, I have no idea how it could be done using a SQL query.

The actual Hibernate code looks like this:

List<String> searchItemList = new ArrayList<>();
searchItemList.add("Mary");
searchItemList.add("Steve");
searchItemList.add("Walter");
Query query = em.createQuery("select v from Value as v where v.text in ( :searchitemlist )");
query.setParameter("searchitemlist", searchItemList);
List result = query.getResultList();
log.info("{}", result.size());
log.info("{}", result);

The searchItemList is a list of all search terms provided by the user. Can be a few hundreds lines long. The current workaround is to search the value table once for each searchItem and note all queries that return 0 rows. That is rather inefficient, surely there is a better approach? Please advise.

CodePudding user response：

You can use the following query to get an array of search items that exist in the database

SELECT DISTINCT value.text from value where value.text in ('Mary', 'Steve', 'Walter');

after running this query, If we assume that the answer is stored in an array called result, notExistSearchListItems will give you the final result

IEnumerable<string> notExistSearchListItems = searchItemList.Except(result);