join in sql

                                                       

   student table
   student_id===================>primary key
======================================================================================
   school table
   school_id===================>primary key
   student_id=================>foreign key
===============================================================================

   marks table
   enroll_id===================>primary key
   school_id===================>foreign key
=========================================================================================

 





SELECT  s.student_fname, s.student_mname, s.student_lname, sc.school_name, sc.school_level,m.marks, m.percentage, m.grade
FROM public.student s cross join school sc  cross join marks m

SELECT  s.student_fname, s.student_mname, s.student_lname, sc.school_name, sc.school_level,m.marks, m.percentage, m.grade
FROM public.student s inner join school sc on (s.student_id=sc.student_id)
inner join marks m on (sc.school_id=m.school_id)

SELECT  s.student_fname, s.student_mname, s.student_lname, sc.school_name, sc.school_level,m.marks, m.percentage, m.grade
FROM public.student s  join school sc on (s.student_id=sc.student_id)
join marks m on (sc.school_id=m.school_id)

SELECT  s.student_fname, s.student_mname, s.student_lname, sc.school_name, sc.school_level,m.marks, m.percentage, m.grade
FROM public.student s right join school sc on (s.student_id=sc.student_id)
right join marks m on (sc.school_id=m.school_id)

SELECT  s.student_fname, s.student_mname, s.student_lname, sc.school_name, sc.school_level,m.marks, m.percentage, m.grade
FROM public.student s left join school sc on (s.student_id=sc.student_id)
left join marks m on (sc.school_id=m.school_id)


SELECT  s.student_fname, s.student_mname, s.student_lname, sc.school_name, sc.school_level,m.marks, m.percentage, m.grade
FROM public.student s, school sc ,marks m where (s.student_id=sc.student_id and sc.school_id=m.school_id)

                      Spring Jdbc concept

//StudentModel .java

package govind.icjs.curd;

public class StudentModel {


private Integer studentId;
private String studentName;
private String studentFatherName;
private String studentMotherName;
private Float marks;
private String percentage;
private String grade;

public StudentModel()
{
}

public Integer getStudentId() {
return studentId;
}
public void setStudentId(Integer studentId) {
this.studentId = studentId;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public String getStudentFatherName() {
return studentFatherName;
}
public void setStudentFatherName(String studentFatherName) {
this.studentFatherName = studentFatherName;
}
public String getStudentMotherName() {
return studentMotherName;
}
public void setStudentMotherName(String studentMotherName) {
this.studentMotherName = studentMotherName;
}
public Float getMarks() {
return marks;
}
public void setMarks(Float marks) {
this.marks = marks;
}
public String getPercentage() {
return percentage;
}
public void setPercentage(String percentage) {
this.percentage = percentage;
}
public String getGrade() {
return grade;
}
public void setGrade(String grade) {
this.grade = grade;
}




}


===========================================================================================================================================================================================
//StudentDao .java

package govind.icjs.curd;

import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

public interface StudentDao {


public void setDataSource(DataSource ds);
public String saveStudentData(StudentModel studentmodel);
public String deleteStudentRecord(Integer studentId);
public String updateStudentRecord(StudentModel studentmodel);
public StudentModel getSingleStudentRecord(Integer studentId);
public List<StudentModel> getAllStudentRecord();
public List<StudentModel> getAllStudentRecordByOneHit();
Map<String, String> insertStudentdata(StudentModel studentmodel);
Map<String, String> updateStudentdata(StudentModel studentmodel);
Map<String, String> deleteStudentdata(Integer Studentid);
public List<StudentModel> getAllStudentList();
public List<StudentModel> getAllStudentList(Integer StudentId);



}


==============================================================================================================================================================================================



package govind.icjs.curd;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

public class StudentDaoImpl implements StudentDao{


