package junit;

import java.io.InputStream;

import java.util.Date;

import java.util.List;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.junit.Test;

import pojo.User;

public class MybatisTest {

    //根据用户名称模糊查询用户列表

    @Test

    public void testfindUserByUsername() throws Exception {

        //加载核心配置文件

        String resource = "sqlMapConfig.xml";

        InputStream in = Resources.getResourceAsStream(resource);

        //创建SqlSessionFactory

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

        //创建SqlSession

        SqlSession sqlSession = sqlSessionFactory.openSession();

        //执行Sql语句

        List<User> users = sqlSession.selectList("test.findUserByUsername", "五");

        for (User user2 : users) {

            System.out.println(user2);

        }

    }

    //添加用户

    @Test

    public void testInsertUser() throws Exception {

        //加载核心配置文件

        String resource = "sqlMapConfig.xml";

        InputStream in = Resources.getResourceAsStream(resource);

        //创建SqlSessionFactory

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

        //创建SqlSession

        SqlSession sqlSession = sqlSessionFactory.openSession();

        //执行Sql语句

        User user = new User();

        user.setUsername("萧炎");

        user.setBirthday(new Date());

        user.setAddress("斗气大陆");

        user.setSex("男");

        int i = sqlSession.insert("test.insertUser", user);

        sqlSession.commit();

        //这里可以获得新建数据的ID，原因见XML文件

        System.out.println(user.getId());

    }

    //更新用户

    @Test

    public void testUpdateUserById() throws Exception {

        //加载核心配置文件

        String resource = "sqlMapConfig.xml";

        InputStream in = Resources.getResourceAsStream(resource);

        //创建SqlSessionFactory

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

        //创建SqlSession

        SqlSession sqlSession = sqlSessionFactory.openSession();

        //执行Sql语句

        User user = new User();

        user.setId(27);

        user.setUsername("唐三");

        user.setBirthday(new Date());

        user.setAddress("斗罗大陆");

        user.setSex("男");

        int i = sqlSession.update("test.updateUserById", user);

        sqlSession.commit();

    }

    //删除

    @Test

    public void testDelete() throws Exception {

        //加载核心配置文件

        String resource = "sqlMapConfig.xml";

        InputStream in = Resources.getResourceAsStream(resource);

        //创建SqlSessionFactory

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

        //创建SqlSession

        SqlSession sqlSession = sqlSessionFactory.openSession();

        sqlSession.delete("test.deleteUserById", 27);

        sqlSession.commit();

    }

}

<?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">

<!-- 写Sql语句   -->

<mapper namespace="test">

    <!-- 通过ID查询一个用户 -->

    <select id="findUserById" parameterType="Integer" resultType="pojo.User">

        select * from user where id = #{v}

    </select>

    <!-- 根据用户名称模糊查询用户列表

    这里区分下#和$

    #{}    select * from user where id = ？    占位符  ? ==  '五'

            自动添加引号

    ${}    select * from user where username like '%${name}%'=='%五%'  字符串拼接

            原样拼接

    -->

    <select id="findUserByUsername" parameterType="String" resultType="pojo.User">

        select * from user where username like '%${value}%'

    </select>

    <!--这里其实这样写也可以,不习惯的话可以采用上面的方式：

        select * from user where username like "%"#{value}"%"

     -->

    <!-- 添加用户 -->

    <insert id="insertUser" parameterType="pojo.User">

        <!-- 这里注意：将插入的数据的主键返回user对象

            只要使用MySQL,order属性必须是AFTER，因为主键自增情况下：

            MySQL数据存储是先存再创建ID

        -->

        <selectKey keyProperty="id" resultType="Integer" order="AFTER">

            select LAST_INSERT_ID()

        </selectKey>

        insert into user (username,birthday,address,sex)

        values (#{username},#{birthday},#{address},#{sex})

    </insert>

    <!-- 更新 -->

    <update id="updateUserById" parameterType="pojo.User">

        update user

        set username = #{username},sex = #{sex},birthday = #{birthday},address = #{address}

        where id = #{id}

    </update>

    <!-- 删除 -->

    <delete id="deleteUserById" parameterType="Integer">

        delete from user

        where id = #{userid}

    </delete>

</mapper>