[大数据]Oracle时间函数

Oracle 时间间隔函数有INTERVAL、NUMTODSINTERVAL等，本文介绍这两个函数，以及如何在MyBatis中使用。要想整合到MyBatis中，首先需要编写正确的SQL，理解SQL的意思，然后在再代码中使用。

INTERVAL

间隔一年

1. 当前时间加一年

SELECT SYSDATE, SYSDATE + INTERVAL '1' YEAR FROM dual;

2. 当前时间减一年

SELECT SYSDATE, SYSDATE - INTERVAL '1' YEAR FROM dual;

间隔一天

SELECT SYSDATE, SYSDATE + INTERVAL '1' DAY FROM dual;

间隔一小时

SELECT SYSDATE, SYSDATE + INTERVAL '1' HOUR FROM dual;

间隔一分钟

SELECT SYSDATE, SYSDATE + INTERVAL '1' MINUTE FROM dual;

NUMTODSINTERVAL

间隔一天

SELECT SYSDATE, SYSDATE + NUMTODSINTERVAL(1, 'DAY') FROM dual;

间隔一小时

SELECT SYSDATE, SYSDATE + NUMTODSINTERVAL(1, 'HOUR') FROM dual;

间隔一分钟

SELECT SYSDATE, SYSDATE + NUMTODSINTERVAL(1, 'MINUTE') FROM dual;

NUMTODSINTERVAL 函数对于 YEAR、MONTH 参数不支持，返回函数的参数非法，如下图所示

MyBatis xml 中计算时间间隔

需要处理的问题

查找表中创建时间超过了24小时的数据，建表语句如下

CREATE TABLE t_test_table (
"ID" NUMBER(18,0), 
"CREATE_TIME" TIMESTAMP(6),
CONSTRAINT pk_id PRIMARY KEY(ID)
)

创建序列，获取唯一ID

CREATE SEQUENCE seq_t_test_table;

插入测试数据

INSERT INTO t_test_table ("ID", "CREATE_TIME") VALUES (SEQ_T_TEST_TABLE.nextval, SYSDATE - NUMTODSINTERVAL(1, 'DAY'));
INSERT INTO t_test_table ("ID", "CREATE_TIME") VALUES (SEQ_T_TEST_TABLE.nextval, SYSDATE - NUMTODSINTERVAL(1, 'HOUR'));
INSERT INTO t_test_table ("ID", "CREATE_TIME") VALUES (SEQ_T_TEST_TABLE.nextval, SYSDATE - NUMTODSINTERVAL(1, 'MINUTE'));

如果把插入语句修改成批量插入，还能获取到正确的序列数据吗?

INSERT ALL 
INTO t_test_table ("ID", "CREATE_TIME") VALUES (SEQ_T_TEST_TABLE.nextval, SYSDATE - NUMTODSINTERVAL(1, 'DAY'))
INTO t_test_table ("ID", "CREATE_TIME") VALUES (SEQ_T_TEST_TABLE.nextval, SYSDATE - NUMTODSINTERVAL(1, 'HOUR'))
INTO t_test_table ("ID", "CREATE_TIME") VALUES (SEQ_T_TEST_TABLE.nextval, SYSDATE - NUMTODSINTERVAL(1, 'MINUTE'))
select 1 from dual;

查询数据是否插入成功

查询超过创建时间大于24小时的sql 如下

SELECT * FROM t_test_table WHERE SYSDATE -  INTERVAL '24' HOUR >= CREATE_TIME;

SELECT * FROM t_test_table WHERE SYSDATE - NUMTODSINTERVAL(24, 'HOUR') >= CREATE_TIME;

INTERVAL 函数整合到 MyBatis

<select id="selectBeforeHour" resultType="java.lang.Long">
        SELECT ID FROM t_test_table WHERE SYSDATE - INTERVAL #{hour} HOUR >= CREATE_TIME
    </select>

测试方法

package com.scd.mybatis.oracle;

import com.scd.mapper.OracleBatchSeqMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;

import java.io.Reader;
import java.util.List;

/**
 * @author James
 */
public class TimeFunTest {

    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void setUp() throws Exception {
        String resource = "templates/mybatis-config-oracle.xml";
        Reader reader = Resources.getResourceAsReader(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
    }

    @Test
    public void testBeforeHour() {
        try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
            OracleBatchSeqMapper oracleBatchSeqMapper = sqlSession.getMapper(OracleBatchSeqMapper.class);
            List<Long> idList = oracleBatchSeqMapper.selectBeforeHour(6);
            System.out.println(idList);
        }
    }
}

运行测试方法报错

22:36:08.489 [main] DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
22:36:08.515 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
22:36:08.515 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
22:36:08.515 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
22:36:08.515 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - PooledDataSource forcefully closed/removed all connections.
22:36:08.616 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection
22:36:09.563 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 1766505436.
22:36:09.564 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@694abbdc]
22:36:09.567 [main] DEBUG com.scd.mapper.OracleBatchSeqMapper.selectBeforeHour - ==>  Preparing: SELECT ID FROM t_test_table WHERE SYSDATE - INTERVAL ? HOUR >= CREATE_TIME
22:36:09.714 [main] DEBUG com.scd.mapper.OracleBatchSeqMapper.selectBeforeHour - ==> Parameters: 6(Integer)
22:36:09.774 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@694abbdc]
22:36:09.775 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@694abbdc]
22:36:09.775 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Returned connection 1766505436 to pool.

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-00920: 无效的关系运算符

