Date and Time API をJDBCで扱ってみる #kanjava

関西Javaエンジニアの会(関ジャバ) '17 4月度
https://kanjava.connpass.com/event/52663/

Transcript

1. Date and Time API Λ JDBCͰѻͬͯΈΔ 2017/04/26 ؔJava '17 4݄౓ @hijiri408
2. ࣗݾ঺հ • Ṥ৲ɹਅ໵ʢͻ͡Γɹ͠Μ΍ʣ • Twitter: @hijiri408 • Java ΤϯδχΞʢϑϦʔϥϯεʣ • SIer Ͱͷ։ൃҊ͕݅ओ
3. ໨࣍ • Date and Time API ͷ؆୯ͳ঺հ • JDBCͷ؆୯ͳ঺հ • JDBCͷ Date and Time API ରԠ • ະରԠͷJDBCυϥΠόͰ Date and Time API Λ࢖͏
4. JDBCΛ௚઀࢖͏͜ͱ͸͋Δʁ • ੲ͸Α͘࢖ΘΕ͍ͯͨ • O/RϚούʔͱ͔࢖͍ग़͢ͱ࢖Θͳ͍ΑͶ • ج൫։ൃͰϑϨʔϜϫʔΫ΍ϥΠϒϥϦΛͭ͘Δ ଆʹͳΔ͜ͱ͕ଟ͘ɺO/RϚούʔ͸͋·Γ࢖ͬ ͨ͜ͱ͕ͳ͍ (Doma ver1 ͚ͩ࢖ͬͨ͜ͱ͕͋Δ)
5. σʔλΞΫηεܥϥΠϒϥϦ ͷίʔυϦʔσΟϯά΍ ֦ுΛ͢Δ࣌ɺ গ͠͸໾ʹཱ͔ͭ΋͠Εͳ͍
6. Date and Time API ͷ ؆୯ͳ঺հ
7. Java 7 ·Ͱͷ೔෇΍࣌ࠁૢ࡞ • java.util.Date ΍ java.util.Calendar ͕࣠ • UNIX ࣌ؒ Λϕʔεʹ͓ͯ͠ΓɺΤϙοΫ͔Βͷܦա࣌ ؒΛϛϦඵͷਫ਼౓Ͱอ͍࣋ͯ͠Δ • ௚ײతͰͳ͔ͬͨΓ࢖͍ͮΒ͍ϝιου΋ଟ͍ • Commons Lang ౳ͷϥΠϒϥϦͰ࢖͍ͮΒ͞ΛΧόʔ • ϛϡʔλϒϧͰ͋ΓɺεϨουΞϯηʔϑ
8. Date and Time API (JSR 310) • Java 8 Ͱಋೖ͞Εͨ೔෇ͱ࣌ࠁΛѻ͏API • ೔෇΍࣌ࠁͷදݱʹؔ͢Δࠃࡍن֨Ͱ͋Δ ISO 8601 Λ ϕʔεʹ͍ͯ͠Δɹ(→ϕʔε͔ΒҟͳΔ) • ೔෇΍࣌ࠁΛදݱͰ͖Δଟ͘ͷΫϥε • ௚ײతʹૢ࡞Ͱ͖Δϝιου܈ • ΠϛϡʔλϒϧͰ͋ΓɺεϨουηʔϑ
9. ೔෇΍࣌ࠁΛද͢ओͳΫϥε Ϋϥε આ໌ LocalDate ೔෇Λѻ͏ LocalTime ࣌ࠁΛѻ͏ LocalDateTime ೔෇ͱ࣌ࠁΛѻ͏ OffsetTime UTC͔Βͷ࣌ࠩͰɺ࣌ࠁΛѻ͏ OffsetDateTime UTC͔Βͷ࣌ࠩͰɺ೔෇ͱ࣌ࠁΛѻ͏ ZonedDateTime λΠϜκʔϯͰɺ೔෇ͱ࣌ࠁΛѻ͏
10. OffsetTimeͱOffsetDateTime • ISO 8601 ͰѻΘΕΔ࣌ࠩ (஍ํ࣌ͱUTCͱͷࠩ) Λѻ ͏ • ೔ຊͩͱ9࣌ؒਐΜͰ͍ΔͷͰʮ+09:00ʯ • Ն࣌ؒͰ͸1࣌ؒ܁Γ্͕͕ͬͯ࣌ࠩมΘΔͨΊɺظ ؒதʹ͸࣌ࠩͷѻ͍ʹ஫ҙ͕ඞཁ • TemporalAdjusterΛར༻ͯ͠ղܾ͢Δํ๏΋͋Δ
11. ZonedDateTime • ISO 8601 Ͱ͸ѻΘΕͳ͍λΠϜκʔϯΛѻ͏ • ZoneIDͰࣝผ͞Εɺ೔ຊͩͱʮAsia/Tokyoʯ • Ն͕࣌ؒαϙʔτ͞Ε͓ͯΓɺద੾ͳ࣌ࠩʹͳΔ • ద੾ͳঢ়ଶΛҡ࣋͢Δʹ͸ɺJRE͕อ࣋͢ΔλΠϜκʔ ϯDBΛɺJREͷόʔδϣϯΞοϓɺ΋͘͠͸tzupdater πʔϧͰߋ৽͍ͯ͘͠ඞཁ͕͋Δ
12. JDBCͷ؆୯ͳ঺հ
13. JDBCͱ͸ • Java͔ΒDBΛૢ࡞͢ΔͨΊͷAPI • JSR 221 Ͱ࢓༷ࡦఆ • ࠷৽όʔδϣϯ͸ 4.2 • ࣮ࡍʹDBΛૢ࡞͢ΔͨΊʹ͸ɺͦͷDB༻ͷ JDBCυϥΠόʔ ͕ඞཁ
14. JDBCυϥΠόʔͱ͸ • ࣮ࡍʹDB΁ͷ઀ଓ΍ૢ࡞Λߦ͏࣮૷ • ઀ଓํ๏͕ҟͳΔ TYPE 1 ʙ 4 ͷछྨ͕͋Δ • ओͳDB޲͚ͷJDBCυϥΠόʔ͸ɺ֤DBMSͷϕϯ μʔ͔Βఏڙ͞Ε͍ͯΔ • εϓϨουγʔτ΍ϑϥοτϑΝΠϧΛૢ࡞͢ΔͨΊ ͷJDBCυϥΠόʔ΋͋Δ
15. DB΍SQLͷܕͱͷϚοϐϯά • JDBCͷ಺෦ͰɺDB΍SQLͷܕΛந৅Խͨ͠JDBCܕ ͕ఆٛ͞Ε͍ͯΔ (java.sql.Types) • σʔλͱͯ͠ѻ͑ΔJavaͷܕ͸ɺରԠ͢ΔJDBCܕ͕ ఆΊΒΕ͍ͯΔ • ֤JDBCυϥΠόͰ͸ɺJDBCܕ΍JavaͷܕʹԠ͡ ͯɺ࣮ࡍͷDB΍SQLͷܕʹϚοϐϯάͤ͞Δ
16. ೔෇΍࣌ࠁͷܕϚοϐϯά • java.util.Date Ͱ͸ͳ͘ɺjava.sql ύοέʔδ ʹ͋Δ೔෇΍࣌ࠁͷΫϥεΛ࢖͏ Java ܕ JDBC ܕ java.sql.Date DATE java.sql.Time TIME java.sql.Timestamp TIMESTAMP
17. java.sql ͷ೔෇΍࣌ࠁͷΫϥε • Date͸೔෇ɺTime͸࣌ࠁɺTimestamp͸೔෇ͱ࣌ࠁΛѻ͏ • ͦΕͧΕͱ΋ java.util.Date ͷαϒΫϥε • Timestamp͸ java.util.Date ͱͯ͠ѻ͏ͳͱJavadocʹ஫ ҙॻ͖ (ඵະຬΛಠࣗʹอ͍࣋ͯ͠ΔͨΊ) • Timestamp͸φϊඵͷਫ਼౓·ͰදͤΔͨΊɺjava.util.Date ΁ม׵͢ΔͱϛϦඵະຬ͕ܽམ͢ΔͷͰ஫ҙ
18. JDBC ͷ Date and Time API ରԠ
19. JSR 221 JDBC API Spec 4.2 • Additional Mappings to Table B-4, Mapping from Java Object to JDBC Types • Added support to map java.time.LocalDate to JDBC DATE. • Added support to map java.time.LocalTime to JDBC TIME. • Added support to map java.time.LocalDateTime to JDBC TIMESTAMP. • Added support to map java.time.LocalOffsetTime to JDBC TIME_WITH_TIMEZONE. • Added support to map java.time.LocalOffsetDateTime to JDBC TIMESTAMP_WITH_TIMEZONE. https://jcp.org/aboutJava/communityprocess/mrel/jsr221/index2.html
20. JSR 221 JDBC API Spec 4.2 • Additional Mappings to Table B-6, Use ResultSet getter Methods to retrieve JDBC Types • Allow getObject to return TIME_WITH_TIMEZONE, TIMESTAMP_WITH_TIMEZONE. • JDBC API changes • Types • Added the types REF_CURSOR, TIME_WITH_TIMEZONE, and TIMESTAMP_WITH_TIEMZONE. https://jcp.org/aboutJava/communityprocess/mrel/jsr221/index2.html
21. JDBC 4.2 Ͱαϙʔτ • JDBCܕʹɺOffsetTimeʹରԠ͢Δ TIME_WITH_TIMEZONE ɺ OffsetDateTimeʹରԠ͢Δ TIMESTAMP_WITH_TIMEZONE Λ௥ Ճ • PreparedStatement౳ͷsetObjectϝιουʹ౉ͤΔJavaΦϒδΣ Ϋτͷܕͱͯ͠ɺલड़ͷ೔෇΍࣌ࠁΛѻ͏ओͳΫϥεͷ͏ͪɺ ZonedDateTimeҎ֎ʹରԠ • ResultSetͷgetObjectϝιου͔Βฦ͞ΕΔJDBCܕͱͯ͠ɺ TIME_WITH_TIMEZONEɺTIMESTAMP_WITH_TIMEZONEΛ௥Ճ
22. JDBCυϥΠόʔͷରԠঢ়گ %# +%#$
    
    αϙʔτόʔδϣϯ 0SBDMF  .Z42-  1PTUHSF42- 
    #VJME
     %# 
    ʹ͸ରԠ͍ͯ͠ΔΆ͍ 42-
    4FSWFS 
