NLS_DATE_FORMAT与JDBC [英] NLS_DATE_FORMAT with JDBC

问题描述

我试图在jdbc中设置NLS_DATE_FORMAT，似乎没有任何效果。
我的代码：

I tried to set NLS_DATE_FORMAT inside jdbc, it didn't seem to have any effect. My code :

//...
Connection conn = ods.getConnection();
Statement stat = conn.createStatement();

stat.execute("alter session set NLS_DATE_FORMAT='YYYY-DD-MM'");
ResultSet rs = stat.executeQuery("select date_column from test_table");

System.out.println(rs.getString(1));   // *** new format not effective here ***
//...

经过一番阅读我知道NLS_DATE_FORMAT是硬编码的JDBC驱动程序。它是否正确 ？由此得出结论：

After some reading. I understand that NLS_DATE_FORMAT is hard coded in JDBC Drivers. is this correct ? conclusion made from this :

语言和领土

Language and Territory

瘦驱动程序获取语言和区域设置（NLS_LANGUAGE和NLS_TERRITORY）从Java区域设置在JVM user.language属性。日期格式（NLS_DATE_FORMAT）根据区域设置设置。

The Thin driver obtains language and territory settings (NLS_LANGUAGE and NLS_TERRITORY) from the Java locale in the JVM user.language property. The date format (NLS_DATE_FORMAT) is set according to the territory setting.

所以我试过这个：

Locale.setDefault(new Locale("en", "US"));
Connection conn = ods.getConnection();
Statement stat = conn.createStatement();

ResultSet rs = stat.executeQuery("select date_column from test_table");

System.out.println(rs.getString(1));   // *** JVM format not effective here 

但它不工作。而不是像

10-OCT-15 (NLS_DATE_FORMAT for US = 'DD-MON-RR')

我得到

2015-10-10 00:00:00.0

使用ORACLE 11g，Oracle jdbc驱动12.1， java 8

Using ORACLE 11g, Oracle jdbc drive 12.1, java 8

推荐答案

JDBC驱动程序的NLS支持是删除在Oracle 10g中。无论如何，我建议您不要依赖Oracle特定的NLS功能来格式化日期。

NLS support from the JDBC driver was dropped in Oracle 10g. Regardless, I'd recommend that you not lean on the Oracle-specific NLS functionality to format your dates.

要格式化从Java中的任何数据库检索的日期，请执行此操作：

To format a date retrieved from any database in Java, do this:

Connection conn = ods.getConnection();
Statement stat = conn.createStatement();

ResultSet rs = stat.executeQuery("select date_column from test_table");

DateFormat format = new SimpleDateFormat('yyyy-dd-MM');
System.out.println(format.format(rs.getDate(1));

请注意，SimpleDateFormat的一个实例是可重用但不是线程安全。

Note that an instance of SimpleDateFormat is reusable but not thread safe.

如果您使用的是Java 8，则可能需要使用新的和改进的Java Time API，不幸的是，ResultSet还没有被更新以返回DateTime的实例，所以你必须转换。查看 DateTimeFormatter 和这个问题关于如何从旧的转换为新的。其他优点，DateTimeFormatter是完全线程安全的。

If you're using Java 8, you might want to use the new and improved Java Time API instead. Unfortunately, ResultSet hasn't been updated to return an instance of DateTime yet, so you have to convert. Check out DateTimeFormatter and this question about how to convert from old to new. Among other advantages, DateTimeFormatter is fully thread safe.

旧样式和新样式的格式模式是一样的; 这里是新的文档。

The format patterns for both the old and new style are the same; here are the docs for the new one.

这篇关于NLS_DATE_FORMAT与JDBC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！