Shulou(Shulou.com)06/02 Report--

This article mainly introduces how to determine the type of Mybatis parameters, the article is very detailed, has a certain reference value, interested friends must read it!

i. Environment configuration

We use SpringBoot + Mybatis + MySql to build the instance demo

Springboot: 2.2.0.RELEASE

Mysql: 5.7.22

1. Project configuration

Org.mybatis.spring.boot mybatis-spring-boot-starter 2.2.0 mysql mysql-connector-java

The core dependency is mybatis-spring-boot-starter. As for version selection, go to the mvn repository and find the latest

Another thing that is not available is db configuration information, appliaction.yml

Spring: datasource: url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai username: root password:2. Database table

Database for testing

CREATE TABLE `money` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar (20) NOT NULL DEFAULT''COMMENT' username', `money`int (26) NOT NULL DEFAULT'0' COMMENT 'money', `is_ deleted`tinyint (1) NOT NULL DEFAULT '0percent, `create_ at`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT' creation time, `update_ at`update time', PRIMARY KEY (`id`), KEY `name` (`name`) ENGINE=InnoDB AUTO_INCREMENT=551 DEFAULT CHARSET=utf8mb4

Test data, mainly the name field, a string whose value is a number

INSERT INTO `money` (`id`, `name`, `money`, `is_ deleted`, `create_ at`, `money`) VALUES (120,120,200,0, '2021-05-24 2020 create_ 041440'); II. Determination of parameter transfer type

This article ignores the details of po, mapper interface and xml file in mybatis. Interested partners can directly view the bottom source code (or check previous blog posts).

1. Parameter type is shaping

For the above case, define an interface to query data according to name, but the name parameter type is an integer

Mapper interface:

/ * int type, the parameter in the final sql is also replaced by int * @ param name * @ return * / List queryByName (@ Param ("name") Integer name)

The corresponding xml file is as follows

Select * from money where `name` = # {name}

The above way of writing is very common, and our problem now is that if the parameter is passed as an integer, is the final sql name = 120 or name = '120'?

So how do you determine what the resulting sql looks like? Here is a way to directly output mysql to execute sql logs.

Open the sql execution log by executing the following two commands on the mysql server

Set global general_log = "ON"; show variables like 'general_log%'

When we visit the above interface, we will find that in the sql statement finally sent to mysql, the parameter replacement is still an integer.

Select * from money where `name` = 1202. Specify jdbcType

When using # {}, ${}, we sometimes see that jdbcType is specified in addition to parameters, so will it affect the final sql generation if we specify this in xml?

Select * from money where `name` = # {name, jdbcType=VARCHAR} and 0name0

The generated sql is as follows

Select * from money where `name` = 120 and 0

From the actual sql point of view, this jdbcType does not affect the final sql parameter splicing, so what is it mainly used for? (it mainly applies to exceptions that may occur in type conversion when null is passed in.)

3. Parameter passing type is String

When the parameter type is string, the final sql reasoning should be in quotation marks

/ * if the parameter type passed in is string, it will be automatically marked with''* @ param name * @ return * / List queryByNameV3 (@ Param ("name") String name)

Corresponding xml

Select * from money where `name` = # {name, jdbcType=VARCHAR} and 1

The final sql generated from the above is as follows

Select * from money where `name` = '120' and 1

4. TypeHandler implements parameter substitution and forcibly adds quotation marks

After reading the above sections, you can basically draw a simple inference (of course, whether it is right or not needs to be analyzed from the source code).

Sql parameter replacement is not simply replaced by a string, but is actually determined by the parameter type of the parameter java. If the java parameter type is a string, the spliced sql is in a string format. If the parameter is passed as an integer, the spliced sql is also an integer.

So the question is, why do you need to know this?

The key point is the problem of index failure.

For example, an index is added to the name in the example in this article. If our sql is select * from money where name = 120, we will not be able to use the index. If we want to do so, the passed parameter must be a string, and there can be no implicit type conversion.

Based on this, we have an application scenario. In order to avoid indexing due to the problem of passing parameters, we want the passing parameters of name. No matter what the actual parameter type is, the final concatenated sql is in the format of a string.

We use custom TypeHandler to implement this scenario.

@ MappedTypes (value = {Long.class, Integer.class}) @ MappedJdbcTypes (value = {JdbcType.CHAR, JdbcType.VARCHAR, JdbcType.LONGVARCHAR}) public class StrTypeHandler extends BaseTypeHandler {/ * java type to jdbc type * * @ param ps * @ param I * @ param jdbcType * @ throws SQLException * / @ Override public void setNonNullParameter (PreparedStatement ps, int I, Object parameter JdbcType jdbcType) throws SQLException {ps.setString (I, String.valueOf (parameter)) } / * jdbc type to java type * * @ param rs * @ param columnName * @ throws SQLException * / @ Override public Object getNullableResult (ResultSet rs, String columnName) throws SQLException {return rs.getString (columnName);} @ Override public Object getNullableResult (ResultSet rs, int columnIndex) throws SQLException {return rs.getString (columnIndex) @ Override public Object getNullableResult (CallableStatement cs, int columnIndex) throws SQLException {return cs.getString (columnIndex);}}

Then in xml, specify TypeHandler

/ * * the conversion of java jdbc types is realized through custom TypeHandler, so that int/long is immediately passed in, and it will also be transferred to String * @ param name * @ return * / List queryByNameV4 (@ Param ("name") Integer name); select * from money where `name` = # {name, jdbcType=VARCHAR, typeHandler=com.git.hui.boot.mybatis.handler.StrTypeHandler} and 2

The sql output by the above method will be enclosed in single quotation marks, which can solve the problem that the parameter transfer type is wrong from the source, resulting in the final failure of the index.

Select * from money where `name` = '120' and 2 and above are all the contents of this article entitled "how to determine the Type of Mybatis parameters". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.