    private DataSource datasource;
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private JdbcTemplate jdbcTemplate;

@Override
public void setDataSource(DataSource datasource) {
this.datasource=datasource;
this.namedParameterJdbcTemplate=new NamedParameterJdbcTemplate(datasource);
this.jdbcTemplate=new JdbcTemplate(datasource);

}



@Override
public String saveStudentData(StudentModel studentmodel) {

String sql="INSERT INTO student_namedparameter(student_name, student_fname, student_mname, marks,"
        +" percentage, grade) VALUES (:student_name, :student_fname, :student_mname,:marks,:percentage,:grade) ";
        SqlParameterSource namedParameters=new MapSqlParameterSource()
        .addValue("student_name", studentmodel.getStudentName())
        .addValue("student_fname",studentmodel.getStudentFatherName())
        .addValue("student_mname", studentmodel.getStudentMotherName())
        .addValue("marks", studentmodel.getMarks())
        .addValue("percentage", studentmodel.getPercentage())
        .addValue("grade", studentmodel.getGrade());
             int insert = namedParameterJdbcTemplate.update(sql, namedParameters);
       
             if(insert==1)
             {
            return "your data save successfully";
             }
           
             else
             {
            return "your data not save successfully";
             }
       
       
     }



@Override
public String deleteStudentRecord(Integer studentId) {
String sql="DELETE FROM student_namedparameter WHERE student_id=:student_id";
        SqlParameterSource namedparameters=new MapSqlParameterSource("student_id",studentId);
    int delete = namedParameterJdbcTemplate.update(sql, namedparameters);
    if(delete==1)
    {
    return "your data deleted successfully";
    }
    else
    {
    return "Data Not Deleted";
    }

}



@Override
public String updateStudentRecord(StudentModel studentmodel) {
String sql="UPDATE student_namedparameter SET student_id=:student_id,"
    + " student_name=:student_name, student_fname=:student_fname, student_mname=:student_mname,"
        + " marks=:marks, percentage=:percentage, grade=:grade"
        + " WHERE student_id=:student_id ";

//SqlParameterSource---->Interface
//MapSqlParameterSource---->Implemented class (i have method add value )
   
SqlParameterSource namedparameters=new MapSqlParameterSource().
         addValue("student_name", studentmodel.getStudentName())
    .addValue("student_fname", studentmodel.getStudentFatherName())
    .addValue("student_mname", studentmodel.getStudentMotherName())
    .addValue("marks", studentmodel.getMarks())
    .addValue("percentage", studentmodel.getPercentage())
    .addValue("grade", studentmodel.getGrade())
    .addValue("student_id", studentmodel.getStudentId());
 
   int updatebyid = namedParameterJdbcTemplate.update(sql, namedparameters);

   if(updatebyid==1)
   {
return "Your Data is Updated";
   }
   else
         {
   return "Your Data is Not Updated";

     }
   
}



@Override
public StudentModel getSingleStudentRecord(Integer studentId) {
String sql="SELECT student_id, student_name, student_fname, student_mname, marks,"
         +" percentage, grade FROM student_namedparameter where student_id=:student_id";
   
SqlParameterSource namedparameters=new MapSqlParameterSource("student_id",studentId);
   
    StudentModel singlerecord = namedParameterJdbcTemplate.queryForObject(sql, namedparameters, new StudentMapper());

 
   return singlerecord;

}



@Override
public List<StudentModel> getAllStudentRecord() {

   String sql="SELECT student_id, student_name, student_fname, student_mname, marks,"
       +" percentage, grade  FROM student_namedparameter ";

   List<StudentModel> allrecord = namedParameterJdbcTemplate.query(sql, new StudentMapper());

return allrecord;



}


//ResultSetExtractor implementation
@Override
public List<StudentModel> getAllStudentRecordByOneHit() {
return namedParameterJdbcTemplate.query("select * from student_namedparameter",new ResultSetExtractor<List<StudentModel>>()
{
       
@Override
public List<StudentModel> extractData(ResultSet rs) throws SQLException, DataAccessException {
List<StudentModel> alldata=new ArrayList<StudentModel>();
while (rs.next())

       {

StudentModel studentmodel=new StudentModel();
    studentmodel.setStudentId(rs.getInt(1));
studentmodel.setStudentName(rs.getString(2));
studentmodel.setStudentFatherName(rs.getString(3));
studentmodel.setStudentMotherName(rs.getString(4));
studentmodel.setMarks(rs.getFloat(5));
studentmodel.setPercentage(rs.getString(6));
studentmodel.setGrade(rs.getString(7));
alldata.add(studentmodel);
   }
return alldata;
}
});

  }



@Override
public Map<String, String> insertStudentdata(StudentModel studentmodel) {
//SqlParameterSource interface and MapSqlParameterSource is implemented class
MapSqlParameterSource namedparameters=new MapSqlParameterSource();
Map<String, String> queryResult=new HashMap<>();
String sql="INSERT INTO student_namedparameter(student_name, student_fname, student_mname, marks,"
        +" percentage, grade) VALUES (:student_name, :student_fname, :student_mname,:marks,:percentage,:grade) ";

        namedparameters.addValue("student_name", studentmodel.getStudentName());
        namedparameters.addValue("student_fname",studentmodel.getStudentFatherName());
        namedparameters.addValue("student_mname", studentmodel.getStudentMotherName());
        namedparameters.addValue("marks", studentmodel.getMarks());
        namedparameters.addValue("percentage", studentmodel.getPercentage());
        namedparameters.addValue("grade", studentmodel.getGrade());
     
        try {
KeyHolder keyHolder = new GeneratedKeyHolder();
int key = namedParameterJdbcTemplate.update(sql, namedparameters, keyHolder);

Map<String, Object> obj = keyHolder.getKeys();
int studentid =(int)  obj.get("student_id");

if(key == 1){

queryResult.put("result", ""+studentid);
}
else
{
queryResult.put("rseult", "dberror");
}


} catch (Exception ex) {

queryResult.put("rseult", "dberror");
}
return queryResult;
     
     
      }



@Override
public Map<String, String> updateStudentdata(StudentModel studentmodel) {
MapSqlParameterSource namedparameters=new MapSqlParameterSource();
Map<String, String> queryResult=new HashMap<>();
String sql="UPDATE student_namedparameter SET student_id=:student_id,"
    + " student_name=:student_name, student_fname=:student_fname, student_mname=:student_mname,"
        + " marks=:marks, percentage=:percentage, grade=:grade"
        + " WHERE student_id=:student_id ";


namedparameters.addValue("student_name", studentmodel.getStudentName());
namedparameters.addValue("student_fname", studentmodel.getStudentFatherName());
namedparameters.addValue("student_mname", studentmodel.getStudentMotherName());
namedparameters.addValue("marks", studentmodel.getMarks());
namedparameters.addValue("percentage", studentmodel.getPercentage());
namedparameters.addValue("grade", studentmodel.getGrade());
namedparameters.addValue("student_id", studentmodel.getStudentId());

try {
KeyHolder keyHolder = new GeneratedKeyHolder();
int key = namedParameterJdbcTemplate.update(sql, namedparameters, keyHolder);

Map<String, Object> obj = keyHolder.getKeys();
int studentid =(int)  obj.get("student_id");


if(key == 1){

queryResult.put("result", ""+studentid);
}else{
queryResult.put("rseult", "dberror");
}

} catch (Exception ex) {
//ex.printStackTrace();
queryResult.put("rseult", "dberror");
}
return queryResult;
}



@Override
public Map<String, String> deleteStudentdata(Integer studentid) {
MapSqlParameterSource namedparameters=new MapSqlParameterSource();
Map<String, String> queryResult=new HashMap<>();
String sql="DELETE FROM student_namedparameter"
        +" WHERE student_id=:student_id";

namedparameters.addValue("student_id", studentid);

try {
KeyHolder keyHolder = new GeneratedKeyHolder();
int key = namedParameterJdbcTemplate.update(sql, namedparameters, keyHolder);

Map<String, Object> obj = keyHolder.getKeys();
int stuid =(int)  obj.get("student_id");


if(key == 1){

queryResult.put("result", ""+stuid);
}else{
queryResult.put("rseult", "dberror");
}

} catch (Exception ex) {
//ex.printStackTrace();
queryResult.put("rseult", "dberror");
}
return queryResult;
}



@Override
public List<StudentModel> getAllStudentList() {
List<StudentModel> studentlist=null;
String sql="SELECT student_id, student_name, student_fname, student_mname, marks,"
       +" percentage, grade  FROM student_namedparameter ";
try
{
studentlist = namedParameterJdbcTemplate.query(sql, new StudentMapper());
}
catch(DataAccessException ex)
{
ex.printStackTrace();
}

if(studentlist!=null && studentlist.size()>0)
{
return studentlist;
}
return studentlist;


}



@Override
public List<StudentModel> getAllStudentList(Integer StudentId) {
List<StudentModel> studentlist=null;
String sql="SELECT student_id, student_name, student_fname, student_mname, marks,"
       +" percentage, grade  FROM student_namedparameter where student_id=?";
try
{
studentlist = jdbcTemplate.query(sql, new Object[]{StudentId}, new StudentMapper());
}
catch(DataAccessException ex)
{
ex.printStackTrace();
}

if(studentlist!=null && studentlist.size()>0)
{
return studentlist;
}
return studentlist;

}


}






============================================================================================================================================================================================

//StudentMapper .java
package govind.icjs.curd;

import java.sql.ResultSet;
import java.sql.SQLException;

import javax.lang.model.util.Types;

import org.springframework.jdbc.core.RowMapper;

public class StudentMapper implements RowMapper<StudentModel>
{


@Override
public StudentModel mapRow(ResultSet rs, int arg1) throws SQLException {

StudentModel studentmodel=new StudentModel();
    studentmodel.setStudentId(rs.getInt("student_id"));
studentmodel.setStudentName(rs.getString("student_name"));
studentmodel.setStudentFatherName(rs.getString("student_fname"));
studentmodel.setStudentMotherName(rs.getString("student_mname"));
studentmodel.setMarks(rs.getFloat("marks"));
studentmodel.setPercentage(rs.getString("percentage"));
studentmodel.setGrade(rs.getString("grade"));
    return studentmodel;
}






}



================================================================================================================================================================================================



package govind.icjs.curd;

import java.util.List;
import java.util.Map;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;


public class StudentMain {


public static void main(String[] args) {
ApplicationContext applicationcontext=new ClassPathXmlApplicationContext("applicationContext.xml");
StudentDaoImpl studentdaoimpl = (StudentDaoImpl)applicationcontext.getBean("sdaoimpl");
    StudentModel studentmodel=new StudentModel();
        studentmodel.setStudentName("sanjay");
    studentmodel.setStudentFatherName("manjay jha");
    studentmodel.setStudentMotherName("manisha jha");
    studentmodel.setMarks(497.00f);
    studentmodel.setPercentage("98.5%");
    studentmodel.setGrade("A+");
 
  /*  System.out.println("---------------Insert Logic-----------------");
 
    String  insert =studentdaoimpl.saveStudentData(studentmodel);
    System.out.println("***R u Happy Dear*** "+insert);*/

 
  /*  System.out.println("---------------Delete Logic-----------------");
 
    String delete = studentdaoimpl.deleteStudentRecord(1);
    System.out.println("***R u Happy Dear*** "+delete);*/
 
 
     /*  System.out.println("---------------Update Logic-----------------");
     
        studentmodel.setStudentName("sanjeev jha");
    studentmodel.setStudentFatherName("sadanand jha");
    studentmodel.setStudentMotherName("shyama jha");
    studentmodel.setMarks(476.00f);
    studentmodel.setPercentage("96.5%");
    studentmodel.setGrade("B");
    studentmodel.setStudentId(12);*/
 
   /* String update = studentdaoimpl.updateStudentRecord(studentmodel);
    System.out.println("***R u Happy Dear*** "+update);
*/

 
/*System.out.println("----------------Get Single Record logic------------------");

StudentModel studentrecord = studentdaoimpl.getSingleStudentRecord(05);
System.out.println(studentrecord.getStudentName());
System.out.println(studentrecord.getStudentFatherName());
System.out.println(studentrecord.getStudentMotherName());
System.out.println(studentrecord.getMarks());
System.out.println(studentrecord.getPercentage());
System.out.println(studentrecord.getGrade());
*/


/* System.out.println("----------------Retrieve All Record----------------------");


List<StudentModel> allrecord = studentdaoimpl.getAllStudentRecord();
int i=0;
for(StudentModel alldata:allrecord)
{
System.out.println("Record Number="+ ++i);
System.out.println(alldata.getStudentId());
System.out.println(alldata.getStudentName());
System.out.println(alldata.getStudentFatherName());
System.out.println(alldata.getStudentMotherName());
System.out.println(alldata.getMarks());
System.out.println(alldata.getPercentage());
System.out.println(alldata.getGrade());
}*/

 
        //Resultset Extractor Implementation
   /* System.out.println("----------------------Result Shows Through Result Set Extractor-----------------");
    List<StudentModel> allstudentrecordbyonehit = studentdaoimpl.getAllStudentRecordByOneHit();
    for(StudentModel kk:allstudentrecordbyonehit)
    {
System.out.println(kk.getStudentId() +" "+kk.getStudentName()+" "+kk.getStudentFatherName()+" "+kk.getStudentMotherName()+" "+kk.getMarks()+" "+kk.getPercentage()+ " "+kk.getGrade());
    }*/
 
 
/*Map<String, String> insert = studentdaoimpl.insertStudentdata(studentmodel);
  if( insert.get("result").equalsIgnoreCase("dberror"))
   {
   System.out.println("Data Not save Successfully");
   }
   else
   {
   System.out.println("Data save Successfully");
   }*/

 
  /* Map<String, String> update = studentdaoimpl.updateStudentdata(studentmodel);
   if( update.get("result").equalsIgnoreCase("dberror"))
   {
   System.out.println("Data Not Updated Successfully");
   }
   else
   {
   System.out.println("Data Updated Successfully");
   }
  */
 
    /*Map<String, String> delete = studentdaoimpl.deleteStudentdata(12);
   if( delete.get("result").equalsIgnoreCase("dberror"))
   {
   System.out.println("Data Not Deleted Successfully");
   }
   else
   {
   System.out.println("Data Deleted Successfully");
   }
    */
 
 

  /*  List<StudentModel> stulist = studentdaoimpl.getAllStudentList();
    for(StudentModel stlist:stulist)
    {
    System.out.println(stlist.getStudentName()+" "+stlist.getStudentFatherName()+" "+stlist.getStudentMotherName()+ " "+stlist.getMarks()+" "+stlist.getPercentage()+ " "+stlist.getGrade());
    }*/
 
 
 
    /*  List<StudentModel> stulist  =studentdaoimpl.getAllStudentList(2);
    for(StudentModel stlist:stulist)
    {
    System.out.println(stlist.getStudentName()+" "+stlist.getStudentFatherName()+" "+stlist.getStudentMotherName()+ " "+stlist.getMarks()+" "+stlist.getPercentage()+ " "+stlist.getGrade());
    } */
 
 
 
 
     }
}



===========================================================================================================================================================================================

//applicationContext.xml


<?xml version="1.0" encoding="UTF-8"?>
<beans
    xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:p="http://www.springframework.org/schema/p"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

<bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.postgresql.Driver" />
<property name="url" value="jdbc:postgresql://localhost:5432/govind" />
<property name="username" value="postgres" />
<property name="password" value="postgres" />
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="datasource"></property>
</bean>
<bean id = "edaoimpl"
      class = "govind.icjs.curd.EmployeeDaoImpl">
      <property name = "dataSource" ref ="datasource" /> 
   </bean>
 
 
<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
<constructor-arg ref="datasource"/>

</bean>
<bean id = "sdaoimpl"
      class = "govind.icjs.curd.StudentDaoImpl">
      <property name = "dataSource" ref ="datasource" /> 
   </bean>
 
