SQL错误17268:年份超出范围(Java/Spring)

Question

当我在数据库上做一个简单的SELECT时，我面临着一个与Oracle/Hibernate相关的问题，我找不到任何关于这个问题的有用信息。应用程序使用Spring Data自己创建查询。数据库为Oracle 11.02.0.4, JDBC驱动相同版本。

SQL Error is 17268 : Year out of range.

我知道这个消息很明显，但是当在数据库上手动执行日志中打印的查询时，我没有问题，并且看到日期字段的年份被设置为2016…

我试过用Java对象映射Date和Timestamp上的这些字段，同样的错误。奇怪的是，客户机突然出现了这个问题，而我无法在自己的环境中重现这个问题(客户机正在运行WebSphere 8.5.5)。

这个错误可能来自哪里，或者我应该调查什么?

参见下面的堆栈跟踪:

[11/14/16 12:05:03:370 CET] 000000d6 SystemOut     O 12:05:03.370 [WebContainer : 1] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 17268, SQLState: 99999
[11/14/16 12:05:03:371 CET] 000000d6 SystemOut     O 12:05:03.371 [WebContainer : 1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Year out of range.
[11/14/16 12:05:03:406 CET] 000000d6 ServletWrappe E com.ibm.ws.webcontainer.servlet.ServletWrapper service SRVE0014E: Uncaught service() exception root cause dispatcher: org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.GenericJDBCException: could not execute statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:979)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:858)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:575)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:668)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1232)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:781)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:480)
    at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:178)
    at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1114)
    at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:3926)
    at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:304)
    at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1007)
    at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1817)
    at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:200)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:463)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:530)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:316)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:287)
    at com.ibm.ws.ssl.channel.impl.SSLConnectionLink.determineNextChannel(SSLConnectionLink.java:1049)
    at com.ibm.ws.ssl.channel.impl.SSLConnectionLink$MyReadCompletedCallback.complete(SSLConnectionLink.java:643)
    at com.ibm.ws.ssl.channel.impl.SSLReadServiceContext$SSLReadCompletedCallback.complete(SSLReadServiceContext.java:1818)
    at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
    at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
    at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
    at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
    at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
    at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
    at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
    at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1881)
Caused by: org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.GenericJDBCException: could not execute statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:415)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:418)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:111)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy83.findByEnvelopeIdAndStatusInAndCurrentVersionOrderByTecidAsc(Unknown Source)
    ...
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:649)
    ...
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:649)
    ...
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
    ...
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:95)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
    at java.lang.reflect.Method.invoke(Method.java:620)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967)
    ... 29 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:458)
    at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:67)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:110)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:97)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:88)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:395)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:373)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 72 more
Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:190)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3285)
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3183)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3525)
    at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:158)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:453)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:345)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350)
    at org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:67)
    at org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1187)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1253)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
    ... 85 more
Caused by: java.sql.SQLException: Year out of range.
    at oracle.jdbc.driver.DateCommonBinder.setOracleCYMD(OraclePreparedStatement.java:19395)
    at oracle.jdbc.driver.TimestampBinder.bind(OraclePreparedStatement.java:19668)
    at oracle.jdbc.driver.OraclePreparedStatement.setupBindBuffers(OraclePreparedStatement.java:3166)
    at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2378)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3608)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3694)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteUpdate(WSJdbcPreparedStatement.java:1187)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeUpdate(WSJdbcPreparedStatement.java:804)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
    ... 98 more

生成的查询:

select
    documentdb0_.tecid as tecid1_3_,
    documentdb0_.comments_nb as comments_nb2_3_,
    documentdb0_.content_path as content_path3_3_,
    documentdb0_.content_size as content_size4_3_,
    documentdb0_.creation_date as creation_date5_3_,
    documentdb0_.creator as creator6_3_,
    documentdb0_.current_version as current_version7_3_,
    documentdb0_.docfamily as docfamily8_3_,
    documentdb0_.doctype as doctype9_3_,
    documentdb0_.attached_document_id as attached_document10_3_,
    documentdb0_.envelope_id as envelope_id11_3_,
    documentdb0_.filename as filename12_3_,
    documentdb0_.hash as hash13_3_,
    documentdb0_.mime_type as mime_type14_3_,
    documentdb0_.modify_date as modify_date15_3_,
    documentdb0_.name as name16_3_,
    documentdb0_.number_of_pages as number_of_pages17_3_,
    documentdb0_.parent_id as parent_id18_3_,
    documentdb0_.root_id as root_id19_3_,
    documentdb0_.serie as serie20_3_,
    documentdb0_.status as status21_3_,
    documentdb0_.teclock as teclock22_3_,
    documentdb0_.version_id as version_id23_3_
from
    tb_document documentdb0_
