jdbcTemplate.queryForList(sql, object, classType) 的返回类型

Question

我按以下方式使用 jdbcTemplate.queryForList 执行命名查询：

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

SQL 查询是：

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 ?)";

但是当按以下方式提取列表的内容时：

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()); 
}

我收到错误：

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

如何将此 linkedMap 转换为所需的对象？ ？

谢谢

Answer 1

为了将查询的结果集映射到特定的 Java 类，您可能最好（假设您有兴趣在其他地方使用该对象）使用 RowMapper 将结果集中的列转换为对象实例。

请参阅使用 JDBC 进行数据访问的第 12.2.1.1 节如何使用行映射器。

简而言之，您需要类似的东西：

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

JdbcTemplate、NamedParameterJdbcTemplate 的完整解决方案（带或不带 RowMapper 示例）。

// 创建员工表

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>  

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

Answer 3

queryForList 返回 LinkedHashMap 对象的列表。

您需要先像这样投射它：

    List list = jdbcTemplate.queryForList(...);
    for (Object o : list) {
       Map m = (Map) o;
       ...
    }

Answer 4

List<Map<String, Object>> List = getJdbcTemplate().queryForList(SELECT_ALL_CONVERSATIONS_SQL_FULL, new Object[] {userId, dateFrom, dateTo});
for (Map<String, Object> rowMap : resultList) {
    DTO dTO = new DTO();
    dTO.setrarchyID((Long) (rowMap.get("ID")));
}

Answer 5

List<Conversation> conversations = **jdbcTemplate**.**queryForList**(
            **SQL_QUERY**,
            new Object[] {userId, dateFrom, dateTo});  //placeholders values

认为
sql 查询就像

SQL_QUERY = "**select** info,count(*),IF(info is null , 'DATA' , 'NO DATA') **from** table where userId=? , dateFrom=? , dateTo=?";

**HERE userId=? , dateFrom=? , dateTo=?**

问号是占位符

**SQL_QUERY**,
            new Object[] {userId, dateFrom, dateTo});

它将作为对象数组与 sql 查询一起出现