Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Hibernate problem

 
mallikarjun dontamsetti
Ranch Hand
Posts: 246
Firefox Browser Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 110
Google App Engine Google Web Toolkit Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 246
Firefox Browser Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for reply, please give me some example it will be help full for me
 
Vijitha Kumara
Bartender
Posts: 3913
9
Chrome Fedora Hibernate
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 246
Firefox Browser Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 47
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 246
Firefox Browser Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 47
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:



No business logic required at all.
 
Tatiana Vorobeva
Greenhorn
Posts: 11
Android Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As I understand, he want to use one column for all skills and to use select like this:


or this (to find more than 1 skill)

 
Ankit Nagpal
Ranch Hand
Posts: 47
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 246
Firefox Browser Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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


when i get the data using joins then is it going to be a performance issue?
 
Vijitha Kumara
Bartender
Posts: 3913
9
Chrome Fedora Hibernate
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 47
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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


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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic