MyBatis handles MySQL field types date and datetime
1) DATETIME Display format: yyyy-MM-dd HH:mm:ss Time range: [ '1000-01-01 00:00:00' to '9999-12-31 23:59:59'] 2) DATE Display format: yyyy-MM-dd Time range: ['1000-01-01' to '9999-12-31'] 3) TIMESTAMP Display format: yyyy-MM-dd HH:mm:ss Time range: [ '1970-01-01 00:00:00' to '2037-12-31 23:59:59']
copy
One, MyBatis has two types of jdbcType for processing dates:
1. jdbcType=DATE 2. jdbcType=TIMESTAMP
Second, there is only one date type in JAVA DateTime
When we use java.util.Date as the date type of the entity (JAVA does not have the DateTime class, the Date class can represent date and time at the same time), java.util.Date can actually represent three field types of mysql:
1. date 2. datetime 3. timestamp
Third, MyBatis processing method
When actually passing java.util.Date as a parameter to Mapper 1. If we do not specify jdbcType, then this date will be automatically converted to MySQL timestamp. The example is as follows: 2. The result of specifying jdbcType=TIMESTAMP is the same as above. 3. Specify jdbcType=DATE, then MyBatis will intercept the incoming parameters as 2018-07-24 (Date)
4. Summary
When using java.util.Date as a parameter to pass to Mapper, MyBatis can automatically perform type conversion regardless of whether the date field type of MySQL is date, datetime or timestamp, and you can directly use =, >, <, > =, <= symbols to filter. The only difference is that when jdbcType=DATE is specified, MyBatis will automatically intercept the time. If the date field type of MySQL is datetime or timestamp, it must not be written in this way. In short, there must be no problem with jdbcType that does not manually specify parameters
MyBatis commonly used jdbcType data type
1 The jdbcType type included by MyBatis
BIT FLOAT CHAR TIMESTAMP OTHER UNDEFINED TINYINT REAL VARCHAR BINARY BLOB NVARCHAR SMALLINT DOUBLE LONG VARCHAR VARBINARY CLOB NCHAR INTEGER NUMERIC DATE LONGVARBINARY BOOLEAN NCLOB BIGINT DECIMAL TIME NULL CURSOR
copy
2 Mybatis javaType and jdbcType correspondence and CRUD example
<resultMap type="java.util.Map" id="resultjcm"> <result property="FLD_NUMBER" column="FLD_NUMBER" javaType="double" jdbcType="NUMERIC"/> <result property="FLD_VARCHAR" column="FLD_VARCHAR" javaType="string" jdbcType="VARCHAR"/> <result property="FLD_DATE" column="FLD_DATE" javaType="java.sql.Date" jdbcType="DATE"/> <result property="FLD_INTEGER" column="FLD_INTEGER" javaType="int" jdbcType="INTEGER"/> <result property="FLD_DOUBLE" column="FLD_DOUBLE" javaType="double" jdbcType="DOUBLE"/> <result property="FLD_LONG" column="FLD_LONG" javaType="long" jdbcType="INTEGER"/> <result property="FLD_CHAR" column="FLD_CHAR" javaType="string" jdbcType="CHAR"/> <result property="FLD_BLOB" column="FLD_BLOB" javaType="[B" jdbcType="BLOB" /> <result property="FLD_CLOB" column="FLD_CLOB" javaType="string" jdbcType="CLOB"/> <result property="FLD_FLOAT" column="FLD_FLOAT" javaType="float" jdbcType="FLOAT"/> <result property="FLD_TIMESTAMP" column="FLD_TIMESTAMP" javaType="java.sql.Timestamp" jdbcType="TIMESTAMP"/> </resultMap>
copy
3 The correspondence between javaType and jdbcType in Mybatis
JDBC Type Java Type CHAR String VARCHAR String LONGVARCHAR String NUMERIC java.math.BigDecimal DECIMALjava.math.BigDecimal BIT boolean BOOLEAN boolean TINYINT byte SMALLINT short INTEGER int BIGINT long REAL float FLOAT double DOUBLE double BINARY byte[] VARBINARY byte[] LONGVARBINARY byte[] DATEjava.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[color=red][/color]
copy
4 oracle data type and corresponding java type
After using mybatis generator to generate the code, when the query statement is executed, the Date type field in oracle is only accurate to the year, month and day, and the subsequent hours, minutes and seconds are all zero. When jdbcType=”DATE”, the fields stored in the database are only year, month, and day! Later, it was found that it was a jdbcType problem, just change it to jdbcType=”TIMESTAMP”. (The original default was jdbcType=”DATE”)
|
SQL data type |
JDBC type code |
standard Java types |
Oracle Extended Java Types |
|---|---|---|---|
|
– |
1.0 standard JDBC types: |
– |
– |
|
CHAR |
java.sql.Types.CHAR |
java.lang.String |
oracle.sql.CHAR |
|
VARCHAR2 |
java.sql.Types.VARCHAR |
java.lang.String |
oracle.sql.CHAR |
|
long |
java.sql.Types.LONGVARCHAR |
java.lang.String |
oracle.sql.CHAR |
|
NUMBER |
java.sql.Types.NUMERIC |
java.math.BigDecimal |
oracle.sql.NUMBER |
|
NUMBER |
java.sql.Types.DECIMAL |
java.math.BigDecimal |
oracle.sql.NUMBER |
|
NUMBER |
java.sql.Types.BIT |
boolean |
oracle.sql.NUMBER |
|
NUMBER |
java.sql.Types.TINYINT |
byte |
oracle.sql.NUMBER |
|
NUMBER |
java.sql.Types.SMALLINT |
short |
oracle.sql.NUMBER |
|
NUMBER |
java.sql.Types.INTEGER |
int |
oracle.sql.NUMBER |
|
NUMBER |
java.sql.Types.BIGINT |
long |
oracle.sql.NUMBER |
|
NUMBER |
java.sql.Types.REAL |
float |
oracle.sql.NUMBER |
|
NUMBER |
java.sql.Types.FLOAT |
double |
oracle.sql.NUMBER |
|
NUMBER |
java.sql.Types.DOUBLE |
double |
oracle.sql.NUMBER |
|
RAW |
java.sql.Types.BINARY |
byte[] |
oracle.sql.RAW |
|
RAW |
java.sql.Types.VARBINARY |
byte[] |
oracle.sql.RAW |
|
LONGRAW |
java.sql.Types.LONGVARBINARY |
byte[] |
oracle.sql.RAW |
|
DATE |
java.sql.Types.DATE |
java.sql.Date |
oracle.sql.DATE |
|
DATE |
java.sql.Types.TIME |
java.sql.Time |
oracle.sql.DATE |
|
TIMESTAMP |
java.sql.Types.TIMESTAMP |
javal.sql.Timestamp |
oracle.sql.TIMESTAMP |
|
– |
2.0 standard JDBC types: |
– |
|
|
BLOB |
java.sql.Types.BLOB |
java.sql.Blob |
oracle.sql.BLOB |
|
CLOB |
java.sql.Types.CLOB |
java.sql.Clob |
oracle.sql.CLOB |
|
user-defined object |
java.sql.Types.STRUCT |
java.sql.Struct |
oracle.sql.STRUCT |
|
user defined reference |
java.sql.Types.REF |
java.sql.Ref |
oracle.sql.REF |
|
user defined collection |
java.sql.Types.ARRAY |
java.sql.Array |
oracle.sql.ARRAY |
|
– |
Oracle extension: |
– |
– |
|
BFILE |
oracle.jdbc.OracleTypes.BFILE |
N/A |
oracle.sql.BFILE |
|
ROWID |
oracle.jdbc.OracleTypes.ROWID |
N/A |
oracle.sql.ROWID |
|
REF CURSOR |
oracle.jdbc.OracleTypes.CURSOR |
java.sql.ResultSet |
oracle.jdbc.OracleResultSet |
|
TIMESTAMP |
oracle.jdbc.OracleTypes.TIMESTAMP |
java.sql.Timestamp |
oracle.sql.TIMESTAMP |
|
TIMESTAMP WITH TIME ZONE |
oracle.jdbc.OracleTypes.TIMESTAMPTZ |
java.sql.Timestamp |
oracle.sql.TIMESTAMPTZ |
|
TIMESTAMP WITH LOCAL TIME ZONE |
oracle.jdbc.OracleTypes.TIMESTAMPLTZ |
java.sql.Timestamp |
oracle.sql.TIMESTAMPLTZ |
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. Java skill treeHomepageOverview 119858 people are studying systematically