Date,time,timestamp processing in JDBC and ibatis

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.

1. Understanding the type of time in JDBC
2. Type mapping between MySQL and JDBC
3. Type mapping between Oracle and JDBC
4. Ibatis is how to handle date-time types
5. Comments
6. Resources

Understanding the type of time 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.
• Converttonull, which returns NULL instead 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, which loses the date Type field, after 9i, and before 11g. The reason is that its JDBC driver processes the date type of Oracle to the Java.sql.Date type, which loses the time component (which seems to be common for the lack of awareness of the three time types under the java.sql package). 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):

<sqlmap namespace= "Info" >  <resultmap id= "info" class= "Pojo". Info ">    <result column=" info_begintime "property=" Begin "Jdbctype=" DATE "/>    <result column=" info _endtime "property=" End "jdbctype=" DATE "/>  </resultMap>

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. The field type in info ( or Javatype property) is Java.util.Date, if you specify Jdbctype as date or time (case sensitive), you will get Dateonlytypehandler or Timeonlytypehandler respectively. 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 Ibatis Operation Date/time/datetime, the summary is as follows:

• Set the property type of Pojo to Java.sql.Date (or Java.sql.Time, Java.sql.Timestamp), which strictly follows the semantics of these three types. 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:
  • Full datetime, to ensure that jdbctype is empty, or a value other than date,time
  • It only takes time to specify jdbctype=
  • Only need date, to specify jdbctype= "date"

Comments

Note 1:use Oracle.sql.DATE or Oracle.sql.TIMESTAMP rather than
Java.sql.Date or Java.sql.Timestamp If you are using JDK 1.5 or earlier versions or require maximum performance. Also use the Oracle.sql data type if you want to read many date values or compute or display only a small percenta Ge. Due to a bugs in all versions of Java prior to JDK 1.6, construction of java.lang.Date and Java.lang.Timestamp objects are S Low, especially in multithreaded environments. This bug was fixed in JDK 1.6.

Note 2:mapping SQL DATE Data type to Java Oracle Database 8i and earlier versions do not support TIMESTAMP Data, but Orac Le DATE data used to has a time component as a extension to the SQL standard. So, Oracle Database 8i and earlier versions of JDBC drivers mapped Oracle.sql.DATE to Java.sql.Timestamp to preserve the T IME component. Starting with Oracle Database 9.0.1, TIMESTAMP support is included and 9i JDBC drivers started mapping Oracle.sql.DATE to Java.sql.Date. This mapping is incorrect as it truncated the time component of Oracle Date data. To overcome this problem, Oracle Database 11.1 introduces a new flag mapdatetotimestamp. The default value of this flag is true, which means-by default the drivers would correctly map Oracle.sql.DATE to Java . sql. Timestamp, retaining the time information. If you still want the incorrect but 10g compatible Oracle.sql.DATE to java.sql.Date mapping and then you can get it by settin G The value of Mapdatetotimestamp flag to false.

Note the explanation of the Jdbctype property given in the Manual of 3:ibatis 2: The property Jdbctype is used to explicitly specify the data type of the database field assigned to this property. For certain operations, if you do not specify a field's data type, some JDBC driver cannot recognize the data type of the field. A good example is the preparedstatement.setnull (int parameterindex, int sqltype) method, which requires specifying the data type. If you do not specify a data type, some driver may be specified as Types.other or types.null. However, there is no guarantee that all driver are consistent. For this scenario, the SQL MAP API allows you to specify the data type using the Jdbctype attribute of the Parametermap child element parameter.
Normally, the Jdbctype property is required only if the field can be null. Another case where you need to specify the Jdbctype property is when the field type is a datetime type. Because Java has only one Date type (java.util.Date), most SQL databases have more than one-typically there are at least 3 of them. Therefore, you need to specify whether the field type is date or DateTime.
The Jdbctype property can be a string value of any parameter defined in the JDBC types class. However, there are some types that are not supported (that is, blobs). A later section of this section describes the data types supported by the schema.
Attention! Most JDBC driver require the Jdbctype property to be specified only if the field can be null. Therefore, for these driver, you only need to specify the Type property if the field can be null.
Attention! When using Oracle driver, if you do not specify the Jdbctype property for nullable fields, the "Invalid column type" error occurs when you attempt to assign a value of NULL to these fields.

Date,time,timestamp processing in JDBC and ibatis