Database assignment with MySQL and Swing

Question

I have completed this assignment from Stanford CS108 on MySQL and Swing (it's part B). It would be wonderful if someone could point out my weak spots in the code and overall design.

I have uploaded code on GitHub.

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.

I ended up with this:

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;
    }

}

Answer 1

getValueAt: Bounds check

if(row < 0 || row >= cityList.size()) return null; doesn't seem right to me. Here is how the DefaultTableModel handles it:

/**
 * [...]
 *
 * @exception  ArrayIndexOutOfBoundsException  if an invalid row or
 *               column was given
 */
public Object getValueAt(int row, int column) {
    Vector rowVector = (Vector)dataVector.elementAt(row);
    return rowVector.elementAt(column);
}

I think throwing a ArrayIndexOutOfBoundsException seems like a good idea. Alternatively, you could also store default values somewhere and return those (eg "missing", "n/a", "", "invalid column", etc).

Misc

• You print quite a lot in your classes. Try to handle this via return values or exceptions instead.
• In your finally block, you should check if connection is null before closing it.
• variables should have as small a scope as possible. For example, preparedStatement doesn't need to be at class level.
• you use prepared statements in add, why not use them in search as well.
• try not to throw or catch the generic Exception, but all the specific exceptions, so that the calling method can handle each as it wants to.

Answer 2

Code is well formatted and good style.

One thing I would avoid using is import * and import the packages you actually need. The latter is better practice, because it avoids import conflicts.