在昨天的基础上完成多条件查询 , 修改 的动态SQL编写并测试
1.多条件查询
OwnerMapper.java
// 动态查询
public List findOwnerByIdOrName(Owner owner);
OwnerMapper.xml
select * from ostore and id = #{id} and owner = #{shop_owner}
OwnerMapperTest.java
@Test
public void findOwnerByIdOrName() throws IOException {
InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);
SqlSession sqlSession = factory.openSession();
OwnerMapper mapper = sqlSession.getMapper(OwnerMapper.class);
Owner owner = new Owner();
owner.setName("张三");
owner.setId(5);
List ownerByIdOrName = mapper.findOwnerByIdOrName(owner);
sqlSession.commit();
for (Owner o : ownerByIdOrName){
System.out.println(o);
}
}
结果:
放法2
OwnerMapper.java
public ListfindByIdAndOwneChoose(Owner owner);
OwnerMapper.xml
OwnerMapperTest.java
@Test
public void findByIdAndOwneChoose() throws IOException {
InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);
SqlSession sqlSession = factory.openSession();
OwnerMapper mapper = sqlSession.getMapper(OwnerMapper.class);
Owner owner = new Owner();
owner.setName("张三");
owner.setId(5);
List ownerByIdOrName = mapper.findByIdAndOwneChoose(owner);
sqlSession.commit();
for (Owner o : ownerByIdOrName){
System.out.println(o);
}
}
2.动态修改
OwnerMapper.java
// 动态修改
public void updateOwner(Owner owner);
OwnerMapper.xml
update ostore
owner = #{shop_owner}
oname = #{name}
oindustry = #{industry}
oarea = #{area}
ophone = #{phone}
ostatus = #{statue}
otime = #{audit_time}
where onumber = #{id_number}
OwnerMapperTest.java
//动态修改
@Test
public void updateOwnerTest() throws IOException {
InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);
SqlSession sqlSession = factory.openSession();
OwnerMapper mapper = sqlSession.getMapper(OwnerMapper.class);
Owner owner = new Owner();
owner.setId_number("123456789");
owner.setShop_owner("呵呵哒");
mapper.updateUser(owner);
sqlSession.commit();
}
结果:
3.动态循环遍历查询
3.1集合
OwnerMapper.java
public ListfindOwnerByList(List ids);
OwnerMapper.xml
OwnerMapperTest.java
@Test
public void findOwnerByListTest() throws IOException {
InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);
SqlSession sqlSession = factory.openSession();
OwnerMapper mapper = sqlSession.getMapper(OwnerMapper.class);
ArrayList list = new ArrayList<>();
list.add(1);
list.add(2);
List ownerByList = mapper.findOwnerByList(list);
for (Owner o : ownerByList){
System.out.println(o);
}
}
结果
3.2数组
OwnerMapper.java
public ListfindOwnerByArray(Integer[] ids);
OwnerMapper.xml
OwnerMapperTest.java
@Test
public void findOwnerByArrayTest() throws IOException {
InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);
SqlSession sqlSession = factory.openSession();
OwnerMapper mapper = sqlSession.getMapper(OwnerMapper.class);
Integer[] ids = {1,2,3};
List ownerByList = mapper.findOwnerByArray(ids);
for (Owner o : ownerByList){
System.out.println(o);
}
}
结果:
3.3 pojo
先在pojo文件夹下建一个QueryVO.java文件
public class QueryVO {
private List ids;
public QueryVO() {
}
public List getIds() {
return ids;
}
public void setIds(List ids) {
this.ids = ids;
}
}
SqlMapConfig.xml
OwnerMapper.java
public ListfindUserByVo(QueryVO queryVO);
OwnerMapper.xml
优化:这样可以减少代码的冗余
select id , owner , onumber , oname , oindustry , oarea , ophone , otime from ostore
OwnerMapperTest.java
@Test
public void findUserByVoTest() throws IOException {
InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);
SqlSession sqlSession = factory.openSession();
OwnerMapper mapper = sqlSession.getMapper(OwnerMapper.class);
ArrayList list = new ArrayList<>();
list.add(1);
list.add(2);
list.add(3);
QueryVO queryVO = new QueryVO();
queryVO.setIds(list);
List ownerByList = mapper.findUserByVo(queryVO);
for (Owner o : ownerByList){
System.out.println(o);
}
}



