Maven+Mybatis一些简单例子

一、创建maven工程

把依赖的包写在pom.xml中。保存后，工程会有错，需要在工程上右键选择“Maven-->Update Project”

pom.xml内容为

<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.mybatis</groupId>
  <artifactId>example</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>
  
  <dependencies>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        <scope>test</scope>
    </dependency>
    
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.3.0</version>
    </dependency>
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.38</version>
    </dependency>
    
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.12</version>
    </dependency>
    
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-log4j12</artifactId>
        <version>1.7.12</version>
    </dependency>
    
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
  </dependencies>
  
  <build>
    <plugins>
        <plugin>
            <artifactId>maven-compiler-plugin</artifactId>
            <configuration>
                <source>1.8</source>
                <target>1.8</target>
            </configuration>
        </plugin>
    </plugins>
  </build>
</project>

二、简单的例子

①创建数据库mybatis，创建表sys_user，sys_role，sys_user_role，并插入数据

DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户Id',
  `user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
  `user_password` varchar(50) DEFAULT NULL COMMENT '密码',
  `user_email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `user_info` text COMMENT '简介',
  `head_img` blob COMMENT '头像',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

INSERT INTO `sys_user` VALUES ('1', 'admin', '123456', 'admin@mybatis.tk', '管理员', null, '2018-11-30 17:00:00');
INSERT INTO `sys_user` VALUES ('2', 'test', '123456', 'test@mybatis.org', 'test info', 0x7B, '2018-12-05 13:45:45');

--  -----

