Mybatis小测试实现单表的增删改查及其他

Mybatis小测试

---

层级

---

配置文件

jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc\:mysql\://localhost\:3306/project
jdbc.username=root
jdbc.password=root

log4j.properties

# Global logging configuration�����鿪��������Ҫ��debug
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

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>

    <!--核心配置文件-->

    <properties resource="jdbc.properties"></properties>


    <typeAliases>
        <package name="com.baidu.poji"></package>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>


    <mappers>
        <package name="com.baidu.mapper"/>
    </mappers>
</configuration>

---

Users

package com.baidu.poji;

/**
 * @类名： Users
 * @说明： TODO
 * @作者： 小马
 * @时间：2019/11/18 14:40
 */

public class Users {
    private Integer id;
    private String name;
    private String phone;

    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 String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "Users{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }
}

---

UsersMapper

package com.baidu.mapper;

import com.baidu.poji.Users;

import java.util.List;

public interface UsersMapper {

    //查询list
    List<Users> findAll();

    //按id查询
    Users findById(int id);

    //模糊查询
    List<Users> findNameLike(String name);

    //添加
    void save(Users users);

    //更新
    void update(Users users);

    //删除
    void delete(int id);

    //查询数量
    int findCount();
}

UsersMapper.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.baidu.mapper.UsersMapper">
    <!--查询list-->
    <select id="findAll" resultType="Users">
        select * from users
    </select>

    <!--按id查询-->
    <select id="findById" resultType="Users" parameterType="int">
        select * from users where id=#{id}
    </select>

    <!--模糊查询-->
    <select id="findNameLike" resultType="Users" parameterType="string">
        select * from users where name like #{name}
    </select>

    <!--添加-->
    <insert id="save" parameterType="Users">
    <!--得到此id-->
    <selectKey keyProperty="id" resultType="int" order="AFTER">
        select LAST_INSERT_ID()
    </selectKey>
        insert into users set name=#{name},phone=#{phone}
    </insert>

    <!--更新-->
    <update id="update" parameterType="users">
        update users set name=#{name},phone=#{phone} where id=#{id}
    </update>

    <!--删除-->
    <delete id="delete" parameterType="int">
        delete from users where id=#{id}
    </delete>

    <!--查询数量-->
    <select id="findCount" resultType="int">
        select count(*) from users
    </select>
</mapper>

---

测试类

package com.baidu.test;


import com.baidu.mapper.UsersMapper;
import com.baidu.poji.Users;
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.Before;
import org.junit.Test;


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

/**
 * @类名： UsersTest
 * @说明： 测试类
 * @作者： 小马
 * @时间：2019/11/18 14:47
 */

public class UsersTest {

    InputStream inputStream;
    SqlSessionFactory sqlSessionFactory;


    @Before
    public void test() throws IOException {
        inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    //查询list
    @Test
    public void findAllTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
        List<Users> list = mapper.findAll();
        list.forEach(c-> System.out.println(c));
        sqlSession.close();
    }


    //按id查询
    @Test
    public void findByIdTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
        Users byId = mapper.findById(1);
        System.out.println(byId);
        sqlSession.close();
    }

    //模糊查询
    @Test
    public void findNameLikeTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
        List<Users> nameLike = mapper.findNameLike("%张%");
        nameLike.forEach(c-> System.out.println(c));
        sqlSession.close();
    }

    //添加
    @Test
    public void saveTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
        Users users=new Users();
        users.setName("王二麻子");
        users.setPhone("666666");
        mapper.save(users);
        sqlSession.commit();
        //得到此id
        System.out.println(users.getId());
        sqlSession.close();
    }

    //更新
    @Test
    public void updateTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
        Users users=new Users();
        users.setId(8);
        users.setName("老王");
        users.setPhone("6777");
        mapper.update(users);
        sqlSession.commit();
        sqlSession.close();
    }

    //删除
    @Test
    public void deleteTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
        mapper.delete(8);
        sqlSession.commit();
        sqlSession.close();
    }


    //查询数量
    @Test
    public void findCountTest(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
        int count = mapper.findCount();
        System.out.println(count);
        sqlSession.close();
    }
}