mybatis入门篇（二）：Mapper接口/关联查询/新增数据

落日映苍穹つ 2022-10-02 00:52 30阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

1、数据准备

![49730676db033c3f6c41d33ffa37003fbd1.jpg][]

2、编写实体类

import java.util.Date;
    public class User {
        private Long id;
        private String userName;
        private String userPassword;
        private String userPhone;
        private String userEmail;
        private byte[] headImg;
        private Date createTime;
        private Date updateTime;
    
        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 getUserPhone() {
            return userPhone;
        }
        public void setUserPhone(String userPhone) {
            this.userPhone = userPhone;
        }
        public String getUserEmail() {
            return userEmail;
        }
        public void setUserEmail(String userEmail) {
            this.userEmail = userEmail;
        }
        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;
        }
        public Date getUpdateTime() {
            return updateTime;
        }
        public void setUpdateTime(Date updateTime) {
            this.updateTime = updateTime;
        }
    }

3、编写相对应的Mapper接口

import com.forest.owl.entity.User;
    import java.util.List;
    
    public interface UserMapper {
        User selectUserById(Long id);
        List<User> selectAllUser();
    }

4、编写UserMapper.xml

在resources中加入文件夹com/forest/owl/mapper（测试发现这个路径要和java代码中mapper接口的路径一致），在此文件夹中添加UserMapper文件

5、修改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>
        <settings>
            <setting name="mapUnderscoreToCamelCase" value="true"/>
        </settings>
        <typeAliases>
            <package name="com.forest.owl.entity"/>
        </typeAliases>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC">
                    <property name="" value=""/>
                </transactionManager>
                <dataSource type="UNPOOLED">
                    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/forest?useSSL=false"/>
                    <property name="username" value="root"/>
                    <property name="password" value="root"/>
                </dataSource>
            </environment>
        </environments>
        <mappers>
            <package name="com.forest.owl.mapper"/>
        </mappers>
    </configuration>

6、编写测试代码

import com.forest.owl.entity.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.BeforeClass;
    import java.io.IOException;
    import java.io.Reader;
    
    public class BaseMapperTest {
        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();
            }
        }
        public SqlSession getSqlSession(){
            SqlSession sqlSession = sqlSessionFactory.openSession();
            return sqlSession;
        }
    }

import com.forest.owl.entity.User;
    import com.forest.owl.mapper.UserMapper;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    import java.util.List;
    public class MapperTest extends BaseMapperTest{
        @Test
        public void UserMapperTest(){
            SqlSession sqlSession = getSqlSession();
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = userMapper.selectUserById((long) 3);
            System.out.println(user.getUserName());
            List<User> userList = userMapper.selectAllUser();
            userList.stream().forEach(i -> System.out.println(i.getUserName()));
        }
    }

7、关联查询

假设在用户信息查询的基础上，我还想查询用户的角色与权限，该如何？下面根据书中的教程操作下。 首先定义角色与权限的实体类

import java.util.Date;
    public class Role {
        private Long id;
        private String roleName;
        private int enabled;
        private Long createBy;
        private Date createTime;
        private Long updateBy;
        private Date updateTime;
    
        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;
        }
        public Long getUpdateBy() {
            return updateBy;
        }
        public void setUpdateBy(Long updateBy) {
            this.updateBy = updateBy;
        }
        public Date getUpdateTime() {
            return updateTime;
        }
        public void setUpdateTime(Date updateTime) {
            this.updateTime = updateTime;
        }
    }

import java.util.Date;
    public class Privilege {
        private Long id;
        private String privilegeName;
        private String privilegeUrl;
        private Long createBy;
        private Date createTime;
        private Long updateBy;
        private Date updateTime;
    
        public Long getId() {
            return id;
        }
        public void setId(Long id) {
            this.id = id;
        }
        public String getPrivilegeName() {
            return privilegeName;
        }
        public void setPrivilegeName(String privilegeName) {
            this.privilegeName = privilegeName;
        }
        public String getPrivilegeUrl() {
            return privilegeUrl;
        }
        public void setPrivilegeUrl(String privilegeUrl) {
            this.privilegeUrl = privilegeUrl;
        }
        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;
        }
        public Long getUpdateBy() {
            return updateBy;
        }
        public void setUpdateBy(Long updateBy) {
            this.updateBy = updateBy;
        }
        public Date getUpdateTime() {
            return updateTime;
        }
        public void setUpdateTime(Date updateTime) {
            this.updateTime = updateTime;
        }
    }

在User实体类中添加Role与Privilege属性

