package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class City implements dbConnection, Comparable<City> {

    private int ID;
    private String name;
    private Country country;
    private String district;
    private int population;

    public City() {
    }

    public City(int ID, String name, Country country, String district, int population) {
        this.ID = ID;
        this.name = name;
        this.country = country;
        this.district = district;
        this.population = population;
    }

    public int getID() {
        return ID;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Country getCountry() {
        return country;
    }

    public void setCountryCode(Country country) {
        this.country = country;
    }

    public String getDistrict() {
        return district;
    }

    public void setDistrict(String district) {
        this.district = district;
    }

    public int getPopulation() {
        return population;
    }

    public void setPopulation(int population) {
        this.population = population;
    }

    @Override
    public int compareTo(City o) {
        return name.compareTo(o.name);
    }

    public List<City> getCities(Country country) {
        List<City> cities = new ArrayList<>();

        try (Connection con = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            String selectStmt = "SELECT * FROM city "
                + "INNER JOIN country "
                + "ON city.CountryCode = country.Code "
                + "WHERE country.Code = ? ";
            PreparedStatement ps = con.prepareStatement(selectStmt);
            ps.setString(1, country.getCode());
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                ID = rs.getInt("ID");
                name = rs.getString("Name");
                district = rs.getString("District");
                population = rs.getInt("Population");

                City city = new City(ID, name, country, district, population);
                cities.add(city);
            }
        } catch (SQLException ex) {
            System.out.println("SQLExeption - getCities()");
        }
        Collections.sort(cities);
        return cities;
    }

    public void deleteCity(City city) {
        try (Connection con = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            String deleteStmt = "DELETE FROM city "
                            + "WHERE ID = ?";
            PreparedStatement ps = con.prepareStatement(deleteStmt);
            ps.setInt(1, city.ID);
            ps.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("SQLExeption - deleteCity()");
        }
    }

}

package model;

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.Collections;
import java.util.List;

public class Country implements dbConnection, Comparable<Country> {

    private String code;
    private String name;
    private String continent;
    private String region;
    private float surfaceArea;
    private Short indepYear;
    private int population;
    private Float lifeExpectancy;
    private Float GNP;
    private Float GNPOld;
    private String localName;
    private String governmentForm;
    private String headOfState;
    private Integer capital;
    private String code2;

    public Country() {
    }

    public Country(String code, String name) {
        this.code = code;
        this.name = name;
    }

    public Country(String code, String name, String continent, String region,
            float surfaceArea, Short indepYear, int population,
            Float lifeExpectancy, Float GNP, Float GNPOld,
            String localName, String governmentForm,
            String headOfState, Integer capital, String code2) {
        this.code = code;
        this.name = name;
        this.continent = continent;
        this.region = region;
        this.surfaceArea = surfaceArea;
        this.indepYear = indepYear;
        this.population = population;
        this.lifeExpectancy = lifeExpectancy;
        this.GNP = GNP;
        this.GNPOld = GNPOld;
        this.localName = localName;
        this.governmentForm = governmentForm;
        this.headOfState = headOfState;
        this.capital = capital;
        this.code2 = code2;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getContinent() {
        return continent;
    }

    public void setContinent(String continent) {
        this.continent = continent;
    }

    public String getRegion() {
        return region;
    }

    public void setRegion(String region) {
        this.region = region;
    }

    public float getSurfaceArea() {
        return surfaceArea;
    }

    public void setSurfaceArea(float surfaceArea) {
        this.surfaceArea = surfaceArea;
    }

    public Short getIndepYear() {
        return indepYear;
    }

    public void setIndepYear(Short indepYear) {
        this.indepYear = indepYear;
    }

    public int getPopulation() {
        return population;
    }

    public void setPopulation(int population) {
        this.population = population;
    }

    public Float getLifeExpectancy() {
        return lifeExpectancy;
    }

    public void setLifeExpectancy(Float lifeExpectancy) {
        this.lifeExpectancy = lifeExpectancy;
    }

    public Float getGNP() {
        return GNP;
    }

    public void setGNP(Float GNP) {
        this.GNP = GNP;
    }

    public Float getGNPOld() {
        return GNPOld;
    }

    public void setGNPOld(Float GNPOld) {
        this.GNPOld = GNPOld;
    }

    public String getLocalName() {
        return localName;
    }

    public void setLocalName(String localName) {
        this.localName = localName;
    }

    public String getGovernmentForm() {
        return governmentForm;
    }

    public void setGovernmentForm(String governmentForm) {
        this.governmentForm = governmentForm;
    }

    public String getHeadOfState() {
        return headOfState;
    }

    public void setHeadOfState(String headOfState) {
        this.headOfState = headOfState;
    }

    public Integer getCapital() {
        return capital;
    }

    public void setCapital(Integer capital) {
        this.capital = capital;
    }

    public String getCode2() {
        return code2;
    }

    public void setCode2(String code2) {
        this.code2 = code2;
    }

    @Override
    public int compareTo(Country o) {
        return name.compareTo(o.getName());
    }

    public List<Country> getCountries() {
        List<Country> countries = new ArrayList<>();

        try (Connection con = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            Statement stmt = con.createStatement();
            String query = "SELECT Code, Name FROM country";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                code = rs.getString("Code");
                name = rs.getString("Name");
                Country country = new Country(code, name);
                countries.add(country);
            }
        } catch (SQLException ex) {
            System.out.println("SQLException - getCountries()");
        }
        Collections.sort(countries);
        return countries;
    }

