MyBatis配置详解和增删改查

柔光的暖阳◎ 2022-12-25 00:54 160阅读 0赞

# 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">
            
            <transactionManager type="JDBC"></transactionManager>
            
        <dataSource type="POOLED"></dataSource>
        </environment>
    
        
        <environment id="test">
            <transactionManager type=""></transactionManager>
            <dataSource type=""></dataSource>
        </environment>
    
            </environments>
    
    <mappers>
    
    <mapper resource="UserMapper.xml"/>
    
    </mappers>
        
    </configuration>

# 2、MyBatis框架的增删改查 #

## 2.1、根据用户id查询用户 ##

UserMapper.xml

<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

<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>

## 2.4 、添加新用户 ##

UserMapper.xml

<insert id="add" parameterType="User">
    
            
            
            <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层开发方式 #

步骤:

1.  准备DAO接口
2.  实现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">
            
            
        </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">
            
            <transactionManager type="JDBC"></transactionManager>
            
        <dataSource type="POOLED"></dataSource>
        </environment>
    
        
        <environment id="test">
            <transactionManager type=""></transactionManager>
            <dataSource type=""></dataSource>
        </environment>
    
            </environments>
    
    <mappers>
    
    <mapper resource="UserMapper.xml"/>
    
    </mappers>
    </configuration>

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZsdWNreV9sb25n_size_16_color_FFFFFF_t_70_pic_center]

# 7、全局配置文件mappers #

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

# 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

<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">
        
        <select id="findall" resultMap="map">
            SELECT * FROM shop
        </select>
    
        
        <resultMap id="map" type="com.bdit.pojo.Shop">
            
            <id property="ids" column="id"/>
            
            <result property="name" column="username"/>
            <result property="pri" column="price"/>
        </resultMap>
    </mapper>

注册ShopMapper.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);
            }
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZsdWNreV9sb25n_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/bc2728c3373d424598c35142b25e94b6.png