如果日期在数据库中存储为字符串（132011），那么在hibernate中使用to_date（）是否正确？ [英] Is it correct to use to_date() in hibernate if date is stored as string like (132011) in Database?

问题描述

我在Oracle数据库中有一个表Products（id，name，productionDate）。 日期栏定义为字符串，它的值为131120（年份的前两位数字，年份的后两位数字和日期的最后两位数字）。我需要使用Hibernate基于日期的排序输出。 SQL查询做到这一点如下所示：

  select * from products order to_date（productionDate，'YYMMDD'）desc; 
  

当我从命令提示符下尝试时，这个sql查询很好。

但是当我试图使用Hibernate的这个查询时，我无法检索信息，并且出现了一些错误。我的Hibernate代码如下所示：

  ....... 
 
字符串queryString = 从产品p到order_date（p.productionDate，'YYMMDD'）desc; 
 Query query = s.createQuery（queryString）; 
 
 ..... 
  

有些行在下面给出，有助于理解）

 线程main中的异常org.hibernate.exception.DataException：可能不执行查询
 ...... 
导致：java.sql.SQLException：ORA-01858：发现一个非数字字符，其中数字是预期的
  
 
 Bean定义如下所示：
 
 
  ...... 
 
字符串ID; 
字符串名称; 
 String productionDate; 
 
 // getter and setter 
  
不支持to_date（） ？如果没有，找到这种类型的查询输出的方法是什么？
解决方案
 HQL不支持to_date  - 至少根据docs  HQL表达式。如果所有日期都大于2000年1月1日，则可以按原始字符串值进行排序而不是转换。  
 
 
您可以使用to_date使用本地sql查询。查看org.hibernate.SQL的日志输出以确保hibernate不会对连接的实体进行太多不必要的查询。  
I have a table "Products(id, name, productionDate)" in Oracle database. "date" column is defined as String and it has value like "131120" (1st two digits for year, 2nd two digits for year & final two digits for date). I need a sorted output based on date using Hibernate. SQL query to do this is like the following:
select * from products order by to_date(productionDate, 'YYMMDD') desc;
This sql query is fine when I try from command prompt.

But when I tried to use this query using Hibernate, I am unable to retrieve the information and i got some error. My Hibernate code is like the following:
.......

String queryString = "from products p order by to_date(p.productionDate, 'YYMMDD')  desc";
Query query = s.createQuery(queryString);

.....
And I got the error like (Only some lines are given below which is helpful to understand)
Exception in thread "main" org.hibernate.exception.DataException: could not execute query
......
Caused by: java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected
Bean Definition is like the following:
......

String id;
String name;
String productionDate;

// getter and setter
Doesn't HQL support to_date()? If not, what is the way to find this type of query output?
解决方案
HQL does not support to_date - at least according to docs HQL expressions. If all of the dates are greater than Jan 1, 2000, you can sort by the raw string value instead of converting. 

You can use a native sql query using to_date. Look at the log output at org.hibernate.SQL to make sure that hibernate is not making too many unnecessary queries on joined entities.  

                        
这篇关于如果日期在数据库中存储为字符串（132011），那么在hibernate中使用to_date（）是否正确？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！