有四个参数，都不是必填项，那么要按照所填写的内容来查找数据：
xml：
<!-- 传输类型是传递对象，结果类型是map <![CDATA[XXXXXXXX]]> 是转义小于号的 --> <select id="selectPersonByCondition" parameterType="xxx.x.QueryCondition" resultMap="BaseResultMap"> select * from person t <where> <if test="name != null"> t.name like '%${name}%' </if> <if test="gender != null"> and t.gender = #{gender} </if> <if test="personAddr != null"> and t.person_addr like '%${personAddr}%' </if> <if test="birthday != null"> <![CDATA[ and t.birthday < #{birthday} ]]> </if> </where> </select>
java：
public void selectPersonByCondition() { //创建SqlSession SqlSession session = sessionFactory.openSession(); try { QueryCondition qc = new QueryCondition(); qc.setName("张三"); //这里任意一行都可以删掉 qc.setPerson_addr("shagnhai");//这里任意一行都可以删掉 qc.setGender(1);//这里任意一行都可以删掉 qc.setBirthday(new Date());//这里任意一行都可以删掉 List<Person> pList = session.selectList("xxx.x.mapper.PersonTestMapper.selectPersonByCondition",qc); for(Person p : pList) { System.out.println(p); } }catch (Exception e) { e.printStackTrace(); session.rollback(); }finally { session.close(); } }
<where>标签就是用于动态条件组合查询，可以自动去掉where后的第一个and。
以后修改，就使用动态修改
当update的时候，有些属性没有重新输入值，那么就更新成null了。
那么只要求修改不为空的数据，为空的不修改：
<!-- 动态修改，使用<set>能处理掉最后一个逗号。
t.person_id = #{personId},的目的就是防止四个属性都为空的时候sql语句会报错
--> <update id="dynamicUpdate" parameterType="person"> update person t <set> t.person_id = #{personId},
<if test="name != null"> t.name = #{name}, </if> <if test="gender != null"> t.gender = #{gender}, </if> <if test="personAddr != null"> t.person_addr = #{personAddr}, </if> <if test="birthday != null"> t.birthday = #{birthday} </if> </set> where t.person_id = #{personId} </update>
public void dynamicUpdate() { //创建SqlSession SqlSession session = sessionFactory.openSession(); try { Person p = new Person(); p.setId(2); p.setName("李四");//此处任何一行都可以去掉，但要保留一行 p.setGender(1);//此处任何一行都可以去掉，但要保留一行 p.setAddress("上海");//此处任何一行都可以去掉 p.setBirthday(new Date()); int count = session.update("xxx.x.mapper.PersonTestMapper.dynamicUpdate",p); //此处有一个返回值，是影响的行数 session.commit(); //数据库的变更（增删改）都要提交事务 System.out.println(count); }catch (Exception e) { e.printStackTrace(); session.rollback(); }finally { session.close(); } }
使用in动态查询多行数据
<!-- map.put("ids", Integer[]) foreach:遍历集合来组装sql collection:map中集合的key open:以某种字符开始 close:以某种字符结束 item:集合中的元素 separator:每一项用什么字符分隔 index:遍历索引号，遍历到第几个 --> <select id= "selectPersonByIn" parameterType="map" resultMap = "BaseResultMap"> select * from person t where t.person_id in <foreach collection="ids" open="(" close=")" item="personId" separator="," index=""> #{personId} </foreach> </select>
public void selectPersonByIn() { //创建SqlSession SqlSession session = sessionFactory.openSession(); try { Integer [] ids = {1,2,3}; Map<String,Object> map = new HashMap<String,Object>(); map.put("ids", ids); List<Person> pList = session.selectList("xxx.x.mapper.PersonTestMapper.selectPersonByIn",map); //此处有一个返回值，是影响的行数 for(Person p : pList) { System.out.println(p); } }catch (Exception e) { e.printStackTrace(); session.rollback(); }finally { session.close(); } }
动态删除/批量删除（例如CheckBox，可以多选）
<delete id="deleteBatch" parameterType="map"> delete from person where person_id in <foreach collection="ids" open="(" close=")" item="personId" separator="," index=""> #{personId} </foreach> </delete>
public void deleteBatch() { // 创建SqlSession SqlSession session = sessionFactory.openSession(); List<Integer> idList = new ArrayList<Integer>(); Map<String, Object> map = new HashMap<String, Object>(); try { for (int i = 0; i <= 1000007; i++) { idList.add(i); if (i % 100 == 0) { map.put("ids", idList); // 这里的"ids"要和xml里的foreach里的collection对应上 session.delete("xxx.x.mapper.PersonTestMapper.deleteBatch", map); idList.clear(); } } map.put("ids", idList); session.delete("xxx.x.mapper.PersonTestMapper.deleteBatch", map); session.commit(); } catch (Exception e) { e.printStackTrace(); session.rollback(); } finally { session.close(); } }
插入数据/批量插入100条数据：
<!-- map.put("personList",List<Person> List) 一条sql语句插入多行的方法： insert into person (id,name) values (1,"张三"),(2,"张三2"),(3,"张三3"),...... --> <insert id="insertBatch" parameterType="map"> <selectKey keyProperty="personId" order="AFTER" resultType="int"> select LAST_INSERT_ID() </selectKey> insert into person (person_id,name,gender,person_addr,birthday) values <foreach collection="personList" separator="," item="person"> (#{person.personId},#{person.name},#{person.gender},#{person.personAddr},#{person.birthday},) </foreach> </insert>
public void insertBatch() { //创建SqlSession SqlSession session = sessionFactory.openSession(); List<Person> pList = new ArrayList<Person>(); for(int i = 0; i < 100; i++) { Person p = new Person(); p.setName("张三" + i); p.setGender(1); p.setAddress("nanjing" + i); p.setBirthday(new Date()); pList.add(p); } Map<String,Object> map = new HashMap<String,Object>(); map.put("personList", pList); //这里的"personList"要和xml里的foreach里的collection对应上 try { int count = session.insert("xxx.x.mapper.PersonTestMapper.insertBatch",map); //此处有一个返回值，是影响的行数 session.commit(); System.out.println(count); }catch (Exception e) { e.printStackTrace(); session.rollback(); }finally { session.close(); } }
但是特别大的数据量不能用以上方法，会造成内存溢出报错。要用以下方式100条100条的执行：
java改，xml不用改
public void insertBatch() { //创建SqlSession SqlSession session = sessionFactory.openSession(); List<Person> pList = new ArrayList<Person>(); Map<String,Object> map = new HashMap<String,Object>(); try { for (int i = 0; i < 1000007; i++) { Person p = new Person(); p.setName("张三" + i); p.setGender(1); p.setAddress("nanjing" + i); p.setBirthday(new Date()); pList.add(p); if (i % 100 == 0) { map.put("personList", pList); //这里的"personList"要和xml里的foreach里的collection对应上 session.insert("xxx.x.mapper.PersonTestMapper.insertBatch", map); pList.clear(); //每到100条输出一次，然后清空一次list } } map.put("personList", pList); //把剩余的7个装进list session.insert("xxx.x.mapper.PersonTestMapper.insertBatch", map); session.commit(); } catch (Exception e) { e.printStackTrace(); session.rollback(); } finally { session.close(); } }
