001/* 002 * Copyright (c) 2018-2023 Tada AB and other contributors, as listed below. 003 * 004 * All rights reserved. This program and the accompanying materials 005 * are made available under the terms of the The BSD 3-Clause License 006 * which accompanies this distribution, and is available at 007 * http://opensource.org/licenses/BSD-3-Clause 008 * 009 * Contributors: 010 * Chapman Flack 011 */ 012package org.postgresql.pljava.example.annotation; 013 014import org.postgresql.pljava.annotation.Function; 015import org.postgresql.pljava.annotation.SQLAction; 016 017/** 018 * Exercise new mappings between date/time types and java.time classes 019 * (JDBC 4.2 change 21). 020 *<p> 021 * Defines a method {@link #javaSpecificationGE javaSpecificationGE} that may be 022 * of use for other examples. 023 */ 024@SQLAction( 025 requires="TypeRoundTripper.roundTrip", 026 install={ 027 " SELECT" + 028 " CASE WHEN every(orig = roundtripped)" + 029 " THEN javatest.logmessage('INFO', 'java.time.LocalDate passes')" + 030 " ELSE javatest.logmessage('WARNING', 'java.time.LocalDate fails')" + 031 " END" + 032 " FROM" + 033 " (VALUES" + 034 " (date 'infinity')," + 035 " (date '2017-08-21')," + 036 " (date '1970-03-07')," + 037 " (date '1919-05-29')," + 038 " (date '-infinity')" + 039 " ) AS p(orig)," + 040 " javatest.roundtrip(p, 'java.time.LocalDate')" + 041 " AS r(roundtripped date)", 042 043 " SELECT" + 044 " CASE WHEN every(orig = roundtripped)" + 045 " THEN javatest.logmessage('INFO', 'java.time.LocalTime passes')" + 046 " ELSE javatest.logmessage('WARNING', 'java.time.LocalTime fails')" + 047 " END" + 048 " FROM" + 049 " (VALUES" + 050 " (current_time::time)," + 051 " ('00:00:00')," + 052 " ('24:00:00')" + 053 " ) AS p(orig)," + 054 " javatest.roundtrip(p, 'java.time.LocalTime')" + 055 " AS r(roundtripped time)", 056 057 " SELECT" + 058 " CASE WHEN every(orig = roundtripped)" + 059 " THEN javatest.logmessage('INFO', 'java.time.OffsetTime passes')" + 060 " ELSE javatest.logmessage('WARNING', 'java.time.OffsetTime fails')" + 061 " END" + 062 " FROM" + 063 " (VALUES" + 064 " (current_time::timetz)," + 065 " ('00:00:00')," + 066 " ('24:00:00')" + 067 " ) AS p(orig)," + 068 " javatest.roundtrip(p, 'java.time.OffsetTime')" + 069 " AS r(roundtripped timetz)", 070 071 " SELECT" + 072 " CASE WHEN every(orig = roundtripped)" + 073 " THEN javatest.logmessage('INFO', 'java.time.LocalDateTime passes')" + 074 " ELSE javatest.logmessage('WARNING','java.time.LocalDateTime fails')"+ 075 " END" + 076 " FROM" + 077 " (SELECT 'on' = current_setting('integer_datetimes')) AS ck(idt)," + 078 " LATERAL (" + 079 " SELECT" + 080 " value" + 081 " FROM" + 082 " (VALUES" + 083 " (true, timestamp '2017-08-21 18:25:29.900005')," + 084 " (true, timestamp '1970-03-07 17:37:49.300009')," + 085 " (true, timestamp '1919-05-29 13:08:33.600001')," + 086 " (idt, timestamp 'infinity')," + 087 " (idt, timestamp '-infinity')" + 088 " ) AS vs(cond, value)" + 089 " WHERE cond" + 090 " ) AS p(orig)," + 091 " javatest.roundtrip(p, 'java.time.LocalDateTime')" + 092 " AS r(roundtripped timestamp)", 093 094 " SELECT" + 095 " CASE WHEN every(orig = roundtripped)" + 096 " THEN javatest.logmessage('INFO', 'java.time.OffsetDateTime passes')"+ 097 " ELSE javatest.logmessage(" + 098 " 'WARNING','java.time.OffsetDateTime fails')"+ 099 " END" + 100 " FROM" + 101 " (SELECT 'on' = current_setting('integer_datetimes')) AS ck(idt)," + 102 " LATERAL (" + 103 " SELECT" + 104 " value" + 105 " FROM" + 106 " (VALUES" + 107 " (true, timestamptz '2017-08-21 18:25:29.900005Z')," + 108 " (true, timestamptz '1970-03-07 17:37:49.300009Z')," + 109 " (true, timestamptz '1919-05-29 13:08:33.600001Z')," + 110 " (idt, timestamptz 'infinity')," + 111 " (idt, timestamptz '-infinity')" + 112 " ) AS vs(cond, value)" + 113 " WHERE cond" + 114 " ) AS p(orig)," + 115 " javatest.roundtrip(p, 'java.time.OffsetDateTime')" + 116 " AS r(roundtripped timestamptz)", 117 118 " SELECT" + 119 " CASE WHEN every(orig = roundtripped)" + 120 " THEN javatest.logmessage('INFO', 'OffsetTime as stmt param passes')"+ 121 " ELSE javatest.logmessage(" + 122 " 'WARNING','java.time.OffsetTime as stmt param fails')"+ 123 " END" + 124 " FROM" + 125 " (SELECT current_time::timetz) AS p(orig)," + 126 " javatest.roundtrip(p, 'java.time.OffsetTime', true)" + 127 " AS r(roundtripped timetz)" 128}) 129public class JDBC42_21 130{ 131 /** 132 * Return true if running under a Java specification version at least as 133 * recent as the argument ('1.6', '1.7', '1.8', '9', '10', '11', ...). 134 */ 135 @Function(schema="javatest", provides="javaSpecificationGE") 136 public static boolean javaSpecificationGE(String want) 137 { 138 String got = System.getProperty("java.specification.version"); 139 if ( want.startsWith("1.") ) 140 want = want.substring(2); 141 if ( got.startsWith("1.") ) 142 got = got.substring(2); 143 return 0 <= Integer.valueOf(got).compareTo(Integer.valueOf(want)); 144 } 145}