Mercurial > hg > LGServices
changeset 66:f106f2487ac1
fix connection not close
author | Zoe Hong <zhong@mpiwg-berlin.mpg.de> |
---|---|
date | Wed, 03 Aug 2016 16:19:31 +0200 |
parents | db737aead627 |
children | 0a9937b06cc3 |
files | src/main/java/de/mpiwg/gazetteer/db/DBCoordinatesBook.java src/main/java/de/mpiwg/gazetteer/rest/SaveText.java src/main/java/de/mpiwg/gazetteer/utils/DBService.java src/main/java/de/mpiwg/web/jsp/BranchPage.java src/main/java/de/mpiwg/web/jsp/JSPProxy.java src/main/webapp/methods/getDataverseForm.jsp |
diffstat | 6 files changed, 173 insertions(+), 7 deletions(-) [+] |
line wrap: on
line diff
--- a/src/main/java/de/mpiwg/gazetteer/db/DBCoordinatesBook.java Tue Jul 26 11:52:54 2016 +0200 +++ b/src/main/java/de/mpiwg/gazetteer/db/DBCoordinatesBook.java Wed Aug 03 16:19:31 2016 +0200 @@ -12,6 +12,13 @@ private String x; private String y; + public DBCoordinatesBook() { + this.id = 0; + this.books_id = ""; + this.place_name = ""; + this.x = ""; + this.y = ""; + } public DBCoordinatesBook(ResultSet rs) throws SQLException{ this.id = rs.getInt("id");
--- a/src/main/java/de/mpiwg/gazetteer/rest/SaveText.java Tue Jul 26 11:52:54 2016 +0200 +++ b/src/main/java/de/mpiwg/gazetteer/rest/SaveText.java Wed Aug 03 16:19:31 2016 +0200 @@ -45,6 +45,12 @@ json.put("branch", jsonBranch); json.put("file", jsonFile); json.put("status", "ok"); + + + // TODO DEBUG just added for testing error case! + json.put("status", "error"); + + } catch (GazetteerException e){ json.put("status", "error"); json.put("message", e.getMessage());
--- a/src/main/java/de/mpiwg/gazetteer/utils/DBService.java Tue Jul 26 11:52:54 2016 +0200 +++ b/src/main/java/de/mpiwg/gazetteer/utils/DBService.java Wed Aug 03 16:19:31 2016 +0200 @@ -80,6 +80,8 @@ while(rs.next()){ this.dynastyList.add(rs.getString("dynasty")); } + rs.close(); + } catch (Exception e) { e.printStackTrace(); }finally{ @@ -135,6 +137,7 @@ DBCoordinatesBook book = new DBCoordinatesBook(rs); this.coordinatesBookMap.put(book.getBooks_id(), book); } + rs.close(); } catch (Exception e) { e.printStackTrace(); }finally{ @@ -178,6 +181,8 @@ this.bookMap.put(book.getId(), book); } + rs.close(); + } catch (Exception e) { e.printStackTrace(); }finally{ @@ -342,7 +347,123 @@ } + public static List<DBContents> searchFullText_X(List<String> termList) throws SQLException{ + //Trying subquery to get both contents and sections in one large mysql query, but it performed worse here... + + Long start = System.currentTimeMillis(); + + String condition = "("; + for(int i=0; i<termList.size() ; i++){ + String term = termList.get(i); + if(i>0){ + condition += " OR "; + } + + condition += " content like '%" + term + "%' "; + } + condition += ")"; + + + String condition_content_in_book = " WHERE books_id=contents.books_id and start_page <= contents.line and end_page >=contents.line LIMIT 0,1"; + + String sql = "SELECT contents.id, contents.books_id AS books_id, contents.line AS line, contents.content AS content, " + + "books.name, books.dynasty, " + + "(SELECT sections_index.id FROM "+ SECTIONS_TABLE + condition_content_in_book + ") AS section_id, " + + "(SELECT sections_index.name FROM "+ SECTIONS_TABLE + condition_content_in_book + ") AS section_name, " + + "(SELECT sections_index.start_page FROM "+ SECTIONS_TABLE + condition_content_in_book + ") AS start_page, " + + "(SELECT sections_index.end_page FROM "+ SECTIONS_TABLE + condition_content_in_book + ") AS end_page " + + + "FROM contents JOIN books ON contents.books_id=books.id " + + "WHERE " + condition;//+ "and (books_id='00204' or books_id='00219')"; // remove the books_id condition after debug + + //logger.debug("sql: " + sql); + + Connection conn = null; + Statement stmt = null; + List<DBContents> resultSet = new ArrayList<DBContents>(); + try { + conn = getNewConnection(); + stmt = conn.createStatement(); + + Integer count = 0; + ResultSet rs = stmt.executeQuery(sql); + + // log execution time + long tmp = System.currentTimeMillis(); + logger.debug("Time execution mysql query [ms]: " + (tmp - start)); + + + while (rs.next()) { + + count++; + DBContents content = new DBContents(rs); + content.setInx(count); // local index for row + + String books_id = rs.getString("books_id"); + + // set this.section by bookId and page + DBSection section = new DBSection(); // some info are not correctly initial, since we update the sql query by query two table at once. + + section.setId(rs.getLong("section_id")); + section.setName(rs.getString("section_name")); + + section.setBookId(books_id); + section.setStart_page(rs.getInt("start_page")); + section.setEnd_page(rs.getInt("end_page")); + + + content.setSection(section); + + // set this.coordinatesBook by bookId + /* + DBCoordinatesBook coordinatesBook = new DBCoordinatesBook(); + + coordinatesBook.setId(rs.getInt("cb_id")); + coordinatesBook.setBooks_id(books_id); + coordinatesBook.setPlace_name(rs.getString("place_name")); + coordinatesBook.setX(rs.getString("x")); + coordinatesBook.setY(rs.getString("y")); + */ + + // set this.coordinatesBook by bookId + content.setCoordinatesBook(DBService.getInstance().getCoordinatesBook(content.getBookId())); + + resultSet.add(content); + + + } + logger.debug("************************* Count " + count + " **********************************"); + // log execution time + long tmp2 = System.currentTimeMillis(); + logger.debug("Time execution parsing to objects [ms]: " + (tmp2 - tmp)); + + + rs.close(); + } catch (Exception e) { + e.printStackTrace(); + } finally { + conn.close(); + } + /* + // setting section in each DBContents content + for (DBContents content : resultSet) { + // set this.section by bookId and page + content.setSection(DBService.getInstance().getSectionByBookIdAndPage(content.getBookId(), content.getPage())); + + // set this.coordinatesBook by bookId + content.setCoordinatesBook(DBService.getInstance().getCoordinatesBook(content.getBookId())); + } + */ + + + long end = System.currentTimeMillis(); + logger.debug("Total Time execution full text search [ms]: " + (end - start)); + + return resultSet; + } + public static List<DBContents> searchFullText(List<String> termList) throws SQLException{ + Long start = System.currentTimeMillis(); String condition = "("; @@ -362,6 +483,8 @@ "WHERE " + condition;//+ "and (books_id='00204' or books_id='00219')"; // remove the books_id condition after debug //logger.debug("sql: " + sql); + + long tmp = 0 , tmp2 = 0; Connection conn = null; Statement stmt = null; @@ -372,6 +495,12 @@ Integer count = 0; ResultSet rs = stmt.executeQuery(sql); + + // log execution time + tmp = System.currentTimeMillis(); + logger.debug("Time execution mysql query [ms]: " + (tmp - start)); + + while (rs.next()) { count++; DBContents content = new DBContents(rs); @@ -379,6 +508,11 @@ resultSet.add(content); } logger.debug("************************* Count " + count + " **********************************"); + + // log execution time + tmp2 = System.currentTimeMillis(); + logger.debug("Time execution parsing to objects [ms]: " + (tmp2 - tmp)); + rs.close(); } catch (Exception e) { e.printStackTrace(); @@ -386,6 +520,8 @@ conn.close(); } + + // setting section in each DBContents content for (DBContents content : resultSet) { // set this.section by bookId and page @@ -396,9 +532,9 @@ } - long end = System.currentTimeMillis(); - logger.debug("Time execution full text search [ms]: " + (end - start)); + logger.debug("Time execution setting sections [ms]: " + (end - tmp2)); + logger.debug("Total Time execution full text search [ms]: " + (end - start)); return resultSet; } @@ -663,6 +799,7 @@ String content = rs.getString("content"); sb.append("【" + line + "】" + content + "\n"); } + rs.close(); return sb.toString(); } @@ -847,10 +984,16 @@ } public static Connection getNewConnection() throws SQLException, IOException{ + + //return LGDataSource.getInstance().getConnection(); + + + return DriverManager.getConnection( DB_URL + PropertiesUtils.getPropValue("db_gazetter_name") + "?useUnicode=yes&characterEncoding=UTF-8", PropertiesUtils.getPropValue("db_gazetter_username"), PropertiesUtils.getPropValue("db_gazetter_password")); + } @@ -876,6 +1019,8 @@ section_version.add(sv); } + rs.close(); + } catch (Exception e) { e.printStackTrace(); } finally {
--- a/src/main/java/de/mpiwg/web/jsp/BranchPage.java Tue Jul 26 11:52:54 2016 +0200 +++ b/src/main/java/de/mpiwg/web/jsp/BranchPage.java Wed Aug 03 16:19:31 2016 +0200 @@ -220,6 +220,9 @@ try { this.branch = (LGBranch)branch.clone(); this.lastFile = DataProvider.getInstance().getFile(branch.getCurrentLastFileId()); + // TODO the currentLastFileId of branch is not updated from the saving? + + this.allFiles = DataProvider.getInstance().getAllFiles(branch.getId()); this.text = FileManager.getFileAsText(this.lastFile); this.contributors = new ArrayList<VDCUser>();
--- a/src/main/java/de/mpiwg/web/jsp/JSPProxy.java Tue Jul 26 11:52:54 2016 +0200 +++ b/src/main/java/de/mpiwg/web/jsp/JSPProxy.java Wed Aug 03 16:19:31 2016 +0200 @@ -38,7 +38,8 @@ getSessionBean().logout(); } //return TopicListPage.page; // the new home page? - return "pages/home.jsp"; + // return "pages/home.jsp"; + return BooksPage.page; } else if (getSessionBean() == null || getSessionBean().getUser() == null) { // return to home page where will ask user to login. @@ -47,7 +48,8 @@ getSessionBean().addMsg("Timeout or Logout at another page! Please login again."); //return TopicListPage.page; // the new home page? - return "pages/home.jsp"; + //return "pages/home.jsp"; + return BooksPage.page; }
--- a/src/main/webapp/methods/getDataverseForm.jsp Tue Jul 26 11:52:54 2016 +0200 +++ b/src/main/webapp/methods/getDataverseForm.jsp Wed Aug 03 16:19:31 2016 +0200 @@ -28,9 +28,12 @@ if(studies.isEmpty()){ %> - - <label>The system does not find studies for the user <%= sessionBean.getUser().getUserName()%>.</label> - + + <label>You have no dataset in LGDataverse.</label> + <!-- + <label>The system does not find dataset for user: <%= sessionBean.getUser().getUserName()%>.</label> + --> + <% } else { %>