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