(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,然后实现关系映射

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

（3）.jdbcTemplate.query(sql, args, new UserMapper());因为有UserMapper，实现把数据库数据转换为对象，所以返回的是List,然后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);
        }
    }