动态SQL 学习
时间：2021-07-01 10:21:17
帮助过：11人阅读
-
<select id="findUser" parameterType="Map" resultType="User">
Select * from User where 1=1
<if test="id>0">
and id = #{id}
</if>
<if test="name!=null">
and name like #{name}
</if>
<if test="pwd!=null">
and pwd = #{pwd}
</if>
<if test="address_id>0">
and address_id = #{address_id}
</if>
</select>
-
private SqlSession sqlSession ;
private PersonDao persondao;
private Logger log = Logger.getLogger(JunitTest.class);
@Before
public void setUp() throws Exception {
sqlSession = SqlSessionFactoryUtil.openSession();
persondao = sqlSession.getMapper(PersonDao.class);
}
@After
public void tearDown() throws Exception {
sqlSession.close();
}
@Test
public void findUser() {
Map<String, Object> map = new HashMap<String, Object>();
// map.put("id", 1);
map.put("name", "张%");
// map.put("pwd", "111111");
// map.put("address_id", 1);
List<User> list = persondao.findUser(map);
for (User user : list) {
log.info(user);
// System.out.println(user);
}
}
- 增删改 返回的是影响的行号
-
/**
* by 用法
*/
@Test
public void findUser2() {
Map<String, Object> map = new HashMap<String, Object>();
map.put("by", "name");
map.put("id", 1);
map.put("name", "张%");
map.put("pwd", "111111");
map.put("address_id", 1);
List<User> list = persondao.findUser2(map);
for (User user : list) {
// log.info(user);
System.out.println(user);
}
}
-
<select id="findUser2" parameterType="Map" resultType="User">
select * from User where 1=1
<choose>
<when test="by==‘id‘">
and id = #{id}
</when>
<when test="by==‘name‘">
and name like #{name}
</when>
<when test="by==‘pwd‘">
and pwd = #{pwd}
</when>
<otherwise>
and address_id = #{address_id}
</otherwise>
</choose>
</select>
- Where 用法
-
select id="findUser3" parameterType="Map" resultType="User">
select * from user
<where>
<if test="id>0">
and id = #{id}
</if>
<if test="name!=null">
and name like #{name}
</if>
<if test="pwd!=null">
and pwd = #{pwd}
</if>
</where>
</select>
- Trim 用法
-
<select id="findUser4" parameterType="Map" resultType="User">
select * from User
<trim prefix="where" prefixOverrides="and|or">
<if test="id>0">
and id = #{id}
</if>
<if test="name!=null">
and name like #{name}
</if>
<if test="pwd!=null">
and pwd = #{pwd}
</if>
</trim>
- foreach 用法
-
<select id="findUser5" parameterType="Map" resultType="User">
select * from user
<where>
<if test="ids!=null">
and id in
<!-- 循环遍历的集合名称 ids -->
<foreach collection="ids" item="id" open="(" close=")"
separator=",">
#{id}
</foreach>
</if>
</where>
</select>
-
@Test
public void findUser5() {
Map< String,Object> map = new HashMap<String, Object>();
List<Integer> list = Arrays.asList(1,2,3,4,5);
map.put("ids", list);
List<User> list1 = persondao.findUser5(map);
for (User user : list1) {
// log.info(user);
System.out.println(user);
}
}
- limit 用法
- SELECT * from `user` LIMIT start,size
-
/**
* 逻辑分页处理
*/
@Test
public void test() {
//传递分页参数,offset : 开始位置 limite: 分页大小
RowBounds bounds = new RowBounds(0,10);
List<User> list = pagedao.findUserByPage(bounds);
for (User user : list) {
System.out.println(user);
}
}
-
/**
* 物理分页处理
*/
@Test
public void test2() {
//传递分页参数,offset : 开始位置 limite: 分页大小
Map<String, Object> map = new HashMap<String, Object>();
map.put("offset", 1);
map.put("limit", 10);
List<User> list = pagedao.findUserByPage2(map);
for (User user : list) {
System.out.println(user);
}
}
-
<!-- 物理分页 查询表中所有的数据 -->
<select id="findUserByPage2" resultType="User" parameterType="Map">
select * from user
<if test="offset!=null and limit!=null">
limit #{offset} , #{limit}
</if>
</select>
动态SQL 学习
标签：
