引入Mybatis

JDBC介绍

Java代码和数据库之间交互的桥梁是JDBC（Java Database Connectivity），使用jdbc连接数据库并执行sql的代码如下：

public class JdbcApp {
    public static void main(String[] args) {
        try(InputStream inputStream = JdbcApp.class.getResourceAsStream("/mysql-env.properties")){
            //0.读取配置
            Properties properties = new Properties();
            properties.load(inputStream);
            String driver = properties.getProperty("driver");
            String url = properties.getProperty("url");
            String username = properties.getProperty("username");
            String password = properties.getProperty("password");

            //1.加载驱动
            Class.forName(driver);
            //2.建立连接
            Connection conn = DriverManager.getConnection(url, username, password);
            //3.创建sql
            String sql = "select id,name,code,comment from conn_test where is_del='0'";
            PreparedStatement statement = conn.prepareStatement(sql);
            //4.执行sql
            ResultSet resultSet = statement.executeQuery();
            //5.处理结果
            while (resultSet.next()){
                long id = resultSet.getLong("id");
                String name = resultSet.getString("name");
                String code = resultSet.getString("code");
                System.out.println(id+name+code);
            }
            //6.关闭资源
            resultSet.close();
            statement.close();
            conn.close();
        } catch (SQLException e) {
            System.out.println("sql错误");
        } catch (ClassNotFoundException e) {
            System.out.println("类型错误");
        } catch (IOException e){
            System.out.println("文件路径错误");
        }
    }
}

这种方式存在几个问题：

1. 对于过长或复杂sql，尤其是需要根据不同条件生成不同sql的场景不好实现
2. 对查询结果的使用不友好，不能自动映射为对象
3. 对于长文本（sql语句），硬编码影响代码美观，可通过配置文件解决

实现一个查询功能

以下是一个典型的系统管理后台，通过jdbc的方式，如何实现其中的查询按钮基本功能？只考虑服务端实现

代码实现过程如下：

1. 实体类

   package top.sunyog.common.entity;
   
   import java.time.LocalDateTime;
   
   /**
    * 查询条件类
    *
    * @author Myste
    * @since 2023/10/31 15:19
    */
   public class AppSearchVo {
       /*应用名称*/
       private String appName;
       /*认证类型*/
       private String authType;
       /*创建时间-开始*/
       private LocalDateTime startDate;
       /*创建时间-结束*/
       private LocalDateTime endDate;
   
       //省略getter、setter、toString方法
   
       public boolean isEmpty(){
           boolean emptyName = appName == null || "".equals(appName);
           boolean emptyAuth = authType == null || "".equals(authType);
           return emptyName && emptyAuth && startDate==null && endDate==null;
       }
   }
   
   package top.sunyog.common.entity;
   
   import java.time.LocalDate;
   
   /**
    * 数据库映射类
    * @author Myste
    * @since 2023/10/31 15:36
    */
   public class AppTestEntity {
       private Long id;
       private String appName;
       private String appCode;
       private String authType;
       private LocalDate createDate;
       private String creator;
   
       //省略getter、setter、toString方法
   }

2. 上下文类

   package top.sunyog.jdbc;
   
   import java.io.IOException;
   import java.io.InputStream;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   import java.util.Properties;
   
   /**
    * 上下文类,用于保持数据库连接
    * @author Myste
    * @since 2023/10/31 15:22
    */
   public class JdbcContext {
       private static Connection dbConn=null;
   
       public void initContext(){
           try(InputStream inputStream = JdbcContext.class.getResourceAsStream("/mysql-env.properties")) {
               //0.读取配置
               Properties properties = new Properties();
               properties.load(inputStream);
               String driver = properties.getProperty("driver");
               String url = properties.getProperty("url");
               String username = properties.getProperty("username");
               String password = properties.getProperty("password");
   
               //1.加载驱动
               Class.forName(driver);
               //2.建立连接
               dbConn = DriverManager.getConnection(url, username, password);
           }catch (SQLException e) {
               System.out.println("sql错误");
           } catch (ClassNotFoundException e) {
               System.out.println("类型错误");
           } catch (IOException e){
               System.out.println("文件路径错误");
           }
       }
   
       public void close() throws SQLException {
           if (dbConn!=null && !dbConn.isClosed()) {
               dbConn.close();
           }
       }
   
       public Connection getConn() {
           if(dbConn==null){
               this.initContext();
           }
           return dbConn;
       }
   }

3. 映射类

   package top.sunyog.jdbc.mapper;
   
   import com.mysql.cj.util.StringUtils;
   import top.sunyog.common.entity.AppSearchVo;
   import top.sunyog.common.entity.AppTestEntity;
   
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.time.LocalDate;
   import java.time.format.DateTimeFormatter;
   import java.util.ArrayList;
   import java.util.List;
   
   /**
    * 数据库映射类,用于查询数据,并将查询结果映射为一个个对象
    * @author Myste
    * @since 2023/10/31 15:35
    */
   public class ApplicationRepository {
       private Connection conn = null;
   
       public ApplicationRepository(Connection conn) {
           this.conn = conn;
       }
   
       public List<AppTestEntity> queryApp(AppSearchVo param) throws SQLException {
           StringBuffer sql = new StringBuffer("select id,app_name,app_code,auth_type,create_date,creator from app_test");
           if (!param.isEmpty()) {
               sql.append(" where 1=1");
               if (!StringUtils.isNullOrEmpty(param.getAppName())) {
                   sql.append(" and app_name like concat('%',?,'%')");
               }
               if (!StringUtils.isNullOrEmpty(param.getAuthType())) {
                   sql.append(" and auth_type = ?");
               }
               if (param.getStartDate() != null) {
                   sql.append(" and create_date >= ?");
               }
               if (param.getEndDate() != null) {
                   sql.append(" and create_date <= ?");
               }
           }
   
           DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
           PreparedStatement statement = this.conn.prepareStatement(sql.toString());
           int i = 0;
           if (!param.isEmpty()) {
               if (!StringUtils.isNullOrEmpty(param.getAppName())) {
                   statement.setString(++i, param.getAppName());
               }
               if (!StringUtils.isNullOrEmpty(param.getAuthType())) {
                   statement.setString(++i, param.getAuthType());
               }
               if (param.getStartDate() != null) {
                   statement.setString(++i, formatter.format(param.getStartDate()));
               }
               if (param.getEndDate() != null) {
                   statement.setString(++i, formatter.format(param.getEndDate()));
               }
           }
   
           ResultSet resultSet = statement.executeQuery();
           //映射结果
           List<AppTestEntity> list = new ArrayList<>();
           while (resultSet.next()) {
               AppTestEntity entity = new AppTestEntity();
               entity.setId(resultSet.getLong("id"));
               entity.setAppName(resultSet.getString("app_name"));
               entity.setAppCode(resultSet.getString("app_code"));
               entity.setAuthType(resultSet.getString("auth_type"));
               String dateStr = resultSet.getString("create_date");
               LocalDate createDate = LocalDate.parse(dateStr, formatter);
               entity.setCreateDate(createDate);
               entity.setCreator(resultSet.getString("creator"));
               list.add(entity);
           }
           return list;
       }
   }

