MyBatis配置详解和增删改查

最新推荐文章于 2022-06-26 13:11:36 发布

忘记自我介绍

最新推荐文章于 2022-06-26 13:11:36 发布

阅读量206

点赞数 1

文章标签： mybatis 增删改查

本文链接：https://blog.youkuaiyun.com/vlucky_long/article/details/110352844

版权

本文详细介绍了MyBatis的配置，包括mybatis-config.xml的解析，数据源配置，以及增删改查操作的实现。讲解了如何根据用户ID和用户名进行查询，使用${}和#的区别，Mapper接口的使用规范，全局Properties配置，数据类型匹配和别名设定，以及手动映射等关键知识点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、mybatis-config.xml配置详解

environments 标签配置数据源环境
●default 属性设置采用的数据源

environment 标签配置具体的数据源环境，可以配置多个
●transactionManager 标签配置事务管理器

dataSource 标签数据源
●type 属性用来指定采用的连接池

<?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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>

        <!--生产环境下的数据源-->
        <environment id="product">
        <!--事务管理器
                type="JDBC"表示当前MyBatis采用JDBC的事务，
                Connection 接口方法 setAutoCommit(false) commit rollback
                type="MANAGERED" 表示mybaits不管理事务，交给其他的框架管理
        -->
        <transactionManager type="JDBC"></transactionManager>
        <!--
            POOLED使用数据库连接池
            UNPOOLED不使用连接池
         -->
    <dataSource type="POOLED"></dataSource>
    </environment>

    <!--测试环境下的数据源-->
    <environment id="test">
        <transactionManager type=""></transactionManager>
        <dataSource type=""></dataSource>
    </environment>

        </environments>

<mappers>
<!--resource属性加载SQL映射文件-->
<mapper resource="UserMapper.xml"/>

</mappers>
    
</configuration>

2、MyBatis框架的增删改查

2.1、根据用户id查询用户

UserMapper.xml

<!--resultType：结果集封装的类型
        parameterType：参数的数据类型
        SQL语句中的取参数语法：#{基本类型 任意命名}
使用#的原理是？占位符，而使用$的原理是直接字符串拼接方式
XML文件中，如果传递的参数不是简单类型(基本数据类型和包装类，String)，而是一个具体的对象，那么在#{属性名}-->

    <!--id是自定义的名字  resultType是对应JavaBean的路径表示返回的是什么类型的值 -->
    <!--查询全部-->
   <!--根据id查询-->
    <select id="find" resultType="USER" parameterType="Integer">
        select * from user where id=#{id}
    </select>

测试类

    @Test
    public void getMyBatis() throws IOException {
       InputStream inputStream= Resources.getResourceAsStream("mybatis-config.xml");

       //从xml中构建SqlSessionFactory的实例
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlSession=sqlSessionFactory.openSession();
     
        User user=sqlSession.selectList("test.find",1);
        System.out.println(user);
        //使用完SqlSession对象，把它关闭掉
        sqlSession.close();
    }

2.2 、根据用户名模糊查询

UserMapper.xml

<!--模糊查询like-->
    <select id="find2" resultType="User" parameterType="String">
        select * from user where name like #{name}
    </select>

测试类：

//模糊查询
    @Test
    public void find2() throws IOException {
        InputStream inputStream= Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlSession=sqlSessionFactory.openSession();

        List<User> list=sqlSession.selectList("test.find2","%查%");
        for (User l:list){
            System.out.println(l);
        }
    }

2.3、根据用户名查询${}参数的方式

模糊查询能用#不用$

使用#的原理是？占位符，而使用$ 的原理是直接字符串拼接方式

$使用在参数传入数据库的对象的时候，例如表名，列名等(select xxx from xxx order by 列名)

UserMapper.xml

<select id="findByName2" resultType="com.bdit.pojo.User" parameterType="String">
	select * from user where name like ${name}
</select>