CREATE TABLE `sys_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '角色Id',
  `role_name` varchar(50) DEFAULT NULL COMMENT '用户名',
  `enabled` int(11) DEFAULT NULL COMMENT '有效标识',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色表';

INSERT INTO `sys_role` VALUES ('1', '管理员', '1', '1', '2018-11-30 17:05:00');
INSERT INTO `sys_role` VALUES ('2', '普通用户', '1', '1', '2018-11-30 17:05:01');

-- ------

CREATE TABLE `sys_user_role` (
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户Id',
  `role_id` bigint(20) DEFAULT NULL COMMENT '角色Id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户角色关联表';

INSERT INTO `sys_user_role` VALUES ('1', '1');
INSERT INTO `sys_user_role` VALUES ('1', '2');
INSERT INTO `sys_user_role` VALUES ('1001', '2');

②创建mybatis配置文件mybatis-config.xml，位置src/main/resources下

<?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>
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <typeAliases>
        <package name="org.mybatis.example.model"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC">
                <property name="" value=""/>
            </transactionManager>
            <dataSource type="UNPOOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
<!--         <package name="org.mybatis.example.mapper"/> -->
        <mapper resource="org/mybatis/example/mapper/UserMapper.xml"/>
        <mapper resource="org/mybatis/example/mapper/RoleMapper.xml"/>
    </mappers>
  </configuration>

③创建model，SysUser，SysRole和SysUserRole，位置src/main/java下，包org.mybatis.example.model

package org.mybatis.example.model;

import java.util.Date;

public class SysUser {
    
    private Long id;
    private String userName;
    private String userPassword;
    private String userEmail;
    private String userInfo;
    private byte[] headImg;
    private Date createTime;
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public String getUserPassword() {
        return userPassword;
    }
    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }
    public String getUserEmail() {
        return userEmail;
    }
    public void setUserEmail(String userEmail) {
        this.userEmail = userEmail;
    }
    public String getUserInfo() {
        return userInfo;
    }
    public void setUserInfo(String userInfo) {
        this.userInfo = userInfo;
    }
    public byte[] getHeadImg() {
        return headImg;
    }
    public void setHeadImg(byte[] headImg) {
        this.headImg = headImg;
    }
    public Date getCreateTime() {
        return createTime;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
    
    

}

package org.mybatis.example.model;

import java.util.Date;

public class SysRole {

    private Long id;
    private String roleName;
    private int enabled;
    private Long createBy;
    private Date createTime;
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getRoleName() {
        return roleName;
    }
    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }
    public int getEnabled() {
        return enabled;
    }
    public void setEnabled(int enabled) {
        this.enabled = enabled;
    }
    public Long getCreateBy() {
        return createBy;
    }
    public void setCreateBy(Long createBy) {
        this.createBy = createBy;
    }
    public Date getCreateTime() {
        return createTime;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
    
    
    
}

package org.mybatis.example.model;

public class SysUserRole {

    private Long userId;
    private Long roleId;
    public Long getUserId() {
        return userId;
    }
    public void setUserId(Long userId) {
        this.userId = userId;
    }
    public Long getRoleId() {
        return roleId;
    }
    public void setRoleId(Long roleId) {
        this.roleId = roleId;
    }
    
    
}

④创建映射的xml文件，UserMapper.xml，位置src/main/resources/org/mybatis/example/mapper下

<?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="org.mybatis.example.mapper.UserMapper">
        
        <select id="selectUserById" resultMap="selectUserMap">
            select * from sys_user where id = #{id}
        </select>
        
        <resultMap type="org.mybatis.example.model.SysUser" id="selectUserMap">
            <id property="id" column="id" />
            <result property="userName" column="user_name"/>
            <result property="userPassword" column="user_password"/>
            <result property="userEmail" column="user_email"/>
            <result property="userInfo" column="user_info"/>
            <result property="headImg" column="head_img" jdbcType="BLOB"/>
            <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        </resultMap>
        
    </mapper>

⑤创建Mapper的接口，UserMapper.java，位置src/main/java下，包org.mybatis.example.mapper

package org.mybatis.example.mapper;

import org.mybatis.example.model.SysUser;

public interface UserMapper {

    SysUser selectUserById(Long id);

}

⑥创建测试程序，TestSelectMapper.java，位置src/main/java下，包org.mybatis.example.app

package org.mybatis.example.app;

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

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.BeforeClass;
import org.junit.Test;
import org.mybatis.example.mapper.RoleMapper;
import org.mybatis.example.mapper.UserMapper;
import org.mybatis.example.model.SysRole;
import org.mybatis.example.model.SysUser;

import junit.framework.Assert;

public class TestSelectMapper {

    private static SqlSessionFactory sqlSessionFactory;
    
    @BeforeClass
    public static void init() {
        try {
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    @Test
    public void testSelectUserById() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser sysUser = userMapper.selectUserById(1L);
            Assert.assertNotNull(sysUser);
            Assert.assertEquals("admin", sysUser.getUserName());
            System.out.println(sysUser.toString());
        } finally {
            sqlSession.close();
        }
    }
}

三、综合增删改查

<?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="org.mybatis.example.mapper.UserMapper">
        
        <select id="selectUserById" resultMap="selectUserMap">
            select * from sys_user where id = #{id}
        </select>
        
        <resultMap type="org.mybatis.example.model.SysUser" id="selectUserMap">
            <id property="id" column="id" />
            <result property="userName" column="user_name"/>
            <result property="userPassword" column="user_password"/>
            <result property="userEmail" column="user_email"/>
            <result property="userInfo" column="user_info"/>
            <result property="headImg" column="head_img" jdbcType="BLOB"/>
            <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        </resultMap>
        
        <select id="selectAllUsers" resultType="org.mybatis.example.model.SysUser">
            select user_name userName, user_password userPassword,
                user_email userEmail, user_info userInfo,
                user_info userInfo, head_img headImg,
                create_time createTime from sys_user
        </select>
        
        <insert id="insertAUser">
            insert into sys_user (id, user_name, user_password, user_email, user_info, head_img, create_time) values 
            (#{id}, #{userName}, #{userPassword}, #{userEmail}, #{userInfo}, #{headImg, jdbcType=BLOB}, 
            #{createTime, jdbcType=TIMESTAMP})
        </insert>
        
        <insert id="insertAUserWithoutId" useGeneratedKeys="true" keyProperty="id">
            insert into sys_user (user_name, user_password, user_email, user_info, head_img, create_time) values 
            (#{userName}, #{userPassword}, #{userEmail}, #{userInfo}, #{headImg, jdbcType=BLOB}, #{createTime, jdbcType=TIMESTAMP})
        </insert>
        
        <update id="updateAUser">
            update sys_user set 
                user_name = #{userName},
                user_email = #{userEmail},
                user_info = #{userInfo},
                create_time = #{createTime, jdbcType=TIMESTAMP}
            where id = #{id}
        </update>
        
        <delete id="deleteAUser">
            delete from sys_user where id = #{id}
        </delete>
        
    </mapper>

package org.mybatis.example.mapper;

import java.util.List;

import org.mybatis.example.model.SysUser;

public interface UserMapper {

    SysUser selectUserById(Long id);
    List<SysUser> selectAllUsers();
    
    int insertAUser(SysUser sysUser);
    int insertAUserWithoutId(SysUser sysUser);
    
    int updateAUser(SysUser sysUser);
    int deleteAUser(Long id);
}

<?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="org.mybatis.example.mapper.RoleMapper">
    <select id="selectRolesByUserId" resultType="org.mybatis.example.model.SysRole">
        select r.id, 
               r.role_name roleName, 
               r.enabled, 
               r.create_by createBy, 
               r.create_time createTime
        from sys_user u
        inner join sys_user_role ur on u.id = ur.user_id
        inner join sys_role r on r.id = ur.role_id
        where u.id = #{userId}
    </select>
    
    <select id="selectRolesByUserIdAndRoleEnabled" resultType="org.mybatis.example.model.SysRole">
        select r.id,
               r.role_name roleName,
               r.enabled,
               r.create_by createBy,
               r.create_time createTime
        from sys_user u
        inner join sys_user_role ur on u.id = ur.user_id
        inner join sys_role r on r.id = ur.role_id
        where u.id = #{userId} and r.enabled = #{enabled}
    </select>
  </mapper>

package org.mybatis.example.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.mybatis.example.model.SysRole;

public interface RoleMapper {

    List<SysRole> selectRolesByUserId(Long id);
    List<SysRole> selectRolesByUserIdAndRoleEnabled(
            @Param("userId") Long userId, 
            @Param("enabled") int enabled);
}

package org.mybatis.example.app;

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

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.BeforeClass;
import org.junit.Test;
import org.mybatis.example.mapper.RoleMapper;
import org.mybatis.example.mapper.UserMapper;
import org.mybatis.example.model.SysRole;
import org.mybatis.example.model.SysUser;

import junit.framework.Assert;

public class TestSelectMapper {

    private static SqlSessionFactory sqlSessionFactory;
    
    @BeforeClass
    public static void init() {
        try {
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    @Test
    public void testSelectUserById() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser sysUser = userMapper.selectUserById(1L);
            Assert.assertNotNull(sysUser);
            Assert.assertEquals("admin", sysUser.getUserName());
            System.out.println(sysUser.toString());
        } finally {
            sqlSession.close();
        }
    }
    
    @Test
    public void testSelectAllUsers() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<SysUser> sysUserList = userMapper.selectAllUsers();
            Assert.assertNotNull(sysUserList);
            Assert.assertTrue(sysUserList.size() > 0);
            System.out.println(sysUserList.size());
        } finally {
            sqlSession.close();
        }
    }
    
    @Test
    public void testSelectRolesByUserId() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            RoleMapper rolerMapper = sqlSession.getMapper(RoleMapper.class);
            List<SysRole> sysRoleList = rolerMapper.selectRolesByUserId(1L);
            Assert.assertNotNull(sysRoleList);
            Assert.assertTrue(sysRoleList.size() > 0);
            System.out.println(sysRoleList.size());
        } finally {
            sqlSession.close();
        }
    }
    
}

package org.mybatis.example.app;

import java.io.IOException;
import java.io.Reader;
import java.util.Date;

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.BeforeClass;
import org.junit.Test;
import org.mybatis.example.mapper.UserMapper;
import org.mybatis.example.model.SysUser;

public class TestInsertMapper {

    private static SqlSessionFactory sqlSessionFactory;
    
    @BeforeClass
    public static void init() {
        try {
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    @Test
    public void testInsertAUser() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser sysUser = new SysUser();
            sysUser.setId(2L);
            sysUser.setUserName("test");
            sysUser.setUserPassword("123456");
            sysUser.setUserEmail("test@mybatis.org");
            sysUser.setUserInfo("test info");
            sysUser.setHeadImg(new byte[] {123});
            sysUser.setCreateTime(new Date());
            int insertResult = userMapper.insertAUser(sysUser);
            System.out.println(insertResult);
        } finally {
            //sqlSession.rollback();
            sqlSession.commit();
            sqlSession.close();
        }
    }
    
    @Test
    public void testInsertAUserWithoutId() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser sysUser = new SysUser();
            sysUser.setUserName("test3");
            sysUser.setUserPassword("123456");
            sysUser.setUserEmail("test3@mybatis.org");
            sysUser.setUserInfo("test3 info");
            sysUser.setCreateTime(new Date());
            int insertResult = userMapper.insertAUser(sysUser);
            System.out.println(insertResult);
        } finally {
            //sqlSession.rollback();
            sqlSession.commit();
            sqlSession.close();
        }
    }
    
    @Test
    public void testUpdateAUser() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            SysUser selectUser = userMapper.selectUserById(1003L);
            selectUser.setUserName("test1003");
            selectUser.setUserEmail("test1003@mybatis.org");
            selectUser.setUserInfo("test1003 info");
            selectUser.setCreateTime(new Date());
            int updateResult = userMapper.updateAUser(selectUser);
            System.out.println(updateResult);
        } finally {
            sqlSession.commit();
            sqlSession.close();
        }
    }
    
    @Test
    public void testDeleteAUser() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            int deleteResult = userMapper.deleteAUser(1003L);
            System.out.println(deleteResult);
        } finally {
            sqlSession.commit();
            sqlSession.close();
        }
    }
}

package org.mybatis.example.app;

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

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.BeforeClass;
import org.junit.Test;
import org.mybatis.example.mapper.RoleMapper;
import org.mybatis.example.model.SysRole;

public class TestParamMapper {

    private static SqlSessionFactory sqlSessionFactory;
    
    @BeforeClass
    public static void init() {
        try {
            Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    @Test
    public void testComplicatedSelect() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
            List<SysRole> sysRoleList = roleMapper.selectRolesByUserIdAndRoleEnabled(1L, 1);
            System.out.println(sysRoleList.size());
        } finally {
            sqlSession.close();
        }
    }
}

当需要传递多个参数的时候，需要用到@Param注解，并且上面的代码中给出了例子：RoleMapper.xml中的SelectRolesByUserIdAndRoleEnabled

需要注意的是，在Mapper.xml中，<mapper>中的属性namespace不能少，之前在学习的过程由于缺少了namespace，导致没有得到正确的结果，排查了半天，才找到原因

 

四、架构图