Mybatis框架二：增删改查

这里是搭建框架和准备数据：

http://www.cnblogs.com/xuyiqing/p/8600888.html

 

实现增删改查功能：

 

测试类：

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

 

 

User.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">
<!-- 写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>