利用lucene对整个数据库建立索引(lucene,SQL,JDBC)(

导言：

如果要对整个数据库做精确查询或模糊查询，我们怎么才可以做到？还是通过SQL查询吗？答案是否定的。因为，通过SQL对整个数据库做精确查询或模糊查询，速度将非常的慢；

lucene解决了这个问题。通过对表或者文本文件预先建立索引，可以很快的实现全文检索。

思路：

1、通过SQL得到所有表名的集合---->2、遍历所有的表，分别为每个表的每个记录建立索引；同时添加表的中文名以及表的说明的索引---->按Writer\analyzer\document\field的循序建索引。

package com.jrj.datamart.tree;

import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.io.Writer;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.lucene.analysis.cn.smart.SmartChineseAnalyzer;
import org.apache.lucene.document.Document;
import org.apache.lucene.document.Field;

import org.apache.lucene.index.CorruptIndexException;
import org.apache.lucene.index.IndexWriter;
import org.apache.lucene.store.FSDirectory;
import org.apache.lucene.store.LockObtainFailedException;
import org.apache.lucene.util.Version;
//import com.jrj.datamart.model.ApiIndicator;
//import com.jrj.datamart.model.ApiInfo;
//import com.jrj.datamart.model.ApiInfoQuery;
//import com.jrj.datamart.service.ApiInfoService;

//对整个数据库的建立索引；并给每个表添加，表的说明和字段的中文名；方便查询
//索引 Lucene 3.0.2
public class IndexerDB {

 // 保存索引文件的地方
 private static  String INDEX_DIR = "F:\\MyLuceneDB2\\LuceneFileIndexDir";
 private String index_dir;
 private File file=new File(INDEX_DIR);
 // 将要搜索TXT文件的地方
 private String data_dir;
 private String DATA_DIR = "F:\\Lucene";
 private String entityName;
// private ApiInfo apiInfo;
// private ApiInfoQuery apiInfoQuery = new ApiInfoQuery();
// private ApiInfoService apiInfoService;
// private List<ApiIndicator> apiIndicators = new ArrayList<ApiIndicator>();
// private ApiIndicator apiIndicator;
 private StringBuilder newsb = new StringBuilder();
 private ResultSet rs = null;
 private ResultSet tempRs = null;
 private ResultSetMetaData rsmd = null;
// private SmartChineseAnalyzer analyzer = new SmartChineseAnalyzer(
//   Version.LUCENE_30, true);
 private Document doc;
 private String tableName;

 public static void main(String[] args) throws Exception {
  IndexerDB indexDB=new IndexerDB();
        File file=new File(INDEX_DIR);
  //1
  indexDB.execute(file);
 }
 

//执行对数据库的索引化
//@param file
//@return
// @throws Exception
//  
 public String execute(File file) throws Exception {
  long start = new Date().getTime();
  //2
  int numIndexed=getAllTableNameFromDBAndIndexing(file);
  long end = new Date().getTime();
  System.out.println("Indexing " + numIndexed + " files took "
   + (end - start) + " milliseconds");
  return "success";
 }
 
// 查询所有的表，并遍历所有的表；
//调用getDataFromTable(tableName)获取表的记录；
//调用indexData(writer, rs, tableName)对单张表做索引
 public int getAllTableNameFromDBAndIndexing(File file)throws Exception {
  ResultSet rs=null;
  String sql1 = null;
  String sql2 = null;
  sql1 = "select [name] from [sysobjects] where [type] = 'u' order by [name]";
  sql2 = "show tables";
  SmartChineseAnalyzer analyzer = new SmartChineseAnalyzer(
    Version.LUCENE_30, true);
  IndexWriter writer = new IndexWriter(FSDirectory.open(file),
    analyzer, true, IndexWriter.MaxFieldLength.LIMITED);
  try {
   rs = JDBCUtil.execute(sql1);
  } catch (Exception e) {
   rs = JDBCUtil.execute(sql2);
  }
  while (rs.next()) {
   
   tableName=rs.getString(1);
   System.out.println("tableName: "+rs.getString(1));
   //ResultSetMetaData rsmd = rs.getMetaData(); 
   ResultSet tempRs=getDataFromTable(tableName);
   System.out.println("already get data of table");
   indexData(writer, tempRs, tableName);
  }
  int numIndexed = writer.numDocs();
  writer.optimize();
  writer.close();
  return numIndexed;
 }
   
 // 取得表的数据
 public ResultSet getDataFromTable(String tableName) throws Exception {
  String sql = "select * from " + tableName;
  System.out.println(""+sql);
  //String sql = "select * from " + "HK_STKCODE";
  return JDBCUtil.execute(sql);
 }
 
//对特定表的记录，采用特定writer，索引
//该方法是lucene的indexer的关键方法
//writer---->document---->field
 private void indexData(IndexWriter writer, ResultSet rs, String tableName)
   throws Exception{
  if (rs == null) {
   return;
  }
  // 取得实体名
  //System.out.println("tableName: " + tableName);
  entityName = getEntityName(tableName);
  System.out.println(" .entityName: " + entityName);
  while (rs.next()) {
   doc = new Document();
   rsmd = rs.getMetaData();
   int colsNum = rsmd.getColumnCount();
   //System.out.println("colsNum: "+colsNum);
   for (int i = 1; i < colsNum + 1; i++) {
   String  columnName=rsmd.getColumnName(i);  
   //System.out.println("columnName: "+columnName);
   //System.out.println(" rs.getString(i): "+ rs.getString(i));
   // 1、对该条记录的第一个字段进行索引
    doc.add(new Field(columnName, (rs.getString(i)==null?"":rs.getString(i)),
      Field.Store.YES, Field.Index.ANALYZED));
   }
// 2、在此处添加，表的说明(description);来至于apiinfo;根据表对应的实体名，找到表对应的apiinfo实体
//   apiInfoQuery.setEntityname(entityName);
//   apiInfo = apiInfoService.gainApiInfoByEntityName(entityName);
//   if (apiInfo != null) {
//    doc.add(new Field("apiDesc", apiInfo.toString(), Field.Store.YES,
//      Field.Index.ANALYZED));
//   }
// 3、在此处添加，表的中英文字段：
// 来自于apiindicator;根据表对应的实体名，去APIINFO中取找apiid,再去apiindicator中找apiindicator的实体
//   List<ApiIndicator> apiIndicators = apiInfoService
//     .gainApiIndicatorsByApiId(apiInfo.getId());
//   // 输出指标和描述结合为字符串
//   for (int i = 0; i < apiIndicators.size(); i++) {
//    apiIndicator = apiIndicators.get(i);
//    newsb.append(apiIndicator.getCnname() + " ")
//      .append(apiIndicator.getEnname() + " ")
//      .append(apiIndicator.getDescription() + "");
//    newsb.append("\n");
//   }
//   System.out.println("newsb.toString(): " + newsb.toString());
//   doc.add(new Field("outputFields", newsb.toString(),
//     Field.Store.YES, Field.Index.ANALYZED));
// 4、文件名
   doc.add(new Field("apiName", entityName,
     Field.Store.YES, Field.Index.ANALYZED));
   writer.addDocument(doc);
  }
 }

 

  //将表名通过字符串处理成为实体名
 public String getEntityName(String tableName) {
  // tableName = tableName.substring(0, tableName.indexOf(".txt"));
  StringBuilder sb = new StringBuilder();
  // 将"_"替换掉，如果有的话，以便处理成与API对应的实体一样的字符串。如：PUB_SEITGC.txt，RSH_RSHRPT_INDFO.txt
  if (tableName.indexOf("_") != -1) {
   String[] subStrings = tableName.split("_");
   for (int i = 0; i < subStrings.length; i++) {
    sb.append(subStrings[i]);
   }
   return sb.toString();
  } else {
   return tableName;
  }
 }

 public String getIndex_dir() {
  return index_dir;
 }

 public void setIndex_dir(String index_dir) {
  this.index_dir = index_dir;
 }

 public String getData_dir() {
  return data_dir;
 }

 public void setData_dir(String data_dir) {
  this.data_dir = data_dir;
 }

// public ApiInfoService getApiInfoService() {
//  return apiInfoService;
// }
//
// public void setApiInfoService(ApiInfoService apiInfoService) {
//  this.apiInfoService = apiInfoService;
// }

}

 

// 数据库查询类

//JDBCUtil用于取得连接；JDBCUtil.execute(sql)用于执行SQL，并返回resultset.

package com.jrj.datamart.tree;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtil {

 public static Connection conn = null;
 // 建立连接
 static void getConntion() {
  try {
   String driver_class = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
   // ?useUnicode=true&characterEncoding=utf-8
   String connection_url = "jdbc:sqlserver://localhost:1433;DatabaseName=UDM";
   String user_name = "sa";
   String db_password = "sa";
   Class.forName(driver_class);
   conn = DriverManager.getConnection(connection_url, user_name,
     db_password);
   conn.setAutoCommit(false);
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

 // 查询后，得到数据
 public static ResultSet execute(String sql) throws Exception {
  // 取得连接
  getConntion();
  // 写SQL
  // 得到一个statment对象
  Statement stmt = conn.createStatement();
  // 得到一个结果集
  return stmt.executeQuery(sql);
 }

 static void close() {
  if (conn != null) {
   try {
    conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

 public static Connection getConn() {
  return conn;
 }

 public static void setConn(Connection conn) {
  JDBCWriteExcel.conn = conn;
 }
}

 

 

文章出自：http://blog.sina.com.cn/s/blog_4f9ce8f30100nt5r.html