对所有列实施搜索过滤器 [英] Implement search filter for all columns

问题描述

我在PostgreSQL http中找到了此搜索示例：//www.postgresql.org/docs/current/interactive/textsearch-tables.html#TEXTSEARCH-TABLES-SEARCH

I found this search example in PostgreSQL http://www.postgresql.org/docs/current/interactive/textsearch-tables.html#TEXTSEARCH-TABLES-SEARCH

我试图实现这一目标此表的代码如下：

I tried to implement this code for this table this way:

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

Java代码

public List<AccountsObj> list(int firstRow, int rowCount, String sortField, boolean sortAscending) throws SQLException
    {
        String SqlStatement = null;

        Connection conn = ds.getConnection();
        if (conn == null)
        {
            throw new SQLException();
        }

        String sortDirection = sortAscending ? "ASC" : "DESC";

        SqlStatement = "SELECT * FROM ACCOUNT "
//            + " WHERE ? IS NULL OR ? IN (USER_NAME, FIRST_NAME, LAST_NAME)"
            + " WHERE to_tsvector('english', USER_NAME || ' ' ) @@ plainto_tsquery(?)"
            + " ORDER BY %S %S offset ? limit ? ";

        String sql = String.format(SqlStatement, sortField, sortDirection);

        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<AccountsObj> resultList = new ArrayList<>();

        try
        {
            conn.setAutoCommit(false);
            boolean committed = false;

            ps = conn.prepareStatement(sql);

            ps.setString(1, searchString);
            ps.setInt(2, firstRow);
            ps.setInt(3, rowCount);

            resultSet = ps.executeQuery();
            resultList = ProcessorArrayList(resultSet);

            conn.commit();
            committed = true;

        }
        finally
        {
            ps.close();
            conn.close();
        }

        return resultList;
    }

但是我有两个问题。当搜索字符串为空时，表也为空。我该如何解决？

But I have two issues. When search string is empty the table is also empty. How I can solve this? Also how I can implement this search for every table column?

我试过 WHERE to_tsvector（'english'，USER_NAME ||''）@ @ plainto_tsquery（？）不为空

但未应用搜索过滤器。

推荐答案

您必须在全文搜索中添加空防护，然后使用 to_tsquery 代替 plainto_tsquery （为了使前缀搜索起作用）。

You will have to add your 'null guard' to the the fulltext search and use to_tsquery instead of plainto_tsquery (in order for prefix search to work).

SqlStatement = "SELECT * FROM ACCOUNT "
    + " WHERE (trim(?) = '') IS NOT FALSE"
    + " OR to_tsvector('english', USER_NAME || ' ' || FIRST_NAME || ' ' || LAST_NAME ) @@  to_tsquery(?)"
    + " ORDER BY user_name ASC offset ? limit ? ";

并将 searchString 添加到您的 PreparedStatement 第二次

 ps = conn.prepareStatement(sql);

 ps.setString(1, searchString);
 ps.setString(2, searchString);
 ps.setInt(3, firstRow);
 ps.setInt(4, rowCount);

注意，使用全文搜索将无法搜索单词-部分（例如％user％，％name 或 us％name ）。不过，您可以搜索前缀，例如用户：*

Note using a fulltext search you will not be able to search for word-parts (like %user%, %name or us%name). You can search for prefixes though, e.g. user:*

这篇关于对所有列实施搜索过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！