CODEWITHSUNDEEP
javadatabaseenums
eversor
eversor

Reputation: 3073

Using enums to **store** DB names of rows and columns is overkill?

Is it a good practice to use enums instead of literals to refer to names of tables and columns? or it is just overkill?

E.g.
Instead of doing this: 

executeQuery("SELECT name, age FROM people WHERE id = '23';");

Do something like this:

executeQuery("SELECT "+COLUMN.NAME.getName()+", "+COLUMN.AGE.getName()+
  "FROM "+ TABLE.PEOPLE.getName()+" WHERE "+COLUMN.ID.getName()+" = '23';");

Having this enums

public Enum COLUMN{
     NAME("name"),
     AGE("age"),
     ID("id");
     //...
}

And what about when the structure of the DB is not yet really well defined but your are commited to start developping a code using that not-yet-defined DB?

Upvotes: 4

Views: 2545

Answers (5)

eliani
eliani

Reputation: 59

YES enums can be a great way to organize your tables and to quickly enumerate over column names. for example in cassandra I do:

public class EventSchema
{
    public static enum EVENTS {
        TYPE_ID("type_id"),
        SLICE_START_TIME("slice_start_time"),
        EVENT_TIME("event_time"),
        EVENT_ID("event_id"),
        EVENT_PAYLOAD("event_payload");

        public final static String TABLE_NAME = "events";
        public final String COLUMN_NAME;
        EVENTS (String name) {COLUMN_NAME = name;}
        public String toString () {return COLUMN_NAME;}
        public static String[] names () {
            return Arrays.toString(values()).replaceAll("\\[|]", "").split(", ");
        }
    }

    public void addEvent (String keyspace, String typeId, long timeSliceStart, long timeSliceDuration, Event event) {
        Object[] values = {typeId, timeSliceStart, event.getTime(), event.getId(), event.getDetails()};
        Statement stmt = QueryBuilder.insertInto(EVENTS.TABLE_NAME).values(EVENTS.names(), values);
        getSession(keyspace).execute(stmt);

    }
}

Upvotes: 0

cHao
cHao

Reputation: 86504

I'd personally call this overkill. It's what's known as "soft coding", which is just as bad as hard coding when you take it too far. Possibly even worse, because its badness is more subtle -- and it's quite tempting to people who've heard over and over about the "evils" of hard coding.

Consider what happens when the age column's name changes. You either have an enum whose values no longer correspond to the columns' names (read: the code's lying to you), or you have to go through and change every reference to COLUMN.AGE to use COLUMN.NEW_NAME_FOR_AGE instead (read: you haven't saved yourself from having to edit code later, which is presumably the biggest reason you're considering this). And that's not even going into what happens if the structure changes. If the column moves to another table or something, your enums simply can't cope as is.

You should probably either have the columns' names as private static members of the class if you really really don't have any control over the DB, or use a stored procedure and let the DB handle the specifics of column names.

Oh, and have a talk with whoever asked you to code against a database that isn't even designed yet. You may at the very least be able to get some things set in stone. If not, then definitely go the stored proc route and let them worry about getting the column names right when things change.

Upvotes: 3

usr
usr

Reputation: 171178

Hm with this approach you have less work when you change your schema and you have more work when you write queries. It is as simple as that.

From my experience with the kind of apps I write (web-apps) I wouldn't do this. It is a judgement descision.

As a side-note you don't have to use enums. You can use any kind of constant you want.

Upvotes: 0

user149341
user149341

Reputation:

Using enums here is massive overkill, and it makes your code much harder to read and write.

If you're concerned about having too much code interacting directly with the database (which, if you're not, you probably should be), a much better approach is to use an ORM.

Upvotes: 0

Corey Ogburn
Corey Ogburn

Reputation: 24717

Use stored procedures instead. Let the database worry about all the SQL and let your code worry about what it's going to do with the data.

My usual approach is to have a class (usually static) dedicated to interacting with the database. Each stored procedure gets its own static method in the class.

Upvotes: 0

Related Questions