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。
