Java 在 PreparedStatement 中使用 setDate
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18614836/
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
Using setDate in PreparedStatement
提问by roymustang86
In order to make our code more standard, we were asked to change all the places where we hardcoded our SQL variables to prepared statements and bind the variables instead.
为了使我们的代码更加标准，我们被要求将我们硬编码 SQL 变量的所有位置更改为准备好的语句，并改为绑定变量。
I am however facing a problem with the setDate().
但是，我正面临setDate().
Here is the code:
这是代码：
DateFormat dateFormatYMD = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
DateFormat dateFormatMDY = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
Date now = new Date();
String vDateYMD = dateFormatYMD.format(now);
String vDateMDY = dateFormatMDY.format(now);
String vDateMDYSQL = vDateMDY ;
java.sql.Date date = new java.sql.Date(0000-00-00);
requestSQL = "INSERT INTO CREDIT_REQ_TITLE_ORDER (REQUEST_ID," +
" ORDER_DT, FOLLOWUP_DT) " + "values(?,?,?,)";
prs = conn.prepareStatement(requestSQL);
prs.setInt(1,new Integer(requestID));
prs.setDate(2,date.valueOf(vDateMDYSQL));
prs.setDate(3,date.valueOf(sqlFollowupDT));
I get this error when the SQL gets executed:
执行 SQL 时出现此错误：
java.lang.IllegalArgumentException
at java.sql.Date.valueOf(Date.java:138)
at com.cmsi.eValuate.TAF.TAFModuleMain.CallTAF(TAFModuleMain.java:1211)
Should I use setString()instead with a to_date()?
我应该setString()用 a 代替to_date()吗？
采纳答案by Paul Vargas
❐ Using java.sql.Date
❐ 使用 java.sql.Date
If your table has a column of type DATE:
如果您的表格有一列类型为DATE：
java.lang.StringThe method
java.sql.Date.valueOf(java.lang.String)received a string representing a date in the formatyyyy-[m]m-[d]d. e.g.:ps.setDate(2, java.sql.Date.valueOf("2013-09-04"));java.util.DateSuppose you have a variable
endDateof typejava.util.Date, you make the conversion thus:ps.setDate(2, new java.sql.Date(endDate.getTime());Current
If you want to insert the current date:
ps.setDate(2, new java.sql.Date(System.currentTimeMillis())); // Since Java 8 ps.setDate(2, java.sql.Date.valueOf(java.time.LocalDate.now()));
java.lang.String该方法
java.sql.Date.valueOf(java.lang.String)接收一个字符串，表示格式为 的日期yyyy-[m]m-[d]d。例如：ps.setDate(2, java.sql.Date.valueOf("2013-09-04"));java.util.Date假设您有一个
endDatetype变量java.util.Date，您可以这样进行转换：ps.setDate(2, new java.sql.Date(endDate.getTime());当前的
如果要插入当前日期：
ps.setDate(2, new java.sql.Date(System.currentTimeMillis())); // Since Java 8 ps.setDate(2, java.sql.Date.valueOf(java.time.LocalDate.now()));
❐ Using java.sql.Timestamp
❐ 使用 java.sql.Timestamp
If your table has a column of type TIMESTAMPor DATETIME:
如果您的表格有一列类型为TIMESTAMPor DATETIME：
java.lang.StringThe method
java.sql.Timestamp.valueOf(java.lang.String)received a string representing a date in the formatyyyy-[m]m-[d]d hh:mm:ss[.f...]. e.g.:ps.setTimestamp(2, java.sql.Timestamp.valueOf("2013-09-04 13:30:00");java.util.DateSuppose you have a variable
endDateof typejava.util.Date, you make the conversion thus:ps.setTimestamp(2, new java.sql.Timestamp(endDate.getTime()));Current
If you require the current timestamp:
ps.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis())); // Since Java 8 ps.setTimestamp(2, java.sql.Timestamp.from(java.time.Instant.now())); ps.setTimestamp(2, java.sql.Timestamp.valueOf(java.time.LocalDateTime.now()));
java.lang.String该方法
java.sql.Timestamp.valueOf(java.lang.String)接收一个字符串，表示格式为 的日期yyyy-[m]m-[d]d hh:mm:ss[.f...]。例如：ps.setTimestamp(2, java.sql.Timestamp.valueOf("2013-09-04 13:30:00");java.util.Date假设您有一个
endDatetype变量java.util.Date，您可以这样进行转换：ps.setTimestamp(2, new java.sql.Timestamp(endDate.getTime()));当前的
如果您需要当前时间戳：
ps.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis())); // Since Java 8 ps.setTimestamp(2, java.sql.Timestamp.from(java.time.Instant.now())); ps.setTimestamp(2, java.sql.Timestamp.valueOf(java.time.LocalDateTime.now()));
回答by Vincent Malgrat
The docsexplicitly says that java.sql.Datewill throw:
该文件明确表示，java.sql.Date将抛出：
IllegalArgumentException- if the date given is not in the JDBC date escape format (yyyy-[m]m-[d]d)
IllegalArgumentException- 如果给定的日期不是 JDBC 日期转义格式 (yyyy-[m]m-[d]d)
Also you shouldn't need to convert a date to a Stringthen to a sql.date, this seems superfluous (and bug-prone!). Instead you could:
此外，您不需要将日期转换为Stringthen 到 a sql.date，这似乎是多余的（并且容易出错！）。相反，您可以：
java.sql.Date sqlDate := new java.sql.Date(now.getTime());
prs.setDate(2, sqlDate);
prs.setDate(3, sqlDate);
回答by Cristian Meneses
The problem you're having is that you're passing incompatible formats from a formatted java.util.Date to construct an instance of java.sql.Date, which don't behave in the same way when using valueOf()since they use different formats.
您遇到的问题是您从格式化的 java.util.Date 传递不兼容的格式来构造 的实例java.sql.Date，valueOf()由于它们使用不同的格式，因此在使用时的行为方式不同。
I also can see that you're aiming to persist hours and minutes, and I think that you'd better change the data type to java.sql.Timestamp, which supports hours and minutes, along with changing your database field to DATETIME or similar (depending on your database vendor).
我还可以看到您的目标是持续数小时和分钟，我认为您最好将数据类型更改为java.sql.Timestamp支持小时和分钟的数据类型，同时将您的数据库字段更改为 DATETIME 或类似（取决于您的数据库）小贩）。
Anyways, if you want to change from java.util.Date to java.sql.Date, I suggest to use
无论如何，如果您想从 更改java.util.Date to java.sql.Date，我建议使用
java.util.Date date = Calendar.getInstance().getTime();
java.sql.Date sqlDate = new java.sql.Date(date.getTime());
// ... more code here
prs.setDate(sqlDate);
回答by Darth Jon
Not sure, but what I think you're looking for is to create a java.util.Date from a String, then convert that java.util.Date to a java.sql.Date.
不确定，但我认为您正在寻找的是从字符串创建 java.util.Date，然后将该 java.util.Date 转换为 java.sql.Date。
try this:
尝试这个：
private static java.sql.Date getCurrentDate(String date) {
java.util.Date today;
java.sql.Date rv = null;
try {
SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy");
today = format.parse(date);
rv = new java.sql.Date(today.getTime());
System.out.println(rv.getTime());
} catch (Exception e) {
System.out.println("Exception: " + e.getMessage());
} finally {
return rv;
}
}
Will return a java.sql.Date object for setDate();
将为 setDate() 返回一个 java.sql.Date 对象；
The function above will print out a long value:
上面的函数将打印出一个长值：
1375934400000
1375934400000
回答by Glenn
If you want to add the current date into the database, I would avoid calculating the date in Java to begin with. Determining "now" on the Java (client) side leads to possible inconsistencies in the database if the client side is mis-configured, has the wrong time, wrong timezone, etc. Instead, the date can be set on the server side in a manner such as the following:
如果您想将当前日期添加到数据库中，我会避免在 Java 中计算日期。如果客户端配置错误、时间错误、时区错误等，在 Java（客户端）端确定“现在”可能会导致数据库中的不一致。相反，可以在服务器端设置日期方式如下：
requestSQL = "INSERT INTO CREDIT_REQ_TITLE_ORDER (" +
"REQUEST_ID, ORDER_DT, FOLLOWUP_DT) " +
"VALUES(?, SYSDATE, SYSDATE + 30)";
...
prs.setInt(1, new Integer(requestID));
This way, only one bind parameter is required and the dates are calculated on the server side will be consistent. Even better would be to add an insert trigger to CREDIT_REQ_TITLE_ORDERand have the trigger insert the dates. That can help enforce consistency between different client apps (for example, someone trying to do a fix via sqlplus.
这样，只需要一个绑定参数，并且在服务器端计算的日期将保持一致。更好的是添加一个插入触发器CREDIT_REQ_TITLE_ORDER并让触发器插入日期。这有助于加强不同客户端应用程序之间的一致性（例如，有人试图通过 sqlplus 进行修复。
回答by Basil Bourque
tl;dr
tl;博士
With JDBC 4.2 or later and java 8 or later:
使用 JDBC 4.2 或更高版本和 java 8 或更高版本：
myPreparedStatement.setObject( … , myLocalDate )
…and…
…和…
myResultSet.getObject( … , LocalDate.class )
Details
细节
The Answer by Vargas is good about mentioning java.time types but refers only to converting to java.sql.Date. No need to convert if your driver is updated.
Vargas 的答案很好地提到了 java.time 类型，但仅指转换为 java.sql.Date。如果您的驱动程序已更新，则无需转换。
java.time
时间
The java.timeframework is built into Java 8 and later. These classes supplant the old troublesome date-time classes such as java.util.Date, .Calendar, & java.text.SimpleDateFormat. The Joda-Timeteam also advises migration to java.time.
该java.time框架是建立在Java 8和更高版本。这些类取代了旧的麻烦的日期时间类，例如java.util.Date, .Calendar, & java.text.SimpleDateFormat。该乔达时球队还建议迁移java.time。
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations.
要了解更多信息，请参阅Oracle 教程。并在 Stack Overflow 上搜索许多示例和解释。
Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backportand further adapted to Android in ThreeTenABP.
多的java.time功能后移植到Java 6和7在ThreeTen-反向移植和在进一步适于到Android ThreeTenABP。
LocalDate
LocalDate
In java.time, the java.time.LocalDateclass represents a date-only value without time-of-day and without time zone.
在 java.time 中，java.time.LocalDate该类表示没有时间和时区的仅日期值。
If using a JDBC drivercompliant with JDBC 4.2or later spec, no need to use the old java.sql.Dateclass. You can pass/fetch LocalDateobjects directly to/from your database via PreparedStatement::setObjectand ResultSet::getObject.
如果使用符合JDBC 4.2或更高版本规范的JDBC 驱动程序，则无需使用旧类。您可以通过/取通过直接从您的数据库对象/和。java.sql.DateLocalDatePreparedStatement::setObjectResultSet::getObject
LocalDate localDate = LocalDate.now( ZoneId.of( "America/Montreal" ) );
myPreparedStatement.setObject( 1 , localDate );
…and…
…和…
LocalDate localDate = myResultSet.getObject( 1 , LocalDate.class );
Before JDBC 4.2, convert
在 JDBC 4.2 之前，转换
If your driver cannot handle the java.time types directly, fall back to converting to java.sql types. But minimize their use, with your business logic using only java.time types.
如果您的驱动程序无法直接处理 java.time 类型，请回退到转换为 java.sql 类型。但是尽量减少它们的使用，您的业务逻辑只使用 java.time 类型。
New methods have been added to the old classes for conversion to/from java.time types. For java.sql.Datesee the valueOfand toLocalDatemethods.
新方法已添加到旧类中，用于在 java.time 类型之间进行转换。对于java.sql.Date看到valueOf和toLocalDate方法。
java.sql.Date sqlDate = java.sql.Date.valueOf( localDate );
…and…
…和…
LocalDate localDate = sqlDate.toLocalDate();
Placeholder value
占位符值
Be wary of using 0000-00-00as a placeholder value as shown in your Question's code. Not all databases and other software can handle going back that far in time. I suggest using something like the commonly-used Unix/Posix epoch reference dateof 1970, 1970-01-01.
小心使用0000-00-00作为占位符值，如您的问题代码所示。并非所有数据库和其他软件都可以处理那么远的时间。我建议使用诸如常用的Unix/Posix 纪元参考日期1970 之类的东西1970-01-01。
LocalDate EPOCH_DATE = LocalDate.ofEpochDay( 0 ); // 1970-01-01 is day 0 in Epoch counting.
About java.time
关于java.time
The java.timeframework is built into Java 8 and later. These classes supplant the troublesome old legacydate-time classes such as java.util.Date, Calendar, & SimpleDateFormat.
该java.time框架是建立在Java 8和更高版本。这些类取代了麻烦的旧的遗留日期时间类，例如java.util.Date, Calendar, & SimpleDateFormat。
The Joda-Timeproject, now in maintenance mode, advises migration to the java.timeclasses.
现在处于维护模式的Joda-Time项目建议迁移到java.time类。
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
要了解更多信息，请参阅Oracle 教程。并在 Stack Overflow 上搜索许多示例和解释。规范是JSR 310。
You may exchange java.timeobjects directly with your database. Use a JDBC drivercompliant with JDBC 4.2or later. No need for strings, no need for java.sql.*classes.
您可以直接与您的数据库交换java.time对象。使用符合JDBC 4.2或更高版本的JDBC 驱动程序。不需要字符串，不需要类。java.sql.*
Where to obtain the java.time classes?
从哪里获得 java.time 类？
- Java SE 8, Java SE 9, Java SE 10, and later
- Built-in.
- Part of the standard Java API with a bundled implementation.
- Java 9 adds some minor features and fixes.
- Java SE 6and Java SE 7
- Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
- Android
- Later versions of Android bundle implementations of the java.time classes.
- For earlier Android (<26), the ThreeTenABPproject adapts ThreeTen-Backport(mentioned above). See How to use ThreeTenABP….
- Java SE 8、Java SE 9、Java SE 10及更高版本
- 内置。
- 具有捆绑实现的标准 Java API 的一部分。
- Java 9 添加了一些小功能和修复。
- Java SE 6和Java SE 7
- 多的java.time功能后移植到Java 6和7在ThreeTen-反向移植。
- 安卓
- 更高版本的 Android 捆绑实现 java.time 类。
- 对于早期的 Android（<26），ThreeTenABP项目采用了ThreeTen-Backport（上面提到过）。请参阅如何使用ThreeTenABP ...。
The ThreeTen-Extraproject extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.
该ThreeTen-额外项目与其他类扩展java.time。该项目是未来可能添加到 java.time 的试验场。你可能在这里找到一些有用的类，比如Interval，YearWeek，YearQuarter，和更多。
