(8)spring整合JDBC_基于RowMapper

约定不等于承诺〃 2022-04-15 23:52 149阅读 0赞

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映射就方便了。