where
    documentdb0_.envelope_id=44104
    and (documentdb0_.status in (‘TO_SIGN') )
    and documentdb0_.current_version=1
order by
    documentdb0_.tecid asc

通过查询(手动)检索的样本数据:

TECID1_3_;COMMENTS_NB2_3_;CONTENT_PATH3_3_;CONTENT_SIZE4_3_;CREATION_DATE5_3_;CREATOR6_3_;CURRENT_VERSION7_3_;DOCFAMILY8_3_;DOCTYPE9_3_;ATTACHED_DOCUMENT10_3_;ENVELOPE_ID11_3_;FILENAME12_3_;HASH13_3_;MIME_TYPE14_3_;MODIFY_DATE15_3_;NAME16_3_;NUMBER_OF_PAGES17_3_;PARENT_ID18_3_;ROOT_ID19_3_;SERIE20_3_;STATUS21_3_;TECLOCK22_3_;VERSION_ID23_3_
55054;0;/data/f5cbf3373b4d7bdc5abfc7df92334f54-20161024091158880.pdf;190354;24-oct-16;993513;1;329;203;;44104;22878767.pdf;f5cbf3373b4d7bdc5abfc7df92334f54;application/pdf;24-oct-16;CONSENT;2;;55054;11;TO_SIGN;3;1

Answer 1

问题实际上奇怪地不在Java所指示的行，这使我一无所获。在逐步调试模式下，我偶然发现了一个更新查询，它在SELECT之前导致了一个问题。

在UPDATE期间试图引入年份为10000的日期，而Oracle支持的最大日期是9999。这个溢出日期是由于从另一个系统读取的截止日期，并定义为31/12/9999 23:23:59:在错误的时区，它被读取为10000 January。

Answer 2

SQL Error is 17268: Year out of range .

当我们尝试插入超过9999的Year值时，会出现以下错误Year out of range。ORACLE supports till 9999 - 10000 leads Year out of range

对于Util日期，他们的限制为Year，你可以使用任何数字777777作为Year，但是当你试图插入相同的Util日期到oracle服务器时，它会导致yearoutorange。

我在多线程概念中遇到过同样的问题。当我尝试将字符串表示的日期转换为带有时间戳的Sql日期时。要复制相同的内容，您可以使用以下代码形式MaintainEqualThreadsPatallel.loopFunction(...)

public static Timestamp getTimeStamp(Date date) { // yearOutOfRange
    //Date dbResponseTime = new java.util.Date();
    Timestamp timestamp = new java.sql.Timestamp(date.getTime());
    System.out.println("Timestamp :"+ timestamp);
    return timestamp;
}
private static final SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
public static Date stringToDate(String date) throws ParseException { // synchronized
    sdf.setTimeZone(TimeZone.getTimeZone("CET"));
    return sdf.parse(date);
}

输出:

getTimeStamp( stringToDate("20-Feb-2020 20:53:12") );
Timestamp :2020-02-20 20:53:12.0
Timestamp :1970-02-20 20:53:12.0
Timestamp :0020-02-20 02:53:12.0
Timestamp :20200-02-20 20:53:12.0  // yearOutOfRange
Timestamp :0020-02-20 02:53:12.0
Timestamp :2020-02-21 04:52:12.0
Timestamp :0012-02-21 20:53:12.0
java.lang.NumberFormatException: For input string: ""

---

从<<p> strong> java.sql。日期源代码我们可以观察到年份支持到9999即YYYY年的日期格式。

SQL Date:

java.util.Date utilDate = new java.util.Date();
java.sql.Date sqlDate = new java.sql.Date( utilDate.getTime() );
// SQL Source: new Date(date.getYear() - 1900, date.getMonthValue() -1, date.getDayOfMonth());

Oracle日期函数

TO_DATE('08-FEB-10000', 'DD-MON-YYYY')
SQL Error: ORA-01830: date format picture ends before converting entire input string
TO_DATE('08-FEB-10000 13:30:12', 'DD-MON-YYYYY HH24:MI:SS')
SQL Error: ORA-01812: year may only be specified once

---

要避免SQL Error Year out of range使用以下任何一种方法

• SimpleDateFormat.parse(strDate)函数调用形式同步块
• LocalDateTime.parse(strDate, DateTimeFormatter)

public static Timestamp getTimestamp(String strDate) {
    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss");
    LocalDateTime dateTime = LocalDateTime.parse(strDate, formatter);
                            //LocalDateTime.of(10000, Month.DECEMBER, 30, 12, 10, 05);
    Timestamp valueOf = java.sql.Timestamp.valueOf(dateTime);
    System.out.format("TimeStamp Local : [%-30s]n", valueOf.toString());
    java.sql.Date sqlDate = new java.sql.Date( valueOf.getTime() );
    System.out.format("java.sql.Date : [%-30s]n", sqlDate);
    return valueOf;
    /*LocalDateTime with = dateTime.with(TemporalAdjusters.lastInMonth(DayOfWeek.SUNDAY));
    int dayOfMonth = with.getDayOfMonth();
    System.out.println("Last Sunday of Month: "+dayOfMonth);*/
}