--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/main/java/de/mpiwg/gazetteer/utils/DBService.java	Thu Apr 23 15:46:01 2015 +0200
@@ -0,0 +1,538 @@
+	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"));
+	}
+	
+}