private Role role;
    private Privilege privilege;
    public Role getRole() {
        return role;
    }
    public void setRole(Role role) {
        this.role = role;
    }
    public Privilege getPrivilege() {
        return privilege;
    }
    public void setPrivilege(Privilege privilege) {
        this.privilege = privilege;
    }

修改UserMapper.xml文件

<!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.forest.owl.mapper.UserMapper">
        <resultMap id="userMap" type="com.forest.owl.entity.User">
            <id property="id" column="id" />
            <result property="userName" column="user_name" />
            <result property="userPassword" column="user_password" />
            <result property="userPhone" column="user_phone" />
            <result property="userEmail" column="user_email" />
            <result property="headImg" column="head_img" jdbcType="BLOB" />
            <result property="createTime" column="create_time" jdbcType="TIMESTAMP" />
            <result property="updateTime" column="update_time" jdbcType="TIMESTAMP" />
            <association property="role">
                <result property="roleName" column="role_name" />
            </association>
            <association property="privilege">
                <result property="privilegeName" column="privilege_name" />
                <result property="privilegeUrl" column="privilege_url" />
            </association>
        </resultMap>
        <select id="selectUserById" resultMap="userMap">
            SELECT u.*,
            r.role_name,
            p.privilege_name,
            p.privilege_url
            FROM user u
            INNER JOIN user_role ur on ur.user_id=u.id
            INNER JOIN role r on r.id=ur.role_id
            INNER JOIN role_privilege rp on rp.role_id=r.id
            INNER JOIN privilege p on p.id=rp.privilege_id
            WHERE u.id=#{id}
        </select>
        <select id="selectAllUser" resultType="com.forest.owl.entity.User">
            SELECT u.id,
            u.user_name,
            u.user_password,
            u.user_phone,
            u.user_email,
            u.head_img,
            u.create_time,
            u.update_time,
            r.role_name as "role.roleName",
            p.privilege_name as "privilege.privilegeName",
            p.privilege_url as "privilege.privilegeUrl"
            FROM user u
            INNER JOIN user_role ur on ur.user_id=u.id
            INNER JOIN role r on r.id=ur.role_id
            INNER JOIN role_privilege rp on rp.role_id=r.id
            INNER JOIN privilege p on p.id=rp.privilege_id
        </select>
    </mapper>

编写测试类

@Test
    public void UserMapperTest(){
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.selectUserById((long) 3);
        System.out.println(user.getUserName());
        System.out.println(user.getRole().getRoleName());
        System.out.println(user.getPrivilege().getPrivilegeName());
        System.out.println(user.getPrivilege().getPrivilegeUrl());
        List<User> userList = userMapper.selectAllUser();
        userList.stream().forEach(i -> {
            System.out.println(i.getUserName());
            System.out.println(i.getRole().getRoleName());
            System.out.println(i.getPrivilege().getPrivilegeName());
            System.out.println(i.getPrivilege().getPrivilegeUrl());
        });
    }

8、新增数据 在UserMapper接口中新增方法

int insertUser(User user);

UserMapper.xml

<insert id="insertUser">
        INSERT INTO user (user_name, user_password, user_phone, user_email, head_img, create_time, update_time)
        VALUES( #{userName},  #{userPassword}, #{userPhone}, #{userEmail}, #{headImg}, #{createTime}, #{updateTime} )
    </insert>

测试代码

@Test
    public void UserMapperTest(){
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUserName("新用户名");
        user.setUserPassword("密码");
        user.setUserPhone("手机号");
        user.setUserEmail("邮箱号");
        int result = userMapper.insertUser(user);
        System.out.println(result);
        sqlSession.commit();
    }

因为SqlSessionFactory.openSession()是不自动提交的，故此处需要手动提交，否则无效。 对于想要得到插入数据的主键，可以设置mybatis-config.xml的配置项useGeneratedKeys为true

然后在<insert />配置中添加keyProperty="id"，在数据插入成功后，mybatis会将主键回写至id里，届时使用user.getId()获取即可。

<insert id="insertUser" keyProperty="id">
        INSERT INTO user (user_name, user_password, user_phone, user_email, head_img, create_time, update_time)
        VALUES( #{userName},  #{userPassword}, #{userPhone}, #{userEmail}, #{headImg}, #{createTime}, #{updateTime} )
    </insert>

转载于:https://my.oschina.net/u/4108765/blog/3059540

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220113/1df1a0e0b9274a30b5036f98e92900a4.png
[49730676db033c3f6c41d33ffa37003fbd1.jpg]: /images/20220113/4154af9d221141e397fece218a5583ab.png