SqlParameterSource Spring Example
January 24, 2023
SqlParameterSource interface is used to insert rows in database tables. The SqlParameterSource is more efficient than plane map approach. Java bean which is mapped to table, can directly be used to insert values. The SqlParameterSource has implementations as BeanPropertySqlParameterSource and MapSqlParameterSource. Here on this page, we will show the uses of these both classes.
Using BeanPropertySqlParameterSource
TheBeanPropertySqlParameterSource is the implementation of SqlParameterSource that obtains parameter values from bean properties of a given JavaBean object. The names of the bean properties have to match the parameter names.
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
TheMapSqlParameterSource is the SqlParameterSource implementation that holds a given Map of parameters.
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());
}
}
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;
}
}
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());
}
}
<?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>
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`) )
