MappingSqlQuery Spring Example
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);
}
}
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`) )
mappingsqlquery-spring-example.zip
