Lucene Foundation (iv)--combined with database usage

Demand

Most of the time we use the database needs fuzzy query, we usually use like statement to do, but this is not a lot of efficiency (I'm sorry we personally to test, many say so), then using Lucene to retrieve the words, the efficiency will be much higher.

Lucene combined with database steps

1. Write a traditional JDBC program that reads each piece of user information from the database
2. Create a Lucene document for each user record
   Document doc = new document ();
   And according to your needs, the individual fields of the user information corresponding to the field in Luncene document are added, such as:
   Doc.add (New Field ("NAME", "USERNAME", field.store.yes,field.index.un_tokenized));
   The doc is then added to the index, such as: Lucenewriter.adddocument (DOC);
   This establishes the index library of Lucene.
3. Write a search program for the index library (see Lucene document), you can quickly find the ID of the corresponding record by looking up the index library of Lucene.
4. Find related records in the database by ID

Note
In the process of indexing, indexes can be indexed incrementally, so that records that have already been indexed are not indexed. Implementation of the idea: Save the last time (Lasttime) of the new ID, when the index, the value of the query after the ID of the record index, update the record of the ID, when the database data modification, for this data to make index changes

Operation Example

Package lucene_demo05;

Import java.io.IOException;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;

Import Org.apache.lucene.analysis.Analyzer;
Import org.apache.lucene.document.Document;
Import Org.apache.lucene.document.Field;
Import Org.apache.lucene.document.TextField;
Import org.apache.lucene.index.CorruptIndexException;
Import Org.apache.lucene.index.DirectoryReader;
Import Org.apache.lucene.index.IndexWriter;
Import Org.apache.lucene.index.IndexWriterConfig;
Import Org.apache.lucene.index.IndexWriterConfig.OpenMode;
Import org.apache.lucene.queryparser.classic.ParseException;
Import Org.apache.lucene.queryparser.classic.QueryParser;
Import Org.apache.lucene.search.IndexSearcher;
Import Org.apache.lucene.search.Query;
Import Org.apache.lucene.search.ScoreDoc;
Import org.apache.lucene.search.highlight.InvalidTokenOffsetsException;
Import org.apache.lucene.store.Directory;
Import org.apache.lucene.store.RAMDirectory;
Import org.apache.lucene.util.Version;
Import Org.wltea.analyzer.lucene.IKAnalyzer;

/**
*
* Lucene is used in conjunction with the database
*
* @author Yipfun
*/
public class LuceneDemo05 {

private static final String driverclassname= "Com.mysql.jdbc.Driver";
private static final String url= "Jdbc:mysql://127.0.0.1:3306/test?characterencoding=utf-8";
private static final String username= "* * *";
private static final String password= "* * *";

Private static final version version = Version.lucene_4_9;
Private Directory directory = NULL;
Private Directoryreader ireader = null;
Private IndexWriter iwriter = null;
Private Ikanalyzer Analyzer;

Private Connection Conn;

Public LuceneDemo05 () {
directory = new Ramdirectory ();
}

Public Indexsearcher Getsearcher () {
try {
if (ireader==null) {
Ireader = directoryreader.open (directory);
} else {
Directoryreader tr = directoryreader.openifchanged (Ireader);
if (tr!=null) {
Ireader.close ();
Ireader = TR;
}
}
return new Indexsearcher (Ireader);
} catch (Corruptindexexception e) {
E.printstacktrace ();
} catch (IOException e) {
E.printstacktrace ();
}
return null;
}

Public Connection getconnection () {
if (This.conn = = null) {
try {
Class.forName (Driverclassname);
conn = drivermanager.getconnection (URL, username, password);
} catch (ClassNotFoundException e) {
E.printstacktrace ();
} catch (SQLException e) {
E.printstacktrace ();
}

}

Return conn;
}

Private Ikanalyzer Getanalyzer () {
if (analyzer = = null) {
return new Ikanalyzer ();
}else{
return analyzer;
}
}

public void CreateIndex () {
Connection conn = getconnection ();
ResultSet rs = null;
PreparedStatement pstmt = null;
if (conn = = null) {
System.out.println ("Get the connection error ...");
return;
}
String sql = "SELECT * from T_user";
try {
pstmt = conn.preparestatement (sql);
rs = Pstmt.executequery ();

Indexwriterconfig iwconfig = new Indexwriterconfig (version, Getanalyzer ());
Iwconfig.setopenmode (Openmode.create_or_append);
Iwriter = new IndexWriter (directory,iwconfig);

while (Rs.next ()) {
int id = rs.getint (1);
String name = rs.getstring (2);
String psd = rs.getstring (3);
Document doc = new document ();
Doc.add (New TextField ("id", id+ "", Field.Store.YES));
Doc.add (New TextField ("name", name+ "", Field.Store.YES));
Doc.add (New TextField ("PSD", psd+ "", Field.Store.YES));
Iwriter.adddocument (DOC);
}
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
} catch (IOException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}finally{
try {
if (iwriter! = null)
Iwriter.close ();
Rs.close ();
Pstmt.close ();
if (!conn.isclosed ()) {
Conn.close ();
}
} catch (IOException e) {
E.printstacktrace ();
} catch (SQLException e) {
TODO auto-generated Catch block
E.printstacktrace ();
}
}
}

public void Searchbyterm (String field,string keyword,int num) throws invalidtokenoffsetsexception{
Indexsearcher Isearcher = Getsearcher ();
Analyzer Analyzer = Getanalyzer ();
To construct a query object using Queryparser Queries Analyzer
Queryparser QP = new Queryparser (Version,field,analyzer);
What is the effect of this sentence?
Qp.setdefaultoperator (Queryparser.or_operator);
try {
Query query = qp.parse (keyword);
Scoredoc[] Hits;

Several methods to pay attention to searcher
hits = isearcher.search (query, NULL, num). Scoredocs;

System.out.println ("The IDS is =");
for (int i = 0; i < hits.length; i++) {
Document doc = Isearcher.doc (hits[i].doc);
System.out.print (Doc.get ("id") + "");
}

} catch (IOException e) {
E.printstacktrace ();
} catch (ParseException e) {
E.printstacktrace ();
}
}

public static void Main (string[] args) throws Invalidtokenoffsetsexception {
LuceneDemo05 ld = new LuceneDemo05 ();
Ld.createindex ();
Ld.searchbyterm ("name", "Bruce", 100);
}
}

After the index can get the need ID, this time by ID query database records, much faster.

Thinking

• This is to index the data of single table, when our business is complex, the required data is usually the result of multiple table union queries, how is our index established?

  1. Use a view to build a view of multiple tables and create an index on top of the view?
  2. or a single-table index, just to dissolve the federated query, in the Lucene index using multiple queries, find the target, in the database query?

• And when data is used, is the index actually associated with the database data, or is it associated with the result set?

The write test program found that it should be indexed on the data result set .

The test is as follows:
T_user table

T_user_teacher table

T_teacher table

Package lucene_demo05;

Import java.io.IOException;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;

Import Org.apache.lucene.analysis.Analyzer;
Import org.apache.lucene.document.Document;
Import Org.apache.lucene.document.Field;
Import Org.apache.lucene.document.TextField;
Import org.apache.lucene.index.CorruptIndexException;
Import Org.apache.lucene.index.DirectoryReader;
Import Org.apache.lucene.index.IndexWriter;
Import Org.apache.lucene.index.IndexWriterConfig;
Import Org.apache.lucene.index.IndexWriterConfig.OpenMode;
Import org.apache.lucene.queryparser.classic.ParseException;
Import Org.apache.lucene.queryparser.classic.QueryParser;
Import Org.apache.lucene.search.IndexSearcher;
Import Org.apache.lucene.search.Query;
Import Org.apache.lucene.search.ScoreDoc;
Import org.apache.lucene.search.highlight.InvalidTokenOffsetsException;
Import org.apache.lucene.store.Directory;
Import org.apache.lucene.store.RAMDirectory;
Import org.apache.lucene.util.Version;
Import Org.wltea.analyzer.lucene.IKAnalyzer;

/**
*
* Lucene is used in conjunction with the database
*
* @author Yipfun
*/
public class LuceneDemo06
{

private static final String Driverclassname = "Com.mysql.jdbc.Driver";
Private static final String URL = "Jdbc:mysql://127.0.0.1:3306/test?characterencoding=utf-8";
Private static final String username = "* * *";
Private static final String password = "* * *";

Private static final version version = Version.lucene_4_9;
Private Directory directory = NULL;
Private Directoryreader ireader = null;
Private IndexWriter iwriter = null;
Private Ikanalyzer Analyzer;

Private Connection Conn;

Public LuceneDemo06 ()
{
directory = new Ramdirectory ();
}

Public Indexsearcher Getsearcher ()
{
Try
{
if (Ireader = = null)
{
Ireader = directoryreader.open (directory);
} else
{
Directoryreader tr = directoryreader.openifchanged (Ireader);
if (tr! = NULL)
{
Ireader.close ();
Ireader = TR;
}
}
return new Indexsearcher (Ireader);
} catch (Corruptindexexception e)
{
E.printstacktrace ();
} catch (IOException e)
{
E.printstacktrace ();
}
return null;
}

Public Connection getconnection ()
{
if (This.conn = = null)
{
Try
{
Class.forName (Driverclassname);
conn = drivermanager.getconnection (URL, username, password);
} catch (ClassNotFoundException e)
{
E.printstacktrace ();
} catch (SQLException e)
{
E.printstacktrace ();
}

}

Return conn;
}

Private Ikanalyzer Getanalyzer ()
{
if (analyzer = = null)
{
return new Ikanalyzer ();
} else
{
return analyzer;
}
}

public void CreateIndex ()
{
Connection conn = getconnection ();
ResultSet rs = null;
PreparedStatement pstmt = null;
if (conn = = null)
{
System.out.println ("Get the connection error ...");
Return
}
String sql = "Select" + "U.id as UID," + "u.name as uname," + "u.psd as UPSD," + "U.email as Uemail," + "U.tel as Utel," + "T.id as Tid,"
+ "T.name as Tname" + "from T_user u, t_user_teacher ut, t_teacher t" + "where u.id=ut.u_id and ut.t_id= t.id";
Try
{
pstmt = conn.preparestatement (sql);
rs = Pstmt.executequery ();

Indexwriterconfig iwconfig = new Indexwriterconfig (version, Getanalyzer ());
Iwconfig.setopenmode (Openmode.create_or_append);
Iwriter = new IndexWriter (directory, iwconfig);

while (Rs.next ())
{
int id = rs.getint ("UID");
String name = rs.getstring ("uname");
String psd = rs.getstring ("upsd");
int tid = rs.getint ("Tid");
String tname = rs.getstring ("Tname");
Document doc = new document ();
Doc.add (New TextField ("UID", id + "", Field.Store.YES));
Doc.add (New TextField ("uname", Name + "", Field.Store.YES));
Doc.add (New TextField ("UPSD", PSD + "", Field.Store.YES));
Doc.add (New TextField ("Tid", Tid + "", Field.Store.YES));
Doc.add (New TextField ("Tname", Tname + "", Field.Store.YES));
Iwriter.adddocument (DOC);
}
} catch (SQLException e)
{
TODO auto-generated Catch block
E.printstacktrace ();
} catch (IOException e)
{
TODO auto-generated Catch block
E.printstacktrace ();
} finally
{
Try
{
if (iwriter! = null)
Iwriter.close ();
Rs.close ();
Pstmt.close ();
if (!conn.isclosed ())
{
Conn.close ();
}
} catch (IOException e)
{
E.printstacktrace ();
} catch (SQLException e)
{
TODO auto-generated Catch block
E.printstacktrace ();
}
}
}

public void Searchbyterm (string field, string keyword, int num) throws Invalidtokenoffsetsexception
{
Indexsearcher Isearcher = Getsearcher ();
Analyzer Analyzer = Getanalyzer ();
To construct a query object using Queryparser Queries Analyzer
Queryparser QP = new Queryparser (version, field, analyzer);
What is the effect of this sentence?
Qp.setdefaultoperator (Queryparser.or_operator);
Try
{
Query query = qp.parse (keyword);
Scoredoc[] Hits;

Several methods to pay attention to searcher
hits = isearcher.search (query, NULL, num). Scoredocs;

System.out.println ("The IDS is =");
for (int i = 0; i < hits.length; i++)
{
Document doc = Isearcher.doc (hits[i].doc);
System.out.print (Doc.get ("UID") + "");
}

} catch (IOException e)
{
E.printstacktrace ();
} catch (ParseException e)
{
E.printstacktrace ();
}
}

public static void Main (string[] args) throws Invalidtokenoffsetsexception
{
LUCENEDEMO06 ld = new LuceneDemo06 ();
Ld.createindex ();
Ld.searchbyterm ("Tname", "AAA", 100);
}
}

Search for the ID of a student who is a teacher AAA
Results:

Load Extension Dictionary: Ext.dic

Load extension Stop dictionary: stopword.dic

The IDS is = 1 2

Lucene Foundation (iv)--combined with database usage