向sql传递数组或List,mybatis使用foreach解析,如下:
根据多个id查询用户信息
查询sql:SELECT * FROM user WHERE id IN (10,22,24)
1.userMapper.xml
<!-- 多个ID查询用户 -->
<mapper namespace="cn.itcast.mybatis.mapper.UserMapper">
<!-- 声明sql片段 -->
<sql id="userFild">
select * from user
</sql>
<select id="selectUserByIds" parameterType="QueryVo" resultType="User">
<!-- 使用include标签加载sql片段;refid是sql片段id -->
<include refid="userFild"/>
<where>
id in
//方式一
<foreach collection="idsList" item="id" separator="," open="(" close=")">
//方式二
// <foreach collection="array" item="id" separator="," open="(" close=")">
//方式三
// <foreach collection="list" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</where>
</select>
</mapper>
2.userMapper.java
package cn.itcast.mybatis.mapper;
import java.util.List;
import cn.itcast.mybatis.pojo.QueryVo;
import cn.itcast.mybatis.pojo.User;
public interface UserMapper {
//根据多个id查询用户信息
//方式二:List<User> selectUserByIds(Integer[] ids);
//方式三:List<User> selectUserByIds(List<Integer> idsList);
//方式一
List<User> selectUserByIds(QueryVo vo);
}
3.QueryVo.java
package cn.itcast.mybatis.pojo;
import java.util.List;
public class QueryVo {
// 包含其他的pojo
private User user;
List<Integer> idsList;
Integer[] ids;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public List<Integer> getIdsList() {
return idsList;
}
public void setIdsList(List<Integer> idsList) {
this.idsList = idsList;
}
public Integer[] getIds() {
return ids;
}
public void setIds(Integer[] ids) {
this.ids = ids;
}
}
4.测试:MybatisMapperTest.java
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws Exception {
// 创建SqlSessionFactoryBuilder
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
// 加载SqlMapConfig.xml配置文件
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
// 创建SqlsessionFactory
this.sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
}
//根据多个id查询用户信息
@Test
public void testselectUserByIds() {
// mybatis和spring整合,整合之后,交给spring管理
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 创建Mapper接口的动态代理对象,整合之后,交给spring管理
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
/*方式二
Integer[] ids = new Integer[3];
ids[0] = 25;
ids[2] = 22;
ids[1] = 24;
List<User> users = userMapper.selectUserByIds(ids);
*/
//方式一、三
List<Integer> ids=new ArrayList<>();
ids.add(10);
ids.add(22);
ids.add(24);
QueryVo vo=new QueryVo();
vo.setIdsList(ids);
List<User> users = userMapper.selectUserByIds(vo);
for(User user2:users) {
System.out.println(user2);
}
}
}