- package com.test;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import org.springframework.jdbc.core.JdbcTemplate;
- import org.springframework.jdbc.core.RowMapper;
- import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
- import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
- import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
- import com.java.common.util.BeanFactory;
- import com.java.ssmb.entity.domain.UserInfo;
- /**
- * 使用NamedParameterJdbcTemplate查询条件sql中in查询和javabean查询
- * 一般spring项目中都存在JdbcTemplate,
- * 例子使用JdbcTemplate创建NamedParameterJdbcTemplate
- *
- * @author yeshun
- *
- */
- public class NamedParameterJdbcTemplateTest {
- private NamedParameterJdbcTemplate namedJdbcTemplate;
- public NamedParameterJdbcTemplateTest(){
- JdbcTemplate jdbcTemplate = BeanFactory.getBean("jdbcTemplate",JdbcTemplate.class);
- this.namedJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
- }
- /*
- * 使用map查询
- */
- public void queryListByMap(List<String> userids){
- String sql = "select * from user_table u where u.user_id in (:userids )";
- Map<String,Object> parameters = new HashMap<String,Object>();
- parameters.put("userids", userids);
- List<Map<String,Object>> list = namedJdbcTemplate.queryForList(sql, parameters);
- for (Map<String, Object> map : list) {
- System.out.println("-------------------");
- System.out.println(map.get("user_id"));
- System.out.println(map.get("user_name"));
- }
- }
- /*
- * 使用MapSqlParameterSource查询
- */
- public void queryListByMapSqlParameterSource(List<String> userids){
- String sql = "select * from user_table u where u.user_id in (:userids )";
- MapSqlParameterSource parameters = new MapSqlParameterSource();
- parameters.addValue("userids", userids);
- List<Map<String,Object>> list = namedJdbcTemplate.queryForList(sql, parameters);
- for (Map<String, Object> map : list) {
- System.out.println("-------------------");
- System.out.println(map.get("user_id"));
- System.out.println(map.get("user_name"));
- }
- }
- /*
- * 使用BeanPropertySqlParameterSource查询,javabean查询
- */
- public void querySingleByBeanPropertySqlParameterSource(){
- //:userId -- 对应UserInfo.userId
- String sql = "select * from user_table u where u.user_id = :userId ";
- UserInfo user = new UserInfo();
- user.setUserId("user0");
- BeanPropertySqlParameterSource parameters = new BeanPropertySqlParameterSource(user);
- Map<String,Object> map = namedJdbcTemplate.queryForMap(sql, parameters);
- System.out.println(map.get("user_id"));
- System.out.println(map.get("user_name"));
- }
- /*
- * 查询单个返回值
- */
- public void querySingle(List<String> userids){
- String sql = "select count(*) from user_table u where u.user_id in (:userids )";
- MapSqlParameterSource parameters = new MapSqlParameterSource();
- parameters.addValue("userids", userids);
- Integer count = namedJdbcTemplate.queryForObject(sql, parameters, Integer.class);
- // queryForInt
- // Integer count = namedJdbcTemplate.queryForInt(sql, parameters);
- System.out.println(count);
- }
- /*
- * 使用MapSqlParameterSource查询,使用RowMapper返回
- */
- public void queryUsersByMapSqlParameterSource(List<String> userids){
- String sql = "select * from user_table u where u.user_id in (:userids )";
- MapSqlParameterSource parameters = new MapSqlParameterSource();
- parameters.addValue("userids", userids);
- List<UserInfo> userList = namedJdbcTemplate.query(sql, parameters,new RowMapper<UserInfo>(){
- @Override
- public UserInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
- // TODO Auto-generated method stub
- UserInfo user = new UserInfo();
- user.setUserId(rs.getString("user_id"));
- user.setUserName(rs.getString("user_name"));
- user.setPassword(rs.getString("password"));
- return user;
- }
- });
- for (UserInfo userInfo : userList) {
- System.out.println(userInfo.getUserId()+"--"+userInfo.getUserName());
- }
- }
- public static void main(String[] args) {
- NamedParameterJdbcTemplateTest test = new NamedParameterJdbcTemplateTest();
- List<String> userids= new ArrayList<String>();
- userids.add("user0");
- userids.add("user1");
- userids.add("user2");
- userids.add("user3");
- // test.queryListByMap(userids);
- // test.queryListByMapSqlParameterSource(userids);
- // test.querySingle(userids);
- // test.querySingleByBeanPropertySqlParameterSource();
- test.queryUsersByMapSqlParameterSource(userids);
- }
- }
springjdbc-NamedParameterJdbcTemplate处理in查询
最新推荐文章于 2022-09-17 13:14:29 发布