Home  >  Spring Core

SqlParameterSource Spring Example

By Arvind Rai, November 08, 2013
SqlParameterSource in spring is used to insert rows in table. SqlParameterSource is more efficient than plane map approach. Java bean which is mapped to table, can directly be used to insert values. SqlParameterSource has two implementation BeanPropertySqlParameterSource and MapSqlParameterSource. We will show the uses of both classes approach.

Using BeanPropertySqlParameterSource

CompanyDao.java
package com.concretepage.dao;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import com.concretepage.bean.Company;
@Repository
public class CompanyDao {
	private SimpleJdbcInsert insertCompany;
    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.insertCompany = new SimpleJdbcInsert(dataSource).withTableName("company").usingGeneratedKeyColumns("id");
    }
    public void addCompnay(Company comp){
    	SqlParameterSource parameters = new BeanPropertySqlParameterSource(comp);
        Number genId = insertCompany.executeAndReturnKey(parameters);
        comp.setId(genId.longValue());
    }
}
 

Using MapSqlParameterSource

CompanyDao.java
package com.concretepage.dao;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import com.concretepage.bean.Company;
@Repository
public class CompanyDao {
	private SimpleJdbcInsert insertCompany;
    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.insertCompany = new SimpleJdbcInsert(dataSource).withTableName("company").usingGeneratedKeyColumns("id");
    }
    public void addCompnay(Company comp){
    	SqlParameterSource parameters = new MapSqlParameterSource().addValue("name", comp.getName())
    			.addValue("location",comp.getId()).addValue("no_of_emp",comp.getNoOfEmp());
        Number genId = insertCompany.executeAndReturnKey(parameters);
        comp.setId(genId.longValue());
    }
}
Company.java
package com.concretepage.bean;
public class Company {
   private long id;	
   private String name;
   private  String location;
   private int noOfEmp;
   public Company(String name, String location, int noOfEmp){
	  this.name = name;
	  this.location = location;
	  this.noOfEmp = noOfEmp;
    }
	public String getName() {
		return name;
	}
	public String getLocation() {
		return location;
	}
	public int getNoOfEmp() {
		return noOfEmp;
	}
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
}
 
SpringTest.java
package com.concretepage;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.concretepage.bean.Company;
import com.concretepage.dao.CompanyDao;
public class SpringTest {
	public static void main(String[] args) {
		ApplicationContext  context = new ClassPathXmlApplicationContext("spring.xml");
		CompanyDao companyDao = (CompanyDao)context.getBean("companyDao");
		Company comp = new Company("Techwox", "Varanasi", 100);
		companyDao.addCompnay(comp);
		System.out.println("Generated Id:"+comp.getId());
	}
}   
 
Spring.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:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-3.0.xsd">
    
    <bean id="companyDao" class="com.concretepage.dao.CompanyDao">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/test"/>
        <property name="username" value="root"/>
        <property name="password" value=""/>
    </bean>
</beans>
 
Table Schema : company
CREATE TABLE `company` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL,
	`location` VARCHAR(50) NULL DEFAULT NULL,
	`no_of_emp` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
 
Download Source Code
sqlparametersource-spring-example.zip
POSTED BY
ARVIND RAI
ARVIND RAI
FIND MORE TUTORILAS






©2019 concretepage.com | Privacy Policy | Contact Us