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
   

   View Slide

2. ࣗݾ঺հ
   • Ṥ৲ɹਅ໵ʢͻ͡Γɹ͠Μ΍ʣ
   • Twitter: @hijiri408
   • Java ΤϯδχΞʢϑϦʔϥϯεʣ
   • SIer Ͱͷ։ൃҊ͕݅ओ
   

   View Slide

3. ໨࣍
   • Date and Time API ͷ؆୯ͳ঺հ
   • JDBCͷ؆୯ͳ঺հ
   • JDBCͷ Date and Time API ରԠ
   • ະରԠͷJDBCυϥΠόͰ Date and Time API
   Λ࢖͏
   

   View Slide

4. JDBCΛ௚઀࢖͏͜ͱ͸͋Δʁ
   • ੲ͸Α͘࢖ΘΕ͍ͯͨ
   • O/RϚούʔͱ͔࢖͍ग़͢ͱ࢖Θͳ͍ΑͶ
   • ج൫։ൃͰϑϨʔϜϫʔΫ΍ϥΠϒϥϦΛͭ͘Δ
   ଆʹͳΔ͜ͱ͕ଟ͘ɺO/RϚούʔ͸͋·Γ࢖ͬ
   ͨ͜ͱ͕ͳ͍ (Doma ver1 ͚ͩ࢖ͬͨ͜ͱ͕͋Δ)
   

   View Slide

5. σʔλΞΫηεܥϥΠϒϥϦ
   ͷίʔυϦʔσΟϯά΍
   ֦ுΛ͢Δ࣌ɺ
   গ͠͸໾ʹཱ͔ͭ΋͠Εͳ͍
   

   View Slide

6. Date and Time API ͷ
   ؆୯ͳ঺հ
   

   View Slide

7. Java 7 ·Ͱͷ೔෇΍࣌ࠁૢ࡞
   • java.util.Date ΍ java.util.Calendar ͕࣠
   • UNIX ࣌ؒ Λϕʔεʹ͓ͯ͠ΓɺΤϙοΫ͔Βͷܦա࣌
   ؒΛϛϦඵͷਫ਼౓Ͱอ͍࣋ͯ͠Δ
   • ௚ײతͰͳ͔ͬͨΓ࢖͍ͮΒ͍ϝιου΋ଟ͍
   • Commons Lang ౳ͷϥΠϒϥϦͰ࢖͍ͮΒ͞ΛΧόʔ
   • ϛϡʔλϒϧͰ͋ΓɺεϨουΞϯηʔϑ
   

   View Slide

8. Date and Time API (JSR 310)
   • Java 8 Ͱಋೖ͞Εͨ೔෇ͱ࣌ࠁΛѻ͏API
   • ೔෇΍࣌ࠁͷදݱʹؔ͢Δࠃࡍن֨Ͱ͋Δ ISO 8601 Λ
   ϕʔεʹ͍ͯ͠Δɹ(→ϕʔε͔ΒҟͳΔ)
   • ೔෇΍࣌ࠁΛදݱͰ͖Δଟ͘ͷΫϥε
   • ௚ײతʹૢ࡞Ͱ͖Δϝιου܈
   • ΠϛϡʔλϒϧͰ͋ΓɺεϨουηʔϑ
   

   View Slide

9. ೔෇΍࣌ࠁΛද͢ओͳΫϥε
   Ϋϥε આ໌
   LocalDate ೔෇Λѻ͏
   LocalTime ࣌ࠁΛѻ͏
   LocalDateTime ೔෇ͱ࣌ࠁΛѻ͏
   OffsetTime UTC͔Βͷ࣌ࠩͰɺ࣌ࠁΛѻ͏
   OffsetDateTime UTC͔Βͷ࣌ࠩͰɺ೔෇ͱ࣌ࠁΛѻ͏
   ZonedDateTime λΠϜκʔϯͰɺ೔෇ͱ࣌ࠁΛѻ͏
   

   View Slide

