java深入探究16-mybatis

链接：http://pan.baidu.com/s/1skJ4TNB 密码：koo9

1.引入mybatis

　　jsbc简单易学,上手快,非常灵活构建SQL，效率高但代码繁琐，难以写出高质量的代码

　　hibernate不用写SQL，完全以面向对象的方式设计和访问但处理复杂业务时，灵活度差

　　所以中间产物：mybatis就应运而生

2.mybatis说法和特定

　　1）MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code，并且改名为MyBatis 。2013年11月迁移到Github。

　　2）iBATIS一词来源于“internet”和“abatis”的组合，是一个基于Java的持久层框架。iBATIS提供的持久层框架包括SQL Maps和Data Access Objects（DAO）

　　3）jdbc/dbutils/springdao，hibernate/springorm，mybaits同属于ORM解决方案之一

3.mybatis快速入门

　　1)导入jar包（5个核心包+2个数据库访问包）：asm-3.3.1.jar；cglib-2.2.2.jar；commons-logging-1.1.1.jar；log4j-1.2.16.jar；mybatis-3.1.1.jar；mysql-connector-java-5.1.7-bin.jar；ojdbc5.jar

　　2）创建sql:

--mysql语法
create table students(
   id  int(5) primary key,
   name varchar(10),
   sal double(8,2)
);
--oracle语法
create table students(
   id  number(5) primary key,
   name varchar2(10),
   sal number(8,2)
);

　创建Student.java

/**
 * 学生
 * @author AdminTC
 */
public class Student {
    private Integer id;
    private String name;
    private Double sal;
    public Student(){}
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Double getSal() {
        return sal;
    }
    public void setSal(Double sal) {
        this.sal = sal;
    }
}

　　3)配置映射文件StudentMapper.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

namespace="mynamespace">
    "add1">
        insert into students(id,name,sal) values(1,'哈哈',7000)
    
    "add2" parameterType="cn.itcast.javaee.mybatis.app05.Student">
        insert into students(id,name,sal) values(#{id},#{name},#{sal})
    

　　4）配置mybatis.xml文件

<?xml version="1.0" encoding="UTF-8"?>
"-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">


    default="development">
        "development">
            "JDBC"/>
            "POOLED">
                "driver" value="com.mysql.jdbc.Driver"/>    
                "url" value="jdbc:mysql://127.0.0.1:3306/mybatis"/>    
                "username" value="root"/>    
                "password" value="root"/>    
            
            
    
    
        "cn/itcast/javaee/mybatis/app05/StudentMapper.xml"/>
    

　　5）创建MyBatisUtil工具类

/**
 * MyBatis工具类
 * @author AdminTC
 */
public class MyBatisUtil {
    private static ThreadLocal threadLocal = new ThreadLocal();
    private static SqlSessionFactory sqlSessionFactory;
    static{
        try {
            Reader reader = Resources.getResourceAsReader("mybatis.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
    private MyBatisUtil(){}
    public static SqlSession getSqlSession(){
        SqlSession sqlSession = threadLocal.get();
        if(sqlSession == null){
            sqlSession = sqlSessionFactory.openSession();
            threadLocal.set(sqlSession);
        }
        return sqlSession;
    }
    public static void closeSqlSession(){
        SqlSession sqlSession = threadLocal.get();
        if(sqlSession != null){
            sqlSession.close();
            threadLocal.remove();
        }
    }
    public static void main(String[] args) {
        Connection conn = MyBatisUtil.getSqlSession().getConnection();
        System.out.println(conn!=null?"连接成功":"连接失败");
    }
}

　　6）创建StudentDao

/**
 * 持久层
 * @author AdminTC
 */
public class StudentDao {
    /**
     * 增加学生（无参）
     */
    public void add1() throws Exception{
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        try{
            sqlSession.insert("mynamespace.add1");
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{
            sqlSession.commit();
        }
        MyBatisUtil.closeSqlSession();
    }
    /**
     * 增加学生（有参）
     */
    public void add2(Student student) throws Exception{
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        try{
            sqlSession.insert("mynamespace.add2",student);
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{
            sqlSession.commit();
        }
        MyBatisUtil.closeSqlSession();
    }
    public static void main(String[] args) throws Exception{
        StudentDao dao = new StudentDao();
        dao.add1();
        dao.add2(new Student(2,"呵呵",8000D));
    }
}

4. mybatis工作流程

　　1)通过Reader对象读取src目录下的mybatis.xml配置文件（改文件的名字和位置可变）
　　2)通过SqlSessionFactoryBuilder对象创建sqlSessionFactory对象
　　3)从当前线程中获取SqlSession对象
　　4)事务开始，mybatis中默认
　　5)通过sqlSession对象读取StudentMapper.xml映射文件中操作编号，从而读取sql语句
　　6)事务提交必写
　　7)关闭SqlSession对象并且分离当前线程与sqlSession对象，让GC尽早回收

5.mybatis配置文件解析

　　1）StudentMapper.xml文件，提倡放在与实体同目录下，文件名任意
　　2）environments连接环境信息优化：
　　　　1.建一个db.properties用来存放连接信息
　　　　2.在mybatis中properties属性可以加载这个配置信息
　　　　3.连接环境配置属性值就可以用${mysql.url}形式从配置文件中获取了
　　3)StudentMapper.xml中insert标签parameterType="app04.Student"每次要类全路径
　　　　简化方式：
　　　　　　1.mybatis中添加
　　　　　　
　　　　　　
　　　　　　
　　　　　　
　　　　　　2.之后在StudentMapper.xml中写类型时都可以用简称student了

<?xml version="1.0" encoding="UTF-8" ?>
"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

namespace="mynamespace">
    "add1">
        insert into students(id,name,sal) values(1,'哈哈',7000)
    
    "add2" parameterType="cn.itcast.javaee.mybatis.app05.Student">
        insert into students(id,name,sal) values(#{id},#{name},#{sal})
    

6.mybatis映射文件祥解(StudentMapper.xml)

　　StudentMapper:存放表映射；sql语句
　　mybatis:连接环境信息，加载映射文件

7.基于MybatisUtil工具类，完成CURD操作

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

namespace="app09.Student">
    
    "app09.Student" id="studentMap">
        "id" column="id"/>
        "name" column="name"/>
        "sal" column="sal"/>
    
    
    
    "add" parameterType="app09.Student">
        insert into students(id,name,sal) values(#{id},#{name},#{sal})
    
    
    <select id="findById" parameterType="int" resultType="app09.Student">
        select * from students where id=#{id}
    select>
    
    <select id="findAll" resultType="app09.Student">
        select id,name,sal from students
    select>
    
    "update" parameterType="app09.Student">
        update students set name=#{name},sal=#{sal}  where id=#{id}
    
    
    "delete" parameterType="app09.Student">
        delete form students where id=#{id}
    
    
        

StudentDao.java

package app09;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import util.MybatisUtil;

public class StudentDao {
    /**
     * 增加学生
     */
    public void add(Student student)throws Exception{
        SqlSession sqlSession=null;
        try{
            sqlSession=MybatisUtil.getSqlSession();
            //开始事务
            sqlSession.insert(Student.class.getName()+".add",student);
            sqlSession.commit();
            //提交
        }catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally{
            //解除关系让GC处理sqlSession对象
            MybatisUtil.closeSqlSession();
        }
    }
    /**
     *根据ID 查询学生
     */
    public Student findById(int id)throws Exception{
        SqlSession sqlSession=null;
        try{
            sqlSession=MybatisUtil.getSqlSession();
            //开始事务
            Student student=sqlSession.selectOne(Student.class.getName()+".findById", id);
            sqlSession.commit();
            return student;
            //提交
        }catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
            throw new RuntimeException();
        }finally{
            //解除关系让GC处理sqlSession对象
            MybatisUtil.closeSqlSession();
        }
    }
    /**
     *    查询所有学生
     */
    public List findAll()throws Exception{
        SqlSession sqlSession=null;
        try{
            sqlSession=MybatisUtil.getSqlSession();
            //开始事务
            return sqlSession.selectOne(Student.class.getName()+".findAll");
            
            //提交
        }catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
            throw new RuntimeException();
        }finally{
            //解除关系让GC处理sqlSession对象
            MybatisUtil.closeSqlSession();
        }
    }
    /**
     * 更新学生 
     */
    public void update(Student student) throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
            sqlSession.update(Student.class.getName()+".update",student);
            sqlSession.commit();
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }
    /**
     * 删除学生 
     */
    public void delete(Student student) throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
            sqlSession.delete(Student.class.getName()+".delete",student);
            sqlSession.commit();
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    public static void main(String[] args) throws Exception{
        StudentDao dao = new StudentDao();
        //dao.add(new Student(1,"哈哈",7000D));
        //dao.add(new Student(2,"呵呵",8000D));
        //dao.add(new Student(3,"班长",9000D));
        //dao.add(new Student(4,"键状高",10000D));
        //Student student = dao.findById(4);
        //List studentList = dao.findAll();
        //for(Student student : studentList){
        //    System.out.print(student.getId()+":"+student.getName()+":"+student.getSal());
        //    System.out.println();
        //}
        Student student = dao.findById(3);
        System.out.println(student.getName());
        //student.setName("靓班长");
        //dao.update(student);
        
        //Student student = dao.findById(3);
        //System.out.print(student.getId()+":"+student.getName()+":"+student.getSal());
        
        //dao.delete(student);
    }
}

8.分页查询

package app10;

import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import util.MybatisUtil;

