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();
}