4. 启动类

   package top.sunyog.jdbc;
   
   import top.sunyog.common.entity.AppSearchVo;
   import top.sunyog.common.entity.AppTestEntity;
   import top.sunyog.jdbc.mapper.ApplicationRepository;
   
   import java.sql.SQLException;
   import java.time.LocalDateTime;
   import java.util.List;
   
   /**
    * 启动类,用于启动上下文,并测试数据库查询功能
    * @author Myste
    * @since 2023/10/31 15:28
    */
   public class JdbcApp {
       public static void main(String[] args) throws SQLException {
           JdbcContext context = new JdbcContext();
           context.initContext();
           ApplicationRepository service = new ApplicationRepository(context.getConn());
           AppSearchVo param = new AppSearchVo();
           param.setAppName("应用");
   //        param.setAuthType("2");
           LocalDateTime now = LocalDateTime.now();
           param.setStartDate(now.plusDays(-1));
           param.setEndDate(now.plusDays(2));
           List<AppTestEntity> list = service.queryApp(param);
           list.forEach(o->System.out.println(o));
   
           context.close();
       }
   }

程序打印结果

AppTestEntity{id=1, appName='测试应用', appCode='ceshi', authType='1', createDate=2023-10-31, creator='admin'}

剩下的工作就是处理查询的结果并返回数据给客户端。通过这个例子对以上提出的几个问题可以有更清晰的认识，那么这些问题如何解决呢？

orm框架的出现就是为了解决以上问题，常用orm框架有

1. Mybatis（半自动、效率高），国内使用率高
2. Hibernate（全自动、效率低）

通过Mybatis实现该功能

1. 编辑配置

   <?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>
       <!--外部资源-->
       <properties resource="mysql-env.properties"/>
   	<!--配置数据源-->
       <environments default="mysql">
           <environment id="mysql">
               <transactionManager type="JDBC"></transactionManager>
               <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>
       </environments>
   	<!--配置映射文件位置-->
       <mappers>
           <mapper resource="mapper/ApplicationMapper.xml"/>
       </mappers>
   </configuration>

2. 读取配置

   package top.sunyog.mybatis;
   
   import org.apache.ibatis.session.SqlSession;
   import org.apache.ibatis.session.SqlSessionFactory;
   import org.apache.ibatis.session.SqlSessionFactoryBuilder;
   import top.sunyog.common.entity.AppSearchVo;
   import top.sunyog.common.entity.AppTestEntity;
   import top.sunyog.mybatis.mapper.ApplicationRepository;
   
   import java.io.IOException;
   import java.io.InputStream;
   import java.util.List;
   
   public class MybatisApp {
       public static void main(String[] args) {
           try (InputStream in = MybatisApp.class.getResourceAsStream("/mybatis-config.xml")) {
               SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
               SqlSessionFactory sqlSessionFactory = builder.build(in);
   
               ...
           } catch (IOException e) {
               System.out.println("资源路径错误");
           }
       }
   }

