Integer Field or Enum field suitable for Spring Jdbc with MySQL

In couple of projects, the integer column is defined and the associated string value is handled in code.

example, status column defined as integer and values saved in DB is 1, 2, 3, 4, 5, 6 and 1-6 values mapped in code as 1-read, 2-write, 3-delete (String values)

In couple of projects, the enum column is defined and the associate enum value is handled in code.

One project uses Spring JdbcTemplate and native SQL queries. Other uses Spring, Hibernate and Hibernate queries

Of the above 2, which one is the best, efficient and non-error prone approach.

Please let me know in detail.
Software ProgrammerAsked:
Who is Participating?
 
Steve BinkConnect With a Mentor Commented:
Not a Java developer, but I find I prefer to store the integer and map in code.  If the number of items is prone to change, I would create a lookup table for these values to handle the mapping, and use that table to build the code-based mapping variable or function.  If the options are not likely to change, then just a hard-coded version of the variable/function would likely suffice.

I find this better because I consider string comparisons less dependable than numeric comparisons, and the strings are generally only useful in user-land (labels and such).  But, I work in PHP, where life is just like that.

Ideally, enums are indicated only if your options are not expected to change at all.  If your enum options change, you'll have to deploy a structural change to your database - not something I would want to do regularly.  That also assumes you're OK with the limitations on changing enum columns.  Remember that reordering or removing options is bad-bad-bad, and the only "safe" change is appending an option to the end of the list.
0
 
Andrea BalassoFull Stack Java DeveloperCommented:
I think that there is not a best approach from the two you mention. The main concept is the enumerator. The data is stored on the db with an integer value and you use also a string associated value with it. This means that you can use an enum class to manage this situation.
I usually implement an enum like below when I need to use data as you.

public enum EnumeratorType {

        READ(1, "Read");

        private int machineName;

        private String displayName;

    EnumeratorType(int machineName, String displayName) {
        this.machineName = machineName;
        this.displayName = displayName;
    }

    public int getMachineName() {
        return machineName;
    }

    public void setMachineName(int machineName) {
        this.machineName = machineName;
    }

    public String getDisplayName() {
        return displayName;
    }

    public void setDisplayName(String displayName) {
        this.displayName = displayName;
    }

    public static EnumeratorType getByMachineName(int machineName) {
        for (EnumeratorType enumerator : Enumerator.values()) {
            if (enumerator.getMachineName() == machineName) {
                return enumerator;
            }
        }
        throw new RuntimeException("Enumerator not found for value = " + machineName);
    }
}

As you can see, in this class I put the integer field and also the string field.

In hibernate you can define the entity to manage the enum class. In the entity you have the db column with his getter and setter

@Column(name = "ENUMERATOR")
private int enumerator;

public void setEnumerator(Integer enumerator) {
      this.enumerator = enumerator;
}

public Integer getEnumerator() {
      return this.enumerator;
}

You can add two transient function to manage the column with the enum defined before

@Transient
private EnumeratorType getEnumeratorType() {
      return EnumeratorType.getByMachineName(getEnumerator());
}

@Transient
private void getEnumeratorType(EnumeratorType enumerator) {
      this.enumerator = enumerator.getMachineName();
}

In spring jdbctemplate, in your bean you define a variable Enumerator type.
When you read from db you db to do something like this :

      object.setEnumeratorType(EnumeratorType.getByMachineName(rs.getInt("userType")));

When you write to db you need to set the integer param with something like that :

      object.getEnumeratorType().getMachineName()
0
 
Software ProgrammerAuthor Commented:
Sorry we are not using Hibernate and we are just using JdbcTemplate and Native SQL queries. Please modify the code accordingly
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Andrea BalassoFull Stack Java DeveloperCommented:
Using the enumerator defined in my previous comment, the code to insert data with jdbctemlate can be this :

jdbcTemplate.update("INSERT INTO SomeTable(columnInteger) VALUES(?)", object.getEnumeratorType().getMachineName());

To read the data from db, you can define a bean with a field type that is EnumeratorType. Also you can define a custom row mapper to map the query result to the bean. After that, you can use the jdbctemplate function to execute the select. Here an example code :

public class SomeTableBeanRowMapper implements RowMapper {
                  
                  public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                        SomeTableBean someTableBean = new SomeTableBean ();
                        someTableBean.setEnumeratorType(EnumeratorTypegetByMachineName(rs.getInt("ENUMERATOR"))
                        return someTableBean ;
                  }
              }

String sql = "SELECT * FROM SomeTable where id = ?";
             
              SomeTableBean someTableBean = (SomeTableBean)getJdbcTemplate().queryForObject(
                  sql, new Object[] { id}, new SomeTableBeanRowMapper());
0
 
Software ProgrammerAuthor Commented:
Out of the 2 which one you prefer...Storing as enum as column in mysql or storing as integer and mapping as enum in code ? I'm asking best out one out of this 2....Let me know the drawbacks and issues of both..
0
 
ste5anSenior DeveloperCommented:
It depends on your architecture and what you use your database for.

As a database guy: I would not store it like this in the database, cause it violates 1NF. While you store an atomic value, you don't store the value of the correct domain. But this is for real enumerations only.

In couple of projects, the integer column is defined and the associated string value is handled in code.
When this is a correct description of your situation, then I would use a normal "lookup" table.

Just my 2¢.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.