     <bean id = "cdaoimpl"
      class = "govind.icjs.curd.CollegeDaoImpl">
      <property name = "dataSource" ref ="datasource" /> 
   </bean>
 
 
 
</beans>

Spring NamedParameterJdbcTemplate

//CollegeModel.java

package govind.icjs.curd;

public class CollegeModel {


private Integer collegeId;
private String collegeName;
private String universityName;
private Integer totalDepartment;
private Integer totalStudents;
private String collegeLevel;


public Integer getCollegeId() {
return collegeId;
}
public void setCollegeId(Integer collegeId) {
this.collegeId = collegeId;
}
public String getCollegeName() {
return collegeName;
}
public void setCollegeName(String collegeName) {
this.collegeName = collegeName;
}
public String getUniversityName() {
return universityName;
}
public void setUniversityName(String universityName) {
this.universityName = universityName;
}
public Integer getTotalDepartment() {
return totalDepartment;
}
public void setTotalDepartment(Integer totalDepartment) {
this.totalDepartment = totalDepartment;
}
public Integer getTotalStudents() {
return totalStudents;
}
public void setTotalStudents(Integer totalStudents) {
this.totalStudents = totalStudents;
}
public String getCollegeLevel() {
return collegeLevel;
}
public void setCollegeLevel(String collegeLevel) {
this.collegeLevel = collegeLevel;
}





}


=============================================================================================================================================================================================
//CollegeDao .java



package govind.icjs.curd;

import java.util.List;

import javax.sql.DataSource;

public interface CollegeDao
{

