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?

Natty

Unrelated to the question, your examples break the Java naming conventions by using double underscores and class names like tbl_Employee. The Java naming conventions are really reasonable and very widely used. You really shouldn't break them without a serious reason. – Jetty

@ Sergey Tachenov: Completely agree, i used this wrong naming convention for sake of example. But it raise one question that how should i name class which define constants for a table because as u said table name convention doesn't apply to class name convention. – Natty

Don't do this. You are using the database wrong if you feel like you need to solve this. – Mccarter

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.

Bracteate

We can not deny fact that on renaming column name it really tedious and non productive task to replicate same change in java code. So We can not do away with constants we have to have some solution to avoid multiplier effect of db changes. As i specified one benefit of constant even if we change constant name itself is refractor facility in eclipse. My main concern is what should be best approach for organizing constants global, class or interface or enum per table or something else. – Natty

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).

Surplus

I am using Hibernate annotation to map Entity class with table. But there can be some database queries which can't be solved by hibernate so same is implemented using sql query rather than hibernate in Dao layer. – Natty

You can embed a standard SQL query in to the Hibernate configuration files as well. This means that any change to the DB would need a corresponding change to the configuration file, but this does allow you to have DB changes only affect one location (and doesn't require a recompile to work). Check the Hibernate documentation under Native SQL queries. – Surplus

(1). I am using hibernate annotation rather than hibernate mapping file. So change will be in multiple files (2).There are some business queries which can not be answered by hibernate pojo so i have to write code out of context of hibernate for same in DAO layer. Thanks – Natty

Hibernate will also provide you with the raw data, if you don't specify a specific POJO object mapping in the query. So you can write HQL that has a select statement, and the Iterator (or List) that comes back is a a list of Object[] which holds the data returned from the query. – Surplus

Annotations are a trade-off between easy to read code and more difficult maintenance of the code. It's not an easy call - do which ever works best for you. – Surplus

Answer 3

You could create an interface that defines the constants.

Here's a good example in Android. Look for the DataColumns interface.

Ejaculate

Looks good, but i guess be it interface or class for both it is not considered good to use them for sole purpose of defining constants only. But definitely this link reflects what practice is being followed so can be useful. But constants for table name is required which is not covered in this code. – Natty

@Ejaculate Link no longer works – Kendrickkendricks

Answer 4

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.

Lowery

Daniel DiPaolo: u use one global file or one class for each table? – Natty

One class per table, though it's not strictly tied to the table name, that's also a constant within the class :) – Lowery

Answer 5

When I have meta data about a database, I would store this in the database also. This is how I have seen other system run, if not initially then eventually. To ensure this data is maintained you could check against the schema of the data base.

Noemi

Answer 6

Lombok's @FieldNameConstants is what you're looking for.

Spue