查询 · Spring6.x（1）

查询常用方法如下： <br/> **1. `Map<String, Object> queryForMap(String sql, Map<String, ?> paramMap)`** ```java String sql = "select id,name,age,gender from student where id=:id"; Map<String, Object> params = new HashMap<>(); params.put("id", 53); Map<String, Object> student = jdbcTemplate.queryForMap(sql, params); ``` **2. `Map<String, Object> queryForMap(String sql, SqlParameterSource paramSource)`** ```java String sql = "select id,name,age,gender from student where id=:id"; Student params = new Student(); params.setId(53); Map<String, Object> student = jdbcTemplate.queryForMap(sql, new BeanPropertySqlParameterSource(params)); ``` **3. `<T> List<T> query(String sql, RowMapper<T> rowMapper)`** ```java String sql = "select id,name,age,gender from student"; List<Student> students = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Student.class)); ``` **4. `<T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper)`** ```java String sql = "select id,name,age,gender from student where age >= :age and gender=:gender"; Map<String, Object> params = new HashMap<>(); params.put("age", 20); params.put("gender", "男"); List<Student> students = jdbcTemplate.query(sql, params, new BeanPropertyRowMapper<>(Student.class)); ``` **5. `<T> List<T> query(String sql, SqlParameterSource paramSource, RowMapper<T> rowMapper)`** ```java String sql = "select id,name,age,gender from student where age >= :age and gender=:gender"; Student params = new Student(); params.setAge(20); params.setGender("男"); List<Student> students = jdbcTemplate.query(sql, new BeanPropertySqlParameterSource(params) , new BeanPropertyRowMapper<>(Student.class)); ``` **6. `List<Map<String, Object>> queryForList(String sql, Map<String, ?> paramMap)`** ```java String sql = "select id,name,age,gender from student where age >= :age and gender=:gender"; Map<String, Object> params = new HashMap<>(); params.put("age", 20); params.put("gender", "男"); List<Map<String, Object>> students = jdbcTemplate.queryForList(sql, params); ``` **7. `List<Map<String, Object>> queryForList(String sql, SqlParameterSource paramSource)`** ```java String sql = "select id,name,age,gender from student where age >= :age and gender=:gender"; Student params = new Student(); params.setAge(20); params.setGender("男"); List<Map<String, Object>> students = jdbcTemplate.queryForList(sql, new BeanPropertySqlParameterSource(params)); ``` **8. 分页查询** `<T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper)` ```java //当前页 int page = 1; //页面大小 int size = 10; String sql = "select id,name,age,gender from student where gender=:gender limit :page,:size"; Map<String, Object> params = new HashMap<>(); params.put("gender", "男"); params.put("page", (page - 1) * size); params.put("size", size); List<Student> dataList = jdbcTemplate.query(sql, params, new BeanPropertyRowMapper<>(Student.class)); ``` **9. Oracle的分页查询语句** ```sql SELECT * FROM (SELECT A.*,ROWNUM AS RN FROM (SELECT * FROM ${tableName} WHERE 1=1 -- 条件 ORDER BY CREATETIME DESC,ID -- 排序 ) A WHERE A.ROWNUM <= #{pageNum}*#{pageSize} ) B WHERE B.RN > #{pageNum-1}*#{pageSize} /* 由于oracle排序算法问题，如果排序遇到相同的条件，比如时间，会使分页后一页包含前一页的内容，所以这个时候添加一个唯一的字段，比如ID。 */ ```