mybatis的增删改查

首先在Navicat咱们的数据库中新建一个student的表，添加上字段，id，usernam,phone,sex,age。写上一些数据。

接着看一下怎么完成对它的查询。

第一章：代理Dao方式的CRUD操作

1. 代理Dao方式的增删改查

        1. 创建项目

        2. UserDao接口代码

import com.qcby.entity.User;

import java.util.List;

public interface UserDao {
    /**
     * 全部查询
     * @return
     */
    public List<User> findAll(); //返回值类型

    /**
     * 根据id进行查询
     * @param id
     */
    public User findById(int id);

    /**
     *  插入数据
     * @param user
     * @return
     */
    public int insert(User user); //封装成User对象

    /**
     * 删除
     * @param id
     * @return
     */
    public int delete(int id);

    /**
     * 修改
     * @param user
     * @return
     */
    public int update(User user);

    /**
     * 获取插入的id
     * @param user
     * @return
     */
    public int insertGetId(User user);

    /**
     * 通过姓名模糊查询
     * @return
     */
    public List<User> likeByName(String username);
}

2.mapper层

SqlMapConfig.xml里把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="com.qcby.Dao.UserDao">
    <select id="findAll" resultType="com.qcby.entity.User">
        select * from user
    </select>

    <select id="findById" resultType="com.qcby.entity.User" parameterType="java.lang.Integer">  //查询入参
        select * from user where id = #{id}
    </select>

    <insert id="insert" parameterType="com.qcby.entity.User">
        insert into user(username,birthday,sex,address)
        values(#{username},#{birthday},#{sex},#{address})
    </insert>

    <delete id="delete" parameterType="java.lang.Integer">
        delete from user where id = #{id}
    </delete>

    <update id="update" parameterType="com.qcby.entity.User">
        update user set username = #{username},birthday = #{birthday},
                sex = #{sex},address = #{address} where id = #{id}
    </update>

    <!--返回主键 ：我们的主键需要设置自动递增 -->
    <insert id="insertGetId" parameterType="com.qcby.entity.User">
        <selectKey keyProperty="id" resultType="int" order="AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into user(username,birthday,sex,address)
        values(#{username},#{birthday},#{sex},#{address})
    </insert>

    <!--${}:拼接 , #{}预编译 -->
    <select id="likeByName" resultType="com.qcby.entity.User" parameterType="java.lang.String">
        select * from user where username like '%${value}%';
    </select>

</mapper>

3.测试类

import com.qcby.dao.UserDao;
import com.qcby.entity.User;
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.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

public class UserTest {

    private InputStream in = null;
    private SqlSession session = null;
    private UserDao mapper = null;

    @Before  //注解：@Before：在所有方法之前执行
    public void init() throws IOException {
        //加载主配置文件，目的是为了构建SqlSessionFactory对象
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //创建SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //通过SqlSessionFactory工厂对象创建SqlSesssion对象
        session = factory.openSession();
        //通过Session创建UserDao接口代理对象
        mapper = session.getMapper(UserDao.class);
    }

    //关闭session对象
    @After
    public void destory() throws IOException {
        //释放资源
        session.close();
        in.close();
    }

    /**
     * 测试查询所有的方法
     */
    @Test
    public void findAll() throws IOException {
        List<User> users = mapper.findAll();
        for (User user:users) {
            System.out.println(user.toString());
        }
    }

    @Test
    public void findById() {
        User user = mapper.findById(2);
        System.out.println(user.toString());
    }


    @Test
    public void insert(){
        User user = new User();
        user.setAddress("保定");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setUsername("张五");
        
        int code = mapper.insert(user);
        //数据修改，需要做session提交
        session.commit();
        System.out.println(code);
    }

    @Test
    public void delete(){
        int code = mapper.delete(7);
        session.commit();
        System.out.println(code);
    }

    @Test
    public void update(){
        User user = new User();
        user.setId(6);
        user.setUsername("赵四");
        user.setBirthday(new Date());
        user.setAddress("保定");
        user.setSex("男");
        int code = mapper.update(user);
        session.commit();
        System.out.println(code);
    }

    @Test
    public void insertGetId(){
        User user = new User();
        user.setUsername("张四");
        user.setBirthday(new Date());
        user.setAddress("保定");
        user.setSex("男");
        int code = mapper.insertGetId(user);
        session.commit();
        System.out.println(code);
        System.out.println(user.getId());
    }

    @Test
    public void likeByName(){
        List<User> users = mapper.likeByName("熊");
        for (User user: users) {
            System.out.println(user);
        }
    }
}

测试查询所有结果:（finaAll）

 根据id查询结果：（findById）

 添加结果：（insert）

 修改结果：（update）