• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Hibernate problem

 
Ranch Hand
Posts: 246
Firefox Browser Oracle
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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(,).
 
Ranch Hand
Posts: 110
Google Web Toolkit Java Google App Engine
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for reply, please give me some example it will be help full for me
 
Bartender
Posts: 4116
72
Mac TypeScript Chrome Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ..
 
Ranch Hand
Posts: 47
Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 11
Android Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 4116
72
Mac TypeScript Chrome Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic