Return Type for jdbcTemplate.queryForList(sql, object, classType)

Question

I'm executing a named query using jdbcTemplate.queryForList in the following manner:

List<Conversation> conversations = jdbcTemplate.queryForList(             SELECT_ALL_CONVERSATIONS_SQL_FULL,             new Object[] {userId, dateFrom, dateTo}); 

The SQL query is:

private final String SELECT_ALL_CONVERSATIONS_SQL_FULL =      "select conversation.conversationID, conversation.room, " +     "conversation.isExternal, conversation.startDate, " +     "conversation.lastActivity, conversation.messageCount " +     "from openfire.ofconversation conversation " +     "WHERE conversation.conversationid IN " +     "(SELECT conversation.conversationID " +     "FROM openfire.ofconversation conversation, " +     "openfire.ofconparticipant participant " +     "WHERE conversation.conversationID = participant.conversationID " +     "AND participant.bareJID LIKE ? " +     "AND conversation.startDate between ? AND ?)"; 

But when extracting the content of the list in the following manner:

for (Conversation conversation : conversations) { builder.append(conversation.getId());             builder.append(",");             builder.append(conversation.getRoom());             builder.append(",");             builder.append(conversation.getIsExternal());             builder.append(",");             builder.append(conversation.getStartDate());                         builder.append(",");                         builder.append(conversation.getEndDate());             builder.append(",");               builder.append(conversation.getMsgCount());             out.write(builder.toString());  } 

I get an error:

java.util.LinkedHashMap cannot be cast to net.org.messagehistory.model.Conversation 

How do I convert this linkedMap into the desired Object??

Thanks

Answer 1

In order to map a the result set of query to a particular Java class you'll probably be best (assuming you're interested in using the object elsewhere) off with a RowMapper to convert the columns in the result set into an object instance.

See Section 12.2.1.1 of Data access with JDBC on how to use a row mapper.

In short, you'll need something like:

List<Conversation> actors = jdbcTemplate.query(     SELECT_ALL_CONVERSATIONS_SQL_FULL,     new Object[] {userId, dateFrom, dateTo},     new RowMapper<Conversation>() {         public Conversation mapRow(ResultSet rs, int rowNum) throws SQLException {             Conversation c = new Conversation();             c.setId(rs.getLong(1));             c.setRoom(rs.getString(2));             [...]             return c;         }     }); 

Answer 2

A complete solution for JdbcTemplate, NamedParameterJdbcTemplate with or without RowMapper Example.

// Create a Employee table

create table employee(   id number(10),   name varchar2(100),   salary number(10)   ); 

======================================================================= //Employee.java

public class Employee { private int id;   private String name;   private float salary;    //no-arg and parameterized constructors    public Employee(){};  public Employee(int  id, String name, float salary){     this.id=id;     this.name=name;     this.salary=salary; }  //getters and setters   public int getId() {     return id; } public void setId(int id) {     this.id = id; } public String getName() {     return name; } public void setName(String name) {     this.name = name; } public float getSalary() {     return salary; } public void setSalary(float salary) {     this.salary = salary; } public String toString(){      return id+" "+name+" "+salary;   }     } 

========================================================================= //EmployeeDao.java

import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map;  import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;  public class EmployeeDao {     private JdbcTemplate jdbcTemplate;     private NamedParameterJdbcTemplate nameTemplate;      public void setnameTemplate(NamedParameterJdbcTemplate template) {       this.nameTemplate = template;    }      public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {     this.jdbcTemplate = jdbcTemplate;    }     // BY using JdbcTemplate  public int saveEmployee(Employee e){    int id = e.getId();  String name = e.getName();  float salary = e.getSalary();  Object p[] = {id, name, salary};     String query="insert into employee values(?,?,?)";       return jdbcTemplate.update(query, p);     /*String query="insert into employee     values('"+e.getId()+"','"+e.getName()+"','"+e.getSalary()+"')";        return jdbcTemplate.update(query);     */  }    //By using NameParameterTemplate public void insertEmploye(Employee e) {   String query="insert into employee values (:id,:name,:salary)";   Map<String,Object> map=new HashMap<String,Object>();   map.put("id",e.getId());   map.put("name",e.getName());   map.put("salary",e.getSalary());    nameTemplate.execute(query,map,new MyPreparedStatement());   } // Updating Employee public int updateEmployee(Employee e){   String query="update employee set  name='"+e.getName()+"',salary='"+e.getSalary()+"' where id='"+e.getId()+"' ";     return jdbcTemplate.update(query);    }  // Deleting a Employee row  public int deleteEmployee(Employee e){    String query="delete from employee where id='"+e.getId()+"' ";    return jdbcTemplate.update(query);    }    //Selecting Single row with condition and also all rows     public int selectEmployee(Employee e){        //String query="select * from employee where id='"+e.getId()+"' ";       String query="select * from employee";       List<Map<String, Object>> rows = jdbcTemplate.queryForList(query);        for(Map<String, Object> row : rows){           String id = row.get("id").toString();           String name = (String)row.get("name");           String salary = row.get("salary").toString();           System.out.println(id + " " + name + " " + salary );         }        return 1;    }       // Can use MyrowMapper class an implementation class for RowMapper interface     public void getAllEmployee()     {      String query="select * from employee";     List<Employee> l = jdbcTemplate.query(query, new MyrowMapper());      Iterator it=l.iterator();     while(it.hasNext())     {       Employee e=(Employee)it.next();       System.out.println(e.getId()+" "+e.getName()+" "+e.getSalary());     }    }      //Can use directly a RowMapper implementation class without an object creation   public List<Employee> getAllEmployee1(){     return jdbcTemplate.query("select * from employee",new RowMapper<Employee>(){         @Override         public Employee mapRow(ResultSet rs, int rownumber) throws  SQLException    {               Employee e=new Employee();               e.setId(rs.getInt(1));               e.setName(rs.getString(2));               e.setSalary(rs.getFloat(3));               return e;             }         });         }      // End of all the function       } 

================================================================ //MyrowMapper.java

 import java.sql.ResultSet;  import java.sql.SQLException;  import org.springframework.jdbc.core.RowMapper;   public class MyrowMapper implements RowMapper<Employee> {    @Override     public Employee mapRow(ResultSet rs, int rownumber) throws SQLException     {       System.out.println("mapRow()====:"+rownumber);     Employee e=new Employee();        e.setId(rs.getInt("id"));        e.setName(rs.getString("name"));        e.setSalary(rs.getFloat("salary"));        return e;         }     }  

========================================================== //MyPreparedStatement.java

import java.sql.PreparedStatement; import java.sql.SQLException; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.PreparedStatementCallback;    public class MyPreparedStatement implements  PreparedStatementCallback<Object> {   @Override  public Object doInPreparedStatement(PreparedStatement ps)         throws SQLException, DataAccessException {       return ps.executeUpdate();    }    }  

===================================================================== //Test.java

import java.util.List; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext;  public class Test {    public static void main(String[] args) {    ApplicationContext ctx=new     ClassPathXmlApplicationContext("applicationContext.xml");     EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");     // By calling constructor for insert  /*      int status=dao.saveEmployee(new Employee(103,"Ajay",35000));       System.out.println(status);    */  // By calling PreparedStatement   dao.insertEmploye(new Employee(103,"Roh",25000));    // By calling setter-getter for update  /*      Employee e=new Employee();      e.setId(102);     e.setName("Rohit");     e.setSalary(8000000);     int status=dao.updateEmployee(e); */  // By calling constructor for update  /*     int status=dao.updateEmployee(new Employee(102,"Sadhan",15000));      System.out.println(status);   */   // Deleting a record   /*           Employee e=new Employee();      e.setId(102);      int status=dao.deleteEmployee(e);      System.out.println(status);  */  // Selecting single or all rows  /*     Employee e=new Employee();      e.setId(102);     int status=dao.selectEmployee(e);     System.out.println(status); */ // Can use MyrowMapper class an implementation class for RowMapper interface      dao.getAllEmployee();  // Can use directly a RowMapper implementation class without an object creation  /*     List<Employee> list=dao.getAllEmployee1();       for(Employee e1:list)       System.out.println(e1);     */       }     }  

================================================================== //applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>   <beans    xmlns="http://www.springframework.org/schema/beans"    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xmlns:p="http://www.springframework.org/schema/p"    xsi:schemaLocation="http://www.springframework.org/schema/beans     http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">    <bean id="ds"        class="org.springframework.jdbc.datasource.DriverManagerDataSource">    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />    <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />    <property name="username" value="hr" />    <property name="password" value="hr" />    </bean>     <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">    <property name="dataSource" ref="ds"></property>    </bean>    <bean id="nameTemplate"    class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">   <constructor-arg ref="ds"></constructor-arg>   </bean>    <bean id="edao" class="EmployeeDao">  <!-- Can use both -->  <property name="nameTemplate" ref="nameTemplate"></property> <property name="jdbcTemplate" ref="jdbcTemplate"></property>  </bean>   

===================================================================