IBatis Date type loss of seconds (loss of precision)

In the previous, encountered the use of ibatis operation of Oracle time accuracy lost, and yesterday encountered the JDBC Operation MySQL Time field problem, from the Internet to see the various styles of interpretation of these issues of the blog/post, but mostly mirrors, to no avail. Understand the time type in JDBC type mapping between MySQL and JDBC type mapping between Oracle and JDBC Ibatis is how to handle a date-time type annotation reference to understand the time type in JDBC

The java.sql package includes three classes, date, time, and Timestamp, which are used to represent dates (no time information, EG:YYYY-MM-DD), times (processing time, no date part, Eg:HH:MM:SS), and timestamp (accurate to nanosecond level). In them all inherit from Java.util.Date. Both Java.sql.Date and java.sql.Time have a time domain that is accurate to the second level, but they only process the date or time portion. As you can see in the implementation of MySQL connector/j (v5.1.13), the time is re-formatted as "YYYY-MM-DD" in Preparestatement#setdate.

Because of the legacy of history and the variety of databases themselves, various JDBC implementations have left behind a variety of tricks, and some special scenarios may work without this condition, but this is not recommended.

You have to choose different types according to your actual needs, usually using the same or higher JDBC type than the corresponding database field type .

In addition, you can use the Java.uitl.Date type to process time. The Java.util.Date type is the parent type of each of the above types, and the JDBC API is mostly available.

In addition, the construction of the java.sql prior to the JDK1.6 version. {date| time| TimeStamp} object has a performance problem, especially in a multithreaded environment. This bug is here. The reason for this is that the Java.util.Date method calls the Timezone.getdefaultref (), and this method is synchronous method Note 1. JDBC Type mapping for MySQL

DATE	Java.sql.Date
Datetime	Java.sql.Timestamp
timestamp[(M)]	Java.sql.Timestamp
Time	Java.sql.Time
year[(2|4)]	If Yearisdatetype Configuration property was set to false and then The returned object type is Java.sql.Short. If set to True (the Default) Then a object of type Java.sql.Date (with the Date Set to January 1st, at midnight).

The notable difference between MySQL's datetime and timestamp two field types is that the timestamp value is between ' 1970-01-01 00:00:01′UTC and ' 2038-01-19 03:14:07′UTC.

MySQL also has a problem with time processing, when the value of a field of type DateTimes is 0000-00-00, the value method gets the following exception:

Cannot convert value ' 0000-00-00 00:00:00 ' from

 column xx to TIMESTAMP

The reason for this problem is that MySQL uses the default of 0000-00-00 to represent the special value of time (see document). In Java, there is no appropriate way to represent this time (because 0 on the timeline in Java is 1970-01-01 00:00:00), which is indicated by a negative number earlier than this time, and the smallest negative number is not represented on the timeline. CONNECTOR/J provides a property zerodatetimebehavior to resolve this issue. Exception (the default), which throws an SQLException with an SQLState of S1009. (The defaults behavior) Converttonull, which returns NULL I Nstead of the date. Round, which rounds the date to the nearest closest value which was 0001-01-01.

The JDBC connection as shown below specifies that the behavior is converted to a null value.

JDBC:

mysql:

//localhost/mydatabase?zerodatetimebehavior=converttonull

type mapping between Oracle and JDBC

DATE	Java.sql.Date
DATE	Java.sql.Time
TIMESTAMP	Java.sql.Timestamp

Oracle database field types are primarily date, TIMESTAMP.

There is a bug in the Oracle JDBC driver for the Date Type field after 9i and 11g, because its JDBC driver processes the date type of Oracle to the Java.sql.Date type. This loses the time component (it seems that the problem of three types of time type under the java.sql package is still common). On this issue, this post gives a good explanation (outside the wall), the method in this article applies to the previous versions of 11g JDBC driver. This issue has been resolved in the 11g JDBC driver, and explanation Note 2 is also given in the Oracle 11g JDBC driver's manual.

In fact, if the type of the Ibatis,pojo property is set to Java.util.Date, ensure that jdbctype is not Date or time, this bug is avoided. Because Ibatis will process the field with Java.sql.Timestamp at this point. I have specifically done this verification, click here to see the test project source code. How the Ibatis handles the date type

Note: This article is based on Ibatis 2.3.4.726 source code analysis. However, according to my initial observation, IBATIS3 is also applicable, but please be aware of problems encountered.

In previous jobs, the Date field in Oracle was left with only the data for the dates, and Google found that some people were resolved to designate Jdbctype as DateTime. Someone even wrote a custom Typehandler to solve the problem. In fact, this is completely blind cat hit Dead Mouse, that datetime is meaningless, but fluke. The general error is the following configuration (or the Pojo property is the Java.sql.Date type): XML code<sqlmapNamespace= "Info"><resultmapId= "Info" class= "Pojo. Info "><resultColumn= "Info_begintime" property= "Begin" Jdbctype= "DATE"/><resultColumn= "Info_endtime" property= "End" jdbctype= "DATE"/> </resultmapXML code <span style= "Font-size:medium;" ><sqlmap namespace= "Info" > <resultmap id= "info" class= "Pojo". Info "> <result column=" info_begintime "property=" Begin "Jdbctype=" DATE "/> <result column=" info_e Ndtime "property=" End "jdbctype=" DATE "/> </resultMap</span>

At this point, whether you pojo. The type of the field in info (or the Javatype property) is Java.util.Date or java.sql.Date, and eventually the data will be lost. In fact, in Pojo. When the field type in info (or the Javatype property) is Java.util.Date, if you specify Jdbctype as Date or time (case sensitive), you will get Dateonlytypehandler or Timeonlytypehandler. If you do not specify Jdbctype, or specify an incorrect value, such as DateTime, or XXXX, you will get Datetypehandler (DateTime will be processed).

If Pojo. The attribute type in info (or the Javatype property in the configuration) is java.sql.Date, and when you choose to process the handler class, you do not use Jdbctype, but you get sqldatetypehandler based on the property type (containing only the date).

In Ibatis's manual, it is pointed out that jdbctype generally is not used to determine the processing method of note 3. In most cases, the processing is judged on the basis of the Javatype or Pojo attribute type, which can only be used if the few parts cannot be judged based on the Pojo attribute type. If you use Java.util.Date to correspond to MySQL, this is one of the few cases. Because MySQL can map multiple database field types to java.util.Date, you need to specify whether it is date or time (must be uppercase), and if not specified, the default is the full datetime (timestamp action by JDBC).

I wrote a test to see the test code for Ibatis's handling of time.

For the ibatis operation Date/time/datetime, summarize the following: Set the Pojo property type to Java.sql.Date (or Java.sql.Time, Java.sql.Timestamp), these three types of semantics are strictly adhered to in this case. However, because of the performance issues mentioned earlier in this approach, it is less used in previous JDK versions of JDK1.6. If you want to use Java.util.Date in Pojo, be aware that the full datetime, to ensure that the jdbctype is empty, or that a value other than date,time only takes time, to specify that the jdbctype= "times" only need a date, to specify Jdbctype = "DATE"

Add: When I follow the changes in this article, I find that this problem still exists, after carefully draining the fork, I found that when I save the data, the parameter type is java.sql.Date I looked at my insert fragment statement as: XML code     <isnotnull  prepend= ","  property= "Record.signdate" &NBSP; ;              <! [cdata[ sign_date =  #record .signdate:date# ]]>   </isNotNull>    XML code    <