Mercurial > hg > LGServices
view src/main/java/de/mpiwg/gazetteer/utils/DBService.java @ 0:3e62083dbcbf
First commit. This project comes from LGServer. We removed the framework icefaces. Now, LGServices uses just JSP and jquery.
author | "jurzua <jurzua@mpiwg-berlin.mpg.de>" |
---|---|
date | Thu, 23 Apr 2015 15:46:01 +0200 |
parents | |
children | 1af9d7db348e |
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.bo.SearchRulesFile; import de.mpiwg.gazetteer.bo.Sequence; import de.mpiwg.gazetteer.db.DBBook; import de.mpiwg.gazetteer.db.DBSection; 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; 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 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; } /** * 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) { } 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")); } }