Adding date range to lucene search query

Question

I have a lucene query that makes fulltext search on indexed fields. I want to add date range to this query.

I found that question and used the answer there: How to search between dates (Hibernate Search)?

But when I want to get data between dates it returns nothing. I am using MSSQL db and type of date field is datetime. But it is annotated as @Temporal(TemporalType.TIMESTAMP) in entity class.

Here is my entity class:

...
@Entity 
@Indexed
@FullTextFilterDef(name = "tarihAraligi", impl = Satislar.class)
@Table(name = "satislar")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Satislar.findAll", query = "SELECT s FROM Satislar s"),
@NamedQuery(name = "Satislar.findById", query = "SELECT s FROM Satislar s      WHERE s.id = :id"),
@NamedQuery(name = "Satislar.findByAdet", query = "SELECT s FROM Satislar s WHERE s.adet = :adet"),
@NamedQuery(name = "Satislar.findByTarih", query = "SELECT s FROM Satislar s WHERE s.tarih = :tarih"),
@NamedQuery(name = "Satislar.findByUrunadi", query = "SELECT s FROM Satislar s WHERE s.urunadi = :urunadi"),
@NamedQuery(name = "Satislar.findByMusteriadi", query = "SELECT s FROM Satislar s WHERE s.musteriadi = :musteriadi"),
@NamedQuery(name = "Satislar.findByUrunkategori", query = "SELECT s FROM Satislar s WHERE s.urunkategori = :urunkategori"),
@NamedQuery(name = "Satislar.findByUrunfiyat", query = "SELECT s FROM Satislar s WHERE s.urunfiyat = :urunfiyat"),
@NamedQuery(name = "Satislar.findByUrunalis", query = "SELECT s FROM Satislar s WHERE s.urunalis = :urunalis")})
public class Satislar implements Serializable {

private static final long serialVersionUID = 1L;
@Id
@DocumentId
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Integer id;
@Basic(optional = false)
@Column(name = "adet")
private int adet;
@Basic(optional = false)
@Column(name = "tarih")
@Temporal(TemporalType.TIMESTAMP)
private Date tarih;
@Basic(optional = false)
@Column(name = "urunadi")
@Field(index=Index.YES, analyze=Analyze.YES, store=Store.NO)
private String urunadi;
@Basic(optional = false)
@Column(name = "musteriadi")
@Field(index=Index.YES, analyze=Analyze.YES, store=Store.NO)
private String musteriadi;
@Basic(optional = false)
@Column(name = "urunkategori")
@Field(index=Index.YES, analyze=Analyze.NO, store=Store.NO)
private String urunkategori;
@Basic(optional = false)
@Column(name = "urunfiyat")
private int urunfiyat;
@Basic(optional = false)
@Column(name = "urunalis")
private int urunalis;
...

And this is where I make fulltext search:

fullTextSession.beginTransaction();

    QueryBuilder b = fullTextSession.getSearchFactory()
            .buildQueryBuilder().forEntity(Satislar.class).get();

    Query luceneQuery
            = b.keyword()
            .wildcard()
            .onFields(fields)
            .matching(kelime + "*")
             .createQuery();


   Query datequery = b
    .range()
        .onField( "tarih" ).ignoreFieldBridge()
        .from( DateTools.dateToString( new Date(2015, 12, 18 , 17, 40, 40), DateTools.Resolution.MILLISECOND ) )
        .to( DateTools.dateToString( new Date(2015, 12, 26 , 17, 40, 40), DateTools.Resolution.MILLISECOND ) ).excludeLimit()
        .createQuery();

    org.hibernate.Query fullTextQuery = fullTextSession.createFullTextQuery(datequery);
    List<Satislar> kayitliSatislar = fullTextQuery.list();


    dtmSonuc.setRowCount(0);

    for (int i = 0; i < kayitliSatislar.size(); i++) {

        Satislar satis = kayitliSatislar.get(i);

        dtmSonuc.addRow(new String[]{satis.getMusteriadi(), satis.getUrunkategori(), satis.getUrunadi(),
            Integer.toString(satis.getAdet()), Integer.toString(satis.getUrunfiyat()), satis.getTarih().toString()});

    }

There are 2 different queries. One makes fulltext wildcard search on specified fields and it works. And the other is supposed to make range search but it does not work

I have 3 questions:

1-I am using MSSQL db and type of date field is datetime. But it is annotated as @Temporal(TemporalType.TIMESTAMP) in entity class. Is that a problem?

2-Why range search is not working?

3-Can I combine fulltext search with range search?

score 1 · Answer 1 · answered Dec 26 '15 at 16:21

1

Try to add this annotation to your Date fields :

@Basic(optional = false)
@Column(name = "tarih")
@Temporal(TemporalType.TIMESTAMP)
@DateBridge(resolution = Resolution.DAY)
private Date tarih;

If it is still not working, try to use Lucene native queries like this :

TermRangeQuery luceneRangeDateQuery = new TermRangeQuery(fieldName, DateTools.dateToString(from, DateTools.Resolution.DAY), 
                DateTools.dateToString(to, DateTools.Resolution.DAY), true, true);

This works always. (You can change your DateTools.Resolution.DAY to HOUR, SECOND, YEAR ...)

answered Dec 26 '15 at 16:21

Bilal BBB

1,154
13
20

when i try TermRangeQuery I get **string cannot be converted to bytesref** error – Erdel Dec 26 '15 at 16:43
Could you show more details please ? what do you put in the place of `from` and `to` – Bilal BBB Dec 26 '15 at 16:53
`TermRangeQuery luceneRangeDateQuery = new TermRangeQuery("tarih", DateTools.dateToString(new Date(2015, 12, 18, 17, 40, 40), DateTools.Resolution.DAY), DateTools.dateToString(new Date(2015, 12, 26, 17, 40, 40), DateTools.Resolution.DAY), true, true)` this gives the error – Erdel Dec 26 '15 at 17:07
Try instead `new GregorianCalendar(2015, 12, 18, 17, 40, 40).getTime()` – Bilal BBB Dec 26 '15 at 17:28
It does not cast , i dont understand why. – Erdel Dec 26 '15 at 17:49
Try to debug and see what is the problem, the same thing works for me. – Bilal BBB Dec 26 '15 at 17:54
1

Try using `TermRangeQuery.toStringRange`, instead of using the ctor (that is: `TermRangeQuery.toStringRange(fieldName, DateTools.dateToString(from, DateTools.Resolution.DAY), DateTools.dateToString(to, DateTools.Resolution.DAY), true, true);`) – femtoRgon Dec 26 '15 at 18:00
It worked as that way : `new BytesRef(DateTools.dateToString(new Date(2015, 12, 18, 17, 40, 40), DateTools.Resolution.DAY)` but still does not return any row from db. I think there is a problem with date field type. output of a date object is different than date which is coming from mssql datetime field. – Erdel Dec 26 '15 at 18:02
Use luke to verify your indexes. – Bilal BBB Dec 26 '15 at 18:06
@BillBilal I dont have trouble with fulltext searching on 3 other fields. Do I still need to verify indexes? Just have a problem with date field. – Erdel Dec 26 '15 at 18:31
Yes you need, because Lucene will compare Strings and not Dates. You need to know if Dates were really indexed. – Bilal BBB Dec 26 '15 at 18:59
I have asked another, (more clear) question. I will try luke if this does not works too. Could you look at that too? https://stackoverflow.com/questions/34474653/date-range-query-with-lucene – Erdel Dec 26 '15 at 19:47

Adding date range to lucene search query

1 Answers1