It's week #3 of my Java self-education. 

I have completed [assignment from Stanford CS108](http://web.stanford.edu/class/cs108/handouts141/20HW3DBSudoku.pdf) on mysql and swing (It's part B). And it would be wonderful if someone could point out my week spots in code and overall design. 

I have uploaded code on [github.](https://github.com/AndrewBaliushin/Stanford-CS108-Object-Oriented-System-Design/tree/master/HW3Sudoku/hw3DBSudoku/src/databaseexercise)   

### Task:
In this part of the assignment you will create a GUI-based (Graphical User Interface) application which will 
allow a user to access the example metropolises database from the Database handout.
The application will provide options allowing the user to either search for metropolises matching particular 
criteria or to add a new metropolis to the database. The application should include a JTable which displays 
information gathered from the metropolises database.
![enter image description here][1]


I ended up with this:
![enter image description here][2]

[1]: https://i.sstatic.net/Ibk8M.png
  [2]: https://i.sstatic.net/UYaCE.png

### JMetropolisViewer.java

    package databaseexercise;
    
    import java.awt.BorderLayout;
    import java.awt.FlowLayout;
    import java.awt.event.ActionEvent;
    import java.awt.event.ActionListener;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    import javax.swing.*;
    import javax.swing.border.TitledBorder;
    import javax.swing.table.AbstractTableModel;
    
    public class JMetropolisViewer extends JFrame {
    	
    	public static final String APP_TITLE = "Metropolis Viewer";
    	public static final String METROPOLIS = "Metropolis";
    	public static final String POPULATION = "Population";
    	public static final String CONTINENT = "Continent";
    	
    	//index[0] must be "larger than"
    	private static final String[] optionsForPopulation = {"Population larger than", "Population less than"};
    	//index[0] must be "partial match" 
    	private static final String[] optionsForMatch = {"Partial match", "Exact match"};
    	
    	private List<City> metropolisList = new ArrayList<City>();
    	private Database db;
    	private MetropolisTableModel tableModel;
    	
    	private JTextField cityNameInput;
    	private JTextField continentInput;
    	private JTextField populationInput;
    	
    	private JButton addButton;
    	private JButton searchButton;
    	
    	private JComboBox<String> populationSearchOptions;
    	private JComboBox<String> matchSearchOptions;
    	
    	public JMetropolisViewer() {
    		super(APP_TITLE);
    		
    		db = new Database(metropolisList);
    		db.readAll();
    		
    		setLayout(new BorderLayout(4, 4));
    		add(makeInputFieldsPanel(), BorderLayout.NORTH);
    		tableModel = new MetropolisTableModel(metropolisList);
    		add(makeResultTable(tableModel), BorderLayout.CENTER);
    		
    		addListners();
    		
    		pack();
    		setVisible(true);
    	}
    
    	public static void main(String[] args) {
    		try {
    			UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());
    		} catch (Exception ignored) { }
    		
    		JMetropolisViewer metropolisViewer = new JMetropolisViewer();	
    		
    	}
    	
    	private JPanel makeInputFieldsPanel() {
    		JPanel inputFieldsPanel = new JPanel();
    		inputFieldsPanel.setLayout(new FlowLayout());
    		
    		JLabel cityL = new JLabel(METROPOLIS + ":");
    	    inputFieldsPanel.add(cityL);
    	    cityNameInput = new JTextField(10);
    	    inputFieldsPanel.add(cityNameInput);
    	    
    	    JLabel contL = new JLabel(CONTINENT + ":");
    	    inputFieldsPanel.add(contL);
    	    continentInput = new JTextField(10);
    	    inputFieldsPanel.add(continentInput);
    	    
    	    JLabel populationL = new JLabel(POPULATION + ":");
    	    inputFieldsPanel.add(populationL);
    	    populationInput = new JTextField(10);
    	    inputFieldsPanel.add(populationInput);
    	    
    	    inputFieldsPanel.add(makeControlPanel());
    	    
    	    return inputFieldsPanel;
    	}
    	
    	private JPanel makeControlPanel() {
    		JPanel contolPanel = new JPanel();
    		contolPanel.setLayout(new BoxLayout(contolPanel, BoxLayout.X_AXIS));
    		
    		addButton = new JButton("Add");
    		contolPanel.add(addButton);
    		
    		searchButton = new JButton("Search");
    		contolPanel.add(searchButton);
    		
    		contolPanel.add(makeSearchOptionBox());
    		
    		return contolPanel;
    	}
    	
    	private JPanel makeSearchOptionBox() {
    		JPanel optionsPanel = new JPanel();
    		optionsPanel.setLayout(new BoxLayout(optionsPanel, BoxLayout.PAGE_AXIS));
    		optionsPanel.setBorder(new TitledBorder("Search options"));
    		
    		populationSearchOptions = new JComboBox<String>(optionsForPopulation);
    		populationSearchOptions.setSelectedIndex(0);
    		optionsPanel.add(populationSearchOptions);
    		
    
    		matchSearchOptions = new JComboBox<String>(optionsForMatch);
    		matchSearchOptions.setSelectedIndex(0);
    		optionsPanel.add(matchSearchOptions);
    		
    		return optionsPanel;		
    	}
    	
    	private JScrollPane makeResultTable(AbstractTableModel model) {
    		JTable resultTable = new JTable(model);
    		JScrollPane scrollPane = new JScrollPane(resultTable);
    		
    		return scrollPane;
    	}
    	
    	private void addListners() {
    		addButton.addActionListener(new ActionListener() {
    			@Override
    			public void actionPerformed(ActionEvent arg0) {
    				try {
    					db.add(createCity());
    					tableModel.fireTableDataChanged();
    				} catch (Exception e) {
    					System.out.println(e);
    					e.printStackTrace();
    				}
    			}
    		});
    		
    		searchButton.addActionListener(new ActionListener() {
    			@Override
    			public void actionPerformed(ActionEvent arg0) {
    				try {
    					db.search(cityNameInput.getText(),
    							continentInput.getText(),
    							parsePopulationInput(),
    							(matchSearchOptions.getSelectedIndex() == 0),
    							(populationSearchOptions.getSelectedIndex() == 0));
    					tableModel.fireTableDataChanged();
    				} catch (Exception e) {
    					System.out.println(e);
    					e.printStackTrace();
    				}
    			}
    		});
    	}
    
    	private City createCity() {
    		String city = cityNameInput.getText();
    		String continent = continentInput.getText();
    		int population = parsePopulationInput();
    		if (city.length() != 0 && continent.length() != 0) {
    			return new City(city,continent, population);
    		} else {
    			return null;
    		}
    	}
    	
    	private int parsePopulationInput() {
    		String input = populationInput.getText();
    		if(input.length() == 0) {
    			return 0;
    		} else {
    			return Integer.parseInt(input);
    		}
    	}
    }

### Database.java

    package databaseexercise;
    
    import java.sql.*;
    import java.util.ArrayList;
    import java.util.Iterator;
    import java.util.List;
    
    public class Database {
    	
    	private static final String METROPOLIS_FIELD = MyDBInfo.METROPOLIS_FIELD;
    	private static final String CONTINENT_FIELD = MyDBInfo.CONTINENT_FIELD;
    	private static final String POPULATION_FIELD = MyDBInfo.POPULATION_FIELD;
    
    	private Connection connection = null;
    	private Statement statement = null;
    	private PreparedStatement preparedStatement = null;
    	private ResultSet resultSet = null;
    	
    	private List<City> citiesList;
    	
    	public Database(List<City> citiesList) {
    		this.citiesList = citiesList;
    	}
    
    	private void readDataBase(String SQL) throws SQLException {
    	    try {
    	      connect();
    	      statement = connection.createStatement();
    	      resultSet = statement
    	          .executeQuery(SQL);
    	      
    	      citiesList.clear();
    	      
    	      while (resultSet.next())
    	      {
    	    	 citiesList.add(new City(resultSet.getString(1), resultSet.getString(2), resultSet.getInt(3)));
    	      } 
    	      
    	    } catch (Exception e) {
    	    	System.out.println(e);
    	    	System.err.println("SQL: " + SQL);
    	    } finally {
    	      connection.close();
    	    }
    
    	  }
    	
    	public void readAll() {
    		try {
    			readDataBase("SELECT * FROM " + MyDBInfo.TABLE_NAME);
    		} catch (SQLException e) {
    			System.out.println(e);
    			e.printStackTrace();
    		}
    	}
    	
    	public void search(String city, String continent, int population, boolean partialMatch,
    			boolean popLargerThan) {
    		if (city.length() == 0 && continent.length() ==0 && population == 0) {
    			readAll();
    			return;
    		}
    		
    		StringBuilder sbSql = new StringBuilder();
    		
    		sbSql.append("SELECT * FROM ");
    		sbSql.append(MyDBInfo.TABLE_NAME);
    		sbSql.append(" WHERE ");
    		
    		List<String> whereStatments = new ArrayList<String>();
    		
    		if (city.length() > 0 && partialMatch) {
    			whereStatments.add(METROPOLIS_FIELD + " LIKE '%" + city + "%'");
    		} else if (city.length() > 0) {
    			whereStatments.add(METROPOLIS_FIELD + " = '" + city + "'");
    		}
    		
    		if (continent.length() > 0 && partialMatch) {
    			whereStatments.add(CONTINENT_FIELD + " LIKE '%" + continent + "%'");
    		} else if (continent.length() > 0) {
    			whereStatments.add(CONTINENT_FIELD + " = '" + continent + "'");
    		}
    		
    		if (population != 0 && popLargerThan) {
    			whereStatments.add(POPULATION_FIELD + " > " + population);
    		} else if (population != 0) {
    			whereStatments.add(POPULATION_FIELD + " < " + population);
    		}
    		
    		Iterator<String> iter = whereStatments.iterator();
    		boolean first = true;
    		while (iter.hasNext()) {
    			if (!first) {
    				sbSql.append(" AND ");
    			} else {
    				first = false;
    			}
    			sbSql.append(iter.next());			
    		}
    		
    		
    		try {
    			readDataBase(sbSql.toString());
    		} catch (SQLException e) {
    			System.out.println("SQL: " + sbSql.toString());
    			e.printStackTrace();
    		}
    	}
    	
    	public void add(City city) throws SQLException {
    		String sql = "INSERT INTO " + MyDBInfo.TABLE_NAME
    				+ " (" + METROPOLIS_FIELD +" , " + CONTINENT_FIELD + ", "
    				+ POPULATION_FIELD + ") " + "VALUES (?, ? , ?)";
    		
    		if (city == null) {
    			System.out.println("City is null");
    			return;
    		}
    
    		try {
    			connect();
    			preparedStatement = connection.prepareStatement(sql);
    			preparedStatement.setString(1, city.getCityName());
    			preparedStatement.setString(2, city.getCityContinent());
    			preparedStatement.setInt(3, city.getPopulation());
    			preparedStatement.executeUpdate();
    			citiesList.add(city);
    		} catch (Exception e) {
    			System.out.println(e);
    			e.printStackTrace();
    		} finally {
    			connection.close();
    			preparedStatement.close();
    		}
    
    	}
    
    	private void connect() throws Exception {
    		Class.forName("com.mysql.jdbc.Driver");
    	    connection = DriverManager.
    	    		  getConnection(MyDBInfo.MYSQL_DATABASE_SERVER + MyDBInfo.MYSQL_DATABASE_NAME,
    	    		  MyDBInfo.MYSQL_USERNAME, MyDBInfo.MYSQL_PASSWORD);
    	}
    }

### MetropolisTableModel.java

    package databaseexercise;
    
    import java.util.List;
    import javax.swing.table.AbstractTableModel;
    
    public class MetropolisTableModel extends AbstractTableModel {
    	
    	private List<City> cityList;
    	
    	MetropolisTableModel(List<City> cityList) {
    		this.cityList = cityList;
    	}
    	
    	private String[] colNames = {JMetropolisViewer.METROPOLIS,
    			JMetropolisViewer.CONTINENT, JMetropolisViewer.POPULATION};
    	
    	@Override
    	public String getColumnName(int column) {
    		return colNames[column];
    	}
    
    	@Override
    	public int getColumnCount() {
    		return colNames.length;
    	}
    	
    	@Override
    	public int getRowCount() {
    		return cityList.size();
    	}
    
    	@Override
    	public Object getValueAt(int row, int column) {
    		if(row < 0 || row >= cityList.size()) return null;
            City obj = cityList.get(row);
            switch(column)
            {
                case 0: return obj.getCityName();
                case 1: return obj.getCityContinent();
                case 2: return obj.getPopulation();
                default: return null;
            }
    	}
    }

### City.java

    package databaseexercise;
    
    public class City {
    	private String cityName;
    	private String cityContinent;
    	private int population;
    	
    	public City(String cityName, String cityContinent, int population) {
    		this.cityName = cityName;
    		this.cityContinent = cityContinent;
    		this.population = population;
    	}
    	
    	public String getCityName() {
    		return cityName;
    	}
    	public String getCityContinent() {
    		return cityContinent;
    	}
    	public int getPopulation() {
    		return population;
    	}
    	
    }