루씬관련. MySQL 적용하기

[Hil]

# Mysql 데이터를 추출하여 Lucene에서 인덱싱 작업

사용 버전
Lucene Library : 4.2.1

lucene-core-4.2.1.jar (22)

lucene-analyzers-common-4.2.1.jar (42)

lucene-codecs-4.2.1.jar (52)

lucene-queryparser-4.2.1.jar (53)

한글 형태소 분석

Library : koreananalyzer.4x

koreananalyzer.4x-20130522.jar (56)

 
- 루씬 한글 분석기 다운로드 : http://cafe.naver.com/korlucene

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146

import java.io.File; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.Date;   import org.apache.lucene.analysis.Analyzer; import org.apache.lucene.analysis.kr.KoreanAnalyzer; import org.apache.lucene.document.Document; import org.apache.lucene.document.Field; import org.apache.lucene.document.FieldType; import org.apache.lucene.index.IndexWriter; import org.apache.lucene.index.IndexWriterConfig; import org.apache.lucene.index.FieldInfo.IndexOptions; import org.apache.lucene.store.FSDirectory; import org.apache.lucene.util.Version;   public class IndexOpenDBMS {     private String driver;     private String url;     private String userId;     private String password;           public IndexOpenDBMS(String indexDir){         // Mysql 드라이버 설정         this.driver= "com.mysql.jdbc.Driver";         // Mysql 접속 정보 설정         this.url = "jdbc:mysql://localhost:3306/database_name?autoReconnect=true";         // Mysql 계정 설정         this.userId = "userId";         this.password = "userPassword";                   // Indexing 처리 메서드 실행         this.indexLuceneDevelop(indexDir);     }           public void indexLuceneDevelop(String indexDir){                   // Build the query information for the MySQL connection:         String query = "SELECT SEQ"                      + " , TITLE"                      + " , CONTENT"                      + " FROM USER_TABLE";          String dbUrl = url;         String dbClass = driver;         Connection conn = null;           // Try to connect to MySQL:         try         {             Class.forName(dbClass);             conn = DriverManager.getConnection (dbUrl, userId, password);               // Create a File object with theFile name of the Lucene index and open that index:             File file = new File(indexDir);             FSDirectory directory = FSDirectory.open(file);               // 한글 형태소 분석 라이브러리 사용(루씬 버전에 맞춰서 사용)             Analyzer analyzer = new KoreanAnalyzer(Version.LUCENE_42);               // Build an IndexWriterConfig to use as the configuration for the indexWriter:             IndexWriterConfig indexWriterConfig = new IndexWriterConfig(Version.LUCENE_42, analyzer);             indexWriterConfig.setOpenMode(IndexWriterConfig.OpenMode.CREATE); // 신규 인덱스 생성(기존 삭제)               // Build the indexWriter:             IndexWriter writer = new IndexWriter(directory, indexWriterConfig);               // Query the database:             PreparedStatement pstmt = conn.prepareStatement(query);             ResultSet rslt = pstmt.executeQuery();               // 필드 타입 설정             FieldType fieldType = new FieldType();             fieldType.setIndexed(true);  // 인덱스 포함 여부             fieldType.setStored(true);    // 검색시 출력 여부             fieldType.setTokenized(true);  // 필드 토큰화 여부             fieldType.setIndexOptions(IndexOptions.DOCS_ONLY); // 문서만 색인 /** ### IndexOptions ### DOCS_AND_FREQS : 문서와 용어 빈도를 포함, 위치 생략 DOCS_AND_FREQS_AND_POSITIONS : 문서와 용어 빈도, 위치값을 포함 DOCS_AND_FREQS_AND_POSITIONS_AND_OFFSETS : 문서와 용어 빈도, 위치값, 오프셋 포함 DOCS_ONLY : 문서만 색인, 용어 빈도, 위치 생략  */                          // 기존 생성된 인덱스를 모두 제거(중복 생성 방지)             writer.deleteAll();             int totCnt = 0;             while (rslt.next()){                 try{                     if (rslt.getString("SEQ") != null){                         // Iterate through the results that are returned:                         Document doc = new Document();                         // Add each individual result to the document for indexing:                         doc.add(new Field("SEQ", rslt.getString("SEQ"), fieldType)); // 포스트 번호                         doc.add(new Field("TITLE", rslt.getString("TITLE"), fieldType)); // 제목                         doc.add(new Field("CONTENT", rslt.getString("CONTENT"), fieldType)); // 내용                                                   // 통합검색 인덱스                         doc.add(new Field("SEARCH_ALL", nullToString(rslt.getString("TITLE"), ""), fieldType));                         doc.add(new Field("SEARCH_ALL", nullToString(rslt.getString("CONTENT"), ""), fieldType));                                                   writer.addDocument(doc);                         ++totCnt;                     }                 }catch (Exception e){                     System.err.println(e);                 }             }             System.out.println("# DevelopDBMS Indexing - adding Total Count:"+totCnt);                           // Close the indexWriter, Analyzer, Directory, and MySQL connection at the end:             writer.commit();             writer.close();             analyzer.close();             directory.close();             rslt.close();             conn.close();         }catch (Exception e){             System.err.println ("Cannot connect to database server: " + e);         }     }           /**      * String이 null인지 체크하여 null인경우 지정한 문자열로 변환      */    public static String nullToString(String str, String nullWord){            return str != null && !str.equals("") ? str : nullWord;    }       public static void main(String[] arr){         // 인덱스 파일 저장위치 지정         String indexDir = "D:\\01.MYSPACE_2013\\indexs\\dbms";           DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");         Date start = new Date();         new IndexOpenDBMS(indexDir); // Indexing 객체 생성및 호출         Date end = new Date();           System.out.println("# Start-Time: "+formatter.format(start)+", End-Time: "+formatter.format(end));         System.out.println("# Elapse-Time: "+(end.getTime()-start.getTime())+"ms");     } }

# Lucene에서 인덱싱 파일을 이용한 검색

SearchOpenDBMS.java

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227

import java.io.BufferedReader; import java.io.File; import java.io.IOException; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date;   import org.apache.lucene.analysis.Analyzer; import org.apache.lucene.analysis.kr.KoreanAnalyzer; import org.apache.lucene.document.Document; import org.apache.lucene.index.DirectoryReader; import org.apache.lucene.index.IndexReader; import org.apache.lucene.queryparser.classic.ParseException; import org.apache.lucene.queryparser.classic.QueryParser; import org.apache.lucene.search.Explanation; import org.apache.lucene.search.IndexSearcher; import org.apache.lucene.search.Query; import org.apache.lucene.search.ScoreDoc; import org.apache.lucene.search.TopDocs; import org.apache.lucene.search.vectorhighlight.BaseFragmentsBuilder; import org.apache.lucene.search.vectorhighlight.FastVectorHighlighter; import org.apache.lucene.search.vectorhighlight.FragListBuilder; import org.apache.lucene.search.vectorhighlight.FragmentsBuilder; import org.apache.lucene.search.vectorhighlight.ScoreOrderFragmentsBuilder; import org.apache.lucene.search.vectorhighlight.SimpleFragListBuilder; import org.apache.lucene.store.FSDirectory; import org.apache.lucene.util.Version;   /** * # 인덱싱 되어 있는 정보를 검색 */ public class SearchOpenDBMS {     // 한글 형태소 분석 라이브러리 사용(루씬 버전에 맞춰서 사용)     private static Analyzer analyzer = new KoreanAnalyzer();           public static ArrayList<SearchOpenTable> searchLucene(String indexDir, String field, String kwd             , int repeat, int hitsPerPage, boolean raw, boolean interactive) throws IOException, ParseException{           IndexReader reader = DirectoryReader.open(FSDirectory.open(new File(indexDir)));         IndexSearcher searcher = new IndexSearcher(reader);           BufferedReader in = null;           QueryParser parser = new QueryParser(Version.LUCENE_42, field, analyzer);                   // ArrayList에 컨텐츠 객체 타입을 저장          ArrayList<SearchOpenTable> list = new ArrayList<SearchOpenTable>();         while (true) {             String line = kwd != null ? kwd : "";               if (line == null || line.length() == -1) {                 break;             }               line = line.trim();             if (line.length() == 0) {                 break;             }               Query query = parser.parse(line);                           System.out.println("### Searching for: " + query.toString(field));               if (repeat > 0) {                           // repeat & time as benchmark                 Date start = new Date();                 for (int i = 0; i < repeat; i++) {                     searcher.search(query, null, 100);                 }                 Date end = new Date();                 System.out.println("Time: "+(end.getTime()-start.getTime())+"ms");             }               LuceneSearchResult result = null;             try {                 Date start = new Date();                 result = doPagingSearch(in, searcher, query, hitsPerPage, raw, interactive);                 Date end = new Date();                 DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");                                   System.out.println("# Start-Time: "+formatter.format(start)+", End-Time: "+formatter.format(end));                 System.out.println("# Elapse-Time: "+(end.getTime()-start.getTime())+"ms");             } catch (IOException e) {                 e.printStackTrace();             }               //            System.out.println("### result.getSize() :"+result.getSize());             for(int i=0; i<result.getSize(); i++){                 SearchOpenTable searchDev = new SearchOpenTable();                 searchDev.setSeq(result.getDocument(i).get("SEQ"));                 searchDev.setTitle(result.getDocument(i).get("TITLE"));                 searchDev.setContent(result.getDocument(i).get("CONTENT"));                           list.add(searchDev);             }               if (kwd != null) {                 break;             }         }         reader.close();           return list;     }       /**     * # 검색 결과 추출     */     public static LuceneSearchResult doPagingSearch(BufferedReader in, IndexSearcher searcher, Query query,             int hitsPerPage, boolean raw, boolean interactive) throws IOException {           // # 검색 정렬 : SEQ 기준 내림차순 정렬(false[asc], true[desc])         SortField sortField = new SortField("SEQ", SortField.Type.STRING, true);         Sort sort = new Sort(sortField);           // Collect enough docs to show 5 pages         TopDocs results = searcher.search(query, 5 * hitsPerPage, sort);         ScoreDoc[] hits = results.scoreDocs;                   int cnt = 1;         for (ScoreDoc match : results.scoreDocs) {             Document doc = searcher.doc(match.doc);                           Explanation explanation = searcher.explain(query, match.doc);                           System.out.println("----------------START-("+cnt+")------------------");             System.out.println("### POST SEQ : "+doc.get("SEQ")+ ", POST TITLE :"+doc.get("TITLE"));             System.out.println("### Explan :"+explanation.toString());             System.out.println("-----------------END-("+cnt+")-------------------");             ++cnt;         }           int numTotalHits = results.totalHits;         System.out.println(numTotalHits + " total matching documents");           int start = 0;         int end = Math.min(numTotalHits, hitsPerPage);           while (true) {             if (end > hits.length) {                 System.out.println("Only results 1 - " + hits.length +" of " + numTotalHits + " total matching documents collected.");                 System.out.println("Collect more (y/n) ?");                 String line = in.readLine();                 if (line.length() == 0 || line.charAt(0) == 'n') {                     break;                 }                   hits = searcher.search(query, numTotalHits).scoreDocs;             }                           end = Math.min(hits.length, start + hitsPerPage);               for (int i = start; i < end; i++) {                 if (raw) {                              // output raw format                     System.out.println("doc="+hits[i].doc+" score="+hits[i].score);                     continue;                 }             }               if (!interactive || end == 0) {                 break;             }               if (numTotalHits >= end) {                 boolean quit = false;                 while (true) {                     System.out.print("Press ");                     if (start - hitsPerPage >= 0) {                         System.out.print("(p)revious page, ");                      }                     if (start + hitsPerPage < numTotalHits) {                         System.out.print("(n)ext page, ");                     }                     System.out.println("(q)uit or enter number to jump to a page.");                       String line = in.readLine();                     if (line.length() == 0 || line.charAt(0)=='q') {                         quit = true;                         break;                     }                     if (line.charAt(0) == 'p') {                         start = Math.max(0, start - hitsPerPage);                         break;                     } else if (line.charAt(0) == 'n') {                         if (start + hitsPerPage < numTotalHits) {                             start+=hitsPerPage;                         }                         break;                     } else {                         int page = Integer.parseInt(line);                         if ((page - 1) * hitsPerPage < numTotalHits) {                             start = (page - 1) * hitsPerPage;                             break;                         } else {                             System.out.println("No such page");                         }                     }                 }                 if (quit) break;                 end = Math.min(numTotalHits, start + hitsPerPage);             }         }         return new LuceneSearchResult(searcher, hits);     }              static FastVectorHighlighter getHighlighter() {         FragListBuilder fragListBuilder = new SimpleFragListBuilder();         FragmentsBuilder fragmentBuilder =             new ScoreOrderFragmentsBuilder(                     BaseFragmentsBuilder.COLORED_PRE_TAGS,                     BaseFragmentsBuilder.COLORED_POST_TAGS);         return new FastVectorHighlighter(true, true, fragListBuilder, fragmentBuilder);     }              public static void main(String[] arr) throws Exception{         String indexDir = "D:\\01.MYSPACE_2013\\indexs\\dbms";                   try {              // 인덱싱 작업에서 설정한 통합인덱스 명칭을 이용한 검색             SearchOpenDBMS.searchLucene(indexDir, "SEARCH_ALL", "검색 키워드", 0, 1000, false, false);         } catch (IOException e) {             e.printStackTrace();         } catch (ParseException e) {             e.printStackTrace();         }     } }

SearchOpenTable.java

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

public class SearchOpenTable {     private String seq;     private String title;     private String content;           public String getSeq() {         return seq;     }     public void setSeq(String seq) {         this.seq = seq;     }     public String getTitle() {         return title;     }     public void setTitle(String title) {         this.title = title;     }     public String getContent() {         return content;     }     public void setContent(String content) {         this.content = content;     } }

LuceneSearchResult.java

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

import org.apache.lucene.document.Document; import org.apache.lucene.search.IndexSearcher; import org.apache.lucene.search.ScoreDoc;   public class LuceneSearchResult {     private IndexSearcher activeSearcher;     private ScoreDoc[] hits;       public LuceneSearchResult(IndexSearcher activeSearcher, ScoreDoc[] hits) {         this.activeSearcher = activeSearcher;         this.hits = hits;     }       public int getSize() {         return hits.length;     }       public Document getDocument(int i) {         try {             return activeSearcher.doc(hits[i].doc);         } catch (RuntimeException e) {             throw e;         } catch (Exception e) {             throw new RuntimeException(e);         }     } }