Apache Lucene - Indexing a Database and Searching the Content

Here is a Java code sample of using Apache Lucene to create the index from a database. (I am using Lucene version 2.3.2 and MySQL)

final File INDEX_DIR = new File("index");

try{
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "password");
   StandardAnalyzer analyzer = new StandardAnalyzer();
   IndexWriter writer = new IndexWriter(INDEX_DIR, analyzer, true);
   System.out.println("Indexing to directory '" + INDEX_DIR + "'...");
   indexDocs(writer, conn);
   writer.optimize();
   writer.close();
} catch (Exception e) {
   e.printStackTrace();
}

void indexDocs(IndexWriter writer, Connection conn) throws Exception {
  String sql = "select id, name, color from pet";
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(sql);
  while (rs.next()) {
     Document d = new Document();
     d.add(new Field("id", rs.getString("id"), Field.Store.YES, Field.Index.NO));
     d.add(new Field("name", rs.getString("name"), Field.Store.NO, Field.Index.TOKENIZED));
     d.add(new Field("color", rs.getString("color"),Field.Store.NO, Field.Index.TOKENIZED));
     writer.addDocument(d);
 }
}

I assumed that there is a table named pet in the "test" database with the fields "id" "name" and "color". After running this a folder named index is created in the working directory including indexed content.


The following code (lucene searcher) shows how to search a record containing a particular keyword using the created lucene index.

Searcher searcher = new IndexSearcher(IndexReader.open("index"));
Query query = new QueryParser("color",analyzer).parse("white");
Hits hits = searcher.search(query);
String sql = "select * from pet where id = ?";

PreparedStatement pstmt = conn.prepareStatement(sql);
for (int i = 0; i < hits.length(); i++){
   id = hits.doc(i).get("id");
   pstmt.setString(1, id);
   displayResults(pstmt);
}

void displayResults(PreparedStatement pstmt) {
   try {
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
         System.out.println(rs.getString("name"));
         System.out.println(rs.getString("color")+"\n");
      }
   } catch (SQLException e) {
      e.printStackTrace();
   }
}