MyBatis-plus小课堂：@Select的参数传递

引言

• @Select的参数传递
• wrapper自定义sql： 使用条件构造器作为参数

I 预备知识

1.1 JDBC

Java Database Connectivity)：一种用于执行 SQL 语句的 Java API，它由一组用 Java 编程语言编写的类和接口组成，JDBC 可做三件事：

• 与数据库建立连接，
• 发送 SQL 语句，
• 处理结果。

MyBatis和JDBC最显著的区别是SQL语句配置化，通过xml文件定义SQL语句。

MyBatis is a first class persistence framework with support for custom SQL, stored procedures and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and retrieval of results. MyBatis can use simple XML or Annotations for configuration and map primitives, Map interfaces and Java POJOs (Plain Old Java Objects) to database records.

1.2 MyBatis的xml配置文件可用自己定义的数据类型

@Select(“select * from Type where id = #{id, jdbcType=BIGINT} and code= #{code, jdbcType=VARCHAR}”)
Type selectTypeById(@Param(“id”) Long id, @Param(“code”) String code);

Associated JDBC type can be specified by two means:

• Adding a jdbcType attribute to the typeHandler element (for example: jdbcType=“VARCHAR”).

<select id='getMeetingnoByCompanyid' parameterType="java.lang.Integer"
        resultType="java.lang.String">
        select a.meetingno
        from xxx a
        where a.companyid = #{companyid, jdbcType=BIGINT}
</select>

• Adding a @MappedJdbcTypes annotation to your TypeHandler class specifying the list of JDBC types to associate it with. This annotation will be ignored if the jdbcType attribute as also been specified.

1.3 MyBatis JdbcType 与Oracle、MySql数据类型对应关系

Mybatis	JdbcType	Oracle	MySql
JdbcType	ARRAY
JdbcType	BIGINT		BIGINT
JdbcType	BINARY
JdbcType	BIT		BIT
JdbcType	BLOB	BLOB	TEXT
JdbcType	BOOLEAN
JdbcType	CHAR	CHAR	CHAR
JdbcType	CLOB	CLOB	CLOB–>修改为TEXT
JdbcType	CURSOR
JdbcType	DATE	DATE	DATE
JdbcType	DECIMAL	DECIMAL	DECIMAL
JdbcType	DOUBLE	NUMBER	DOUBLE
JdbcType	FLOAT	FLOAT	FLOAT
JdbcType	INTEGER	INTEGER	INTEGER
JdbcType	LONGVARBINARY
JdbcType	LONGVARCHAR	LONG VARCHAR
JdbcType	NCHAR	NCHAR
JdbcType	NCLOB	NCLOB
JdbcType	NULL
JdbcType	NUMERIC	NUMERIC/NUMBER	NUMERIC/
JdbcType	NVARCHAR
JdbcType	OTHER
JdbcType	REAL	REAL	REAL
JdbcType	SMALLINT	SMALLINT	SMALLINT
JdbcType	STRUCT
JdbcType	TIME		TIME
JdbcType	TIMESTAMP	TIMESTAMP	TIMESTAMP/DATETIME
JdbcType	TINYINT		TINYINT
JdbcType	UNDEFINED
JdbcType	VARBINARY
JdbcType	VARCHAR	VARCHAR	VARCHAR

1.4 Mybatis JdbcType

www.mybatis.org/mybatis-3/a…

public enum JdbcType {
    ARRAY(2003),
    BIT(-7),
    TINYINT(-6),
    SMALLINT(5),
    INTEGER(4),
    BIGINT(-5),
    FLOAT(6),
    REAL(7),
    DOUBLE(8),
    NUMERIC(2),
    DECIMAL(3),
    CHAR(1),
    VARCHAR(12),
    LONGVARCHAR(-1),
    DATE(91),
    TIME(92),
    TIMESTAMP(93),
    BINARY(-2),
    VARBINARY(-3),
    LONGVARBINARY(-4),
    NULL(0),
    OTHER(1111),
    BLOB(2004),
    CLOB(2005),
    BOOLEAN(16),
    CURSOR(-10),
    UNDEFINED(-2147482648),
    NVARCHAR(-9),
    NCHAR(-15),
    NCLOB(2011),
    STRUCT(2002),
    JAVA_OBJECT(2000),
    DISTINCT(2001),
    REF(2006),
    DATALINK(70),
    ROWID(-8),
    LONGNVARCHAR(-16),
    SQLXML(2009),
    DATETIMEOFFSET(-155),
    TIME_WITH_TIMEZONE(2013),
    TIMESTAMP_WITH_TIMEZONE(2014);

    public final int typeCode;
    private static final Map<Integer, JdbcType> CODE_MAP = new ConcurrentHashMap(100, 1.0F);

    private JdbcType(int code) {
        this.typeCode = code;
    }

    public static JdbcType valueOf(int code) {
        return (JdbcType)CODE_MAP.get(code);
    }

    static {
        JdbcType[] var0 = values();
        int var1 = var0.length;

        for(int var2 = 0; var2 < var1; ++var2) {
            JdbcType type = var0[var2];
            CODE_MAP.put(type.typeCode, type);
        }

    }
}

II @Select的参数传递

2.1 普通类型传递

案例1

@Select(“select * from Type where id = #{id, jdbcType=BIGINT} and code= #{code, jdbcType=VARCHAR}”)
Type selectTypeById(@Param(“id”) Long id, @Param(“code”) String code);

案例2

@Select("select an.* from sys_announcement an  ${ew.customSqlSegment} and  not in (select   from sys_announcement a  inner join sys_announcement_read r on r.announcement_id= where r.user_id = #{user_id,jdbcType=BIGINT})  order by an.create_time desc")
    List<SysAnnouncement> listUnRead(@Param(Constants.WRAPPER) LambdaQueryWrapper<SysAnnouncement> lambda,@Param("user_id") Long userId);

使用

List<SysAnnouncement> listUnRead(@Param(Constants.WRAPPER) LambdaQueryWrapper<SysAnnouncement> lambda,@Param("user_id") Long userId);

2.2 使用条件构造器作为参数

1. mapper.java/Service.java定义接口方法
2. 添加 @Param(Constants.WRAPPER)形参和${ew.customSqlSegment}值参

${ew.customSqlSegment}值参 以where关键字开头，@Select语句如果有其他查询条件，必须放在${ew.customSqlSegment}之后。

@Select("select  a.* from sys_announcement a  inner join t_sys_announcement_read r on r.announcement_id=  ${ew.customSqlSegment} order by a.create_time desc")
    List<SysAnnouncement> listRead(@Param(Constants.WRAPPER) LambdaQueryWrapper<SysAnnouncement> lambda);

使用

LambdaQueryWrapper<SysAnnouncement> lambda = new LambdaQueryWrapper<>();
        lambda.apply(input.getSendChannel() != null, "an.send_channel like {0}", "%"+input.getSendChannel()+"%");
        lambda.apply(input.getStartTime() != null, "an.create_time > {0}", input.getStartTime());
        lambda.apply(input.getEndTime() != null, "an.create_time > {0}", input.getEndTime());
        lambda.apply(input.getState() != null, "an.state = {0}", input.getState());
                list = tSysAnnouncementService.listUnRead(lambda, LoginHelper.getUserId());

2.3 案例

mybatis-plus小课堂：多表查询【案例篇】（apply 拼接 in SQL,来查询从表某个范围内的数据）