使用spring的jdbcTemplate-----用JDBC模板查询数据库

JdbcTemplate类声明了几个重载的query()模板方法来控制整个查询过程，就像进行更新数据操作一样，通过实现PreparedStatementCreator和PreparedStatementSetter接口，也可以覆盖语句创建任务。

1，用RowCallbackHandler提取数据

RowCallbackHandler是允许处理结果集当前行的主要接口，JdbcTemplate中的一个query()方法能替你迭代结果集，并为每一行调用RowCallbackHandler，因此，对于返回结果集中的每一行，processRow()方法都会被调用一次

public class JdbcUserDao implements UserDao{

    public User findByUserId(int id){

        String sql ="select *from user where id =?";
        JdbcTemplate jdbcTemplate  = new JdbcTemplate(dataSource);

        final User user = new User();
        jdbcTemplate.query(sql,new Objcet[]{id},new RowCallbackHandler(){

            public void processRow(ResultSet rs)throws Exception{

                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
            }
        });

        return user;
    }
}

public class JdbcUserDao implements UserDao{

	public User findByUserId(int id){

		String sql ="select *from user where id =?";
		JdbcTemplate jdbcTemplate  = new JdbcTemplate(dataSource);
	
		final User user = new User();
		jdbcTemplate.query(sql,new Objcet[]{id},new RowCallbackHandler(){

			public void processRow(ResultSet rs)throws Exception{

				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
			}
		});

		return user;
	}
}

 

 

由于最多只能返回SQL查询结果中的一行记录，因此可以以局部变量的形式创建一个User对象，并从结果集中提取数据来设置它的属性，对于不止一行的结果集来说，应该将对象收集成一个列表，

2，用RowMapper提取数据

RowMapper比RowCallbackHandler更为通用，它的目的是将结果集的单独一行映射到自定义的对象上，因此它适用于单行和多行结果集，从重用的角度考虑，最好将RowMapper接口实现为一般通用的类，而非内部类，在这个接口的mapRow()方法中，必须构建表示行数据的对象，并将它作为方法的返回值

public class UserRowMapper implements RowMapper{

    public Object mapRow(ResultSet rs ,int rowNum)throws SQLException{

        User user  = new User();
        user.setId(rs.getInt("id"));
        user.setUsername(rs.getString("username"));
        user.setPassword(rs.getString("password"));

        return user;
    }
}

public class UserRowMapper implements RowMapper{

	public Object mapRow(ResultSet rs ,int rowNum)throws SQLException{

		User user  = new User();
		user.setId(rs.getInt("id"));
		user.setUsername(rs.getString("username"));
		user.setPassword(rs.getString("password"));

		return user;
	}
}

 

 

RowMapper可以用于单行或者多行的结果集，比如在像findByUserId()这样的方法中，查询唯一对象时，必须调用JdbcTemplate的queryForObject()方法，

public class JdbcUserDao implements UserDao{

    ...........

    public User findByUserId(int id){

        String sql ="select *from user where id =?";
        JdbcTemplate jdbcTemplate  = new JdbcTemplate(dataSource);

        User user =(User)jdbcTemplate.queryForObject(sql,new Object[]{id},new UseRowMapper());
        return user;
    }
}

public class JdbcUserDao implements UserDao{

	...........
	
	public User findByUserId(int id){

		String sql ="select *from user where id =?";
		JdbcTemplate jdbcTemplate  = new JdbcTemplate(dataSource);

		User user =(User)jdbcTemplate.queryForObject(sql,new Object[]{id},new UseRowMapper());
		return user;
	}
}

 

 

spring2.5提供了一个更便利的RowMapper实现--BeanPropertyRowMapper，它可以自动的将某一行数据映射到指定类的新实例中，它首先将这个类实例化，然后通过名称匹配的方法，将每个列的值都映射到属性中。

public class JdbcUserDao implements UserDao{

    public User findByUserId(int id){

        String sql ="select *from user where id = ?";
        JdbcTemplate jdbcTemplate  = new JdbcTemplate(dataSource);

        User user =(User)jdbcTemplate.queryForObject(sql,new Object[]                                   {id},BeanPropertyRowMapper.newInstance(User.class));
        return user;
    }
}

public class JdbcUserDao implements UserDao{

	public User findByUserId(int id){

		String sql ="select *from user where id = ?";
		JdbcTemplate jdbcTemplate  = new JdbcTemplate(dataSource);
		
		User user =(User)jdbcTemplate.queryForObject(sql,new Object[]									{id},BeanPropertyRowMapper.newInstance(User.class));
		return user;
	}
}

 

 

