--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/main/java/de/mpiwg/gazetteer/utils/DBService.java	Tue Mar 10 14:50:41 2015 +0100
@@ -0,0 +1,338 @@
+	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.List;
+
+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;
+import de.mpiwg.web.SessionBean;
+
+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/";
+
+	public static List<DBSection> searchSection(List<String> termList) throws SQLException{
+		List<DBSection> list = new ArrayList<DBSection>();
+		
+		Connection conn = null;
+		Statement stmt = null;
+		
+		String query = "SELECT name, id, books_id FROM sections WHERE ";
+		for(int i=0; i<termList.size() ; i++){
+			String term = termList.get(i);
+			if(i>0){
+				query += " OR ";
+			}
+			query += "name like '" + term + "%' ";
+		}
+		query += " limit 50";
+
+		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 = getBook0(conn, section.getBookId());
+				section.setBook(book);
+				list.add(section);
+			}
+			rs.close();
+		} catch (Exception e) {
+			e.printStackTrace();
+		} finally {
+			conn.close();
+		}
+		
+		return list;
+	}
+	
+	public static List<String> suggestSectionName(String term) throws SQLException {
+		List<String> list = new ArrayList<String>();
+		
+		Connection conn = null;
+		Statement stmt = null;
+		
+		String query = "SELECT name FROM sections WHERE name like '" + term + "%' limit 50";
+
+		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) throws SQLException {
+		List<String> list = new ArrayList<String>();
+		
+		Connection conn = null;
+		Statement stmt = null;
+		
+		String query = "SELECT id FROM sections WHERE id like '" + input + "%' limit 50";
+
+		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 response = new DBSection(sectionId);
+		
+		String query = "SELECT * FROM sections WHERE id = '" + sectionId + "'";
+		
+		try {
+			Class.forName(JDBC_DRIVER);
+			conn = getNewConnection();
+			stmt = conn.createStatement();
+			
+			ResultSet rs = stmt.executeQuery(query);
+			while (rs.next()) {
+				
+				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);
+				response.setText(content);
+				
+				DBBook book = getBook0(conn, bookId);
+				response.setBook(book);
+				
+			}
+			rs.close();
+		} catch (Exception e) {
+			e.printStackTrace();
+		} finally {
+			conn.close();
+		}
+		return response;
+	}
+	
+	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{
+		
+		String query = "SELECT content, line FROM contents WHERE books_id = '" + bookId + "' AND line >= '" + startLine + "' AND line <= '" + endLine + "'";
+		
+		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 getBook(String id) throws SQLException{
+		Connection conn = null;
+		DBBook book = null;
+	
+		try {
+			Class.forName(JDBC_DRIVER);
+
+			conn = getNewConnection();
+			book = getBook0(conn, id);
+		} catch (Exception e) {
+			e.printStackTrace();
+		} finally {
+			conn.close();
+		}
+		return book;
+	}
+	
+	private static DBBook getBook0(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  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"));
+	}
+	
+}