MappingSqlQuery in Spring
March 03, 2023
On this page, we will learn using MappingSqlQuery
in our Spring JDBC application.
1. The
MappingSqlQuery
is an abstract class that is inherited by a class to implement mapRow(ResultSet, int)
method that converts each row of the JDBC ResultSet
into an object.
2. The
MappingSqlQuery
has following constructors.
MappingSqlQuery() MappingSqlQuery(DataSource ds, String sql)
protected abstract T mapRow(ResultSet rs, int rowNum) throws SQLException
ResultSet
into an object.
Complete Example
CompanyMappingSqlQuery.javapackage com.concretepage; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import javax.sql.DataSource; import org.springframework.jdbc.core.SqlParameter; import org.springframework.jdbc.object.MappingSqlQuery; import com.concretepage.bean.Company; public class CompanyMappingSqlQuery extends MappingSqlQuery<Company>{ public CompanyMappingSqlQuery(DataSource ds) { super(ds, "select id, name, location, no_of_emp from company where id = ?"); super.declareParameter(new SqlParameter("id", Types.INTEGER)); compile(); } @Override protected Company mapRow(ResultSet rs, int rowNum) throws SQLException { Company company = new Company(); company.setId(rs.getLong("id")); company.setName(rs.getString("name")); company.setLocation(rs.getString("location")); company.setNoOfEmp(rs.getInt("no_of_emp")); return company; } }
package com.concretepage.dao; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; import com.concretepage.CompanyMappingSqlQuery; import com.concretepage.bean.Company; @Repository public class CompanyDao { private CompanyMappingSqlQuery compMapQuery; @Autowired public void setDataSource(DataSource dataSource) { this.compMapQuery = new CompanyMappingSqlQuery(dataSource); } public Company getCompany(int id) { return compMapQuery.findObject(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 = companyDao.getCompany(1); System.out.println(comp.getLocation()+" "+comp.getName()+" "+comp.getNoOfEmp()); } }
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`) )