10. OffsetTimeͱOffsetDateTime
    • ISO 8601 ͰѻΘΕΔ࣌ࠩ (஍ํ࣌ͱUTCͱͷࠩ) Λѻ
    ͏
    • ೔ຊͩͱ9࣌ؒਐΜͰ͍ΔͷͰʮ+09:00ʯ
    • Ն࣌ؒͰ͸1࣌ؒ܁Γ্͕͕ͬͯ࣌ࠩมΘΔͨΊɺظ
    ؒதʹ͸࣌ࠩͷѻ͍ʹ஫ҙ͕ඞཁ
    • TemporalAdjusterΛར༻ͯ͠ղܾ͢Δํ๏΋͋Δ
    

    View Slide

11. ZonedDateTime
    • ISO 8601 Ͱ͸ѻΘΕͳ͍λΠϜκʔϯΛѻ͏
    • ZoneIDͰࣝผ͞Εɺ೔ຊͩͱʮAsia/Tokyoʯ
    • Ն͕࣌ؒαϙʔτ͞Ε͓ͯΓɺద੾ͳ࣌ࠩʹͳΔ
    • ద੾ͳঢ়ଶΛҡ࣋͢Δʹ͸ɺJRE͕อ࣋͢ΔλΠϜκʔ
    ϯDBΛɺJREͷόʔδϣϯΞοϓɺ΋͘͠͸tzupdater
    πʔϧͰߋ৽͍ͯ͘͠ඞཁ͕͋Δ
    

    View Slide

12. JDBCͷ؆୯ͳ঺հ
    

    View Slide

13. JDBCͱ͸
    • Java͔ΒDBΛૢ࡞͢ΔͨΊͷAPI
    • JSR 221 Ͱ࢓༷ࡦఆ
    • ࠷৽όʔδϣϯ͸ 4.2
    • ࣮ࡍʹDBΛૢ࡞͢ΔͨΊʹ͸ɺͦͷDB༻ͷ
    JDBCυϥΠόʔ ͕ඞཁ
    

    View Slide

14. JDBCυϥΠόʔͱ͸
    • ࣮ࡍʹDB΁ͷ઀ଓ΍ૢ࡞Λߦ͏࣮૷
    • ઀ଓํ๏͕ҟͳΔ TYPE 1 ʙ 4 ͷछྨ͕͋Δ
    • ओͳDB޲͚ͷJDBCυϥΠόʔ͸ɺ֤DBMSͷϕϯ
    μʔ͔Βఏڙ͞Ε͍ͯΔ
    • εϓϨουγʔτ΍ϑϥοτϑΝΠϧΛૢ࡞͢ΔͨΊ
    ͷJDBCυϥΠόʔ΋͋Δ
    

    View Slide

15. DB΍SQLͷܕͱͷϚοϐϯά
    • JDBCͷ಺෦ͰɺDB΍SQLͷܕΛந৅Խͨ͠JDBCܕ
    ͕ఆٛ͞Ε͍ͯΔ (java.sql.Types)
    • σʔλͱͯ͠ѻ͑ΔJavaͷܕ͸ɺରԠ͢ΔJDBCܕ͕
    ఆΊΒΕ͍ͯΔ
    • ֤JDBCυϥΠόͰ͸ɺJDBCܕ΍JavaͷܕʹԠ͡
    ͯɺ࣮ࡍͷDB΍SQLͷܕʹϚοϐϯάͤ͞Δ
    

    View Slide

16. ೔෇΍࣌ࠁͷܕϚοϐϯά
    • java.util.Date Ͱ͸ͳ͘ɺjava.sql ύοέʔδ
    ʹ͋Δ೔෇΍࣌ࠁͷΫϥεΛ࢖͏
    Java ܕ JDBC ܕ
    java.sql.Date DATE
    java.sql.Time TIME
    java.sql.Timestamp TIMESTAMP
    

    View Slide

17. java.sql ͷ೔෇΍࣌ࠁͷΫϥε
    • Date͸೔෇ɺTime͸࣌ࠁɺTimestamp͸೔෇ͱ࣌ࠁΛѻ͏
    • ͦΕͧΕͱ΋ java.util.Date ͷαϒΫϥε
    • Timestamp͸ java.util.Date ͱͯ͠ѻ͏ͳͱJavadocʹ஫
    ҙॻ͖ (ඵະຬΛಠࣗʹอ͍࣋ͯ͠ΔͨΊ)
    • Timestamp͸φϊඵͷਫ਼౓·ͰදͤΔͨΊɺjava.util.Date
    ΁ม׵͢ΔͱϛϦඵະຬ͕ܽམ͢ΔͷͰ஫ҙ
    

    View Slide

