How to use Java Date in Oracle driver

If you encounter some problems when using Java Date in the Oracle driver, do you want to understand what is confusing, the following articles mainly introduce the actual application solution of the Java Date issue in Oracle version drivers, to solve your problem about using Java Date in Oracle driver.

I have been tuning an SQL statement for two days. This SQL statement is not complex, similar

 
 

  
  
select ... from some_view union all select ... 
from some_table where datetime >= d1 and datetime< d2 and ....  
 
 

Underlying usage

 
 

  
  
ibatis2.1.6 + oracle 10g 
 
 

Today, it took some time to continue studying this problem. The cause of this problem is indeed that "oracle implicitly converted the datetime field, and CBO failed to use the global index of this column ", however, the problem is not ibatis, But Oracle driver. Let's use this SQL statement to query the database by binding variables (type: java. util. date), where end_date is of the date type and an index is created.

 
 

  
  
“select count(*) from table1 where end_date >=
 :1 and end_date <= :2” 
 
 

Generally, in the face of such an SQL statement, we want its execution plan to go through index range scan. However, by default, oracle CBO does not select the index location. For example, oracle uses table full scan. Why? This type of problem occurs only when TIMESTAMP is introduced after oracle version 9.2.

Before 9.2, Oracle only had DATE, but not TIMESTAMP. When jdbc preparedStatement. setTimestamp is used, the type of the variable to be bound is set to DATE correctly. After 9.2, oracle began to support TIMESTAMP, both of which support the precision of yyyy-MM-dd hh24: mi: ss, of course, TIMESTAMP can support to the nanosecond level ).

However, if the jdbc driver api is not changed, when preparedStatement. setTimestamp is used, the oracle driver must choose whether to set the binding variable type to DATE or TIMESTAMP? It is estimated that the TIMESTAMP precision is higher, and Oracle finally chooses to set the type of the bound variable to TIMESTAMP by default. In this case, if the actual attribute is the DATE column, it will cause oracle to be implicitly formatted

 
 

  
  
“TO_TIMESTAMP(date_column) = parameter_timestamp” 
 
 

For conversion, you must know that oracle CBO does not select the index of the column to which a function applies, unless it is a function index. Therefore, table full scan instead of index range scan is used at the top of the page.

Does Oracle provide other methods to correctly bind variables? Oracle provides several methods to solve this problem.

1. Upgrade to 11 GB and use the new correct driver api.

2. Change all DATE columns to the TIMESTAMP column.

3. Use V8Compatible flag.

The above is an introduction to how to use Java Date in the Oracle version driver, and I hope you will get something.