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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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
Steve BinkCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.