18. JDBC ͷ Date and Time API
    ରԠ
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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Λ௥Ճ
    

    View Slide

22. JDBCυϥΠόʔͷରԠঢ়گ
    %# +%#$αϙʔτόʔδϣϯ
    0SBDMF
    .Z42-
    1PTUHSF42- #VJME
    %# ʹ͸ରԠ͍ͯ͠ΔΆ͍
    42-4FSWFS
    

    View Slide

23. JDBCυϥΠόʔͷରԠঢ়گ
    • جຊతʹ͸ͲΕ΋ରԠ͞Ε͍ͯΔ
    • DB2͚ͩʮ JDBC 4.0 ରԠʯͱ͔͠෼͔Βͳ͔ͬͨ (IBMͷα
    Πτ͕ॏͯ͘୳͢ͷπϥ͍)
    • ৽ػೳ͕Ͳ͜·Ͱαϙʔτ͞Ε͍ͯΔ͔͸JDBCυϥΠόʔͷ
    ࣮૷ʹΑΔ
    • ྫ͑͹MySQLͰ͸ɺ৽͍͠JDBCܕͷ
    TIME_WITH_TIMEZONE ͸αϙʔτ͞Ε͍ͯͳ͍౳
    

    View Slide

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 Ͱ
    ௥Ճ͞Εͨ
    

    View Slide

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/&
    υΩϡϝϯτͷରԠද
    ʹهࡌ͕ແ͔ͬͨ…
    

    View Slide

26. ಈ͔ͯ͠Έͨ؀ڥ
    • Oracle 12c Release 1 (12.1.0.2)
    • ઌि Docker Store Ͱެ։͞Ε͍ͯͨ
    • ࠷ॳ͸MySQLΛߟ͕͑ͨɺλΠϜκʔϯ෇͖ͷܕ
    ͕ແ͘ɺ৽͍͠JDBCܕ͕ࢼͤͳ͍ͷͰࢭΊͨ
    • JDBC 4.2 Λαϙʔτ͍ͯ͠Δ 12.2.0.1 ͷJDBCυ
    ϥΠόʔ͕࢖͑Δ
    

    View Slide

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/&
    λΠϜκʔϯ༻
    

    View Slide

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ͷ೔͕࣌ొ࿥͞Ε͍ͯΔ
    

    View Slide

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ͷφϊඵΛؚΉ
    ೔͕࣌ొ࿥͞Ε͍ͯΔ
    

    View Slide

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ܕʹ΋౤ೖͰ͖Δ
    (อ࣋Ͱ͖ͳ͍ඵະຬ͸ܽམ)
    

    View Slide

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ͷ࣌ࠩͷ
    ··ొ࿥͞Ε͍ͯΔ
    

    View Slide

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)
    ྫ֎͕ૹग़͞ΕΔ
    ʢϝοηʔδ΋෼͔Δʣ
    

    View Slide

33. setObjectͰܕΛ໌ࣔ
    • setObjectͰJDBCܕΛ໌ࣔ͢Δ৔߹ɺҎԼͷJDBCܕΛࢦ
    ఆͰ͖Δ
    Java ܕ JDBC ܕ
    java.util.LocalDate
    CHARɺVARCHARɺ
    LONGVARCHARɺDATE
    java.util.LocalTime
    CHARɺVARCHARɺ
    LONGVARCHARɺTIME
    ʢଓ͘ʣ
    

    View Slide

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
    

    View Slide

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υϥΠόґଘʁ)
    

    View Slide

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υϥΠόʔ࣍ୈ͕ͩɺܕ
    Ϛοϐϯά͸ׂͱϧʔζͳײ͡
    

    View Slide

