Mybatis

mybatis

mybatis快速入门

1. 创建一个maven项目
2. 在pom.xml文件中添加依赖

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.5</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
</dependency>

3. 添加mybatis配置文件mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://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:///bjpowernode"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="AdminMapper.xml"/>
    </mappers>
</configuration>

4. 添加sql映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="admin">
    <select id="selectAdmin" resultType="com.mybatis.pojo.Admin">
        select * from admin;
    </select>
</mapper>

5. 创建对应的实体类

package com.mybatis.pojo;

public class Admin {
    Integer id;
    String name;
    String pwd;

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    @Override
    public String toString() {
        return "Admin{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

6. 编写对应的代码

package com.mybatis;

import com.mybatis.pojo.Admin;
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对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

//        3.执行sql
//        AdminMapper.xml文件的命名空间（admin）和对应的sql语句脚本文件id（selectAdmin）
        List<Admin> admins = sqlSession.selectList("admin.selectAdmin");
        System.out.println(admins);
//         4. 释放资源
        sqlSession.close();
    }
}

mapper代理开发

不依赖于字符串字面值，会更安全一点；其次，如果你的 IDE 有代码补全功能，那么代码补全可以帮你快速选择到映射好的 SQL 语句。

1. 定义与SQL映射文件同名的Mapper接口

package com.mybatis.mapper;

import com.mybatis.pojo.Admin;

import java.util.List;

public interface AdminMapper {

    List<Admin> selectAdmin();
}

2. 修改之前的AdminMapper.xml文件内容

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.mapper.AdminMapper">
    <select id="selectAdmin" resultType="com.mybatis.pojo.Admin">
        select * from admin;
    </select>
</mapper>

3. 修改mybatis-config.xml中mapper对应的路径

<mappers>
    <mapper resource="com/mybatis/mapper/AdminMapper.xml"/>
</mappers>

4. 编写对应的Java代码

package com.mybatis;

import com.mybatis.pojo.Admin;
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对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

//        3.执行sql
        AdminMapper adminMapper = sqlSession.getMapper(AdminMapper.class);
        List<Admin> admins = adminMapper.selectAdmin();
        System.out.println(admins);
//         4. 释放资源
        sqlSession.close();
    }
}

5. 包扫描的方式加载映射文件

 <mappers>
<!--        <mapper resource="com/mybatis/mapper/AdminMapper.xml"/>-->
     <package name="com.mybatis.mapper"/>
</mappers>

mybatis核心配置文件

环境配置

在不同的环境使用不同的数据库

比如测试环境与开发环境使用不同的数据库

<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:///bjpowernode"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
        </dataSource>
    </environment>
    <environment id="test">
        <transactionManager type="JDBC"/>
        <dataSource type="POOLED">
            <property name="driver" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql:///test"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
        </dataSource>
    </environment>
</environments>

类型别名

<typeAliases>
    <package name="com.mybatis.pojo"/>
</typeAliases>

配置别名之后会去读取对应的实体类，从而在mapper文件中resultType可以直接用实体类的名称（不区分大小写）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.mapper.AdminMapper">
    <select id="selectAdmin" resultType="admin">
        select * from admin;
    </select>
</mapper>

配置顺序

标签的书写顺序按照下面的顺序配置，不然会报错

MybatisX插件

结果映射，属性别名

Java命名规范用的驼峰命名法，数据库设计列名命名是用的下划线命名，此时使用mybatis查询数据时，列字段名对应不上，不能自动封装数据，查询出来的结果是null。
此时需要对查询的结果做映射，设置属性别名。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">



<mapper namespace="com.mybatis.mapper.BrandMapper">

    <resultMap id="brandResultMap" type="brand">
        <result column="brand_name" property="brandName" />
        <result column="company_name" property="companyName" />
    </resultMap>

    <select id="selectAll" resultMap="brandResultMap">
        select * from t_brand;
    </select>
</mapper>

动态参数

#{} 和 ${}

#{} 采用的是?定义参数替换sql的形式
${} 采用的是拼接sql的形式，会被sql注入，用的很少，比如某sql的表名是动态的时候

特殊字符处理

xml文件中是标签，sql语句中小于大于也是< > 与xml冲突，会导致xml异常
因此需要用

1. 转义字符如 < 代替 < &rt; 代替 >

<resultMap id="brandResultMap" type="brand">
    <result column="brand_name" property="brandName" />
    <result column="company_name" property="companyName" />
