mybatis-01

创建students.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;    }}

在entity目录下创建StudentMapper.xml配置文件

<?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="mynamespace">    <insert id="add1">        insert into students(id,name,sal) values(1,'哈哈',7000)    </insert>    <insert id="add2" parameterType="cn.yun.mybatis.app05.Student">        insert into students(id,name,sal) values(#{id},#{name},#{sal})    </insert></mapper>

在src目录下创建mybatis.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>    <environments default="development">        <environment id="development">            <transactionManager type="JDBC"/>            <dataSource type="POOLED">                <property name="driver" value="com.mysql.jdbc.Driver"/>                 <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis"/>                  <property name="username" value="root"/>                    <property name="password" value="root"/>                </dataSource>        </environment>      </environments>    <mappers>        <mapper resource="cn/yun/javaee/mybatis/app05/StudentMapper.xml"/>    </mappers></configuration>

在util目录下创建MyBatisUtil.java类，并测试与数据库是否能连接

/** * MyBatis工具类 * @author AdminTC */public class MyBatisUtil {    private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();    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?"连接成功":"连接失败");    }}

在dao目录下创建StudentDao.java类并测试

/** * 持久层 * @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));    }}

第五章 mybatis工作流程

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

例子

<?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="mynamespace">        <insert id="add1">                insert into students(id,name,sal) values(1,'哈哈',7000)        </insert>        <!-- 增加学生（无参） -->        <insert id="add2" parameterType="com.yun.entity.Student">                insert into students(id,name,sal) values(#{id},#{name},#{sal})         </insert>        <!-- 增加学生（有参） -->        <update id="update" parameterType="com.yun.entity.Student">                update students set name=#{name},sal=#{sal} where id=#{id}        </update>            <!-- 修改学生 -->         <select id="findById" parameterType="int" resultType="com.yun.entity.Student">                select * from students where id=#{xx}        </select>        <!--    查询多个学生 -->         <select id="findAll"  resultType="com.yun.entity.Student">                select * from students         </select>          <!--    删除学生 -->        <select id="delete"  parameterType="int">                delete from students where id=#{xx}        </select>         <!--    无条件分页查询学生 -->        <select id="findAllWithFy"  parameterType="map" resultType="com.yun.entity.Student">                select id,name,sal from students limit #{pstart},#{psize}        </select>     <!--    有条件分页查询学生 -->        <select id="findAllByNameWithFy"  parameterType="map" resultType="com.yun.entity.Student">                select id,name,sal from students where name like #{pname} limit #{pstart},#{psize}        </select> </mapper>

package com.yun.dao;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import org.apache.ibatis.session.SqlSession;import org.junit.Test;import com.yun.entity.Student;import util.MyBatisUtil;public class StudentDao {    @Test  //增加学生（无参）    public void add1(){        SqlSession sqlSession = MyBatisUtil.getSqlSession();        int line = sqlSession.insert("mynamespace.add1");        sqlSession.commit();        System.out.println("影响了"+line+"行");        MyBatisUtil.closeSqlSession();    }    @Test  //增加学生（有参）    public void add2(){        SqlSession sqlSession = MyBatisUtil.getSqlSession();        int line = sqlSession.insert("mynamespace.add2",new Student(2,"呵呵",8000D));        sqlSession.commit();        System.out.println("影响了"+line+"行");        MyBatisUtil.closeSqlSession();    }    @Test  //增加学生（有参）    public void xx(){        for(int i=0;i<10;i++){            SqlSession sqlSession = MyBatisUtil.getSqlSession();            int line = sqlSession.insert("mynamespace.add2",new Student(3+i,"呵呵",8000D));            sqlSession.commit();            System.out.println("影响了"+line+"行");            MyBatisUtil.closeSqlSession();            i++;        }    }    /**     * 修改学生     */    @Test      public void update(){        SqlSession sqlSession = MyBatisUtil.getSqlSession();        int line = sqlSession.update("mynamespace.update",new Student(1,"中华1111",12000D));        sqlSession.commit();        System.out.println("影响了"+line+"行");        MyBatisUtil.closeSqlSession();    }    /**     * 查询单个学生     */    @Test      public void findById(){        SqlSession sqlSession = MyBatisUtil.getSqlSession();        Student student= sqlSession.selectOne("mynamespace.findById",1);        System.out.println(student);        MyBatisUtil.closeSqlSession();    }    /**     * 查询多个学生     */    @Test      public void findAll(){        SqlSession sqlSession = MyBatisUtil.getSqlSession();        List<Student> student= sqlSession.selectList("mynamespace.findAll");        System.out.println(student);        MyBatisUtil.closeSqlSession();    }    /**     * 删除学生     */    @Test      public void delete(){        SqlSession sqlSession = MyBatisUtil.getSqlSession();        int line= sqlSession.delete("mynamespace.delete",2);        sqlSession.commit();        System.out.println("影响了"+line+"行");        MyBatisUtil.closeSqlSession();    }    /**     * 无条件分页查询学生     */    @Test      public void findAllWithFy(){        SqlSession sqlSession = MyBatisUtil.getSqlSession();        Map<String,Integer> map = new LinkedHashMap<String,Integer>();        map.put("pstart",2);        map.put("psize",4);        List<Student> student= sqlSession.selectList("mynamespace.findAllWithFy",map);        System.out.println(student);        MyBatisUtil.closeSqlSession();    }    /**     * 有条件分页查询学生     */    @Test      public void findAllByNameWithFy(){        SqlSession sqlSession = MyBatisUtil.getSqlSession();        Map<String,Object> map = new LinkedHashMap<String,Object>();        map.put("pstart",0);        map.put("psize",4);        map.put("pname","%11%");        List<Student> student= sqlSession.selectList("mynamespace.findAllByNameWithFy",map);        System.out.println(student);        MyBatisUtil.closeSqlSession();    }}

