diff --git a/.github/.OwlBot.lock.yaml b/.github/.OwlBot.lock.yaml index c1e4d2da..288e3948 100644 --- a/.github/.OwlBot.lock.yaml +++ b/.github/.OwlBot.lock.yaml @@ -13,4 +13,4 @@ # limitations under the License. docker: image: gcr.io/cloud-devrel-public-resources/owlbot-java:latest - digest: sha256:a57d2ea6d1a77aa96c17ad0850b779ec6295f88b6c1da3d214b2095d140a2066 + digest: sha256:9de537d592b60e5eac73b374a28263969bae91ecdb29b445e894576fbf54851c diff --git a/.kokoro/presubmit/graalvm-native-17.cfg b/.kokoro/presubmit/graalvm-native-17.cfg index a3f7fb9d..e20330c3 100644 --- a/.kokoro/presubmit/graalvm-native-17.cfg +++ b/.kokoro/presubmit/graalvm-native-17.cfg @@ -3,7 +3,7 @@ # Configure the docker image for kokoro-trampoline. env_vars: { key: "TRAMPOLINE_IMAGE" - value: "gcr.io/cloud-devrel-kokoro-resources/graalvm17" + value: "gcr.io/cloud-devrel-kokoro-resources/graalvm17:22.3.0" } env_vars: { diff --git a/.kokoro/presubmit/graalvm-native.cfg b/.kokoro/presubmit/graalvm-native.cfg index 4c7225ec..0fd6ba2f 100644 --- a/.kokoro/presubmit/graalvm-native.cfg +++ b/.kokoro/presubmit/graalvm-native.cfg @@ -3,7 +3,7 @@ # Configure the docker image for kokoro-trampoline. env_vars: { key: "TRAMPOLINE_IMAGE" - value: "gcr.io/cloud-devrel-kokoro-resources/graalvm" + value: "gcr.io/cloud-devrel-kokoro-resources/graalvm:22.3.0" } env_vars: { diff --git a/.kokoro/requirements.in b/.kokoro/requirements.in index 924f94ae..a5010f77 100644 --- a/.kokoro/requirements.in +++ b/.kokoro/requirements.in @@ -17,7 +17,7 @@ pycparser==2.21 pyperclip==1.8.2 python-dateutil==2.8.2 requests==2.27.1 -certifi==2022.9.24 +certifi==2022.12.7 importlib-metadata==4.8.3 zipp==3.6.0 google_api_core==2.8.2 diff --git a/.kokoro/requirements.txt b/.kokoro/requirements.txt index 71fcafc7..15c404aa 100644 --- a/.kokoro/requirements.txt +++ b/.kokoro/requirements.txt @@ -16,9 +16,9 @@ cachetools==4.2.4 \ # via # -r requirements.in # google-auth -certifi==2022.9.24 \ - --hash=sha256:0d9c601124e5a6ba9712dbc60d9c53c21e34f5f641fe83002317394311bdce14 \ - --hash=sha256:90c1a32f1d68f940488354e36370f6cca89f0f106db09518524c88d6ed83f382 +certifi==2022.12.7 \ + --hash=sha256:35824b4c3a97115964b408844d64aa14db1cc518f6562e8d7261699d1350a9e3 \ + --hash=sha256:4ad3232f5e926d6718ec31cfc1fcadfde020920e278684144551c91769c7bc18 # via # -r requirements.in # requests diff --git a/CHANGELOG.md b/CHANGELOG.md index 089c1eb2..b23d783c 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -1,5 +1,18 @@ # Changelog +## [2.9.0](https://github.com/googleapis/java-spanner-jdbc/compare/v2.8.0...v2.9.0) (2022-12-14) + + +### Features + +* Add tests for DML with Returning clause ([#936](https://github.com/googleapis/java-spanner-jdbc/issues/936)) ([8a86467](https://github.com/googleapis/java-spanner-jdbc/commit/8a86467c6db7a4e99fdf23cdbce2d78382f8cde9)) + + +### Dependencies + +* Update dependency com.google.cloud:google-cloud-shared-dependencies to v3.1.0 ([#1069](https://github.com/googleapis/java-spanner-jdbc/issues/1069)) ([c2ff33a](https://github.com/googleapis/java-spanner-jdbc/commit/c2ff33a8f6a7051e1639fee235e1b6bba5916c3a)) +* Update dependency com.google.cloud:google-cloud-spanner-bom to v6.34.1 ([#1072](https://github.com/googleapis/java-spanner-jdbc/issues/1072)) ([0045a5e](https://github.com/googleapis/java-spanner-jdbc/commit/0045a5e51c5a40523f70ec22c9b75bc8707dccb7)) + ## [2.8.0](https://github.com/googleapis/java-spanner-jdbc/compare/v2.7.13...v2.8.0) (2022-11-17) diff --git a/pom.xml b/pom.xml index bb3fd74b..fd691251 100644 --- a/pom.xml +++ b/pom.xml @@ -4,7 +4,7 @@ xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> 4.0.0 google-cloud-spanner-jdbc - 2.8.0 + 2.9.0 jar Google Cloud Spanner JDBC https://github.com/googleapis/java-spanner-jdbc @@ -14,7 +14,7 @@ com.google.cloud google-cloud-shared-config - 1.5.4 + 1.5.5 @@ -62,14 +62,14 @@ com.google.cloud google-cloud-spanner-bom - 6.33.0 + 6.34.1 pom import com.google.cloud google-cloud-shared-dependencies - 3.0.6 + 3.1.0 pom import diff --git a/samples/install-without-bom/pom.xml b/samples/install-without-bom/pom.xml index cccec274..69fc313a 100644 --- a/samples/install-without-bom/pom.xml +++ b/samples/install-without-bom/pom.xml @@ -29,7 +29,7 @@ com.google.cloud google-cloud-spanner-jdbc - 2.7.12 + 2.8.0 diff --git a/samples/snapshot/pom.xml b/samples/snapshot/pom.xml index 0532d8a3..b39196d3 100644 --- a/samples/snapshot/pom.xml +++ b/samples/snapshot/pom.xml @@ -28,7 +28,7 @@ com.google.cloud google-cloud-spanner-jdbc - 2.8.0 + 2.9.0 diff --git a/samples/snippets/pom.xml b/samples/snippets/pom.xml index e735290d..0e5a4245 100644 --- a/samples/snippets/pom.xml +++ b/samples/snippets/pom.xml @@ -30,7 +30,7 @@ com.google.cloud libraries-bom - 26.1.4 + 26.1.5 pom import diff --git a/src/main/java/com/google/cloud/spanner/jdbc/JdbcStatement.java b/src/main/java/com/google/cloud/spanner/jdbc/JdbcStatement.java index f37eb21a..051ed9d6 100644 --- a/src/main/java/com/google/cloud/spanner/jdbc/JdbcStatement.java +++ b/src/main/java/com/google/cloud/spanner/jdbc/JdbcStatement.java @@ -87,7 +87,7 @@ public long executeLargeUpdate(String sql) throws SQLException { switch (result.getResultType()) { case RESULT_SET: throw JdbcSqlExceptionFactory.of( - "The statement is not an update or DDL statement", Code.INVALID_ARGUMENT); + "The statement is not a non-returning DML or DDL statement", Code.INVALID_ARGUMENT); case UPDATE_COUNT: return result.getUpdateCount(); case NO_RESULT: diff --git a/src/test/java/com/google/cloud/spanner/jdbc/JdbcStatementTest.java b/src/test/java/com/google/cloud/spanner/jdbc/JdbcStatementTest.java index 3ee9edcb..c7b6a6cb 100644 --- a/src/test/java/com/google/cloud/spanner/jdbc/JdbcStatementTest.java +++ b/src/test/java/com/google/cloud/spanner/jdbc/JdbcStatementTest.java @@ -17,6 +17,12 @@ package com.google.cloud.spanner.jdbc; import static com.google.common.truth.Truth.assertThat; +import static org.junit.Assert.assertArrayEquals; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertNotNull; +import static org.junit.Assert.assertThrows; +import static org.junit.Assert.assertTrue; import static org.junit.Assert.fail; import static org.mockito.Mockito.anyList; import static org.mockito.Mockito.mock; @@ -53,6 +59,8 @@ public class JdbcStatementTest { private static final String SELECT = "SELECT 1"; private static final String UPDATE = "UPDATE FOO SET BAR=1 WHERE BAZ=2"; private static final String LARGE_UPDATE = "UPDATE FOO SET BAR=1 WHERE 1=1"; + private static final String DML_RETURNING_GSQL = "UPDATE FOO SET BAR=1 WHERE 1=1 THEN RETURN *"; + private static final String DML_RETURNING_PG = "UPDATE FOO SET BAR=1 WHERE 1=1 RETURNING *"; private static final String DDL = "CREATE INDEX FOO ON BAR(ID)"; @Parameter public Dialect dialect; @@ -62,11 +70,20 @@ public static Object[] data() { return Dialect.values(); } + private String getDmlReturningSql() { + if (dialect == Dialect.GOOGLE_STANDARD_SQL) { + return DML_RETURNING_GSQL; + } + return DML_RETURNING_PG; + } + @SuppressWarnings("unchecked") private JdbcStatement createStatement() throws SQLException { Connection spanner = mock(Connection.class); when(spanner.getDialect()).thenReturn(dialect); + final String DML_RETURNING_SQL = getDmlReturningSql(); + com.google.cloud.spanner.ResultSet resultSet = mock(com.google.cloud.spanner.ResultSet.class); when(resultSet.next()).thenReturn(true, false); when(resultSet.getColumnType(0)).thenReturn(Type.int64()); @@ -88,6 +105,19 @@ private JdbcStatement createStatement() throws SQLException { when(spanner.execute(com.google.cloud.spanner.Statement.of(LARGE_UPDATE))) .thenReturn(largeUpdateResult); + com.google.cloud.spanner.ResultSet dmlReturningResultSet = + mock(com.google.cloud.spanner.ResultSet.class); + when(dmlReturningResultSet.next()).thenReturn(true, false); + when(dmlReturningResultSet.getColumnCount()).thenReturn(1); + when(dmlReturningResultSet.getColumnType(0)).thenReturn(Type.int64()); + when(dmlReturningResultSet.getLong(0)).thenReturn(1L); + + StatementResult dmlReturningResult = mock(StatementResult.class); + when(dmlReturningResult.getResultType()).thenReturn(ResultType.RESULT_SET); + when(dmlReturningResult.getResultSet()).thenReturn(dmlReturningResultSet); + when(spanner.execute(com.google.cloud.spanner.Statement.of(DML_RETURNING_SQL))) + .thenReturn(dmlReturningResult); + StatementResult ddlResult = mock(StatementResult.class); when(ddlResult.getResultType()).thenReturn(ResultType.NO_RESULT); when(spanner.execute(com.google.cloud.spanner.Statement.of(DDL))).thenReturn(ddlResult); @@ -96,6 +126,8 @@ private JdbcStatement createStatement() throws SQLException { when(spanner.executeQuery(com.google.cloud.spanner.Statement.of(UPDATE))) .thenThrow( SpannerExceptionFactory.newSpannerException(ErrorCode.INVALID_ARGUMENT, "not a query")); + when(spanner.executeQuery(com.google.cloud.spanner.Statement.of(DML_RETURNING_SQL))) + .thenReturn(dmlReturningResultSet); when(spanner.executeQuery(com.google.cloud.spanner.Statement.of(DDL))) .thenThrow( SpannerExceptionFactory.newSpannerException(ErrorCode.INVALID_ARGUMENT, "not a query")); @@ -109,6 +141,10 @@ private JdbcStatement createStatement() throws SQLException { .thenThrow( SpannerExceptionFactory.newSpannerException( ErrorCode.INVALID_ARGUMENT, "not an update")); + when(spanner.executeUpdate(com.google.cloud.spanner.Statement.of(DML_RETURNING_SQL))) + .thenThrow( + SpannerExceptionFactory.newSpannerException( + ErrorCode.FAILED_PRECONDITION, "cannot execute dml returning over executeUpdate")); when(spanner.executeBatchUpdate(anyList())) .thenAnswer( @@ -219,6 +255,20 @@ public void testExecuteWithDdlStatement() throws SQLException { assertThat(statement.getUpdateCount()).isEqualTo(JdbcConstants.STATEMENT_NO_RESULT); } + @Test + public void testExecuteWithDmlReturningStatement() throws SQLException { + Statement statement = createStatement(); + boolean res = statement.execute(getDmlReturningSql()); + assertTrue(res); + assertEquals(statement.getUpdateCount(), JdbcConstants.STATEMENT_RESULT_SET); + try (ResultSet rs = statement.getResultSet()) { + assertNotNull(rs); + assertTrue(rs.next()); + assertEquals(rs.getLong(1), 1L); + assertFalse(rs.next()); + } + } + @Test public void testExecuteWithGeneratedKeys() throws SQLException { Statement statement = createStatement(); @@ -257,6 +307,17 @@ public void testExecuteQueryWithUpdateStatement() { } } + @Test + public void testExecuteQueryWithDmlReturningStatement() throws SQLException { + Statement statement = createStatement(); + try (ResultSet rs = statement.executeQuery(getDmlReturningSql())) { + assertNotNull(rs); + assertTrue(rs.next()); + assertEquals(rs.getLong(1), 1L); + assertFalse(rs.next()); + } + } + @Test public void testExecuteQueryWithDdlStatement() { try { @@ -353,12 +414,29 @@ public void testExecuteUpdateWithSelectStatement() { } catch (SQLException e) { assertThat( JdbcExceptionMatcher.matchCodeAndMessage( - Code.INVALID_ARGUMENT, "The statement is not an update or DDL statement") + Code.INVALID_ARGUMENT, + "The statement is not a non-returning DML or DDL statement") .matches(e)) .isTrue(); } } + @Test + public void testExecuteUpdateWithDmlReturningStatement() { + try { + Statement statement = createStatement(); + SQLException e = + assertThrows(SQLException.class, () -> statement.executeUpdate(getDmlReturningSql())); + assertTrue( + JdbcExceptionMatcher.matchCodeAndMessage( + Code.INVALID_ARGUMENT, + "The statement is not a non-returning DML or DDL statement") + .matches(e)); + } catch (SQLException e) { + // ignore exception. + } + } + @Test public void testExecuteUpdateWithDdlStatement() throws SQLException { Statement statement = createStatement(); @@ -438,6 +516,19 @@ public void testDmlBatch() throws SQLException { } } + @Test + public void testDmlBatchWithDmlReturning() throws SQLException { + try (Statement statement = createStatement()) { + // Verify that multiple batches can be executed on the same statement. + for (int i = 0; i < 2; i++) { + statement.addBatch(getDmlReturningSql()); + statement.addBatch(getDmlReturningSql()); + statement.addBatch(getDmlReturningSql()); + assertArrayEquals(statement.executeBatch(), new int[] {1, 1, 1}); + } + } + } + @Test public void testLargeDmlBatch() throws SQLException { try (Statement statement = createStatement()) { diff --git a/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcPreparedStatementTest.java b/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcPreparedStatementTest.java index a452fdb0..c1d84169 100644 --- a/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcPreparedStatementTest.java +++ b/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcPreparedStatementTest.java @@ -36,6 +36,7 @@ import com.google.cloud.spanner.testing.EmulatorSpannerHelper; import com.google.common.base.Strings; import com.google.common.io.BaseEncoding; +import com.google.common.io.CharStreams; import java.io.IOException; import java.io.InputStream; import java.io.StringReader; @@ -263,6 +264,24 @@ private void setPreparedStatement(Connection connection, PreparedStatement ps, D ps.setArray(6, connection.createArrayOf("INT64", this.ticketPrices)); } } + + private void assertEqualsFields(Connection connection, ResultSet rs, Dialect dialect) + throws SQLException { + assertEquals(rs.getLong(1), this.venueId); + assertEquals(rs.getLong(2), this.singerId); + if (dialect == Dialect.POSTGRESQL) { + assertEquals(rs.getString(3), this.concertDate.toString()); + assertEquals(rs.getString(4), this.beginTime.toString()); + assertEquals(rs.getString(5), this.endTime.toString()); + } else { + assertEquals(rs.getDate(3), this.concertDate); + assertEquals(rs.getTimestamp(4), this.beginTime); + assertEquals(rs.getTimestamp(5), this.endTime); + assertArrayEquals( + (Object[]) rs.getArray(6).getArray(), + (Object[]) connection.createArrayOf("INT64", this.ticketPrices).getArray()); + } + } } private static Date parseDate(String value) { @@ -333,6 +352,34 @@ private String getConcertsInsertQuery(Dialect dialect) { return "INSERT INTO Concerts (VenueId, SingerId, ConcertDate, BeginTime, EndTime, TicketPrices) VALUES (?,?,?,?,?,?);"; } + private String getConcertsInsertReturningQuery(Dialect dialect) { + if (dialect == Dialect.POSTGRESQL) { + return "INSERT INTO Concerts (VenueId, SingerId, ConcertDate, BeginTime, EndTime) VALUES (?,?,?,?,?) RETURNING *;"; + } + return "INSERT INTO Concerts (VenueId, SingerId, ConcertDate, BeginTime, EndTime, TicketPrices) VALUES (?,?,?,?,?,?) THEN RETURN *;"; + } + + private String getSingersInsertReturningQuery(Dialect dialect) { + if (dialect == Dialect.POSTGRESQL) { + return "INSERT INTO Singers (SingerId, FirstName, LastName, SingerInfo, BirthDate) values (?,?,?,?,?) RETURNING *"; + } + return "INSERT INTO Singers (SingerId, FirstName, LastName, SingerInfo, BirthDate) values (?,?,?,?,?) THEN RETURN *"; + } + + private String getAlbumsInsertReturningQuery(Dialect dialect) { + if (dialect == Dialect.POSTGRESQL) { + return "INSERT INTO Albums (SingerId, AlbumId, AlbumTitle, MarketingBudget) VALUES (?,?,?,?) RETURNING *"; + } + return "INSERT INTO Albums (SingerId, AlbumId, AlbumTitle, MarketingBudget) VALUES (?,?,?,?) THEN RETURN *"; + } + + private String getSongsInsertReturningQuery(Dialect dialect) { + if (dialect == Dialect.POSTGRESQL) { + return "INSERT INTO Songs (SingerId, AlbumId, TrackId, SongName, Duration, SongGenre) VALUES (?,?,?,?,?,?) RETURNING *;"; + } + return "INSERT INTO Songs (SingerId, AlbumId, TrackId, SongName, Duration, SongGenre) VALUES (?,?,?,?,?,?) THEN RETURN *;"; + } + private int getConcertExpectedParamCount(Dialect dialect) { if (dialect == Dialect.POSTGRESQL) { return 5; @@ -1150,6 +1197,103 @@ private void assertDefaultParameterMetaData(ParameterMetaData pmd, int expectedP } } + @Test + public void test12_InsertReturningTestData() throws SQLException { + assumeFalse( + "Emulator does not support DML with returning clause", + EmulatorSpannerHelper.isUsingEmulator()); + try (Connection connection = createConnection(env, database)) { + connection.setAutoCommit(false); + // Delete existing rows from tables populated by other tests, + // so that this test can populate rows from scratch. + Statement deleteStatements = connection.createStatement(); + deleteStatements.addBatch("DELETE FROM Concerts WHERE TRUE"); + deleteStatements.addBatch("DELETE FROM Songs WHERE TRUE"); + deleteStatements.addBatch("DELETE FROM Albums WHERE TRUE"); + deleteStatements.addBatch("DELETE FROM Singers WHERE TRUE"); + deleteStatements.executeBatch(); + try (PreparedStatement ps = + connection.prepareStatement(getSingersInsertReturningQuery(dialect.dialect))) { + assertDefaultParameterMetaData(ps.getParameterMetaData(), 5); + for (Singer singer : createSingers()) { + singer.setPreparedStatement(ps, getDialect()); + assertInsertSingerParameterMetadata(ps.getParameterMetaData()); + ps.addBatch(); + // check that adding the current params to a batch will not reset the metadata + assertInsertSingerParameterMetadata(ps.getParameterMetaData()); + } + int[] results = ps.executeBatch(); + for (int res : results) { + assertEquals(1, res); + } + } + try (PreparedStatement ps = + connection.prepareStatement(getAlbumsInsertReturningQuery(dialect.dialect))) { + assertDefaultParameterMetaData(ps.getParameterMetaData(), 4); + for (Album album : createAlbums()) { + ps.setLong(1, album.singerId); + ps.setLong(2, album.albumId); + ps.setString(3, album.albumTitle); + ps.setLong(4, album.marketingBudget); + assertInsertAlbumParameterMetadata(ps.getParameterMetaData()); + try (ResultSet rs = ps.executeQuery()) { + rs.next(); + assertEquals(rs.getLong(1), album.singerId); + assertEquals(rs.getLong(2), album.albumId); + assertEquals(rs.getString(3), album.albumTitle); + assertEquals(rs.getLong(4), album.marketingBudget); + } + // check that calling executeQuery will not reset the metadata + assertInsertAlbumParameterMetadata(ps.getParameterMetaData()); + } + } + try (PreparedStatement ps = + connection.prepareStatement(getSongsInsertReturningQuery(dialect.dialect))) { + assertDefaultParameterMetaData(ps.getParameterMetaData(), 6); + for (Song song : createSongs()) { + ps.setByte(1, (byte) song.singerId); + ps.setInt(2, (int) song.albumId); + ps.setShort(3, (short) song.songId); + ps.setNString(4, song.songName); + ps.setLong(5, song.duration); + ps.setCharacterStream(6, new StringReader(song.songGenre)); + assertInsertSongParameterMetadata(ps.getParameterMetaData()); + try (ResultSet rs = ps.executeQuery()) { + rs.next(); + assertEquals(rs.getByte(1), (byte) song.singerId); + assertEquals(rs.getInt(2), (int) song.albumId); + assertEquals(rs.getShort(3), (short) song.songId); + assertEquals(rs.getNString(4), song.songName); + assertEquals(rs.getLong(5), song.duration); + assertEquals( + CharStreams.toString(rs.getCharacterStream(6)), + CharStreams.toString(new StringReader(song.songGenre))); + } + // check that calling executeQuery will not reset the metadata + assertInsertSongParameterMetadata(ps.getParameterMetaData()); + } + } catch (IOException e) { + // ignore exception. + } + try (PreparedStatement ps = + connection.prepareStatement(getConcertsInsertReturningQuery(dialect.dialect))) { + assertDefaultParameterMetaData( + ps.getParameterMetaData(), getConcertExpectedParamCount(dialect.dialect)); + for (Concert concert : createConcerts()) { + concert.setPreparedStatement(connection, ps, getDialect()); + assertInsertConcertParameterMetadata(ps.getParameterMetaData()); + try (ResultSet rs = ps.executeQuery()) { + rs.next(); + concert.assertEqualsFields(connection, rs, dialect.dialect); + } + // check that calling executeQuery will not reset the meta data + assertInsertConcertParameterMetadata(ps.getParameterMetaData()); + } + } + connection.commit(); + } + } + private List readValuesFromFile(String filename) { StringBuilder builder = new StringBuilder(); try (InputStream stream = ITJdbcPreparedStatementTest.class.getResourceAsStream(filename)) { diff --git a/versions.txt b/versions.txt index 104164ed..11ecba39 100644 --- a/versions.txt +++ b/versions.txt @@ -1,4 +1,4 @@ # Format: # module:released-version:current-version -google-cloud-spanner-jdbc:2.8.0:2.8.0 +google-cloud-spanner-jdbc:2.9.0:2.9.0