 public void setDataSource(DataSource datasource);
 public Integer insertCollegeData(CollegeModel collegemodel);
 public Integer updateCollegeData(CollegeModel collegemodel);
 public Integer deleteCollegeData(Integer collegeid);
 public CollegeModel getcollegeData(Integer collegeid);
 public List<CollegeModel> getAllCollegeData();
 public List<CollegeModel> getAllCollegeDataByOneHit();

 //batch update

 public int[] batchInsert(final List<CollegeModel> collegeList);
 public int[] batchUpdate(final List<CollegeModel> collegeList);


}


=============================================================================================================================================================================================
//CollegeDaoImpl.java

package govind.icjs.curd;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

public class CollegeDaoImpl implements CollegeDao{

private DataSource datasource;
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private JdbcTemplate jdbcTemplate;

@Override
public void setDataSource(DataSource datasource) {
this.datasource=datasource;
this.namedParameterJdbcTemplate=new NamedParameterJdbcTemplate(datasource);
this.jdbcTemplate=new JdbcTemplate(datasource);

}
    @Override
public Integer insertCollegeData(CollegeModel collegemodel) {
Integer insert=0;
Map<String,Object> namedparameters=new HashMap<>();
String sql="INSERT INTO college(college_name, university_name, total_dept, total_students,"
        +" college_level) VALUES ( :college_name, :university_name, :total_dept, :total_students, "
        +" :college_level)";

namedparameters.put("college_name", collegemodel.getCollegeName());
namedparameters.put("university_name", collegemodel.getUniversityName());
namedparameters.put("total_dept", collegemodel.getTotalDepartment());
namedparameters.put("total_students", collegemodel.getTotalStudents());
namedparameters.put("college_level", collegemodel.getCollegeLevel());

    insert = namedParameterJdbcTemplate.update(sql, namedparameters);

if(insert!=0)
{
return insert;
}

else
{
return 0;
}

}







@Override
public Integer updateCollegeData(CollegeModel collegemodel) {

Integer update=0;
String sql="UPDATE college SET college_name=:college_name, university_name=:university_name, total_dept=:total_dept,"
        +" total_students=:total_students, college_level=:college_level WHERE college_id=:college_id ";

Map<String, Object> namedparameteres=new HashMap<>();
namedparameteres.put("college_name", collegemodel.getCollegeName());
namedparameteres.put("university_name", collegemodel.getUniversityName());
namedparameteres.put("total_dept", collegemodel.getTotalDepartment());
namedparameteres.put("total_students", collegemodel.getTotalStudents());
namedparameteres.put("college_level", collegemodel.getCollegeLevel());
namedparameteres.put("college_id", collegemodel.getCollegeId());
    update = namedParameterJdbcTemplate.update(sql, namedparameteres);

if(update==1)
{
return update;
}
else
{
return 0;
}
}


@Override
public Integer deleteCollegeData(Integer collegeid) {
Integer deletedata=0;
String sql="DELETE FROM college  WHERE college_id=:college_id ";
Map<String, Object> namedparameteres=new HashMap<>();
namedparameteres.put("college_id", collegeid);
    deletedata = namedParameterJdbcTemplate.update(sql, namedparameteres);
if(deletedata==1)
{
return deletedata;
}
else
{
return 0;

}
}
@Override
public CollegeModel getcollegeData(Integer collegeid) {
CollegeModel collegemodel=null;
String sql="SELECT college_id, college_name, university_name, total_dept, total_students,"
       +" college_level FROM college where college_id=:college_id ";
Map<String, Object> namedparameters=new HashMap<>();
namedparameters.put("college_id", collegeid);
    collegemodel = namedParameterJdbcTemplate.queryForObject(sql, namedparameters, new CollegeMapper());
if(collegemodel!=null)
{
return collegemodel;
}

else
{
return null;
}



}









@Override
public List<CollegeModel> getAllCollegeData() {
List<CollegeModel> collegemodel=null;
String sql="SELECT college_id, college_name, university_name, total_dept, total_students,"
       +" college_level FROM college ";

collegemodel = namedParameterJdbcTemplate.query(sql, new CollegeMapper());
if(collegemodel!=null)
{
return collegemodel;
}
else
{
  return null;
}

}
@Override
public List<CollegeModel> getAllCollegeDataByOneHit() {


return namedParameterJdbcTemplate.query("select * from college",new  ResultSetExtractor<List<CollegeModel>>()
{

@Override
public List<CollegeModel> extractData(ResultSet rs) throws SQLException, DataAccessException {

List<CollegeModel> collegemodellist=new ArrayList<>();
while (rs.next())

       {

CollegeModel collegemodel=new CollegeModel();
collegemodel.setCollegeId(rs.getInt(1));
collegemodel.setCollegeName(rs.getString(2));
collegemodel.setUniversityName(rs.getString(3));
collegemodel.setTotalDepartment(rs.getInt(4));
collegemodel.setTotalStudents(rs.getInt(5));
collegemodel.setCollegeLevel(rs.getString(5));
collegemodellist.add(collegemodel);
   }
return collegemodellist;
}

});

}
@Override
public int[] batchInsert(List<CollegeModel> collegeList) {

String sql="INSERT INTO college("
            +" college_name, university_name, total_dept, total_students,"
            +" college_level) VALUES (?, ?, ?, ?, ?)";

return jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter() {

@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, collegeList.get(i).getCollegeName());
ps.setString(2, collegeList.get(i).getUniversityName());
ps.setInt(3, collegeList.get(i).getTotalDepartment());
ps.setInt(4, collegeList.get(i).getTotalStudents());
ps.setString(5, collegeList.get(i).getCollegeLevel());
}

@Override
public int getBatchSize() {
System.out.println(collegeList.size());
return collegeList.size();
}


});


}
@Override
public int[] batchUpdate(List<CollegeModel> collegeList) {
String sql="UPDATE college SET college_name=?, university_name=?, total_dept=?,"
      +" total_students=?, college_level=?"
      +" WHERE college_id=?";

return jdbcTemplate.batchUpdate(sql,new BatchPreparedStatementSetter() {

@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, collegeList.get(i).getCollegeName());
ps.setString(2, collegeList.get(i).getUniversityName());
ps.setInt(3, collegeList.get(i).getTotalDepartment());
ps.setInt(4, collegeList.get(i).getTotalStudents());
ps.setString(5, collegeList.get(i).getCollegeLevel());
ps.setInt(6, collegeList.get(i).getCollegeId());
}

@Override
public int getBatchSize() {
System.out.println(collegeList.size());
return collegeList.size();
}


});


}
}

========================================================================================================================================================================================
//CollegeMapper .java

package govind.icjs.curd;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class CollegeMapper implements RowMapper<CollegeModel>
{

@Override
public CollegeModel mapRow(ResultSet rs, int arg1) throws SQLException {
CollegeModel collegemodel=new CollegeModel();
collegemodel.setCollegeId(rs.getInt("college_id"));
collegemodel.setCollegeName(rs.getString("college_name"));
collegemodel.setUniversityName(rs.getString("university_name"));
collegemodel.setTotalDepartment(rs.getInt("total_dept"));
collegemodel.setTotalStudents(rs.getInt("total_students"));
collegemodel.setCollegeLevel(rs.getString("college_level"));

return collegemodel;
}

}



=============================================================================================================================================================================================//CollegeMain .java

package govind.icjs.curd;

import java.util.ArrayList;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class CollegeMain {


public static void main(String[] args) {
ApplicationContext applicationcontext=new ClassPathXmlApplicationContext("applicationContext.xml");
CollegeDaoImpl collegedaoimpl = (CollegeDaoImpl)applicationcontext.getBean("cdaoimpl");

CollegeModel collegemodel=new CollegeModel();
   /*
collegemodel.setCollegeName("TIT");
collegemodel.setUniversityName("RGPV");
collegemodel.setTotalDepartment(7);
collegemodel.setTotalStudents(546);
collegemodel.setCollegeLevel("A");
System.out.println("-----------------Insert Data Logic------------");
Integer collegeinsert = collegedaoimpl.insertCollegeData(collegemodel);
if(collegeinsert==1)
{
System.out.println("Data Save Successfully");
}
else
{
System.out.println("Data Not Save Successfully");
}*/


/*System.out.println("------------------Update Data Logic------------------");

collegemodel.setCollegeName("Rkdf");
collegemodel.setUniversityName("RGPVV");
collegemodel.setTotalDepartment(8);
collegemodel.setTotalStudents(450);
collegemodel.setCollegeLevel("B");
collegemodel.setCollegeId(2);
Integer updatedata = collegedaoimpl.updateCollegeData(collegemodel);
if(updatedata==1)
{
System.out.println("Update Successfully");
}
else
{
System.out.println("Data Not Updated");
}*/

/*System.out.println("-----------Delete logic---------");

Integer deletedata = collegedaoimpl.deleteCollegeData(5);

if(deletedata==1)
{
System.out.println("Delete data sucessfully");
}
else
{
System.out.println("Data Not Deleted");
}*/



/*System.out.println("-------------Retrieve Single Row Logics------------------");

CollegeModel retrivesinglerow = collegedaoimpl.getcollegeData(2);
if(retrivesinglerow==null)
{
System.out.println("No data Retrieve");
}
else
{
System.out.println("Retrieve Data Successfully");
System.out.println(retrivesinglerow.getCollegeId()+" "+retrivesinglerow.getCollegeName()+" "+retrivesinglerow.getUniversityName()+" "+retrivesinglerow.getTotalDepartment()+" "+retrivesinglerow.getTotalStudents()+" "+retrivesinglerow.getCollegeLevel());
}
*/

/*System.out.println("-------------Retrieve All Row Logics------------------");
System.out.println("Retrieve Data Successfully");
List<CollegeModel> collegemodellist = collegedaoimpl.getAllCollegeData();
if(collegemodellist!=null)
{
for(CollegeModel collegedetails:collegemodellist)
{

System.out.println(collegedetails.getCollegeId()+ " "+collegedetails.getCollegeName()+" "+collegedetails.getUniversityName()+" "+collegedetails.getTotalDepartment()+" "+collegedetails.getTotalStudents()+" "+collegedetails.getCollegeLevel());
}
}

else
{
System.out.println(" Data Not Retrieve Successfully");
}
*/
System.out.println("----------------------Retrieve All Data Through ResultSetExtractor--------------");

List<CollegeModel> collegedetaillist = collegedaoimpl.getAllCollegeDataByOneHit();

for(CollegeModel clglist:collegedetaillist)
{
System.out.println(clglist.getCollegeName()+" "+clglist.getUniversityName()+" "+clglist.getTotalDepartment()+" "+clglist.getTotalStudents()+" "+clglist.getCollegeLevel());
}

System.out.println("---------------------Batch Update Logics--------------");
CollegeModel cm=new CollegeModel();
cm.setCollegeName(" JNCT");
cm.setUniversityName("Rppv");
cm.setTotalDepartment(6);
cm.setTotalStudents(392);
cm.setCollegeLevel("B");
cm.setCollegeId(5);


CollegeModel cm1=new CollegeModel();
cm1.setCollegeName("MNCT");
cm1.setUniversityName("Rgpv");
cm1.setTotalDepartment(13);
cm1.setTotalStudents(375);
cm1.setCollegeLevel("C");
cm1.setCollegeId(6);

List<CollegeModel> collegebatch=new ArrayList<>();
collegebatch.add(cm);
collegebatch.add(cm1);

int[] kk = collegedaoimpl.batchInsert(collegebatch);
System.out.println("Total Number Of row Inserted=========>"+kk.length);
if(kk.length>0)
{
System.out.println("All record inserted through batch updated");
}
else
{
System.out.println("All record Not inserted through batch updated");
}


/*int[] kk = collegedaoimpl.batchUpdate(collegebatch);
System.out.println("Total Number Of row Updated=========>"+kk.length);
if(kk.length>0)
{
System.out.println("All record Updated through batch updated");
}
else
{
System.out.println("All record Not Updated through batch updated");
}
*/

System.out.println("----------------------------After Btach Update Result--------------------");
List<CollegeModel> collegedetaillist1= collegedaoimpl.getAllCollegeDataByOneHit();

for(CollegeModel clglist:collegedetaillist1)
{
System.out.println(clglist.getCollegeName()+" "+clglist.getUniversityName()+" "+clglist.getTotalDepartment()+" "+clglist.getTotalStudents()+" "+clglist.getCollegeLevel());
}


}}



==========================================================================


/applicationContext.xml


<?xml version="1.0" encoding="UTF-8"?> 
<beans 
    xmlns="http://www.springframework.org/schema/beans" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:p="http://www.springframework.org/schema/p" 
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd"> 
 
<bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> 
<property name="driverClassName" value="org.postgresql.Driver" /> 
<property name="url" value="jdbc:postgresql://localhost:5432/govind" /> 
<property name="username" value="postgres" /> 
<property name="password" value="postgres" /> 
</bean> 
 
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> 
<property name="dataSource" ref="datasource"></property> 
</bean> 
<bean id = "edaoimpl"
      class = "govind.icjs.curd.EmployeeDaoImpl">
      <property name = "dataSource" ref ="datasource" />   
   </bean>
 
 
<bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"> 
<constructor-arg ref="datasource"/>

</bean> 
<bean id = "sdaoimpl"
      class = "govind.icjs.curd.StudentDaoImpl">
      <property name = "dataSource" ref ="datasource" />   
   </bean>
 
     <bean id = "cdaoimpl"
      class = "govind.icjs.curd.CollegeDaoImpl">
      <property name = "dataSource" ref ="datasource" />   
   </bean>
 
 
 
</beans>
 
 

                      Spring JdbcTemplate

=======================================================================

//Employee.java

package govind.icjs.curd;

public class Employee {

private Integer id;
private String empName;
private String empAddress;
private String empEmail;
private String empDesignation;
private Float empSalary;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public String getEmpAddress() {
return empAddress;
}
public void setEmpAddress(String empAddress) {
this.empAddress = empAddress;
}
public String getEmpEmail() {
return empEmail;
}
public void setEmpEmail(String empEmail) {
this.empEmail = empEmail;
}
public String getEmpDesignation() {
return empDesignation;
}
public void setEmpDesignation(String empDesignation) {
this.empDesignation = empDesignation;
}
public Float getEmpSalary() {
return empSalary;
}
public void setEmpSalary(Float empSalary) {
this.empSalary = empSalary;
}



}


=================================================================================================================================
package govind.icjs.curd;
// EmployeeDao.java
import java.util.List;

import javax.sql.DataSource;

public interface EmployeeDao {


   public void setDataSource(DataSource ds);

   public void insertData(Integer id,String empName,String empAddress,String empEmail,String empDesignation,Float empSalary);

   public Employee getEmployee(Integer id);

   public List<Employee> getAllEmployee();

   public void delete(Integer id);

   public int update(Integer id,String emp_name,String emp_address);

}


=======================================================================================================================================
//EmployeeDaoImpl.java
============================================
package govind.icjs.curd;


