辽宁省交通建设投资集团网站,最近的国际新闻,银川森淼生态园,如何分析网站设计目录1. UserMapper接口1. sql-if2. sql-foreach3. 多条件查询和根据多个id查询测试say sth..
每当我回顾以前写jdbc分页#xff0c;多条件查询时#xff0c;我那是有多痛苦。
特别是当我拼接条件时#xff0c;对象.getName 又是判断null#xff0c;又是判断空字符串的。
还…
目录1. UserMapper接口1. sql-if2. sql-foreach3. 多条件查询和根据多个id查询测试say sth..
每当我回顾以前写jdbc分页多条件查询时我那是有多痛苦。
特别是当我拼接条件时对象.getName 又是判断null又是判断空字符串的。
还得拿一个集合来装条件。以及列名后面的逗号where 条件的空格。
我/(ㄒoㄒ)/~~
搞得我特别压抑好了现在mybatis的sql标签来甜我了。\(︶*\))jdbc规范下的动态查询
1. UserMapper接口
user表字段userId userName userPassword
查询涉及到- where不确定条件查询 sql-if- in 多条件查询- 不提供实体类关于mybatis环境搭建关于代理开发接口实现
package cn.bitqian.dao;import cn.bitqian.entity.User;import java.util.List;/*** author echo lovely* date 2020/9/11 15:36*/
public interface UserMapper {public ListUser queryUserByCondition(User user);public ListUser queryUserByIds(ListInteger ids);
}
1. sql-if
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtdmapper namespacecn.bitqian.dao.UserMapper!--sql 片段的抽取 statement标签内使用include标签进行引用--sql idselectUserPartselect * from users1/sql!-- 根据id or name or pwd 查询 --select idqueryUserByCondition parameterTypeuser resultTypeuserinclude refidselectUserPart/includewhereif testuserId ! 0 and userId ! nulland userid #{userId}/ifif testuserName ! nulland username #{userName}/ifif testuserPassword ! nulland userpassword #{userPassword}/if/where/select
/mapper2. sql-foreach
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtdmapper namespacecn.bitqian.dao.UserMapperselect idqueryUserByIds parameterTypelist resultTypeuserselect * from users1 !-- where userid in (1, 2, 3) --where!--collectionarrayopen, end 拼接sql写死的片段separator 为分隔符item 为list中的每个元素--foreach collectionlist openuserid in ( close) separator, itemuserId#{userId}/foreach/where/select/mapper3. 多条件查询和根据多个id查询测试
import cn.bitqian.dao.UserMapper;
import cn.bitqian.entity.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;/*** 动态sql测试* author echo lovely* date 2020/9/11 15:50*/
public class DynamicDemoTest {public SqlSession getSqlSession() {try {InputStream resourceAsStream Resources.getResourceAsStream(mybatis-config.xml);SqlSessionFactory sqlSessionFactory new SqlSessionFactoryBuilder().build(resourceAsStream);// 默认提交事务return sqlSessionFactory.openSession(true);} catch (IOException e) {e.printStackTrace();}return null;}Testpublic void test1() {SqlSession sqlSession getSqlSession();if (sqlSession ! null) {UserMapper userMapper sqlSession.getMapper(UserMapper.class);User user new User();// user.setUserId(1);user.setUserName(rose);user.setUserPassword(love jack);ListUser userList userMapper.queryUserByCondition(user);System.out.println(userList);sqlSession.close();}}Testpublic void test2() {SqlSession sqlSession getSqlSession();if (sqlSession ! null) {UserMapper userMapper sqlSession.getMapper(UserMapper.class);ArrayListInteger list new ArrayList();list.add(1);list.add(3);// 查询user id 为1或者为2的数据记录System.out.println(userMapper.queryUserByIds(list));sqlSession.close();}}}
