Mercurial > hg > LGServer

comparison src/main/java/de/mpiwg/gazetteer/utils/DBService.java @ 0:7682c04c63a8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
First commit of the source code!
author "jurzua <jurzua@mpiwg-berlin.mpg.de>"
date Tue, 10 Mar 2015 14:50:41 +0100
parents
children 5316e79f9a27
comparison
equal deleted inserted replaced
-1:000000000000 0:7682c04c63a8
1 package de.mpiwg.gazetteer.utils;
2
3 import java.io.IOException;
4 import java.sql.Connection;
5 import java.sql.DriverManager;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.sql.Statement;
9 import java.util.ArrayList;
10 import java.util.Date;
11 import java.util.List;
12
13 import org.apache.log4j.Logger;
14 import org.hibernate.Query;
15 import org.hibernate.Session;
16
17 import de.mpiwg.gazetteer.bo.DBEntry;
18 import de.mpiwg.gazetteer.bo.LGBranch;
19 import de.mpiwg.gazetteer.bo.LGFile;
20 import de.mpiwg.gazetteer.bo.SearchRulesFile;
21 import de.mpiwg.gazetteer.bo.Sequence;
22 import de.mpiwg.gazetteer.db.DBBook;
23 import de.mpiwg.gazetteer.db.DBSection;
24 import de.mpiwg.web.SessionBean;
25
26 public class DBService {
27
28 private static Logger logger = Logger.getLogger(DBService.class);
29
30 // JDBC driver name and database URL
31 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
32 static final String DB_URL = "jdbc:mysql://localhost/";
33
34 public static List<DBSection> searchSection(List<String> termList) throws SQLException{
35 List<DBSection> list = new ArrayList<DBSection>();
36
37 Connection conn = null;
38 Statement stmt = null;
39
40 String query = "SELECT name, id, books_id FROM sections WHERE ";
41 for(int i=0; i<termList.size() ; i++){
42 String term = termList.get(i);
43 if(i>0){
44 query += " OR ";
45 }
46 query += "name like '" + term + "%' ";
47 }
48 query += " limit 50";
49
50 try {
51 Class.forName(JDBC_DRIVER);
52 conn = getNewConnection();
53 stmt = conn.createStatement();
54
55 ResultSet rs = stmt.executeQuery(query);
56 while (rs.next()) {
57 DBSection section = new DBSection(rs);
58 DBBook book = getBook0(conn, section.getBookId());
59 section.setBook(book);
60 list.add(section);
61 }
62 rs.close();
63 } catch (Exception e) {
64 e.printStackTrace();
65 } finally {
66 conn.close();
67 }
68
69 return list;
70 }
71
72 public static List<String> suggestSectionName(String term) throws SQLException {
73 List<String> list = new ArrayList<String>();
74
75 Connection conn = null;
76 Statement stmt = null;
77
78 String query = "SELECT name FROM sections WHERE name like '" + term + "%' limit 50";
79
80 try {
81 Class.forName(JDBC_DRIVER);
82 conn = getNewConnection();
83 stmt = conn.createStatement();
84
85 ResultSet rs = stmt.executeQuery(query);
86 while (rs.next()) {
87 String name = rs.getString("name");
88 if(!list.contains(name)){
89 list.add(name);
90 }
91 }
92 rs.close();
93 } catch (Exception e) {
94 e.printStackTrace();
95 } finally {
96 conn.close();
97 }
98 return list;
99 }
100
101
102 public static List<String> suggestSectionId(String input) throws SQLException {
103 List<String> list = new ArrayList<String>();
104
105 Connection conn = null;
106 Statement stmt = null;
107
108 String query = "SELECT id FROM sections WHERE id like '" + input + "%' limit 50";
109
110 try {
111 Class.forName(JDBC_DRIVER);
112 conn = getNewConnection();
113 stmt = conn.createStatement();
114
115 ResultSet rs = stmt.executeQuery(query);
116 while (rs.next()) {
117
118 String id = rs.getString("id");
119 list.add(id);
120
121 }
122 rs.close();
123 } catch (Exception e) {
124 e.printStackTrace();
125 } finally {
126 conn.close();
127 }
128
129
130
131 return list;
132 }
133
134 public static DBSection getSectionWithContent(Long sectionId) throws SQLException {
135
136 Connection conn = null;
137 Statement stmt = null;
138 DBSection response = new DBSection(sectionId);
139
140 String query = "SELECT * FROM sections WHERE id = '" + sectionId + "'";
141
142 try {
143 Class.forName(JDBC_DRIVER);
144 conn = getNewConnection();
145 stmt = conn.createStatement();
146
147 ResultSet rs = stmt.executeQuery(query);
148 while (rs.next()) {
149
150 String bookId = rs.getString("books_id");
151 int startPage = rs.getInt("start_page");
152 int endPage = rs.getInt("end_page");
153 String sectionName = rs.getString("name");
154
155 response.setBookId(bookId);
156 response.setName(sectionName);
157
158 System.out.println("bookId=" + bookId + ", startPage=" + startPage + ", endPage=" + endPage);
159 String content = getContent(conn, bookId, startPage, endPage);
160 response.setText(content);
161
162 DBBook book = getBook0(conn, bookId);
163 response.setBook(book);
164
165 }
166 rs.close();
167 } catch (Exception e) {
168 e.printStackTrace();
169 } finally {
170 conn.close();
171 }
172 return response;
173 }
174
175 public static String fixToNewline(String orig){
176 char[] chars = orig.toCharArray();
177 StringBuilder sb = new StringBuilder(100);
178 for(char c : chars){
179 switch(c){
180 case '\r':
181 case '\f':
182 break;
183 case '\n':
184 sb.append("<br>");
185 break;
186 default:
187 sb.append(c);
188 }
189 }
190 return sb.toString();
191 }
192
193 //"SELECT `content`, `line`, `books_id` FROM `contents` WHERE `books_id`=\"%s\" AND `line`>=%d AND `line`<=%d
194 private static String getContent(Connection conn, String bookId, Integer startLine, Integer endLine) throws Exception{
195
196 String query = "SELECT content, line FROM contents WHERE books_id = '" + bookId + "' AND line >= '" + startLine + "' AND line <= '" + endLine + "'";
197
198 logger.debug(query);
199
200 Statement stmt = conn.createStatement();
201 ResultSet rs = stmt.executeQuery(query);
202
203 StringBuilder sb = new StringBuilder();
204 while(rs.next()){
205 String line = rs.getString("line");
206 String content = rs.getString("content");
207 sb.append("【" + line + "】" + content + "\n");
208 }
209
210 return sb.toString();
211 }
212
213
214 public static DBBook getBook(String id) throws SQLException{
215 Connection conn = null;
216 DBBook book = null;
217
218 try {
219 Class.forName(JDBC_DRIVER);
220
221 conn = getNewConnection();
222 book = getBook0(conn, id);
223 } catch (Exception e) {
224 e.printStackTrace();
225 } finally {
226 conn.close();
227 }
228 return book;
229 }
230
231 private static DBBook getBook0(Connection conn, String id) throws SQLException{
232 DBBook book = null;
233
234 String query = "SELECT * FROM books WHERE id = '" + id + "'";
235 logger.debug(query);
236
237 Statement stmt = conn.createStatement();
238 ResultSet rs = stmt.executeQuery(query);
239
240 if(rs.next()){
241 book = new DBBook(rs);
242 }
243
244 return book;
245 }
246
247 /**
248 * This method removed all files for a particular fileId.
249 * The elimination includes the current version as well as the old versions.
250 * @param fileId
251 * @return
252 */
253 protected static int deleteBranchFromDB(Long branchId){
254 logger.info("Deleting Branch by branchId=" + branchId);
255
256 int modifiedFiles;
257 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
258 session.getTransaction().begin();
259
260 Query query = session.createQuery("delete LGBranch where id = :id");
261 query.setLong("id", branchId);
262 modifiedFiles = query.executeUpdate();
263
264 Query query0 = session.createQuery("delete LGFile where branchId = :branchId");
265 query0.setLong("branchId", branchId);
266 modifiedFiles += query0.executeUpdate();
267
268 session.getTransaction().commit();
269
270 return modifiedFiles;
271 }
272
273 protected static List<LGFile> getAllLGFileFromDB(){
274 List<LGFile> list = null;
275
276 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
277 session.getTransaction().begin();
278 Query query = session.createQuery("from LGFile");
279 list = query.list();
280 session.getTransaction().commit();
281
282 return list;
283 }
284
285 protected static List<LGFile> getCurrentLGFilesFromDB(){
286 List<LGFile> list = null;
287
288 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
289 session.getTransaction().begin();
290 Query query = session.createQuery("from LGFile where lastVersion = :lastVersion");
291 query.setBoolean("lastVersion", true);
292 list = query.list();
293 session.getTransaction().commit();
294
295 return list;
296 }
297
298 protected static List<LGBranch> getAllLGBranchFromDB(){
299 List<LGBranch> list = null;
300
301 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
302 session.getTransaction().begin();
303 Query query = session.createQuery("from LGBranch");
304 list = query.list();
305 session.getTransaction().commit();
306
307 return list;
308 }
309
310 protected static void saveDBEntry(DBEntry entry, Date date){
311
312 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
313 session.getTransaction().begin();
314
315 saveDBEntry0(session, entry, date);
316
317 session.getTransaction().commit();
318 }
319
320 public static void saveDBEntry0(Session session, DBEntry entry, Date date){
321 entry.setLastChangeDate(date);
322 if (entry.isPersistent()) {
323 session.update(entry);
324 } else {
325 entry.setCreationDate(date);
326 session.save(entry);
327 }
328 logger.info("saveDBEntry: " + entry.toString());
329 }
330
331 public static Connection getNewConnection() throws SQLException, IOException{
332 return DriverManager.getConnection(
333 DB_URL + PropertiesUtils.getPropValue("db_gazetter_name") + "?useUnicode=yes&characterEncoding=UTF-8",
334 PropertiesUtils.getPropValue("db_gazetter_username"),
335 PropertiesUtils.getPropValue("db_gazetter_password"));
336 }
337
338 }