spring jdbc之query详细实例1

  首发：http://inmethetiger.iteye.com/blog/1687442

 

  spring的jdbc里面对查询的的实现比较多，除了queryForXXX之外，还有重载了很多query方法。我根据这个写了一些query方法的小实例。

准备：

 

@BeforeClass
	public static void setUpClass() {
		String url = "jdbc:hsqldb:mem:test";
		String username = "sa";
		String password = "";
		DriverManagerDataSource dataSource = new DriverManagerDataSource(url,
				username, password);
		dataSource.setDriverClassName("org.hsqldb.jdbcDriver");
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	// 创建表结构
	@Before
	public void before() {
		String createTableSql = "create memory table test"
				+ "(id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "
				+ "name varchar(100))";
		jdbcTemplate.update(createTableSql);
	}

 

结果集处理回调

回调函数使用RowMapper的方法

1：<T> List<T> query(String sql, RowMapper<T> rowMapper)方法

 

根据给定的sql语句执行query方法。通过RowMapper将每行映射成为一个java对象。 使用一个JDBC

Statement，而不是使用预编译的的PrepareStatement。如果想用PrepareStatement执行 一个查询，使用重载的方法。

 

@Test
	public void SqlRowMapper() {

		jdbcTemplate.update("insert into test(name) values('name1')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name4')");

		String listSql = "select * from test";
		List<Map> objList = jdbcTemplate.query(listSql, new RowMapper<Map>() {
			@Override
			public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map row = new HashMap();
				row.put(rowNum, rs.getString("name"));
				return row;
			}

		});
		for (int i = 0; i < objList.size(); i++) {
			Object obj = objList.get(i);
			System.out.println(obj.toString());
		}
	}

 

如果想使用预编译的可以写成这个样子：使用这个方法，然后参数为空

 

@Test
	public void SqlRowMapper() {

		jdbcTemplate.update("insert into test(name) values('name1')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name4')");

		String listSql = "select * from test";
		List<Map> objList = jdbcTemplate.query(listSql,new Object{}{}, new RowMapper<Map>() {
			@Override
			public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map row = new HashMap();
				row.put(rowNum, rs.getString("name"));
				return row;
			}

		});
		for (int i = 0; i < objList.size(); i++) {
			Object obj = objList.get(i);
			System.out.println(obj.toString());
		}
	}

 

2：<T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper)

 

@Test
	public void SqlArgsRowMapper() {
		jdbcTemplate.update("insert into test(name) values('name1')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name4')");
		String listSql = "select * from test where name=?";

		List<Map> objList = jdbcTemplate.query(listSql,
				new Object[] { "name2" }, new RowMapper<Map>() {

					@Override
					public Map mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Map row = new HashMap();
						row.put(rowNum, rs.getString("name"));
						return row;
					}

				});
		for (int i = 0; i < objList.size(); i++) {
			Object obj = objList.get(i);
			System.out.println(obj.toString());
		}

	}

 

 

3：<T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper)

 

@Test
	public void SqlArgsArgsTypeRowMapper() {
		jdbcTemplate.update("insert into test(name) values('name1')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name4')");
		String listSql = "select * from test where name=?";

		List<Map> objList = jdbcTemplate.query(listSql,
				new Object[] { "name2" },new int[]{java.sql.Types.ARRAY}, new RowMapper<Map>() {
					@Override
					public Map mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Map row = new HashMap();
						row.put(rowNum, rs.getString("name"));
						return row;
					}

				});

		for (int i = 0; i < objList.size(); i++) {
			Object obj = objList.get(i);
			System.out.println(obj.toString());
		}

	}

 

4：<T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args)

 

@Test
	public void SqlRowMapperArgs() {
		jdbcTemplate.update("insert into test(name) values('name1')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name4')");
		String listSql = "select * from test where name=?";

		List<Map> objList = jdbcTemplate.query(listSql, new RowMapper<Map>() {

			@Override
			public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map row = new HashMap();
				row.put(rowNum, rs.getString("name"));
				return row;
			}

		}, "name2");
		for (int i = 0; i < objList.size(); i++) {
			Object obj = objList.get(i);
			System.out.println(obj.toString());
		}

	}

 

5：<T> List<T> query(PreparedStatementCreator psc, RowMapper<T> rowMapper)

 

@Test
	public void PreRowMapper() {
		jdbcTemplate.update("insert into test(name) values('name1')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name4')");
		final String listSql = "select * from test where name=?";
		List<Map> objList = jdbcTemplate.query(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement pstmt = con.prepareStatement(listSql);
				pstmt.setString(1, "name2");
				return pstmt;
			}
		}, new RowMapper<Map>() {
			@Override
			public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
				Map row = new HashMap();
				row.put(rowNum, rs.getString("name"));
				return row;
			}

		});
		for (int i = 0; i < objList.size(); i++) {
			Object obj = objList.get(i);
			System.out.println(obj.toString());
		}

	}

 

 

6：<T> List<T> query(String sql, PreparedStatementSetter pss, RowMapper<T> rowMapper)

 

@Test
	public void SqlPreRowMapper() {
		jdbcTemplate.update("insert into test(name) values('name1')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name2')");
		jdbcTemplate.update("insert into test(name) values('name4')");
		String listSql = "select * from test where name=?";

		List<Map> objList = jdbcTemplate.query(listSql,
				new PreparedStatementSetter() {
					@Override
					public void setValues(PreparedStatement ps)
							throws SQLException {
						ps.setString(1, "name2");
					}
				}, new RowMapper<Map>() {

					@Override
					public Map mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Map row = new HashMap();
						row.put(rowNum, rs.getString("name"));
						return row;
					}

				});
		for (int i = 0; i < objList.size(); i++) {
			Object obj = objList.get(i);
			System.out.println(obj.toString());
		}

	}
	
	

 

在spring的api中query方法中，涉及RowMapper的都写了一个简单的实例。

下一篇，是关于query方法中，涉及RowCallbackHandler的实例。总体来说类似。但是不同的是，返回值为空，并没有包装。