23. JDBCυϥΠόʔͷରԠঢ়گ • جຊతʹ͸ͲΕ΋ରԠ͞Ε͍ͯΔ • DB2͚ͩʮ JDBC 4.0 ରԠʯͱ͔͠෼͔Βͳ͔ͬͨ (IBMͷα Πτ͕ॏͯ͘୳͢ͷπϥ͍) • ৽ػೳ͕Ͳ͜·Ͱαϙʔτ͞Ε͍ͯΔ͔͸JDBCυϥΠόʔͷ ࣮૷ʹΑΔ • ྫ͑͹MySQLͰ͸ɺ৽͍͠JDBCܕͷ TIME_WITH_TIMEZONE ͸αϙʔτ͞Ε͍ͯͳ͍౳
24. setObjectͷࣗಈϚοϐϯά • setObjectͰJDBCܕΛ໌ࣔ͠ͳ͍৔߹ɺҎԼͷΑ͏ʹ Ϛοϐϯά͞ΕΔ Java ܕ JDBC ܕ java.util.LocalDate DATE java.util.LocalTime TIME java.util.LocalDateTime TIMESTAMP java.time.OffsetTime TIME_WITH_TIMEZONE java.time.OffsetDateTime TIMESTAMP_WITH_TIMEEZONE JDBC 4.2 Ͱ ௥Ճ͞Εͨ
25. ྫɿOracleͰͷܕϚοϐϯά +BWB
    ܕ +%#$
    ܕ 0SBDMF
    σʔλ
    ܕ KBWBTRM%BUF %"5& %"5& KBWBTRM5JNF 5*.& %"5& KBWBTRM5JNFTUBNQ 5*.&45".1 5*.&45".1 KBWBUJNF0⒎TFU5JNF 5*.&
    8*5)
    5*.&;0/& KBWBUJNF0⒎TFU%BUF 5JNF 5*.&45".1
    8*5)
    5*.&;0/& υΩϡϝϯτͷରԠද ʹهࡌ͕ແ͔ͬͨ…
