MyBatis框架的基本使用

1.MyBatis简介

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。

2.Mybatis入门使用

1.首先创建一个数据库表用于操作

DROP DATABASE mybatis;
CREATE database mybatis;
use mybatis;
drop table if exists tb_user;

CREATE TABLE tb_user(
 id int PRIMARY KEY auto_increment,
 username VARCHAR(20),
 password VARCHAR(20),
 gender char(1),
 addr varchar(30)
 );
 
 insert into tb_user VALUES (1,'王伟','123','男','北京');
 insert into tb_user VALUES (2,'杨晓萌','234','女','天津');
 insert into tb_user VALUES (3,'孙冰','11','男','西安');
 

2.然后需要在maven的xml文件中配置Mybatis依赖

<!--mybatis的依赖-->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.5</version>
    </dependency>

3.写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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--数据库连接信息-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="1234"/>
            </dataSource>
        </environment>
    </environments>
    <!--指定sql映射的文件-->
    <mappers>
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>

4.写sql映射文件

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

<!--
    namespace 名称空间
-->

<mapper namespace="test">
    <!--id是sql语句的唯一标识  resultType对应返回结果的类型-->
    <select id="selectAll" resultType="com.yrh.pojo.User">
        select * from tb_user;
    </select>
</mapper>

5.Mybatis测试类Java代码

User类如何实现此处省略

package com.yrh;

import com.yrh.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 java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MybatisDemo {
    public static void main(String[] args) throws IOException {
        //1.加载mybatis的核心配置文件 获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2.获取SqlSession对象,用来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3.执行sql语句
        List<User> users = sqlSession.selectList("test.selectAll");
        System.out.println(users);
        //4.释放资源
        sqlSession.close();
    }
}

3.Mapper代理开发(常用)

Mybits-config

    <!--指定sql映射的文件-->
    <mappers>
<!--        <mapper resource="com/yrh/mapper/UserMapper.xml"/>-->
<!--        包扫描方式-->
        <package name="com.yrh.mapper"/>
    </mappers>
</configuration>

UseMapper.xml

<!--设定为Mapper接口的全限定名-->
<mapper namespace="com.yrh.mapper.UserMapper">
    <!--id是sql语句的唯一标识  resultType对应返回结果的类型-->
    <select id="selectAll" resultType="com.yrh.pojo.User">
        select * from tb_user;
    </select>
</mapper>

UserMapper.java(接口)

package com.yrh.mapper;

import com.yrh.pojo.User;

import java.util.List;

public interface UserMapper {
    List<User> selectAll();
}

Mapper代理开发Java代码

package com.yrh.pojo;


import com.yrh.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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MybatisDemo2 {
    public static void main(String[] args) throws IOException {
        //1.加载mybatis的核心配置文件 获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2.获取SqlSession对象,用来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3.执行sql语句
        //List<User> users = sqlSession.selectList("test.selectAll");
        //System.out.println(users);
        // 3.1 获取对应的UserMapper接口的代理对象
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.selectAll();
        for (User user : users) {
            System.out.println(user);
        }
        //4.释放资源
        sqlSession.close();
    }
}

4.使用Mybatis实现数据的增删改查

<mapper namespace="com.yrh.mapper.BrandMapper">
    <resultMap id="brandResultMap" type="com.yrh.pojo.Brand">
        <!--
            id：完成主键字段的映射
                column：表的列名
                property：实体类的属性名
            result：完成一般字段的映射
                column：表的列名
                property：实体类的属性名
        -->
        <result column="brand_name" property="brandName"/>
        <result column="company_name" property="companyName"/>
    </resultMap>
    <select id="selectAll" resultMap="brandResultMap">
        select * from tb_brand;
    </select>
</mapper>

1.查询功能

多条件查询:

<select id="selectByCondition" resultMap="brandResultMap">
        select * from tb_brand where status = #{status} and company_name like #{companyName} and brand_name like #{brandName}
    </select>

List<Brand> selectByCondition(Brand brand);

 public static void selectbyCondition() throws IOException{

        int status = 1;
        String companyName = "华为";
        String brandName = "华为";

        companyName = "%"+companyName+"%";
        brandName = "%"+brandName+"%";
        Brand brand = new Brand();
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        brand.setStatus(status);
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2.获取SqlSession对象,用来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3.执行sql语句
        //List<User> users = sqlSession.selectList("test.selectAll");
        //System.out.println(users);
        // 3.1 获取对应的UserMapper接口的代理对象
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        List<Brand> brands = mapper.selectByCondition(brand);
        System.out.println(brands);
        //4.释放资源
        sqlSession.close();
    }

动态条件查询:dan

<select id="selectByCondition" resultMap="brandResultMap">
        select * from tb_brand
        <where>
            <if test="status!=null">and status = #{status}</if>
            <if test="companyName!=null and companyName!=''">
                and company_name like #{companyName}
            </if>
            <if test="brandName!=null and brandName!=''">
                and brand_name like #{brandName}
            </if>

        </where>
    </select>

单条件动态查询

 2.添加功能

<insert id="add" useGeneratedKeys="true" keyProperty="id">
        insert into tb_brand (brand_name, company_name, ordered, description, status)
        values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});

    </insert>

使用修改数据库的功能时需要手动提交事务,也可以设置自动提交

SqlSession sqlSession = sqlSessionFactory.openSession(true);

设置主键返回

3.修改功能

 

<update id="update">
        update tb_brand
        <set>
            <if test="brandName != null and brandName != ''">
                brand_name = #{brandName},
            </if>
            <if test="companyName != null and companyName != ''">
                company_name = #{companyName},
            </if>
            <if test="ordered != null">
                ordered = #{ordered},
            </if>
            <if test="description != null and description != ''">
                description = #{description},
            </if>
            <if test="status != null">
                status = #{status}
            </if>
        </set>
        where id = #{id};
    </update>

4.删除功能

 /**
     * 批量删除
     */
    void deleteByIds(int[] ids);

    <delete id="deleteByIds">
        delete from tb_brand where id
        in
            <foreach collection="array" item="id" separator="," open="(" close=")">
                #{id}
            </foreach>
             ;
    </delete>

5.参数传递

6.使用注解的方式完成增删改查

@Select("select * from tb_user where id = #{id}")
    User selectById(int id);