【Mybatis框架】Mybatis配置与增删查改操作

导读:本篇文章讲解 【Mybatis框架】Mybatis配置与增删查改操作,文章出自:https://blog.csdn.net/weixin_43316702/article/details/105315804希望对大家有帮助,欢迎收藏,转发!站点地址:www.javazhiyin.com.com

>>强大,10k+点赞的 SpringBoot 后台管理系统竟然出了详细教程!

Mybatis:
MyBatis 是一种持久层框架,它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java pojo为数据库中的记录。

Mybatis配置:
首先需要导入mybatis有关的jar包
下载地址:https://github.com/mybatis/mybatis-3/releases
创建SqlMapConfig.xml文件:(全局配置文件
【注意:文件放在config文件夹下(已设置为 Resources Root文件)】

<?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>
    <!-- 是用resource属性加载外部配置文件 -->
    <properties resource="db.properties">
        <!-- 在properties内部用property定义属性 -->
        <!-- 如果外部配置文件有该属性,则内部定义属性被外部属性覆盖 -->
        <property name="jdbc.username" value="root123" />
        <property name="jdbc.password" value="root123" />
    </properties>
    <!-- 配置mybatis的环境信息,与spring整合,该信息由spring来管理-->
    <!-- 和spring整合后 environments配置将废除 -->
    <environments default="development">
        <environment id="development">
            <!-- 配置JDBC事务控制,由mybatis进行管理-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 配置数据源,采用mybatis连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"></property>
                <property name="url" value="${jdbc.url}"></property>
                <property name="username" value="${jdbc.username}"></property>
                <property name="password" value="${jdbc.password}"></property>
            </dataSource>
        </environment>
    </environments>
    <!-- 加载映射文件 -->
    <mappers>
    <mapper resource="sqlmap/User.xml"/>
    <mapper resource="mapper/UserMapper.xml" />
    <mapper resource="mapper/OrderMapper.xml" />
    </mappers>
</configuration>

log4j.properties:

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

映射文件:(在全局配置文件中要加载所需的映射文件)
User.xml:【这是对应UserDao的】

<?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代理时,它具有特殊及重要的作用-->
<mapper namespace="com.mybatis.dao.UserDao">
    <!-- 根据用户ID查询用户信息-->
    <!-- select:表示一个MappingStatement对象-->
    <!-- id:statement的唯一标识-->
    <!-- #{}:表示一个占位符?-->
    <!-- #{id}:里面的id表示输入参数的参数名称,如果该参数是简单类型,那么#{}里面的参数名称可以任意-->
    <!-- parameterType:输入参数的java类型-->
    <!-- resultType:输出结果的所映射的java类型(单条结果所对应的的java类型)-->
    <select id="findUserById" parameterType="int" resultType="com.mybatis.po.User">
        SELECT * FROM USER WHERE id =#{id}
    </select>
    <!--查询全部-->
    <select id="findUserAll" resultType="com.mybatis.po.User">
        SELECT * FROM USER
    </select>
    <!--通过username查找用户-->
    <select id="findUserByUsername" parameterType="String" resultType="com.mybatis.po.User">
        select * from user where username like '%${value}%'
    </select>
    <!-- 新增用户 -->
    <insert id="insertUser" parameterType="com.mybatis.po.User">
        <!-- selectKey 标签实现主键返回 -->
        <!-- keyColumn:主键对应的表中的哪一列 -->
        <!-- keyProperty:主键对应的pojo中的哪一个属性 -->
        <!-- order:设置在执行insert语句前执行查询id的sql,孩纸在执行insert语句之后执行查询id的sql -->
        <!-- resultType:设置返回的id的类型 -->
        <selectKey keyColumn="id" keyProperty="id" order="AFTER" resultType="int">
            SELECT LAST_INSERT_ID()
        </selectKey>
		INSERT INTO `user`
		(username,birthday,sex,address) VALUES (#{username},#{birthday},#{sex},#{address})
	</insert>
    <!-- 更新用户 -->
    <update id="updateUserById" parameterType="com.mybatis.po.User">
	UPDATE `user` SET
	username = #{username} WHERE id = #{id}
    </update>
    <!-- 删除用户 -->
    <delete id="deleteUserById" parameterType="int">
		delete from user where id=#{id}
	</delete>
</mapper>

UserMapper.xml:【这是对应UserMapper的】

<?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:命名空间,用于隔离sql -->
<!-- 还有一个很重要的作用,使用动态代理开发DAO,1. namespace必须和Mapper接口类路径一致 -->
<mapper namespace="com.mybatis.mapper.UserMapper">
<!-- 根据用户ID查询用户信息-->
    <!-- select:表示一个MappingStatement对象-->
    <!-- id:statement的唯一标识-->
    <!-- #{}:表示一个占位符?-->
    <!-- #{id}:里面的id表示输入参数的参数名称,如果该参数是简单类型,那么#{}里面的参数名称可以任意-->
    <!-- parameterType:输入参数的java类型-->
    <!-- resultType:输出结果的所映射的java类型(单条结果所对应的的java类型)-->
    <select id="findUserById" parameterType="int" resultType="com.mybatis.po.User">
        SELECT * FROM USER WHERE id =#{id}
    </select>
    <!--查询全部-->
    <select id="findUserAll" resultType="com.mybatis.po.User">
        SELECT * FROM USER
    </select>
    <!--通过username查找用户-->
    <select id="findUserByUsername" parameterType="String" resultType="com.mybatis.po.User">
        select * from user where username like '%${value}%'
    </select>
    <!-- 新增用户 -->
    <insert id="insertUser" parameterType="com.mybatis.po.User">
        <!-- selectKey 标签实现主键返回 -->
        <!-- keyColumn:主键对应的表中的哪一列 -->
        <!-- keyProperty:主键对应的pojo中的哪一个属性 -->
        <!-- order:设置在执行insert语句前执行查询id的sql,孩纸在执行insert语句之后执行查询id的sql -->
        <!-- resultType:设置返回的id的类型 -->
        <selectKey keyColumn="id" keyProperty="id" order="AFTER" resultType="int">
            SELECT LAST_INSERT_ID()
        </selectKey>
		INSERT INTO `user`
		(username,birthday,sex,address) VALUES (#{username},#{birthday},#{sex},#{address})
	</insert>
    <!-- 更新用户 -->
    <update id="updateUserById" parameterType="com.mybatis.po.User">
	UPDATE `user` SET
	username = #{username} WHERE id = #{id}
    </update>
    <!-- 删除用户 -->
    <delete id="deleteUserById" parameterType="int">
		delete from user where id=#{id}
	</delete>
    <!-- 根据条件查询用户 -->
    <select id="queryUserByWhere" parameterType="com.mybatis.po.User" resultType="com.mybatis.po.User">
        SELECT id, username, birthday, sex, address FROM `user`
        <!-- where标签可以自动添加where,同时处理sql语句中第一个and关键字 -->
        <where>
            <if test="sex != null">
                AND sex = #{sex}
            </if>
            <if test="username != null and username != ''">
                AND username LIKE
                '%${username}%'
            </if>
        </where>
    </select>
</mapper>

User类:

package com.mybatis.po;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

public class User implements Serializable {
    private int id;
    private String username;
    private String sex;
    private Date birthday;
    private String address;

    public User(){}

    public User(String username,Date birthday, String sex,  String address) {
        this.username = username;
        this.sex = sex;
        this.birthday = birthday;
        this.address = address;
    }

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                ", address='" + address + 
                '}';
    }
}

第一种实现方式:
接口加实现类:

接口:
package com.mybatis.dao;

import com.mybatis.po.User;
import java.util.List;

public interface UserDao {

    //通过id查找用户信息
    User findUserById(Integer id);

    //查找所有用户信息
    List<User> findUserAll();

    //通过username查找用户信息
    List<User> findUserByUsername(String username);

    //新增用户
    void insertUser(User user);

    //通过id更新username
    void updateUserById(User user);

    //通过id删除指定用户
    void deleteUserById(Integer id);
}


实现类:
package com.mybatis.dao.impl;

import com.mybatis.dao.UserDao;
import com.mybatis.po.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import java.util.List;

public class UserDaoImpl implements UserDao {
    //创建SqlSessionFactory
    public SqlSessionFactory sqlSessionFactory;

    public UserDaoImpl(SqlSessionFactory sqlSessionFactory) {
        super();
        this.sqlSessionFactory = sqlSessionFactory;
    }

    @Override
    public User findUserById(Integer id) {
        // 创建SqlSession
        SqlSession sqlSession = this.sqlSessionFactory.openSession();
        // 执行查询逻辑
        User user = sqlSession.selectOne("findUserById", id);
        // 释放资源
        sqlSession.close();

        return user;
    }

    @Override
    public List<User> findUserAll() {
        // 创建SqlSession
        SqlSession sqlSession = this.sqlSessionFactory.openSession();
        // 执行查询逻辑
        List<User> user = sqlSession.selectList("findUserAll");
        // 释放资源
        sqlSession.close();
        return user;
    }

    @Override
    public List<User> findUserByUsername(String username) {
        // 创建SqlSession
        SqlSession sqlSession = this.sqlSessionFactory.openSession();
        // 执行查询逻辑
        List<User> user = sqlSession.selectList("findUserByUsername",username);
        // 释放资源
        sqlSession.close();
        return user;
    }

    @Override
    public void insertUser(User user) {
        // 创建SqlSession
        SqlSession sqlSession = this.sqlSessionFactory.openSession();
        // 执行保存逻辑
        sqlSession.insert("insertUser", user);
        // 提交事务
        sqlSession.commit();
        // 释放资源
        sqlSession.close();
    }

    @Override
    public void updateUserById(User user) {
        // 创建SqlSession
        SqlSession sqlSession = this.sqlSessionFactory.openSession();
        // 执行保存逻辑
        sqlSession.insert("updateUserById", user);
        // 提交事务
        sqlSession.commit();
        // 释放资源
        sqlSession.close();
    }

    @Override
    public void deleteUserById(Integer id) {
        // 创建SqlSession
        SqlSession sqlSession = this.sqlSessionFactory.openSession();
        // 执行保存逻辑
        sqlSession.insert("deleteUserById", id);
        // 提交事务
        sqlSession.commit();
        // 释放资源
        sqlSession.close();
    }
}

测试类:

package com.mybatis.test;

import com.mybatis.dao.UserDao;
import com.mybatis.dao.impl.UserDaoImpl;
import com.mybatis.po.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import java.io.InputStream;
import java.util.Date;
import java.util.List;

public class UserTest2 {
    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws Exception {
        // 创建SqlSessionFactoryBuilder
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        // 加载SqlMapConfig.xml配置文件
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 创建SqlsessionFactory
        this.sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
    }

    @Test
    public void testFindUserById() {
        // 创建DAO
        UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
        // 执行查询
        User user = userDao.findUserById(1);
        System.out.println(user);
    }

    @Test
    public void testFindUserAll() {
        // 创建DAO
        UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
        // 执行查询
        List<User> list = userDao.findUserAll();
        for (User user:list) {
            System.out.println(user);
        }
    }

    @Test
    public void testFindUserByUsername() {
        // 创建DAO
        UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
        // 执行查询
        List<User> list = userDao.findUserByUsername("王");
        for (User user:list) {
            System.out.println(user);
        }
    }

    @Test
    public void testInsertUser() {
        // 创建DAO
        UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
        //封装数据
        User user = new User();
        user.setUsername("陈小姐");
        user.setSex("女");
        user.setBirthday(new Date());
        user.setAddress("马来山市");
        // 执行操作
        userDao.insertUser(user);
    }

    @Test
    public void testUpdateUsernameById() {
        // 创建DAO
        UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
        //封装数据
        User user = new User();
        user.setId(12);
        user.setUsername("陈美");
        // 执行操作
        userDao.updateUserById(user);
    }

    @Test
    public void testDeleteUserById() {
        // 创建DAO
        UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
        // 执行操作
        userDao.deleteUserById(12);
    }
}

第二种mapper代理:
创建与映射文件同名的接口,对应相对应操作的方法。

package com.mybatis.mapper;
import com.mybatis.po.OrderUser;
import com.mybatis.po.User;
import java.util.List;

public interface UserMapper {
    //通过id查找用户信息
    User findUserById(Integer id);

    //查找所有用户信息
    List<User> findUserAll();

    //通过username查找用户信息
    List<User> findUserByUsername(String username);

    //新增用户
    void insertUser(User user);

    //通过id更新username
    void updateUserById(User user);

    //通过id删除指定用户
    void deleteUserById(Integer id);

    //根据条件查找用户
    List<User> queryUserByWhere(User user);
}

测试类:

package com.mybatis.test;

import com.mybatis.po.OrderUser;
import com.mybatis.po.User;
import com.mybatis.mapper.UserMapper;
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.Before;
import org.junit.Test;

import java.io.InputStream;
import java.util.Date;
import java.util.List;

public class UserMapperTest {
    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws Exception {
        // 创建SqlSessionFactoryBuilder
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        // 加载SqlMapConfig.xml配置文件
        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        // 创建SqlsessionFactory
        this.sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
    }

    @Test
    public void testFindUserById() {
        // 获取sqlSession,和spring整合后由spring管理
        SqlSession sqlSession = this.sqlSessionFactory.openSession();

        // 从sqlSession中获取Mapper接口的代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        // 执行查询方法
        User user = userMapper.findUserById(1);
        System.out.println(user);

        // 和spring整合后由spring管理
        sqlSession.close();
    }

    @Test
    public void testFindUserAll() {
        // 获取sqlSession,和spring整合后由spring管理
        SqlSession sqlSession = this.sqlSessionFactory.openSession();

        // 从sqlSession中获取Mapper接口的代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        // 执行查询方法
        List<User> list = userMapper.findUserAll();
        for (User user:list) {
            System.out.println(user);
        }

        // 和spring整合后由spring管理
        sqlSession.close();
    }

    @Test
    public void testFindUserByUsername() {
        // 获取sqlSession,和spring整合后由spring管理
        SqlSession sqlSession = this.sqlSessionFactory.openSession();
        // 从sqlSession中获取Mapper接口的代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        // 执行查询方法
        List<User> list = userMapper.findUserByUsername("王");
        for (User user:list) {
            System.out.println(user);
        }
        // 和spring整合后由spring管理
        sqlSession.close();
}

    @Test
    public void testInsertUser() {
        // 获取sqlSession,和spring整合后由spring管理
        SqlSession sqlSession = this.sqlSessionFactory.openSession();
        // 从sqlSession中获取Mapper接口的代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        //封装数据
        User user = new User();
        user.setUsername("陈珂");
        user.setSex("女");
        user.setBirthday(new Date());
        user.setAddress("马来山市");
        // 执行操作
        userMapper.insertUser(user);

        // 和spring整合后由spring管理
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testUpdateUsernameById() {
        // 获取sqlSession,和spring整合后由spring管理
        SqlSession sqlSession = this.sqlSessionFactory.openSession();
        // 从sqlSession中获取Mapper接口的代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        //封装数据
        User user = new User();
        user.setId(14);
        user.setUsername("陈琳");
        // 执行操作
        userMapper.updateUserById(user);

        // 和spring整合后由spring管理
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testDeleteUserById() {
        // 获取sqlSession,和spring整合后由spring管理
        SqlSession sqlSession = this.sqlSessionFactory.openSession();
        // 从sqlSession中获取Mapper接口的代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        // 执行操作
        userMapper.deleteUserById(13);

        // 和spring整合后由spring管理
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void testQueryUserByWhere() {
        // 获取sqlSession,和spring整合后由spring管理
        SqlSession sqlSession = this.sqlSessionFactory.openSession();

        // 从sqlSession中获取Mapper接口的代理对象
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        // 执行查询方法
        User u = new User();
        u.setSex("女");
        List<User> list = userMapper.queryUserByWhere(u);
        for (User user:list){
            System.out.println(user);
        }

        // 和spring整合后由spring管理
        sqlSession.close();
    }
}

下一节 mybatis多表对应如何实现(一对一,一对多,多对多)