Java 日期 - 插入数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1081234/
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
Java Date - Insert into database
提问by zSynopsis
I need to figure out a way to insert a record with a java.util.Date field into a database and i'm stuck.
我需要想办法将带有 java.util.Date 字段的记录插入到数据库中，但我被卡住了。
Does anyone know how i can do this? Right now i have something like.
有谁知道我该怎么做？现在我有类似的东西。
java.util.Date myDate = new java.util.Date("01/01/2009");
sb.append("INSERT INTO USERS");
sb.append("(USER_ID, FIRST_NAME, LAST_NAME, SEX, DATE) ");
sb.append("VALUES ( ");
sb.append(" '" + userId + "'");
sb.append(", '" + myUser.GetFirstname() + "' ");
sb.append(", '" + myUser.GetLastname() + "' ");
sb.append(", '" + myUser.GetSex() + "' ");
sb.append(", '" + myDate + "'");
sb.append(")");
Util.executeUpdate(sb.toString());
But when i run something like this i get the error: The syntax of the string representation of a datetime value is incorrect.
但是当我运行这样的东西时，我得到了错误：日期时间值的字符串表示的语法不正确。
Heres what the sql statement looks like:
下面是 sql 语句的样子：
INSERT INTO USERS (USER_ID
, FIRST_NAME
, LAST_NAME
, SEX
, CRDATE)
VALUES (
'user'
, 'FirstTest'
, 'LastTest'
, 'M'
, 'Thu Jan 01 00:00:00 CST 2009')
Thanks
谢谢
采纳答案by Brian
Granted, PreparedStatementwill make your code better, but to answer your question you need to tell the DBMS the format of your string representation of the Date. In Oracle (you don't name your database vendor) a string date is converted to Dateusing the TO_DATE()function:
当然，PreparedStatement会使您的代码更好，但要回答您的问题，您需要告诉 DBMS 日期的字符串表示形式。在 Oracle 中（你没有命名你的数据库供应商）一个字符串日期被转换为Date使用TO_DATE()函数：
INSERT INTO TABLE_NAME(
date_column
)values(
TO_DATE('06/06/2006', 'mm/dd/yyyy')
)
回答by fortran
回答by OscarRyz
PreparedStatement
准备好的语句
You should definitely use a PreparedStatement. (Tutorial)
您绝对应该使用PreparedStatement. （教程）
That way you can invoke:
这样你就可以调用：
pstmt.setDate( 1, aDate );
The JDBC driverwill do date-time handling appropriate for your particular database.
该JDBC驱动程序会为你的特定数据库做的日期时间处理适当。
Also, a PreparedStatement stops any SQL injectionhacking attempts – very important!(humor)
此外，PreparedStatement 会阻止任何SQL 注入黑客尝试——非常重要！（幽默）
It should look like this:
它应该是这样的：
SimpleDateFormat format = new SimpleDateFormat( "MM/dd/yyyy" ); // United States style of format.
java.util.Date myDate = format.parse( "10/10/2009" ); // Notice the ".util." of package name.
PreparedStatement pstmt = connection.prepareStatement(
"INSERT INTO USERS ( USER_ID, FIRST_NAME, LAST_NAME, SEX, DATE ) " +
" values (?, ?, ?, ?, ? )");
pstmt.setString( 1, userId );
pstmt.setString( 3, myUser.getLastName() );
pstmt.setString( 2, myUser.getFirstName() ); // please use "getFir…" instead of "GetFir…", per Java conventions.
pstmt.setString( 4, myUser.getSex() );
java.sql.Date sqlDate = new java.sql.Date( myDate.getTime() ); // Notice the ".sql." (not "util") in package name.
pstmt.setDate( 5, sqlDate );
And that's it, the JDBC driver will create the right SQL syntax for you.
就是这样，JDBC 驱动程序将为您创建正确的 SQL 语法。
Retrieving
检索
When retrieving a Date object, you can use a SimpleDateFormatto create a formatted string representation of the date-time value.
检索 Date 对象时，您可以使用SimpleDateFormat创建日期时间值的格式化字符串表示形式。
Here is one quick example line, but search StackOverflow for manymore.
下面是一个简单的例子线，但StackOverflow上搜索了很多更多。
String s = new SimpleDateFormat("dd/MM/yyyy").format( aDate );
回答by objects
You should be using java.sql.Timestamp instead of java.util.Date. Also using a PreparedStatement will save you worrying about the formatting.
您应该使用 java.sql.Timestamp 而不是 java.util.Date。使用 PreparedStatement 还可以让您不必担心格式问题。
回答by Hyman Leow
Before I answer your question, I'd like to mention that you should probably look into using some sort of ORM solution (e.g., Hibernate), wrapped behind a data access tier. What you are doing appear to be very anti-OO. I admittedly do not know what the rest of your code looks like, but generally, if you start seeing yourself using a lot of Utility classes, you're probably taking too structural of an approach.
在我回答您的问题之前，我想提一下您可能应该考虑使用某种 ORM 解决方案（例如，Hibernate），包装在数据访问层之后。您正在做的事情似乎非常反 OO。诚然，我不知道您的代码的其余部分是什么样子，但一般来说，如果您开始看到自己使用了很多 Utility 类，那么您可能采用了过于结构化的方法。
To answer your question, as others have mentioned, look into java.sql.PreparedStatement, and use java.sql.Dateor java.sql.Timestamp. Something like (to use your original code as much as possible, you probably want to change it even more):
要回答您的问题，正如其他人所提到的，请查看java.sql.PreparedStatement并使用java.sql.Date或java.sql.Timestamp。类似的东西（为了尽可能多地使用你的原始代码，你可能想要更多地改变它）：
java.util.Date myDate = new java.util.Date("10/10/2009");
java.sql.Date sqlDate = new java.sql.Date(myDate.getTime());
sb.append("INSERT INTO USERS");
sb.append("(USER_ID, FIRST_NAME, LAST_NAME, SEX, DATE) ");
sb.append("VALUES ( ");
sb.append("?, ?, ?, ?, ?");
sb.append(")");
Connection conn = ...;// you'll have to get this connection somehow
PreparedStatement stmt = conn.prepareStatement(sb.toString());
stmt.setString(1, userId);
stmt.setString(2, myUser.GetFirstName());
stmt.setString(3, myUser.GetLastName());
stmt.setString(4, myUser.GetSex());
stmt.setDate(5, sqlDate);
stmt.executeUpdate(); // optionally check the return value of this call
One additional benefit of this approach is that it automatically escapes your strings for you (e.g., if were to insert someone with the last name "O'Brien", you'd have problems with your original implementation).
这种方法的另一个好处是它会自动为您转义您的字符串（例如，如果要插入姓氏“O'Brien”的人，您的原始实现就会出现问题）。
回答by nightingale2k1
if you are using mysql .. you can save date as "2009-12-31" for example.
如果您使用的是 mysql .. 例如，您可以将日期保存为“2009-12-31”。
update person set birthday_date = '2009-12-31'
更新人员设置birthday_date = '2009-12-31'
but i prefer to use jdbc although you have to create java.sql.Date ...
但我更喜欢使用 jdbc 虽然你必须创建 java.sql.Date ...
*Date is kind of evil in this world ... :)
*日期在这个世界上有点邪恶...... :)
回答by user3629370
pst.setDate(6, new java.sql.Date(txtDate.getDate().getTime()));
this is the code I used to save date into the database using jdbc works fine for me
这是我用来使用 jdbc 将日期保存到数据库中的代码对我来说很好用
pstis a variable for preparedstatementtxtdateis the name for the JDateChooser
pst是 Preparedstatement 的变量txtdate是 JDateChooser 的名称
回答by Basil Bourque
The Answerby OscarRyz is correct, and should have been the accepted Answer. But now that Answer is out-dated.
OscarRyz的答案是正确的，应该是公认的答案。但是现在这个答案已经过时了。
java.time
时间
In Java 8 and later, we have the new java.time package(inspired by Joda-Time, defined by JSR 310, with tutorial, extended by ThreeTen-Extraproject).
在 Java 8 及更高版本中，我们有新的java.time 包（受Joda-Time启发，由JSR 310定义，带有教程，由ThreeTen-Extra项目扩展）。
Avoid Old Date-Time Classes
避免旧的日期时间类
The old java.util.Date/.Calendar, SimpleDateFormat, and java.sql.Date classes are a confusing mess. For one thing, j.u.Date has date andtime-of-day while j.s.Date is date-only withouttime-of-day. Oh, except that j.s.Date only pretendsto not have a time-of-day. As a subclass of j.u.Date, j.s.Date inherits the time-of-day but automatically adjusts that time-of-day to midnight (00:00:00.000). Confusing? Yes. A bad hack, frankly.
旧的 java.util.Date/.Calendar、SimpleDateFormat 和 java.sql.Date 类是一团混乱。一方面， juDate 有日期和时间，而 jsDate 是日期，没有时间。哦，除了 jsDate 只是假装没有时间。作为 juDate 的子类，jsDate 继承了时间，但会自动将该时间调整为午夜 ( 00:00:00.000)。令人困惑？是的。坦率地说，这是一个糟糕的黑客攻击。
For this and many more reasons, those old classes should be avoided, used only a last resort. Use java.time where possible, with Joda-Timeas a fallback.
出于这个原因以及更多原因，应该避免使用那些旧类，仅作为最后手段使用。尽可能使用 java.time，Joda-Time作为后备。
LocalDate
LocalDate
In java.time, the LocalDateclass cleanly represents a date-only value without any time-of-day or time zone. That is what we need for this Question's solution.
在 java.time 中，LocalDate该类干净地表示没有任何时间或时区的仅日期值。这就是这个问题的解决方案所需要的。
To get that LocalDate object, we parse the input string. But rather than use the old SimpleDateFormatclass, java.time provides a new DateTimeFormatterclass in the java.time.format package.
为了获得那个 LocalDate 对象，我们解析输入字符串。但是SimpleDateFormatjava.time没有使用旧类，而是DateTimeFormatter在java.time.format 包中提供了一个新类。
String input = "01/01/2009" ;
DateTimeFormatter formatter = DateTimeFormatter.ofPattern( "MM/dd/yyyy" ) ;
LocalDate localDate = LocalDate.parse( input, formatter ) ;
JDBC drivers compliant with JDBC 4.2or later can use java.time types directly via the PreparedStatement::setObjectand ResultSet::getObjectmethods.
符合JDBC 4.2或更高版本的JDBC 驱动程序可以直接通过PreparedStatement::setObject和ResultSet::getObject方法使用 java.time 类型。
PreparedStatement pstmt = connection.prepareStatement(
"INSERT INTO USERS ( USER_ID, FIRST_NAME, LAST_NAME, SEX, DATE ) " +
" VALUES (?, ?, ?, ?, ? )");
pstmt.setString( 1, userId );
pstmt.setString( 3, myUser.getLastName() );
pstmt.setString( 2, myUser.getFirstName() ); // please use "getFir…" instead of "GetFir…", per Java conventions.
pstmt.setString( 4, myUser.getSex() );
pstmt.setObject( 5, localDate ) ; // Pass java.time object directly, without any need for java.sql.*.
But until you have such an updated JDBC driver, fallback on using the java.sql.Dateclass. Fortunately, that old java.sql.Dateclass has been gifted by Java 8 with a new convenient conversion static method, valueOf( LocalDate ).
但是，在您拥有这样一个更新的 JDBC 驱动程序之前，请回退到使用java.sql.Date类。幸运的是，java.sql.DateJava 8 赋予了这个旧类一个新的方便的静态转换方法，valueOf( LocalDate ).
In the sample code of the sibling Answer by OscarRyz, replace its "sqlDate =" line with this one:
在OscarRyz 的兄弟 Answer的示例代码中，将其“sqlDate =”行替换为：
java.sql.Date sqlDate = java.sql.Date.valueOf( localDate ) ;
回答by Madhuka Dilhan
you can use this code date and time time is 24 h
您可以使用此代码日期和时间时间为 24 小时
INSERT INTO TABLE_NAME(
date_column
)values(
TO_DATE('2016-10-05 10:53:56', 'SYYYY-MM-DD HH24:MI:SS')
)
回答by Nugroho Prayogo
VALUES ('"+user+"' , '"+FirstTest+"' , '"+LastTest+"'..............etc)
You can use it to insert variables into sql query.
您可以使用它在 sql 查询中插入变量。