### The error may exist in mapper/OracleBatchSeqMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT ID FROM t_test_table WHERE SYSDATE - INTERVAL ? HOUR >= CREATE_TIME
### Cause: java.sql.SQLSyntaxErrorException: ORA-00920: 无效的关系运算符


	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
	at com.sun.proxy.$Proxy8.selectBeforeHour(Unknown Source)
	at com.scd.mybatis.oracle.TimeFunTest.testBeforeHour(TimeFunTest.java:34)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
	at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00920: 无效的关系运算符

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
	at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy10.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	... 32 more
Caused by: Error : 920, Position : 53, Sql = SELECT ID FROM t_test_table WHERE SYSDATE - INTERVAL :1  HOUR >= CREATE_TIME, OriginalSql = SELECT ID FROM t_test_table WHERE SYSDATE - INTERVAL ? HOUR >= CREATE_TIME, Error Msg = ORA-00920: 无效的关系运算符

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
	... 60 more

输出的查询sql

22:36:09.567 [main] DEBUG com.scd.mapper.OracleBatchSeqMapper.selectBeforeHour - ==>  Preparing: SELECT ID FROM t_test_table WHERE SYSDATE - INTERVAL ? HOUR >= CREATE_TIME
22:36:09.714 [main] DEBUG com.scd.mapper.OracleBatchSeqMapper.selectBeforeHour - ==> Parameters: 6(Integer)
22:36:09.774 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@694abbdc]

ORA-00920: 无效的关系运算符 少了符号 '，修改 xml 如下

<select id="selectBeforeHour" resultType="java.lang.Long">
        SELECT ID FROM t_test_table WHERE SYSDATE - INTERVAL &#039;#{hour}&#039; HOUR >= CREATE_TIME
    </select>

输出的查询sql

22:42:57.663 [main] DEBUG com.scd.mapper.OracleBatchSeqMapper.selectBeforeHour - ==>  Preparing: SELECT ID FROM t_test_table WHERE SYSDATE - INTERVAL '?' HOUR >= CREATE_TIME

报错信息

Caused by: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: 无效的列索引
	at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:75)
	at org.apache.ibatis.type.UnknownTypeHandler.setNonNullParameter(UnknownTypeHandler.java:67)
	at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:73)
	... 42 more
Caused by: java.sql.SQLException: 无效的列索引
	at oracle.jdbc.driver.OraclePreparedStatement.setIntInternal(OraclePreparedStatement.java:4530)
	at oracle.jdbc.driver.OraclePreparedStatement.setInt(OraclePreparedStatement.java:4521)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.setInt(OraclePreparedStatementWrapper.java:197)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:67)
	at com.sun.proxy.$Proxy10.setInt(Unknown Source)
	at org.apache.ibatis.type.IntegerTypeHandler.setNonNullParameter(IntegerTypeHandler.java:31)
	at org.apache.ibatis.type.IntegerTypeHandler.setNonNullParameter(IntegerTypeHandler.java:26)
	at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:73)
	... 44 more

看报错信息是预编译sql 设置参数有问题，如果把 # 改成 $ 符号是否会成功了？修改xml 如下

 <select id="selectBeforeHour" resultType="java.lang.Long">
        SELECT ID FROM t_test_table WHERE SYSDATE - INTERVAL &#039;${hour}&#039; HOUR >= CREATE_TIME
    </select>

再次运行测试方法，查询成功，xml 中使用 $ 符号就不在使用预编译的sql了，直接拼接sql，如果这么写，估计有些安全检查过不了，使用另外一个方法 NUMTODSINTERVAL就可以使用 # 预编译参数的方式

$ 符号拼接 sql 源码追踪

在 org.apache.ibatis.scripting.xmltags.TextSqlNode#apply 这个方法加个断点

解析到 ${ 符号就会开始拼接sql, 具体逻辑代码如下

NUMTODSINTERVAL 函数整合到 MyBatis

修改 xml 中的 sql, 将时间间隔函数修改成 NUMTODSINTERVAL

    <select id="selectBeforeHour" resultType="java.lang.Long">
        SELECT ID FROM t_test_table WHERE SYSDATE - NUMTODSINTERVAL(#{hour}, 'HOUR') >= CREATE_TIME
    </select>

运行输出的 sql 如下：

23:10:53.627 [main] DEBUG com.scd.mapper.OracleBatchSeqMapper.selectBeforeHour - ==>  Preparing: SELECT ID FROM t_test_table WHERE SYSDATE - NUMTODSINTERVAL(?, 'HOUR') >= CREATE_TIME
23:10:53.780 [main] DEBUG com.scd.mapper.OracleBatchSeqMapper.selectBeforeHour - ==> Parameters: 6(Integer)
23:10:53.873 [main] DEBUG com.scd.mapper.OracleBatchSeqMapper.selectBeforeHour - <==      Total: 1

正确的获取到了结果