java 1.5: Best practice to keep constants for column name of db tables?

Question

Technology: - Java 1.5 or 1.6 - Hibernate 3.4

To avoid update of column name on multiple places on change of column name or tablename, i want to have a constant file for same.

I have following queries?

• One possible solution is to maintain one global file which stores constants for column names of all of tables in database. like

  class DbConstants
  {
          public static final String EMPLOYEE__PERFORMANCE_DESC="performance_desc";        
  } 
  

In above case employees is name of table and performance_desc is name of column name. So kind of tablename__columnname format is followed for naming a constant to avoid collision between two constants of two different tables if both have have column name.

One problem with this approach i see is that as database grows no of constants in this file will grow to thousands which is difficult to manage. Other problem is if table name is changed, i have to change prefix table name for all of tables.

• Suppose if i change name of column in above example from performance_desc to achievements_desc. In this case it is very likely that i will like to change constant also i.e from EMPLOYEE__PERFORMANCE_DESC to EMPLOYEE__ACHIEVEMENT_DESC. Since in this case i needed to change both column name and constant name i don't see much use of using constant instead of column name directly in my code although there is one benefit that on change of constant name i can use refraction to reflect constant name name change wherever referenced. It seems either there is not much use of using constants or i am using it wrong way.

• In project code i have seem people defining one class for each table columns list to define constants as shown below.

  public class tbl_Employee
  {
          public static final PERFORMANCE_DESC=performance_desc;
  }    
  

this can solve some of issues with global file like table name change will lead to class name change only. One major issue with this is that i am using class for sole purpose of defining constants which is not good coding practice.

• Read some where about Enum with value string rather than int not sure is it available in java 1.5 or 1.6 and if its is advisable to use in given scenario.

• What is best practice for given defining db constants?

• Is it really useful to use db constants?

• If i use one class for each table like mentioned above, one problem i face is naming convention. What should be relation between name of table and corresponding class' name which define constants for columns of the table.

• Above cases covers case for only column names not table name. I may like to use constant rather table name in code so what should be approach for defining constants for table names.

• It is often argued that table name and column names doesn't change much once product or related version is released. Changes in table name and column name happen mostly during development phase or feature enhancement (new version). Is it strong argument to avoid using constants for table name or column names?

  Please suggest how can i make my question more representable or what is i am missing that my questions are not voted?

Answer 1

It sounds like you're asking all the right questions - you want to make the code more maintainable, but realize that this could get unwieldy and end up making the code worse rather than better. Think of something like "Color.RED, Color.BLACK".

I've found that a reasonable amount of constants like this makes the code more readable. I don't think db column names belong in something like this, because

• they're not going to be changed often, or at least they shouldn't be

• there's enough of them that you'll end with a large list of constants, at which point people stop using them because it's harder to find the constant than to just look up the damn name in the db.

I've seen db files like this with thousands of constants, including custom queries, parts of queries, etc. etc (even a gem like public static final String COMMA=","; to take care of the possibility that the spelling of commas will change in the future). At this point they devolve into "use once" strings, and nobody dares to change them.

One other caveat about string constants - finals get compiled into your class as strings. So if you recompile the constant class, but not the class that uses the definition, it's possible to end up with the new definition not propagating.

Answer 2

Have you considered using an Entity Mapping Framework (like Hibernate)?

It can store all of the database table information (as well as all other DB specific information) in a configuration file. It also offers a separation layer between "hard" DB design and your application (which would make it easier to absorb changes to either).

Answer 3

On my current project, we are making heavy use of annotations for a lot of the DB-related metadata because we can't use a framework like Hibernate. For actual column constants, yes, we use the tried and true public static final String. And yes, it's fairly fragile.