//模糊查询
    @Test
    public void find2() throws IOException {
        InputStream inputStream= Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlSession=sqlSessionFactory.openSession();

        List<User> list=sqlSession.selectList("test.find2","%查%");
        for (User l:list){
            System.out.println(l);
        }
    }

2.4 、添加新用户

UserMapper.xml

<!--添加  parameterType表示给它一个什么类型的值-->
    <insert id="add" parameterType="User">

        <!--
            selectKey标签是用来执行一次SQL语句的
            属性:order：在insert之前或之后执行
            keyProperty：查询的结果放在哪里显示
            resultType：查询结果的类型
        -->
        <!--获取添加之后的id-->
        <selectKey order="AFTER" keyProperty="id" resultType="Integer">
            select LAST_INSERT_ID()
        </selectKey>

        insert into user(name,keyword,description)value(
        #{name},#{keyword},#{description}
        )
    </insert>

 //添加
    @Test
    public void add() throws IOException {
        InputStream inputStream= Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlSession=sqlSessionFactory.openSession();

        User user=new User();
        user.setName("小明");
        user.setKeyword("abc-zxc");
        user.setDescription("飞行员");
        int a=sqlSession.insert("test.add",user);
        System.out.println(a);
        System.out.println(user.getId());//获取添加之后的id
        sqlSession.commit();
        sqlSession.close();
    }

2.5、根据id修改用户和删除用户

UserMapper.xml

<!--修改-->
    <update id="update" parameterType="User">
        update user set name=#{name},keyword=#{keyword},description=#{description} where id=#{id}
    </update>

    <!--删除-->
    <delete id="delete" parameterType="Integer">
        delete from user where id=#{id}
    </delete>

