Hi All,
I am using Oracle 9i and i am having employee table as follows,


CREATE TABLE EMPLOYEE(
ID NUMBER NOT NULL,
NAME VARCHAR2 (50) NOT NULL,
DEPARTMENT_ID NUMBER NOT NULL,

CONSTRAINT EMPLOYEE_PK PRIMARY KEY (ID),
CONSTRAINT EMPLOYEE_UQ UNIQUE (NAME),
CONSTRAINT DEPARTMENT_ID_FK FOREIGN KEY (ROLE_ID) REFERENCES DEPARTMENT(ID)
);

as all of you can see here the 'id' is the primary key and i have added unique constraint on 'name' field such that wile inserting the duplicate name it raises an unique constraint violation error. but this checking for the uniqueness is 'case sensitive' i.e if i tried to insert name say 'xyz' and if 'xyz' already exist it will raise an error but if i tried inserting 'XYZ' (xyz in caps) then it allows me to insert without an error.
I wanted to put a filter for this case insensitive checking, surely i can add some code and get this functionality done.
I am using Hibernate, so can anybody help me configure hibernate such that i can get this case insensitive cheking done withouth writting any java code, may be better question here is, does hibernate allows this kind of checks?.
Can i overload "equals" method for the entity name in my persistent class which is used in hibernate mappings, will this be helpfull in ablove mentioned scenario?
If i am able to write a HQL query then also i will be happy, i am just trying to avoid extra coding here.

Thanx in advance.
Regards,
Yuwaraj

Yuwaraj,

If you want to enforce a case-insentitive constraint I recommend that you choose to store only lower- or uppercase values in the name column. You could easily achieve this by changing the setter for the name attribute on your employee table. You can also change your constraint:

CREATE UNIQUE INDEX EMPLOYEE_UQ
ON EMPLOYEE (
LOWER("NAME")
)

If you want to check yourself in a filter (why??) then you would query the database before inserting/updating the employee

select count(*) from Employee e where upper(e.name) = :name
query.setParameter("name",name.toUpperCase());
int count = ((Integer)query.uniqueResult()).intValue();

and raise an error if the count != 0.

Hi oharsta,
Thanx for replying so early, yes i am agree with you that i can do the case insensitive checks by converting the employee name into upper/ lower case but my requirment is, i am not allowed to change the input from the user i.e. if employee creates his profile with say 'xYz' then while displaying i have to show 'xYz' only so beacause of this requirment i can't convert the name to upper or lower case.

It is not clear what your requirement is...whatever I could understand it is as follows:

-your current application( limiting our discussion to NAME property) is case-insensitive.
-you want to make it case-sensitive.

If my above assessment is not correct let me know:

So what I feel overriding equals method is the best option for you...and it certainly can be done using Hibernate.

You cannot use other two options:
-using LOWER, UPPER in DDL since that will tie you down to using either LOWER or UPPER only whereas your requirement is it may 'xyz' or 'XYZ' or 'xYz' e.t.c
-using setter: this of no use when you are comparing equality for objects(for example when tow User are equal or not)

Overriding equals() is pretty simple for your case

Bibroy,
Let me explain you my requirments first,
-On the employee information page whatever the user has provided as a employee name say 'xYz' , i need to retain the same, such that while displaying the employee name i should show him 'xYz' only i.e. name is case sensitive here.
- But provided this i should not allow any other employee to register with name 'xyz'.

I have added unique constrain on name but is case sensitive such that if i adds an employee 'xyz' it allows me, so i need some kind of filter here.

As you said in your reply(thanx for that) i can't use just lower/upper on name field.

Yes i was reading about overriding equals and i think it will help me but the only thing i do not understand how will Hibernate call this equals against all the employees which are already in the database. Here Hibernate need to maintain list of users to check against the user getting added or he need to fetch all the users at run time.

can you please help me configure my application such that i can override equals and hibernate will be able to call that method while inserting the employee.

I think this is the solution i am looking for.
Thanx in advance.

I feel you have two requirement:
1. implementing case-sensitivity on the database side(during INSERT)
2. implementing case sensitivity in the java application(after data retrieval)

As for #1, you should read relevant docs of your Database-server as to how to implement case-sensitivity...you may write even an INSERT trigger for this...or there might some easier way

As for #2, you will be able to get numerous tutorials(one is Sun Java Official Tutorial) as to how to implement equals..it is simple....even google serach may fetch you useful code

You need not worry as to how to hibernate takes care of equals method....when you check for object equality, Java Language automatically takes care of that....however remember database identity and Java equality(2 cases) are different.....you should read the book "Hibernate in Action" for this.