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：

 1 package com.yas.entity;
 2 
 3 import java.util.Date;
 4 
 5 public class SysUser {
 6     private Integer id;
 7     private String account;
 8     private String username;
 9     private String pwd;
10     private Date gmt_create;
11     private Date gmt_modified;
12 
13     public SysUser() {
14     }
15 
16     public SysUser(Integer id, String account, String username, String pwd, Date gmt_create, Date gmt_modified) {
17         this.id = id;
18         this.account = account;
19         this.username = username;
20         this.pwd = pwd;
21         this.gmt_create = gmt_create;
22         this.gmt_modified = gmt_modified;
23     }
24 
25     public Integer getId() {
26         return id;
27     }
28 
29     public void setId(Integer id) {
30         this.id = id;
31     }
32 
33     public String getAccount() {
34         return account;
35     }
36 
37     public void setAccount(String account) {
38         this.account = account;
39     }
40 
41     public String getUsername() {
42         return username;
43     }
44 
45     public void setUsername(String username) {
46         this.username = username;
47     }
48 
49     public String getPwd() {
50         return pwd;
51     }
52 
53     public void setPwd(String pwd) {
54         this.pwd = pwd;
55     }
56 
57     public Date getGmt_create() {
58         return gmt_create;
59     }
60 
61     public void setGmt_create(Date gmt_create) {
62         this.gmt_create = gmt_create;
63     }
64 
65     public Date getGmt_modified() {
66         return gmt_modified;
67     }
68 
69     public void setGmt_modified(Date gmt_modified) {
70         this.gmt_modified = gmt_modified;
71     }
72 
73     @Override
74     public String toString() {
75         return "SysUser{" +
76                 "id=" + id +
77                 ", account='" + account + '\'' +
78                 ", username='" + username + '\'' +
79                 ", pwd='" + pwd + '\'' +
80                 ", gmt_create=" + gmt_create +
81                 ", gmt_modified=" + gmt_modified +
82                 '}';
83     }
84 }

5、增删改查的接口UserDAO：

 1 package com.yas.dao;
 2 
 3 import com.yas.entity.SysUser;
 4 import org.apache.ibatis.annotations.Param;
 5 
 6 import java.util.List;
 7 import java.util.Map;
 8 
 9 public interface UserDAO {
10     SysUser queryUserById(Integer id);
11 
12     SysUser queryUserByIdAndUsername(@Param("id") Integer id, @Param("username") String username);
13 
14     SysUser queryUserByMap(Map map);
15 
16     SysUser queryUserByEntity(SysUser user);
17 
18     List<SysUser> queryUserByUsername(@Param("username") String username);
19 
20     Integer deleteUser(@Param("id") Integer id);
21 
22     Integer updateUser(SysUser user);
23 
24     Integer insertUser(SysUser user);
25 }

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、测试类：

 1 import com.yas.dao.UserDAO;
 2 import com.yas.entity.SysUser;
 3 import org.apache.ibatis.io.Resources;
 4 import org.apache.ibatis.session.SqlSession;
 5 import org.apache.ibatis.session.SqlSessionFactory;
 6 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 7 
 8 import java.io.IOException;
 9 import java.io.InputStream;
10 import java.util.Date;
11 import java.util.HashMap;
12 import java.util.List;
13 import java.util.Map;
14 
15 public class TestMyBatis {
16     public static void main(String[] args) throws IOException {
17         // MyBatis API
18         // 1、加载配置文件
19         InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
20 
21         // 2、构建 SqlSessionFactory
22         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
23 
24         // 3、通过SqlSessionFactory，创建 SqlSession
25         SqlSession sqlSession = sqlSessionFactory.openSession();
26 
27         // 4、通过SqlSession，获得DAO实现类的对象
28         UserDAO mapper = sqlSession.getMapper(UserDAO.class);//获取UserDAO实现类的对象
29 
30         // 5、测试查询
31 //        SysUser sysUser1 = mapper.queryUserById(1);
32 //        System.out.println(sysUser1);
33 
34 //        SysUser sysUser2 = mapper.queryUserByIdAndUsername(2,"刘翔");
35 //        System.out.println(sysUser2);
36 
37 //        Map<String,String> map = new HashMap<>();
38 //        map.put("id","1");
39 //        map.put("username","管理员");
40 //        SysUser sysUser3 = mapper.queryUserByMap(map);
41 //        System.out.println(sysUser3);
42 
43 //        SysUser user = new SysUser();
44 //        user.setId(1);
45 //        user.setUsername("管理员");
46 //        SysUser user4 = mapper.queryUserByEntity(user);
47 //        System.out.println(user4);
48 
49 //        List<SysUser> users = mapper.queryUserByUsername("C");
50 //        for (SysUser user : users) {
51 //            System.out.println(user);
52 //        }
53 
54 //        mapper.deleteUser(4);
55 
56 
57 //        SysUser user = new SysUser(4,"manager","李白","000000",new Date(),new Date());
58 //        Integer result = mapper.updateUser(user);
59 //        System.out.println(result);
60 
61 
62         SysUser user = new SysUser(null,"manager","王维","000000",new Date(),new Date());
63         Integer result = mapper.insertUser(user);
64         System.out.println(user);
65 
66 
67         sqlSession.commit();
68 //        sqlSession.rollback();
69     }
70 }