fulltext.java

非常棒的java数据库

        for (int i = 0; i < index.keys.length; i++) {
            if (i > 0) {
                buff.append(" AND ");
            }
            int columnIndex = index.keys[i];
            buff.append(StringUtils.quoteIdentifier(index.columnNames[columnIndex]));
            Object o = row[columnIndex];
            if (o == null) {
                buff.append(" IS NULL");
            } else {
                buff.append("=");
                buff.append(quoteSQL(o, dataTypes[columnIndex]));
            }
        }
        String key = buff.toString();
        return key;
    }

    private String quoteString(String data) {
        if (data.indexOf('\'') < 0) {
            return "'" + data + "'";
        }
        StringBuffer buff = new StringBuffer(data.length() + 2);
        buff.append('\'');
        for (int i = 0; i < data.length(); i++) {
            char ch = data.charAt(i);
            if (ch == '\'') {
                buff.append(ch);
            }
            buff.append(ch);
        }
        buff.append('\'');
        return buff.toString();
    }

    private String quoteBinary(byte[] data) {
        return "'" + ByteUtils.convertBytesToString(data) + "'";
    }

    private String asString(Object data, int type) throws SQLException {
        if (data == null) {
            return "NULL";
        }
        switch (type) {
        case Types.BIT:
        case DataType.TYPE_BOOLEAN:
        case Types.INTEGER:
        case Types.BIGINT:
        case Types.DECIMAL:
        case Types.DOUBLE:
        case Types.FLOAT:
        case Types.NUMERIC:
        case Types.REAL:
        case Types.SMALLINT:
        case Types.TINYINT:
        case Types.DATE:
        case Types.TIME:
        case Types.TIMESTAMP:
        case Types.LONGVARCHAR:
        case Types.CHAR:
        case Types.VARCHAR:
            return data.toString();
        case Types.VARBINARY:
        case Types.LONGVARBINARY:
        case Types.BINARY:
        case Types.JAVA_OBJECT:
        case Types.CLOB:
        case Types.OTHER:
        case Types.BLOB:
        case Types.STRUCT:
        case Types.REF:
        case Types.NULL:
        case Types.ARRAY:
        case DataType.TYPE_DATALINK:
        case Types.DISTINCT:
            throw new SQLException("FULLTEXT", "Unsupported column data type: " + type);
        default:
            return "";
        }
    }

    private String quoteSQL(Object data, int type) throws SQLException {
        if (data == null) {
            return "NULL";
        }
        switch (type) {
        case Types.BIT:
        case DataType.TYPE_BOOLEAN:
        case Types.INTEGER:
        case Types.BIGINT:
        case Types.DECIMAL:
        case Types.DOUBLE:
        case Types.FLOAT:
        case Types.NUMERIC:
        case Types.REAL:
        case Types.SMALLINT:
        case Types.TINYINT:
            return data.toString();
        case Types.DATE:
        case Types.TIME:
        case Types.TIMESTAMP:
        case Types.LONGVARCHAR:
        case Types.CHAR:
        case Types.VARCHAR:
            return quoteString(data.toString());
        case Types.VARBINARY:
        case Types.LONGVARBINARY:
        case Types.BINARY:
            return quoteBinary((byte[]) data);
        case Types.JAVA_OBJECT:
        case Types.CLOB:
        case Types.OTHER:
        case Types.BLOB:
        case Types.STRUCT:
        case Types.REF:
        case Types.NULL:
        case Types.ARRAY:
        case DataType.TYPE_DATALINK:
        case Types.DISTINCT:
            throw new SQLException("FULLTEXT", "Unsupported key data type: " + type);
        default:
            return "";
        }
    }

    private static void addWords(FullTextSettings setting, HashSet set, String text) {
        StringTokenizer tokenizer = new StringTokenizer(text, " \t\n\r\f+\"*%&/()=?'!,.;:-_#@|^~`{}[]");
        while (tokenizer.hasMoreTokens()) {
            String word = tokenizer.nextToken();
            word = setting.convertWord(word);
            if (word != null) {
                set.add(word);
            }
        }
    }

    private int[] getWordIds(FullTextSettings setting, Object[] row) throws SQLException {
        HashSet words = new HashSet();
        for (int i = 0; i < index.indexColumns.length; i++) {
            int idx = index.indexColumns[i];
            String data = asString(row[idx], dataTypes[idx]);
            addWords(setting, words, data);
        }
        HashMap allWords = setting.getWordList();
        int[] wordIds = new int[words.size()];
        Iterator it = words.iterator();
        for (int i = 0; it.hasNext(); i++) {
            String word = (String) it.next();
            Integer wId = (Integer) allWords.get(word);
            int wordId;
            if (wId == null) {
                prepInsertWord.setString(1, word);
                prepInsertWord.execute();
                ResultSet rs = JdbcUtils.getGeneratedKeys(prepInsertWord);
                rs.next();
                wordId = rs.getInt(1);
                allWords.put(word, ObjectUtils.getInteger(wordId));
            } else {
                wordId = wId.intValue();
            }
            wordIds[i] = wordId;
        }
        Arrays.sort(wordIds);
        return wordIds;
    }

    private void insert(FullTextSettings setting, Object[] row) throws SQLException {
        String key = getKey(row);
        int hash = key.hashCode();
        prepInsertRow.setInt(1, hash);
        prepInsertRow.setInt(2, index.id);
        prepInsertRow.setString(3, key);
        prepInsertRow.execute();
        ResultSet rs = JdbcUtils.getGeneratedKeys(prepInsertRow);
        rs.next();
        int rowId = rs.getInt(1);
        prepInsertMap.setInt(1, rowId);
        int[] wordIds = getWordIds(setting, row);
        for (int i = 0; i < wordIds.length; i++) {
            prepInsertMap.setInt(2, wordIds[i]);
            prepInsertMap.execute();
        }
    }

    private void delete(FullTextSettings setting, Object[] row) throws SQLException {
        String key = getKey(row);
        int hash = key.hashCode();
        prepSelectRow.setInt(1, hash);
        prepSelectRow.setInt(2, index.id);
        prepSelectRow.setString(3, key);
        ResultSet rs = prepSelectRow.executeQuery();
        if (rs.next()) {
            int rowId = rs.getInt(1);
            prepDeleteMap.setInt(1, rowId);
            int[] wordIds = getWordIds(setting, row);
            for (int i = 0; i < wordIds.length; i++) {
                prepDeleteMap.setInt(2, wordIds[i]);
                prepDeleteMap.executeUpdate();
            }
            prepDeleteRow.setInt(1, hash);
            prepDeleteRow.setInt(2, index.id);
            prepDeleteRow.setString(3, key);
            prepDeleteRow.executeUpdate();
        }
    }

    /**
     * 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:
     * <ul>
     * <li>SCHEMA (varchar): The schema name. Example: PUBLIC </li>
     * <li>TABLE (varchar): The table name. Example: TEST </li>
     * <li>COLUMNS (array of varchar): Comma separated list of quoted column
     * names. The column names are quoted if necessary. Example: (ID) </li>
     * <li>KEYS (array of values): Comma separated list of values. Example: (1)
     * </li>
     * </ul>
     * 
     * @param conn the connection
     * @param text the search query
     * @param limit the maximum number of rows or 0 for no limit
     * @param offset the offset or 0 for no offset
     * @return the result set
     */
    public static ResultSet searchData(Connection conn, String text, int limit, int offset) throws SQLException {
        return search(conn, text, limit, offset, true);
    }

    /**
     * Searches from the full text index for this database.
     * The returned result set has the following column:
     * <ul><li>QUERY (varchar): The query to use to get the data.
     * The query does not include 'SELECT * FROM '. Example:
     * PUBLIC.TEST WHERE ID = 1
     * </li></ul>
     *
     * @param conn the connection
     * @param text the search query
     * @param limit the maximum number of rows or 0 for no limit
     * @param offset the offset or 0 for no offset
     * @return the result set
     */
    public static ResultSet search(Connection conn, String text, int limit, int offset) throws SQLException {
        return search(conn, text, limit, offset, false);
    }
    
    protected static SimpleResultSet createResultSet(boolean data) throws SQLException {
        SimpleResultSet result = new SimpleResultSet();
        if (data) {
            result.addColumn(FullText.FIELD_SCHEMA, Types.VARCHAR, 0, 0);
            result.addColumn(FullText.FIELD_TABLE, Types.VARCHAR, 0, 0);
            result.addColumn(FullText.FIELD_COLUMNS, Types.ARRAY, 0, 0);
            result.addColumn(FullText.FIELD_KEYS, Types.ARRAY, 0, 0);
        } else {
            result.addColumn(FullText.FIELD_QUERY, Types.VARCHAR, 0, 0);
        }
        return result;
    }

    private static ResultSet search(Connection conn, String text, int limit, int offset, boolean data) throws SQLException {
        SimpleResultSet result = createResultSet(data);
        if (conn.getMetaData().getURL().startsWith("jdbc:columnlist:")) {
            // this is just to query the result set columns
            return result;
        }
        FullTextSettings setting = FullTextSettings.getInstance(conn);
        HashSet words = new HashSet();
        addWords(setting, words, text);
        HashSet rIds = null, lastRowIds = null;
        HashMap allWords = setting.getWordList();

        PreparedStatement prepSelectMapByWordId = setting.getPrepSelectMapByWordId();
        for (Iterator it = words.iterator(); it.hasNext();) {
            lastRowIds = rIds;
            rIds = new HashSet();
            String word = (String) it.next();
            Integer wId = (Integer) allWords.get(word);
            if (wId == null) {
                continue;
            }
            prepSelectMapByWordId.setInt(1, wId.intValue());
            ResultSet rs = prepSelectMapByWordId.executeQuery();
            while (rs.next()) {
                Integer rId = ObjectUtils.getInteger(rs.getInt(1));
                if (lastRowIds == null || lastRowIds.contains(rId)) {
                    rIds.add(rId);
                }
            }
        }
        if (rIds == null || rIds.size() == 0) {
            return result;
        }
        PreparedStatement prepSelectRowById = setting.getPrepSelectRowById();
        int rowCount = 0;
        for (Iterator it = rIds.iterator(); it.hasNext();) {
            int rowId = ((Integer) it.next()).intValue();
            prepSelectRowById.setInt(1, rowId);
            ResultSet rs = prepSelectRowById.executeQuery();
            if (!rs.next()) {
                continue;
            }
            if (offset > 0) {
                offset--;
            } else {
                String key = rs.getString(1);
                int indexId = rs.getInt(2);
                IndexInfo index = setting.getIndexInfo(indexId);
                if (data) {
                    Object[][] columnData = parseKey(conn, key);
                    Object[] row = new Object[] {  
                        index.schemaName,
                        index.tableName,
                        columnData[0],
                        columnData[1]
                    };
                    result.addRow(row);
                } else {
                    StringBuffer buff = new StringBuffer();
                    buff.append(StringUtils.quoteIdentifier(index.schemaName));
                    buff.append('.');
                    buff.append(StringUtils.quoteIdentifier(index.tableName));
                    buff.append(" WHERE ");
                    buff.append(key);
                    String query = buff.toString();
                    result.addRow(new String[] { query });
                }
                rowCount++;
                if (limit > 0 && rowCount >= limit) {
                    break;
                }
            }
        }
        return result;
    }

    protected static Object[][] parseKey(Connection conn, String key) throws SQLException {
        ArrayList columns = new ArrayList();
        ArrayList data = new ArrayList();
        JdbcConnection c = (JdbcConnection) conn;
        Session session = (Session) c.getSession();
        Parser p = new Parser(session);
        Expression expr = p.parseExpression(key);
        addColumnData(columns, data, expr);
        Object[] col = new Object[columns.size()];
        columns.toArray(col);
        Object[] dat = new Object[columns.size()];
        data.toArray(dat);
        Object[][] columnData = new Object[][] {
                col, dat
        };
        return columnData;
    }
    
    private static void addColumnData(ArrayList columns, ArrayList data, Expression expr) {
        if (expr instanceof ConditionAndOr) {
            ConditionAndOr and = (ConditionAndOr) expr;
            Expression left = and.getExpression(true);
            Expression right = and.getExpression(false);
            addColumnData(columns, data, left);
            addColumnData(columns, data, right);
        } else {
            Comparison comp = (Comparison) expr;
            ExpressionColumn ec = (ExpressionColumn) comp.getExpression(true);
            ValueExpression ev = (ValueExpression) comp.getExpression(false);
            String columnName = ec.getColumnName();
            columns.add(columnName);
            if (ev == null) {
                data.add(null);
            } else {
                data.add(ev.getValue(null).getString());
            }
        }
    }

}