Mercurial > hg > LGServices
comparison src/main/java/de/mpiwg/gazetteer/utils/DBService.java @ 0:3e62083dbcbf
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 } |