Sqlite FTS experiment (Full Text "Near" Search). This really is an experiment. Not kidding!

gistfile1.java

package com.javaforu.sqlite.demo;

import java.sql.*;

/*
* Author: Ashwin Jayaprakash / Date: Oct 23, 2010 / Time: 3:58:22 PM / Contact: http://www.ashwinjayaprakash.com
*
* Sqlite jdbc library (sqlite-jdbc-3.7.2.jar) from: http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC
*/
public class FtsSample {
    public static void main(String[] args) throws ClassNotFoundException {
        Class.forName("org.sqlite.JDBC");

        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:sqlite::memory:");
            Statement statement = connection.createStatement();
            statement.setQueryTimeout(30);

            statement.executeUpdate("drop table if exists timeseries");
            statement.executeUpdate("create virtual table timeseries using fts3(symbol text, series text);");

            statement.executeUpdate(
                    "insert into timeseries values('goog', 'U010 U002 U010 D010 U005 U015 D002 D003 U001')");
            statement.executeUpdate(
                    "insert into timeseries values('yhoo', 'D010 D005 U005 U001 D001 U015 D003 U015 U005')");

            //-------------

            ResultSet rs = statement.executeQuery(
                    "select symbol, snippet(timeseries, '{', '}')" +
                            " from timeseries" +
                            " where symbol = 'goog' and series match 'U005 U015 D003'" +
                            " union all" +
                            " select symbol, snippet(timeseries, '{', '}')" +
                            " from timeseries" +
                            " where symbol = 'yhoo' and series match 'U005 U015 D003'");

            while (rs.next()) {
                System.out.println(rs.getString(1) + "\t" + rs.getString(2));
            }

            rs.close();

            //-------------

            statement.executeUpdate("update timeseries set series = series || ' ' || 'D012' where symbol = 'goog'");

            //-------------

            System.out.println("~~~~~~~~~");

            rs = statement.executeQuery(
                    "select symbol, snippet(timeseries, '{', '}')" +
                            " from timeseries" +
                            " where symbol = 'goog' and series match 'U005 NEAR/1 U015 NEAR/1 D003'" +
                            " union all" +
                            " select symbol, snippet(timeseries, '{', '}')" +
                            " from timeseries" +
                            " where symbol = 'yhoo' and series match 'U005 NEAR/1 U015 NEAR/1 D003'");

            while (rs.next()) {
                System.out.println(rs.getString(1) + "\t" + rs.getString(2));
            }

            rs.close();
        }
        catch (SQLException e) {
            // if the error message is "out of memory",
            // it probably means no database file is found
            System.err.println(e.getMessage());
        }
        finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            }
            catch (SQLException e) {
                // connection close failed.
                System.err.println(e);
            }
        }
    }

}