</resultMap>

<select id="selectAll" resultMap="brandResultMap">
    select * from t_brand where ordered &lt; #{ordered};
</select>

2. CDATA区

<resultMap id="brandResultMap" type="brand">
    <result column="brand_name" property="brandName" />
    <result column="company_name" property="companyName" />
</resultMap>

<select id="selectAll" resultMap="brandResultMap">
select * from t_brand where ordered <![CDATA[
            <
        ]]> #{ordered};
</select>

查询

散装参数

//        1.加载mybatis的核心配置文件，获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//        2.获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

//        3.执行sql
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//        List<Brand> brands = brandMapper.selectAll();
        int ordered = 10;
        String brandName = "%苹果%";
        String companyName = "%红富士%";
        List<Brand> brands = brandMapper.selectBrand(ordered,brandName,companyName);
        System.out.println(brands);
//         4. 释放资源
        sqlSession.close();

public interface BrandMapper {
    public List<Brand> selectAll();

    public List<Brand> selectBrand(@Param("ordered") int ordered, @Param("brandName") String brand_name, @Param("companyName") String companyName);
}

<mapper namespace="com.mybatis.mapper.BrandMapper">

    <resultMap id="brandResultMap" type="brand">
        <result column="brand_name" property="brandName"/>
        <result column="company_name" property="companyName"/>
    </resultMap>

    <select id="selectAll" resultType="com.mybatis.pojo.Brand">
        select * from t_brand;
    </select>

    <select id="selectBrand" resultMap="brandResultMap">
        select * from t_brand
        <where>
            <if test="ordered != null">
                and ordered = #{ordered}
            </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>
</mapper>

对象参数

Brand brand = new Brand();
brand.setOrdered(ordered);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
List<Brand> brands = brandMapper.selectBrandByObj(brand);

public interface BrandMapper {
    public List<Brand> selectAll();

    public List<Brand> selectBrand(@Param("ordered") int ordered, @Param("brandName") String brand_name, @Param("companyName") String companyName);

    public List<Brand> selectBrandByObj(Brand brand);
}

<select id="selectBrandByObj" resultMap="brandResultMap">
    select * from t_brand
    <where>
        <if test="ordered != null">
            and ordered = #{ordered}
        </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>

map参数

Map map = new HashMap();
map.put("ordered",ordered);
map.put("brandName",brandName);
map.put("companyName",companyName);
List<Brand> brands = brandMapper.selectBrandByMap(map);

public interface BrandMapper {
    public List<Brand> selectAll();

    public List<Brand> selectBrand(@Param("ordered") int ordered, @Param("brandName") String brand_name, @Param("companyName") String companyName);

    public List<Brand> selectBrandByObj(Brand brand);

    public List<Brand> selectBrandByMap(Map map);
}

<select id="selectBrandByMap" resultMap="brandResultMap">
    select * from t_brand
    <where>
        <if test="ordered != null">
            and ordered = #{ordered}
        </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>

动态SQL单一查询

Map map = new HashMap();
map.put("ordered",ordered);
List<Brand> brands = brandMapper.selectBrandByMapAny(map);

public interface BrandMapper {
    public List<Brand> selectAll();

    public List<Brand> selectBrand(@Param("ordered") int ordered, @Param("brandName") String brand_name, @Param("companyName") String companyName);

    public List<Brand> selectBrandByObj(Brand brand);

    public List<Brand> selectBrandByMapAny(Map map);
}

<select id="selectBrandByMapAny" resultMap="brandResultMap">
    select * from t_brand
    <where>
        <choose><!--相当于switch-->
            <when test="ordered != null"><!--相当于case-->
                ordered = #{ordered}
            </when>
            <when test="companyName != null and companyName != '' "><!--相当于case-->
                company_name like #{companyName}
            </when>
            <when test="brandName != null and brandName != ''"><!--相当于case-->
                brand_name like #{brandName}
            </when>

        </choose>
    </where>
</select>

添加

public static void main(String[] args) throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //        默认开启事务,可以给openSession传递一个布尔值true,表示自动提交事务
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    int ordered = 10;
    String brandName = "苹果";
    String companyName = "红富士2";
    String description = "描述";
    Brand brand = new Brand();
    brand.setOrdered(ordered);
    brand.setBrandName(brandName);
    brand.setCompanyName(companyName);
    brand.setDescription(description);
    brandMapper.add(brand);
    // 手动提交事务
    sqlSession.commit();
    //        获取新增的主键id
    Integer id = brand.getId();
    System.out.println(id);
    //         4. 释放资源
    sqlSession.close();	
}

