mybatis（二）接口编程 、动态sql 、批量删除 、动态更新、连表查询
原理等不在赘述，这里主要通过代码展现。
在mybatis（一）基础上，新建一个dao包，并在里面编写接口，然后再在xml文件中引入接口路径，其他不变，在运用阶段将比原始方法更节约时间，因为不用再去手动的
引用sql,我们只需调用dao层的方法，然后让dao层的方法去找配置文件，去找sql，sql查询数据并赋值给对象，或者设置对象参数值在经过sql更新到数据库，小伙伴们这就是
orm对象关系映射模型！（个人见解，不对之处请指正。）
新增后项目：
一、新建一个com.ckx.dao包，并new一个（interface）DeptDao.java。
DeptDao.java：
package com.ckx.dao; import java.util.List; import org.apache.ibatis.annotations.Param; import com.ckx.entity.Dept; public interface DeptDao { //通过ID查数据 public Dept selectDeptById(int deptno); //查询一个结果集 public List<Dept> selectDeptAll(); //多条件动态 ，查询注意@Param的重要性，没有回报参数not find错误 public List<Dept> selectDeptByMore(@Param("deptno") int deptno,@Param("dname") String dname); //根据ID动态修改 public int updateDeptById(Dept d); //批量删除 public int deleteDepts(List<Integer> list); //新增 public int insertDept(Dept d); public int insertDept2(Dept d); //多表关联 public Dept selectDeptEmps(int t); }
二、配置Dept.xml：
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!--接口编程--> <mapper namespace="com.ckx.dao.DeptDao"> <resultMap type="Dept" id="DeptList"> <result column="deptno" property="deptno"/> <result column="dname" property="dname"/> <result column="loc" property="loc"/> <!-- 代表集合数据 property：实体bean中的属性名称 ofType：数据类型 --> <collection property="emps" ofType="Emp"> <result column="EMPNO" property="empno" /> <result column="ENAME" property="ename" /> <result column="JOB" property="job" /> <result column="MGR" property="mgr" /> <result column="HIREDATE" property="hiredate" /> <result column="SAL" property="sal" /> <result column="COMM" property="comm" /> <result column="DEPNO" property="depno" /> </collection> </resultMap> <!-- 通过ID查询--> <select id="selectDeptById" parameterType="int" resultType="Dept"> select * from dept where deptno = #{deptno} </select> <!-- 查询整张表，注意返回参数是一个DeptList集合，这个需要在上面配置--> <select id="selectDeptAll" resultMap="DeptList"> select * from dept </select> <!-- 多参数动态查询--> <select id="selectDeptByMore" resultMap="DeptList"> select * from dept <where> <if test="deptno != 0"> deptno = #{deptno} </if> <if test="dname != null"> or dname like #{dname} </if> </where> </select> <!-- 动态更新数据--> <update id="updateDeptById" parameterType="Dept"> update dept <set> <if test="deptno != null"> dname = #{dname}, </if> <if test="deptno != null"> loc = #{loc} </if> </set> where deptno = #{deptno} </update> <!-- 批量删除--> <delete id="deleteDepts" parameterType="list"> delete from dept where deptno in <foreach item="item" index="index" collection="list" open="(" separator=", " close=") "> #{item} </foreach> </delete> <!-- 新增数据--> <insert id="insertDept" parameterType="Dept" > insert into dept (deptno,dname,loc) values (#{deptno},#{dname},#{loc}) </insert> <!-- 连表查询，注意1、到层返回值为对象类本身 2、查询返回值为list集合。--> <select id="selectDeptEmps" parameterType="int" resultMap="DeptList"> SELECT d.DNAME,e.* FROM dept d,emp e WHERE d.DEPTNO=e.DEPNO and d.DEPTNO = #{deptno} </select> <!-- 未完成--> <sql id='dept'>TEST_USER</sql> <!-- 注意这里需要先查询自增主键值 --> <insert id="insertDept2" parameterType="Dept"> <selectKey keyProperty="deptno" resultType="int" order="BEFORE"> SELECT LAST_INSERT_deptno() </selectKey> insert into <include refid="dept" /> (deptno,dname,loc) values ( #{deptno},#{dname},#{loc} ) </insert> </mapper>
三、测试
DeptTest.java：
package com.ckx.test; import java.io.IOException; import java.io.Reader; import java.util.ArrayList; import java.util.Collection; import java.util.Iterator; import java.util.List; import java.util.ListIterator; import javax.mail.Session; 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 com.ckx.dao.DeptDao; import com.ckx.entity.Dept; import com.ckx.entity.Emp; public class DeptTest { static SqlSessionFactory sessionFactory; static Reader reader; static SqlSession sqlSession; static { try { reader = Resources.getResourceAsReader("config.xml"); sessionFactory = new SqlSessionFactoryBuilder().build(reader); sqlSession = sessionFactory.openSession(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public void select(){ DeptDao dao = sqlSession.getMapper(DeptDao.class); Dept d = dao.selectDeptById(10); System.out.println(d.getDname()); } public void selectDeptAll(){ DeptDao dao = sqlSession.getMapper(DeptDao.class); List<Dept> list = dao.selectDeptAll(); for (Dept dept : list) { String s = dept.getDname(); System.out.println(s); } Iterator<Dept> ite = list.iterator(); while (ite.hasNext()) { Dept d = ite.next(); String ss = d.getLoc(); System.out.println(ss); } for (int i = 0; i < list.size(); i++) { Dept d = list.get(i); int t = d.getDeptno(); System.out.println(t); } } public void selectDeptByMore(){ DeptDao dao = sqlSession.getMapper(DeptDao.class); List<Dept> list = dao.selectDeptByMore(20,"A%"); Iterator<Dept> it = list.iterator(); while (it.hasNext()) { Dept dept = (Dept) it.next(); String s = dept.getDname(); System.out.println(s); } } public void updateDeptById(){ DeptDao dao = sqlSession.getMapper(DeptDao.class); Dept d = new Dept(); d.setDeptno(10); d.setDname("ASC"); d.setLoc("desc"); dao.updateDeptById(d); System.out.println(d.getDeptno()+d.getDname()+d.getLoc()); sqlSession.commit(); } public void deleteDepts(){ DeptDao dao = sqlSession.getMapper(DeptDao.class); List<Integer> list = new ArrayList<Integer>(); list.add(0); dao.deleteDepts(list); } public void insertDept(){ DeptDao dao = sqlSession.getMapper(DeptDao.class); Dept d = new Dept(); d.setDeptno(10); d.setDname("assc"); d.setLoc("deff"); dao.insertDept(d); sqlSession.commit(); } //多表联查 public void selectDeptEmps(){ DeptDao dao = sqlSession.getMapper(DeptDao.class); Dept d = dao.selectDeptEmps(10); List<Emp> list = d.getEmps(); for (Emp emp : list) { String s = emp.getEname(); System.out.println(d.getDname()+"\t"+s); } } ; //主键自增为完成 public void insertDept2(){ DeptDao dao = sqlSession.getMapper(DeptDao.class); Dept d = new Dept(); d.setDname("sss"); d.setLoc("ddd"); dao.insertDept(d); sqlSession.commit(); System.out.println(d.getDeptno()); } public static void main(String[] args) { DeptTest dt = new DeptTest(); dt.selectDeptEmps(); } }
以上皆为测试过运行正常的方法，每一个方法的调用只需能改dt后的方法即可。
具体注意说明：
