package com.boonya.h2.lucene;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import org.h2.fulltext.FullTextLucene;
import com.boonya.h2.jdbc.connection.DBConnection;
/**
 * MyLucene.java
 * 功能:全文搜索[主要有两个类FullText和FullTextLucene]
 * @author boonya
 * v1.0 2013-03-11
 * ----创建全文搜索以及调用:
 *  CREATE ALIAS IF NOT EXISTS FT_INIT FOR "org.h2.fulltext.FullText.init";
 *  CALL FT_INIT();
 *  DROP TABLE IF EXISTS TEST;
 *  CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR);
 *  INSERT INTO TEST VALUES(1, 'Hello World');
 *  CALL FT_CREATE_INDEX('PUBLIC', 'TEST', NULL);
 *  
 *  SELECT * FROM FT_SEARCH('Hello', 0, 0); //result: "PUBLIC"."TEST" WHERE "ID"=1 
 *  
 *  SELECT T.* FROM FT_SEARCH_DATA('hello', 0, 0) FT, TEST T
 *  WHERE FT.TABLE='TEST' AND T.ID=FT.KEYS[0];
 *  --------------- 1  Hello World ----------------
 *  
 *  --重建全文探索--
 *  DELETE FROM TEST;
 *  INSERT INTO TEST VALUES(1, 'Hello World');
 *  INSERT INTO TEST VALUES(2, 'Goodbye World');
 *  INSERT INTO TEST VALUES(3, 'Hello Goodbye');
 *  CALL FT_REINDEX();
 *  SELECT * FROM FT_SEARCH('hello goodbye', 0, 0);//result:"PUBLIC"."TEST" WHERE "ID"=3
 *  
 *  SELECT T.* FROM FT_SEARCH_DATA('hello', 0, 0) FT, TEST T
 *  WHERE FT.TABLE='TEST' AND T.ID=FT.KEYS[0];
 *  -------results---------------------
 *      1 Hello World 
 *      3 Hello Goodbye 
 */
public class MyLucene {
	
	public void search(String text,int limit,int offset){
		Connection conn=DBConnection.getConnection();
		ResultSet rs=null;
		try {
			rs=FullTextLucene.search(conn, text, limit, offset);
			try {
				ResultSetMetaData meta=rs.getMetaData();
				while (rs.next()) {
					//System.out.println("--有数据");
					for (int i = 0,j=meta.getColumnCount(); i <j; i++) {
						System.out.println(meta.getColumnLabel(i+1)+" : "+rs.getString(i+1));
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
					rs.close();
					rs=null;
				}
				if(conn!=null){
					conn.close();
					conn=null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void searchData(String text,int limit,int offset){
		Connection conn=DBConnection.getConnection();
		ResultSet rs=null;
		try {
			rs=FullTextLucene.searchData(conn, text, limit, offset);
			try {
				ResultSetMetaData meta=rs.getMetaData();
				while (rs.next()) {
					//System.out.println("--有数据");
					for (int i = 0,j=meta.getColumnCount(); i <j; i++) {
						System.out.println(meta.getColumnLabel(i+1)+" : "+rs.getString(i+1));
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
					rs.close();
					rs=null;
				}
				if(conn!=null){
					conn.close();
					conn=null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void main(String[] args) {
		MyLucene lucene=new MyLucene();
//		lucene.search("hello", 10, 0);
		lucene.searchData("hello", 0, 0);
	}

}