    @Override
    public String toString() {
        return getName();
    }

}

package model;

public interface dbConnection {
    String URL = "jdbc:mysql://localhost:3306/world";
    String USERNAME = "root";
    String PASSWORD = "1234";
}

package view;

import java.awt.Graphics;
import java.awt.Image;
import java.awt.event.ActionListener;
import java.net.URL;
import javax.swing.ImageIcon;
import javax.swing.JFrame;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;
import javax.swing.JOptionPane;
import javax.swing.JPanel;

public class MainWindow {

    private JFrame frame;
    private JMenuBar menuBar;
    private JMenu file;
    private JMenu data;
    private JMenuItem exitMenuItem;
    private JMenuItem citiesMenuItem;

    public MainWindow() {
        initComponents();
    }

    private void initComponents() {
        frame = new JFrame("Swing application with JDBC using World sample database");
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setSize(625, 650);
        frame.setResizable(false);
        frame.setJMenuBar(createMenuBar());

        try {
            frame.getContentPane().add(new MyBackgroundPanel());
        } catch (NullPointerException ex) {
            JOptionPane.showMessageDialog(null, "Background image is missing!", "Error", JOptionPane.ERROR_MESSAGE);
        }

        frame.setVisible(true);
        frame.setLocationRelativeTo(null);
    }

    private JMenuBar createMenuBar() {
        menuBar = new JMenuBar();
        file = new JMenu("File");
        exitMenuItem = new JMenuItem("Exit");
        file.add(exitMenuItem);

        data = new JMenu("Data");
        citiesMenuItem = new JMenuItem("Load cities");
        data.add(citiesMenuItem);

        menuBar.add(file);
        menuBar.add(data);
        return menuBar;
    }

    public JMenuItem getExitMenu() {
        return exitMenuItem;
    }

    public JMenuItem getLoadCitiesMenu() {
        return citiesMenuItem;
    }

    public void addMenuActionListener (ActionListener menuActionListener) {
        exitMenuItem.addActionListener(menuActionListener);
        citiesMenuItem.addActionListener(menuActionListener);
    }

    private class MyBackgroundPanel extends JPanel {
        URL url = getClass().getResource("worldMap03.jpg");
        Image img = new ImageIcon(url).getImage();

        @Override
        protected void paintComponent(Graphics g) {
            g.drawImage(img, 0, 0, null);
        }
    }
}

package view;

import java.awt.Dimension;
import java.awt.FlowLayout;
import java.awt.event.ActionListener;
import java.awt.event.MouseListener;
import java.util.Vector;
import javax.swing.Box;
import javax.swing.DefaultListSelectionModel;
import javax.swing.JButton;
import javax.swing.JDialog;
import javax.swing.JList;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.SwingConstants;
import javax.swing.border.Border;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableModel;

public class CitiesDialog extends JDialog {

    private JPanel listPanel;
    private JPanel tablePanel;
    private JList list;
    private DefaultTableModel model;
    private JTable table;
    private JScrollPane tScrollPane;
    private DefaultTableCellRenderer rightRenderer;
    private JButton deleteButton;
    private Border listBorder;
    private final Object[] columnNames = {"City name", "District", "Population"};

    public CitiesDialog() {
        setTitle("Country dialog");
        setSize(800, 600);
        setLayout(new FlowLayout(FlowLayout.LEADING, 0, 0));
        setLocationRelativeTo(null);
        add(createListPanel());
        add(createTablePanel());
        //setModal(true);
        setResizable(false);
        setVisible(true);
    }

    private JPanel createListPanel() {
        listPanel = new JPanel();

    //JList for countries
        list = new JList();
        JScrollPane listScrollPane = new JScrollPane(list);
        listScrollPane.setPreferredSize(new Dimension(200, 560));

        listPanel.add(listScrollPane);
        return listPanel;
    }

