(8)spring整合JDBC_基于RowMapper

package spring.model;

public class User {
private int id;
private String username;
private String password;
private String nickname;
private Group group;



public Group getGroup() {
	return group;
}
public void setGroup(Group group) {
	this.group = group;
}
public User(String username, String password, String nickname) {
	super();
	this.username = username;
	this.password = password;
	this.nickname = nickname;
}
public int getId() {
	return id;
}
public void setId(int id) {
	this.id = id;
}
public String getUsername() {
	return username;
}
public void setUsername(String username) {
	this.username = username;
}
public String getPassword() {
	return password;
}
public void setPassword(String password) {
	this.password = password;
}
public String getNickname() {
	return nickname;
}
public void setNickname(String nickname) {
	this.nickname = nickname;
}
public User() {
}
@Override
public String toString() {
	return "User [id=" + id + ", username=" + username + ", password=" + password + ", nickname=" + nickname
			+ ", group=" + group + "]";
}



}

package spring.model;

public class Group {
private int id;
private String name;
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;
}

}

GroupDao:

@Repository("groupJdbcDao")
public class GroupDao implements IGroupDao {
private JdbcTemplate jdbcTemplate;
	@Override
	public void add(Group group) {
		// TODO 自动生成的方法存根
		final String INSERT_SQL = "insert into t_group (name) value(?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			
			@Override
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				// TODO 自动生成的方法存根
				 PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] {"id"});
		         ps.setString(1, group.getName());
		         return ps;
			}
		},keyHolder);
		group.setId(keyHolder.getKey().intValue());//获取数据库中id的值，为group对象设置id

	}
	
	@Resource
	public void setDataSource(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

}

UserDao:

@Repository("userJdbcDao")
public class UserDao implements IUserDao {
private JdbcTemplate jdbcTemplate;
	
	
	
	@Resource
	public void setDataSource(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}
	@Override
	public void add(User user) {
		jdbcTemplate.update("insert into t_user(username,password,nickname,gid) value (?,?,?,?)",
				user.getUsername(),user.getPassword(),user.getNickname(),user.getGroup().getId());
	}
	

	@Override
	public void update(User user) {
		// TODO 自动生成的方法存根
		jdbcTemplate.update("update user set username=?,password=?,nickname=? where id=?",
				user.getUsername(),user.getPassword(),user.getNickname(),user.getId());
	}

	@Override
	public void delete(int id) {
		// TODO 自动生成的方法存根
		jdbcTemplate.update("delete from user where id=?",id);
	}

	@Override
	public User load(int id) {
		// TODO 自动生成的方法存根
		String sql="select t1.id uid,t1.*,t2.* from t_user t1 left join t_group t2 on (t1.gid=t2.id) where t1.id=?";
		User u=(User)jdbcTemplate.queryForObject(sql, new Object[]{id},new UserMapper());
	    return u;
	}

	@Override
	public List<User> list(String sql,Object[] args) {//把数据库中数据映射到对象
		// TODO 自动生成的方法存根
		String Count="select count(*) from t_user";
		int count=jdbcTemplate.queryForObject(Count,Integer.class);
		System.out.println("count:"+count);
		String userSql="select username,nickname from t_user";
		List<Map<String, Object>> us=jdbcTemplate.queryForList(userSql);
		for(Map<String, Object> u:us) {
System.out.println(u.get("username")+","+u.get("nickname"));			
		}
		return jdbcTemplate.query(sql, args, new UserMapper());
	}
private class UserMapper implements RowMapper<User>{

	@Override
	public User mapRow(ResultSet rs, int rowNum) throws SQLException {
		// TODO 自动生成的方法存根
		Group g=new Group();
		g.setName(rs.getString("name"));
		g.setId(rs.getInt("gid"));
		User u=new User();
		u.setId(rs.getInt("uid"));
		u.setNickname(rs.getString("nickname"));
		u.setPassword(rs.getString("password"));
		u.setUsername(rs.getString("username"));
		u.setGroup(g);
		return u;
	}
	
}
}

注意：（1）.创建UserMapper类继承RowMapper<User>,然后实现关系映射

（2）.load传入的参数是Object[]{id}，然后传入UserMapper对象

（3）.jdbcTemplate.query(sql, args, new UserMapper());因为有UserMapper，实现把数据库数据转换为对象，所以返回的是List<T>,然后args是Object[]类型的

（4）.数据库表名不能和sql关键字一样，比如group就不行，得创建表名为t_group

 

测试类:

@Test
	public void testLoad() {
		User u=userJdbcDao.load(2);
		System.out.println(u);
	}
	
	@Test
	public void testList() {
		String sql="select t1.id uid,t1.*,t2.* from t_user t1 left join t_group t2 on (t1.gid=t2.id)";，
		List<User> us=userJdbcDao.list(sql, null);//无参数就传入null
		for(User u:us) {
			System.out.println(u);
		}
	}

注意：（1）.两个表连接在一起，有2个id，无法区分，所以把User的id设为uid，这样在UserDao的RowMapper映射就方便了。