Archive

Archive for the ‘SQL’ Category

How to send multiple rows of data to Spring Controller

March 1, 2014 1 comment

Common requirement would be sending multiple rows of data to Spring Controller.

We use @RequestParam String value to hold the single form field. But when we dealing with multiple rows @RequestParam String value won’t work.

If It is single row like below like below

S.NO First Name Last Name Age
1 AAA BBB 23

We can use @RequestParam annotation to capture the form data:

public String saveEmployee(@RequestParam Integer sNo, @RequestParam String firstName, @RequestParam String firstName, @RequestParam Integer age){
//here we can access the form data.
}

Suppose we have a form with multiple rows. Then We have to use @RequestParam String[] values,

Let’s see the example below:

S.NO First Name Last Name Age
1 AAA BBB 23
2 CCC DDD 24
3 EEE FFF 28

And here is the Controller Code:


public String saveEmployees(@RequestParam Integer[] sNo, @RequestParam[] String firstName, @RequestParam[] String lastName, @RequestParam[] Integer age){

 //first row data
 Integer sNo_0 = sNo[0];
 String firstName_0 = firstName[0];
 String lastName_0 = lastName[0];
 Integer age_0 = age[0];

//second row data

 Integer sNo_1 = sNo[1];
 String firstName_1 = firstName[1];
 String lastName_1 = lastName[1];
 Integer age_1 = age[1];

//third row data

 Integer sNo_2 = sNo[2];
 String firstName_2 = firstName[2];
 String lastName_2 = lastName[2];
 Integer age_2 = age[2];
 }

Thanks for reading.

Advertisements
Categories: SQL

SQL Joins explained in simple terms

Joins are used to join two or more tables in SQL based on the common data.
In this post I have explained below joins

1. Inner Join
2. Left outer join
3. Right outer join

Example:  Let’s say we have an Employee table and Department table. Where every employee is associated with Department, new employees many not have Department.
And Every Department will have employees, for new Department, employees may not be there.

Department Table with data:
department_tableEmployee table with data:
employee_data

Here department_id is the foreign key to Department table, Department 3 doesn’t have any employees associated with it.  And Employees 4,5 are not associated with any Department.

Now will perform different type of joins and examine the results:

1. Inner Join: Intersection of two tables is called inner join, Only the matched data will be returned from the two tables

Query: select e.*, d.* from employee e inner join department d on e.department_id = d.id
Resulted data :
inner_join

The employees who doesn’t have any department and Department which doesn’t have employees won’t be returned.
In Venn diagram it looks like this:

inner_join_first
2. Left outer join: returns all the data from the left table and only matched data from the right table.

Query: select e.*, d.* from employee e left outer join department d on e.department_id = d.id
Resulted data :
left_outer_join

Returns all the 5 records from the Employee table and only 3 records from the Department table.
In the place of Department details for the employees 4,5( who doesn’t have department associated ) gives NULL values:
Venn diagram looks like this:
left_outer_join_venn

If we do left outer join from Department to Employee the result would be:
Query:  select  d.*, e.* from  department d left outer join employee e on  d.id = e.department_id

Resulted Data:

left_outer_join_reverse
Returns all the data from Department table and in the place of employee details for Department 3 gives NULL values:

3. Right Outer Join:

Returns all the data from right side table and only matched data from the left side table.
Query: select e.*, d.* from employee e right outer join department d on e.department_id = d.id

Resulted data:

right_outer_join

Returned all records from Department table and NULL values in the place Department 3 as no employees associated with it.
Venn diagram looks like this:
right_outer_join_venn

Thanks for reading.

Categories: SQL