here i am trying out many-to-many relationship in JPA, I'v created tables "tblcourse" and "tblStudent", a student can register to many courses,
create table tblcourse(
id integer primary key,
name varchar(100),
duration integer
);
create table tblcourseStudent(
studentid integer references tblstudent(studentId),
courseId integer references tblcourse(id),
constraint pk_composit_cs primary key(studentid,courseId)
)
Create table tblStudent(
studentId integer primary key,
……..
….
);
The JPA representation of the above relation is as follows, this is the code for StudentEntity.java,
@Entity
@Table(name="TBLSTUDENT")
public class StudentEntity implements Serializable{
private static final long serialVersionUID = 100034222342L;
@Id
@Column(name="STUDENTID")
private Integer studentId;
@Column(name="STUDENTNAME")
private String studentName;
@Column(name="CONTACTNO")
private String contactNumber;
@Embedded
private StudentAddress address;
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="DEPTID")
private DeptEntity deptEntity;
@ManyToMany(fetch=FetchType.LAZY)
@JoinTable(name="tblcourseStudent",
joinColumns=@JoinColumn(name="studentid"),
inverseJoinColumns=@JoinColumn(name="courseId"))
private List<CourseEntity> courseList;
....
.....
.....
}
this the code for CourseEntity.java,
@Entity
@Table(name="TBLCOURSE")
public class CourseEntity implements Serializable{
public CourseEntity(){
}
public CourseEntity(Integer courseId,String courseName,Integer courseDuration){
this.courseId = courseId;
this.courseName = courseName;
this.courseDuration = courseDuration;
}
/**
*
*/
private static final long serialVersionUID = -2192479237310864341L;
@Id
@Column(name="ID")
private Integer courseId;
@Column(name="NAME")
private String courseName;
@Column(name="DURATION")
private Integer courseDuration;
@ManyToMany(fetch=FetchType.LAZY)
@JoinTable(name="tblcourseStudent",
joinColumns=@JoinColumn(name="courseId"),
inverseJoinColumns=@JoinColumn(name="studentid"))
private List<StudentEntity> studentList;
.........
}
Now, when i try to insert courses throught StudentEntity.java, the SQL queries fired at backend are
delete
from
tblcourseStudent
where
studentid=?
insert
into
tblcourseStudent
(studentid, courseId)
values
(?, ?)
insert
into
tblcourseStudent
(studentid, courseId)
values
(?, ?)
And, when i try to insert students throught CourseEntity.java, the SQL queries fired are as follows,
delete
from
tblcourseStudent
where
courseId=?
insert
into
tblcourseStudent
(courseId, studentid)
values
(?, ?)
in both of my case, the records are deleted and than the new mapping is inserted. So if i am inserting Courses for a student, first all the previouse courses for the student will be deleted from the third table, and the new courses will be entered,
So, my question is, if i don't want to delete old courses and add the new courses for the student how can i achieve, i.e i want to retain the old relationship,
Weather i have to achieve this programatically, or i have change the annotation, Waiting for the reply
This the code written in StudentServiceBean.java and the method "mapStudentToCourses" gets called when we map a single student to multiple Courses
@Stateless
@TransactionManagement(TransactionManagementType.CONTAINER)
public class StudentServiceBean implements StudentService{
@PersistenceContext(unitName="forPractise")
private EntityManager entityMgr;
@Resource
private SessionContext sessionContext;
@EJB
private DeptService deptService;
..........
......
...
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void mapStudentToCourses(Integer studentId,String courseIdList) throws Exception{
List<CourseEntity> courseList = null;
StudentEntity studentEntity = null;
TypedQuery<CourseEntity> courseQuery = null;
String query = "select c from CourseEntity c where c.courseId in ("+courseIdList+")";
try{
courseQuery = entityMgr.createQuery(query,CourseEntity.class);
courseList = courseQuery.getResultList();
studentEntity = entityMgr.find(StudentEntity.class, studentId);
studentEntity.setCourseList(courseList);
entityMgr.merge(studentEntity);
}catch(Exception e){
sessionContext.setRollbackOnly();
throw e;
}
}
This is the code when one Course is mapped to multiple students, its CourseServiceBean.java
@Stateless
@TransactionManagement(TransactionManagementType.CONTAINER)
public class CourseServiceBean implements CourseService{
@PersistenceContext(name="forPractise")
private EntityManager em;
@Resource
private SessionContext sessionCtx;
private Map<Integer, String> durationCode = null;
@EJB
private StudentService studentService;
........
......
...
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void mapCourseToStudents(Integer courseId,String studentIdList) throws Exception{
List<StudentEntity> studentEntityList = null;
TypedQuery<StudentEntity> studentQuery = null;
String query = "select s from StudentEntity s where s.studentId IN ("+studentIdList+")";
CourseEntity courseEntity = null;
try{
studentQuery = em.createQuery(query, StudentEntity.class);
studentEntityList = studentQuery.getResultList();
courseEntity = em.find(CourseEntity.class,courseId);
courseEntity.setStudentList(studentEntityList);
em.merge(courseEntity);
}catch(Exception e){
sessionCtx.setRollbackOnly();
throw e;
}
}
}
this my persistence.xml file,
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0">
<persistence-unit name="forPractise" transaction-type="JTA">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>jdbc/app</jta-data-source>
<class>com.entity.StudentEntity</class>
<class>com.entity.DeptEntity</class>
<class>com.entity.CourseEntity</class>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.DerbyDialect" />
<property name="hibernate.show_sql" value="true" />
<property name="hibernate.format_sql" value="true" />
</properties>
</persistence-unit>
</persistence>
waiting for the reply....