Java enum in mysql

Question

I would like to put in and put out products from my database. Color of this products are as enums

public enum Color {
WHITE("#FFFFFF"), BLACK("#000000"), GREEN("#008000"), RED("#FF0000"), BLUE("#0000FF"), YELLOW("#FFFF00"), ORANGE("#FFA500"), PURPLE("#800080"),
GRAY("#808080");

private String color;

Color (String color){
    this.color = color;
}

public String getHex() {
    return color;
}

}

Here i have relation with my databases. There should be Color color not as String color. I try both options. Any suggestion how to fix it?

public List<Product> getAllProducts( ){
    List<Product> products = new LinkedList<Product>();
    Statement statement;
    try {
        statement = connection.createStatement();
        query = "select * from " + tableName;
        ResultSet resultSet = statement.executeQuery(query);
        /* (name, price, weight, color, product count, size, material */
        while(resultSet.next()) {
            Long id = resultSet.getLong("id");
            String name = resultSet.getString("name");
            Float price = resultSet.getFloat("price");
            Float weight = resultSet.getFloat("weight");
            String color = resultSet.getString(("color"));
            Integer productCount = resultSet.getInt("productcount");
            String size = resultSet.getString("size");
            String material = resultSet.getString("material");

            Product product = new Product(id, name, price, weight, color, productCount, size, material);
            products.add(product);
        }

    }
    catch (SQLException e) {
        e.printStackTrace();
    }
}

What exactly does not work? Do you get an exception? If you store colors as strings in your DB, you could deserialize to a Color object by doing `Color color = Color.valueOf(resultSet.getString(("color")));` — Simon, Mar 05 '20 at 18:46
Thank you Simon, i didnt start it but idea marked it and im sure there is mistake, but propably you resolved it and you show me the way for it. — Patient Zero, Mar 05 '20 at 18:52

Jason · Accepted Answer · 2020-03-05T19:03:07.807

In this kind of situation you can simply do;

Color color = Color.valueOf(resultSet.getString("color"));

It should be noted that you should surround with a try-catch for the IllegalStateException if no enum element exists for the String.

On an unrelated note

Wrap your Statement in a try-with-resources or you're going to get resource leaks.

try (Statement statement = connection.createStatement()) {

}

Consider using a PreparedStatement.

Always use a PreparedStatement instead of a Statement when you can (should be always). This is a helpful link on why.

Dont explicitly modify the query

Don't insert values into the query. Instead, modify the PreparedStatement object. SQL injection is currently possible. Your query would look like this.

String query = "SELECT * FROM ?";

This will allow you to replace ? with the table name by doing the following.

try (PreparedStatement statement = connection.prepareStatement(query)) {
    statement.setString(1, tableName);
}

score 0 · Answer 2 · answered Mar 05 '20 at 21:38

One of the aproaches is using the name value of the Enum, and using Color.valueOf(resultSet.getString("color")), as explained in other answer, but there are other possibilities.

A more efficient way would be to use the ordinal of the enum, and store it as an int. To store the value you need to call the ordinal() method on the enum. It returns a zero-based index, so in your example, WHITE.ordinal() would return 0, and GRAY.ordinal() would return 8.

To read the value from the database you could do:

Color.values()[resultSet.getInt("color")]

(see: https://stackoverflow.com/a/609866/11751648)

Note that efficiency comes at a price: if you need to modify your enum, you only can add values at the end, otherwise if the order is altered, the ordinal values would change and your data would be incoherent.

Java enum in mysql

2 Answers2