MyBatis的增删改查操作

0.准备数据库

CREATE TABLE USER(
id INT(11) NOT NULL AUTO_INCREMENT,
username VARCHAR(32),
birthday DATETIME,
sex CHAR(1),
address VARCHAR(256),
PRIMARY KEY(id)
);
INSERT INTO USER(id,username,birthday,sex,address) VALUES(41,"老王",'2018-2-27','男',"北京市顺义区");

INSERT INTO USER(id,username,birthday,sex,address) VALUES(42,"小二王",'2018-3-2','女',"北京市金燕龙");

INSERT INTO USER(id,username,birthday,sex,address) VALUES(43,"小二王",'2018-3-4','女',"北京市金燕龙");

INSERT INTO USER(id,username,birthday,sex,address) VALUES(44,"老王",'2018-3-7','男',"北京");

INSERT INTO USER(id,username,birthday,sex,address) VALUES(45,"小马宝莉",'2018-3-8','女',"北京修正");

1.导入项目依赖，建议放进父工程中

<?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>com.heng</groupId>
    <artifactId>MyBatis-HeiMa-Study</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>mybatis-01</module>
        <module>mybatis-02</module>
    </modules>
    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
    <dependencies>
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/log4j/log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/junit/junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
            <scope>provided</scope>
        </dependency>

    </dependencies>
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>

        <pluginManagement>
            <plugins>
                <plugin>
                    <groupId>org.apache.tomcat.maven</groupId>
                    <artifactId>tomcat7-maven-plugin</artifactId>
                    <version>2.2</version>
                </plugin>
            </plugins>
        </pluginManagement>
    </build>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
</project>

2.写实体类

package com.ghh.domain;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.Date;
@AllArgsConstructor
@NoArgsConstructor
@Data
public class User implements Serializable {
    private Integer id;
    private String username;
    private String address;
    private String sex;
    private Date birthday;
}

3.写核心配置文件

<?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="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis_heima?
                useSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="123"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/ghh/dao/UserDao.xml"/>
    </mappers>
</configuration>

主要的操作就在下面的三个步骤中：

4.写操作数据库的方法接口

package com.ghh.dao;

import com.ghh.domain.User;

import java.util.List;

public interface UserDao {
    List<User> findAll();
    int saveUser(User user);
    int updateUser(User user);
    int deleteUser(Integer id);
    User findById(Integer id);
    List<User> findByName(String username);
    int findTotal();
}

5.写接口的配置文件

<?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.ghh.dao.UserDao">

    <!--查询所有用户-->
    <select id="findAll" resultType="com.ghh.domain.User">
        select * from user;
    </select>

    <!--添加用户-->
    <insert id="saveUser" parameterType="com.ghh.domain.User">
        insert into user(username,address,sex,birthday) values (#{username},#{address},#{sex},#{birthday})
    </insert>

    <!--修改用户-->
    <update id="updateUser" parameterType="com.ghh.domain.User">
        update user set username=#{username},address=#{address},sex=#{sex},birthday=#{birthday} where id=#{id}
    </update>

    <!--删除用户-->
    <delete id="deleteUser" parameterType="Int">
        delete from user where id=#{uid}
    </delete>

    <!--根据id查询用户-->
    <select id="findById" parameterType="int" resultType="com.ghh.domain.User">
        select * from user where id=#{uid}
    </select>

    <!--根据用户名模糊查询用户-->
    <select id="findByName" parameterType="String" resultType="com.ghh.domain.User">
        select * from user where username like #{name}
    </select>

    <!--查询总记录数-->
    <select id="findTotal" resultType="int">
        select count(id) from user;
    </select>
</mapper>

6.写测试类

package com.ghh.test;

import com.ghh.dao.UserDao;
import com.ghh.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.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

public class MybatisTest {
    private InputStream inputStream;
    private SqlSession sqlSession;
    private UserDao mapper;
    @Before
    public void init() throws IOException {
        //1.读取配置文件
        inputStream =
                Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.获取SqlSessionFactoryBuilder对象
        SqlSessionFactoryBuilder builder =
                new SqlSessionFactoryBuilder();
        //3.通过SqlSessionFactoryBuilder对象创建sqlSessionFqctory对象
        SqlSessionFactory sqlSessionFactory =
                builder.build(inputStream);
        //通过SqlSessionFactory对象创建SqlSession对象
        sqlSession = sqlSessionFactory.openSession();
        //获取dao的代理对象
        mapper = sqlSession.getMapper(UserDao.class);
    }

    @After
    public void destroy() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        inputStream.close();
    }

    @Test
    public void testFindAll() throws IOException {
        List<User> users = mapper.findAll();
        for(User user:users){
            System.out.println(user);
        }
    }

    @Test
    public void testSaveUser(){
        User user = new User();
        user.setUsername("丽丽");
        user.setAddress("南京");
        user.setSex("男");
        user.setBirthday(new Date());
        mapper.saveUser(user);
    }

    @Test
    public void testUpdateUser(){
        User user = new User();
        user.setUsername("郑爽");
        user.setSex("女");
        user.setBirthday(new Date());
        user.setAddress("北京");
        user.setId(49);

        mapper.updateUser(user);
    }

    @Test
    public void testDeleteUser(){
        mapper.deleteUser(48);
    }

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

    @Test
    public void testFindByName(){
        List<User> users = mapper.findByName("%王%");
        for(User user:users){
            System.out.println(user);
        }
    }
    
    @Test
    public void testFindTotal(){
        int count = mapper.findTotal();
        System.out.println(count);
    }
}