The role of JDBC type in mybatis

Time:2021-5-10

background

See colleagues using JDBC type in code, I am very confused.

knowledge

This is for the security of the program. In some special cases, when the passed in parameter name is empty, the program will not have problems. When the name is empty, mybatis does not know what JDBC type to convert to. In some special cases, an error will be reported. Mybatis often appears: invalid column type: 1111 error, which is caused by not setting jdbctype. In mybatis, it is also explicitly suggested that the jdbctype attribute should be added when mapping field data. This is relatively safe.
The following situation is under the premise of ensuring that the first four items cannot be empty, and when the latter items are empty, the program will not report errors.

`<insert id=”saveRole”>

insert into role_p values (
    #{roleId},
    #{name},
    #{remarks},
    #{orderNo},
    #{createBy,jdbcType=VARCHAR},
    #{createDept,jdbcType=VARCHAR},
    #{createTime,jdbcType=DATE},
    #{updateBy,jdbcType=VARCHAR},
    #{updateTime,jdbcType=DATE}
)

</insert>`
The common type correspondence is as follows:

JDBC Type           Java Type
CHAR                String
VARCHAR             String
LONGVARCHAR         String
NUMERIC             java.math.BigDecimal
DECIMAL             java.math.BigDecimal
BIT                 boolean
BOOLEAN             boolean
TINYINT             byte
SMALLINT            short
INTEGER             INTEGER
BIGINT              long
REAL                float
FLOAT               double
DOUBLE              double
BINARY              byte[]
VARBINARY           byte[]
LONGVARBINARY       byte[]
DATE                java.sql.Date
TIME                java.sql.Time
TIMESTAMP           java.sql.Timestamp
CLOB                Clob
BLOB                Blob
ARRAY               Array
DISTINCT            mapping of underlying type
STRUCT              Struct
REF                 Ref
DATALINK            java.net.URL

The corresponding relationship between jdbctype and Java type (Java. SQL. Date, Java. SQL. Time, Java. SQL, timestamp)
https://blog.csdn.net/fanfan1…

reference resources

http://www.mybatis.cn/archive…
https://www.cnblogs.com/lzjdm…