英文:
How to insert a String into Oracle as a date type
问题
我有一个作为字符串的出生日期
String memBirthDate = "19990715";
并且我尝试使用以下查询将其插入到 Oracle 中。
<entry key="insertMember">
INSERT INTO MEMBER
(
MEM_BIRTHDATE
)
VALUES (
(TO_DATE)?
)
</entry>
但它不起作用。我认为我的 Oracle 查询中存在一些问题，但我不确定。
我应该怎么办？
英文:
I have a birthdate as string
String memBirthDate = "19990715";
and I tried to insert this into oracle by using query below.
<entry key="insertMember">
INSERT INTO MEMBER
(
MEM_BIRTHDATE
)
VALUES (
(TO_DATE)?
)
</entry>
But it doesn't work. I think there's some problem in my oracle query but I'm not sure.
What should I do?
答案1
得分: 2
避免使用旧类
永远不要使用Date类，它们都属于可怕的日期时间旧类，这些类在多年前被现代的java.time类取代，这些类在JSR 310中定义。
LocalDate
对于仅包含日期的数值，请使用LocalDate类。
您的输入字符串符合ISO 8601标准格式的“basic”变体，因此请使用预定义的格式化程序，DateTimeFormatter.BASIC_ISO_DATE。
String input = "19990715";
LocalDate birthDate = LocalDate.parse(input, DateTimeFormatter.BASIC_ISO_DATE);
编写SQL如下。
提示：在所有数据库命名中使用尾随下划线，以避免与保留关键字发生冲突。SQL标准明确承诺永不使用尾随下划线。请参阅此答案。
另一个提示：对于嵌入的SQL代码，请使用文本块。
String sql =
"""
INSERT INTO member_ (id_, birth_date_)
VALUES (?, ?)
;
""";
在写入数据库时，使用LocalDate对象替换。
myPreparedStatement.setObject(2, birthDate);
从数据库中检索数据值时。
LocalDate birthDate = myResultSet.getObject(…, LocalDate.class);
所有这些内容在Stack Overflow上已经有很多次讨论过。搜索以获取更多信息。您将找到创建表、插入行和检索行的示例应用程序的完整源代码。
英文:
Avoid legacy classes
Never use either Date class. Both are part of the terrible date-time legacy classes that were years ago supplanted by the modern java.time classes defined in JSR 310.
LocalDate
For a date-only value, use LocalDate class.
Your input string complies with the “basic” variation of the ISO 8601 standard format, YYYYMMDD. So use the predefined formatter, DateTimeFormatter.BASIC_ISO_DATE.
String input = "19990715" ;
LocalDate birthDate = LocalDate.parse( input , DateTimeFormatter.BASIC_ISO_DATE ) ;
Write your SQL like the following.
Tip: Use trailing underscore in all your database naming to avoid collision with reserved keywords. The SQL standard promises explicitly to never use a trailing underscore. See this Answer by me.
Another tip: Use text blocks for your embedded SQL code.
String sql =
"""
INSERT INTO member_ ( id_ , birth_date_ )
VALUES ( ? , ? )
;
""";
Exchange the LocalDate object for writing to database.
myPreparedStatement.setObject( 2 , birthDate ) ;
When retrieving the data value from the database.
LocalDate birthDate = myResultSet.getObject( … , LocalDate.class ) ;
All this has been covered many times on Stack Overflow. Search to learn more. You will find complete source code for example apps that create a table, insert rows, and retrieve rows.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论