Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package ba.unsa.etf.rs;
- import java.io.FileInputStream;
- import java.io.FileNotFoundException;
- import java.sql.*;
- import java.util.ArrayList;
- import java.util.Scanner;
- public class GeografijaDAO {
- private static GeografijaDAO instance;
- private Connection conn;
- private PreparedStatement testUpit, gradoviUpit, drzavaNazivUpit, glavniGradUpit, drzavaIdUpit, obrisiDrzavuUpit,
- obrisiGradoveUpit, dodajGradUpit, dodajDrzavuUpit, zadnjiGradIdUpit, zadnjaDrzavaIdUpit;
- public static GeografijaDAO getInstance() {
- if (instance == null) {
- instance = new GeografijaDAO();
- }
- return instance;
- }
- private GeografijaDAO() {
- try {
- conn = DriverManager.getConnection("jdbc:sqlite:baza.db");
- } catch (SQLException e) {
- e.printStackTrace();
- }
- try {
- testUpit = conn.prepareStatement("SELECT * FROM grad, drzava");
- } catch (SQLException e) {
- obnoviBazu();
- try {
- gradoviUpit = conn.prepareStatement("SELECT * FROM grad ORDER BY broj_stanovnika DESC");
- drzavaNazivUpit = conn.prepareStatement("SELECT * FROM drzava WHERE drzava.naziv =?");
- glavniGradUpit = conn.prepareStatement("SELECT * FROM grad, drzava WHERE drzava.naziv =? AND drzava.glavni_grad = grad.id");
- drzavaIdUpit = conn.prepareStatement("SELECT * FROM drzava WHERE drzava.id=?");
- obrisiDrzavuUpit = conn.prepareStatement("DELETE FROM drzava WHERE drzava.id=?");
- obrisiGradoveUpit = conn.prepareStatement("DELETE FROM grad WHERE grad.drzava=?");
- dodajGradUpit = conn.prepareStatement("INSERT INTO grad VALUES (?,?,?,?)");
- dodajDrzavuUpit = conn.prepareStatement("INSERT INTO drzava VALUES (?,?,?)");
- zadnjiGradIdUpit = conn.prepareStatement("SELECT MAX(id) FROM grad");
- zadnjaDrzavaIdUpit = conn.prepareStatement("SELECT MAX(id) FROM drzava");
- } catch (SQLException throwables) {
- throwables.printStackTrace();
- }
- }
- }
- public static void removeInstance() { instance = null; }
- public Grad glavniGrad(String drzava) {
- try {
- glavniGradUpit.setString(1 , drzava);
- ResultSet rs = glavniGradUpit.executeQuery();
- if (!rs.next()) return null;
- return dajGradIzResultSeta(rs);
- } catch (SQLException throwables) {
- throwables.printStackTrace();
- return null;
- }
- }
- public void obrisiDrzavu(String drzava) {
- try {
- drzavaNazivUpit.setString(1, drzava);
- ResultSet rs = drzavaNazivUpit.executeQuery();
- if (!rs.next()) return;
- obrisiGradoveUpit.setInt(1, rs.getInt(1));
- obrisiDrzavuUpit.setInt(1, rs.getInt(1));
- obrisiGradoveUpit.executeUpdate();
- obrisiDrzavuUpit.executeUpdate();
- } catch (SQLException throwables) {
- throwables.printStackTrace();
- }
- }
- public ArrayList<Grad> gradovi() {
- ArrayList<Grad> rez = new ArrayList<>();
- try {
- ResultSet rsGrad = gradoviUpit.executeQuery();
- while (rsGrad.next()) {
- Grad grad = dajGradIzResultSeta(rsGrad);
- rez.add(grad);
- }
- return rez;
- } catch (SQLException e) {
- e.printStackTrace();
- return null;
- }
- }
- public void dodajGrad(Grad grad) {
- try {
- ResultSet rs = zadnjiGradIdUpit.executeQuery();
- int id = 1;
- if (rs.next()) {
- id = rs.getInt(1) + 1;
- }
- dodajGradUpit.setInt(1, id);
- dodajGradUpit.setString(2, grad.getNaziv());
- dodajGradUpit.setInt(3, grad.getBrojStanovnika());
- dodajGradUpit.setInt(3, grad.getDrzava().getId());
- dodajGradUpit.executeUpdate();
- } catch (SQLException throwables) {
- throwables.printStackTrace();
- }
- }
- public void dodajDrzavu(Drzava drzava) {
- try {
- ResultSet rs = zadnjaDrzavaIdUpit.executeQuery();
- dodajDrzavuUpit.setInt(1, ;
- dodajDrzavuUpit.setString(2, drzava.getNaziv());
- dodajDrzavuUpit.setInt(3, drzava.getGlavniGrad().getId());
- dodajDrzavuUpit.executeUpdate();
- } catch (SQLException throwables) {
- throwables.printStackTrace();
- }
- }
- private Grad dajGradIzResultSeta(ResultSet rs) throws SQLException {
- Grad grad = new Grad(rs.getInt(1), rs.getString(2), rs.getInt(3), null);
- grad.setDrzava(dajDrzavu(rs.getInt(4), grad));
- return grad;
- }
- private Drzava dajDrzavu(int id, Grad grad) {
- try {
- drzavaIdUpit.setInt(1, id);
- ResultSet rs = drzavaIdUpit.executeQuery();
- return dajDrzavuIzResultSeta(rs, grad);
- } catch (SQLException throwables) {
- throwables.printStackTrace();
- return null;
- }
- }
- private Drzava dajDrzavuIzResultSeta(ResultSet rs, Grad grad) throws SQLException {
- return new Drzava(rs.getInt(1), rs.getString(2), grad);
- }
- private void obnoviBazu() {
- try {
- Scanner scan = new Scanner(new FileInputStream("baza.db.sql"));
- String upit = "";
- while(scan.hasNext()) {
- upit += scan.nextLine();
- if (upit.charAt(upit.length() - 1) == ';') {
- try {
- Statement stmt = conn.createStatement();
- stmt.executeQuery(upit);
- upit = "";
- } catch (SQLException throwables) {
- upit = "";
- }
- }
- }
- scan.close();
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- }
- }
- public Drzava nadjiDrzavu(String francuska) {
- try {
- drzavaNazivUpit.setString(1, francuska);
- ResultSet rs = drzavaNazivUpit.executeQuery();
- return dajDrzavuIzResultSeta(rs, null);
- } catch (SQLException throwables) {
- throwables.printStackTrace();
- return null;
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement