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>
No comments:
Post a Comment