Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package service; 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.HashMap; import java.util.Locale; import java.util.Map; import java.util.logging.Level; import java.util.logging.Logger; import org.apache.commons.io.IOUtils; /** * * @author Mikhail */ public class DBConnector { private String host; private String user; private String password; private Connection connection; private Term term; public DBConnector() { this.host = "jdbc:oracle:thin:@dev.iati.ee:1521:XE"; this.user = "IATI_IT_DICTIONARY"; this.password = "vnzngv"; this.term = new Term(); } public void openConnection() { try { Locale.setDefault(Locale.ENGLISH); Class.forName("oracle.jdbc.OracleDriver"); this.connection = DriverManager.getConnection(host, user, password); } catch (SQLException ex) { System.err.println(ex.getMessage()); ex.printStackTrace(); } catch (ClassNotFoundException ex) { Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex); } } public void closeConnection() { try { this.connection.close(); } catch (SQLException ex) { System.err.println(ex.getMessage()); ex.printStackTrace(); } } public ArrayList loadAllTermsFromDB() { ArrayList list = new ArrayList<Term>(); Statement select; ResultSet result = null; try { select = this.connection.createStatement(); result = select.executeQuery("SELECT * FROM terms"); while (result.next()) { int id = result.getInt("TERM_ID"); String term = result.getString("TERM"); String lang = result.getString("LANG"); list.add(new Term(id, term, lang)); } select.close(); } catch (SQLException ex) { Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex); } return list; } public ArrayList loadTermsByLangAndModulesIDFromDB(String paramLang, int modulesId) { paramLang = paramLang.trim().toUpperCase(); ArrayList list = new ArrayList<Term>(); Statement select; ResultSet result = null; try { select = this.connection.createStatement(); result = select.executeQuery(String.format( "SELECT * FROM terms WHERE UPPER(lang) = '%s' AND term_id IN(SELECT term_id FROM terms_modules WHERE module_id = %d)", paramLang, modulesId)); while (result.next()) { int id = result.getInt("TERM_ID"); String term = result.getString("TERM"); String lang = result.getString("LANG"); list.add(new Term(id, term, lang)); } select.close(); } catch (SQLException ex) { Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex); } return list; } public ArrayList loadTermsBylangAndTERM(String paramLang, String termParam) { paramLang = paramLang.trim().toUpperCase(); termParam.toUpperCase(); ArrayList list = new ArrayList<Term>(); Statement select; ResultSet result = null; try { select = this.connection.createStatement(); result = select.executeQuery( String.format("SELECT * FROM terms WHERE UPPER(lang) = '%s' AND UPPER(term) LIKE %s%% ", paramLang, termParam)); while (result.next()) { int id = result.getInt("TERM_ID"); String term = result.getString("TERM"); String lang = result.getString("LANG"); list.add(new Term(id, term, lang)); } select.close(); } catch (SQLException ex) { Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex); } return list; } public Definition loadDefinitionByIdFromDB(int termId) { Definition definition = new Definition(); Statement select; ResultSet result = null; Map tmp = new HashMap<String, String>(); String lang = ""; try { select = this.connection.createStatement(); result = select.executeQuery(String.format("SELECT lang FROM terms WHERE term_id =%d", termId)); while (result.next()) { lang = result.getString("LANG").toUpperCase().trim(); } if (lang.equals("ENG")) { result = select.executeQuery(String.format("SELECT term, description, d.lang\n" + "FROM terms_definitions td \n" + "INNER JOIN definitions d\n" + "ON (d.definition_id=td.definition_id)\n" + "LEFT OUTER JOIN terms t\n" + "ON (t.term_id=td.term_id)\n" + "WHERE td.definition_id IN (SELECT transl_defination_id \n" + " FROM definations_translates \n" + " WHERE eng_defination_id=(SELECT definition_id \n" + " FROM definitions \n" + " WHERE definition_id=(SELECT definition_id \n" + " FROM terms_definitions \n" + " WHERE term_id=%d)))\n" + "UNION\n" + "SELECT term, description, d1.lang\n" + "FROM terms_definitions td1 \n" + "INNER JOIN definitions d1\n" + "ON (d1.definition_id=td1.definition_id)\n" + "LEFT OUTER JOIN terms t1\n" + "ON (t1.term_id=td1.term_id)\n" + "WHERE td1.definition_id =(SELECT definition_id \n" + " FROM definitions \n" + " WHERE definition_id=(SELECT definition_id \n" + " FROM terms_definitions \n" + " WHERE term_id=%d))", termId, termId)); while (result.next()) { tmp.put(result.getString("LANG"), result.getString("DESCRIPTION")); definition.setDescription(tmp); } } else { int serachableId = 0; result = select.executeQuery(String.format("SELECT term_id \n" + "FROM terms_definitions td \n" + "WHERE (td.definition_id=(SELECT eng_defination_id \n" + " FROM definations_translates \n" + " WHERE transl_defination_id=(SELECT definition_id \n" + " FROM definitions \n" + " WHERE definition_id=(SELECT definition_id \n" + " FROM terms_definitions \n" + " WHERE term_id=%d))))", termId)); while (result.next()) { serachableId = result.getInt("TERM_ID"); } result = select.executeQuery(String.format("SELECT term, description, d.lang\n" + "FROM terms_definitions td \n" + "INNER JOIN definitions d\n" + "ON (d.definition_id=td.definition_id)\n" + "LEFT OUTER JOIN terms t\n" + "ON (t.term_id=td.term_id)\n" + "WHERE td.definition_id IN (SELECT transl_defination_id \n" + " FROM definations_translates \n" + " WHERE eng_defination_id=(SELECT definition_id \n" + " FROM definitions \n" + " WHERE definition_id=(SELECT definition_id \n" + " FROM terms_definitions \n" + " WHERE term_id=%d)))\n" + "UNION\n" + "SELECT term, description, d1.lang\n" + "FROM terms_definitions td1 \n" + "INNER JOIN definitions d1\n" + "ON (d1.definition_id=td1.definition_id)\n" + "LEFT OUTER JOIN terms t1\n" + "ON (t1.term_id=td1.term_id)\n" + "WHERE td1.definition_id =(SELECT definition_id \n" + " FROM definitions \n" + " WHERE definition_id=(SELECT definition_id \n" + " FROM terms_definitions \n" + " WHERE term_id=%d))", serachableId, serachableId)); while (result.next()) { tmp.put(result.getString("LANG"), result.getString("DESCRIPTION")); definition.setDescription(tmp); } } select.close(); } catch (SQLException ex) { Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex); System.err.println(ex.getMessage()); } return definition; } public ArrayList loadModuls() { ArrayList list = new ArrayList<Modul>(); Statement select; ResultSet result = null; try { select = this.connection.createStatement(); result = select.executeQuery("SELECT * FROM modules"); while (result.next()) { String name = result.getString("MODULE_NAME"); int id = result.getInt("MODULE_ID"); list.add(new Modul(name, id)); } select.close(); } catch (SQLException ex) { Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex); } return list; } }