public class StudentDao {
    public void add(Student student)throws Exception{
        SqlSession sqlSession=null;
        try{
            sqlSession.insert(Student.class.getName()+".add",student);
            sqlSession.commit();
        }catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }
    /**
     * 分页
     * 在映射文件中参数不能是多个，对个多个参数需要封装用map
     * @param start
     * @param size
     * @return
     */
    public List findAllWithFy(int start,int size){
        SqlSession sqlSession=null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
            
            Map map = new LinkedHashMap();
            map.put("pstart",start);
            map.put("psize",size);
            return sqlSession.selectList(Student.class.getName()+".findAllWithFy", map);
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw new RuntimeException();
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }
    /**
     * 分页有条件
     * 在映射文件中参数不能是多个，对个多个参数需要封装用map
     * @param start
     * @param size
     * @return
     */
    public List findAllWithFy(String name,int start,int size){
        SqlSession sqlSession=null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
            
            Map map = new LinkedHashMap();
            map.put("pstart",start);
            map.put("psize",size);
            map.put("pname", "%"+name+"%");
            return sqlSession.selectList(Student.class.getName()+".findAllByNameWithFy", map);
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw new RuntimeException();
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    public static void main(String[] args) throws Exception{
        StudentDao dao = new StudentDao();
        
        //for(int i=1;i<=10;i++){
        //    dao.add(new Student(i,"哈哈",7000D));
        //}
        
        System.out.println("--------------------第一页");
        List studentList1=dao.findAllWithFy("xiao",0, 2);
        
        //List studentList1 = dao.findAllByNameWithFy("哈",0,3);
        for(Student s : studentList1){
            System.out.println(s.getId()+":"+s.getName()+":"+s.getSal());
        }
        //System.out.println("--------------------第二页");
        //List studentList2 = dao.findAllByNameWithFy("哈",3,3);
        //for(Student s : studentList2){
        //    System.out.println(s.getId()+":"+s.getName()+":"+s.getSal());
        //}
        //System.out.println("--------------------第三页");
        //List studentList3 = dao.findAllByNameWithFy("哈",6,3);
        //for(Student s : studentList3){
        //    System.out.println(s.getId()+":"+s.getName()+":"+s.getSal());
        //}
        
    }
}

<?xml version="1.0" encoding="UTF-8" ?>
"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

namespace="app10.Student">
    
    "app10.Student" id="studentMap">
        "id" column="id"/>
        "name" column="name"/>
        "sal" column="sal"/>
    
    

