Announcement Announcement Module
No announcement yet.
Comparison between "queryForList" and "query" working with a List of Objects Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Comparison between "queryForList" and "query" working with a List of Objects

    Hello guys

    Normally I work with Hibernate, now due of a possible speak/exposition I am working with Spring JDBC.

    I have this table

    create table clientecuentacabecera(
    	idClienteCuentaCabecera varchar(15) not null,
    	fechaCreacionClienteCuentaCabecera datetime not null,	
    	totalDisponibleClienteCuentaCabecera integer(4) not null,	
    	estadoClienteCuentaCabecera varchar(15) not null,
    	idCliente varchar(10) not null,
    	idMoneda varchar(5) not null,
    	PRIMARY KEY(idClienteCuentaCabecera),
    	FOREIGN KEY (idCliente) REFERENCES cliente (idCliente),
    	FOREIGN KEY (idMoneda) REFERENCES moneda (idMoneda)
    And I have this class too

    public class ClienteCuentaCabecera implements Serializable{
    	private String idClienteCuentaCabecera;
    	private DateTime fechaCreacionClienteCuentaCabecera;
    	private Integer totalDisponibleClienteCuentaCabecera;
    	private String estadoClienteCuentaCabecera;
    	private Cliente cliente;	
    	private Moneda moneda;
    You can see that the fields and variables match in a 100%

    Now for other object in my domain named Moneda If I want get an object for this class in a easy way I use

    public Moneda getMonedaById(String idMoneda){
    		return (Moneda) jdbcTemplate.queryForObject("SELECT * FROM moneda m WHERE m.idMoneda=?", 
    			                           			    new Object[]{idMoneda},
    				                                    new BeanPropertyRowMapper<Moneda>(Moneda.class));
    But What happen if I want get a collection of a X Object?

    I have read the API for the JdbcTemplate and the key method is queryForList which has 7 variations

     List<Map<String,Object>> queryForList(String sql)
     <T> List<T> queryForList(String sql, Class<T> elementType)
     <T> List<T> queryForList(String sql, Class<T> elementType, Object... args)
     List<Map<String,Object>> queryForList(String sql, Object... args)
     <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType)
     List<Map<String,Object>> 	queryForList(String sql, Object[] args, int[] argTypes)
     <T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType)
    At a first glance, the most adequate, or more reasonable is

    <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType)

    Which says

    public <T> List<T> queryForList(String sql,
                                    Object[] args,
                                    Class<T> elementType)
                         throws DataAccessException
        Description copied from interface: JdbcOperations
        Query given SQL to create a prepared statement from SQL and a list of arguments to bind to the query, expecting a result list.
        The results will be mapped to a List (one entry for each row) of result objects, each of them matching the specified element type.
        Specified by:
            queryForList in interface JdbcOperations
            sql - SQL query to execute
            args - arguments to bind to the query (leaving it to the PreparedStatement to guess the corresponding SQL type); may also contain SqlParameterValue objects which indicate not only the argument value but also the SQL type and optionally the scale
            elementType - the required type of element in the result list (for example, Integer.class) 
            a List of objects that match the specified element type 
            DataAccessException - if the query fails
        See Also:
            JdbcOperations.queryForList(String, Class), SingleColumnRowMapper
    Well in my dao method I have

     String sql = "SELECT ccc.* FROM clientecuentacabecera ccc, cliente c, Moneda m "+
    		 "WHERE ccc.idCliente=c.idCliente "+
    		 "AND ccc.idMoneda=m.idMoneda " +
    		 "AND c.idCliente=? "+
    		 "ORDER BY ccc.idClienteCuentaCabecera";
     List<ClienteCuentaCabecera> clientesCuentaCabecera = new ArrayList<ClienteCuentaCabecera>();
     clientesCuentaCabecera = jdbcTemplate.queryForList(sql,
       			                   	          new Object[]{idCliente},
    But when I execute the code I get

     	[org.springframework.batch.core.repository.dao.JdbcStepExecutionDao] - 
    	<Truncating long message before update of StepExecution, original message is: 
    	Incorrect column count: expected 1, actual 6
    Until here, question number

    one: What is wrong?
    elementType is ClienteCuentaCabecera.class

    I did a research in Google and I found more examples working with the List<Map<String,Object>> approach,
    where the developer later must work with the Map and "get" each field ....

    Well it is acceptable for small pojos, but not for a pojo with 40/50 variables, think about of a Car object where it
    contains a lot of possible variables.

    I did again a research in the JdbcTemplate API and found the query method, it has a lot of varations.
    Well, I have chosen this.

    <T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper)

    Well, If I use

      clientesCuentaCabecera = jdbcTemplate.query(sql, 
    					        new Object[]{idCliente}, 
    					        new ClienteCuentaCabeceraRowMapper());

    Question number

    two: is possible with the queryForList method do the same than query method about get my expected List<ClienteCuentaCabecera>?

    perhaps I am missing something when I work with queryForList?

    Thanks in advanced
    Last edited by dr_pompeii; Oct 31st, 2012, 10:16 AM.

  • #2
    queryForList is for simple types only (Integer, String, Long)... Spring has no way to know how to construct your object, that is what the query method is for.


    • #3
      Thanks for the Advice.

      queryForList is for simple types only (Integer, String, Long)...
      Something like that appear in the JdbcTemplate API?

      Spring has no way to know how to construct your object, that is what the query method is for.