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

by hiji

Slide 1

Slide 1 text

Date and Time API Λ JDBCͰѻͬͯΈΔ 2017/04/26 ؔJava '17 4݄౓ @hijiri408

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

Date and Time API ͷ ؆୯ͳ঺հ

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

JDBCͷ؆୯ͳ঺հ

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

JDBC ͷ Date and Time API ରԠ

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

ద౰ͳςʔϒϧΛ༻ҙ ྻ໊ σʔλܕ આ໌ JE 7"3$)"3 ࣝผ஋ EU %"5& ೔෇༻ EU@DIBS 7"3$)"3 ೔෇ͷจࣈྻԽ༻ UT 5*.&45".1 ೔࣌༻ UT@U[ 5*.&45".1 8*5)5*.&;0/& λΠϜκʔϯ༻

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

ະରԠͷόʔδϣϯͰ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) ྫ֎͕ૹग़͞ΕΔ ʢϝοηʔδ΋෼͔Δʣ

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

ະରԠͷόʔδϣϯͰ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) ྫ֎͕ૹग़͞ΕΔ ʢϝοηʔδ΋෼͔Δʣ

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

• java.sql ͷΫϥεʹม׵ϝιου͕௥Ճ͞Εͯ ͍Δ java.sql ͷΫϥε͔Βͷม׵ ม׵ݩ ϝιου ม׵ޙ KBWBTRM%BUF UP-PDBM%BUF KBWBUJNF-PDBM%BUF KBWBTRM5JNF UP-PDBM5JNF KBWBUJNF-PDBM5JNF KBWBTRM5JNFTUBNQ UP-PDBM%BUF5JNF KBWBUJNF-PDBM%BUF5JNF

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

ZonedDateTime͸ʁ

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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