MyBatis

• MyBatis是简化JDBC开发的优秀持久框架
• MyBatis原本是Apache的开源项目iBatis。2010年，该项目由apache组成 software foundation迁移到google code，并更名为MyBatis。2013年11月迁至Githubbis。
• 官网：https://mybatis.org/mybatis-3/zh/getting-started.html

持久层：

• 将数据保存到数据库的代码层
• JavaEE三层架构：表现层、业务层、持久层

框架：

• 框架是半成品软件，是一套可重用的、通用的、基本的软件代码模型
• 在框架的基础上，构建更高效、标准化、通用、可扩展的软件编写

1. 创建user表，添加数据

1. 创建模块，导入坐标

<dependencies>    <dependency>        <groupId>org.mybatis</groupId>        <artifactId>mybatis</artifactId>        <version>3.5.10</version>    </dependency>    <dependency>        <groupId>mysql</groupId>        <artifactId>mysql-connector-java</artifactId>        <version>8.0.31</version>    </dependency>    <dependency>        <groupId>junit</groupId>        <artifactId>junit</artifactId>        <version>4.13.2</version>        <scope>test</scope>    </dependency>    <dependency>        <groupId>org.slf4j</groupId>        <artifactId>slf4j-api</artifactId>        <version>1.7.20</version>    </dependency>    <dependency>        <groupId>ch.qos.logback</groupId>        <artifactId>logback-classic</artifactId>        <version>1.2.3</version>    </dependency>    <dependency>        <groupId>ch.qos.logback</groupId>        <artifactId>logback-core</artifactId>        <version>1.2.3</version>    </dependency>      <dependency>        <groupId>org.projectlombok</groupId>        <artifactId>lombok</artifactId>        <version>1.18.26</version>    </dependency></dependencies>

将logback导入resource.xml配置

<?xml version="1.0" encoding="UTF-8"?><configuration>    <appender name="Console" class="ch.qos.logback.core.ConsoleAppender">        <encoder>            <pattern>[%level] %blue(%d{HH:mm:ss.SSS}) %cyan([%thread]) %boldGreen(%logger{15}) - %msg %n</pattern>        </encoder>    </appender>    <logger name="com.xc" level = "DEBUG" additivity="false">        <appender-ref ref="Console" />    </logger></configuration>

1. 编制MyBatis核心配置文件 --> 替换连接信息，解决硬编码问题

<?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.cj.jdbc.Driver"/>                <property name="url" value="jdbc:mysql:///test?useSSL=false"/>                <property name="username" value="root"/>                <property name="password" value=******>            </dataSource>        </environment>    </environments>    <mappers>        <mapper resource="UserMapper.xml"/>    </mappers></configuration>

1. 编制SQL映射文件 --> 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"><!--    namespace: 命名空间--><mapper namespace="test">    <select id="selectAll" resultType="com.xc.pojo.User">        select * from user    </select></mapper>

1. 编码

1. 定义POJO类

package com.xc.pojo;import lombok.Data;/** * @author xc * @date 2023/5/30 18:21 */@Datapublic class User {    private Long id;    private String name;    private Integer age;    private String role;}

1. 加载核心配置文件，获取SqlSessionFactory对象
2. 获取SQLSession对象，执行SQL语句
3. 释放资源

