Class FullText

  • Direct Known Subclasses:
    FullTextLucene

    public class FullText
    extends java.lang.Object
    This class implements the native full text search. Most methods can be called using SQL statements as well.
    • Nested Class Summary

      Nested Classes 
      Modifier and Type Class Description
      static class  FullText.FullTextTrigger
      Trigger updates the index when a inserting, updating, or deleting a row.
    • Field Summary

      Fields 
      Modifier and Type Field Description
      private static java.lang.String FIELD_COLUMNS
      A column name of the result set returned by the searchData method.
      private static java.lang.String FIELD_KEYS
      A column name of the result set returned by the searchData method.
      private static java.lang.String FIELD_QUERY
      The column name of the result set returned by the search method.
      private static java.lang.String FIELD_SCHEMA
      A column name of the result set returned by the searchData method.
      private static java.lang.String FIELD_SCORE
      The hit score.
      private static java.lang.String FIELD_TABLE
      A column name of the result set returned by the searchData method.
      private static java.lang.String SCHEMA  
      private static java.lang.String SELECT_MAP_BY_WORD_ID  
      private static java.lang.String SELECT_ROW_BY_ID  
      private static java.lang.String TRIGGER_PREFIX  
    • Constructor Summary

      Constructors 
      Constructor Description
      FullText()  
    • Method Summary

      All Methods Static Methods Concrete Methods 
      Modifier and Type Method Description
      private static void addColumnData​(SessionLocal session, java.util.ArrayList<java.lang.String> columns, java.util.ArrayList<java.lang.String> data, Expression expr)  
      protected static void addWords​(FullTextSettings setting, java.util.Set<java.lang.String> set, java.io.Reader reader)
      Add all words in the given text to the hash set.
      protected static void addWords​(FullTextSettings setting, java.util.Set<java.lang.String> set, java.lang.String text)
      Add all words in the given text to the hash set.
      protected static java.lang.String asString​(java.lang.Object data, int type)
      INTERNAL.
      static void closeAll()
      INTERNAL Close all fulltext settings, freeing up memory.
      static void createIndex​(java.sql.Connection conn, java.lang.String schema, java.lang.String table, java.lang.String columnList)
      Create a new full text index for a table and column list.
      private static void createOrDropTrigger​(java.sql.Connection conn, java.lang.String schema, java.lang.String table, boolean create)  
      protected static SimpleResultSet createResultSet​(boolean data)
      Create an empty search result and initialize the columns.
      private static void createTrigger​(java.sql.Connection conn, java.lang.String schema, java.lang.String table)
      Create the trigger.
      static void dropAll​(java.sql.Connection conn)
      Drops all full text indexes from the database.
      static void dropIndex​(java.sql.Connection conn, java.lang.String schema, java.lang.String table)
      Drop an existing full text index for a table.
      protected static boolean hasChanged​(java.lang.Object[] oldRow, java.lang.Object[] newRow, int[] indexColumns)
      Check if a the indexed columns of a row probably have changed.
      private static void indexExistingRows​(java.sql.Connection conn, java.lang.String schema, java.lang.String table)
      Add the existing data to the index.
      static void init​(java.sql.Connection conn)
      Initializes full text search functionality for this database.
      protected static java.lang.String[][] parseKey​(java.sql.Connection conn, java.lang.String key)
      Parse a primary key condition into the primary key columns.
      protected static java.lang.String quoteSQL​(java.lang.Object data, int type)
      INTERNAL.
      private static java.lang.String quoteString​(java.lang.String data)  
      static void reindex​(java.sql.Connection conn)
      Re-creates the full text index for this database.
      protected static void removeAllTriggers​(java.sql.Connection conn, java.lang.String prefix)
      Remove all triggers that start with the given prefix.
      static java.sql.ResultSet search​(java.sql.Connection conn, java.lang.String text, int limit, int offset)
      Searches from the full text index for this database.
      protected static java.sql.ResultSet search​(java.sql.Connection conn, java.lang.String text, int limit, int offset, boolean data)
      Do the search.
      static java.sql.ResultSet searchData​(java.sql.Connection conn, java.lang.String text, int limit, int offset)
      Searches from the full text index for this database.
      protected static void setColumns​(int[] index, java.util.ArrayList<java.lang.String> keys, java.util.ArrayList<java.lang.String> columns)
      Set the column indices of a set of keys.
      static void setIgnoreList​(java.sql.Connection conn, java.lang.String commaSeparatedList)
      Change the ignore list.
      private static void setIgnoreList​(FullTextSettings setting, java.lang.String commaSeparatedList)  
      static void setWhitespaceChars​(java.sql.Connection conn, java.lang.String whitespaceChars)
      Change the whitespace characters.
      protected static java.sql.SQLException throwException​(java.lang.String message)
      Throw a SQLException with the given message.
      • Methods inherited from class java.lang.Object

        clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
    • Field Detail

      • FIELD_SCHEMA

        private static final java.lang.String FIELD_SCHEMA
        A column name of the result set returned by the searchData method.
        See Also:
        Constant Field Values
      • FIELD_TABLE

        private static final java.lang.String FIELD_TABLE
        A column name of the result set returned by the searchData method.
        See Also:
        Constant Field Values
      • FIELD_COLUMNS

        private static final java.lang.String FIELD_COLUMNS
        A column name of the result set returned by the searchData method.
        See Also:
        Constant Field Values
      • FIELD_KEYS

        private static final java.lang.String FIELD_KEYS
        A column name of the result set returned by the searchData method.
        See Also:
        Constant Field Values
      • FIELD_SCORE

        private static final java.lang.String FIELD_SCORE
        The hit score.
        See Also:
        Constant Field Values
      • SELECT_MAP_BY_WORD_ID

        private static final java.lang.String SELECT_MAP_BY_WORD_ID
        See Also:
        Constant Field Values
      • SELECT_ROW_BY_ID

        private static final java.lang.String SELECT_ROW_BY_ID
        See Also:
        Constant Field Values
      • FIELD_QUERY

        private static final java.lang.String FIELD_QUERY
        The column name of the result set returned by the search method.
        See Also:
        Constant Field Values
    • Constructor Detail

      • FullText

        public FullText()
    • Method Detail

      • init

        public static void init​(java.sql.Connection conn)
                         throws java.sql.SQLException
        Initializes full text search functionality for this database. This adds the following Java functions to the database:
        • FT_CREATE_INDEX(schemaNameString, tableNameString, columnListString)
        • FT_SEARCH(queryString, limitInt, offsetInt): result set
        • FT_REINDEX()
        • FT_DROP_ALL()
        It also adds a schema FT to the database where bookkeeping information is stored. This function may be called from a Java application, or by using the SQL statements:
         CREATE ALIAS IF NOT EXISTS FT_INIT FOR
              "org.h2.fulltext.FullText.init";
         CALL FT_INIT();
         
        Parameters:
        conn - the connection
        Throws:
        java.sql.SQLException - on failure
      • createIndex

        public static void createIndex​(java.sql.Connection conn,
                                       java.lang.String schema,
                                       java.lang.String table,
                                       java.lang.String columnList)
                                throws java.sql.SQLException
        Create a new full text index for a table and column list. Each table may only have one index at any time.
        Parameters:
        conn - the connection
        schema - the schema name of the table (case sensitive)
        table - the table name (case sensitive)
        columnList - the column list (null for all columns)
        Throws:
        java.sql.SQLException - on failure
      • reindex

        public static void reindex​(java.sql.Connection conn)
                            throws java.sql.SQLException
        Re-creates the full text index for this database. Calling this method is usually not needed, as the index is kept up-to-date automatically.
        Parameters:
        conn - the connection
        Throws:
        java.sql.SQLException - on failure
      • dropIndex

        public static void dropIndex​(java.sql.Connection conn,
                                     java.lang.String schema,
                                     java.lang.String table)
                              throws java.sql.SQLException
        Drop an existing full text index for a table. This method returns silently if no index for this table exists.
        Parameters:
        conn - the connection
        schema - the schema name of the table (case sensitive)
        table - the table name (case sensitive)
        Throws:
        java.sql.SQLException - on failure
      • dropAll

        public static void dropAll​(java.sql.Connection conn)
                            throws java.sql.SQLException
        Drops all full text indexes from the database.
        Parameters:
        conn - the connection
        Throws:
        java.sql.SQLException - on failure
      • search

        public static java.sql.ResultSet search​(java.sql.Connection conn,
                                                java.lang.String text,
                                                int limit,
                                                int offset)
                                         throws java.sql.SQLException
        Searches from the full text index for this database. The returned result set has the following column:
        • QUERY (varchar): the query to use to get the data. The query does not include 'SELECT * FROM '. Example: PUBLIC.TEST WHERE ID = 1
        • SCORE (float) the relevance score. This value is always 1.0 for the native fulltext search.
        Parameters:
        conn - the connection
        text - the search query
        limit - the maximum number of rows or 0 for no limit
        offset - the offset or 0 for no offset
        Returns:
        the result set
        Throws:
        java.sql.SQLException - on failure
      • searchData

        public static java.sql.ResultSet searchData​(java.sql.Connection conn,
                                                    java.lang.String text,
                                                    int limit,
                                                    int offset)
                                             throws java.sql.SQLException
        Searches from the full text index for this database. The result contains the primary key data as an array. The returned result set has the following columns:
        • SCHEMA (varchar): the schema name. Example: PUBLIC
        • TABLE (varchar): the table name. Example: TEST
        • COLUMNS (array of varchar): comma separated list of quoted column names. The column names are quoted if necessary. Example: (ID)
        • KEYS (array of values): comma separated list of values. Example: (1)
        • SCORE (float) the relevance score. This value is always 1.0 for the native fulltext search.
        Parameters:
        conn - the connection
        text - the search query
        limit - the maximum number of rows or 0 for no limit
        offset - the offset or 0 for no offset
        Returns:
        the result set
        Throws:
        java.sql.SQLException - on failure
      • setIgnoreList

        public static void setIgnoreList​(java.sql.Connection conn,
                                         java.lang.String commaSeparatedList)
                                  throws java.sql.SQLException
        Change the ignore list. The ignore list is a comma separated list of common words that must not be indexed. The default ignore list is empty. If indexes already exist at the time this list is changed, reindex must be called.
        Parameters:
        conn - the connection
        commaSeparatedList - the list
        Throws:
        java.sql.SQLException - on failure
      • setWhitespaceChars

        public static void setWhitespaceChars​(java.sql.Connection conn,
                                              java.lang.String whitespaceChars)
                                       throws java.sql.SQLException
        Change the whitespace characters. The whitespace characters are used to separate words. If indexes already exist at the time this list is changed, reindex must be called.
        Parameters:
        conn - the connection
        whitespaceChars - the list of characters
        Throws:
        java.sql.SQLException - on failure
      • asString

        protected static java.lang.String asString​(java.lang.Object data,
                                                   int type)
                                            throws java.sql.SQLException
        INTERNAL. Convert the object to a string.
        Parameters:
        data - the object
        type - the SQL type
        Returns:
        the string
        Throws:
        java.sql.SQLException - on failure
      • createResultSet

        protected static SimpleResultSet createResultSet​(boolean data)
        Create an empty search result and initialize the columns.
        Parameters:
        data - true if the result set should contain the primary key data as an array.
        Returns:
        the empty result set
      • parseKey

        protected static java.lang.String[][] parseKey​(java.sql.Connection conn,
                                                       java.lang.String key)
        Parse a primary key condition into the primary key columns.
        Parameters:
        conn - the database connection
        key - the primary key condition as a string
        Returns:
        an array containing the column name list and the data list
      • quoteSQL

        protected static java.lang.String quoteSQL​(java.lang.Object data,
                                                   int type)
                                            throws java.sql.SQLException
        INTERNAL. Convert an object to a String as used in a SQL statement.
        Parameters:
        data - the object
        type - the SQL type
        Returns:
        the SQL String
        Throws:
        java.sql.SQLException - on failure
      • removeAllTriggers

        protected static void removeAllTriggers​(java.sql.Connection conn,
                                                java.lang.String prefix)
                                         throws java.sql.SQLException
        Remove all triggers that start with the given prefix.
        Parameters:
        conn - the database connection
        prefix - the prefix
        Throws:
        java.sql.SQLException - on failure
      • setColumns

        protected static void setColumns​(int[] index,
                                         java.util.ArrayList<java.lang.String> keys,
                                         java.util.ArrayList<java.lang.String> columns)
                                  throws java.sql.SQLException
        Set the column indices of a set of keys.
        Parameters:
        index - the column indices (will be modified)
        keys - the key list
        columns - the column list
        Throws:
        java.sql.SQLException - on failure
      • search

        protected static java.sql.ResultSet search​(java.sql.Connection conn,
                                                   java.lang.String text,
                                                   int limit,
                                                   int offset,
                                                   boolean data)
                                            throws java.sql.SQLException
        Do the search.
        Parameters:
        conn - the database connection
        text - the query
        limit - the limit
        offset - the offset
        data - whether the raw data should be returned
        Returns:
        the result set
        Throws:
        java.sql.SQLException - on failure
      • addColumnData

        private static void addColumnData​(SessionLocal session,
                                          java.util.ArrayList<java.lang.String> columns,
                                          java.util.ArrayList<java.lang.String> data,
                                          Expression expr)
      • addWords

        protected static void addWords​(FullTextSettings setting,
                                       java.util.Set<java.lang.String> set,
                                       java.io.Reader reader)
        Add all words in the given text to the hash set.
        Parameters:
        setting - the fulltext settings
        set - the hash set
        reader - the reader
      • addWords

        protected static void addWords​(FullTextSettings setting,
                                       java.util.Set<java.lang.String> set,
                                       java.lang.String text)
        Add all words in the given text to the hash set.
        Parameters:
        setting - the fulltext settings
        set - the hash set
        text - the text
      • createTrigger

        private static void createTrigger​(java.sql.Connection conn,
                                          java.lang.String schema,
                                          java.lang.String table)
                                   throws java.sql.SQLException
        Create the trigger.
        Parameters:
        conn - the database connection
        schema - the schema name
        table - the table name
        Throws:
        java.sql.SQLException - on failure
      • createOrDropTrigger

        private static void createOrDropTrigger​(java.sql.Connection conn,
                                                java.lang.String schema,
                                                java.lang.String table,
                                                boolean create)
                                         throws java.sql.SQLException
        Throws:
        java.sql.SQLException
      • indexExistingRows

        private static void indexExistingRows​(java.sql.Connection conn,
                                              java.lang.String schema,
                                              java.lang.String table)
                                       throws java.sql.SQLException
        Add the existing data to the index.
        Parameters:
        conn - the database connection
        schema - the schema name
        table - the table name
        Throws:
        java.sql.SQLException - on failure
      • quoteString

        private static java.lang.String quoteString​(java.lang.String data)
      • setIgnoreList

        private static void setIgnoreList​(FullTextSettings setting,
                                          java.lang.String commaSeparatedList)
      • hasChanged

        protected static boolean hasChanged​(java.lang.Object[] oldRow,
                                            java.lang.Object[] newRow,
                                            int[] indexColumns)
        Check if a the indexed columns of a row probably have changed. It may return true even if the change was minimal (for example from 0.0 to 0.00).
        Parameters:
        oldRow - the old row
        newRow - the new row
        indexColumns - the indexed columns
        Returns:
        true if the indexed columns don't match
      • closeAll

        public static void closeAll()
        INTERNAL Close all fulltext settings, freeing up memory.
      • throwException

        protected static java.sql.SQLException throwException​(java.lang.String message)
                                                       throws java.sql.SQLException
        Throw a SQLException with the given message.
        Parameters:
        message - the message
        Returns:
        never returns normally
        Throws:
        java.sql.SQLException - the exception