Mybatis实现数据库表的增删改查

maven项目结构

pom文件

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>org.example</groupId>
  <artifactId>mybatis-study-01</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>mybatis-study-01 Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
  <--！junit单元测试-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
  <--！mysql连接驱动-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.26</version>
    </dependency>
  <--！mybatis-->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.6</version>
    </dependency>
  <--！lombok简化setter/getter-->
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.20</version>
    </dependency>
  <--！分页插件-->
    <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>5.1.4</version>
    </dependency>

  </dependencies>

  <build>
    <--！静态资源过滤-->
    <resources>
      <resource>
        <directory>src/main/java</directory>
        <includes>
          <include>**/*.properties</include>
          <include>**/*.xml</include>
        </includes>
        <filtering>false</filtering>
      </resource>
      <resource>
        <directory>src/main/resources</directory>
        <includes>
          <include>**/*.properties</include>
          <include>**/*.xml</include>
        </includes>
        <filtering>false</filtering>
      </resource>
    </resources>
  </build>


</project>

entity层

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private int id;
    private String name;
    private String sex;
    private int age;
    private String address;
}

Dao层

public interface UserDao {
    //查询所有用户
    List<User> getUserList();
    //根据Id查找用户
    User getUserById(int id);
    //插入一个用户
    int addUser(User user);
    //修改一个用户
    int updateUser(User user);
    //删除一个用户
    int deleteUser(int id);
}

resourecs目录下
UserDao.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.at.dao.UserDao">
 <select id="getUserList" resultType="com.at.pojo.User">
     select * from studytest.user
 </select>
<select id="getUserById" parameterType="int" resultType="com.at.pojo.User">
    select * from studytest.user where id=#{id};
</select>
 <insert id="addUser" parameterType="com.at.pojo.User">
    insert into studytest.user(id,name,sex,age,address) values(#{id},#{name},#{sex},#{age},#{address});
 </insert>
<update id="updateUser" parameterType="com.at.pojo.User">
    update studytest.user set name=#{name},sex=#{sex},age=#{age},address=#{address} where id=#{id};
</update>
<delete id="deleteUser" parameterType="int">
    delete from studytest.user where id=#{id};
</delete>
</mapper>

db.properties数据库配置文件

driver=com.mysql.cj.jdbc.Driver  	//MySQL8的数据库驱动包
url=jdbc:mysql://localhost:3306/studytest？useSSL=true&useUnicode=true&characterEncoding=gbk&Timezone=UTC
user=root
password=123456

mybatis-config.xml mybatis相关配置

<?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>
<--!分页插件-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <property name="helperDialect" value="mysql"/>
        </plugin>
    </plugins>
<--!配置数据源-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/studytest?useSSL=true&amp;useUnicode=true&amp;characterEncoding=gbk&amp;Timezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
<--!配置mapper资源地址--> 
    <mappers>
        <mapper resource="com/at/dao/UserDao.xml"/>
    </mappers>
</configuration>

utils包下，MybatisUtils

public class MybatisUtils {
    /*注入SqlSessionFactory */
    private static  SqlSessionFactory sqlSessionFactory;
    static{
        /*加载配置文件*/
        String resource="mybatis-config.xml";
        try {
            /*创建输入流*/
            InputStream inputStream=Resources.getResourceAsStream(resource);
            /*通过sqlSessionFactory对象调用SqlSessionFactoryBuilder().build()输入流*/
            sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /*开放sqlSessionFactory*/
    public static SqlSession getSqlSession(){
        return  sqlSessionFactory.openSession();
    }
}

新建测试类

public class UserDaoTest {
    SqlSession  sqlSession= MybatisUtils.getSqlSession();
    private SqlSessionFactory sqlSessionFactory;

    /*根据用户年龄升序排序*/
    @Test
    public void test(){
        try{
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            List<User> userList=userDao.getUserList();
            userList.sort(Comparator.comparing(User::getAge));
            userList.forEach(user -> {System.out.println(user.getId()+" "+user.getName()+" "+user.getAge());});
//            for (User user:userList) {
//                System.out.println(user);
//            }
        }catch ( Exception e){
          e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }
	
    /*根据用户id查询用户*/
    @Test
    public void getUserById(){
        SqlSession sqlSession=MybatisUtils.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        User user= mapper.getUserById(2);
        sqlSession.close();
        System.out.println(user);
    }
	
	
	/*添加用户*/
    @Test
    public void addUser(){
     SqlSession sqlSession=MybatisUtils.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        int user = mapper.addUser(new User(7,"李四","女",15,"四川"));
        if (user>0){
            System.out.println("插入成功");
        }
        sqlSession.commit();
        sqlSession.close();
    }
	
	/*更新用户*/
    @Test
    public void UpdateUser(){
        SqlSession sqlSession=MybatisUtils.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        mapper.updateUser(new User(7,"周凯","女",14,"武汉"));
        sqlSession.commit();
        sqlSession.close();
    }
	
	/*删除用户*/
    @Test
    public void deleteUser(){
        SqlSession sqlSession=MybatisUtils.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        mapper.deleteUser(5);
        sqlSession.commit();//事务提交
        sqlSession.close();
    }
	
	/*分页查询*/
    @Test
    public void testPage(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserDao mapper=sqlSession.getMapper(UserDao.class);
        PageHelper.startPage(1,4);
        List<User> users=mapper.getUserList();
        users.forEach(user -> {System.out.println(user+" ");});
        sqlSession.commit();
        sqlSession.close();
    }
}