MyBatis的增删改查Spring方式

使用IDEA建立一个空的Maven项目。

1、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>MyBatisDemo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
</project>

2、在resources目录下(类路径)建立mybatis核心配置文件，mybatis-config.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>
        <!-- 单独注册一个类的别名 -->
<!--        <typeAlias type="com.yas.entity.SysUser" alias="sysuser"></typeAlias>-->

        <!-- 包内的每个类都自动用其类名作为别名 -->
        <package name="com.yas.entity"/>
    </typeAliases>

    <!-- 核心配置信息 -->
    <environments default="default_config">
        <!-- 数据库相关配置 -->
        <environment id="default_config">
            <!-- 事务控制类型 -->
            <transactionManager type="jdbc"></transactionManager>
            <!-- 数据库连接参数 -->
            <dataSource type="org.apache.ibatis.datasource.pooled.PooledDataSourceFactory">
                <!-- & 转义为 &amp; -->
<!--                <property name="driver" value="com.mysql.jdbc.Driver"/>-->
<!--                <property name="url" value="jdbc:mysql://localhost:3306/lending_management?useUnicode=true&amp;characterEncoding=utf-8"/>-->
<!--                <property name="username" value="root"/>-->
<!--                <property name="password" value="cs1986@0312"/>-->
                <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>
    <!-- 注册mapper文件 -->
    <mappers>
        <mapper resource="UserDAOMapper.xml"></mapper>
    </mappers>
</configuration>

3、在resources目录下(类路径)建立ORM映射文件，UserDAOMapper.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.yas.dao.UserDAO">
<!--    <select id="queryUserById" resultType="com.yas.entity.SysUser">-->
    <select id="queryUserById" resultType="sysuser">
        SELECT * FROM sys_user WHERE id = #{id}
    </select>


    <select id="queryUserByIdAndUsername" resultType="sysuser">
        SELECT * FROM sys_user WHERE id = #{id} and username = #{username}
    </select>

    <select id="queryUserByMap" resultType="sysuser">
        SELECT * FROM sys_user WHERE id = #{id} and username = #{username}
    </select>

    <select id="queryUserByEntity" resultType="sysuser">
        SELECT * FROM sys_user WHERE id = #{id} and username = #{username}
    </select>

    <select id="queryUserByUsername" resultType="sysuser">
        SELECT * FROM sys_user WHERE username like concat('%',#{username},'%')
    </select>

    <delete id="deleteUser" parameterType="int">
        DELETE FROM sys_user WHERE id = #{id}
    </delete>

    <update id="updateUser" parameterType="sysuser">
        UPDATE sys_user SET username = #{username} WHERE id = #{id}
    </update>

<!--    <insert id="insertUser" parameterType="sysuser">-->
<!--        <selectKey order="AFTER" resultType="int" keyProperty="id">-->
<!--            select last_insert_id()-->
<!--        </selectKey>-->
<!--        INSERT INTO sys_user VALUES(#{id},#{account},#{username},#{pwd},#{gmt_create},#{gmt_modified})-->
<!--    </insert>-->

    <insert id="insertUser" parameterType="sysuser" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO sys_user VALUES(#{id},#{account},#{username},#{pwd},#{gmt_create},#{gmt_modified})
    </insert>
</mapper>

4、建立实体类SysUser：

package com.yas.entity;

import java.util.Date;

public class SysUser {
    private Integer id;
    private String account;
    private String username;
    private String pwd;
    private Date gmt_create;
    private Date gmt_modified;

    public SysUser() {
    }

    public SysUser(Integer id, String account, String username, String pwd, Date gmt_create, Date gmt_modified) {
        this.id = id;
        this.account = account;
        this.username = username;
        this.pwd = pwd;
        this.gmt_create = gmt_create;
        this.gmt_modified = gmt_modified;
    }

    public Integer getId() {
        return id;
    }

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

    public String getAccount() {
        return account;
    }

    public void setAccount(String account) {
        this.account = account;
    }

    public String getUsername() {
        return username;
    }

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

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public Date getGmt_create() {
        return gmt_create;
    }

    public void setGmt_create(Date gmt_create) {
        this.gmt_create = gmt_create;
    }

    public Date getGmt_modified() {
        return gmt_modified;
    }

    public void setGmt_modified(Date gmt_modified) {
        this.gmt_modified = gmt_modified;
    }

    @Override
    public String toString() {
        return "SysUser{" +
                "id=" + id +
                ", account='" + account + '\'' +
                ", username='" + username + '\'' +
                ", pwd='" + pwd + '\'' +
                ", gmt_create=" + gmt_create +
                ", gmt_modified=" + gmt_modified +
                '}';
    }
}

5、增删改查的接口UserDAO：

package com.yas.dao;

import com.yas.entity.SysUser;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface UserDAO {
    SysUser queryUserById(Integer id);

    SysUser queryUserByIdAndUsername(@Param("id") Integer id, @Param("username") String username);

    SysUser queryUserByMap(Map map);

    SysUser queryUserByEntity(SysUser user);

    List<SysUser> queryUserByUsername(@Param("username") String username);

    Integer deleteUser(@Param("id") Integer id);

    Integer updateUser(SysUser user);

    Integer insertUser(SysUser user);
}

6、数据库连接信息配置文件，jdbc.properties：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/lending_management?useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=root

7、log4j配置文件，log4j.properties：

log4j.rootLogger=DEBUG, stdout

log4j.logger.org.mybatis.example.BlogMapper=TRACE

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

8、测试类：

import com.yas.dao.UserDAO;
import com.yas.entity.SysUser;
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 java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TestMyBatis {
    public static void main(String[] args) throws IOException {
        // MyBatis API
        // 1、加载配置文件
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");

        // 2、构建 SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        // 3、通过SqlSessionFactory，创建 SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 4、通过SqlSession，获得DAO实现类的对象
        UserDAO mapper = sqlSession.getMapper(UserDAO.class);//获取UserDAO实现类的对象

        // 5、测试查询
//        SysUser sysUser1 = mapper.queryUserById(1);
//        System.out.println(sysUser1);

//        SysUser sysUser2 = mapper.queryUserByIdAndUsername(2,"刘翔");
//        System.out.println(sysUser2);

//        Map<String,String> map = new HashMap<>();
//        map.put("id","1");
//        map.put("username","管理员");
//        SysUser sysUser3 = mapper.queryUserByMap(map);
//        System.out.println(sysUser3);

//        SysUser user = new SysUser();
//        user.setId(1);
//        user.setUsername("管理员");
//        SysUser user4 = mapper.queryUserByEntity(user);
//        System.out.println(user4);

//        List<SysUser> users = mapper.queryUserByUsername("C");
//        for (SysUser user : users) {
//            System.out.println(user);
//        }

//        mapper.deleteUser(4);


//        SysUser user = new SysUser(4,"manager","李白","000000",new Date(),new Date());
//        Integer result = mapper.updateUser(user);
//        System.out.println(result);


        SysUser user = new SysUser(null,"manager","王维","000000",new Date(),new Date());
        Integer result = mapper.insertUser(user);
        System.out.println(user);


        sqlSession.commit();
//        sqlSession.rollback();
    }
}