入门 · 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内部调用各个元素的子解析方法完成解析。下面分别介绍这些子元素。