I have been using the mybatis framework for almost four years, and I have always had a question I would like to ask.
As we all know, the DATE type data in the oracle database is available in two formats, such as '2019-07-31 10:49:43' and '2019-07-31'.
When the field in the database is of the type '2019-07-31 10:49:43', my usual practice is to use the java.util.Date class as the field type in the Entity class. When using directly in the mapper xml' #{param,jdbcType=DATE}' Assignment will cause part of the time to be lost, the data will become '2019-07-31', only use '#{param,jdbcType=TIMESTAMP}' to guarantee the hour, minute and second part Not lost. (I think this is an unreasonable solution, the reasons are pointed out below)
In oracle, if you pass string data to a field of type NUMBER, it will cause oracle to cast, affecting the efficiency of sql execution. Similarly, if you pass TIMESTAMP type data to a field that needs to store DATE type, it will also cause Cast.
///////////////////////////////////////////////////////////////////////////////////////////////////////////// Fake code:
Entity class: import java.util.Date;
public class ExampleEntity {
private Date param;
get();set();
}
Mapper interface:
public interface ExampleMapper {
// select statement:select sysdate from dual
ExampleEntity getDate();
// insert statement:insert into test id,date values (1,#{param,jdbcType=TIMESTAMP})
// If the above code is modified to 'insert into test id, date values (1, #{param, jdbcType=DATE})'
// it will cause partial loss of time, minute and second.
void transferDate(Date param);
}
mapper xml:
{param,jdbcType=DATE} --- The extracted data will be lost in minutes and seconds.
{param,jdbcType=TIMESTAMP} --- The fetched data can retain the time, minute, and second fields, but will cause the oracle to cast the data type.
thanks.
Comment From: harawata
Hi @anpu2018 ,
the DATE type data in the oracle database is available in two formats, such as '2019-07-31 10:49:43' and '2019-07-31'.
Oracle's DATE type always has time part even though it is not printed in some cases.
if you pass TIMESTAMP type data to a field that needs to store DATE type, it will also cause Cast.
Do you have a proof of that theory?
It seems unlikely that there is a big difference between Date and Timestamp.
Comment From: killersteps
Hi @anpu2018 ,
the DATE type data in the oracle database is available in two formats, such as '2019-07-31 10:49:43' and '2019-07-31'.
Oracle's DATE type always has time part even though it is not printed in some cases.
if you pass TIMESTAMP type data to a field that needs to store DATE type, it will also cause Cast.
Do you have a proof of that theory? It seems unlikely that there is a big difference between
DateandTimestamp.
In fact, when I call the 'transferDate' mapper to execute the inserted SQL statement, the choice of 'jdbcType="DATE"' and 'jdbcType="TIMESTAMP"' causes the difference in the data actually written.
When 'jdbcType="DATE"' is selected, the actual written data will lose the time portion, but it will not be lost when 'jdbcType="TIMESTAMP"' is selected.
But the consequence of choosing 'jdbcType="TIMESTAMP"' is that oracle will cause a cast (this is tested by our company's professional dba), because the passed parameter is of type 'TIMESTAMP', but the field in oracle is ' Date' type
For example, the data queried is '2019-08-07 17:30:30'. If you select 'jdbcType="DATE"', the data written by the actual call insert statement will become '2019-08-07 00:00:00', only select 'jdbcType="TIMESTAMP"' will make the data of the call insert statement become '2019-08-07 17:30:30' (but this will cause oracle to cast, affecting efficiency)
Comment From: harawata
When 'jdbcType="DATE"' is selected, the actual written data will lose the time portion, but it will not be lost when 'jdbcType="TIMESTAMP"' is selected.
This behavior looks reasonable to me.
And even if there is a cast/efficiency issue when specifying jdbcType=TIMESTAMP, there is nothing MyBatis can do, I am afraid.
If you believe there is room for improvement, you should report it to Oracle.
p.s.
You might want to try changing the field type to java.time.LocalDateTime and see if it helps with the performance if you haven't.
Comment From: killersteps
When 'jdbcType="DATE"' is selected, the actual written data will lose the time portion, but it will not be lost when 'jdbcType="TIMESTAMP"' is selected.
This behavior looks reasonable to me.
And even if there is a cast/efficiency issue when specifying
jdbcType=TIMESTAMP, there is nothing MyBatis can do, I am afraid. If you believe there is room for improvement, you should report it to Oracle.p.s. You might want to try changing the field type to
java.time.LocalDateTimeand see if it helps with the performance if you haven't.
Can I think this way: If you want to keep the time part in the insert statement, you must use the 'jdbcType="TIMESTAMP"' tag, and mybatis does not care about the oracle efficiency problem here.
Comment From: harawata
You can omit jdbcType, actually, but that would be correct if you use java.util.Date.
As you might be aware, the DATE type in JDBC does not have time part by definition and the corresponding java.sql.Date also does not have time [1].
So, by specifying jdbcType="DATE", you explicitly declare that you don't need the time part.
[1] https://docs.oracle.com/javase/8/docs/api/java/sql/Date.html
To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.
Comment From: killersteps
You can omit
jdbcType, actually, but that would be correct if you usejava.util.Date.As you might be aware, the
DATEtype in JDBC does not have time part by definition and the correspondingjava.sql.Datealso does not have time [1]. So, by specifyingjdbcType="DATE", you explicitly declare that you don't need the time part.[1] https://docs.oracle.com/javase/8/docs/api/java/sql/Date.html
To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.
First of all, thank you very much for your patience. All in all, the java.util.Date class is too old, I try to use LocalDateTime to solve it. Is the LocalDateTime and mybatis framework compatible in the springboot development environment?
Comment From: kazuki43zoo
mybatis framework compatible in the springboot development environment?
Yes! you can use the LocalDateTime in Spring Boot application.
The MyBatis supports JSR-310 types since 3.4.5 and the JDBC driver that have compatibility with JDBC 4.2 is required since 3.5.0. Please confirm a JDBC version that use on your application.
Related Issues
-
974
-
1081
-
1478
Comment From: liuchu
{param,jdbcType=TIMESTAMP} --- The fetched data can retain the time, minute, and second fields, but will cause the oracle to cast the data type.
@anpu2018 Supplementing my experience on this.
Acutally cast only happened when param(Java Date type) has millisecond. Examples:
Let's say param is a java.util.Java type, and column is a Oracle Date type column.
1. Has millisecond, cast happened
Java code: Date param = new Date(); // has millisecond
Mybatis sql statement: where t.column = #{param,jdbcType=TIMESTAMP}
Oracle datatype: TimeStamp
Oracle Predicate information: filter((INTERNAL_FUNCTION("t"."column") = :1))
- No millisecond,
castwon't happen Java code:Date param = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("2020-08-11 09:11:03"); // no millisecondMybatis sql statement:where t.column = #{param,jdbcType=TIMESTAMP}Oracle datatype:TimeStampOracle Predicate information:filter(("t"."column" = :1))
Notice the Oracle Predicate information diffrence, example 1 has INTERNAL_FUNCTION on column, which would cause sql execution plain issue.
Coclusion: Ensure param has no millisecond, jdbcType=TIMESTAMP works fine on Oracle Date column