Spring JDBC 面向对象操作

Spring版本：2.5，不包括JDBC事务和存储过程

下面的代码中使用MappingSqlQuery的findObjectByNamedParam时候总是给我报错，报错的内容大致是：需要提供两个参数的值但是只提供了一个。而我的确是在Map里放置的两个值，如果谁能解释该问题欢迎留言。

package com.spring.xkey.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
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.SqlParameter;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.jdbc.object.SqlQuery;
import org.springframework.jdbc.object.SqlUpdate;

class SqlUser {
	private int userid;
	private String username;
	private String email;

	public void setUserid(int id) {
		userid = id;
	}

	public int getUserid() {
		return userid;
	}

	public void setUsername(String user) {
		username = user;
	}

	public String getUsername() {
		return username;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getEmail() {
		return email;
	}
}

class UserRowMapper implements RowMapper {
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        SqlUser user=new SqlUser();
        user.setUserid(rs.getInt("userid"));
        user.setUsername(rs.getString("username"));
        user.setEmail(rs.getString("email"));
        return user;
    }
}
class UserSqlQuery extends SqlQuery{

	public UserSqlQuery(){
		
	}
	public UserSqlQuery(JdbcTemplate jdbcTemplate,String sql){
		super.setJdbcTemplate(jdbcTemplate);
		super.setSql(sql);
		/**
		 * 使用declareParameter和setTypes方法均可
		 * */
		super.declareParameter(new SqlParameter(Types.VARCHAR));
		super.declareParameter(new SqlParameter(Types.INTEGER));
		 int[] types = {Types.VARCHAR,Types.INTEGER };
	    //super.setTypes(types);
		super.compile();
	}
	@Override
	protected RowMapper newRowMapper(Object[] arg0, Map arg1) {
		// TODO Auto-generated method stub
		return new UserRowMapper();
	}
	
	public void testSqlQuery(JdbcTemplate jdbcTemplate) {  
		String sql = "select * from tb_user where username =?  and userid =?";
	    SqlQuery query = new UserSqlQuery(jdbcTemplate,sql); 
	   
	    List<SqlUser> result = query.execute(new Object[]{"xkey",1});   
	    for(Iterator it = result.iterator() ; it.hasNext() ;){
	    	SqlUser user = (SqlUser)it.next();
	    	System.out.println(user.getUserid()+user.getUsername()+user.getEmail());
	    }
	}  
	
}
class UserMappingSqlQuery extends MappingSqlQuery{
	public UserMappingSqlQuery(){
		
	}
	public UserMappingSqlQuery(JdbcTemplate jdbcTemplate,String sql){
		 super.setDataSource(jdbcTemplate.getDataSource()); 
		 super.setSql(sql);
		 super.declareParameter(new SqlParameter(Types.INTEGER));
		 super.declareParameter(new SqlParameter(Types.VARCHAR));
			
		//super.declareParameter(new SqlParameter("username",Types.VARCHAR));
		 //super.declareParameter(new SqlParameter("userid",Types.INTEGER));
		 super.compile();
	}
	public void testMappingSqlQuery(JdbcTemplate jdbcTemplate) {
		//String sql = "select * from tb_user where userid =: userid and username =:username";
		String sql = "select * from tb_user where userid =? and username =?";
		
		MappingSqlQuery query = new UserMappingSqlQuery(jdbcTemplate,sql);
		
		/**
		 * 这里为paramMap提供两个参数值，使用的sql语句是上面注释掉的
		 * */
		 Map<String, Object> paramMap = new HashMap<String, Object>();  
		 paramMap.put("username", "color");  
		 paramMap.put("userid", 4);
		 /*findObjectByNamedParam报错*/
		 //SqlUser result = (SqlUser) query.findObjectByNamedParam(paramMap); 
		 List<SqlUser> result = query.execute(new Object[]{4,"xkeycolor"});
		 System.out.println(result.size());
		 
		 
		 for(Iterator it = result.iterator() ; it.hasNext() ;){
		    	SqlUser user = (SqlUser)it.next();
		    	System.out.println(user.getUserid()+user.getUsername()+user.getEmail());
		    }
		 }
	@Override
	protected Object mapRow(ResultSet rs, int arg1) throws SQLException {
		SqlUser user=new SqlUser();
        user.setUserid(rs.getInt("userid"));
        user.setUsername(rs.getString("username"));
        user.setEmail(rs.getString("email"));
        return user;
	}
	
	public void testSqlUpdate(JdbcTemplate jdbcTemplate){
		/**
		 * insert update delete语句都类似
		 * */
		String sql = "update tb_user set username = ?, email = ? where userid = ?";
		SqlUpdate update = new SqlUpdate(jdbcTemplate.getDataSource(),
				sql,new int[]{Types.VARCHAR,Types.VARCHAR,Types.INTEGER});
		update.update(new Object[]{"xkeycolor","**********l@163.com",4});
	}
}

public class ObjectJdbc {

	private static JdbcTemplate jdbcTemplate;

	public void setUp() {
		String url = "jdbc:mysql://localhost:3306/Spring_Test";
		String username = "root";
		String password = "123456";
		DriverManagerDataSource dataSource = new DriverManagerDataSource(url,
				username, password);
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		ObjectJdbc jdbc = new ObjectJdbc();
		jdbc.setUp();
		UserSqlQuery query = new UserSqlQuery();
		query.testSqlQuery(jdbc.jdbcTemplate);
		UserMappingSqlQuery mq = new UserMappingSqlQuery();
		mq.testMappingSqlQuery(jdbcTemplate);
		//mq.testSqlUpdate(jdbcTemplate);
		//mq.testMappingSqlQuery(jdbcTemplate);
	}

}