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(,).
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
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.
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.
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:
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.
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.