Mercurial > hg > LGServices
view src/main/java/de/mpiwg/gazetteer/utils/DBService.java @ 26:ce2e3f2814c0
new: check userGroup when login
author | Zoe Hong <zhong@mpiwg-berlin.mpg.de> |
---|---|
date | Tue, 30 Jun 2015 14:31:29 +0200 |
parents | 2c6f44ef34ab |
children | 3b3e2963c8f7 |
line wrap: on
line source
package de.mpiwg.gazetteer.utils; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.log4j.Logger; import org.hibernate.Query; import org.hibernate.Session; import de.mpiwg.gazetteer.bo.DBEntry; import de.mpiwg.gazetteer.bo.LGBranch; import de.mpiwg.gazetteer.bo.LGFile; import de.mpiwg.gazetteer.db.DBBook; import de.mpiwg.gazetteer.db.DBCoordinatesBook; import de.mpiwg.gazetteer.db.DBSection; import de.mpiwg.gazetteer.db.DBSectionVersion; 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/"; private static String SECTIONS_TABLE = "sections_index"; 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")); } } 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) { // TODO Auto-generated catch block 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(); } catch (Exception e) { e.printStackTrace(); } } return this.coordinatesBookMap; } private void loadCoordinatesBookMap() throws SQLException{ Long start = System.currentTimeMillis(); Connection conn = null; Statement stmt = null; this.coordinatesBookMap = new HashMap<String, DBCoordinatesBook>(); try { 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); this.coordinatesBookMap.put(book.getBooks_id(), book); } } catch (Exception e) { e.printStackTrace(); }finally{ conn.close(); } long end = System.currentTimeMillis(); logger.debug("Time execution loading Coordinates Book Map [ms]: " + (end - start)); } public DBBook getBook(String id){ return getBookMap().get(id); } private Map<String, DBBook> getBookMap(){ if(bookMap == null){ try { 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"; logger.debug(query); conn = getNewConnection(); stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(query); while(rs.next()){ DBBook book = new DBBook(rs); this.bookMap.put(book.getId(), book); } } 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++){ String term = termList.get(i); if(i>0){ query += " OR "; } query += field + " like '%" + term + "%' "; } 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(); ResultSet rs0 = stmt.executeQuery(sql); while(rs0.next()){ DBSection section = new DBSection(rs0); section.setBook(book); list.add(section); } } rs.close(); } catch (Exception e) { e.printStackTrace(); } finally { conn.close(); } long end = System.currentTimeMillis(); logger.debug("Time execution serching Books [ms]: " + (end - start)); return list; } public static List<DBBook> 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 += " contents.content like '%" + term + "%' "; } 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, " + "books.start_year AS start_year, books.end_year AS end_year, " + "books.id AS id, contents.line AS PAGE, contents.content AS CONTENT, " + "books.volume AS volume, books.author AS author, books.edition AS edition " + "FROM contents " + "JOIN books ON contents.books_id = books.id " + "WHERE " + condition + " " + "ORDER BY contents.books_id, contents.line"; Connection conn = null; Statement stmt = null; List<DBBook> resultSet = new ArrayList<DBBook>(); try { conn = getNewConnection(); stmt = conn.createStatement(); int count = 0; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { count++; 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; } /** * 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); if(i>0){ query += " OR "; } query += "name like '%" + term + "%' "; } try { Class.forName(JDBC_DRIVER); conn = getNewConnection(); stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { DBSection section = new DBSection(rs); DBBook book = getInstance().getBook(section.getBookId()); section.setBook(book); list.add(section); } rs.close(); } catch (Exception e) { e.printStackTrace(); } 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"); 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) { e.printStackTrace(); } 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); */ //System.out.println("bookId=" + bookId + ", startPage=" + startPage + ", endPage=" + endPage); //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) { e.printStackTrace(); } finally { conn.close(); } return section; } public static String fixToNewline(String orig){ char[] chars = orig.toCharArray(); StringBuilder sb = new StringBuilder(100); for(char c : chars){ switch(c){ case '\r': case '\f': break; case '\n': sb.append("<br>"); break; default: sb.append(c); } } 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"); String content = rs.getString("content"); sb.append("【" + line + "】" + content + "\n"); } return sb.toString(); } /* public static DBBook getBookFromDB(String id) throws SQLException{ Connection conn = null; DBBook book = null; try { //Class.forName(JDBC_DRIVER); conn = getNewConnection(); book = getBook0FromDB(conn, id); } catch (Exception e) { e.printStackTrace(); } finally { conn.close(); } return book; }*/ /* 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; }*/ /** * This method removed all files for a particular fileId. * The elimination includes the current version as well as the old versions. * @param fileId * @return */ 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 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> 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(); 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(); 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(); return list; } protected static void saveDBEntry(DBEntry entry, Date date){ Session session = HibernateUtil.getSessionFactory().getCurrentSession(); session.getTransaction().begin(); saveDBEntry0(session, entry, date); session.getTransaction().commit(); } public static void saveDBEntry0(Session session, DBEntry entry, Date date){ entry.setLastChangeDate(date); if (entry.isPersistent()) { session.update(entry); } else { entry.setCreationDate(date); session.save(entry); } logger.info("saveDBEntry: " + entry.toString()); } 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"), 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. // Each line is a current version (with largest version number) for a book. String query = "SELECT * FROM sections_versions s1 " + "JOIN ( " + "SELECT books_id, MAX(version) AS version " + "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); } } 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); } }