aspose file tools*
The moose likes Object Relational Mapping and the fly likes Hibernate problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate problem" Watch "Hibernate problem" New topic
Author

Hibernate problem

mallikarjun dontamsetti
Ranch Hand

Joined: Mar 18, 2011
Posts: 244

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

Joined: Jul 30, 2010
Posts: 110

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.


Feel free to ask me anything!
www.BlackBeltFactory.com/ui#!/ref=jmotta, SCJP 6, OCWCD JEE5, OCE EJB JEE6
mallikarjun dontamsetti
Ranch Hand

Joined: Mar 18, 2011
Posts: 244

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

Joined: Mar 24, 2008
Posts: 3860

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.


SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
mallikarjun dontamsetti
Ranch Hand

Joined: Mar 18, 2011
Posts: 244

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

Joined: Sep 09, 2008
Posts: 47

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

Joined: Mar 18, 2011
Posts: 244

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

Joined: Sep 09, 2008
Posts: 47

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

Joined: Aug 06, 2011
Posts: 11

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

Joined: Sep 09, 2008
Posts: 47

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

Joined: Mar 18, 2011
Posts: 244

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

Joined: Mar 24, 2008
Posts: 3860

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

Joined: Sep 09, 2008
Posts: 47

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Hibernate problem