mybatis
CRUD是指在做增加(Create)、读取(Retrieve)（重新得到数据）、更新(Update)和删除(Delete)几个单词的首字母简写。主要被用在描述软件系统中数据库或者持久层的基本操作功能。
这儿主要讲解mybatis的增删改查，对入门没有了解的，可以去参考上一篇文章。如有不对之处，请谅解，并提出，本人也是才自学的新手。 浅谈MyBatis 之 入门（一）
数据库表
表内容： 
总配置文件
MyBatis-config.xml文件：
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration> <!-- 引入外部 配置 文件 --> <PRoperties resource="jdbc.properties" /> <!-- 配置 别名 --> <typeAliases> <typeAlias alias="Dept" type="com.wm.mybatis.POJO.Dept"/> </typeAliases> <environments default="development"> <environment id="development" > <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driverClass}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="passWord" value="${jdbc.password}"/> </dataSource> </environment> </environments> <!-- 配置的映射文件 --> <mappers> <mapper resource="mapper/deptCURD.xml" /> </mappers></configuration>
POJO
Dept.java
package com.wm.mybatis.POJO;public class Dept { private Integer id ; private String name ; private String address ; public Dept(){} public Dept(Integer id, String name, String address) { super(); this.id = id; this.name = name; this.address = address; } public Integer getId() { System.out.println(id); return id; } public void setId(Integer id) { this.id = id; } public String getName() { System.out.println(name); return name; } public void setName(String name) { this.name = name; } public String getAddress() { System.out.println(address); return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return "Dept [id=" + id + ", name=" + name + ", address=" + address + "]"; }}
首先写一个公共类
公共类sessionManagerUtil.java 是实现 获取sqlsession 用的，这样做的好处就是：便于管理当前线程与session的一个关系，还有就是 便于操作session。
package com.wm.mybatis.util;import java.io.IOException;import java.io.Reader;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;public class SessionManagerUtil { // 同一个线程 下 session 操作 private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>(); private static SqlSessionFactory sessionFactory = null; // 静态加载块 加载配置文件 static{ try { Reader config = Resources.getResourceAsReader("MyBatis-config.xml"); sessionFactory = new SqlSessionFactoryBuilder().build(config); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException(); } } // 防止直接new private SessionManagerUtil(){} // 获取session public static SqlSession getSession(){ SqlSession sqlSession = threadLocal.get(); if (sqlSession == null) { sqlSession = sessionFactory.openSession(); threadLocal.set(sqlSession); } return sqlSession; } /// 关闭session public static void closeSession(){ SqlSession sqlSession = threadLocal.get(); if (sqlSession != null) { sqlSession.close(); threadLocal.remove(); // 与当前线程 分离 } }}CRUD
增加
首先配置映射文件
<?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.wm.mybatis.dao.IDeptCURDMapperDao"> <!-- 由于数据库表字段 和 JavaBean Dept类属性 不一致 所以 要配置 resultMap 来实现一一对应--> <resultMap type="Dept" id="resultDept"> <result property="id" column="d_id" /> <result property="name" column="d_name" /> <result property="address" column="d_address" /> </resultMap> <!-- 增加 部门 --> <insert id="addDept" parameterType="Dept" > insert into base_55demo.demo_mawei_dept(d_id,d_name,d_address) values(#{id},#{name},#{address}) </insert></mapper>DAO层：
DeptCURDMapperDaoImpl
import java.util.HashMap;import java.util.List;import org.apache.ibatis.session.SqlSession;import com.wm.mybatis.POJO.Dept;import com.wm.mybatis.util.SessionManagerUtil;public class DeptCURDMapperDaoImpl{ // 添加部门 public void addDept(Dept dept) throws Exception { SqlSession session = null; try { session = SessionManagerUtil.getSession(); //获取session int count = session.insert(IDeptCURDMapperDao.class.getName()+".addDept", dept); System.out.println("插入了记录：" + count + " 条"); // 更新 要提交 session.commit(); } catch (Exception e) { e.printStackTrace(); session.rollback(); // 回滚 throw e; } finally{ SessionManagerUtil.closeSession(); //关闭session } }}测试
TestDeptCURD
package com.wm.mybatis.Test;import java.util.List;import org.junit.Test;import com.wm.mybatis.POJO.Dept;import com.wm.mybatis.dao.DeptCURDMapperDaoImpl;public class TestDeptCURD { // 增加 @Test public void addDept() throws Exception{ DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl(); dao.addDept(new Dept(35, "卫生部", "香港")); }}结果
数据库表：
删除
映射配置
<!-- 删除 --> <delete id="deleteDept" parameterType="Dept"> delete from base_55demo.demo_mawei_dept t where t.d_id = #{id} and t.d_name = #{name} </delete> <!-- 删除部门 根据 ID --> <delete id="deleteDeptById" parameterType="int"> delete from base_55demo.demo_mawei_dept t where t.d_id = #{id} </delete>DAO层
// 删除 public void deleteDept(Dept dept) throws Exception { SqlSession session = null; try { session = SessionManagerUtil.getSession(); int count = session.delete(IDeptCURDMapperDao.class.getName()+".deleteDept", dept); System.out.println("删除了记录："+count+" 条"); session.commit(); } catch (Exception e) { e.printStackTrace(); session.rollback(); throw e; } finally{ SessionManagerUtil.closeSession(); } } //删除部门 根据 ID来执行 public void deleteDeptById(int id) throws Exception { SqlSession session = null; try { session = SessionManagerUtil.getSession(); int count = session.delete(IDeptCURDMapperDao.class.getName()+".deleteDeptById", id); System.out.println("删除了记录："+count+" 条"); session.commit(); } catch (Exception e) { e.printStackTrace(); session.rollback(); throw e; } finally{ SessionManagerUtil.closeSession(); } }测试
// 删除 @Test public void deleteDept() throws Exception{ DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl(); dao.deleteDept(new Dept(9, "9", "9")); } // 根据ID 删除 @Test public void deleteDeptById() throws Exception{ DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl(); dao.deleteDeptById(8); dao.deleteDeptById(12); }结果
数据库表： 
结果表明：ID为8、9、12的数据都被删除。
修改
配置映射
<!-- 更新 --> <update id="updateDept" parameterType="Dept"> update base_55demo.demo_mawei_dept t set t.d_name = #{name} , t.d_address = #{address} where t.d_id = #{id} </update>DAO层
// 修改更新 public void updateDept(Dept dept) throws Exception { SqlSession session = null; try { session = SessionManagerUtil.getSession(); int count = session.update(IDeptCURDMapperDao.class.getName()+".updateDept", dept); System.out.println("更新了记录："+count+" 条"); session.commit(); } catch (Exception e) { e.printStackTrace(); session.rollback(); throw e; } finally{ SessionManagerUtil.closeSession(); } }测试
// 更新 @Test public void updateDept() throws Exception{ DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl(); Dept dept = dao.getDeptById(6); dept.setName("计费BOSS"); //修改数据 dao.updateDept(dept); }结果
数据库表： 
查询
配置映射
<!-- 根据ID 来查询部门 --> <select id="getDeptById" parameterType="int" resultMap="resultDept"> select * from base_55demo.demo_mawei_dept t where t.d_id = #{id} </select> <!-- 查询所有的部门 --> <select id="getDeptALL" resultMap="resultDept"> select * from base_55demo.demo_mawei_dept t </select> <!-- 分页查询 --> <select id="getDeptByPage" resultMap="resultDept" parameterType="map"> <![CDATA[ select dept.d_id,dept.d_name, dept.d_address from (select rownum num, t.* from base_55demo.demo_mawei_dept t where rownum <= (#{start}+#{num})) dept where dept.num > #{start} ]]> </select> <!-- 查询 总记录数 --> <select id="getTotalNum" resultType="int"> select count(0) from base_55demo.demo_mawei_dept </select>DAO层
//查询 通过 ID public Dept getDeptById(int id) { SqlSession session = SessionManagerUtil.getSession(); Dept dept = session.selectOne(IDeptCURDMapperDao.class.getName()+".getDeptById", id); SessionManagerUtil.closeSession(); System.out.println(dept); return dept; } //查询 所以 部门 public List<Dept> getDeptALL() { SqlSession session = SessionManagerUtil.getSession(); List<Dept> depts = session.selectList(IDeptCURDMapperDao.class.getName()+".getDeptALL"); SessionManagerUtil.closeSession(); return depts; } // 分页查询 public List<Dept> getDeptByPage(int start, int num){ SqlSession session = SessionManagerUtil.getSession(); HashMap<String, Object> map = new HashMap<String, Object>(); map.put("start", start); map.put("num", num); List<Dept> depts = session.selectList(IDeptCURDMapperDao.class.getName()+".getDeptByPage", map); return depts; } // 查询所有记录 public int getTotalNum(){ SqlSession session = SessionManagerUtil.getSession(); int num = session.selectOne(IDeptCURDMapperDao.class.getName()+".getTotalNum"); SessionManagerUtil.closeSession(); return num; }测试
// 根据 ID 查询 @Test public void getDeptById() throws Exception{ DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl(); Dept dept = dao.getDeptById(19); System.out.println(dept); } // 查询所有的 @Test public void getDeptALL() throws Exception{ DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl(); List<Dept> depts = dao.getDeptALL(); for (Dept dept : depts) { System.out.println(dept); } } // 分页查询 @Test public void getDeptByPage() throws Exception{ DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl(); int totalNum = dao.getTotalNum(); int pageNum = 4; int totalPage = (totalNum % pageNum == 0) ? (totalNum / pageNum) : (totalNum / pageNum) + 1 ; for (int i = 0; i < totalPage; i++) { System.out.println("第 "+(i+1)+" 页"); List<Dept> depts = dao.getDeptByPage(i*pageNum,pageNum); for (Dept dept : depts) { System.out.println(dept); } } } // 查询总记录 @Test public void getTotalNum() throws Exception{ DeptCURDMapperDaoImpl dao = new DeptCURDMapperDaoImpl(); int num = dao.getTotalNum(); System.out.println("总共："+num+" 条记录"); }结果
此处 展示分页查询的结果，其他的查询比较简单。
总结
1、如果映射文件配置namespace 为dao接口类路径，则在实现操作时，可以简化。<mapper namespace="com.wm.mybatis.dao.IDeptCURDMapperDao">此处就可以写成 IDeptCURDMapperDao.class.getName()
session.insert(IDeptCURDMapperDao.class.getName()+".addDept", dept);2、如果配置文件中返回的是多结果查询，本应该是List，但是这儿配置List里面放置的类型为返回值类型。resultMap=”resultDept” <!-- 分页查询 --> <select id="getDeptByPage" resultMap="resultDept" parameterType="map">3、如果配置映射参数是map时，获取值的名字要与map放入的名字一致。parameterType=”map” <!-- 分页查询 --> <select id="getDeptByPage" resultMap="resultDept" parameterType="map"> <![CDATA[ select dept.d_id,dept.d_name, dept.d_address from (select rownum num, t.* from base_55demo.demo_mawei_dept t where rownum <= (#{start}+#{num})) dept where dept.num > #{start} ]]> </select> // 分页查询 public List<Dept> getDeptByPage(int start, int num){ SqlSession session = SessionManagerUtil.getSession(); HashMap<String, Object> map = new HashMap<String, Object>(); map.put("start", start); map.put("num", num); List<Dept> depts = session.selectList(IDeptCURDMapperDao.class.getName()+".getDeptByPage", map); return depts; }配置映射文件中和dao层的map放入值名称要一致 map.put(“start”, start); map.put(“num”, num);
4、一个小技巧
在使用mybatis时，配置Log4j配置，可以打印 显示出（sessions连接ID、连接的开启、关闭、及执行的SQL、动态SQL参数等信息）
在log4j.properties文件中加入：
log4j.logger.com.ibatis=DEBUGlog4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUGlog4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUGlog4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUGlog4j.logger.java.sql.Connection=DEBUGlog4j.logger.java.sql.Statement=DEBUGlog4j.logger.java.sql.PreparedStatement=DEBUG显示的结果如下：
这样便于学习mybatis，可以看见过程及背后的SQL。
