Does Lucene support searching between multiple index documents?

Question

In Database :

I have two tables. and there are three columns in each table. Table_A (column_primaryKey_A,column_A1,column_A2) Table_B (column_primaryKey_B,column_B1,column_B2)

Below is the SQL that I want to do using Lucene: SELECT * FROM Table_A,Table_B WHERE column_primaryKey_A = column_primaryKey_B AND column_A1 = XX AND column_B1 = XX

I want to create two index documents（Index_Table_A, Index_Table_A）and how do I implement above things using the mechanism of Lucene or Hibernate Search?.

Any help will be thankful!

Answer 1

I'm not sure why do you need to indexes or why do you want to use lucene in the first place, but I can tell you how to implement it using one index. (using two indexes is possible but is significantly less performance)

Using one index, index any document as following:

title = column_primaryKey_A
column_A1 = val...
column_A2 = val2...
column_B1 = val3...
column_B2 = val4...

When searching you would like to use a conjuntion query on field queries as following

query = (column_A1:XX) AND (column_B1:XX)

You could find more information here on lucene query syntax.

EDIT: Another option consist of two searches might be less performing: You could have a first search on the first column collecting all the corresponding titles (id's), and add them to a second query (on the second column):

Query: (Column_B1:XX) AND ( id1 OR id2 OR ...)

where the Id's are the results collected from the first search.