public interface BrandMapper {
    public List<Brand> selectAll();

    public List<Brand> selectBrand(@Param("ordered") int ordered, @Param("brandName") String brand_name, @Param("companyName") String companyName);

    public List<Brand> selectBrandByObj(Brand brand);

    public List<Brand> selectBrandByMapAny(Map map);

    public void add(Brand brand);
}

<!--    设置useGeneratedKeys=true表示需要获取对应的id, KeyProperty表示id映射的值 -->
<insert id="add" useGeneratedKeys="true" keyProperty="id">
    insert into t_brand (brand_name, company_name, ordered, description)
    values (#{brandName}, #{companyName}, #{ordered}, #{description});
</insert>

修改

String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//        默认开启事务,可以给openSession传递一个布尔值true,表示自动提交事务
SqlSession sqlSession = sqlSessionFactory.openSession();
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
int ordered = 10;
String brandName = "苹果";
String companyName = "红富士2";
String description = "描述";
Brand brand = new Brand();
brand.setOrdered(ordered);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setDescription(description);
brandMapper.add(brand);
//        获取新增的主键id
int id = brand.getId();
brand.setBrandName("苹果更新");
int update_total = brandMapper.update(brand);
// 手动提交事务
sqlSession.commit();
List<Brand> brands = brandMapper.selectAll();
System.out.println(update_total);
//         4. 释放资源
sqlSession.close();

public interface BrandMapper {
    public List<Brand> selectAll();

    public List<Brand> selectBrand(@Param("ordered") int ordered, @Param("brandName") String brand_name, @Param("companyName") String companyName);

    public List<Brand> selectBrandByObj(Brand brand);

    public List<Brand> selectBrandByMapAny(Map map);

    public void add(Brand brand);

    public int update(Brand brand);
}

<update id="update">
    update t_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>
    </set>
    where id = #{id};
</update>

删除

单个删除

public static void main(String[] args) throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //        默认开启事务,可以给openSession传递一个布尔值true,表示自动提交事务
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    brandMapper.deleteById(1);
    // 手动提交事务
    sqlSession.commit();
    //         4. 释放资源
    sqlSession.close();
}

public interface BrandMapper {
    public List<Brand> selectAll();

    public List<Brand> selectBrand(@Param("ordered") int ordered, @Param("brandName") String brand_name, @Param("companyName") String companyName);

    public List<Brand> selectBrandByObj(Brand brand);

    public List<Brand> selectBrandByMapAny(Map map);

    public void add(Brand brand);

    public int update(Brand brand);

    /**
     * 根据id删除
     */
    public void deleteById(int id);
}

<delete id="deleteById">
    delete from t_brand where id = #{id};
</delete>

批量删除

public static void main(String[] args) throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //        默认开启事务,可以给openSession传递一个布尔值true,表示自动提交事务
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    int [] ids = {3,4,5};
    brandMapper.deleteByIds(ids);
    // 手动提交事务
    sqlSession.commit();
    //         4. 释放资源
    sqlSession.close();
}

public interface BrandMapper {
    public List<Brand> selectAll();

    public List<Brand> selectBrand(@Param("ordered") int ordered, @Param("brandName") String brand_name, @Param("companyName") String companyName);

    public List<Brand> selectBrandByObj(Brand brand);

    public List<Brand> selectBrandByMapAny(Map map);

    public void add(Brand brand);

    public int update(Brand brand);

    /**
     * 根据id删除
     */
    public void deleteById(int id);


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

<!--
        mybatis会将数组参数，封装为一个Map集合。
            * 默认：array = 数组
            * 使用@Param注解改变map集合的默认key的名称
    -->

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

注解开发

mybatis提供了四个注解，用来实现简单Sql语句的执行

@Select()

@Update()

@Insert()

@Delete()

例如 查询t_brand中所有的数据

public static void main(String[] args) throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //        默认开启事务,可以给openSession传递一个布尔值true,表示自动提交事务
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    List<Brand> brands = brandMapper.selectAllByAnnotation();
    System.out.println(brands);
    // 手动提交事务
    sqlSession.commit();
    //         4. 释放资源
    sqlSession.close();
}

public interface BrandMapper {
	
    ...
    @Select("select * from t_brand;")
    public List<Brand> selectAllByAnnotation();
}