37. setObjectͰܕΛ໌ࣔ
    • Ҏલ͸ java.sql.Types ʹఆٛ͞Εͨ int ஋Ͱࢦఆ͍ͯͨ͠
    • JDBC 4.2 ͔Β enum ͷ java.sql.JDBCType ͕௥Ճ͞Εɺ
    setObjectͷܕࢦఆʹ౉ͤΔΑ͏ʹͳͬͨ
    • Ҿ਺ͱͯ͠ java.sql.JDBCType Λαϙʔτ͍ͯ͠Δ͔͸
    JDBCυϥΠόʔ࣍ୈɻྫ͑͹ɺPostgreSQLͰ͸
    SQLFeatureNotSupportedException ͕ૹग़͞ΕΔɻ
    

    View Slide

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
    

    View Slide

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ͱͯ͠औಘͰ͖Δ
    

    View Slide

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 ͱͯ͠औಘͰ͖Δ
    

    View Slide

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 ͱͯ͠औಘͰ͖Δ
    

    View Slide

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)
    ྫ֎͕ૹग़͞ΕΔ
    ʢϝοηʔδ΋෼͔Δʣ
    

    View Slide

43. ະରԠͷJDBCυϥΠόͰ
    Date and Time API Λ࢖͏
    

    View Slide

44. ະରԠͷJDBCυϥΠόͰ͸
    • setObject΍getObjectͰ௚઀ Date and Time API ͷΫ
    ϥεΛѻ͓͏ͱͨ͠Βɺલड़ͷͱ͓Γྫ֎͕ૹग़͞Ε
    ͯ͠·͏
    • JDBCυϥΠόͰѻ͏ࡍʹ java.sql ύοέʔδͷΫϥ
    εʹͳ͍ͬͯΕ͹Α͍
    • Date and Time API ͷΫϥεͱ java.sql ύοέʔδͷ
    Ϋϥεͱͷ૬ޓม׵͕Ͱ͖Ε͹ղܾ͢Δ
    

    View Slide

45. java.sql ͷΫϥε΁ͷม׵
    • java.sql ͷΫϥεʹ valueOf ͱ͍͏ϑΝΫτϦ
    ϝιου͕௥Ճ͞Ε͍ͯΔ
    • ͨͩ͠ɺjava.sql ͷΫϥε͕࣌ࠩ৘ใΛอ࣋Ͱ
    ͖ͳ͍ͨΊɺ࣌ࠩΛѻ͏Ϋϥε͔Β͸ม׵Ͱ
    ͖ͳ͍
    

    View Slide

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 ϝιουͰੜ੒Ͱ͖Δ
    

    View Slide

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
    

    View Slide

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
    

    View Slide

49. ࣌ࠩΛѻ͏Ϋϥεͱͷม׵
    • JDBCυϥΠόͰಠ֦ࣗுͨ࣌ࠩ͠Λѻ͏Ϋϥ
    εΛ࢖͏ (OracleͳΒ
    oracle.sql.TIMESTAMPTZ ౳)
    • ࣌ࠩ৘ใ͸ผͷखஈͰΧόʔ͢Δ
    • ࣌ࠩ΍λΠϜκʔϯ͸ผͷྻʹొ࿥͢Δ
    

    View Slide

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());
    

    View Slide

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"));
    

    View Slide

52. ZonedDateTime͸ʁ
    

    View Slide

53. ZonedDateTime࢖͍ͬͯ·͢
    • λΠϜκʔϯDBͷߋ৽͕ඞཁʹͳͬͨΓͰϦεΫେ͖͍͠
    Φεεϝ͠ͳ͍ͱ͔ݟΔ͚Ͳɺ࣮ࡍʹ࢖͍ͬͯ·͢
    • ։ൃ్த (ޙظ) ͔Β Java 8 ΁Ҡߦͨ͠
    • ࠃࡍԽରԠͰλΠϜκʔϯΛ͢Ͱʹ࢖͍ͬͯͨ
    • ొ࿥࣌ͷλΠϜκʔϯ͸ผͷྻͰอ࣋ (લड़ͷύλʔϯ)
    • (࠷ॳʹ΋͋ͬͨ) λΠϜκʔϯDBͷϝϯςφϯε͸՝୊
    

    View Slide

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());
    

    View Slide

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"))));
    

    View Slide

56. Date and Time API ͸
    JDBC͕৽ͯ͘͠΋ݹͯ͘΋
    ී௨ʹ࢖͑Δ
    

    View Slide

57. ͝ਗ਼ௌ
    ͋Γ͕ͱ͏͍͟͝·ͨ͠
    

    View Slide