0

Assume that you have a STORE table having a varchar column STATUS that accepts values (OPEN,CLOSED)

On java side, and especially in your sqls I find myself writing queries like this

select * from store where status='OPEN'

Now this is not a written contract and is open to lots of bugs.

I want to manage cases where on db side a new status added or an existing one renamed and handle it on java side. For example if on STORE table if all statuses with OPEN are changed to OP, my sql code will fail.

PS:This question is in fact programming language and database server agnostic, but I tag it with java since I deal with it more.

Serkan Arıkuşu
  • 5,549
  • 5
  • 33
  • 50
  • Instead of putting hard coded as 'OPEN' take it in variable to compare the incoming values with existing record in database. – subodh Apr 10 '13 at 09:00

2 Answers2

1

Your need is a bit strange. Usually stuff don't just "happen" in database, and you don't have to cope with it. Rather, you decide to change things in your app(s) and both change your code and migrate your data.

This being said, if you want to ensure your data are consistent with a well-known set of values, you can create library tables. In your case:

create table STORE (status varchar(32))  -- your table
create table LIB_STORE_STATUS (status varchar(32) unique)  -- a lib table for statuses
alter table STORE add constraint FK_STORE_STATUS foreign key (status) references LIB_STORE_STATUS(status)  -- constraints the values in your STORE table

Then:

insert into STORE values ('A') -- fails
insert into LIB_STORE_STATUS values ('A')
insert into STORE values ('A') -- passes

With this, you just have to ensure your lib table is always in sync with your code (i.e. your enum names when using JPA's @Enumerated(EnumType.STRING) mapping strategy).

Vincent
  • 1,035
  • 6
  • 14
0

Use enums, you can map directrly to the enum instance name (not necessary to convert to the int ordinal)

But in this case I would have a boolean/bit column called open, and its possible values would be true or false.

(boolean is bit 0/1 in most DB's)

NimChimpsky
  • 46,453
  • 60
  • 198
  • 311
  • Thank you for your answer but the problem is not just about how to handle constants in java, in fact it is about synchronizing these constants with db column values. – Serkan Arıkuşu Apr 10 '13 at 09:57
  • @SerkanArıkuşu yeah I understood hence my suggestion to map directly to enum name (its more descriptive on db level), or in this specific case refactor to use a boolean in java mapped to a bit column on db. – NimChimpsky Apr 10 '13 at 10:13