Hi,
I am trying to map a many to many relation using hibernate. The tables are
1. employee_master (primary key employee_id auto generated via db sequence)
2. skill_master (primary key skill_id auto generated via db sequence)
3. employee_skills (relation table, primary key employee_id+skill_id)
The relevant code sections are -
Code:
@Entity
@Table(name="L004_EMPLOYEE_MASTER")
@SequenceGenerator(name="SEQ_EMPLOYEE_MASTER",sequenceName="SEQ_L004_EMPLOYEE_MASTER",allocationSize=1)
public class Employee implements Serializable
{
@Id
@Column(name = "EMPLOYEE_ID_N",nullable = false)
@GeneratedValue(strategy = GenerationType.SEQUENCE,generator="SEQ_EMPLOYEE_MASTER")
private Integer employeeId;
@ManyToMany(fetch=FetchType.LAZY)
@JoinTable
(
name="L004_EMPLOYEE_SKILLS",
joinColumns=@JoinColumn(name="EMPLOYEE_ID_N",referencedColumnName="EMPLOYEE_ID_N",nullable=false),
inverseJoinColumns=@JoinColumn(name="SKILL_ID_N", referencedColumnName="SKILL_ID_N",nullable=false),
)
@MapKey(name="skillId")
private Map<Integer,Skill> skills = new HashMap<Integer,Skill>();
public void addSkill(Skill skill)
{
if(skill != null)
{
if(!skills.containsKey(skill.getSkillId()))
{
/*
* Sequence of following two statements is critical.
* Reversing the sequence will create a infinite recursion
*/
skills.put(skill.getSkillId(),skill);
//skill.addEmployee(this);
}
}
}
}
My persistence XML file is -
Code:
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">
<persistence-unit name="HELLO" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<class>testJPA.Employee</class>
<class>testJPA.Department</class>
<class>testJPA.Skill</class>
<properties>
<property name="hibernate.connection.driver_class" value="com.p6spy.engine.spy.P6SpyDriver"></property>
<property name="hibernate.connection.url" value="jdbc:postgresql://localhost:5432/postgres"></property>
<property name="hibernate.connection.username" value="postgres"></property>
<property name="hibernate.connection.password" value="root"></property>
<property name="hibernate.connection.autocommit" value="false"></property>
<property name="hibernate.connection.provider_class" value="org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider"></property>
<property name="hibernate.default_schema" value="JPA"></property>
<property name="hibernate.c3p0.acquire_increment" value="1"></property>
<property name="hibernate.c3p0.idle_test_period" value="0"></property>
<property name="hibernate.c3p0.min_size" value="1"></property>
<property name="hibernate.c3p0.max_size" value="15"></property>
<property name="hibernate.c3p0.timeout" value="0"></property>
<property name="hibernate.show_sql" value="false"></property>
<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"></property>
<property name="query.substitutions" value="true 1, false 0, yes 'Y', no 'N'"></property>
<property name="hibernate.id.new_generator_mappings" value="true"></property>
</properties>
</persistence-unit>
</persistence>
The line
if(!skills.containsKey(skill.getSkillId())) in addSkill method generates the following SQL
select skills0_.EMPLOYEE_ID_N as EMPLOYEE1_0_1_,
skills0_.SKILL_ID_N as SKILL2_1_,
(select a3.SKILL_ID_N from
L004_SKILL_MASTER a3 where a3.SKILL_ID_N=skills0_.SKILL_ID_N) as formula0_1_,
skill1_.SKILL_ID_N as SKILL1_2_0_,
skill1_.LAST_UPDATE_TIME_T as LAST2_2_0_,
skill1_.NAME_V as NAME3_2_0_
from JPA.L004_EMPLOYEE_SKILLS skills0_ inner join JPA.L004_SKILL_MASTER skill1_
on skills0_.SKILL_ID_N=skill1_.SKILL_ID_N
where skills0_.EMPLOYEE_ID_N=?
Please notice the missing schema name in the subquery table (highlighted in red).
Is this a bug? Or I am missing something?
My environment -
1. Windows 7
2. Eclipse Indigo
3. JRE 1.7 (oracle)
4. postgresSQL server 9.1
5. Application environment pojo
Regards,
Dinabandhu
Login
FAQ
Search