import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;


public class EmployeeDaoImpl implements EmployeeDao {

private DataSource datasource;
private JdbcTemplate jdbcTemplate;

@Override
public void setDataSource(DataSource datasource) {
this.datasource=datasource;
this.jdbcTemplate=new JdbcTemplate(datasource);

}
    @Override
public void insertData(Integer id, String empName, String empAddress, String empEmail, String empDesignation,
Float empSalary) {
String sql="insert into emp_jdbctemplate(id,emp_name,emp_address,emp_email,emp_designation,emp_salary)values(?,?,?,?,?,?)";
jdbcTemplate.update(sql,new Object[]{id,empName,empAddress,empEmail,empDesignation,empSalary});
    System.out.println("Insert Data Successfully");
}

@Override
public Employee getEmployee(Integer id) {
String sql="select * from emp_jdbctemplate where id=?";
Employee emp = jdbcTemplate.queryForObject(sql, new Object[]{id},new EmployeeMapper());
return emp;

 
 
}

@Override
public List<Employee> getAllEmployee() {

String sql="select * from emp_jdbctemplate";
List<Employee> emp = jdbcTemplate.query(sql, new EmployeeMapper());
return emp;


}

@Override
public void delete(Integer id) {
String sql="delete from emp_jdbctemplate where id="+id+" ";
jdbcTemplate.update(sql);
System.out.println("Delete Data Successfully and id="+id);

}

@Override
public int update(Integer id,String emp_name,String emp_address)
    {
String sql="update emp_jdbctemplate set emp_name=?,emp_address=? where id=?";
    return jdbcTemplate.update(sql,new Object[]{emp_name,emp_address,id});
}
}


==============================================================================================================================================
//EmployeeMapper.java
======================================
package govind.icjs.curd;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;


public class EmployeeMapper implements RowMapper<Employee>{

public Employee mapRow(ResultSet rs, int rownum) throws SQLException {
    Employee e=new Employee();
e.setId(rs.getInt("id"));
e.setEmpName(rs.getString("emp_name"));
e.setEmpAddress(rs.getString("emp_address"));
e.setEmpEmail(rs.getString("emp_email"));
e.setEmpDesignation(rs.getString("emp_designation"));
e.setEmpSalary(rs.getFloat("emp_salary"));
return e;

}


}


===========================================================================================================================================
// EmployeeMain.java
=============================
package govind.icjs.curd;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class EmployeeMain {

public static void main(String[] args) {
ApplicationContext ac=new ClassPathXmlApplicationContext("applicationContext.xml");
    EmployeeDaoImpl edi = (EmployeeDaoImpl)ac.getBean("edaoimpl");

     System.out.println("-------------------Insert Data-----------------");
    edi.insertData(111,"sanjay", "ashoknagar", "sanjay@gmail.com","employee", 30000.00f);

    System.out.println("-------------------Delete Data-------------------");
        edi.delete(103);
   
       System.out.println("------------------Update Data----------------------");
    int status=edi.update(101, "harsh", "munirka");
    if(status>0)
{
System.out.println("-------Update data successfully-----");
}

else
{
System.out.println("---------Updated not Successfully------");
}


   Employee emp = edi.getEmployee(106);
   System.out.println(emp.getEmpName()+" "+emp.getEmpAddress()+" "+emp.getEmpEmail()+" "+emp.getEmpDesignation()+" "+emp.getEmpSalary());
   System.out.println("-----------------Data Retrieve successfully based on id----------");



    System.out.println("---------------------Retrieve all data------------------");
    List<Employee> l = edi.getAllEmployee();
    for(Employee e1:l)
    {
     System.out.println(e1.getId()+" "+e1.getEmpName()+" "+e1.getEmpAddress()+" "+e1.getEmpDesignation()+" "+e1.getEmpSalary());
    }
    System.out.println("------------Retrieve all data successfully--------------");



}

}


===========================================================================================================================================
applicationContext.xml
================================
<?xml version="1.0" encoding="UTF-8"?>
<beans
    xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:p="http://www.springframework.org/schema/p"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

<bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.postgresql.Driver" />
<property name="url" value="jdbc:postgresql://localhost:5432/govind" />
<property name="username" value="postgres" />
<property name="password" value="postgres" />
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="datasource"></property>
</bean>
<bean id = "edaoimpl"
      class = "govind.icjs.curd.EmployeeDaoImpl">
      <property name = "dataSource" ref ="datasource" />
   </bean>
</beans>



  =======================================================================================================================================

required jar files
=====================
commons-logging-1.1.2.jar
org.springframework.beans.jar
org.springframework.context.jar
postgresql-9.4-1200-jdbc41.jar
spring-core-3.2.3.release.jar
spring-dao-2.0.6.jar
spring-jdbc-3.2.4.release.jar