第十一章 动态SQL操作之查询

1） 查询条件不确定，需要根据情况产生SQL语法，这种情况叫动态SQL
/**
* 动态SQL–查询
*/

<?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="mynamespace">    <select id="dynaSQLwithSelect" parameterType="map" resultType="cn.yun.mybatis.app11.Student">        select id,name,sal from students        <where>            <if test="pname!=null">                and name=#{pname}            </if>            <if test="psal!=null">                and sal=#{psal}            </if>        </where>        </select></mapper>

@Test      public void dynaSQLwithSelect(){        SqlSession sqlSession = MyBatisUtil.getSqlSession();        Map<String,Object> map = new LinkedHashMap<String,Object>();//      map.put("pstart",0);//      map.put("psize",4);        map.put("pname","%呵%");        map.put("psal",8000);        List<Student> student= sqlSession.selectList("mynamespace.dynaSQLwithSelect",map);        for(Student s:student){            System.out.println(s);        }        MyBatisUtil.closeSqlSession();    }

/**
* 动态SQL–更新
*/

 <select id="dynaSQLwithUpdate"  parameterType="com.yun.entity.Student" >                update students                 <set>                    <if test="name!=null">                        name=#{name},                    </if>                    <if test="sal!=null">                        sal=#{sal},                    </if>                </set>                        where id=#{id}</select>

@Test      public void dynaSQLwithUpdate(){        SqlSession sqlSession = MyBatisUtil.getSqlSession();        int line = sqlSession.update("mynamespace.dynaSQLwithUpdate",new Student(11,"中",12000D));        sqlSession.commit();        System.out.println("影响了"+line+"行");        MyBatisUtil.closeSqlSession();    }

/**
* 动态SQL–删除
*/

<delete id="dynaSQLwithDelete">                delete from students where id in                <foreach collection="array" open="(" close=")" separator="," item="ids">                    ${ids}                </foreach>                    <!--                <foreach collection="list" open="(" close=")" separator="," item="ids">                    ${ids}                </foreach>  -->    </delete>   

@Test      public void dynaSQLwithDelete(){        SqlSession sqlSession = MyBatisUtil.getSqlSession();        int[] ids=new int[]{1,3,5,7,9,11};        int line = sqlSession.delete("mynamespace.dynaSQLwithDelete",ids);        sqlSession.commit();        System.out.println("影响了"+line+"行");        MyBatisUtil.closeSqlSession();    }

/**
* 动态SQL–插入
*/

<sql id="key">        <trim suffixOverrides=",">            <if test="id!=null">                id,            </if>            <if test="name!=null">                name,            </if>            <if test="sal!=null">                sal,            </if>        </trim>    </sql>    <sql id="value">        <trim suffixOverrides=",">            <if test="id!=null">                #{id},            </if>            <if test="name!=null">                #{name},            </if>            <if test="sal!=null">                #{sal},            </if>        </trim>    </sql>        <insert id="dynaSQLwithInsert" parameterType="com.yun.entity.Student">                insert into students(<include refid="key"/>) values(<include refid="value"/>)</insert>

@Test      public void dynaSQLwithInsert(){        SqlSession sqlSession = MyBatisUtil.getSqlSession();        int line = sqlSession.insert("mynamespace.dynaSQLwithInsert",new Student(11,"中11",12000D));        sqlSession.commit();        System.out.println("影响了"+line+"行");        MyBatisUtil.closeSqlSession();}