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

[dr_pompeii]

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)
	
)ENGINE=InnoDB;

And I have this class too

@SuppressWarnings("serial")
public class ClienteCuentaCabecera implements Serializable{

	private String idClienteCuentaCabecera;
	private DateTime fechaCreacionClienteCuentaCabecera;
	private Integer totalDisponibleClienteCuentaCabecera;
	private String estadoClienteCuentaCabecera;
	
	private Cliente cliente;	
	private Moneda moneda;
	
	//setters/getters
	
	//toString()
}

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

@Override
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

  queryForList

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

    Parameters:
        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) 
    Returns:
        a List of objects that match the specified element type 
    Throws:
        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},
			                   	          ClienteCuentaCabecera.class
						           		);

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: 
	org.springframework.jdbc.IncorrectResultSetColumnCountException: 
	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());

work.

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

[Marten Deinum]

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.

[dr_pompeii]

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.

Confirmed.

Thanks