CODEWITHSUNDEEP
javaspringspring-3spring-jdbc
user182944
user182944

Reputation: 8067

Spring 3.x + SimpleJdbcTemplate : Returning multiple columns

I am using SimpleJdbcTemplate with Spring 3.x. For getting a single column, I use the below code and it works fine:

public String selectSingleColumn(int deptId){
    return jdbcTemplate.queryForObject("SELECT DEPT_NAME FROM DEPT WHERE DEPT_ID = ?", String.class, deptId);
}

Problem:

I want to fetch multiple columns such as DEPT_NAME and DEPT_CODE from that above table (but not all that belong to the table), how to modify the above code to get it done? I am confused with the second parameter of the queryForObject in this case; ideally I think it should be Object[] but stilll confused. Please guide me.

Upvotes: 2

Views: 13894

Answers (2)

Punnoose
Punnoose

Reputation: 72

Given below are the steps to do this -

(1) Create a domain Object for Department

public class Department{
    private String departmentName;
    private String departmentCode;
    //getters and setters omitted for brevity
}

(2) Create a RowMapper class to map the result set to the Department Object

public class DepartmentRowMapper implements RowMapper<Department>
{
    public Department mapRow(ResultSet rs, int rowNum) throws SQLException {
        Department department= new Department ();
        department.setDepartmentName(rs.getString("DEPT_NAME"));
        department.setDepartmentCode(rs.getString("DEPT_CODE"));
        return department;
    }

}

(3) Create the Dao class

public class DepartmentDao
   private JdbcTemplate jdbcTemplate; 
   //getters and setters omitted for brevity 
   public Department getDepartment(int deptId){
    return (Department)jdbcTemplate.queryForObject("SELECT DEPT_NAME FROM DEPT WHERE DEPT_ID = ?",new Object[] {deptId}, new DepartmentRowMapper ());
   }
}

Upvotes: -1

Paul Samsotha
Paul Samsotha

Reputation: 208944

I would just query for an entire domain object, instead of having to write different queries for different columns. For one, it makes the dao more reusable.

For example:

Department domain object

public class Department {
    private long id;
    private String deptName;
    private String deptCode;
    // other fields

    // getters and setters
}

DepartmentDao

public class DepartmentDaoImpl extends JdbcTemplate implements DepartmentDao {

    private static final String DEPT_BY_ID 
                          = "select * from DEPARTMENT where DEPT_ID = ?";

    @Override
    public Department getDepartmentById(long id) {
        return (Department) queryForObject(
             DEPT_BY_ID, 
             new Object[] { id },
             new RowMapper<Department>() {
                 @Override
                 public Department mapRow(ResultSet rs, int rowNumber) {
                     Department dept = new Department();
                     dept.setId(rs.getLong("DEPT_ID");
                     dept.setDeptName(rs.getString("DEPT_NAME");
                     dept.setDeptCode(rs.getString("DEPT_CODE");
                     // set other properties

                     return dept;
                 }
             });
    }
}

If you really, really only want two columns, you could use queryForMap

public class TestCustomerDao extends JdbcTemplate implements DepartmentDao {

    private static final String FOR_MAP 
                = "select DEPT_NAME,DEPT_CODE from DEPARTMENT where DEPT_ID = ?";

    @Override
    public Map<String, Object> getCoupleColumnsById(long id) {
        return (Map<String, Object>)queryForMap(FOR_MAP, new Object[] {id});
    }
}

The map will return as

   key        value
DEPT_NAME  =  value
DEPT_CODE  =  value

Upvotes: 3

Related Questions