Mercurial > hg > LGServices
changeset 85:110be241ff54
All session of ?select? statement are changed to openSession() from getCurreentSession() to avoid nested transaction exception
author | Calvin Yeh <cyeh@mpipw-berlin.mpg.com> |
---|---|
date | Fri, 19 May 2017 20:08:17 +0200 |
parents | c4835abc2524 |
children | 53ca65aad5f7 |
files | src/main/java/de/mpiwg/gazetteer/utils/DBService.java |
diffstat | 1 files changed, 434 insertions(+), 338 deletions(-) [+] |
line wrap: on
line diff
--- 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 --- */ - - + + }