@Test
    //修改
    public void update() throws IOException {
        InputStream inputStream= Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlSession=sqlSessionFactory.openSession();

        User user=sqlSession.selectOne("test.find",22);
        user.setName("小洛");
        user.setKeyword("123");
        user.setDescription("坦克");
        int a=sqlSession.update("test.update",user);
        System.out.println(a);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    //删除
    public void delete() throws IOException {
        InputStream inputStream= Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlSession=sqlSessionFactory.openSession();

        int a=sqlSession.delete("test.delete",23);
        System.out.println(a);
        sqlSession.commit();
        sqlSession.close();
    }

3、原始DAO层开发方式

步骤:

准备DAO接口
实现DAO接口中的方法

IUserDao，代码：

package com.bdit.dao;

import com.bdit.pojo.User;

import java.util.List;

public interface IUserDao {

    public int add(User user);
    public int update(User user);
    public int delete(Integer id);
    public User find(Integer id);
    public List<User> findall();
}

UserDaoImpl实现类：

package com.bdit.dao.impl;

import com.bdit.dao.IUserDao;
import com.bdit.pojo.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import java.util.List;

public class UserDaoImpl implements IUserDao {
    private SqlSessionFactory sqlSessionFactory;
    public UserDaoImpl(SqlSessionFactory sqlSessionFactory){
        this.sqlSessionFactory=sqlSessionFactory;
    }

    @Override
    public int add(User user){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        int a=sqlSession.insert("test.add",user);
        return a;
    }

    @Override
    public int update(User user){
        SqlSession sqlSession=sqlSessionFactory.openSession();
        int a=sqlSession.update("test.update",user);
        return a;
    }

    @Override
    public int delete(Integer id) {
        SqlSession sqlSession=sqlSessionFactory.openSession();
        int a=sqlSession.delete("test.delete",id);
        return a;
    }

    @Override
    public User find(Integer id) {
        SqlSession sqlSession=sqlSessionFactory.openSession();
        User user=sqlSession.selectOne("test.find",id);
        return user;
    }

    @Override
    public List<User> findall() {
        SqlSession sqlSession=sqlSessionFactory.openSession();
        List<User> list=sqlSession.selectList("test.findall");
        return list;
    }
}

测试类：

package com.bdit;

import com.bdit.dao.IUserDao;
import com.bdit.dao.impl.UserDaoImpl;
import com.bdit.pojo.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.Before;
import org.junit.Test;

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

public class MyBatiesDaoTest {
    private  SqlSessionFactory sqlSessionFactory;

    @Before
    public void init() throws IOException {
        InputStream inputStream=Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
        sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
    }

    @Test
    public void findall(){
        IUserDao iUserDao= new UserDaoImpl(sqlSessionFactory);
        List<User> list=iUserDao.findall();
        for(User l:list){
            System.out.println(l);
        }
    }
}

4、Mapper接口

定义一个Mapper接口，这个接口其实和我们IUserDao接口的目的是一样的，就是用来定义一系列相关的方法的声明。mybatis中我们不需要为mapper接口提供实现类，因为mybatis框架提供了一个Mapper接口的代理对象，通过代理对象调用接口中的方法完成业务。

传统的dao开发方式中的实现类其实起到了一个连接、承上启下的作用，连接了接口和XML映射文件，效果就是调用接口的方法时能够找到XML映射文件。

Mapper动态代理开发遵从的规范：

●SQL映射文件中的namespace必须和mapper接口的全限定名保持一致

●mapper接口中的方法名必须和SQL映射文件中SQL语句的id属性值保持一致

●mapper接口中方法的参数类型必须和SQL语句中paramterType的值保持一致

●mapper接口中方法的返回值类型必须和SQL语句中resultType的值保持一致

IUserMapper

package com.bdit.dao;

import com.bdit.pojo.Quary;
import com.bdit.pojo.User;

import java.util.List;

public interface IUserMapper {

    public int add(User user);
    public int update(User user);
    public int delete(Integer id);
    public User find(Integer id);
    public List<User> findall();
}

测试类：

package com.bdit;

import com.bdit.dao.IUserMapper;
import com.bdit.pojo.Quary;
import com.bdit.pojo.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.After;
import org.junit.Before;
import org.junit.Test;

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

public class MyBatiesMapperTest {
    private SqlSessionFactory sqlSessionFactory;
    private SqlSession sqlSession;
    @Before
    public void init() throws IOException {
        InputStream inputStream= Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
        sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
        sqlSession=sqlSessionFactory.openSession();
    }

    @Test
    public void findall(){
        IUserMapper iUserMapper=sqlSession.getMapper(IUserMapper.class);
        List<User> list=iUserMapper.findall();
        for(User l:list){
            System.out.println(l);
        }
    }
    @After
    public void destory(){
        sqlSession.commit();
        sqlSession.close();
    }
}

5、全局Properties配置

<?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="db.properties">
        <!--连接数据库方法二：-->
        <!--<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/test3?useUnicode=true&amp;characterEncoding=utf8&amp;serverTimezone=GMT%2B8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>-->
    </properties>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>

        <!--生产环境下的数据源-->
        <environment id="product">
        <!--事务管理器
                type="JDBC"表示当前MyBatis采用JDBC的事务，
                Connection 接口方法 setAutoCommit(false) commit rollback
                type="MANAGERED" 表示mybaits不管理事务，交给其他的框架管理
        -->
        <transactionManager type="JDBC"></transactionManager>
        <!--
            POOLED使用数据库连接池
            UNPOOLED不使用连接池
         -->
    <dataSource type="POOLED"></dataSource>
    </environment>

    <!--测试环境下的数据源-->
    <environment id="test">
        <transactionManager type=""></transactionManager>
        <dataSource type=""></dataSource>
    </environment>

        </environments>

<mappers>
<!--resource属性加载SQL映射文件-->
<mapper resource="UserMapper.xml"/>

</mappers>
</configuration>

6、MyBatis数据类型匹配和别名设置

在这里插入图片描述

<typeAliases>
        <!--设置别名方法一 -->
        <!--<typeAlias type="com.bdit.pojo.User" alias="User"/>-->
        <!--设置别名方法二  在这个路径下不区分大小写-->
        <package name="com.bdit.pojo"/>
    </typeAliases>

7、全局配置文件mappers

mappers注册SQL映射文件的
●resource属性加载SQL映射文件
●class 使用注解时来配置Mapper接口
●package批量扫描注册

<mappers>
<!--resource属性加载SQL映射文件-->
<mapper resource="UserMapper.xml"/>
<!--class使用注解时来配置Mapper文件-->
<mapper class="com.bdit.dao.IUserMapper"/>
<!--package批量扫描-->
<package name="com.bdit.dao"/>
</mappers>

8、Mybatis输入参数类型

如果一个POJO中的属性，包含了另一个POJO类

QueryVo

package com.bdit.pojo;

public class Quary {
    //特殊情况包含另一个POJO类
    private User user;

    public Quary(){}

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    @Override
    public String toString() {
        return "Quary{" +
                "user=" + user +
                '}';
    }
}

IUserMapper

//特殊情况包含另一个POJO类
    public List<User> findall2(Quary quary);

xml

 <!--特殊情况包含另一个POJO类-->
    <select id="findall2" resultType="User" parameterType="Quary">
        select * from user where name like #{user.name}
    </select>

测试：

 //特殊情况包含另一个POJO类
    @Test
    public void findall2(){
        IUserMapper iUserMapper=sqlSession.getMapper(IUserMapper.class);
        Quary quary=new Quary();
        User user=new User();
        user.setName("%修%");
        quary.setUser(user);
        List<User> list=iUserMapper.findall2(quary);
        for(User l:list){
            System.out.println(l);
        }
    }

9、Mybatis手动映射

当数据表中的列名和POJO类中的属性名不同时，将会出现封装数据失败的情况，Mybatis无法将数据表中的数据准确的封装到POJO对象中，因此必须手动映射表中字段和属性的匹配关系。

Shop

package com.bdit.pojo;

import java.io.Serializable;

public class Shop implements Serializable {
    private static final long serialVersionUID = 2550483658181847727L;

    //和数据库的名不一样
    private Integer ids;
    private String name;
    private Integer pri;
    public Shop(){}

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getPri() {
        return pri;
    }

    public void setPri(Integer pri) {
        this.pri = pri;
    }

    public Integer getIds() {
        return ids;
    }

    public void setIds(Integer ids) {
        this.ids = ids;
    }

    @Override
    public String toString() {
        return "Shop{" +
                "ids=" + ids +
                ", name='" + name + '\'' +
                ", pri=" + pri +
                '}';
    }
}

IShopMapper接口

package com.bdit.dao;

import com.bdit.pojo.Shop;

import java.util.List;

public interface IShopMapper {
    public List<Shop> findall();
}

ShopMapper.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.bdit.dao.IShopMapper">
    <!--查询t_user表中所有的数据-->
    <select id="findall" resultMap="map">
        SELECT * FROM shop
    </select>

    <!--手动映射属性和表中字段的映射关系-->
    <resultMap id="map" type="com.bdit.pojo.Shop">
        <!--表中主键和属性id的映射关系-->
        <id property="ids" column="id"/>
        <!--普通属性和列名的映射关系-->
        <result property="name" column="username"/>
        <result property="pri" column="price"/>
    </resultMap>
</mapper>

注册ShopMapper.xml

<mapper resource="StudentMapper.xml"/>

测试类：

package com.bdit;

import com.bdit.dao.IShopMapper;
import com.bdit.pojo.Shop;
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.IOException;
import java.io.InputStream;
import java.util.List;


public class ShopMyBatiesTest {

    private SqlSessionFactory sqlSessionFactory;
    private SqlSession sqlSession;
    @Before
    public void init() throws IOException {
       InputStream inputStream= Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder=new SqlSessionFactoryBuilder();
        sqlSessionFactory=sqlSessionFactoryBuilder.build(inputStream);
        sqlSession=sqlSessionFactory.openSession();
    }

    @Test
    public void findall(){
        IShopMapper iShopMapper=sqlSession.getMapper(IShopMapper.class);
        List<Shop> list=iShopMapper.findall();
        for(Shop s:list){
            System.out.println(s);
        }
    }
}