Implementation of many to many relationship in java.

In this tutorial i will show you how to implement many to many relationship in sql when tables are in normalize form.In normalization of a database when two tables have many to many relationship we broke the tables into three tables two tables are original tables and one is a junction table which connects the two tables which have many to many relation.
In that way we break many to many realtion nto 1 to many relation.I will give you an example that clears the concept.

For example you have two entities or tables which are student and courses.Now one student can take many courses and similarly a course is taken by many students so these entities have many to many relation.

Okay lets discuss what are the problems arise if we implement tables as it is without breaking the many to many relationship,So these are the problems that can arise

 -Update anomaly
-Delete anomaly
-Data repetition
-Insert anomaly

To avoid these problems these tables should be normalized by creating the junction table,now these are the steps to normalize these tables.

1)Create a table with name student_course which has the composite primary key of both the id's of student and course table for example if student and courses tables have the attributes.
Student(std_id,name,address)
Courses(cr_id,name)

so the student_course table has the following attribute

student_course(std_id,cr_id , date)

2)In student_course table std_id and cr_id is composite pk which is taken by student and courses tables as a foreign key.

So in that way you succesfully normalized your tables.


Now the main problem is what are the queries to implement this concept which is the very big problem
just follow these simple steps to implement this concept.

1)create table of student with student id as primary key and auto increment.
2)create table of courses with course id as primary key and auto increment.
3)create the junction table using foreign key of student and courses tables id and make it composite pk.
4)First you have to execute the insert query of student table that inserted a record in student table with id 1.
5)insert some courses in courses tables.
6)Now very importantly Execute this query "SET @last_id=LAST_INSERT_ID()"
this query gives you the last inserted id in the student table,So you will use this variable to insert values int the student_courses table.You should run this insert queries in loop so that many courses are registered for one student.

Here are the queries

String sql="insert into student(`fname`,`lname`,`email`,`gender`,`desc`,`subject`,`city`)"+"values('"+fname+"','"+lname+"','"+email+"','"+gender+"','"+desc+"','"+s+"','"+city+"')";


         conn.createStatement().executeUpdate(sql);

         conn.createStatement().executeUpdate("SET @last_id=LAST_INSERT_ID();");

         for(int i=0;i<subject.length; i++){
         String sql2="insert into std_sbj(`std_id`,`sbj_id`)values(@last_id,'"+Integer.parseInt(subject[i])+"')";

        conn.createStatement().executeUpdate(sql2);