26. ಈ͔ͯ͠Έͨ؀ڥ • Oracle 12c Release 1 (12.1.0.2) • ઌि Docker Store Ͱެ։͞Ε͍ͯͨ • ࠷ॳ͸MySQLΛߟ͕͑ͨɺλΠϜκʔϯ෇͖ͷܕ ͕ແ͘ɺ৽͍͠JDBCܕ͕ࢼͤͳ͍ͷͰࢭΊͨ • JDBC 4.2 Λαϙʔτ͍ͯ͠Δ 12.2.0.1 ͷJDBCυ ϥΠόʔ͕࢖͑Δ
27. ద౰ͳςʔϒϧΛ༻ҙ ྻ໊ σʔλܕ આ໌ JE 7"3$)"3 ࣝผ஋ EU %"5& ೔෇༻ EU@DIBS 7"3$)"3 ೔෇ͷจࣈྻԽ༻ UT 5*.&45".1 ೔࣌༻ UT@U[ 5*.&45".1
    8*5)
    5*.&;0/& λΠϜκʔϯ༻
28. setObjectͰLocalDate PreparedStatement stmt = conn.prepareStatement( "UPDATE sample SET dt=? WHERE id='hoge'"); // LocalDateΦϒδΣΫτΛDATEܕʹ౤ೖ stmt.setObject(1, LocalDate.of(2016, 4, 26)); SELECT TO_CHAR(dt, 'yyyy/mm/dd') as dt FROM sample WHERE id='hoge'; dt —————- 2016/04/26 LocalDateͷ೔͕࣌ొ࿥͞Ε͍ͯΔ
29. setObjectͰLocalDateTime PreparedStatement stmt = conn.prepareStatement( "UPDATE sample SET ts=? WHERE id='hoge'"); // LocalDateTimeΦϒδΣΫτΛTIMESTAMPܕʹ౤ೖ stmt.setObject(1, LocalDateTime.of( 2016, 4, 26, 10, 11, 12, 123456789)); SELECT TO_CHAR(ts, 'yyyy/mm/dd hh24:mi:ss.ff9') as ts FROM sample WHERE id='hoge'; ts —————- 2016/04/26 10:11:12.123456789 LocalDateTimeͷφϊඵΛؚΉ ೔͕࣌ొ࿥͞Ε͍ͯΔ
30. setObjectͰLocalDateTime PreparedStatement stmt = conn.prepareStatement( "UPDATE sample SET dt=? WHERE id='hoge'"); // LocalDateTimeΦϒδΣΫτΛDATEܕʹ౤ೖ stmt.setObject(1, LocalDateTime.of( 2016, 4, 26, 10, 11, 12, 123456789)); SELECT TO_CHAR(dt, 'yyyy/mm/dd hh24:mi:ss') as dt FROM sample WHERE id='hoge'; dt —————- 2016/04/26 10:11:12 LocalDateTimeʹ͸TIMESTAMP͕ର Ԡ͢Δ͕ɺOracleͷJDBCυϥΠόʔ Ͱ͸DATEܕʹ΋౤ೖͰ͖Δ (อ࣋Ͱ͖ͳ͍ඵະຬ͸ܽམ)
31. setObjectͰOffsetDateTime PreparedStatement stmt = conn.prepareStatement( "UPDATE sample SET ts_tz=? WHERE id='hoge'"); // OffsetDateTimeΦϒδΣΫτΛTIMESTAMP WITH TIMEZONEܕʹ౤ೖ stmt.setObject(1, OffsetDateTime.of( 2016, 4, 26, 10, 11, 12, 123456789, ZoneOffset.of("+08:00")); SELECT TO_CHAR(ts_tz, 'yyyy/mm/dd hh24:mi:ss.ff9 tzh:tzm') as ts_tz FROM sample WHERE id='hoge'; ts_tz —————- 2016/04/26 10:11:12.123456789 +08:00 OffsetDateTimeͷ࣌ࠩͷ ··ొ࿥͞Ε͍ͯΔ
32. ະରԠͷόʔδϣϯͰsetObject java.sql.SQLException: ྻͷܕ͕ແޮͰ͢ɻ at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePr eparedStatement.java:10495) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePr eparedStatement.java:9974) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePr eparedStatement.java:10799) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedSt atement.java:10776) at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePre paredStatementWrapper.java:241) ྫ֎͕ૹग़͞ΕΔ ʢϝοηʔδ΋෼͔Δʣ
33. setObjectͰܕΛ໌ࣔ • setObjectͰJDBCܕΛ໌ࣔ͢Δ৔߹ɺҎԼͷJDBCܕΛࢦ ఆͰ͖Δ Java ܕ JDBC ܕ java.util.LocalDate CHARɺVARCHARɺ LONGVARCHARɺDATE java.util.LocalTime CHARɺVARCHARɺ LONGVARCHARɺTIME ʢଓ͘ʣ
34. setObjectͰܕΛ໌ࣔ Java ܕ JDBC ܕ java.util.LocalDateTime CHARɺVARCHARɺ LONGVARCHARɺDATEɺTIMEɺ TIMESTAMP java.time.OffsetTime CHARɺVARCHARɺ LONGVARCHARɺ TIME_WITH_TIMEZONE java.time.OffsetDateTime CHARɺVARCHARɺ LONGVARCHARɺ TIMESTAMP_WITH_TIMEEZONE
35. LocalDateΛVARCHARࢦఆ PreparedStatement stmt = conn.prepareStatement( "UPDATE sample SET dt_char=? WHERE id='hoge'"); // LocalDateΦϒδΣΫτΛVARCHARΛࢦఆͯ͠VARCHAR2ܕʹ౤ೖ stmt.setObject(1, LocalDate.of(2016, 4, 26), JDBCType.VARCHAR); SELECT dt_char FROM sample WHERE id='hoge'; dt_char —————- 2016-04-26 ͦΕͬΆ͘จࣈྻͰొ࿥͞Εͨ (ϑΥʔϚοτ͸JDBCυϥΠόґଘʁ)
36. LocalDateΛVARCHARܕ΁ PreparedStatement stmt = conn.prepareStatement( "UPDATE sample SET dt_char=? WHERE id='hoge'"); // LocalDateΦϒδΣΫτΛVARCHARΛࢦఆͤͣʹVARCHAR2ܕʹ౤ೖ stmt.setObject(1, LocalDate.of(2016, 4, 26)); SELECT dt_char FROM sample WHERE id='hoge'; dt_char —————- 16-04-26 00:00:00.000000000 ࢦఆ͠ͳͯ͘΋౤ೖͰ͖Δɻ JDBCυϥΠόʔ࣍ୈ͕ͩɺܕ Ϛοϐϯά͸ׂͱϧʔζͳײ͡
37. setObjectͰܕΛ໌ࣔ • Ҏલ͸ java.sql.Types ʹఆٛ͞Εͨ int ஋Ͱࢦఆ͍ͯͨ͠ • JDBC 4.2 ͔Β enum ͷ java.sql.JDBCType ͕௥Ճ͞Εɺ setObjectͷܕࢦఆʹ౉ͤΔΑ͏ʹͳͬͨ • Ҿ਺ͱͯ͠ java.sql.JDBCType Λαϙʔτ͍ͯ͠Δ͔͸ JDBCυϥΠόʔ࣍ୈɻྫ͑͹ɺPostgreSQLͰ͸ SQLFeatureNotSupportedException ͕ૹग़͞ΕΔɻ
38. getObjectͰऔಘ • औಘ͢ΔࡍͷܕΛɺҾ਺ʹࢦఆͰ͖Δ getObject Ͱαϙʔτ͞Ε͍ͯΔ JDBC ܕ TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC, BIT, BOOLEAN, CHAR, VARCHAR, LONGVARCHAR, BINARY, VARBINARY, LONVARBINARY, DATE, TIME, TIMESTAMP, CLOB, BLOB, ARRAY, REF, DATALINK, STRUCT, JAVA_OBJECT, ROWID, NCHAR, NVARCHAR, LONGNVARCHAR, NCLOB, SQLXML, TIME_WITH_TIMEZONE, TIMESTAMP_WITH_TIMEZONE
39. getObjectͰLocalDate // DATEܕΛLocalDateͰऔಘ LocalDate result = resultSet.getObject("dt", LocalDate.class); assertThat(result, is(LocalDate.of(2016, 4, 26))); SELECT TO_CHAR(dt, 'yyyy/mm/dd') as dt FROM sample WHERE id='hoge'; dt —————- 2016/04/26 LocalDateͱͯ͠औಘͰ͖Δ
40. getObjectͰLocalDateTime // TIMESTAMPܕΛLocalDateTimeͰऔಘ LocalDateTime result = resultSet.getObject("ts", LocalDateTime.class); assertThat(result, is(LocalDateTime.of( 2016, 4, 26, 10, 11, 12, 123456789)); SELECT TO_CHAR(ts, 'yyyy/mm/dd hh24:mi:ss.ff9') as ts FROM sample WHERE id='hoge'; ts —————- 2016/04/26 10:11:12.123456789 φϊඵͷਫ਼౓ͷ·· LocalDateTime ͱͯ͠औಘͰ͖Δ
41. getObjectͰOffsetDateTime // TIMESTAMP WITH TIMEZONEܕΛOffsetDateTimeͰऔಘ OffsetDateTime result = resultSet.getObject("ts_tz", OffsetDateTime.class); assertThat(result, is(OffsetDateTime.of( 2016, 4, 26, 10, 11, 12, 123456789, ZoneOffset.of("+08:00")); SELECT TO_CHAR(ts_tz, 'yyyy/mm/dd hh24:mi:ss.ff9 tzh:tzm') as ts_tz FROM sample WHERE id='hoge'; ts_tz —————- 2016/04/26 10:11:12.123456789 +08:00 ࣌ࠩ΋ͦͷ·· OffsetDateTime ͱͯ͠औಘͰ͖Δ
42. ະରԠͷόʔδϣϯͰgetObject java.sql.SQLException: ྻͷܕ͕ແޮͰ͢ɻ at oracle.jdbc.driver.Redirector$2.redirect(Redirector.java:264) at oracle.jdbc.driver.Representation.getObject(Representation.java:436) at oracle.jdbc.driver.Accessor.getObject(Accessor.java:1014) at oracle.jdbc.driver.OracleStatement.getObject(OracleStatement.java: 6697) at oracle.jdbc.driver.InsensitiveScrollableResultSet.getObject(Insensiti veScrollableResultSet.java:912) at oracle.jdbc.driver.OracleResultSet.getObject(OracleResultSet.java: 1120) ྫ֎͕ૹग़͞ΕΔ ʢϝοηʔδ΋෼͔Δʣ
43. ະରԠͷJDBCυϥΠόͰ Date and Time API Λ࢖͏
44. ະରԠͷJDBCυϥΠόͰ͸ • setObject΍getObjectͰ௚઀ Date and Time API ͷΫ ϥεΛѻ͓͏ͱͨ͠Βɺલड़ͷͱ͓Γྫ֎͕ૹग़͞Ε ͯ͠·͏ • JDBCυϥΠόͰѻ͏ࡍʹ java.sql ύοέʔδͷΫϥ εʹͳ͍ͬͯΕ͹Α͍ • Date and Time API ͷΫϥεͱ java.sql ύοέʔδͷ Ϋϥεͱͷ૬ޓม׵͕Ͱ͖Ε͹ղܾ͢Δ
45. java.sql ͷΫϥε΁ͷม׵ • java.sql ͷΫϥεʹ valueOf ͱ͍͏ϑΝΫτϦ ϝιου͕௥Ճ͞Ε͍ͯΔ • ͨͩ͠ɺjava.sql ͷΫϥε͕࣌ࠩ৘ใΛอ࣋Ͱ ͖ͳ͍ͨΊɺ࣌ࠩΛѻ͏Ϋϥε͔Β͸ม׵Ͱ ͖ͳ͍
46. LocalDateΛDate΁ม׵ PreparedStatement stmt = conn.prepareStatement( "UPDATE sample SET dt=? WHERE id='hoge'"); LocalDate date = LocalDate.of(2016, 4, 26); stmt.setObject(1, java.sql.Date.valueOf(date)); SELECT TO_CHAR(dt, 'yyyy/mm/dd') as dt FROM sample WHERE id='hoge'; dt —————- 2016/04/26 valueOf ϝιουͰੜ੒Ͱ͖Δ
47. • java.sql ͷΫϥεʹม׵ϝιου͕௥Ճ͞Εͯ ͍Δ java.sql ͷΫϥε͔Βͷม׵ ม׵ݩ ϝιου ม׵ޙ KBWBTRM%BUF UP-PDBM%BUF KBWBUJNF-PDBM%BUF KBWBTRM5JNF UP-PDBM5JNF KBWBUJNF-PDBM5JNF KBWBTRM5JNFTUBNQ UP-PDBM%BUF5JNF KBWBUJNF-PDBM%BUF5JNF
48. Date͔ΒLocalDate΁ม׵ java.sql.Date date = resultSet.getDate("dt"); LocalDate result = date.toLocalDate(); assertThat(result, is(LocalDate.of(2016, 4, 26))); SELECT TO_CHAR(dt, 'yyyy/mm/dd') as dt FROM sample WHERE id='hoge'; dt —————- 2016/04/26
49. ࣌ࠩΛѻ͏Ϋϥεͱͷม׵ • JDBCυϥΠόͰಠ֦ࣗுͨ࣌ࠩ͠Λѻ͏Ϋϥ εΛ࢖͏ (OracleͳΒ oracle.sql.TIMESTAMPTZ ౳) • ࣌ࠩ৘ใ͸ผͷखஈͰΧόʔ͢Δ • ࣌ࠩ΍λΠϜκʔϯ͸ผͷྻʹొ࿥͢Δ
50. OffsetDatetime͔Βม׵ PreparedStatement stmt = conn.prepareStatement( "UPDATE sample SET ts=?, ts_offset=? WHERE id='hoge'"); OffsetDateTime offsetDateTime = OffsetDateTime.of( 2016, 4, 26, 10, 11, 12, 123456789, ZoneOffset.of("+08:00")); // ࣌ࠩ৘ใΛഁغͯ͠LocalDateTime΁Ұ୴ม׵͠ɺ͔ͦ͜ΒTimestampʹม׵͢Δ LocalDateTime localDateTime = offsetDateTime.toLocalDateTime(); stmt.setObject(1, java.sql.Timestamp.valueOf(localDateTime)); // ࣌ࠩ৘ใͷΈผͷྻ΁౤ೖ stmt.setObject(2, offsetDateTime.getOffset().getId());
51. OffsetDateTime΁ม׵ Timestamp timestamp = resultSet.getTimestamp("ts"); String offset = resultSet.getString("ts_offset"); // Ұ୴LocalDateTime΁ม׵͠ɺ࣌ࠩ৘ใΛϓϥεͯ͠OffsetDateTime΁ม׵ LocalDateTime localDateTime = timestamp.toLocalDateTime(); OffsetDateTime result = OffsetDateTime.of( localDateTime, ZoneOffset.of(offset)); assertThat(result, is(OffsetDateTime.of( 2016, 4, 26, 10, 11, 12, 123456789, ZoneOffset.of("+08:00"));
52. ZonedDateTime͸ʁ
53. ZonedDateTime࢖͍ͬͯ·͢ • λΠϜκʔϯDBͷߋ৽͕ඞཁʹͳͬͨΓͰϦεΫେ͖͍͠ Φεεϝ͠ͳ͍ͱ͔ݟΔ͚Ͳɺ࣮ࡍʹ࢖͍ͬͯ·͢ • ։ൃ్த (ޙظ) ͔Β Java 8 ΁Ҡߦͨ͠ • ࠃࡍԽରԠͰλΠϜκʔϯΛ͢Ͱʹ࢖͍ͬͯͨ • ొ࿥࣌ͷλΠϜκʔϯ͸ผͷྻͰอ࣋ (લड़ͷύλʔϯ) • (࠷ॳʹ΋͋ͬͨ) λΠϜκʔϯDBͷϝϯςφϯε͸՝୊
54. ZonedDateTime͔Βม׵ PreparedStatement stmt = conn.prepareStatement( "UPDATE sample SET ts=?, ts_zone=? WHERE id='hoge'"); ZonedDateTime zonedDateTime = ZonedDateTime.of(2016, 4, 26, 10, 11, 12, 123456789, ZoneId.of("Asia/Tokyo")); // java.util.DateΛܦ༝͠ͳͯ͘΋Α͍͕ɺ࣮ࡍʹ࢖͍ͬͯΔྫͱͯ͠ɺ // ͜͜Ͱ͸·ͣjava.util.DateΛม׵͢Δ (ϛϦඵະຬ͸ܽམ͢ΔͷͰ஫ҙ) Instant instant = zonedDateTime.toInstant(); java.util.Date date = java.util.Date.from(instant); // Date͔Βjava.sql.Timestamp΁ม׵͢Δ stmt.setObject(1, new java.sql.Timestamp(date.getTime())); stmt.setObject(2, zonedDateTime.getZone().getId());
55. ZonedDateTime΁ม׵ Timestamp timestamp = resultSet.getTimestamp("ts"); String offset = resultSet.getString("ts_zone"); // ZonedDateTime͔Βม׵ͨ࣌͠ͷखॱΛٯʹ͢Δ // Ұ୴java.util.Date΁ม׵͠ɺλΠϜκʔϯΛ෇༩ͯ͠ZonedDateTime΁ม׵ java.util.Date date = new java.util.Date(timestamp.getTime()); Instant instant = date.toInstant(); ZonedDateTime result = ZonedDateTime.ofInstant(instant, ZoneId.of(offset)); assertThat(result, is(ZonedDateTime.of( 2016, 4, 26, 10, 11, 12, 123000000, ZoneId.of("Asia/Tokyo"))));
56. Date and Time API ͸ JDBC͕৽ͯ͘͠΋ݹͯ͘΋ ී௨ʹ࢖͑Δ
57. ͝ਗ਼ௌ ͋Γ͕ͱ͏͍͟͝·ͨ͠