3,查询多行

现在我们来看看如何查询一个带有多行记录的结果集。

public interface UserDao{

    public List<User> findAll();
}

public interface UserDao{

	public List<User> findAll();
}

 

 

没哟RowMapper的帮助，仍然可以调用queryForList()方法，传递一个sql语句，返回的结果集是Map列表，每个Map保存结果集中的一个行，用列名称做键。

public class JdbcUserDao implements UserDao{

    public List<User> findAll(){

        String sql ="select *from user";
        JdbcTemplate jdbcTemplate  = new JdbcTemplate(dataSource);

        List<User> lists = new ArrayList<User>();
        List<Map> rows = jdbcTemplate.queryForList(sql);

        for(Map row: rows){

            User user  = new User();
            user.setId((int)row.get("id"));
            user.setUsername((String)row.get("username"));
            user.setPasword((String)row.get("password"));
            lists.add(user);
        }
        return lists;
    }
}

public class JdbcUserDao implements UserDao{

	public List<User> findAll(){

		String sql ="select *from user";
		JdbcTemplate jdbcTemplate  = new JdbcTemplate(dataSource);
		
		List<User> lists = new ArrayList<User>();
		List<Map> rows = jdbcTemplate.queryForList(sql);

		for(Map row: rows){

			User user  = new User();
			user.setId((int)row.get("id"));
			user.setUsername((String)row.get("username"));
			user.setPasword((String)row.get("password"));
			lists.add(user);			
		}
		return lists;
	}
}

 

 

通过下面的代码测试findAll方法

public class Main{

    public static void main(String[] args){

        UserDao userdao = new JdbcUserDao();
        List<User> user =userdao.findAll();

        for(User u: user){

            System.out.println("id :"+user.getId());
            System.out.println("username :"+user.getUsername());
            System.out.println("password :"+user.getPassword());
        }
    }
}

public class Main{

	public static void main(String[] args){

		UserDao userdao = new JdbcUserDao();
		List<User> user =userdao.findAll();
		
		for(User u: user){

			System.out.println("id :"+user.getId());
			System.out.println("username :"+user.getUsername());
			System.out.println("password :"+user.getPassword());
		}
	}
}

 

 

如果使用RowMapper对象映射结果对象中的数据行，就能从query()方法中得到一个映射对象的列表，

public class JdbcUserDao implements UserDao{

    public List<User> findAll(){

        String sql = "select *from user";
        JdbcTemplate jdbcTemplate  = new JdbcTemplate(dataSource);

        List<User> users = jdbcTemplate.query(sql,BeanPropertyRowMapper.newInstance(User.class));
        return users;
    }
}

public class JdbcUserDao implements UserDao{

	public List<User> findAll(){

		String sql = "select *from user";
		JdbcTemplate jdbcTemplate  = new JdbcTemplate(dataSource);

		List<User> users = jdbcTemplate.query(sql,BeanPropertyRowMapper.newInstance(User.class));
		return users;
	}
}

 

 

4,查询单值

最后，看看如何查询单行和单列的结果集。

public interface UserDao{

    public String getUsername(int id);
    public int countAll();
}

public interface UserDao{

	public String getUsername(int id);
	public int countAll();
}

 

 

要查询单独的字符串值，可以调用重载的queryForObject方法，对于整型值而言，可以调用queryForInt()方法，

public class JdbcUserDao implements UserDao{

    public String getUsername(int id){

        String sql = "select username from user where id =?";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        String username =(String)jdbcTempalte.queryForObject(sql,new Object[]{id},String.class);
        return username;
    }

    public int countAll(){

        String sql ="select count(*) from user";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        int count = jdbcTemplate.queryForInt(sql);
        return count;
    }
}

public class JdbcUserDao implements UserDao{

	public String getUsername(int id){

		String sql = "select username from user where id =?";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

		String username =(String)jdbcTempalte.queryForObject(sql,new Object[]{id},String.class);
		return username;
	}

	public int countAll(){

		String sql ="select count(*) from user";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		int count = jdbcTemplate.queryForInt(sql);
		return count;
	}
}

 

 

可以通过下面的代码测试上面的数据

public class Main{

    public static void main(String[] args){

        UserDao dao =new JdbcUserDao();
        int count = dao.countAll();
        System.out.println("user count :"+count);
        String username = dao.getUsername(1);
        System