public static void main(String[] args) throws IOException {        // 加载mybatis核心配置文件        String resource = "mybatis-config.xml";        InputStream inputStream = Resources.getResourceAsStream(resource);        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);        // 获取sqlsession对象        SqlSession sqlSession = sqlSessionFactory.openSession();        // 执行sql        List<User> userList = sqlSession.selectList("test.selectAll");        System.out.println(userList);        // 释放资源        sqlSession.close();    }

结果

• 目的

• 解决原始方法中硬编码的问题
• 后期SQL简化

1. 定义与SQL映射文件同名的Mapper接口，并在同一目录下放置Mapper接口和SQL映射文件

1. 设置SQL映射文件的namespace属性是Mapper接口的全限定名

1. 在Mapper接口中定义方法名是SQL映射文件中的SQL语句id，并保持参数类型与返回值类型一致

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

1. 编码

1. Mapper接口的代理对象是通过SqlSessiongetmaper获取的
2. 调用相应的方法完成sql执行

public static void main(String[] args) throws IOException {    // 加载mybatis核心配置文件    String resource = "mybatis-config.xml";    InputStream inputStream = Resources.getResourceAsStream(resource);    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);    // 获取sqlsession对象    SqlSession sqlSession = sqlSessionFactory.openSession();    // 代理对象获取UserMaper接口    UserMapper mapper = sqlSession.getMapper(UserMapper.class);    // 执行sql    List<User> userList = mapper.selectAll();    System.out.println(userList);    // 释放资源    sqlSession.close();}

结果

注：您还可以使用包扫描来加载sql映射文件

<?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.cj.jdbc.Driver"/>                <property name="url" value="jdbc:mysql:///test?useSSL=false"/>                <property name="username" value="root"/>                <property name="password" value=*******>            </dataSource>        </environment>    </environments>    <mappers><!--        加载sql映射文件--><!--        加载sql映射文件--><!--        <mapper resource="com/xc/mapper/UserMapper.xml"/>--><!--        Mapper代理-->        <package name="com.xc.mapper"/>    </mappers></configuration>

官网：https://mybatis.org/mybatis-3/zh/configuration.html

environments：配置数据库连接环境信息，可配置多个environment，通过default属性切换不同的environment

typeAliases：类型别名可以是 Java 类型设置缩写名称。 它仅用于 XML 配置旨在减少冗余的全限定类名写作。

在配置每个标签时，需要遵守前后顺序

编写Mapper接口

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

测试

package com.xc.testUser;import com.xc.mapper.BrandMapper;import com.xc.mapper.UserMapper;import com.xc.pojo.Brand;import com.xc.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;/** * @author xc * @date 2023/5/30 18:24 */public class Mybatis {    SqlSession sqlSession;    BrandMapper mapper;    @Before    public void init() throws IOException {        // 加载mybatis核心配置文件        String resource = "mybatis-config.xml";        InputStream inputStream = Resources.getResourceAsStream(resource);        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);        // 获取sqlsession对象        sqlSession = sqlSessionFactory.openSession();        // 代理对象获取UserMaper接口        mapper = sqlSession.getMapper(BrandMapper.class);    }    @Test    public void testSelectAll() {        List<Brand> brands = mapper.selectAll();        System.out.println(brands);    }    @After    public void end() {        // 释放资源        sqlSession.close();    }}

结果

由于字段名与数据库不对应，发现有些字段为null，解决方案：

或使用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"><!--    namespace: 命名空间--><mapper namespace="com.xc.mapper.BrandMapper"><!--    sql片段-->    <sql id="breand_cloumn">        id, brand_name as brandName, company_name as companyName, ordered, description,status    </sql>    <select id="selectAll" resultType="com.xc.pojo.Brand">        select        <include refid="breand_cloumn"/>        from tb_brand    </select></mapper>

结果

使用resultmapp

<?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"><!--    namespace: 命名空间--><mapper namespace="com.xc.mapper.BrandMapper"><!--      id：唯一标识    type：支持别名的映射类型  -->    <resultMap id="brandResultMap" type="com.xc.pojo.Brand"><!--           id：完成主键字段的映射      column：表的列名      property：实体字段名    result：完成一般字段的映射-->        <result column="brand_name" property="brandName" />        <result column="company_name" property="companyName" />    </resultMap><!-- 使用 resultmap属性 -->    <select id="selectAll" resultMap="brandResultMap">        select        *        from tb_brand    </select></mapper>

结果

定义Mapper接口

public interface BrandMapper {    List<Brand> selectAll();    Brand selectById(int id);}

定义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"><!--    namespace: 命名空间--><mapper namespace="com.xc.mapper.BrandMapper">    <resultMap id="brandResultMap" type="com.xc.pojo.Brand">        <result column="brand_name" property="brandName" />        <result column="company_name" property="companyName" />    </resultMap><!--  查id-->    <select id="selectById" resultMap="brandResultMap">        select * from tb_brand where id = #{id}    </select></mapper>

查询

package com.xc.testUser;import com.xc.mapper.BrandMapper;import com.xc.mapper.UserMapper;import com.xc.pojo.Brand;import com.xc.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;/** * @author xc * @date 2023/5/30 18:24 */public class Mybatis {    SqlSession sqlSession;    BrandMapper mapper;    @Before    public void init() throws IOException {        // 加载mybatis核心配置文件        String resource = "mybatis-config.xml";        InputStream inputStream = Resources.getResourceAsStream(resource);        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);        // 获取sqlsession对象        sqlSession = sqlSessionFactory.openSession();        // 代理对象获取UserMaper接口        mapper = sqlSession.getMapper(BrandMapper.class);    }    @Test    public void testSelectById() {        Brand brand = mapper.selectById(1);        System.out.println(brand);    }    @After    public void end() {        // 释放资源        sqlSession.close();    }}

结果

定义Mapper接口

public interface BrandMapper {    List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);    List<Brand> selectByCondition(Brand brand);    List<Brand> selectByCondition(Map map);}

编写映射文件

<?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"><!--    namespace: 命名空间--><mapper namespace="com.xc.mapper.BrandMapper">    <resultMap id="brandResultMap" type="com.xc.pojo.Brand">        <result column="brand_name" property="brandName" />        <result column="company_name" property="companyName" />    </resultMap>    <select id="selectByCondition" resultMap="brandResultMap">        select * from tb_brand        where            status = #{status}        and company_name like #{companyName}        and brand_name like #{brandName}    </select></mapper>

测试

package com.xc.testUser;import com.xc.mapper.BrandMapper;import com.xc.mapper.UserMapper;import com.xc.pojo.Brand;import com.xc.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.HashMap;import java.util.List;import java.util.Map;/** * @author xc * @date 2023/5/30 18:24 */public class Mybatis {    SqlSession sqlSession;    BrandMapper mapper;    @Before    public void init() throws IOException {        // 加载mybatis核心配置文件        String resource = "mybatis-config.xml";        InputStream inputStream = Resources.getResourceAsStream(resource);        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);        // 获取sqlsession对象        sqlSession = sqlSessionFactory.openSession();        // 代理对象获取UserMaper接口        mapper = sqlSession.getMapper(BrandMapper.class);    }    @Test    public void testSelectByCondition() {        List<Brand> brands1 = mapper.selectByCondition(1,“%小%”，"%小米%";        Brand b = new Brand();        b.setStatus(1);        b.setBrandName(%华%);        b.setCompanyName(%华%);        List<Brand> brands2 = mapper.selectByCondition(b);        Map<String,Object> map = new HashMap<>();        map.put("status",0);        map.put("companyName"%三%";        map.put("brandName","%三%";        List<Brand> brands3 = mapper.selectByCondition(map);        System.out.println(brands1);        System.out.println(=======================);        System.out.println(brands2);        System.out.println(=======================);        System.out.println(brands3);    }    @After    public void end() {        // 释放资源        sqlSession.close();    }}

结果

在xml中定义sql语句中添加if标签

<select id="selectByCondition" resultMap="brandResultMap">        select * from tb_brand        where 1 = 1            <if test="status != null">                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>    </select>

优雅方式

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

多选一

Mapper接口

// 直接使用Brand对象Listtt，因为不知道要传输哪个参数。<Brand> selectByConditionSingle(Brand brand);

或者直接用where标签包裹

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

• 查询：@Select
• 添加：@Insert
• 修改：@Update
• 删除：@Delete

提示：

• 完成简单功能的注释
• 配置文件完成复杂功能