Country, city, and address tables for a customer

Question

I need to create a customer and I have foreign keys for each table. Below is an image displaying the relationships of the tables when I want to insert a new customer. Then below the diagram I list out 3 methods that I would like to know any suggestions on how to best remove all the duplication. The main obstacle I encounter is that each method has a different amount of PrepareStatement and params to set (i.e. setString, setLong, etc). I want to use this example to learn how to do it to clean up the rest of my code. Please note that I am not allowed to use any external libraries or frameworks for this project outside of the standard java libraries.

private void handleSaveButton() throws SQLException {
    countryId = getCountryId();
    cityId = getCityId();
    addressId = getAddressId();
}

/**
 *
 * @return @throws SQLException
 */
private long getCountryId() throws SQLException {
    getConnection();
    param = toTitleCase(countryTextField.getText());
    System.out.println("country param: " + param);
    String query = "insert into country (country) values (?)";
    ps = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
    ps.setString(1, param);
    try {
        ps.executeUpdate();
        rs = ps.getGeneratedKeys();
        rs.next();
        primaryKey = rs.getLong(1);
        System.out.println("inserted country id: " + primaryKey);
    } catch (SQLIntegrityConstraintViolationException e) {
        ps = con.prepareStatement("select countryId from country where country = ?");
        ps.setString(1, param);
        rs = ps.executeQuery();
        rs.next();
        primaryKey = (long) rs.getInt(1);
        System.out.println("existing country id: " + primaryKey);
    } finally {
        closeConnection(ps);
    }
    return primaryKey;
}

/**
 *
 * @return @throws SQLException
 */
private long getCityId() throws SQLException {
    getConnection();
    param = toTitleCase(cityTextField.getText());
    System.out.println("city param: " + param);
    String query = "insert into city (city, countryId) values (?, ?)";
    ps = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
    ps.setString(1, param);
    ps.setLong(2, countryId);
    try {
        ps.executeUpdate();
        rs = ps.getGeneratedKeys();
        rs.next();
        primaryKey = rs.getLong(1);
        System.out.println("inserted city id: " + primaryKey);
    } catch (SQLIntegrityConstraintViolationException e) {
        ps = con.prepareStatement("select cityId from city where city = ?");
        ps.setString(1, param);
        rs = ps.executeQuery();
        rs.next();
        primaryKey = (long) rs.getInt(1);
        System.out.println("existing city id: " + primaryKey);
    } finally {
        closeConnection(ps);
    }
    return primaryKey;
}

/**
 *
 * @return @throws SQLException
 */
private long getAddressId() throws SQLException {
    getConnection();
    param = toTitleCase(addressTextField.getText());
    System.out.println("address param: " + param);
    String query = "insert into address (address, cityId, postalCode, phone) values (?,?,?,?)";
    ps = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
    ps.setString(1, param);
    ps.setLong(2, cityId);
    ps.setString(3, postalTextField.getText());
    ps.setString(4, phoneTextField.getText());
    try {
        ps.executeUpdate();
        rs = ps.getGeneratedKeys();
        rs.next();
        primaryKey = rs.getLong(1);
        System.out.println("inserted address id: " + primaryKey);
    } catch (SQLIntegrityConstraintViolationException e) {
        ps = con.prepareStatement("select addressId from address where address = ? and cityId = ?");
        ps.setString(1, param);
        ps.setLong(2, cityId);
        rs = ps.executeQuery();
        rs.next();
        primaryKey = (long) rs.getInt(1);
        System.out.println("existing address id: " + primaryKey);
    } finally {
        closeConnection(ps);
    }
    return primaryKey;
}

Answer 1

besides removal of duplication, there are other issues, more important IMO, that need addressing:

1. Using Exceptions as control flow is considered an anti pattern. There are several reasons for this, they are outlined here. in your case, an added reason is that the insert is only needed for the first time for every key. the proper way to do it is to try to select and when you get no data, do an insert. this way, the decision to insert is done in an if statement, and after the 2nd time, there will be no redundant access to the DB.

2. naming convention: in Java (as well as several other languages) there is a naming convention for method that dictates that prefix getXxx() means the method retrieves information and does not modify it. your methods break the principle of least surprise. if the caller of these methods is not aware of the implementation, it will not expect changes to DB after calling them.