JdbcTemplate in()传参

 

---

 

1. 实体类

import java.util.List;

public class Param {

    private List<String> names;
    private List<String> depts;
    //gettes/setters略 

}

public class Employee {

    private Long id;
    private String name;
    private String dept;
    // getters/setters略  

}

 

2. JdbcTemplate in()传参代码

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class JdbcTempleteInParam {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 示例
     */
    public List<Employee> getEmployees(Param param) {
        // 定义结果集
        List<Employee> result = null;

        // 拼接sql
        String sql = "select * from employee where 1=1 ";

        // 参数
        Map<String, Object> paramMap = new HashMap<String, Object>();
        if (null != param) {
            if (null != param.getNames() && param.getNames().size() > 0) {
                sql += "and name in (:names) ";
                paramMap.put("names", param.getNames());
            }

            if (null != param.getDepts() && param.getDepts().size() > 0) {
                sql += "and dept in (:depts) ";
                paramMap.put("depts", param.getDepts());
            }
        }

        // 查询
        NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
        result = jdbc.query(sql, paramMap, new RowMapper<Employee>() {
            @Override
            public Employee mapRow(ResultSet rs, int index) throws SQLException {
                Employee emp = new Employee();
                emp.setId(rs.getLong("id"));
                emp.setName(rs.getString("name"));
                emp.setDept(rs.getString("dept"));
                return emp;
            }
        });

        // 返回查询结果
        return result;
    }

}