3. 建立SQL映射，包括接口和xml文件

   mapper接口如下：

   package top.sunyog.mybatis.mapper;
   
   import top.sunyog.common.entity.AppSearchVo;
   import top.sunyog.common.entity.AppTestEntity;
   
   import java.util.List;
   
   public interface ApplicationRepository {
       List<AppTestEntity> queryApp(AppSearchVo param);
   }

   mapper映射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="top.sunyog.mybatis.mapper.ApplicationRepository">
       ...
       <select id="queryApp" resultType="top.sunyog.common.entity.AppTestEntity">
           select id,app_name,app_code,auth_type,create_date,creator from app_test
           <where>
               <if test="null != appName and ''.toString() != appName">and app_name like concat('%',#{appName},'%')</if>
               <if test="null != authType and ''.toString() != authType">and auth_type = #{authType}</if>
               <if test="null != startDate and '' != startDate">and create_date >= #{startDate}</if>
               <if test="null != endDate and '' != endDate">and create_date &lt;= #{endDate}</if>
           </where>
       </select>
   </mapper>

4. 使用Mapper实现功能

   ...
   public class MybatisApp {
       public static void main(String[] args) {
           try (InputStream in = MybatisApp.class.getResourceAsStream("/mybatis-config.xml")) {
               SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
               SqlSessionFactory sqlSessionFactory = builder.build(in);
   
               AppSearchVo param = new AppSearchVo();
               param.setAppName("应用");
               param.setAuthType("2");
               SqlSession session = sqlSessionFactory.openSession();
               ApplicationRepository mapper = session.getMapper(ApplicationRepository.class);
               List<AppTestEntity> list = mapper.queryApp(param);
               list.forEach(o-> System.out.println(o));
               session.close();
           } catch (IOException e) {
               System.out.println("资源路径错误");
           }
       }
   }

按照以上步骤执行后，会发现打印出的结果都是空数据，这是由于数据库中app_test表的字段名和AppTestEntity类的属性名不一致，没有映射成功导致的，解决这个问题有两种方式

1. 通过ResultMap映射

<mapper namespace="...">
   	<resultMap id="appTestMap" type="top.sunyog.common.entity.AppTestEntity">
        <result column="app_name" property="appName"/>
        <result column="app_code" property="appCode"/>
        <result column="auth_type" property="authType"/>
        <result column="create_date" property="createDate"/>
    </resultMap>
       
    <select id="queryApp" resultMap="appTestMap">...</select>
</mapper>

2. 在配置文件中配置mapUnderscoreToCamelCase为true，使用这种方式需要保证数据库中的字段下划线命名和java类中的驼峰命名一一对应

   <configuration>
       <properties .../>
       
       <settings>
           <setting name="mapUnderscoreToCamelCase" value="true"/>
       </settings>
       
       <environments .../>
       
       <mappers>
       	...
       </mappers>
   </configuration>

Mybatis功能

映射

简单的增删改查

<insert id="" parameterType=""></insert>
<delete id=""></delete>
<update id="" parameterType=""></update>
<select id="" resultType=""></select>

main方法改造

public class MybatisApp {
    public static void main(String[] args) {
        SqlSession session=null;
        try (InputStream in = MybatisApp.class.getResourceAsStream("/mybatis-config.xml")) {
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory sqlSessionFactory = builder.build(in);

            session = sqlSessionFactory.openSession();
            ApplicationRepository mapper = session.getMapper(ApplicationRepository.class);

//            testInsert(mapper);
            testDelete(mapper);
//            testUpdate(mapper);
//            testQuery(mapper);

            session.commit();
        } catch (IOException e) {
            System.out.println("资源路径错误");
        } finally {
            if (session!=null){
                session.close();
            }
        }
    }
}

增

//mapper接口
int addApp(AppTestEntity entity);

<mapper>
    <insert id="addApp" parameterType="top.sunyog.common.entity.AppTestEntity">
        insert into app_test(app_name,app_code,auth_type,create_date,creator)
        values(#{appName},#{appCode},#{authType},#{createDate},#{creator})
    </insert>
</mapper>

//测试代码
    private static void testInsert(ApplicationRepository mapper) {
        AppTestEntity entity = new AppTestEntity();
        entity.setAppName("测试新增");
        entity.setAppCode("test_add_app");
        entity.setAuthType("2");
        entity.setCreateDate(LocalDate.now());
        entity.setCreator("admin2");
        int row = mapper.addApp(entity);
        System.out.println("新增成功, 影响行数: "+row);
    }

以下代码具有和xml映射文件同等的作用

@Insert("insert into app_test(app_name,app_code,auth_type,create_date,creator)"+
        "values(#{appName},#{appCode},#{authType},#{createDate},#{creator})")
int addApp(AppTestEntity entity);

删

int deleteById(Long id);

<mapper>
	<delete id="deleteById" parameterType="long">
        delete from app_test where id=#{id}
    </delete>
</mapper>

	private static void testDelete(ApplicationRepository mapper) {
        int row = mapper.deleteById(4L);
        System.out.println("删除成功,影响行数: "+row);
    }

以下代码具有同等作用

@Delete("delete from app_test where id=#{id}")
int deleteById(Long id);

改

int updateById(AppTestEntity entity);

<mapper>
	<update id="updateById" parameterType="top.sunyog.common.entity.AppTestEntity">
<!--        update app_test set app_name=#{appName},app_code=#{appCode},auth_type=#{authType},create_date=#{createDate},-->
<!--            creator=#{creator} where id=#{id}-->
        update app_test
        <set>
            <if test="appName != null and '' != appName">app_name = #{appName},</if>
            <if test="appCode != null and '' != appCode">app_code = #{appCode},</if>
            <if test="authType != null and '' != authType">auth_type = #{authType},</if>
            <if test="createDate != null and '' != createDate">create_date = #{createDate},</if>
            <if test="creator != null and '' != creator">creator = #{creator},</if>
        </set>
        where id=#{id}
    </update>
</mapper>

    private static void testUpdate(ApplicationRepository mapper) {
        AppTestEntity entity = new AppTestEntity();
//        entity.setAppName("测试修改3");
        entity.setId(4L);
//        entity.setAppCode("test_update_app");
        entity.setAuthType("2");
//        entity.setCreateDate(LocalDate.now());
//        entity.setCreator("zhangsan");
        int row = mapper.updateById(entity);
        System.out.println("修改成功,影响行数: "+row);
    }

更新操作中存在动态sql标签，直接使用 @Update 注解会使代码变复杂，后续动态sql章节详细介绍基于注解的写法。

查

AppTestEntity queryById(Long id);

<mapper>
	<select id="queryById" resultType="top.sunyog.common.entity.AppTestEntity">
        select id,app_name,app_code,auth_type,create_date,creator from app_test where id=#{id} limit 0,1
    </select>
</mapper>

    private static void testOne(ApplicationRepository mapper) {
        AppTestEntity entity = mapper.queryById(1L);
        System.out.println(entity);
    }

以下代码具有同等作用

@Select("select id,app_name,app_code,auth_type,create_date,creator from app_test where id=#{id} limit 0,1")
AppTestEntity queryById(Long id);

可优化的点

以上代码存在的问题

1. xml标签中parameterType和parameterType元素写类全名过长

   通过设置别名解决，通过注解 @Insert，@Delete，@Update，@Select 可以间接解决此问题

2. 列表查询和单个查询中存在重复代码

   通过<sql ...></sql>标签解决

3. 删除功能在业务上一般都是逻辑删除

   数据库中增加删除标记，如：is_del字段

4. insert语句新增的不一定是单行数据，可能是一个数组或list列表

   数据量小的通过<foreach ...></foreach>标签解决

   大量数据的导入，通过batch模式。见Mybatis配置说明#defaultExecutorType一章 #坑 #此坑已填

增删改查扩展

设置别名

<!-- 配置文件中新增内容 -->
<configuration>
    <properties .../>
    
	<settings>
        ...
    </settings>
    
    <!--添加别名-->
    <typeAliases>
        <!--相当于在app和top.sunyog.common.entity.AppTestEntity类之间增加了一个映射-->
        <typeAlias type="top.sunyog.common.entity.AppTestEntity" alias="app"/>
        <!--最常用-->
        <!--可以使用类文件名代替类全路径名，首字母可小写-->
        <package name="top.sunyog.my.entity"/>
    </typeAliases>
    
    <environments ...>...</environments>
    
    <mappers>...</mappers>
</configuration>

设置别名后的效果和<delete id="deleteById" parameterType="long">的效果类似，有可能mybatis内部已经预定义了long类型的别名。

可在mybatis源码的org.apache.ibatis.type.TypeAliasRegistry类中查看

sql标签用法

<mapper>
	<sql id="query_column">
        select id,app_name,app_code,auth_type,create_date,creator from app_test
    </sql>
    
    <select id="queryApp" resultMap="appTestMap">
        <!-- 引用 sql -->
        <include refid="query_column"/>
        ...
    </select>
    
    <select id="queryById" resultType="AppTestEntity">
        <!-- 引用 sql -->
        <include refid="query_column"/> 
        where id=#{id} limit 0,1
    </select>
</mapper>

逻辑删除的写法，@param注解的使用

逻辑删除就是设置删除标记，或者说修改数据状态，实际是更新操作

-- 添加数据状态字段
alter table app_test
    add app_status char default '0' not null comment '数据状态(0-正常,1-已删除,2-已冻结,3-已过期)';

//mapper接口方法
int changeStatus(Long id,String status);

<mapper ...>
	<update id="changeStatus">
        update app_test set app_status=#{status} where id=#{id}
    </update>
</mapper>

public class MybatisApp {
	private static void testUpdateStatus(ApplicationRepository mapper) {
        int row = mapper.changeStatus(1L, "1");
        System.out.println("状态修改成功, 影响行数: "+row);
    }
}

如果直接执行这段程序会报错，这里存在一个问题当mapper接口方法中存在多个入参，需要通过@param注解明确标注入参的名称，写法如下：

int changeStatus(@Param("id") Long id,@Param("status") String status);

如果只是设置一个删除标记，用不着有多个状态，可以将该字段名称设置为is_del

foreach标签的用法

//mapper接口方法
int addList(List<AppTestEntity> list);

<mapper>
	<sql id="insert_column">
        insert into app_test(app_name,app_code,auth_type,create_date,creator)
    </sql>

    <insert id="addApp" parameterType="top.sunyog.common.entity.AppTestEntity">
        <include refid="insert_column"/>
        values(#{appName},#{appCode},#{authType},#{createDate},#{creator})
    </insert>
	<!--foreach标签的用法, 类似于for(T item, collection){}-->
    <!-- 
		参数说明:
		collection--list或array类型的(mapper方法的参数名称,或@Param标注的名称)
		separator--collection标注的list中各个元素的分隔符
		item--collection中的元素名称
		index--以下标的形式获取collection集合元素
		open--foreach的起始字符串
		close--foreach的结束字符串
	-->
    <insert id="addList" parameterType="appTestEntity">
        <include refid="insert_column"/>
        values
        <foreach collection="list" separator="," item="entity" index="index" open="" close="">
            (#{entity.appName},#{entity.appCode},#{entity.authType},#{entity.createDate},#{entity.creator})
        </foreach>
    </insert>
</mapper>

public class MybatisApp {	
	private static void testAddList(ApplicationRepository mapper) {
        AppTestEntityBuilder builder = new AppTestEntityBuilder();
        builder.setCreator("admin3").setAuthType("2");
        List<AppTestEntity> list=new ArrayList<>();
        list.add(builder.setAppName("名称1").setAppCode("code-1").build());
        list.add(builder.setAppName("name2").setAppCode("code-2").build());
        list.add(builder.setAppName("jack liu").setAppCode("code-3").build());

        int row = mapper.addList(list);
        System.out.println("数据新增成功,影响行数: "+row);
    }
}

//这里为了代码好看,可以通过builder的方式创建多个集合元素
public class AppTestEntityBuilder {
    private String appName;
    private String appCode;
    private String authType;
    private String creator;

    public AppTestEntityBuilder setAppName(String appName) {
        this.appName = appName;
        return this;
    }

    public AppTestEntityBuilder setAppCode(String appCode) {
        this.appCode = appCode;
        return this;
    }

    public AppTestEntityBuilder setAuthType(String authType) {
        this.authType = authType;
        return this;
    }

    public AppTestEntityBuilder setCreator(String creator) {
        this.creator = creator;
        return this;
    }

    public AppTestEntity build(){
        AppTestEntity res = new AppTestEntity();
        res.setAppName(this.appName);
        res.setAppCode(this.appCode);
        res.setCreateDate(LocalDate.now());
        res.setCreator(this.creator);
        res.setAuthType(this.authType);

        return res;
    }
}

selectKey标签生成主键

Mybatis基础#增一章中提到的新增数据操作依赖于mysql数据库的自增主键，如果mysql没有设置自增可以通过以下代码达到同样的效果

<insert id="addApp" parameterType="top.sunyog.common.entity.AppTestEntity">  
    <selectKey keyProperty="id" resultType="long" order="BEFORE">  
        select max(id)+1 as id from app_test  
    </selectKey>  
    insert into app_test(id,app_name,app_code,auth_type,create_date,creator)
    values(#{id},#{appName},#{appCode},#{authType},#{createDate},#{creator})  
</insert>

selectKey标签属性说明

1. keyProperty——实体类中的主键属性名
2. resultType——查询结果类型
3. order——可在BEFORE和AFTER中选择，表示在insert语句之前（或之后）执行
4. keyColumn——数据库中的字段名
5. statementType——JDBC的statement类型，可在STATEMENT、PREPARED、CALLABLE中选择 以上配置可根据实际情况选择使用

提出的疑问

1. mapper接口中的方法，入参有几种形式

2. sql查询的返回值支持几种形式

3. resultMap标签是否支持复杂对象

4. 对于状态型的字段，如app_status，是否支持枚举类型

5. 如果希望跨类型映射，通过什么方式实现（类型转换）

   这里说的跨类型是指，varchar类型的字段，映射为非String类型，如StringBuilder或者其他带有String类属性的类；或者date、datetime类型字段，可以自行选择转换为Date、String、LocalDate、LocaclDateTime、自定义等类型

以上问题，Mybatis都有自己的解决方案。

mybatis映射详解

为了方便后续测试，简单改造一下现有程序

1. 构建全局上下文

   /**
    * mybatis全局上下文类，功能是读取配置文件,构建SqlSessionFactory,注册service
    */
   public class MybatisAppContext {
       private static SqlSessionFactory sqlSessionFactory = null;
   
       private Map<String, MybatisService> serviceMap = new ConcurrentHashMap<>();
   }

2. 构建基础service类

   public abstract class MybatisService<T> {
       private SqlSession session=null;
       private T defaultMapper;
   
       public void registrySession(SqlSession session) {
           this.session=session;
       }
   
       public void closeSession(){
           if (session!=null){
	           session.commit();
               session.close();
           }
       }
   
       protected T getMapper(Class<? extends T> mapperClass){
           if (this.defaultMapper==null) {
               this.defaultMapper = this.session.getMapper(mapperClass);
           }
           return this.defaultMapper;
       }
   
       abstract void doService();
   }

3. 将SqlSessionFactory添加到全局上下文

   public class MybatisAppContext {
       private static SqlSessionFactory sqlSessionFactory = null;
   
       private Map<String, MybatisService> serviceMap = new ConcurrentHashMap<>();
   
       
       /**
        * 注册SqlSessionFactory
        */
       static {
           SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
           try (InputStream in = MybatisApp.class.getResourceAsStream("/mybatis-config.xml")) {
               sqlSessionFactory = builder.build(in);
           } catch (IOException e) {
               System.out.println("文件路径读取错误");
           }
       }
       
       
       /**
        * 关闭所有session
        *
        * @author Myste
        * @since 2023/11/3 11:25
        */
       public void close() {
           for (Map.Entry<String, MybatisService> entry : this.serviceMap.entrySet()) {
               entry.getValue().closeSession();
           }
       }
   }

4. 在全局上下文注册service

   public class MybatisAppContext {	
       ...
           
   	/**
        * 注册service
        *
        * @param serviceClass
        * @return
        * @throws InstantiationException
        * @throws IllegalAccessException
        * @author Myste
        * @since 2023/11/3 11:04
        */
       public MybatisService registryService(Class<? extends MybatisService> serviceClass) throws InstantiationException, IllegalAccessException {
           SqlSession session = sqlSessionFactory.openSession();
           MybatisService service = serviceClass.newInstance();
           service.registrySession(session);
           this.serviceMap.put(serviceClass.getName(), service);
           return service;
       }
   
       /**
        * 获取service
        *
        * @param serviceClass
        * @return
        * @author Myste
        * @since 2023/11/3 11:04
        */
       public MybatisService getService(Class<? extends MybatisService> serviceClass) throws InstantiationException, IllegalAccessException {
           String name = serviceClass.getName();
           if (this.serviceMap.containsKey(name)) {
               return this.serviceMap.get(name);
           } else {
               return this.registryService(serviceClass);
           }
       }
   }

5. 将之前的测试代码，提取到ApplicationService类中

   所有静态方法改为非静态方法

   实现MybatisService#doService方法

   package top.sunyog.mybatis.service;
   
   import top.sunyog.common.entity.AppSearchVo;
   import top.sunyog.common.entity.AppTestEntity;
   import top.sunyog.common.entity.AppTestEntityBuilder;
   import top.sunyog.mybatis.mapper.ApplicationRepository;
   
   import java.time.LocalDate;
   import java.util.ArrayList;
   import java.util.List;
   
   /**
    * @author Myste
    * @since 2023/11/3 10:51
    */
   public class ApplicationService extends MybatisService<ApplicationRepository>{
       
       private void testUpdateStatus(ApplicationRepository mapper) {
           int row = mapper.changeStatus(1L, "1");
           System.out.println("状态修改成功, 影响行数: "+row);
       }
   
       private void testAddList(ApplicationRepository mapper) {
           AppTestEntityBuilder builder = new AppTestEntityBuilder();
           builder.setCreator("admin3").setAuthType("2");
           List<AppTestEntity> list=new ArrayList<>();
           list.add(builder.setAppName("名称1").setAppCode("code-1").build());
           list.add(builder.setAppName("name2").setAppCode("code-2").build());
           list.add(builder.setAppName("jack liu").setAppCode("code-3").build());
   
           int row = mapper.addList(list);
           System.out.println("数据新增成功,影响行数: "+row);
       }
   
       private void testOne(ApplicationRepository mapper) {
           AppTestEntity entity = mapper.queryById(1L);
           System.out.println(entity);
       }
   
       private void testDelete(ApplicationRepository mapper) {
           int row = mapper.deleteById(4L);
           System.out.println("删除成功,影响行数: "+row);
       }
   
       private void testUpdate(ApplicationRepository mapper) {
           AppTestEntity entity = new AppTestEntity();
   //        entity.setAppName("测试修改3");
           entity.setId(4L);
   //        entity.setAppCode("test_update_app");
           entity.setAuthType("2");
   //        entity.setCreateDate(LocalDate.now());
   //        entity.setCreator("zhangsan");
           int row = mapper.updateById(entity);
           System.out.println("修改成功,影响行数: "+row);
       }
   
       private void testInsert(ApplicationRepository mapper) {
           AppTestEntity entity = new AppTestEntity();
           entity.setAppName("测试新增");
           entity.setAppCode("test_add_app");
           entity.setAuthType("2");
           entity.setCreateDate(LocalDate.now());
           entity.setCreator("admin2");
           int row = mapper.addApp(entity);
           System.out.println("新增成功, 影响行数: "+row);
       }
   
       private void testQuery(ApplicationRepository mapper) {
           AppSearchVo param = new AppSearchVo();
           param.setAppName("应用");
           param.setAuthType("2");
           List<AppTestEntity> list = mapper.queryApp(param);
           list.forEach(o-> System.out.println(o));
       }
   
       @Override
       public void doService() {
           testQuery(super.getMapper(ApplicationRepository.class));
       }
   }

6. 修改main方法

   /**
    * mybatis启动类,,测试数据查询
    *
    * @author Myste
    * @since 2023/10/31 16:53
    */
   public class MybatisApp {
       public static void main(String[] args) throws InstantiationException, IllegalAccessException {
           MybatisAppContext context = new MybatisAppContext();
           MybatisService service = context.registryService(ApplicationService.class);
           service.doService();
           context.close();
       }
   }

mapper方法的入参形式

之前使用过的入参的形式有：

1. 一个入参，数据库类型，如：基本类型包装类
2. 一个入参，简单JavaBean
3. 多参数，通过@Param标记
4. HashMap作为入参
5. 数组或集合类型，并通过<foreach ...></foreach>标签包裹

若将查询条件改为关键字，通过模糊搜索名称和编码。这类业务可以通过以HashMap作为参数进行查询。

//mapper接口
public interface SimpleQueryMapper {
    List<AppTestEntity> queryMap(Map<String, Object> params);
}

//映射文件
<mapper namespace="top.sunyog.mybatis.mapper.SimpleQueryMapper">
    <select id="queryMap" resultType="appTestEntity">
        select id,app_name,app_code,auth_type,create_date,creator from app_test
        <where>
            <if test="null != keyword and ''.toString() != keyword">
                and (app_name like concat('%',#{keyword},'%') or app_code like concat('%',#{keyword},'%'))
            </if>
            <if test="null != authType and ''.toString() != authType">and auth_type = #{authType}</if>
        </where>
    </select>
</mapper>

//业务service
public class SimpleQueryService extends MybatisService<SimpleQueryMapper>{
    @Override
    public void doService() {
        SimpleQueryMapper mapper = super.getMapper(SimpleQueryMapper.class);
        this.testHashMapParam(mapper);
    }

    private void testHashMapParam(SimpleQueryMapper mapper) {
        Map<String, Object> params=new HashMap<>(8);
        params.put("keyword","-");
        params.put("authType","2");

        List<AppTestEntity> list = mapper.queryMap(params);
        list.forEach(o-> System.out.println(o));
    }
}

//测试启动
public class MybatisApp {
    public static void main(String[] args) throws InstantiationException, IllegalAccessException {
        MybatisAppContext context = new MybatisAppContext();
        MybatisService service = context.registryService(SimpleQueryService.class);
        service.doService();
        context.close();
    }
}

结果映射的形式

结果映射的形式包括

1. 基本类型包装类
2. 简单JavaBean，或POJO
3. Map，（这种形式不建议使用）
4. 复杂类型 Mybatis基础#复杂类型返回值
5. 以上类型的数组或集合

Map类型结果

<!--映射文件-->
<select id="queryMapRes" resultType="java.util.Map">  
    select id,app_name,app_code,auth_type,create_date,creator from app_test  
    <where>  
	    <choose>        
		    <when test="id != null and id > 0">id=#{id}</when>
	        <otherwise>id='1'</otherwise>
	    </choose>
    </where>
</select>

//mapper接口
List<Map<String, Object>> queryMapRes(@Param("id") int id);

//业务service
@Override  
public void doService() {  
    SimpleQueryMapper mapper = super.getMapper(SimpleQueryMapper.class);  

    this.testHashMapResult(mapper);  
}

private void testHashMapResult(SimpleQueryMapper mapper){  
    List<Map<String,Object>> list=mapper.queryMapRes(null);  
    list.forEach(o->{  
        o.entrySet().forEach(entry->{  
            String str = entry.getKey() + " : " + entry.getValue().toString();  
            System.out.println(str);  
        });  
    });  
}

打印结果如下：

app_name : 测试应用
auth_type : 1
creator : admin
id : 1
create_date : 2023-10-31
app_code : ceshi

复杂类型结果

Mybatis结果映射

枚举类型映射

简单枚举映射

如果需要返回枚举类型的查询结果，如果返回值和枚举值一一对应，可以直接使用枚举类型接收返回结果。

新增字典数据

INSERT INTO dict_test (dict_name, dict_code, dict_type, dict_sort) VALUES ('NONE', '1', 'app_auth_type', 0);
INSERT INTO dict_test (dict_name, dict_code, dict_type, dict_sort) VALUES ('MOBILE', '2', 'app_auth_type', 2);
INSERT INTO dict_test (dict_name, dict_code, dict_type, dict_sort) VALUES ('WECHAT', '3', 'app_auth_type', 3);
INSERT INTO dict_test (dict_name, dict_code, dict_type, dict_sort) VALUES ('QQ', '4', 'app_auth_type', 4);

新增枚举类

public enum AuthType {  
    NONE,WECHAT,QQ,MOBILE;  
}

mapper映射

AuthType getAuthType(@Param("code")String code);

<select id="getAuthType" resultType="top.sunyog.common.entity.AuthType">  
    select dict_name from dict_test where dict_type='app_auth_type' and dict_code=#{code}  
</select>

测试类

private void testEnumResultService(SimpleQueryMapper mapper){  
    AuthType authType = mapper.getAuthType("3");  
    System.out.println(authType);  
}

打印结果

WECHAT

枚举顺序映射

mybatis内置了EnumOrdinalTypeHandler类型处理器，来实现字典顺序号和枚举类型之间的映射。注意枚举类型的顺序号从0开始。 代码示例： mapper-xml

<resultMap id="app-auth-order" type="map">  
    <result property="auth_type" column="auth_type" javaType="top.sunyog.common.entity.AuthType" typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>  
</resultMap>  
<select id="getAppAuthOrder" resultMap="app-auth-order">  
    select auth_type from app_test where id=#{id}  
</select>

mapper接口

Map<String,Object> getAppAuthOrder(@Param("id") Long id);

测试类：

public class SimpleQueryService extends MybatisService<SimpleQueryMapper>{
	private void testEnumOrdder(SimpleQueryMapper mapper) {  
	    Map<String, Object> map = mapper.getAppAuthOrder(2L);  
	    map.entrySet().forEach(o-> System.out.println(o.getKey()+": "+o.getValue()));  
	}
}

打印结果(auth_type=2)

auth_type: QQ

复杂枚举映射

对于返回值和枚举名称不对应的情况，可以使用自定义类型处理器的方式解决, 在类型处理器中处理数据库数据和枚举类型之间的对应关系

自定义类型处理器

public class AppAuthTypeHandler extends BaseTypeHandler<AppStatus> {  
    @Override  
    public void setNonNullParameter(PreparedStatement ps, int i, AppStatus parameter, JdbcType jdbcType) throws SQLException {  
        ps.setString(i,this.appStatusToString(parameter));  
    }  
  
    @Override  
    public AppStatus getNullableResult(ResultSet rs, String columnName) throws SQLException {  
        String str = rs.getString(columnName);  
        return this.stringToAppStatus(str);  
    }  
  
    @Override  
    public AppStatus getNullableResult(ResultSet rs, int columnIndex) throws SQLException {  
        String str = rs.getString(columnIndex);  
        return this.stringToAppStatus(str);  
    }  
  
    @Override  
    public AppStatus getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {  
        String str = cs.getString(columnIndex);  
        return this.stringToAppStatus(str);  
    }  
  
    private String appStatusToString(AppStatus status){  
        switch (status){  
            case FREEZE:  
                return "冻结应用";  
            case NORMAL:  
                return "正常应用";  
            case OVERDUE:  
                return "过期应用";  
            case TEMPORARY:  
            default:  
                return "临时应用";  
        }  
    }  
  
    private AppStatus stringToAppStatus(String str){  
        switch (str){  
            case "冻结应用":  
                return AppStatus.FREEZE;  
            case "正常应用":  
                return AppStatus.NORMAL;  
            case "过期应用":  
                return AppStatus.OVERDUE;  
            default:  
                return AppStatus.TEMPORARY;  
        }  
    }  
}

定义新的结果值类型

public class AppDict {  
    private AppStatus appStatus;  
  
    public AppStatus getAppStatus() {  
        return appStatus;  
    }  
  
    public void setAppStatus(AppStatus appStatus) {  
        this.appStatus = appStatus;  
    }  
  
    @Override  
    public String toString() {  
        return "AppDict{" +  
                "appStatus=" + appStatus +  
                '}';  
    }  
}

新增mapper方法

AppDict getAppStatusEnum(@Param("code")String code);

定义映射文件，通过resultMap设置类型处理器

<resultMap id="app-status-enum" type="top.sunyog.common.entity.AppDict">  
    <result property="appStatus" column="dict_name" typeHandler="top.sunyog.mybatis.handler.AppAuthTypeHandler"/>  
</resultMap>  
<select id="getAppStatusEnum" resultMap="app-status-enum">  
    select dict_name from dict_test where dict_type='app_status' and dict_code=#{code}  
</select>

测试代码

private void testEnumStatusService(SimpleQueryMapper mapper){  
    AppDict appDict = mapper.getAppStatusEnum("1");  
    System.out.println(appDict);  
}

打印结果

AppDict{appStatus=FREEZE}

类型处理器

以上对复杂枚举映射的解决方式即是类型处理器的简单应用，在开发过程中更常见的是对LocalDateTime等事件类型的转换。

这是因为在Mybatis的早期版本中，对于日期类型的数据通常使用 Java.util.Date类型接收，如果使用 java.time.LocalDateTime类型接收该字段会造成结果值为空的情况，这时候要么升级Mybatis版本，要么通过自定义类型处理器实现

降低mybatis版本到3.4.4

<dependencies>  
    <dependency>        
	    <groupId>org.mybatis</groupId>  
        <artifactId>mybatis</artifactId>  
        <version>3.4.4</version>  
    </dependency>
</dependencies>

此时重新启动项目会报错，需要修改启动类

public class MybatisAppContext {  
    private static SqlSessionFactory sqlSessionFactory = null;  
  
    private Map<String, MybatisService> serviceMap = new ConcurrentHashMap<>();  
  
    /**  
     * 注册SqlSessionFactory  
     */    static {  
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();  
        try (InputStream in = MybatisApp.class.getResourceAsStream("/mybatis-config.xml");  
                InputStreamReader reader=new InputStreamReader(in)) {  
            sqlSessionFactory = builder.build(reader);  
        } catch (IOException e) {  
            System.out.println("文件路径读取错误");  
        }  
    }
	
	...
}

此时再启动项目仍会报错，提示没有对应的类处理器 新增类型处理器 LocalDateHandler

package top.sunyog.mybatis.handler;  
  
import org.apache.ibatis.type.BaseTypeHandler;  
import org.apache.ibatis.type.JdbcType;  
  
import java.sql.CallableStatement;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.time.LocalDate;  
  
public class LocalDateHandler extends BaseTypeHandler<LocalDate> {  
    @Override  
    public void setNonNullParameter(PreparedStatement ps, int i, LocalDate parameter, JdbcType jdbcType) throws SQLException {  
        ps.setObject(i,parameter);  
    }  
  
    @Override  
    public LocalDate getNullableResult(ResultSet rs, String columnName) throws SQLException {  
        return rs.getObject(columnName,LocalDate.class);  
    }  
  
    @Override  
    public LocalDate getNullableResult(ResultSet rs, int columnIndex) throws SQLException {  
        return rs.getObject(columnIndex,LocalDate.class);  
    }  
  
    @Override  
    public LocalDate getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {  
        return cs.getObject(columnIndex,LocalDate.class);  
    }  
}

配置文件添加配置项typeHandler

<settings .../>
<typeAliasis .../>

<typeHandlers>  
    <typeHandler handler="top.sunyog.mybatis.handler.LocalDateHandler"/>  
</typeHandlers>

<environments .../>

<mappers .../>

测试代码

public class SimpleQueryService extends MybatisService<SimpleQueryMapper>{  
    @Override  
    public void doService() {  
        SimpleQueryMapper mapper = super.getMapper(SimpleQueryMapper.class);  
        this.testHashMapParam(mapper);
    }
}

打印结果

AppTestEntity{id=5, appName='名称1', appCode='code-1', authType='2', createDate=2023-11-03, creator='admin3', appStatus='null', authTypeDict=null, appStatusDict=null, services=null}
AppTestEntity{id=6, appName='name2', appCode='code-2', authType='2', createDate=2023-11-03, creator='admin3', appStatus='null', authTypeDict=null, appStatusDict=null, services=null}
AppTestEntity{id=7, appName='jack liu', appCode='code-3', authType='2', createDate=2023-11-03, creator='admin3', appStatus='null', authTypeDict=null, appStatusDict=null, services=null}

注意：以上处理方式只能解决由于Mybatis版本原因造成的LocalDateTime或LocalDate等的类型转换失败问题。但类型转换失败有可能是数据库驱动、或连接池的版本问题造成的，实际开发过程中遇到过在Oracle数据库中ojdbc7驱动接收LocalDateTime类时间数据失败报错的问题，一般通过升级到ojdbc8都能解决。如果项目版本升级比较麻烦，可以使用Date类型接收日期时间数据，在service层再做转换或不转换，通过@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss", timezone="GMT+8")注解的方式指定时区保证时间的准确性

再谈动态SQL

对于第一章提到的问题（jdbc对于过长，过复杂，多条件查询的无力）Mybatis提供动态SQL这一特性解决拼接SQL语句的痛点。

1. 条件语句if、choose...when...otherwise
2. 循环语句foreach
3. sql条件语句where、set
4. 扩展语句trim、bind、script
5. 动态sql API

Mybatis动态SQL详解

配置

读取配置

mybatis项目读取配置的方式有两种，一种是通过读取xml配置文件的方式读取，另一种是通过java代码进行配置，这两中方式都是通过SqlSessionFactory这个类完成对mybatis的配置工作，这两种方式的样例代码如下：

1. 通过xml文件配置

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>  
	<!--设置外部资源路径-->
    <properties resource="mysql-env.properties"/>  
    
    <settings>  
	    <!--设置下划线映射为驼峰-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>  
    </settings>  
    
    <typeAliases>  
	    <!--设置类型别名-->
        <package name="top.sunyog.common.entity"/>  
    </typeAliases>  
    
    <typeHandlers>    
        <!--设置类型处理器-->    
	    <typeHandler handler="top.sunyog.mybatis.handler.LocalDateHandler"/>  
    </typeHandlers>    
    
    <environments default="mysql">  
    <!--设置数据源和事务处理器-->
        <environment id="mysql">  
            <transactionManager type="JDBC"></transactionManager>  
            <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>    
    </environments>  

	<!--设置mapper映射文件/映射接口的位置-->
    <mappers>        
	    <mapper resource="mapper/ApplicationMapper.xml"/>  
    </mappers>  
</configuration>

读取xml配置

public class MybatisApp{
	private static SqlSessionFactory sqlSessionFactory;
	
	public static void startConfigFromXml(){
		SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();  
		
		try (InputStream in = MybatisApp.class.getResourceAsStream("/mybatis-config.xml");  
		        InputStreamReader reader=new InputStreamReader(in)) {  
		    this.sqlSessionFactory = builder.build(reader);  
		} catch (IOException e) {  
		    System.out.println("文件路径读取错误");  
		}
	}
}

2. 通过javaAPI配置

public class MybatisApp{
    private static SqlSessionFactory sqlSessionFactory;
	private static void startConfigFromApi(){
        MysqlDataSource dataSource = new MysqlDataSource();
        dataSource.setPassword("123456admin");
        dataSource.setUrl("jdbc:mysql://db.sunyog.top:13306/sunyog_db");
        dataSource.setUser("root");
        JdbcTransactionFactory transFactory = new JdbcTransactionFactory();
        Environment env = new Environment("env-2", transFactory,dataSource);

        Configuration config = new Configuration();
        config.setEnvironment(env);
        config.getMapperRegistry().addMappers("top.sunyog.my.mapper");
        config.getTypeAliasRegistry().registerAliases("top.sunyog.my.entity");
        config.setMapUnderscoreToCamelCase(true);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
    }	
}

通过以上示例，并分析Mybatis代码可以看出，Mybatis通过org.apache.ibatis.session.Configuration类来保存所有的配置，通过org.apache.ibatis.session.SqlSessionFactoryBuilder类来 读取/构建 配置。xml和API的区别在于，API的方式直接通过Configuration类创建DefaultSqlSessionFactory类，而XML的方式是先读取XML文件构建Configuration（这项工作在org.apache.ibatis.builder.xml.XMLConfigBuilder#parseConfiguration方法中），再创建DefaultSqlSessionFactory。

配置说明

Mybatis配置说明

插件开发

Mybatis插件

扩展

Mybatis注意点

版权所有

本文链接：/article/yd5dopzz/

许可证：署名-非商业性 4.0 国际 (CC-BY-NC-4.0)