    private JPanel createTablePanel() {
        tablePanel = new JPanel(new FlowLayout(FlowLayout.RIGHT, 0, 10));
        tablePanel.setPreferredSize(new Dimension(575, 560));

    // Delete Button
        deleteButton = new JButton("Delete");
        deleteButton.setFocusable(false);

    // JTable with DefaultTableModel
        model = new DefaultTableModel(columnNames, 0) {
            @Override
            public boolean isCellEditable(int row, int column) {
                return false;
            }
        };

        table = new JTable(model);
        rightRenderer = new DefaultTableCellRenderer();
        rightRenderer.setHorizontalAlignment(SwingConstants.RIGHT);
        table.getColumnModel().getColumn(2).setCellRenderer(rightRenderer);
        table.setPreferredScrollableViewportSize(new Dimension(570, 491));
        table.setSelectionMode(DefaultListSelectionModel.SINGLE_SELECTION);
        tScrollPane = new JScrollPane(table);

        tablePanel.add(deleteButton);
        tablePanel.add(Box.createRigidArea(new Dimension(15, 20)));
        tablePanel.add(tScrollPane);
        return tablePanel;
    }

    public int getSelectedIndex() {
        return list.getSelectedIndex();
    }

    public int getSelectedRow() {
        return table.getSelectedRow();
    }

    public void loadCountryNames(Vector listData) {
        list.setListData(listData);
    }

    public void fillTableData(Vector cityData) {
        model.addRow(cityData);
    }

    public void clearTable() {
        model.getDataVector().removeAllElements();
    }

    public void removeRow(int row) {
        model.removeRow(row);
    }

    @Override
    public void addMouseListener(MouseListener mouseListener) {
        list.addMouseListener(mouseListener);
    }

    public void addDeleteButtonListener(ActionListener actionlistener) {
        deleteButton.addActionListener(actionlistener);
    }

}

package controller;

import java.awt.EventQueue;
import view.CitiesDialog;
import model.Country;
import model.City;
import view.MainWindow;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseEvent;
import java.awt.event.MouseListener;
import java.util.List;
import java.util.Vector;

public class Controller {

    private City city;
    private Country country;
    private MainWindow mainWindow;
    private CitiesDialog citiesDialog;
    private Country selectedCountry;
    List<Country> countries;
    List<City> cities;

    public Controller() {
    }

    public Controller(City city, Country country, MainWindow mainWindow) {
        this.city = city;
        this.country = country;
        this.mainWindow = mainWindow;
        selectedCountry = null;
        mainWindow.addMenuActionListener(new MenuActionListener());
    }

    private class MenuActionListener implements ActionListener {

        @Override
        public void actionPerformed(ActionEvent e) {
            if (e.getSource() == mainWindow.getExitMenu()) {
                System.exit(0);
            } else if (e.getSource() == mainWindow.getLoadCitiesMenu()) {
                EventQueue.invokeLater(new Runnable() {
                    @Override
                    public void run() {
                        citiesDialog = new CitiesDialog();
                        citiesDialog.addMouseListener(new ListMouseListener());
                        citiesDialog.addDeleteButtonListener(new DeleteButtonActionListener());

                        countries = country.getCountries();
                        Vector vector = new Vector();

                        for (Country ct : countries) {
                            vector.add(ct.getName());
                        }
                        citiesDialog.loadCountryNames(vector);
                    }
                });

            }
        }
    }

    private class DeleteButtonActionListener implements ActionListener {

        @Override
        public void actionPerformed(ActionEvent e) {
            int selectedRow = citiesDialog.getSelectedRow();

            if ((selectedCountry != null) && (selectedRow >= 0)) {
                city.deleteCity(cities.get(selectedRow));
                citiesDialog.removeRow(selectedRow);
            }
        }
    }

    private class ListMouseListener implements MouseListener {

        @Override
        public void mouseClicked(MouseEvent e) {
            citiesDialog.clearTable();
            selectedCountry = countries.get(citiesDialog.getSelectedIndex());
            cities = city.getCities(selectedCountry);

            for (City c : cities) {
                Vector row = new Vector();
                row.addElement(c.getName());
                row.addElement(c.getDistrict());
                row.addElement(String.format("%, d", c.getPopulation()));
                citiesDialog.fillTableData(row);
            }
        }

        @Override
        public void mousePressed(MouseEvent e) {
        }

        @Override
        public void mouseReleased(MouseEvent e) {
        }

        @Override
        public void mouseEntered(MouseEvent e) {
        }

        @Override
        public void mouseExited(MouseEvent e) {
        }
    }
}

package controller;

import model.Country;
import model.City;
import view.MainWindow;

public class SwingWorldJDBC {

    public static void main(String[] args) {
        City city = new City();
        Country country = new Country();
        MainWindow mw = new MainWindow();
        Controller controller = new Controller(city, country, mw);
    }
}