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 com.spvp.dal; import com.spvp.model.Grad; import com.spvp.model.Location; import com.spvp.model.Prognoza; import com.spvp.services.IWebService; import com.spvp.services.LocationService; import com.spvp.services.WebService; import com.spvp.services.WorldWeatherOnlineWebService; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.ParseException; import java.util.ArrayList; import java.util.Calendar; import java.util.concurrent.TimeUnit; import java.util.logging.Level; import java.util.logging.Logger; import javax.sql.DataSource; import org.apache.commons.dbcp.BasicDataSource; import org.springframework.context.support.ClassPathXmlApplicationContext; /** * * @author Ragib Smajic */ public class MySqlDatabase implements IDatabase { private DataSource dataSource; private WebService webService; public void setWebService(IWebService webService) { this.webService = new WebService(webService); } private static MySqlDatabase db = null; public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } private MySqlDatabase() { } public static MySqlDatabase getInstance() { if (db == null) { ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); db = ctx.getBean("mysqlDb", MySqlDatabase.class); } return db; } private Connection getConnection() throws SQLException { return dataSource.getConnection(); } @Override public Boolean ucitajGradoveUBazu(ArrayList<Grad> gradovi) throws SQLException { Connection conn = null; Boolean status = false; try { conn = getConnection(); PreparedStatement pstmt = conn.prepareStatement( "INSERT INTO gradovi (ime, longitude, latitude, veci_centar)" + "VALUES(?,?,?,?)"); for (Grad x : gradovi) { pstmt.clearParameters(); pstmt.setString(1, x.getImeGrada()); pstmt.setDouble(2, x.getLongitude()); pstmt.setDouble(3, x.getLatitude()); pstmt.setBoolean(4, x.getVeciCentar()); pstmt.addBatch(); } pstmt.executeBatch(); status = true; } catch (SQLException ex) { Logger.getLogger(MySqlDatabase.class.getName()).log(Level.SEVERE, null, ex); } finally { if (conn != null) conn.close(); } return status; } @Override public Boolean ucitajPrognozeUBazu(ArrayList<Prognoza> prognoze) throws SQLException { Connection conn = null; Boolean status = false; try { conn = getConnection(); conn.setAutoCommit(false); Statement s = conn.createStatement(); ResultSet rs = s.executeQuery("SELECT AUTO_INCREMENT " + "FROM INFORMATION_SCHEMA.TABLES " + "WHERE TABLE_SCHEMA = 'weather_forecasting' " + "AND TABLE_NAME = 'historija_prognoze';"); int zadnjiId = -1; rs.next(); zadnjiId = rs.getInt("AUTO_INCREMENT"); int idGrada = -1; PreparedStatement pstmt = conn.prepareStatement( "INSERT INTO historija_prognoze (id, vrijeme, temp, pritisak, brzina_vjetra, vlaznost_zraka, datum) " + "VALUES(?, ?,?,?,?,?,?)"); PreparedStatement pstmt3 = conn .prepareStatement("INSERT INTO gradovi_prognoze (prognoza_id, grad_id) " + "VALUES(?,?)"); for (Prognoza x : prognoze) { pstmt.clearParameters(); pstmt.setInt(1, zadnjiId); pstmt.setString(2, x.getVrijeme()); pstmt.setString(3, x.getTemperatura()); pstmt.setString(4, x.getPritisakZraka()); pstmt.setString(5, x.getBrzinaVjetra()); pstmt.setString(6, x.getVlaznostZraka()); pstmt.setDate(7, new java.sql.Date(x.getDatum().getTime())); pstmt.addBatch(); idGrada = dajIdGradaPoImenu(x.getZaGrad().getImeGrada()); pstmt3.clearParameters(); pstmt3.setInt(1, zadnjiId); pstmt3.setInt(2, idGrada); pstmt3.addBatch(); zadnjiId++; } pstmt.executeBatch(); pstmt3.executeBatch(); conn.commit(); status = true; } catch (SQLException ex) { Logger.getLogger(MySqlDatabase.class.getName()).log(Level.SEVERE, null, ex); if (conn != null) conn.rollback(); } finally { if (conn != null) conn.close(); } return status; } @Override public Grad dajGradPoImenu(String imeGrada) throws SQLException { int id; String ime; Double longitude; Double latitude; Boolean veciCentar; try (Connection conn = getConnection()) { PreparedStatement ps = conn.prepareStatement("SELECT id, ime, longitude, latitude, veci_centar " + "FROM gradovi " + "WHERE LOWER(ime) LIKE LOWER(?) "); ps.setString(1, imeGrada); ResultSet rs = ps.executeQuery(); rs.next(); id = rs.getInt("id"); ime = rs.getString("ime"); longitude = rs.getDouble("longitude"); latitude = rs.getDouble("latitude"); veciCentar = rs.getBoolean("veci_centar"); } Grad g = new Grad(ime, longitude, latitude, veciCentar); g.setIdGrada(id); return g; } private int dajIdGradaPoImenu(String imeGrada) throws SQLException { int x; try (Connection conn = getConnection()) { PreparedStatement ps = conn .prepareStatement("SELECT id " + "FROM gradovi " + "WHERE LOWER(ime) LIKE LOWER(?)"); ps.setString(1, imeGrada); ResultSet rs = ps.executeQuery(); if (rs.next()) x = rs.getInt("id"); else return -1; } return x; } @Override public ArrayList<Prognoza> dajHistorijskePodatkePrognozaZaGrad(String grad, int brojDana) throws SQLException { Grad x = this.dajGradPoImenu(grad); this.osvjeziHistorijuPrognozaZaGrad(x, webService); ArrayList<Prognoza> listaPrognoza = new ArrayList<>(); int idGrada = x.getIdGrada(); if (idGrada == -1) return listaPrognoza; try (Connection conn = getConnection()) { PreparedStatement s = conn .prepareStatement("SELECT vrijeme, temp, pritisak, brzina_vjetra, vlaznost_zraka, datum " + "FROM historija_prognoze p, gradovi_prognoze " + "WHERE p.id = prognoza_id AND grad_id =? " + "ORDER BY datum DESC " + "LIMIT ?"); s.setInt(1, idGrada); s.setInt(2, brojDana); ResultSet rs = s.executeQuery(); Grad g = dajGradPoImenu(grad); Prognoza p; while (rs.next()) { p = new Prognoza(g, rs.getString("temp"), rs.getString("vlaznost_zraka"), rs.getString("pritisak"), rs.getString("brzina_vjetra"), rs.getString("vrijeme")); p.setDatum(rs.getDate("datum")); listaPrognoza.add(p); } conn.close(); s.close(); rs.close(); } return listaPrognoza; } private void osvjeziHistorijuPrognozaZaGrad(Grad g, WebService ws) { try (Connection conn = getConnection()) { PreparedStatement ps = conn.prepareStatement("SELECT hp.datum dat " + "FROM historija_prognoze hp, gradovi_prognoze gp " + "WHERE hp.id = gp.prognoza_id AND gp.grad_id = ? " + "ORDER BY hp.datum DESC " + "LIMIT 1"); ps.setInt(1, g.getIdGrada()); ResultSet rs = ps.executeQuery(); if (rs.next()) { Calendar cal = Calendar.getInstance(); cal.setTime(rs.getDate("dat")); cal.set(Calendar.MILLISECOND, 0); cal.set(Calendar.SECOND, 0); cal.set(Calendar.MINUTE, 0); cal.set(Calendar.HOUR, 0); //System.out.println("Zadnji datum u bazi je bio: " + cal.getTime().toString()); Calendar tempDate = Calendar.getInstance(); tempDate.set(Calendar.MILLISECOND, 0); tempDate.set(Calendar.SECOND, 0); tempDate.set(Calendar.MINUTE, 0); tempDate.set(Calendar.HOUR, 0); //System.out.println("Danasnji datum je: " + tempDate.getTime().toString()); long diff = tempDate.getTime().getTime() - cal.getTime().getTime(); long brDana = TimeUnit.DAYS.convert(diff, TimeUnit.MILLISECONDS); //System.out.println("Razlika u danima je: " + brDana); if (brDana == 0) return; //System.out.println("Osvjezavanje zadnjeg dana u bazi..."); this.osvjeziZadnjuPrognozuZaGrad(g, ws); //System.out.println("Ucitavanje novih prognoza..."); Location l = new Location(false); l.setCity(g.getImeGrada()); l.setCountry("Bosnia and Herzegovina"); l.setCountryCode("ba"); l.setLatitude(g.getLatitude()); l.setLongitude(g.getLongitude()); l.setStatus(Boolean.TRUE); this.ucitajPrognozeUBazu(ws.getHistorijskePodatkeByLocation(l, (int) brDana)); } else { // Ne postoji ni jedan unos prognoze za dati grad //System.out.println("Zadnji datum u bazi je bio: " + rs.getDate("dat").toString()); Location l = new Location(true); l.setCity(g.getImeGrada()); l.setCountryCode("ba"); l.setLatitude(g.getLatitude()); l.setLongitude(g.getLongitude()); l.setCountry("Bosnia and Herzegovina"); this.ucitajPrognozeUBazu(ws.getHistorijskePodatkeByLocation(l, 15)); } } catch (SQLException ex) { Logger.getLogger(MySqlDatabase.class.getName()).log(Level.SEVERE, null, ex); } catch (ParseException ex) { Logger.getLogger(MySqlDatabase.class.getName()).log(Level.SEVERE, null, ex); } } @Override public void osvjeziHistorijuPrognoza(WebService ws) throws SQLException, ParseException { try (Connection conn = getConnection()) { Statement s = conn.createStatement(); ResultSet rs = s.executeQuery("SELECT id, ime, longitude, latitude " + "FROM gradovi"); Grad x; while (rs.next()) { x = new Grad(rs.getString("ime"), rs.getDouble("longitude"), rs.getDouble("latitude")); x.setIdGrada(rs.getInt("id")); this.osvjeziHistorijuPrognozaZaGrad(x, ws); } rs.close(); s.close(); } catch (SQLException ex) { Logger.getLogger(MySqlDatabase.class.getName()).log(Level.SEVERE, null, ex); } } public static void main(String[] args) throws SQLException, ParseException { //MySqlDatabase db = new MySqlDatabase(); //BasicDataSource ds = new BasicDataSource(); //ds.setUrl("jdbc:mysql://192.168.1.50:3306/weather_forecasting?serverTimezone=CET"); //ds.setUsername("rsmajic"); //ds.setPassword("ragib"); //db.setDataSource(ds); //WebService ws = new WebService(new WorldWeatherOnlineWebService()); //Grad g = LocationService.getLocationByCityName("Mostar").getGrad(); //g.setIdGrada(db.dajIdGradaPoImenu("Sarajevo")); //db.osvjeziHistorijuPrognozaZaGrad(g, ws); //db.osvjeziZadnjuPrognozuZaGrad("Sarajevo", ws); //db.osvjeziHistorijuPrognoza(ws); //for(int i = 0; i < 500; i++) //System.out.println(db.dajIdGradaPoImenu("Sarajevo")); //db.ucitajGradoveUBazu(db.dajVeceCentre()); //db.ucitajGradoveUBazu(db.dajManjeCentre()); //db.ucitajPrognozeUBazu(db.dajPrognozeZaVeceCentre()); } @Override public void osvjeziZadnjuPrognozuZaGrad(Grad grad, WebService ws) throws SQLException, ParseException { int id = grad.getIdGrada(); try (Connection conn = getConnection()) { PreparedStatement ps = conn.prepareStatement( "SELECT hp.id id, hp.datum datum " + "FROM historija_prognoze hp, gradovi_prognoze gp " + "WHERE hp.id = gp.prognoza_id AND gp.grad_id = ? AND hp.datum =(SELECT thp.datum " + "FROM historija_prognoze thp, gradovi_prognoze tgp " + "WHERE thp.id = tgp.prognoza_id AND tgp.grad_id = ? " + "ORDER BY thp.datum DESC " + "LIMIT 1 " + ")"); ps.setInt(1, id); ps.setInt(2, id); ResultSet rs = ps.executeQuery(); int idPrognoze = -1; Calendar cal = null; if (rs.next()) { idPrognoze = rs.getInt("id"); cal = Calendar.getInstance(); cal.setTime(rs.getDate("datum")); } else return; Location l = new Location(true); l.setCity(grad.getImeGrada()); l.setCountry("Bosnia"); l.setCountryCode("ba"); l.setLatitude(grad.getLatitude()); l.setLongitude(grad.getLongitude()); //System.out.println(l.getCity()); Prognoza prognoza = ws.getHistorijskePodatkeByLocationOnSpecificDate(l, cal); ps = conn.prepareStatement( "UPDATE historija_prognoze " + "SET vrijeme = ?, " + " temp = ?, " + " pritisak = ?, " + " brzina_vjetra = ?, " + " vlaznost_zraka = ? " + "WHERE id = ?"); ps.setString(1, prognoza.getVrijeme()); ps.setString(2, prognoza.getTemperatura()); ps.setString(3, prognoza.getPritisakZraka()); ps.setString(4, prognoza.getBrzinaVjetra()); ps.setString(5, prognoza.getVlaznostZraka()); ps.setInt(6, idPrognoze); ps.executeUpdate(); ps.close(); conn.close(); rs.close(); } } private ArrayList<Grad> dajVeceCentre() { ArrayList<Grad> gradovi = new ArrayList<>(); Grad g = LocationService.getLocationByCityName("Sarajevo").getGrad(); g.setVeciCentar(Boolean.TRUE); gradovi.add(g); g = LocationService.getLocationByCityName("Zenica").getGrad(); g.setVeciCentar(Boolean.TRUE); gradovi.add(g); g = LocationService.getLocationByCityName("Tuzla").getGrad(); g.setVeciCentar(Boolean.TRUE); gradovi.add(g); g = LocationService.getLocationByCityName("Mostar").getGrad(); g.setVeciCentar(Boolean.TRUE); gradovi.add(g); g = LocationService.getLocationByCityName("Banja Luka").getGrad(); g.setVeciCentar(Boolean.TRUE); gradovi.add(g); g = LocationService.getLocationByCityName("Bihac").getGrad(); g.setVeciCentar(Boolean.TRUE); gradovi.add(g); g = LocationService.getLocationByCityName("Trebinje").getGrad(); g.setVeciCentar(Boolean.TRUE); gradovi.add(g); g = LocationService.getLocationByCityName("Visegrad").getGrad(); g.setVeciCentar(Boolean.TRUE); gradovi.add(g); g = LocationService.getLocationByCityName("Doboj").getGrad(); g.setVeciCentar(Boolean.TRUE); gradovi.add(g); g = LocationService.getLocationByCityName("Bugojno").getGrad(); g.setVeciCentar(Boolean.TRUE); gradovi.add(g); g = LocationService.getLocationByCityName("Livno").getGrad(); g.setVeciCentar(Boolean.TRUE); gradovi.add(g); return gradovi; } private ArrayList<Grad> dajManjeCentre() { ArrayList<Grad> gradovi = new ArrayList<>(); Grad g = LocationService.getLocationByCityName("Sanski Most").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Travnik").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Konjic").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Neum").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Jablanica").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Mrkonjic Grad").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Drvar").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Gradacac").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Bijeljina").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Gorazde").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Foca").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Stolac").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Ljubinje").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Derventa").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Gradiska").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Kakanj").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Vlasenica").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Visoko").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Fojnica").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Srebrenica").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); g = LocationService.getLocationByCityName("Velika Kladusa").getGrad(); g.setVeciCentar(Boolean.FALSE); gradovi.add(g); return gradovi; } private ArrayList<Prognoza> dajPrognozeZaVeceCentre() throws ParseException { WebService ws = new WebService(new WorldWeatherOnlineWebService()); ArrayList<Prognoza> prognoze; ArrayList<Prognoza> sve = new ArrayList<>(); Location l = LocationService.getLocationByCityName("Sarajevo"); prognoze = ws.getHistorijskePodatkeByLocation(l, 10); sve.addAll(prognoze); //l.setCity("Zenica"); l = LocationService.getLocationByCityName("Zenica"); prognoze = ws.getHistorijskePodatkeByLocation(l, 10); sve.addAll(prognoze); //l.setCity("Mostar"); l = LocationService.getLocationByCityName("Mostar"); prognoze = ws.getHistorijskePodatkeByLocation(l, 10); sve.addAll(prognoze); //l.setCity("Tuzla"); l = LocationService.getLocationByCityName("Tuzla"); prognoze = ws.getHistorijskePodatkeByLocation(l, 10); sve.addAll(prognoze); //l.setCity("Banja Luka"); l = LocationService.getLocationByCityName("Banja Luka"); prognoze = ws.getHistorijskePodatkeByLocation(l, 10); sve.addAll(prognoze); return sve; } }