有时候要查询条件是多条件的,尤其是使用mybatis的时候如何创建sql语句呢?
这里mybatis有自己的办法,如下:
案例:通过传入map,根据map里面的数据来查询
mapper配置如下:
<select id="select04" parameterType="Emp1" resultType="Emp1"> select * from emp <where> <if test="empno != 0"> and empno = #{empno} </if> <if test="ename != null"> and ename = #{ename} </if> <if test = "deptno != null "> and deptno = #{deptno} </if> </where> </select>
测试类如下:
package com.yc.mybatis; import java.io.IOException; import java.io.InputStream; import java.util.List; import java.util.Map; 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; public class TestTest01 { InputStream is = null; SqlSessionFactory factory = null; SqlSession session = null; { try { is = Resources.getResourceAsStream("mybatis-config.xml"); factory = new SqlSessionFactoryBuilder().build(is); session = factory.openSession(); } catch (IOException e) { e.printStackTrace(); } } @Test public void TTest04(){ Emp1 emp = new Emp1(); emp.setDeptno(20); List<Emp1> list = session.selectList("TTest.select04", emp);//TTest为mapper的命名空间 for(Emp1 e : list){ System.out.println(e); } } }
实体类如下:
package com.yc.mybatis; public class Emp1 { private int empno; private int deptno; private String ename; @Override public String toString() { return "Emp1 [empno=" + empno + ", deptno=" + deptno + ", ename=" + ename + "]"; } public int getEmpno() { return empno; } public void setEmpno(int empno) { this.empno = empno; } public int getDeptno() { return deptno; } public void setDeptno(int deptno) { this.deptno = deptno; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + deptno; result = prime * result + empno; result = prime * result + ((ename == null) ? 0 : ename.hashCode()); return result; } @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (getClass() != obj.getClass()) return false; Emp1 other = (Emp1) obj; if (deptno != other.deptno) return false; if (empno != other.empno) return false; if (ename == null) { if (other.ename != null) return false; } else if (!ename.equals(other.ename)) return false; return true; } public Emp1(int empno, int deptno, String ename) { super(); this.empno = empno; this.deptno = deptno; this.ename = ename; } public Emp1() { super(); } }Emp1.java
结果截图如下:
多条件查询到此结束,但是相关的思想却非常重要,比如有多条件更新,多个值的赋值再插入数据等等,这里我就将配置项发一下,相关的测试类和实体类就不一 一列举了。
多数据更新:
<update id="update01" parameterType="Emp1"> update emp <set> <if test="ename != null "> ename = #{ename}, </if> <if test = "deptno != 0"> deptno = #{deptno}, </if> </set> where empno = #{empno} </update>
多条件删除:
<delete id="delete01" parameterType="Emp"> delete emp <where> <if test="empno != 0"> and empno = #{empno} </if> <if test="ename != null"> and ename = #{ename} </if> <if test="deptno != 0"> and deptno = #{deptno} </if> </where> </delete>
注意:关于添加、删除和修改必须要手动提交一次才行,要session.commit(),因为mybatis不会自动帮你提交。
基本的mybatis到此。