入門 · TUNA-daily

[TOC] ## 1. 入門實例 1. 配置文件(總) ~~~ <?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"/> //加載數據庫連接信息  <environments default="development"> <environment id="development">  <transactionManager type="JDBC" />  <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.user}" /> <property name="password" value="${jdbc.password}" /> </dataSource> </environment> </environments>  <mappers> <mapper resource="entity/DeptMapper.xml"/> </mappers> </configuration> ~~~ 2. DAO ~~~ package dao; import java.io.IOException; import java.io.Reader; import entity.Dept; 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 DeptDaoImpl { /* * 1.讀取配置文件信息 * 2。構建session工廠 * 3。創建session * 4.開啟事務（可選） * 5。處理數據 * 6。提交、回滾事務 * 7。關閉session * */ public int save(Dept dept) { int i = 0; String path = "sqlMapConfig.xml"; SqlSession session = null; Reader reader = null; try { //獲取配置文件的信息 reader = Resources.getResourceAsReader(path); //構建sessionfactory SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader); //創建session session = sessionFactory.openSession(); //啟用事務，這里默認已啟用 //執行數據處理,第一個參數用“命名空間+sql id"來定位sql,第二個參數用來給sql傳參數 i = session.insert("entity.DeptMapper.insertDept", dept); //提交事務 session.commit(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); session.rollback(); } finally { if (reader != null) { try { reader.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (session != null) { session.close(); } } return i; } } ~~~ 3. 數據庫映射配置文件 ~~~ <?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="entity.DeptMapper">  <resultMap type="entity.Dept" id="deptResultMap">  <id column="dept_id" property="deptId"/>  <result column="dept_name" property="deptName"/> <result column="dept_address" property="deptAddress"/> </resultMap>  <insert id="insertDept" parameterType="entity.Dept">  insert into dept(dept_name,dept_address) values(#{deptName},#{deptAddress}) </insert> </mapper> ~~~ 4. entity ~~~ package entity; import java.io.Serializable; public class Dept implements Serializable { private static final long serialVersionUID = -2525513725816258556L; private Integer deptId;//部門編號 private String deptName;//部門名稱 private String deptAddress;//部門地址 public Integer getDeptId() { return deptId; } public void setDeptId(Integer deptId) { this.deptId = deptId; } public String getDeptName() { return deptName; } public void setDeptName(String deptName) { this.deptName = deptName; } public String getDeptAddress() { return deptAddress; } public void setDeptAddress(String deptAddress) { this.deptAddress = deptAddress; } @Override public String toString() { return "Dept [deptId=" + deptId + ", deptName=" + deptName + ", deptAddress=" + deptAddress + "]"; } } ~~~ 5. 測試類 ~~~ import static org.junit.Assert.*; import dao.DeptDaoImpl; import entity.Dept; import org.junit.AfterClass; import org.junit.Before; import org.junit.BeforeClass; import org.junit.Test; public class TestDeptDaoImpl { private DeptDaoImpl deptDaoImpl =null; @Before public void setUpBeforeClass() throws Exception { deptDaoImpl = new DeptDaoImpl(); } @Test public void test() { Dept dept = new Dept(); dept.setDeptName("綜合部"); dept.setDeptAddress("廣州天河"); System.out.println("受影響的行數："+deptDaoImpl.save(dept)); } } ~~~ ### 1.2 mybatis工具類 #### 1.2.1 sqlSession工具類線程安全: 1. ThreadLocal把當前線程和SqlSession綁定 2. 靜態成員 ~~~ public class MybatisSessionFactory { //定義 ThreadLocal<SqlSession> 存放SqlSession private static final ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>(); private static SqlSessionFactory sessionFactory; private static String CONFIG_FILE_LOCATION = "sqlMapConfig.xml"; //靜態塊，創建Session工廠 static { buildSessionFactory(); } private MybatisSessionFactory() { } public static SqlSession getSession() throws Exception { SqlSession session = (SqlSession) threadLocal.get(); if (session == null ) { if (sessionFactory == null) { buildSessionFactory(); } session = (sessionFactory != null) ? sessionFactory.openSession() : null; threadLocal.set(session); } return session; } /** * Rebuild session factory * */ public static void buildSessionFactory() { Reader reader =null; try { reader = Resources.getResourceAsReader(CONFIG_FILE_LOCATION); //讀取配置文件 sessionFactory = new SqlSessionFactoryBuilder().build(reader); //創建工廠對象 } catch (Exception e) { System.err.println("%%%% Error Creating SessionFactory %%%%"); e.printStackTrace(); }finally{ if(reader!=null){ try { reader.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } /** * Close the single session instance. * * @throws Exception */ public static void closeSession() throws Exception { SqlSession session = (SqlSession) threadLocal.get(); threadLocal.set(null); if (session != null) { session.close(); } } /** * return session factory * */ public static SqlSessionFactory getSessionFactory() { return sessionFactory; } } ~~~ #### 1.2.2 dao工具類 ~~~ public class DeptDaoImpl2 { public int save(Dept dept) throws Exception { int i = 0; SqlSession session = null; try { //獲取配置文件的信息 //構建sessionfactory session = MybatisSessionFactory.getSession(); //創建session //啟用事務，這里默認已啟用 //執行數據處理,第一個參數用“命名空間+sql id"來定位sql,第二個參數用來給sql傳參數 i = session.insert("entity.DeptMapper.insertDept", dept); //提交事務 session.commit(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); session.rollback(); } finally { if (session != null) { MybatisSessionFactory.closeSession(); } } return i; } public int update(Dept dp) throws Exception { int i = 0; SqlSession session = null; try { session = MybatisSessionFactory.getSession(); i = session.update("entity.DeptMapper.updateDept", dp); //提交事務 session.commit(); } catch (Exception e) { e.printStackTrace(); }finally { if (session != null) { MybatisSessionFactory.closeSession(); } } return i; } /** * * @param id * @return * @throws Exception * */ public int delete(Integer id) throws Exception { int i = 0; SqlSession session = null; try { session = MybatisSessionFactory.getSession(); i = session.delete("entity.DeptMapper.deleteDept", id); //提交事務 session.commit(); } catch (Exception e) { e.printStackTrace(); }finally { if (session != null) { MybatisSessionFactory.closeSession(); } } return i; } public Dept select(Integer id) throws Exception { int i = 0; SqlSession session = null; Dept dp = null; try { session = MybatisSessionFactory.getSession(); dp = session.selectOne("entity.DeptMapper.selectDept", id); //提交事務 session.commit(); } catch (Exception e) { e.printStackTrace(); }finally { if (session != null) { MybatisSessionFactory.closeSession(); } } return dp; } public List<Dept> selectMany(String adress) throws Exception { int i = 0; SqlSession session = null; List<Dept> depts = new ArrayList<Dept>(); try { session = MybatisSessionFactory.getSession(); depts = session.selectList("entity.DeptMapper.selectManyDept", adress); //提交事務 session.commit(); } catch (Exception e) { e.printStackTrace(); }finally { if (session != null) { MybatisSessionFactory.closeSession(); } } return depts; } public List<Dept> selectLike(String adress) throws Exception { int i = 0; SqlSession session = null; List<Dept> depts = new ArrayList<Dept>(); try { session = MybatisSessionFactory.getSession(); depts = session.selectList("entity.DeptMapper.selectLike", adress); //提交事務 session.commit(); } catch (Exception e) { e.printStackTrace(); }finally { if (session != null) { MybatisSessionFactory.closeSession(); } } return depts; } } ~~~ #### 1.2.3 mapper ~~~ <?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="entity.DeptMapper">  <resultMap type="entity.Dept" id="deptResultMap">  <id column="dept_id" property="deptId"/>  <result column="dept_name" property="deptName"/> <result column="dept_address" property="deptAddress"/> </resultMap>  <insert id="insertDept" parameterType="entity.Dept">  insert into dept(dept_name,dept_address) values(#{deptName},#{deptAddress}) </insert>  <update id="updateDept" parameterType="Dept"> UPDATE dept SET dept_name = #{deptName},dept_address = #{deptAddress} WHERE dept_id = #{deptId} </update> <delete id="deleteDept" parameterType="integer"> DELETE FROM dept WHERE dept_id = #{deptId} </delete>  <select id="selectDept" parameterType="integer" resultMap="deptResultMap"> SELECT * FROM dept WHERE dept_id = #{deptId} </select> //多查詢 <select id="selectManyDept" parameterType="string" resultMap="deptResultMap"> SELECT dept_id,dept_name,dept_address FROM dept WHERE dept_address = #{deptId} </select> //模糊查詢 <select id="selectLike" parameterType="string" resultMap="deptResultMap"> SELECT dept_id,dept_name,dept_address FROM dept WHERE dept_address LIKE #{deptId} </select> </mapper> ~~~ #### 1.2.4 測試 ~~~ import dao.DeptDaoImpl; import dao.DeptDaoImpl2; import entity.Dept; import org.junit.Before; import org.junit.Test; import java.util.List; public class TestDeptDaoImpl2 { private DeptDaoImpl2 deptDaoImpl =null; @Before public void setUpBeforeClass() throws Exception { deptDaoImpl = new DeptDaoImpl2(); } @Test public void test() throws Exception { Dept dept = new Dept(); dept.setDeptName("大數據部"); dept.setDeptAddress("吉林長春"); System.out.println("受影響的行數："+deptDaoImpl.save(dept)); } @Test public void test1() throws Exception { Dept dept = new Dept(); dept.setDeptName("研發部"); dept.setDeptAddress("吉林長春"); dept.setDeptId(5); System.out.println("受影響的行數："+deptDaoImpl.update(dept)); } @Test public void test2() throws Exception { // Dept dept = new Dept(); // dept.setDeptName("研發部"); // dept.setDeptAddress("吉林長春"); // dept.setDeptId(5); System.out.println("受影響的行數："+deptDaoImpl.delete(6)); } @Test public void test3() throws Exception { Dept dp = deptDaoImpl.select(5); System.out.println(dp); } @Test public void test4() throws Exception { List<Dept> depts = deptDaoImpl.selectMany("廣州"); System.out.println(depts); } @Test public void test5() throws Exception { List<Dept> depts = deptDaoImpl.selectLike("%吉%"); System.out.println(depts); } } ~~~ ### 1.3 動態SQL 根據實體類屬性的多少動態生成sql，例如實體有地址，就根據地址生成SQL 1. mapper（if標簽：判斷標簽之間不影響） ~~~ <select id="selectDynamic" parameterType="dept" resultMap="deptResultMap"> SELECT dept_id,dept_name,dept_address FROM dept WHERE 1 = 1 # 防止查詢條件全空 <if test="deptId!=null">and dept_id = #{deptId}</if> <if test="deptName!=null">and dept_name = #{deptName}</if> <if test="deptAddress!=null">and dept_address = #{deptAddress}</if> </select> ~~~ 2. dao ~~~ public List<Dept> selectDynamic(Dept dp) throws Exception { int i = 0; SqlSession session = null; List<Dept> depts = new ArrayList<Dept>(); try { session = MybatisSessionFactory.getSession(); depts = session.selectList("entity.DeptMapper.selectDynamic", dp); //提交事務 session.commit(); } catch (Exception e) { e.printStackTrace(); }finally { if (session != null) { MybatisSessionFactory.closeSession(); } } return depts; } ~~~ 3. 測試 ~~~ @Test public void test6() throws Exception { Dept dp = new Dept(); // dp.setDeptId(); dp.setDeptAddress("廣州"); # 根據地址查詢 List<Dept> depts = deptDaoImpl.selectDynamic(dp); System.out.println(depts); } ~~~ 4. 改善（使用where標簽，若空就不加where） ~~~ <select id="selectDynamic" parameterType="dept" resultMap="deptResultMap"> SELECT dept_id,dept_name,dept_address FROM dept <where> <if test="deptId!=null">and dept_id = #{deptId}</if> <if test="deptName!=null">and dept_name = #{deptName}</if> <if test="deptAddress!=null">and dept_address = #{deptAddress}</if> </where> </select> ~~~ 5. chose標簽 when：條件成立 otherwise:不成立時這倆標簽相當于if和else ~~~ <select id="selectDynamic" parameterType="dept" resultMap="deptResultMap"> SELECT dept_id,dept_name,dept_address FROM dept <where> <choose> <when test="deptId!=null">and dept_id = #{deptId}</when> <when test="deptName!=null">and dept_name = #{deptName}</when> <when test="deptAddress!=null">and dept_address = #{deptAddress}</when> <otherwise>AND 1 = 2</otherwise> </choose> </where> </select> ~~~ 6. set 標簽 UPDATE dept SET dept_address=? WHERE dept_id = ? 防止，實體類中有的屬性為空，更新數據庫時，把數據庫數據也更新為空 ~~~ <update id="updateDeptUsetSet" parameterType="dept"> UPDATE dept <set> <if test="deptName!=null">dept_name=#{deptName},</if> <if test="deptAddress!=null">dept_address=#{deptAddress},</if> </set> WHERE dept_id = #{deptId} </update> ~~~ ~~~ public int updateDynamic(Dept dp) throws Exception { int i = 0; SqlSession session = null; List<Dept> depts = new ArrayList<Dept>(); try { session = MybatisSessionFactory.getSession(); i = session.update("entity.DeptMapper.updateDeptUsetSet", dp); //提交事務 session.commit(); } catch (Exception e) { e.printStackTrace(); }finally { if (session != null) { MybatisSessionFactory.closeSession(); } } return i; } ~~~ 測試:只更新地址屬性 ~~~ @Test public void test7() throws Exception { Dept dp = new Dept(); dp.setDeptId(1); dp.setDeptAddress("四川成都"); int i = deptDaoImpl.updateDynamic(dp); System.out.println("有" + i + "行受影響！"); } ~~~ 7. foreach 標簽 SELECT dept_id,dept_name,dept_address FROM dept WHERE dept_id IN ( ? , ? , ? ) ~~~ <select id="updateByForeach" resultMap="deptResultMap"> SELECT dept_id,dept_name,dept_address FROM dept WHERE dept_id IN ( <foreach collection="list" item="deptId" separator=","> #{deptId} </foreach> ) </select> ~~~ 或者這樣寫括號看，屬性open="(" close=")" ~~~ <select id="updateByForeach" resultMap="deptResultMap"> SELECT dept_id,dept_name,dept_address FROM dept WHERE dept_id IN <foreach collection="list" item="deptId" separator="," open="(" close=")"> #{deptId} </foreach> </select> ~~~ ### 1.4 mybatis 配置log4j打印sql 1. log4j.xml ~~~ <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd"> <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/" debug="false"> <appender name="CONSOLE" class="org.apache.log4j.ConsoleAppender"> <layout class="org.apache.log4j.PatternLayout"> <param name="ConversionPattern" value="[%d{dd/MM/yy hh:mm:ss:sss z}] %5p %c{2}: %m%n" /> </layout> </appender>                 <logger name="com.ibatis" additivity="true"> <level value="DEBUG" /> </logger> <logger name="java.sql.Connection" additivity="true"> <level value="DEBUG" /> </logger> <logger name="java.sql.Statement" additivity="true"> <level value="DEBUG" /> </logger> <logger name="java.sql.PreparedStatement" additivity="true"> <level value="DEBUG" /> </logger> <logger name="java.sql.ResultSet" additivity="true"> <level value="DEBUG" /> </logger> <root> <level value="DEBUG" /> <appender-ref ref="CONSOLE" />   </root> </log4j:configuration> ~~~ 2. log4j.properties ~~~ # Rules reminder: # DEBUG < INFO < WARN < ERROR < FATAL # Global logging configuration log4j.rootLogger=debug,stdout # My logging configuration... log4j.logger.cn.jbit.mybatisdemo=DEBUG ## Console output... log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p %d %C: %m%n log4j.logger.org.apache.ibatis=DEBUG ## log4j.logger.org.apache.jdbc.SimpleDataSource=DEBUG log4j.logger.org.apache.ibatis.jdbc.ScriptRunner=DEBUG ## log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapclientDelegate=DEBUG log4j.logger.java.sql.Connection=DEBUG log4j.logger.java.sql.Statement=DEBUG log4j.logger.java.sql.PreparedStatement=DEBUG ~~~ 3. maven ~~~ <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.16</version> </dependency> ~~~ ## 2. 概念理解 ### 2.1 mapper 與 mapper.xml 1. mapper配置文件是配置sql映射的地方 2. mapper配置文件中的namespace屬性，有兩個作用：一是用于區分不同的mapper（在不同的mapper文件里，子元素的id可以相同，mybatis通過namespace和子元素的id聯合區分）二是與接口關聯（應用程序通過接口訪問mybatis時，mybatis通過接口的完整名稱查找對應的mapper配置，因此namespace的命名務必小心一定要某接口同名）。此外，mapper配置文件還有幾個頂級子元素（它們須按照順序定義）：即根據調用接口時，在進行SQL的時候根據接口名與namespace的名稱一樣進行關聯，接口中的方法要與配置文件中增刪改查標簽中id名稱相同故:mapper配置文件中namespace要與對應的mapper接口名相同（包名+接口名）；mapper配置文件中增刪改查標簽id與mapper接口類方法 l cache -配置本定命名空間的緩存。 l cache-ref –從其他命名空間引用緩存配置。 l resultMap –結果映射，用來描述如何從數據庫結果集映射到你想要的對象。 l parameterMap –已經被廢棄了！建議不要使用，本章也不會對它進行講解。 l sql –可以重用的SQL塊，可以被其他數據庫操作語句引用。 l insert|update|delete|select–數據庫操作語句 Mapper的解析在XMLMapperBuilder里完成，主要通過configurationElement方法完成解析，在configurationElement內部調用各個元素的子解析方法完成解析。下面分別介紹這些子元素。