Mercurial > hg > LGServices

comparison src/main/java/de/mpiwg/gazetteer/utils/DBService.java @ 0:3e62083dbcbf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
First commit. This project comes from LGServer. We removed the framework icefaces. Now, LGServices uses just JSP and jquery.
author "jurzua <jurzua@mpiwg-berlin.mpg.de>"
date Thu, 23 Apr 2015 15:46:01 +0200
parents
children 1af9d7db348e
comparison
equal deleted inserted replaced
-1:000000000000 0:3e62083dbcbf
1 package de.mpiwg.gazetteer.utils;
2
3 import java.io.IOException;
4 import java.sql.Connection;
5 import java.sql.DriverManager;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.sql.Statement;
9 import java.util.ArrayList;
10 import java.util.Date;
11 import java.util.HashMap;
12 import java.util.List;
13 import java.util.Map;
14
15 import org.apache.log4j.Logger;
16 import org.hibernate.Query;
17 import org.hibernate.Session;
18
19 import de.mpiwg.gazetteer.bo.DBEntry;
20 import de.mpiwg.gazetteer.bo.LGBranch;
21 import de.mpiwg.gazetteer.bo.LGFile;
22 import de.mpiwg.gazetteer.bo.SearchRulesFile;
23 import de.mpiwg.gazetteer.bo.Sequence;
24 import de.mpiwg.gazetteer.db.DBBook;
25 import de.mpiwg.gazetteer.db.DBSection;
26
27 public class DBService {
28
29 private static Logger logger = Logger.getLogger(DBService.class);
30
31 // JDBC driver name and database URL
32 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
33 static final String DB_URL = "jdbc:mysql://localhost/";
34
35 private static String SECTIONS_TABLE = "sections_index";
36
37 private static DBService instance = null;
38
39 public static DBService getInstance(){
40 if(instance == null){
41 instance = new DBService();
42 }
43 return instance;
44 }
45
46 private Map<String, DBBook> bookMap;
47 private List<String> dynastyList;
48
49 public List<String> getDynastyList() throws SQLException{
50 if(this.dynastyList == null){
51 this.loadDynastyList();
52 }
53 return dynastyList;
54 }
55
56 private void loadDynastyList() throws SQLException{
57 Long start = System.currentTimeMillis();
58 Connection conn = null;
59 Statement stmt = null;
60 this.dynastyList = new ArrayList<String>();
61
62 try {
63 String query = "SELECT DISTINCT dynasty FROM books";
64 logger.debug(query);
65 conn = getNewConnection();
66 stmt = conn.createStatement();
67 ResultSet rs = stmt.executeQuery(query);
68
69 while(rs.next()){
70 this.dynastyList.add(rs.getString("dynasty"));
71 }
72 } catch (Exception e) {
73 e.printStackTrace();
74 }finally{
75 conn.close();
76 }
77
78 long end = System.currentTimeMillis();
79 logger.debug("Time execution loading Book Map [ms]: " + (end - start));
80 }
81
82 public DBBook getBook(String id){
83 return getBookMap().get(id);
84 }
85
86 private Map<String, DBBook> getBookMap(){
87 if(bookMap == null){
88 try {
89 this.loadBookMap();
90 } catch (Exception e) {
91 e.printStackTrace();
92 }
93 }
94 return this.bookMap;
95 }
96
97 private void loadBookMap() throws SQLException{
98 Long start = System.currentTimeMillis();
99 Connection conn = null;
100 Statement stmt = null;
101 this.bookMap = new HashMap<String, DBBook>();
102
103 try {
104 String query = "SELECT * FROM books";
105 logger.debug(query);
106 conn = getNewConnection();
107 stmt = conn.createStatement();
108 ResultSet rs = stmt.executeQuery(query);
109
110 while(rs.next()){
111 DBBook book = new DBBook(rs);
112 this.bookMap.put(book.getId(), book);
113 }
114 } catch (Exception e) {
115 e.printStackTrace();
116 }finally{
117 conn.close();
118 }
119
120 long end = System.currentTimeMillis();
121 logger.debug("Time execution loading Book Map [ms]: " + (end - start));
122 }
123
124 public static List<DBSection> searchBook(List<String> termList, String field) throws SQLException{
125 Long start = System.currentTimeMillis();
126
127 List<DBSection> list = new ArrayList<DBSection>();
128
129 Connection conn = null;
130 Statement stmt = null;
131
132 //TODO this query should join the section table
133 String query = "SELECT * FROM books WHERE ";
134 for(int i=0; i<termList.size() ; i++){
135 String term = termList.get(i);
136 if(i>0){
137 query += " OR ";
138 }
139 query += field + " like '%" + term + "%' ";
140 }
141
142 try {
143 conn = getNewConnection();
144 stmt = conn.createStatement();
145
146 ResultSet rs = stmt.executeQuery(query);
147 while (rs.next()) {
148 DBBook book = new DBBook(rs);
149
150 String sql = "SELECT * FROM "+ SECTIONS_TABLE +" WHERE " + "books_id = '" + book.getId() + "'";
151 logger.debug(sql);
152 stmt = conn.createStatement();
153 ResultSet rs0 = stmt.executeQuery(sql);
154 while(rs0.next()){
155 DBSection section = new DBSection(rs0);
156 section.setBook(book);
157 list.add(section);
158 }
159 }
160 rs.close();
161 } catch (Exception e) {
162 e.printStackTrace();
163 } finally {
164 conn.close();
165 }
166
167 long end = System.currentTimeMillis();
168 logger.debug("Time execution serching Books [ms]: " + (end - start));
169
170 return list;
171 }
172
173 /**
174 * This methods search from a list of terms.
175 * Every term is considered a subsequence of whole section name.
176 *
177 * @param termList
178 * @return
179 * @throws SQLException
180 */
181 public static List<DBSection> searchSection(List<String> termList) throws SQLException{
182
183 Long start = System.currentTimeMillis();
184
185 List<DBSection> list = new ArrayList<DBSection>();
186
187 Connection conn = null;
188 Statement stmt = null;
189
190 String query = "SELECT * FROM "+ SECTIONS_TABLE +" WHERE ";
191 for(int i=0; i<termList.size() ; i++){
192 String term = termList.get(i);
193 if(i>0){
194 query += " OR ";
195 }
196 query += "name like '%" + term + "%' ";
197 }
198
199 try {
200 Class.forName(JDBC_DRIVER);
201 conn = getNewConnection();
202 stmt = conn.createStatement();
203
204 ResultSet rs = stmt.executeQuery(query);
205 while (rs.next()) {
206 DBSection section = new DBSection(rs);
207 DBBook book = getInstance().getBook(section.getBookId());
208 section.setBook(book);
209 list.add(section);
210 }
211 rs.close();
212 } catch (Exception e) {
213 e.printStackTrace();
214 } finally {
215 conn.close();
216 }
217
218 long end = System.currentTimeMillis();
219 logger.debug("Time execution serching Sections [ms]: " + (end - start));
220
221 return list;
222 }
223
224 public static List<String> suggestBookName(String term, int limit) throws SQLException{
225 List<String> list = new ArrayList<String>();
226
227 Connection conn = null;
228 Statement stmt = null;
229
230 String query = "SELECT name FROM books WHERE name like '" + term + "%' limit " + limit;
231
232 try {
233 Class.forName(JDBC_DRIVER);
234 conn = getNewConnection();
235 stmt = conn.createStatement();
236
237 ResultSet rs = stmt.executeQuery(query);
238 while (rs.next()) {
239 String name = rs.getString("name");
240 if(!list.contains(name)){
241 list.add(name);
242 }
243 }
244 rs.close();
245 } catch (Exception e) {
246 e.printStackTrace();
247 } finally {
248 conn.close();
249 }
250 return list;
251 }
252
253 public static List<String> suggestSectionName(String term, int limit) throws SQLException {
254 List<String> list = new ArrayList<String>();
255
256 Connection conn = null;
257 Statement stmt = null;
258
259 String query = "SELECT name FROM "+SECTIONS_TABLE+" WHERE name like '" + term + "%' limit " + limit;
260
261 try {
262 Class.forName(JDBC_DRIVER);
263 conn = getNewConnection();
264 stmt = conn.createStatement();
265
266 ResultSet rs = stmt.executeQuery(query);
267 while (rs.next()) {
268 String name = rs.getString("name");
269 if(!list.contains(name)){
270 list.add(name);
271 }
272 }
273 rs.close();
274 } catch (Exception e) {
275 e.printStackTrace();
276 } finally {
277 conn.close();
278 }
279 return list;
280 }
281
282
283 public static List<String> suggestSectionId(String input, int limit) throws SQLException {
284 List<String> list = new ArrayList<String>();
285
286 Connection conn = null;
287 Statement stmt = null;
288
289 String query = "SELECT id FROM "+SECTIONS_TABLE+" WHERE id like '" + input + "%' limit " + limit;
290
291 try {
292 Class.forName(JDBC_DRIVER);
293 conn = getNewConnection();
294 stmt = conn.createStatement();
295
296 ResultSet rs = stmt.executeQuery(query);
297 while (rs.next()) {
298
299 String id = rs.getString("id");
300 list.add(id);
301
302 }
303 rs.close();
304 } catch (Exception e) {
305 e.printStackTrace();
306 } finally {
307 conn.close();
308 }
309
310
311
312 return list;
313 }
314
315 public static DBSection getSectionWithContent(Long sectionId) throws SQLException {
316
317 Connection conn = null;
318 Statement stmt = null;
319 DBSection section = null;
320
321 String query = "SELECT * FROM "+SECTIONS_TABLE+" WHERE id = '" + sectionId + "'";
322
323 try {
324 Class.forName(JDBC_DRIVER);
325 conn = getNewConnection();
326 stmt = conn.createStatement();
327
328 ResultSet rs = stmt.executeQuery(query);
329 while (rs.next()) {
330
331 section = new DBSection(rs);
332 /*
333 String bookId = rs.getString("books_id");
334 int startPage = rs.getInt("start_page");
335 int endPage = rs.getInt("end_page");
336 String sectionName = rs.getString("name");
337
338 response.setBookId(bookId);
339 response.setName(sectionName);
340 */
341 //System.out.println("bookId=" + bookId + ", startPage=" + startPage + ", endPage=" + endPage);
342 //String content = getContent(conn, bookId, startPage, endPage);
343 String content = getContent(conn, section);
344 section.setText(content);
345
346 //DBBook book = getBook0FromDB(conn, bookId);
347 DBBook book = DBService.getInstance().getBook(section.getBookId());
348 section.setBook(book);
349
350 }
351 rs.close();
352 } catch (Exception e) {
353 } finally {
354 conn.close();
355 }
356 return section;
357 }
358
359 public static String fixToNewline(String orig){
360 char[] chars = orig.toCharArray();
361 StringBuilder sb = new StringBuilder(100);
362 for(char c : chars){
363 switch(c){
364 case '\r':
365 case '\f':
366 break;
367 case '\n':
368 sb.append("<br>");
369 break;
370 default:
371 sb.append(c);
372 }
373 }
374 return sb.toString();
375 }
376
377 //"SELECT `content`, `line`, `books_id` FROM `contents` WHERE `books_id`=\"%s\" AND `line`>=%d AND `line`<=%d
378 //private static String getContent(Connection conn, String bookId, Integer startLine, Integer endLine) throws Exception{
379 private static String getContent(Connection conn, DBSection section) throws Exception{
380 String query = "SELECT content, line FROM contents WHERE books_id = '" + section.getBookId() + "' AND line >= '" + section.getStart_page() + "' AND line <= '" + section.getEnd_page() + "'";
381
382 logger.debug(query);
383
384 Statement stmt = conn.createStatement();
385 ResultSet rs = stmt.executeQuery(query);
386
387 StringBuilder sb = new StringBuilder();
388 while(rs.next()){
389 String line = rs.getString("line");
390 String content = rs.getString("content");
391 sb.append("【" + line + "】" + content + "\n");
392 }
393
394 return sb.toString();
395 }
396
397 /*
398 public static DBBook getBookFromDB(String id) throws SQLException{
399 Connection conn = null;
400 DBBook book = null;
401
402 try {
403 //Class.forName(JDBC_DRIVER);
404 conn = getNewConnection();
405 book = getBook0FromDB(conn, id);
406 } catch (Exception e) {
407 e.printStackTrace();
408 } finally {
409 conn.close();
410 }
411 return book;
412 }*/
413 /*
414 private static DBBook getBook0FromDB(Connection conn, String id) throws SQLException{
415 DBBook book = null;
416
417 String query = "SELECT * FROM books WHERE id = '" + id + "'";
418 logger.debug(query);
419
420 Statement stmt = conn.createStatement();
421 ResultSet rs = stmt.executeQuery(query);
422
423 if(rs.next()){
424 book = new DBBook(rs);
425 }
426
427 return book;
428 }*/
429
430 /**
431 * This method removed all files for a particular fileId.
432 * The elimination includes the current version as well as the old versions.
433 * @param fileId
434 * @return
435 */
436 protected static int deleteBranchFromDB(Long branchId){
437 logger.info("Deleting Branch by branchId=" + branchId);
438
439 int modifiedFiles;
440 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
441 session.getTransaction().begin();
442
443 Query query = session.createQuery("delete LGBranch where id = :id");
444 query.setLong("id", branchId);
445 modifiedFiles = query.executeUpdate();
446
447 Query query0 = session.createQuery("delete LGFile where branchId = :branchId");
448 query0.setLong("branchId", branchId);
449 modifiedFiles += query0.executeUpdate();
450
451 session.getTransaction().commit();
452
453 return modifiedFiles;
454 }
455
456 protected static int deleteFileFromDB(Long fileId){
457
458 int modifiedFiles;
459 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
460 session.getTransaction().begin();
461
462
463 Query query0 = session.createQuery("delete LGFile where id = :fileId");
464 query0.setLong("fileId", fileId);
465 modifiedFiles = query0.executeUpdate();
466
467 session.getTransaction().commit();
468
469 return modifiedFiles;
470
471 }
472
473 protected static List<LGFile> getAllLGFileFromDB(){
474 List<LGFile> list = null;
475
476 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
477 session.getTransaction().begin();
478 Query query = session.createQuery("from LGFile");
479 list = query.list();
480 session.getTransaction().commit();
481
482 return list;
483 }
484
485 protected static List<LGFile> getCurrentLGFilesFromDB(){
486 List<LGFile> list = null;
487
488 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
489 session.getTransaction().begin();
490 Query query = session.createQuery("from LGFile where lastVersion = :lastVersion");
491 query.setBoolean("lastVersion", true);
492 list = query.list();
493 session.getTransaction().commit();
494
495 return list;
496 }
497
498 protected static List<LGBranch> getAllLGBranchFromDB(){
499 List<LGBranch> list = null;
500
501 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
502 session.getTransaction().begin();
503 Query query = session.createQuery("from LGBranch");
504 list = query.list();
505 session.getTransaction().commit();
506
507 return list;
508 }
509
510 protected static void saveDBEntry(DBEntry entry, Date date){
511
512 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
513 session.getTransaction().begin();
514
515 saveDBEntry0(session, entry, date);
516
517 session.getTransaction().commit();
518 }
519
520 public static void saveDBEntry0(Session session, DBEntry entry, Date date){
521 entry.setLastChangeDate(date);
522 if (entry.isPersistent()) {
523 session.update(entry);
524 } else {
525 entry.setCreationDate(date);
526 session.save(entry);
527 }
528 logger.info("saveDBEntry: " + entry.toString());
529 }
530
531 public static Connection getNewConnection() throws SQLException, IOException{
532 return DriverManager.getConnection(
533 DB_URL + PropertiesUtils.getPropValue("db_gazetter_name") + "?useUnicode=yes&characterEncoding=UTF-8",
534 PropertiesUtils.getPropValue("db_gazetter_username"),
535 PropertiesUtils.getPropValue("db_gazetter_password"));
536 }
537
538 }