MappingSqlQuery Spring Example

By Arvind Rai, November 09, 2013

MappingSqlQuery in spring is used for reusable query. A class which needs to use MappingSqlQuery has to override mapRow() method. It converts each row to an object of given bean. The subclass of MappingSqlQuery defines a constructor which passes query, data source and where clause parameter to MappingSqlQuery. Now while using the MappingSqlQuery, we need to call findObject() of MappingSqlQuery and need to pass parameter of where clause of the sql query.

CompanyMappingSqlQuery.java

package 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;
    }
}

CompanyDao.java

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);
	}
}

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 = companyDao.getCompany(1);
		System.out.println(comp.getLocation()+" "+comp.getName()+" "+comp.getNoOfEmp());
	}
}

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
mappingsqlquery-spring-example.zip

POSTED BY