需求 查询id号 1,2,4,5,7的数据
Sql: select * from demo_user where id in (1,2,4,5,7…)
/*** 业务: 查询id号 1,2,4,5,7的数据*/&#64;Testpublic void testFindIn(){SqlSession sqlSession &#61; sqlSessionFactory.openSession();UserMapper2 userMapper2 &#61; sqlSession.getMapper(UserMapper2.class);//将数据封装为数组int[] ids &#61; {1,2,4,5,7};List<User> userList &#61; userMapper2.findIn(ids);System.out.println(userList);sqlSession.close();}
<select id&#61;"findIn" resultType&#61;"User">select * from demo_user where id in<foreach collection&#61;"array" open&#61;"(" close&#61;")"separator&#61;"," item&#61;"id">#{id}foreach>select>
/*** 业务: 查询id号 1,2,4,5,7的数据* 知识点: 数组转化时,需要使用包装类型.* 根源: 基本类型没有get/set方法* 包装类型是对象 对象中有方法*/&#64;Testpublic void testFindInList(){SqlSession sqlSession &#61; sqlSessionFactory.openSession();UserMapper2 userMapper2 &#61; sqlSession.getMapper(UserMapper2.class);//将数组转化为List集合Integer[] ids &#61; {1,2,4,5,7};List list &#61; Arrays.asList(ids);List<User> userList &#61; userMapper2.findInList(list);System.out.println(userList);sqlSession.close();}
List<User> findInList(List list);
<select id&#61;"findInList" resultType&#61;"User">select * from demo_user where id in<foreach collection&#61;"list" open&#61;"(" close&#61;")"separator&#61;"," item&#61;"id">#{id}foreach>select>
/*** 需求&#xff1a;* 查询id&#61;1,3,5,6,7 并且sex&#61;"男"的用户* Sql:* select * from demo_user where id in (1,3....)* and sex &#61; "男"*/&#64;Testpublic void testFindInMap(){SqlSession sqlSession &#61; sqlSessionFactory.openSession();UserMapper2 userMapper2 &#61; sqlSession.getMapper(UserMapper2.class);int[] ids &#61; {1,3,5,6,7};String sex &#61; "男";/* Map map &#61; new HashMap();map.put("ids",ids);map.put("sex",sex);*/List<User> userList &#61; userMapper2.findInMap(ids,sex);System.out.println(userList);sqlSession.close();}
<select id&#61;"findInMap" resultType&#61;"User">select * from demo_user where id in (<foreach collection&#61;"ids" item&#61;"id" separator&#61;",">#{id}foreach>)and sex &#61; #{sex}select>
说明: 用户传递了一个user对象, 要求根据user中不为null的属性查询数据.
例子1:
User {name:“张三”}
Sql: select * from demo_user where name &#61; “张三”
例子2:
User {name:“张三”, age: 18}
Sql: select * from demo_user where name &#61; “张三” and age&#61;18
/*** 动态Sql练习 根据对象中不为null的元素查询数据*/&#64;Testpublic void testSqlWhere(){User user &#61; new User(null,"黑熊精",3000, "男");SqlSession sqlSession &#61; sqlSessionFactory.openSession();UserMapper2 userMapper2 &#61; sqlSession.getMapper(UserMapper2.class);List<User> userList &#61; userMapper2.findSqlWhere(user);System.out.println(userList);sqlSession.close();}
User对象中的数据可能为null.但是如果sql不做处理,则将会把null当做参数.导致程序查询异常.
例子:
List<User> findSqlWhere(User user);
<select id&#61;"findSqlWhere" resultType&#61;"User">select * from demo_user<where><if test&#61;"id !&#61; null"> id &#61; #{id}if><if test&#61;"name !&#61; null">and name &#61; #{name}if><if test&#61;"age !&#61; null ">and age &#61; #{age}if><if test&#61;"sex !&#61; null ">and sex &#61; #{sex}if>where>select>
/*** 需求: 实现用户数据修改, 根据对象中不为null的数据完成修改操作*/&#64;Testpublic void testSqlSet(){SqlSession sqlSession &#61;sqlSessionFactory.openSession(true);UserMapper2 userMapper &#61; sqlSession.getMapper(UserMapper2.class);User user &#61; new User(1,"守山使者",3000, null);int rows &#61; userMapper.updateSqlSet(user);System.out.println("影响"&#43;rows&#43;"行");sqlSession.close();}
int updateSqlSet(User user);
<update id&#61;"updateSqlSet">update demo_user<set><if test&#61;"name !&#61;null"> name&#61;#{name}, if><if test&#61;"age !&#61;null"> age &#61; #{age}, if><if test&#61;"sex !&#61;null"> sex &#61; #{sex} if>set>where id &#61; #{id}update>
需求: 根据属性查询数据, 如果name有值 按照name查询,否则按照年龄查询,如果name,age都没有 按照sex查询
需求分析:
if(name !&#61;null ){name &#61; #{name}}else if( age !&#61;null){age &#61; #{age}}else{sex &#61; #{sex}}
&#64;Testpublic void testChoose(){SqlSession sqlSession &#61;sqlSessionFactory.openSession();UserMapper2 userMapper &#61; sqlSession.getMapper(UserMapper2.class);User user &#61; new User(null,null,null,"男");List<User> userList &#61; userMapper.findChoose(user);System.out.println(userList);sqlSession.close();}
List<User> findChoose(User user);
<select id&#61;"findChoose" resultType&#61;"User">select * from demo_user<where><choose><when test&#61;"name !&#61;null">name &#61; #{name}when><when test&#61;"age !&#61;null">age &#61; #{age}when><otherwise>sex &#61; #{sex}otherwise>choose>where>select>
表结构
表名
&#64;Data
&#64;Accessors(chain &#61; true)
&#64;NoArgsConstructor
&#64;AllArgsConstructor
public class Dog implements Serializable {private Integer dogId;private String dogName;
}
public class TestDog {private SqlSessionFactory sqlSessionFactory;&#64;BeforeEachpublic void init() throws IOException {String resource &#61; "mybatis/mybatis-config.xml";InputStream inputStream &#61;Resources.getResourceAsStream(resource);sqlSessionFactory &#61; new SqlSessionFactoryBuilder().build(inputStream);}//查询所有dog表的数据&#64;Testpublic void testResultMap(){SqlSession sqlSession &#61; sqlSessionFactory.openSession();DogMapper dogMapper &#61; sqlSession.getMapper(DogMapper.class);List<Dog> dogList &#61; dogMapper.findAll();System.out.println(dogList);sqlSession.close();}}
public interface DogMapper {List<Dog> findAll();
}
DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace&#61;"com.jt.mapper.DogMapper"><select id&#61;"findAll" resultMap&#61;"dogRM">select * from dogselect><resultMap id&#61;"dogRM" type&#61;"com.jt.pojo.Dog"><id column&#61;"dog_id" property&#61;"dogId"/><result column&#61;"dog_name" property&#61;"dogName"/>resultMap>
mapper>
//测试注解功能&#64;Testpublic void testAnno(){SqlSession sqlSession &#61; sqlSessionFactory.openSession();UserAnnoMapper userAnnoMapper &#61;sqlSession.getMapper(UserAnnoMapper.class);List<User> userList &#61; userAnnoMapper.findAll();System.out.println(userList);sqlSession.close();}
public interface UserAnnoMapper {/*** 注解使用规则:* 1.注解标识接口方法. 接口方法调用,直接注解的内容.* 2.注解将查询的结果集,根据方法的返回值类型动态映射.*///查询user表的数据记录&#64;Select("select * from demo_user")//新增 &#64;Insert("sql")//修改 &#64;Update("sql")//删除 &#64;Delete("sql")List<User> findAll();}
<mappers><mapper resource&#61;"mybatis/mappers/UserMapper.xml"/><mapper resource&#61;"mybatis/mappers/UserMapper2.xml"/><mapper resource&#61;"mybatis/mappers/DogMapper.xml"/><mapper class&#61;"com.jt.mapper.UserAnnoMapper">mapper>mappers>
<select id&#61;"findSqlWhere" resultType&#61;"User">select * from demo_user<where><if test&#61;"id !&#61; null"> id &#61; #{id}if><if test&#61;"name !&#61; null">and name &#61; #{name}if><if test&#61;"age !&#61; null ">and age &#61; #{age}if><if test&#61;"sex !&#61; null ">and sex &#61; #{sex}if>where>select>
2.set-if set标签用法: 去除set条件中多余的,号
3.choose-when-otherwise 标识都是互斥事件,只有一个有效.
4.resultMap 字段与属性名称不一致时,使用resultMap
5.Mybatis的注解开发 &#64;insert &#64;update &#64;delete &#64;select