MyBatis ——CURD操作

目录结构

SqlMapConfig.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://localhost:3306/dbmybatis?serverTimezone=UTC"/>
            <property name="username" value="root"/>
            <property name="password" value="****"/>
        </dataSource>
    </environment>
</environments>
<mappers>
    <mapper resource="com/fy/Dao/UserDao.xml"/>
</mappers>
</configuration>

domain.User

package com.fy.domain;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

QueryVo

package com.fy.domain;

public class QueryVo {
    User user = new User();

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

dao.UserDao

package com.fy.Dao;

import com.fy.domain.QueryVo;
import com.fy.domain.User;
import java.util.List;

/**
 * 用户持久层接口
 */
public interface UserDao {
    /**
     * 查询所有用户
     * @return
     */
    List<User> findAll();

    /**
     * 保存用户
     * @param user
     */
    void saveUser(User user);

    /**
     * 更新用户
     */
    void updateUser(User user);

    /**
     * 删除用户
     */
    void deleteUser(Integer userId);

    /**
     * 根据id查询用户信息
     */
    User findById(Integer userId);

    /**
     * 模糊查询
     */
    List<User> findByName(String username);

    /**
     * 查询总用户数
     */
    int findTotal();


    /**
     * QueryVo模糊查询
     */
    List<User> findByNameVo(QueryVo queryVo);
}

UserDao.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.fy.Dao.UserDao">
    <!--配置查询结果的列名和实体类的属性名的对应关系-->
    <resultMap id="userMap" type="com.fy.domain.User">
        <!--逐渐字段的对应-->
        <id property="" column=""></id>
        <!--非主键的对应-->
        <result property="" column=""></result>
        <!--在select中调用在属性中用resultMap调用-->
    </resultMap>

    <!--查询所有-->
    <select id="findAll" resultType="com.fy.domain.User">
    select * from user;
  </select>
    <!--保存用户-->
    <insert id="saveUser" parameterType="com.fy.domain.User">
        <selectKey keyProperty="id" keyColumn="id" resultType="Integer" order="AFTER">
            select last_insert_id();
        </selectKey>
        insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address})
    </insert>
    <!--更新用户-->
    <update id="updateUser" parameterType="com.fy.domain.User">
        update user set username=#{username},address=#{address},sex=#{sex},birthday=#{birthday} where id=#{id}
    </update>
    <!--删除用户-->
    <update id="deleteUser" parameterType="Integer">
        delete from user where id=#{id}
    </update>
    <!--根据id查询用户-->
    <select id="findById" parameterType="Integer" resultType="com.fy.domain.User">
        select * from user where id=#{id}
    </select>
    <!--根据username模糊查询用户-->
    <select id="findByName" parameterType="String" resultType="com.fy.domain.User">
        select * from user where username like #{username}
    </select>
    <!--根据用户的额总记录条数-->
    <select id="findTotal" resultType="Integer" >
        select count(id) from user
    </select>
    <!--用QueryVo查询-->
    <select id="findByNameVo" parameterType="com.fy.domain.QueryVo" resultType="com.fy.domain.User">
        select * from user where username like #{user.username}
    </select>
</mapper>

测试类

package com.fy;

import com.fy.Dao.UserDao;
import com.fy.domain.QueryVo;
import com.fy.domain.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.InputStream;
import java.util.Date;
import java.util.List;

/**
 * 测试crud操作
 */
public class MyBatis_CRUD {
    private InputStream in;
    private SqlSession sqlSession;
    private UserDao userDao;

    @Before//用在测试方法执行之前执行
    public void init()throws Exception{
        //1.读取配置文件，生成字节输入流
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.获取SqlSessionFactory
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //3.获取SqlSession对象
        sqlSession = factory.openSession();
        //4.获取dao的代理对象
        userDao = sqlSession.getMapper(UserDao.class);
    }
    @After//用在测试方法执行之后执行
    public void destory() throws Exception{
        //6.释放资源
        sqlSession.close();
        in.close();
    }

    @Test
    /**
     * 测试查询操作
     */
    public void testFindAll() {
        //5.执行查询方法
        List<User> users = userDao.findAll();
        for (User user : users) {
            System.out.println(user);
        }
    }

    /**
     * 测试保存操作
     */
    @Test
    public  void testSave(){
        User user = new User();
        user.setUsername("test1");
        user.setBirthday(new Date());
        user.setAddress("武汉市");
        user.setSex("1");
        //5.执行保存操作
        userDao.saveUser(user);
        //提交事务
        sqlSession.commit();
    }

    /**
     * 测试更新操作
     */
    @Test
    public  void testUpdate(){
        User user = new User();
        user.setId(112);
        user.setUsername("更新");
        user.setBirthday(new Date());
        user.setAddress("武汉市");
        user.setSex("0");
        //5.执行更新操作
        userDao.updateUser(user);
        sqlSession.commit();
    }

    /**
     * 测试删除操作
     */
    @Test
    public  void testDelete(){
        //5.执行保存操作
        userDao.deleteUser(1);
        sqlSession.commit();
    }

    /**
     * 测试查询一个id操作
     */
    @Test
    public  void testFindById(){
        //5.执行保存操作
        User user = userDao.findById(112);
        System.out.println(user);
    }

    /**
     * 测试模糊查询操作
     */
    @Test
    public  void testFindByName(){
        //5.执行保存操作
        List<User> users = userDao.findByName("%更%");
        for (User user : users) {
            System.out.println(user);
        }
    }

    /**
     * 测试总记录条数操作
     */
    @Test
    public  void testFindTotal(){
        //5.执行保存操作
        int count = userDao.findTotal();
        System.out.println(count);
    }

    /**
     * 测试QueryVo查询操作
     */
    @Test
    public  void testFindByNameVo(){
        //5.执行保存操作
        QueryVo queryVo = new QueryVo();
        User use = new User();
        use.setUsername("%更%");
        queryVo.setUser(use);
        List<User> users = userDao.findByNameVo(queryVo);
        for (User user : users) {
            System.out.println(user);
        }
    }
}