Hibernate problem

[mallikarjun dontamsetti]

Hi i have a table which contain a column "skills" it has data like "java,struts,..". My problem is i have to search the skills. how can i achieve that. is there any way to tell hibernate search column separated by comma(,).

[Jayr Motta]

If you are using Criteria API use projections to select only the fields you want, get them in the result set (an array of objects) or use the bind feature of JPA to bind them with their mapped entity, thus, cast the object to a string and split the string using comma as the regex. But if you are using JP QL or HQL, list the fields you want after the select statement and do the same describe before with the result set.

[mallikarjun dontamsetti]

Thanks for reply, please give me some example it will be help full for me

[Vijitha Kumara]

mallikarjun dontamsetti wrote:Hi i have a table which contain a column "skills" it has data like "java,struts,..".

Do you mean for a one row you have this multiple values for that column? Then you should avoid multi-valued columns in the first place.

[mallikarjun dontamsetti]

Vijitha Kumara wrote:

mallikarjun dontamsetti wrote:Hi i have a table which contain a column "skills" it has data like "java,struts,..".

Do you mean for a one row you have this multiple values for that column?

I have number of rows in my table i that table one column is allowed to enter a string value separated by comma like java,struts,hibernate.

Then you should avoid multi-valued columns in the first place.

i don't get you ..

[Ankit Nagpal]

mallikarjun dontamsetti wrote:
i don't get you ..

It means that your db tables are not designed correctly. Implement many-to-many mapping for skills and your existing table. For e.g. (Assuming your existing table name is employee):

Employee: Employee ID (PK), Name, ....
Skils: Skill ID (PK), Skill Name, ...
Employee_Skills: Employee ID (FK to Employee), Skill ID (FK to Skills), and Employee ID + Skill ID would be the composite key for this table.

Map these tables in your hibernate based application and I am sure it would be a lot easier to search the skills now.

[mallikarjun dontamsetti]

Is there any other chance, without changing DB. If skills are more than 10 for each record(Employee), then the table has 10 records for each one. and we have to search each one when it comes to search. if there are more than 10000 employee the data base table has 10000X10=100000 like it is going to be difficult. so please help me in this.

[Ankit Nagpal]

mallikarjun dontamsetti wrote:if there are more than 10000 employee the data base table has 10000X10=100000 like it is going to be difficult. so please help me in this.

Well, that's what the DBMS is for. It's not going to be difficult for
a) the application i.e. maintainability
b) the developer i.e. ease of development and
c) the DBMS i.e. isn't it designed to effectively store this much?

it's still better as a single query can fetch you the desired result rather than selecting the whole data and applying the logic for search. Remember to use appropriate joins in the query.

E.g. if you want to search all employees with java skill-set, you would use the following sql query:

select emp.name from skills sk join employee_skills emp_sk on sk.id = emp_sk.skillid join employee emp on emp.id = emp_sk.employeeid where sk.name = 'Java'

No business logic required at all.

[Tatiana Vorobeva]

As I understand, he want to use one column for all skills and to use select like this:
select emp_name, skills from employee where skills like '%Java%'

or this (to find more than 1 skill)

select emp_name, skills from employee where skills like '%Java%' and skills like '%Struts%'

[Ankit Nagpal]

Tatiana Vorobeva wrote:As I understand, he want to use one column for all skills

Sounds trouble in the long run though.
If possible, try to correct the root problem in the db design.

[mallikarjun dontamsetti]

Thanks guys for reply. And i prefer DB Change. and thing i need some clarity. If one user has multiple skills i think it will be stored like this

emp_skills    Emp_Id     skill_id ......... ...... ....... ......
1               101           2
2               101           5
3               101           4      
......

when i get the data using joins then is it going to be a performance issue?

[Vijitha Kumara]

Not clear what you have mentioned here. Define two tables Skills, Employee_skills with the latter references the foreign key form the Skills table along with employee id from possibly from the Employee table.

[Ankit Nagpal]

mallikarjun dontamsetti wrote:Thanks guys for reply. And i prefer DB Change. and thing i need some clarity. If one user has multiple skills i think it will be stored like this

emp_skills    Emp_Id     skill_id ......... ...... ....... ......
1               101           2
2               101           5
3               101           4      
......

when i get the data using joins then is it going to be a performance issue?

You are absolutely correct now, and as far as I know, joins are absolutely fine til the time you have the proper joining conditions on each and every joined table. It is a general practice to follow this approach during many-to-many associations.