MappingSqlQuery Spring Example
March 03, 2023
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.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;
}
}
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`) )
