Save 37% off PRO during our Black Friday Sale! »

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

Bd593f2def2a91327c2845cb47dfd359?s=47 hiji
April 27, 2017

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

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

Bd593f2def2a91327c2845cb47dfd359?s=128

hiji

April 27, 2017
Tweet

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".18*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. ͝ਗ਼ௌ ͋Γ͕ͱ͏͍͟͝·ͨ͠