CODEWITHSUNDEEP
javasqldatabase
Serkan Arıkuşu
Serkan Arıkuşu

Reputation: 5619

Sharing constants between java and database

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.

Upvotes: 0

Views: 452

Answers (2)

NimChimpsky
NimChimpsky

Reputation: 47300

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)

Upvotes: 0

Vincent
Vincent

Reputation: 1035

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

Upvotes: 1

Related Questions