oracle “文字与格式字符串不匹配”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16378111/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"Literal does not match the format string" error
提问by Akki
When I try to execute the below code it gives me an java.sql.SQLException: ORA-01861: literal does not match format stringerror.
当我尝试执行以下代码时，它给了我一个java.sql.SQLException: ORA-01861: literal does not match format string错误。
I am trying to copy some of the column values from customer1_detailstable to customer2_detailstable. The columns datatype which I am trying to move is TIMESTAMP(6)for TIME_REGISTERED, DATE_DISCHARGEDcolumns and the datatype for DATE_OF_BIRTHcolumn is DATE
我正在尝试将某些列值从customer1_details表复制到customer2_details表。我试图移动的列数据类型是TIMESTAMP(6)for TIME_REGISTERED, DATE_DISCHARGEDcolumns 和列的数据类型DATE_OF_BIRTH是DATE
try
{
Connection conn=Address.getOracleConnection();
int id = 1;
Date dob = null;
Timestamp timereg = null,datedischarged = null;
Statement stmt=conn.createStatement();
ResultSet res=stmt.executeQuery("SELECT TIME_REGISTERED,DATE_DISCHARGED,DATE_OF_BIRTH from customer1_details WHERE customer_id = '"+id+"' ");
if(res.next())
{
timereg=res.getTimestamp("TIME_REGISTERED");
datedischarged=res.getTimestamp("DATE_DISCHARGED");
dob=res.getDate("DATE_OF_BIRTH");
}
String sql1="INSERT INTO customer2_details(TIME_REGISTERED_3,DATE_DISCHARGED_3,DATE_OF_BIRTH,customer_ID) "
+ "VALUES('"+timereg+"','"+datedischarged+"','"+dob+"','"+id+"') ";
PreparedStatement pst=conn.prepareStatement(sql1);
pst.executeUpdate();
pst.close();
conn.close();
}
catch(Exception e)
{ System.out.print(e); }
It will be more helpful if anyone provides the answer without using INSERT INTO ... SELECT ... statement.
如果有人提供答案会更有帮助without using INSERT INTO ... SELECT ... statement。
回答by Barmar
YOu can do it in one statement with a query like:
您可以在一个语句中使用如下查询来完成：
"INSERT INTO customer2_details (TIME_REGISTERED_3,DATE_DISCHARGED_3,DATE_OF_BIRTH,customer_ID)
SELECT TIME_REGISTERED,DATE_DISCHARGED,DATE_OF_BIRTH, customer_id
from customer1_details WHERE customer_id = '"+id+"' "
回答by Martin Drautzburg
This is most likely caused by passing your Date and Timestamp variables as Strings to the insert statement.
这很可能是由于将 Date 和 Timestamp 变量作为字符串传递给 insert 语句造成的。
When you insert or update Date or Timestamp values, there is a default format in which you can pass those values as strings. What you pass is java's idea of how to convert Dates and Timestamps into strings. These two don't seem to match.
当您插入或更新 Date 或 Timestamp 值时，有一种默认格式，您可以在其中将这些值作为字符串传递。你传递的是java关于如何将日期和时间戳转换为字符串的想法。这两个好像不太搭。
Your best bet is probably to use bind variables, then the framework should take care of that.
您最好的选择可能是使用绑定变量，然后框架应该负责。
An Alternative would be to use Oracle's to_date() function, where you can specify the format string. You would then define a format string which considers java's way of representing dates as strings. However, I am not sure if the java representation depends on the locale. If so, you would have to write you own date_to_string() method, which always returns dates in the same format, or your program may work on some computers, but not on others with a different locale.
另一种方法是使用 Oracle 的 to_date() 函数，您可以在其中指定格式字符串。然后，您将定义一个格式字符串，该字符串考虑 java 将日期表示为字符串的方式。但是，我不确定 java 表示是否取决于语言环境。如果是这样，您将必须编写自己的 date_to_string() 方法，该方法始终以相同的格式返回日期，或者您的程序可能在某些计算机上运行，但不能在具有不同语言环境的其他计算机上运行。
And finally you can do an insert-select which bypasses the java layer entirely.
最后，您可以执行完全绕过 java 层的插入选择。
回答by Felype
Read the timestamps as strings with getString();
使用 getString() 将时间戳作为字符串读取；
OR call toString() in your java Timestamp object instances.
或者在您的 java Timestamp 对象实例中调用 toString() 。
String sql1="INSERT INTO customer2_details(TIME_REGISTERED_3,DATE_DISCHARGED_3,DATE_OF_BIRTH,customer_ID) "
+ "VALUES('"+timereg.toString()+"','"+datedischarged.toString()+"','"+dob.toString()+"','"+id+"') ";
