mybatis连接数据库的几种方式

1、通过配置文件的方式
首先进行数据库配置，以Spring Boot为例：

spring.datasource.url=jdbc:oracle:thin:@192.168.1.105:1521/oracle2
spring.datasource.username=ENC(LZDUrI0+Q0XbEhqbow4Ggw==)
spring.datasource.password=ENC(LZDUrI0+Q0XbEhqbow4Ggw==)
spring.datasource.driverClassName=oracle.jdbc.OracleDriver
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.maximum-pool-size=200
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.pool-name=ServiceTaskHikariCP
spring.datasource.hikari.max-lifetime=1200000
spring.datasource.hikari.connection-timeout=30000
# mybatis-plus config
mybatis-plus.mapper-locations=classpath:/mapper/*Mapper.xml
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
# 主键类型 0:"数据库ID自增", 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID";
mybatis-plus.global-config.id-type=3
# 字段策略 0:"忽略判断",1:"非 NULL 判断",2:"非空判断"
# mybatis-plus.global-config.field-strategy=2
# 驼峰下划线转换
mybatis-plus.global-config.db-column-underline=true
# global table prefix
#mybatis-plus.global-config.table-prefix=t_
mybatis-plus.configuration.jdbc-type-for-null=null
mybatis-plus.type-aliases-package=com.bw.note.entity
# 逻辑删除配置
mybatis-plus.global-config.sql-injector=com.baomidou.mybatisplus.mapper.LogicSqlInjector
mybatis-plus.global-config.logic-delete-value=1
mybatis-plus.global-config.logic-not-delete-value=0
# 返回map为空的字段
mybatis-plus.configuration.call-setters-on-nulls=true

示例如下

<?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.bw.note.mapper.UserMapper">
    <select id="selectByNameAndId" resultType="java.util.HashMap">
        select ID AS "id",AD_NAME as "name",ad_index as "adIndex" from USER_AD_MGR
    </select>
</mapper>

如果接收参数为实体，可以通过创建resultMap来接收，其中column对应数据库中的字段，property对应实体中的字段名称.

<?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.bw.note.mapper.UserMapper">
	<resultMap id="QualityMap" type="com.bw.note.entity.po.Quality">
        <result column="id" property="id"/>
        <result column="status" property="status"/>
        <result column="region_id" property="regionId"/>
        <result column="user_name" property="userName"/>
        <result column="password" property="password"/>
        <result column="note" property="note"/>
        <result column="deal_user" property="dealUser"/>
        <result column="deal_time" property="dealTime"/>
    </resultMap>
    <select id="selectByRegionId" resultMap="QualityMap">
        select t.* from SYS_QUALITY t where t.region_id = #{id}
    </select>
</mapper>

2、通过注解的方式

@Select("select count(1) from SYS_USER where user_name = #{userName} and is_super = 1")
Integer selectCountSuperAdmin(@Param("userName") String userName);

（1）、当sql语句涉及sql拼接，例如if判断的时候，可以引用script标签:

@Select({"<script>" +
            "select t.name,sum(t.total) total from operationindex t where 1=1 " +
            "<if test='startDate != null and startDate != \"\" '> " +
            " and t.create_time &gt;= #{startDate}" +
            "</if>" +
            "<if test='endDate != null and endDate != \"\" '> " +
            " and t.create_time &lt;= #{endDate}" +
            "</if>" +
            " group by t.name" +
            "</script>"})
    List<OperationIndex> getOperationIndex(String startDate, String endDate);

（2）、接收对象为实体

@Select("select * from tab_comments limit #{limit}")
@Results({
        @Result(property = "tableName",column = "table_name"),
        @Result(property = "tableType",column = "table_type"),
        @Result(property = "comments",column = "comments")
})
Cursor<Comments> scan(@Param("limit") int limit);

（3）、使用Provider进行sql的拼接

public class OrderProvider {
    private final String TBL_ORDER = "tbl_order";

    public String queryOrderByParam(OrderPara param) {
        SQL sql = new SQL().SELECT("*").FROM(TBL_ORDER);
        String room = param.getRoom();
        if (StringUtils.hasText(room)) {
            sql.WHERE("room LIKE #{room}");
        }
        Date myDate = param.getMyDate();
        if (myDate != null) {
            sql.WHERE("mydate LIKE #{mydate}");
        }
        return sql.toString();
    }
}

public interface OrderDAO {
    
    @SelectProvider(type = OrderProvider.class, method = "queryOrderByParam")
    List<Order> queryOrderByParam(OrderParam param);

}