--- a/src/main/java/de/mpiwg/gazetteer/utils/DBService.java	Fri May 19 20:05:55 2017 +0200
+++ b/src/main/java/de/mpiwg/gazetteer/utils/DBService.java	Fri May 19 20:08:17 2017 +0200
@@ -33,93 +33,93 @@
 public class DBService {
 
 	private static Logger logger = Logger.getLogger(DBService.class);
-	
+
 	// JDBC driver name and database URL
 	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
-	
-	
+
+
 	//static final String DB_URL = "jdbc:mysql://localhost/";
 	static final String DB_URL = "jdbc:mysql://194.94.134.66/";	// TODO: when move Gazetteer database to SBB, need to re-config this
 																// replace the IP with SBB's host IP: 194.94.134.66
-	
+
 	private static String SECTIONS_TABLE = "sections_index";
 
-	
-	// TODO: for all connection to mysql, we need to new a connection once (don't close it) and check everytime is the connection is still available. 
+
+	// TODO: for all connection to mysql, we need to new a connection once (don't close it) and check everytime is the connection is still available.
 	// It probably will timeout. If that then new a new connection.
-	
-	
+
+
 	private static DBService instance = null;
-	
+
 	public static DBService getInstance(){
 		if(instance == null){
 			instance = new DBService();
 		}
 		return instance;
 	}
-	
+
 	private Map<String, DBBook> bookMap;
-	
+
 	private List<String> dynastyList;
 
 	private Map<String, DBCoordinatesBook> coordinatesBookMap;
-	
+
 	public List<String> getDynastyList() throws SQLException{
 		if(this.dynastyList == null){
 			this.loadDynastyList();
 		}
 		return dynastyList;
 	}
-	
+
 	private void loadDynastyList() throws SQLException{
 		Long start = System.currentTimeMillis();
 		Connection conn = null;
 		Statement stmt = null;
 		this.dynastyList = new ArrayList<String>();
-		
+
 		try {
 			String query = "SELECT DISTINCT dynasty FROM books";
 			logger.debug(query);
 			conn = getNewConnection();
 			stmt   = conn.createStatement();
 			ResultSet rs = stmt.executeQuery(query);
-			
+
 			while(rs.next()){
 				this.dynastyList.add(rs.getString("dynasty"));
 			}
 			rs.close();
-			
+
 		} catch (Exception e) {
 			e.printStackTrace();
 		}finally{
 			conn.close();
 		}
-		
+
 		long end = System.currentTimeMillis();
 		logger.debug("Time execution loading Book Map [ms]: " + (end - start));
 	}
-	
+
 	public List<DBBook> getBooks(){
 		try {
 			this.loadBookMap();
 		} catch (SQLException e) {
-			
+
 			e.printStackTrace();
 		}
-		
+
 		// getBookMap().values() returns a Collection. In this way, we can use List.
 		return new ArrayList<DBBook>(getBookMap().values());
 	}
-	
-	
+
+
 	public DBCoordinatesBook getCoordinatesBook(String bookId) {
 		return getCoordinatesBookMap().get(bookId);
 	}
-	
+
 	private Map<String, DBCoordinatesBook> getCoordinatesBookMap() {
 		if (coordinatesBookMap == null) {
 			try {
-				this.loadCoordinatesBookMap();	
+				this.loadCoordinatesBookMap();
 			} catch (Exception e) {
 				e.printStackTrace();
 			}
@@ -132,16 +132,16 @@
 		Connection conn = null;
 		Statement stmt = null;
 		this.coordinatesBookMap = new HashMap<String, DBCoordinatesBook>();
-		
+
 		try {
-			String query = "SELECT * FROM coordinates_books";				
+			String query = "SELECT * FROM coordinates_books";
 			logger.debug(query);
 			conn = getNewConnection();
 			stmt   = conn.createStatement();
 			ResultSet rs = stmt.executeQuery(query);
-			
+
 			while(rs.next()){
-				DBCoordinatesBook book = new DBCoordinatesBook(rs);		
+				DBCoordinatesBook book = new DBCoordinatesBook(rs);
 				this.coordinatesBookMap.put(book.getBooks_id(), book);
 			}
 			rs.close();
@@ -150,7 +150,7 @@
 		}finally{
 			conn.close();
 		}
-		
+
 		long end = System.currentTimeMillis();
 		logger.debug("Time execution loading Coordinates Book Map [ms]: " + (end - start));
 	}
@@ -158,56 +158,56 @@
 	public DBBook getBook(String id){
 		return getBookMap().get(id);
 	}
-	
+
 	public Map<String, DBBook> getBookMap(){
 		if(bookMap == null){
 			try {
-				this.loadBookMap();	
+				this.loadBookMap();
 			} catch (Exception e) {
 				e.printStackTrace();
 			}
 		}
 		return this.bookMap;
 	}
-	
+
 	private void loadBookMap() throws SQLException{
 		Long start = System.currentTimeMillis();
 		Connection conn = null;
 		Statement stmt = null;
 		this.bookMap = new HashMap<String, DBBook>();
-		
+
 		try {
-			String query = "SELECT * FROM books";				
+			String query = "SELECT * FROM books";
 			logger.debug(query);
 			conn = getNewConnection();
 			stmt   = conn.createStatement();
 			ResultSet rs = stmt.executeQuery(query);
-			
+
 			while(rs.next()){
-				DBBook book = new DBBook(rs);	
-				
+				DBBook book = new DBBook(rs);
+
 				this.bookMap.put(book.getId(), book);
 			}
 			rs.close();
-			
+
 		} catch (Exception e) {
 			e.printStackTrace();
 		}finally{
 			conn.close();
 		}
-		
+
 		long end = System.currentTimeMillis();
 		logger.debug("Time execution loading Book Map [ms]: " + (end - start));
 	}
-	
+
 	public static List<DBSection> searchBook(List<String> termList, String field) throws SQLException{
 		Long start = System.currentTimeMillis();
-		
+
 		List<DBSection> list = new ArrayList<DBSection>();
-		
+
 		Connection conn = null;
 		Statement stmt = null;
-		
+
 		//TODO this query should join the section table
 		String query = "SELECT * FROM books WHERE ";
 		for(int i=0; i<termList.size() ; i++){
@@ -221,11 +221,11 @@
 		try {
 			conn = getNewConnection();
 			stmt = conn.createStatement();
-			
+
 			ResultSet rs = stmt.executeQuery(query);
 			while (rs.next()) {
 				DBBook book = new DBBook(rs);
-				
+
 				String sql = "SELECT * FROM "+ SECTIONS_TABLE +" WHERE " + "books_id = '" + book.getId() + "'";
 				logger.debug(sql);
 				stmt = conn.createStatement();
@@ -242,17 +242,17 @@
 		} finally {
 			conn.close();
 		}
-		
+
 		long end = System.currentTimeMillis();
 		logger.debug("Time execution serching Books [ms]: " + (end - start));
-		
+
 		return list;
 	}
-	
+
 	/*
 	public static List<DBBook> searchFullText0(List<String> termList) throws SQLException{
 		Long start = System.currentTimeMillis();
-		
+
 		String condition = "";
 		for(int i=0; i<termList.size() ; i++){
 			String term = termList.get(i);
@@ -262,8 +262,8 @@
 			condition += " contents.content like '%" + term + "%' ";
 		}
 
-		
-		String sql = 
+
+		String sql =
 					"SELECT books.level1 AS level1, books.level2 AS level2, " +
                        "books.name AS name, books.line AS line,books.period AS period, books.dynasty AS dynasty, " +
                        "books.admin_type as admin_type, books.in_jibengujiku AS in_jibengujiku, " +
@@ -275,7 +275,7 @@
                        "WHERE " + condition + " " +
                        "ORDER BY contents.books_id, contents.line";
 
-		
+
 		Connection conn = null;
 		Statement stmt = null;
 		List<DBBook> resultSet = new ArrayList<DBBook>();
@@ -283,7 +283,7 @@
 		try {
 			conn = getNewConnection();
 			stmt = conn.createStatement();
-			
+
 			int count = 0;
 			ResultSet rs = stmt.executeQuery(sql);
 			while (rs.next()) {
@@ -291,56 +291,56 @@
 				DBBook book = new DBBook(rs);
 				resultSet.add(book);
 			}
-			
+
 			System.out.println("************************* Count " + count + " **********************************");
-			
+
 			rs.close();
 		} catch (Exception e) {
 			e.printStackTrace();
 		} finally {
 			conn.close();
 		}
-		
+
 		long end = System.currentTimeMillis();
 		logger.debug("Time execution serching Books [ms]: " + (end - start));
-		
+
 		return resultSet;
 	}
 	*/
-	
-	
+
+
 	public DBSection getSectionByBookIdAndPage(String bookId, int page) throws SQLException {
-		
+
 		Connection conn = null;
 		Statement stmt = null;
-		
+
 		DBSection section = null;
-		
+
 		String query = "SELECT * FROM "+ SECTIONS_TABLE +" WHERE books_id='" + bookId + "' " +
 						"and start_page<=" + page + " and end_page>=" + page;
-		
+
 		try {
 			Class.forName(JDBC_DRIVER);
 			conn = getNewConnection();
 			stmt = conn.createStatement();
-			
+
 			ResultSet rs = stmt.executeQuery(query);
-			
+
 			while (rs.next()) {
 				section = new DBSection(rs);
 				DBBook book = getInstance().getBook(bookId);
 				section.setBook(book);
-				
+
 			}
 			rs.close();
-			
+
 		} catch (Exception e) {
 			e.printStackTrace();
 		} finally {
 			conn.close();
 		}
-		
-		if (section == null) {	
+
+		if (section == null) {
 			// section_index table is not completely correct by TOC_edit task (done by human), so query finds null in section
 
 			section = new DBSection();
@@ -348,41 +348,41 @@
 			DBBook book = getInstance().getBook(bookId);
 			section.setBook(book);
 		}
-		
+
 		return section;
-		
+
 	}
 
-	
+
 	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 " + 
-						
+						"(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;
@@ -391,60 +391,60 @@
 		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);	
+				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);	
-				
-				
+
+				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();
@@ -456,41 +456,41 @@
 		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 = "(";
 		for(int i=0; i<termList.size() ; i++){
 			String term = termList.get(i);
 			if(i>0){
 				condition += " OR ";
 			}
-			
+
 			condition += " content like '%" + term + "%' ";
 		}
 		condition += ")";
 
-		
+
 		String sql = "SELECT contents.id, contents.books_id AS books_id, contents.line AS line, contents.content AS content " +
                     	"FROM contents " +
                     	"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;
@@ -499,72 +499,72 @@
 		try {
 			conn = getNewConnection();
 			stmt = conn.createStatement();
-			
+
 			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);	
+				DBContents content = new DBContents(rs);
 				content.setInx(count);	// local index for row
-				resultSet.add(content);	
+				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();
 		} 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("Time execution setting sections [ms]: " + (end - tmp2));
 		logger.debug("Total Time execution full text search [ms]: " + (end - start));
-		
+
 		return resultSet;
 	}
-	
-	
+
+
 
 	/**
-	 * This methods search from a list of terms. 
+	 * This methods search from a list of terms.
 	 * Every term is considered a subsequence of whole section name.
-	 * 
+	 *
 	 * @param termList
 	 * @return
 	 * @throws SQLException
 	 */
 	public static List<DBSection> searchSection(List<String> termList) throws SQLException{
-		
+
 		Long start = System.currentTimeMillis();
-		
+
 		List<DBSection> list = new ArrayList<DBSection>();
-		
+
 		Connection conn = null;
 		Statement stmt = null;
-		
+
 		String query = "SELECT * FROM "+ SECTIONS_TABLE +" WHERE ";
 		for(int i=0; i<termList.size() ; i++){
 			String term = termList.get(i);
@@ -578,7 +578,7 @@
 			Class.forName(JDBC_DRIVER);
 			conn = getNewConnection();
 			stmt = conn.createStatement();
-			
+
 			ResultSet rs = stmt.executeQuery(query);
 			while (rs.next()) {
 				DBSection section = new DBSection(rs);
@@ -592,55 +592,26 @@
 		} finally {
 			conn.close();
 		}
-		
+
 		long end = System.currentTimeMillis();
 		logger.debug("Time execution serching Sections [ms]: " + (end - start));
-		
+
 		return list;
 	}
-	
+
 	public static List<String> suggestBookName(String term, int limit) throws SQLException{
 		List<String> list = new ArrayList<String>();
-		
+
 		Connection conn = null;
 		Statement stmt = null;
-		
+
 		String query = "SELECT name FROM books WHERE name like '" + term + "%' limit " + limit;
 
 		try {
 			Class.forName(JDBC_DRIVER);
 			conn = getNewConnection();
 			stmt = conn.createStatement();
-			
-			ResultSet rs = stmt.executeQuery(query);
-			while (rs.next()) {
-				String name = rs.getString("name");
-				if(!list.contains(name)){
-					list.add(name);
-				}
-			}
-			rs.close();
-		} catch (Exception e) {
-			e.printStackTrace();
-		} finally {
-			conn.close();
-		}
-		return list;		
-	}
-	
-	public static List<String> suggestSectionName(String term, int limit) throws SQLException {
-		List<String> list = new ArrayList<String>();
-		
-		Connection conn = null;
-		Statement stmt = null;
-		
-		String query = "SELECT name FROM "+SECTIONS_TABLE+" WHERE name like '" + term + "%' limit " + limit;
 
-		try {
-			Class.forName(JDBC_DRIVER);
-			conn = getNewConnection();
-			stmt = conn.createStatement();
-			
 			ResultSet rs = stmt.executeQuery(query);
 			while (rs.next()) {
 				String name = rs.getString("name");
@@ -656,27 +627,56 @@
 		}
 		return list;
 	}
-	
-	
+
+	public static List<String> suggestSectionName(String term, int limit) throws SQLException {
+		List<String> list = new ArrayList<String>();
+
+		Connection conn = null;
+		Statement stmt = null;
+
+		String query = "SELECT name FROM "+SECTIONS_TABLE+" WHERE name like '" + term + "%' limit " + limit;
+
+		try {
+			Class.forName(JDBC_DRIVER);
+			conn = getNewConnection();
+			stmt = conn.createStatement();
+
+			ResultSet rs = stmt.executeQuery(query);
+			while (rs.next()) {
+				String name = rs.getString("name");
+				if(!list.contains(name)){
+					list.add(name);
+				}
+			}
+			rs.close();
+		} catch (Exception e) {
+			e.printStackTrace();
+		} finally {
+			conn.close();
+		}
+		return list;
+	}
+
+
 	public static List<String> suggestSectionId(String input, int limit) throws SQLException {
 		List<String> list = new ArrayList<String>();
-		
+
 		Connection conn = null;
 		Statement stmt = null;
-		
+
 		String query = "SELECT id FROM "+SECTIONS_TABLE+" WHERE id like '" + input + "%' limit " + limit;
 
 		try {
 			Class.forName(JDBC_DRIVER);
 			conn = getNewConnection();
 			stmt = conn.createStatement();
-			
+
 			ResultSet rs = stmt.executeQuery(query);
 			while (rs.next()) {
-				
+
 				String id = rs.getString("id");
 				list.add(id);
-				
+
 			}
 			rs.close();
 		} catch (Exception e) {
@@ -684,36 +684,36 @@
 		} finally {
 			conn.close();
 		}
-		
-	
-		
+
+
+
 		return list;
 	}
-	
-	
+
+
 	public static DBSection getSectionWithContent(Long sectionId) throws SQLException {
 
 		Connection conn = null;
 		Statement stmt = null;
 		DBSection section = null;
-		
+
 		String query = "SELECT * FROM "+SECTIONS_TABLE+" WHERE id = '" + sectionId + "'";
-		
+
 		try {
 			Class.forName(JDBC_DRIVER);
 			conn = getNewConnection();
 			stmt = conn.createStatement();
-			
+
 			ResultSet rs = stmt.executeQuery(query);
 			while (rs.next()) {
-				
+
 				section = new DBSection(rs);
 				/*
 				String bookId = rs.getString("books_id");
 				int startPage = rs.getInt("start_page");
 				int endPage = rs.getInt("end_page");
 				String sectionName = rs.getString("name");
-				
+
 				response.setBookId(bookId);
 				response.setName(sectionName);
 				*/
@@ -721,58 +721,58 @@
 				//String content = getContent(conn, bookId, startPage, endPage);
 				String content = getContent(conn, section);
 				section.setText(content);
-				
+
 				//DBBook book = getBook0FromDB(conn, bookId);
 				DBBook book = DBService.getInstance().getBook(section.getBookId());
 				section.setBook(book);
-				
+
 			}
 			rs.close();
-		} catch (Exception e) {	
+		} catch (Exception e) {
 			e.printStackTrace();
 		} finally {
 			conn.close();
 		}
 		return section;
 	}
-	
+
 	public static DBSection getDeprecatedSectionWithContent(Long sectionId) throws SQLException {
 
 		Connection conn = null;
 		Statement stmt = null;
 		DBSection section = null;
-		
+
 		String query = "SELECT * FROM sections_revisions WHERE sections_id = '" + sectionId + "'";
-		
+
 		try {
 			Class.forName(JDBC_DRIVER);
 			conn = getNewConnection();
 			stmt = conn.createStatement();
-			
+
 			ResultSet rs = stmt.executeQuery(query);
 			rs.next();
 			//while (rs.next()) {
-				
+
 				section = new DBSection(rs);
-				
+
 				String content = getContent(conn, section);
 				section.setText(content);
-				
+
 				DBBook book = DBService.getInstance().getBook(section.getBookId());
 				section.setBook(book);
-				
+
 			//}
 			rs.close();
-		} catch (Exception e) {	
+		} catch (Exception e) {
 			e.printStackTrace();
 		} finally {
 			conn.close();
 		}
 		return section;
 	}
-	
-	
-	
+
+
+
 	public static String fixToNewline(String orig){
 	    char[] chars = orig.toCharArray();
 	    StringBuilder sb = new StringBuilder(100);
@@ -790,16 +790,16 @@
 	    }
 	    return sb.toString();
 	}
-	
+
 	//"SELECT `content`, `line`, `books_id` FROM `contents` WHERE `books_id`=\"%s\" AND `line`>=%d AND `line`<=%d
 	//private static String getContent(Connection conn, String bookId, Integer startLine, Integer endLine) throws Exception{
 	private static String getContent(Connection conn, DBSection section) throws Exception{
 		String query = "SELECT content, line FROM contents WHERE books_id = '" + section.getBookId() + "' AND line >= '" + section.getStart_page() + "' AND line <= '" + section.getEnd_page() + "'";
 		//logger.debug(query);
-		
+
 		Statement stmt   = conn.createStatement();
 		ResultSet rs = stmt.executeQuery(query);
-		
+
 		StringBuilder sb = new StringBuilder();
 		while(rs.next()){
 			String line = rs.getString("line");
@@ -807,15 +807,15 @@
 			sb.append("【" + line + "】" + content + "\n");
 		}
 		rs.close();
-		
+
 		return sb.toString();
 	}
-	
+
 	/*
 	public static DBBook getBookFromDB(String id) throws SQLException{
 		Connection conn = null;
 		DBBook book = null;
-	
+
 		try {
 			//Class.forName(JDBC_DRIVER);
 			conn = getNewConnection();
@@ -830,17 +830,17 @@
 	/*
 	private static DBBook getBook0FromDB(Connection conn, String id) throws SQLException{
 		DBBook book = null;
-		
+
 		String query = "SELECT * FROM books WHERE id = '" + id + "'";
 		logger.debug(query);
-		
+
 		Statement stmt   = conn.createStatement();
 		ResultSet rs = stmt.executeQuery(query);
-		
+
 		if(rs.next()){
 			book = new DBBook(rs);
 		}
-		
+
 		return book;
 	}*/
 
@@ -852,123 +852,190 @@
 	 */
 	protected static int deleteBranchFromDB(Long branchId){
 		logger.info("Deleting Branch by branchId=" + branchId);
-		
+
 		int modifiedFiles;
 		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
 		session.getTransaction().begin();
-		
+
 		Query query = session.createQuery("delete LGBranch where id = :id");
 		query.setLong("id", branchId);
 		modifiedFiles = query.executeUpdate();
-		
+
 		Query query0 = session.createQuery("delete LGFile where branchId = :branchId");
 		query0.setLong("branchId", branchId);
 		modifiedFiles += query0.executeUpdate();
-		
+
 		session.getTransaction().commit();
-		
+
 		return modifiedFiles;
 	}
-	
+
 	protected static int deleteFullTextSearchFileFromDB(Long fileId){
 		int modifiedFiles = 0;
-		
+
 		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
 		session.getTransaction().begin();
-		
+
 		Query query0 = session.createQuery("delete LGFullTextSearchFile where id = :fileId");
 		query0.setLong("fileId", fileId);
 		modifiedFiles = query0.executeUpdate();
-		
+
 		session.getTransaction().commit();
-		
+
 		return modifiedFiles;
 	}
-	
+
 	protected static int deleteFileFromDB(Long fileId){
-		
+
 		int modifiedFiles;
 		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
 		session.getTransaction().begin();
-		
-		
+
+
 		Query query0 = session.createQuery("delete LGFile where id = :fileId");
 		query0.setLong("fileId", fileId);
 		modifiedFiles = query0.executeUpdate();
-		
+
 		session.getTransaction().commit();
-		
+
 		return modifiedFiles;
-		
+
 	}
-	
+
 	protected static List<LGFile> getLGFileInDatasetFromDB(String datasetPersistentId) {
 		List<LGFile> list = null;
-		
-		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
-		session.getTransaction().begin();
-		Query query = session.createQuery("from LGFile where datasetPersistentId = :datasetPersistentId and fileIdInDv != NULL");
-		query.setString("datasetPersistentId", datasetPersistentId);
-		list = query.list();
-		session.getTransaction().commit();
+
+		Session session = HibernateUtil.getSessionFactory().openSession();
+
+		try{
+
+			session.getTransaction().begin();
+			Query query = session.createQuery("from LGFile where datasetPersistentId = :datasetPersistentId and fileIdInDv != NULL");
+			query.setString("datasetPersistentId", datasetPersistentId);
+			list = query.list();
+			session.getTransaction().commit();
 
+		}
+		catch(Exception e){
+			 throw e;
+		}
+		finally{
+
+			if (session != null && session.isOpen()) {
+        session.close();
+      }
+		}
 
 		return list;
 	}
-	
-	
+
+
 	protected static List<LGFullTextSearchFile> getAllLGFullTextSearchFileFromDB() {
+
 		List<LGFullTextSearchFile> list = null;
-		
-		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
-		session.getTransaction().begin();
-		Query query = session.createQuery("from LGFullTextSearchFile");
-		list = query.list();
-		session.getTransaction().commit();
+
+		Session session = HibernateUtil.getSessionFactory().openSession();
+
+		try{
+
+			session.getTransaction().begin();
+			Query query = session.createQuery("from LGFullTextSearchFile");
+			list = query.list();
+			session.getTransaction().commit();
+
+		}
+		finally{
+
+			if (session != null && session.isOpen()) {
+        session.close();
+      }
+		}
 
 		return list;
-		
+
 	}
-	
+
 	protected static  List<LGFile> getAllLGFileFromDB(){
 		List<LGFile> list = null;
 
-		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
-		session.getTransaction().begin();
-		Query query = session.createQuery("from LGFile");
-		list = query.list();
-		session.getTransaction().commit();
+		Session session = HibernateUtil.getSessionFactory().openSession();
+
+		try{
+
+			session.getTransaction().begin();
+			Query query = session.createQuery("from LGFile");
+			list = query.list();
+			session.getTransaction().commit();
+
+		}
+		catch(Exception e){
+			 throw e;
+		}
+		finally{
+
+			if (session != null && session.isOpen()) {
+        session.close();
+      }
+		}
 
 		return list;
-	}	
-	
+	}
+
 	protected static  List<LGFile> getCurrentLGFilesFromDB(){
 		List<LGFile> list = null;
 
-		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
-		session.getTransaction().begin();
-		Query query = session.createQuery("from LGFile where lastVersion = :lastVersion");
-		query.setBoolean("lastVersion", true);
-		list = query.list();
-		session.getTransaction().commit();
+		Session session = HibernateUtil.getSessionFactory().openSession();
+
+		try{
+
+			session.getTransaction().begin();
+			Query query = session.createQuery("from LGFile where lastVersion = :lastVersion");
+			query.setBoolean("lastVersion", true);
+			list = query.list();
+			session.getTransaction().commit();
+
+		}
+		catch(Exception e){
+			 throw e;
+		}
+		finally{
+
+			if (session != null && session.isOpen()) {
+        session.close();
+      }
+		}
 
 		return list;
 	}
-	
-	
-	
+
+
+
 	protected static List<LGBranch> getAllLGBranchFromDB(){
 		List<LGBranch> list = null;
-		
-		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
-		session.getTransaction().begin();
-		Query query = session.createQuery("from LGBranch");
-		list = query.list();
-		session.getTransaction().commit();
+
+		Session session = HibernateUtil.getSessionFactory().openSession();
+
+		try{
+
+			session.getTransaction().begin();
+			Query query = session.createQuery("from LGBranch");
+			list = query.list();
+			session.getTransaction().commit();
+
+		}
+		catch(Exception e){
+			 throw e;
+		}
+		finally{
+
+			if (session != null && session.isOpen()) {
+        session.close();
+      }
+		}
 
 		return list;
 	}
-	
+
 	protected static void saveDBEntry(DBEntry entry, Date date){
 
 		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
@@ -991,23 +1058,23 @@
 	}
 
 	public static Connection getNewConnection() throws SQLException, IOException{
-		
+
 		return DriverManager.getConnection(
-				DB_URL + PropertiesUtils.getPropValue("db_gazetter_name") + "?useUnicode=yes&characterEncoding=UTF-8", 
-				PropertiesUtils.getPropValue("db_gazetter_username"), 
+				DB_URL + PropertiesUtils.getPropValue("db_gazetter_name") + "?useUnicode=yes&characterEncoding=UTF-8",
+				PropertiesUtils.getPropValue("db_gazetter_username"),
 				PropertiesUtils.getPropValue("db_gazetter_password"));
-		
+
 	}
 
 
 	public List<DBSectionVersion> getSectionVersionList() throws SQLException {
 		List<DBSectionVersion> section_version = new ArrayList<DBSectionVersion>();
-		
+
 		Connection con = null;
 		try{
 			con = getNewConnection();
 			Statement stmt = con.createStatement();
-			// query in sections_versions table. 
+			// query in sections_versions table.
 			// Each line is a current version (with largest version number) for a book.
 			String query = "SELECT * FROM sections_versions s1 " +
 						"JOIN ( " +
@@ -1015,73 +1082,73 @@
 							"FROM sections_versions " +
 							"GROUP BY books_id) AS s2 " +
 							"ON s1.books_id = s2.books_id AND s1.version = s2.version";
-			
+
 			ResultSet rs = stmt.executeQuery(query);
 			while(rs.next()){
 				DBSectionVersion sv = new DBSectionVersion(rs);
 				section_version.add(sv);
-				
+
 			}
 			rs.close();
-			
+
 		} catch (Exception e) {
 			e.printStackTrace();
 		} finally {
 			con.close();
 		}
 		// ----
-		
+
 		return section_version;
 	}
 
 	public void updateTocCorrection(String bookId, String finishedStatus) throws SQLException {
 		Connection conn = null;
 		Statement stmt = null;
-		
+
 		try {
 			String query = "UPDATE books SET toc_correction='"+ finishedStatus + "' WHERE id='" + bookId + "'";
 			conn = getNewConnection();
 			stmt   = conn.createStatement();
 			int rs = stmt.executeUpdate(query);
-			
+
 		} catch (Exception e) {
 			e.printStackTrace();
 		}finally{
 			conn.close();
 		}
-		
+
 		DBBook book = this.bookMap.get(bookId);
 		book.setTocCorrection(finishedStatus);
-	
-		
+
+
 	}
 
 	public void updateComment(String bookId, String comment) throws SQLException {
 		Connection conn = null;
 		Statement stmt = null;
-		
+
 		try {
 			String query = "UPDATE books SET comments='"+ comment + "' WHERE id='" + bookId + "'";
 			conn = getNewConnection();
 			stmt   = conn.createStatement();
 			int rs = stmt.executeUpdate(query);
-			
+
 		} catch (Exception e) {
 			e.printStackTrace();
 		}finally{
 			conn.close();
 		}
-		
+
 		DBBook book = this.bookMap.get(bookId);
 		book.setComments(comment);
-		
-		
+
+
 	}
 
-	
+
 	// remove it
 	/*
-	public static LGFullTextSearchFile getExistFullTextSearchFile(Long userId, String fileName) {		
+	public static LGFullTextSearchFile getExistFullTextSearchFile(Long userId, String fileName) {
 		//logger.info("getExistFullTextSearchFile: (userId,fileName)=" + userId + ","+fileName);
 		List<LGFullTextSearchFile> list = new ArrayList<LGFullTextSearchFile>();
 
@@ -1090,7 +1157,7 @@
 		Query query = session.createQuery("from LGFullTextSearchFile where userId = :userId and fileName = :fileName");
 		query.setLong("userId", userId);
 		query.setString("fileName", fileName);
-		
+
 		list = query.list();
 		session.getTransaction().commit();
 
@@ -1101,91 +1168,120 @@
 			//logger.info("new record.");
 			return null;
 		}
-				
+
 	}
 	 */
-	
-	
-	
+
+
+
 
 	/* --- topic --- */
 	protected static List<LGTopic> getAllLGTopicFromDB(){
 		List<LGTopic> list = null;
-		
-		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
-		session.getTransaction().begin();
-		Query query = session.createQuery("from LGTopic");
-		list = query.list();
-		session.getTransaction().commit();
+
+		Session session = HibernateUtil.getSessionFactory().openSession();
+
+		try{
+
+			session.getTransaction().begin();
+			Query query = session.createQuery("from LGTopic");
+			list = query.list();
+			session.getTransaction().commit();
+
+		}
+		catch(Exception e){
+			 throw e;
+		}
+		finally{
+
+			if (session != null && session.isOpen()) {
+        session.close();
+      }
+		}
 
 		return list;
 	}
-	
+
 	protected static int deleteTopicFromDB(Long topicId){
 		logger.info("Deleting topic by topicId=" + topicId);
-		
+
 		int modifiedTopic;
 		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
+
 		session.getTransaction().begin();
-		
+
 		// delete record in Topic table
 		Query query = session.createQuery("delete LGTopic where id = :id");
 		query.setLong("id", topicId);
 		modifiedTopic = query.executeUpdate();
-		
-		
+
+
 		// delete records in TopicSectionRelation table
 		Query query0 = session.createQuery("delete LGTopicSectionRelation where topicId = :topicId");
 		query0.setLong("topicId", topicId);
 		modifiedTopic += query0.executeUpdate();
-		
+
 		// delete records in TopicTagRelation table
 		Query query1 = session.createQuery("delete LGTopicTagRelation where topicId = :topicId");
 		query1.setLong("topicId", topicId);
 		modifiedTopic += query1.executeUpdate();
-		
-		
+
+
 		session.getTransaction().commit();
-		
+
 		return modifiedTopic;
 	}
-	
-	
+
+
 	protected static List<LGTopicSectionRelation> getAllLGTopicSectionRelationFromDB(){
+
 		List<LGTopicSectionRelation> list = null;
-		
-		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
-		session.getTransaction().begin();
-		Query query = session.createQuery("from LGTopicSectionRelation");
-		list = query.list();
-		session.getTransaction().commit();
-		
+
+		Session session = HibernateUtil.getSessionFactory().openSession();
+
+		try{
+
+			session.getTransaction().begin();
+			Query query = session.createQuery("from LGTopicSectionRelation");
+			list = query.list();
+			session.getTransaction().commit();
+
+		}
+		catch(Exception e){
+			 session.getTransaction().rollback();
+		}
+		finally{
+
+			if (session != null && session.isOpen()) {
+        session.close();
+      }
+		}
 
 		return list;
 	}
-	
+
 	protected static int deleteTopicSectionRelationFromDB(Long relationId){
-		
+
 		int modifiedRelation;
 		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
 		session.getTransaction().begin();
-		
-		
+
+
 		Query query0 = session.createQuery("delete LGTopicSectionRelation where id = :relationId");
 		query0.setLong("relationId", relationId);
 		modifiedRelation = query0.executeUpdate();
-		
+
 		session.getTransaction().commit();
-		
+
 		return modifiedRelation;
-		
+
 	}
 
-	
-	
-	
+
+
+
 	/* --- end topic --- */
 
-	
-	
+
+
 }