登录
MyBatis-plus小课堂：@Select的参数传递
2023年4月28日 09:28
我正在参加「掘金·启航计划」
引言
-
@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
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 an.id not in (select a.id from sys_announcement a inner join sys_announcement_read r on r.announcement_id=a.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 使用条件构造器作为参数
-
mapper.java/Service.java定义接口方法 - 添加
@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=a.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,来查询从表某个范围内的数据）kunnan.blog.csdn.net/article/det…
