Mercurial > hg > LGServices

changeset 66:f106f2487ac1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 {
 		%>