    "add" parameterType="app10.Student">
        insert into students(id,name,sal) values(#{id},#{name},#{sal})
    
    <select id="findAllWithFy" parameterType="map" resultMap="studentMap">
        select id,name,sal from students limit #{pstart},#{psize}
    select>
    <select id="findAllByNameWithFy" parameterType="map" resultMap="studentMap">
        select id,name,sal from students where name like #{pname} limit #{pstart},#{psize}
    select>

9。动态SQL操作

　　1）动态查询：select id,name,sal from students where 1=1 and name=? and sal=?

<where>
            <if test="pid!=null">
                and id=#{pid}
            if>
            <if test="pname!=null">
                and name=#{pname}
            if>
            <if test="psal=null">
                and sal=#{psal}
            if>
where>

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

namespace="app11.Student">
    
    "app11.Student" id="studentMap">
        "id" column="id"/>
        "name" column="name"/>
        "sal" column="sal"/>
    
    

    <select id="findAll" parameterType="map" resultMap="studentMap">
        select * from students
        <where>
            <if test="pid!=null">
                and id=#{pid}
            if>
            <if test="pname!=null">
                and name=#{pname}
            if>
            <if test="psal=null">
                and sal=#{psal}
            if>
        where>
    select>
    

StudentDao.java

package app11;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import util.MybatisUtil;

/**
 * 持久层 
 * @author AdminTC
 */
public class StudentDao {
    /**
     * 有条件的查询所有学生
     */
    public List findAll(Integer id,String name,Double sal) throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
                
            Map map = new LinkedHashMap();
            map.put("pid",id);
            map.put("pname",name);
            map.put("psal",sal);
            
            return sqlSession.selectList(Student.class.getName()+".findAll",map);
        }catch(Exception e){
            e.printStackTrace();
            throw e;
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    public static void main(String[] args) throws Exception{
        StudentDao dao = new StudentDao();
        List studentList = dao.findAll(4,null,null);
        for(Student s : studentList){
            System.out.println(s.getId()+":"+s.getName()+":"+s.getSal());
        }
    }
}

　　2）动态更新：update students set name=?,sal=? where id=?

StudentDao.java

package app12;

import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import util.MybatisUtil;

/**
 * 持久层 
 * @author AdminTC
 */
public class StudentDao {
    /**
     * 有条件更新学生
     */
    public void dynaUpdate(Integer id,String name,Double sal) throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
                
            Map map = new HashMap();
            map.put("pid",id);
            map.put("pname",name);
            map.put("psal",sal);
            sqlSession.update("studentNamespace.dynaUpdate",map);
            sqlSession.commit();
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    public static void main(String[] args) throws Exception{
        StudentDao dao = new StudentDao();
        //关注SQL的变化
        //dao.dynaUpdate(1,null,9000D);//update students set sal=? where id=?
        //dao.dynaUpdate(1,"笨笨",null);//update students set name=? where id=?
        dao.dynaUpdate(1,"笨笨",10000D);//update students set name=? and sal=? where id=?
    }
}

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

namespace="studentNamespace">    

    "app12.Student" id="studentMap">
        "id" column="id"/>
        "name" column="name"/>
        "sal" column="sal"/>
    

    
    "dynaUpdate" parameterType="map">
        update students 
        <set>
            <if test="pname!=null">
                students_name = #{pname},
            if>
            <if test="psal!=null">
                students_sal = #{psal},            
            if>
        set>
        where students_id = #{pid}
    
    
    

　　3）动态删除

delete from students where id in (1,3,5,7)
select id,name,sal from students where id in (2,4,6,8)

StudentDao.java

package app13;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;

import app13.StudentDao;

import util.MybatisUtil;

/**
 * 持久层 
 * @author AdminTC
 */
public class StudentDao {
    /**
     * 根据ID批量删除学生(数组版本)
     * @param ids
     * @throws Exception
     */
    public void dynaDeleteArray(int ...ids)throws Exception{
        SqlSession sqlSession=null;
        try{
            sqlSession=MybatisUtil.getSqlSession();
            sqlSession.delete(Student.class.getName()+".dynaDeleteArray",ids);
            sqlSession.commit();
        }catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
            throw new RuntimeException();
        }finally{
            MybatisUtil.closeSqlSession();
        }    
    }
    
    public void dynaDeleteList(List ids)throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
            sqlSession.delete("studentNamespace.dynaDeleteList",ids);
            sqlSession.commit();
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{
            MybatisUtil.closeSqlSession();
        }
    }
    
    

    
    public static void main(String[] args) throws Exception{
        StudentDao dao = new StudentDao();
        dao.dynaDeleteArray(2,4);
        
        //List ids = new ArrayList();
        //ids.add(6);
        //ids.add(8);
        //ids.add(9);
        //dao.dynaDeleteList(ids);
    }
}

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

namespace="app13.Student">    

    "app13.Student" id="studentMap">
        "id" column="id"/>
        "name" column="name"/>
        "sal" column="sal"/>
    
    
    "dynaDeleteArray">
        delete from students where id in
        
         <foreach collection="array" open="(" close=")" separator="," item="ids">
             #{ids}
         foreach>
    

    "dynaDeleteList">
        delete from students where id in
        <foreach collection="list" open="(" close=")" separator="," item="ids">
            #{ids}
        foreach>
    

　　4）动态插入：insert into student(id,name,sal) values(?,?,?)

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

namespace="studentNamespace">    

    "app14.Student" id="studentMap">
        "id" column="id"/>
        "name" column="name"/>
        "sal" column="sal"/>
    

    
    "key">
        ",">
            <if test="id!=null">
                id,
            if>
            <if test="name!=null">
                name,
            if>
            <if test="sal!=null">
                sal,
            if>
        
        
    
    
    "value">
        
        ",">
            <if test="id!=null">
                #{},
            if>
            <if test="name!=null">
                #{},
            if>
            <if test="sal!=null">
                #{},
            if>
        
        
    
    
    "dynaInsert" parameterType="day14.Student">
        insert into students("key">) values("value">)
    
    

StudentDao.java

package app14;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;

import app13.Student;

import util.MybatisUtil;

/**
 * 持久层 
 * @author AdminTC
 */
public class StudentDao {
    /**
     * 动态插入学生
     */
    public void dynaInsert(Student student) throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
            sqlSession.insert("studentNamespace.dynaInsert",student);
            sqlSession.commit();
        }catch(Exception e){
            e.printStackTrace();
            sqlSession.rollback();
            throw e;
        }finally{
            //MybatisUtil.closeSqlSession();
        }
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    public static void main(String[] args) throws Exception{
        StudentDao dao = new StudentDao();
        //dao.dynaInsert(new Student(1,"哈哈",7000D));//insert into 表名(*,*,*) values(?,?,?)
        dao.dynaInsert(new Student(2,"哈哈",null));//insert into 表名(*,*) values(?,?)
        //dao.dynaInsert(new Student(3,null,7000D));//insert into 表名(*,*) values(?,?)
        //dao.dynaInsert(new Student(4,null,null));//insert into 表名(*) values(?)
    }
}