8000 samples: add standalone numeric samples for Spanner (#3707) · coderatgoogle/java-docs-samples@1b93c01 · GitHub
[go: up one dir, main page]
Skip to content

Commit 1b93c01

Browse files
olavloitelarkee
olavloite
and
larkee
authored
samples: add standalone numeric samples for Spanner (GoogleCloudPlatform#3707)
* samples: add standalone numeric samples for Spanner * fix: review comments * chore: format code * fix: print Venues instead of Records Co-authored-by: larkee <31196561+larkee@users.noreply.github.com> * fix: print Venues instead of Records Co-authored-by: larkee <31196561+larkee@users.noreply.github.com> Co-authored-by: larkee <31196561+larkee@users.noreply.github.com>
1 parent b1e3527 commit 1b93c01

File tree

4 files changed

+276
-1
lines changed

4 files changed

+276
-1
lines changed

‎spanner/cloud-client/src/main/java/com/example/spanner/AddNumericColumnSample.java

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,58 @@
1+
/*
2+
* Copyright 2020 Google Inc.
3+
*
4+
* Licensed under the Apache License, Version 2.0 (the "License");
5+
* you may not use this file except in compliance with the License.
6+
* You may obtain a copy of the License at
7+
*
8+
* http://www.apache.org/licenses/LICENSE-2.0
9+
*
10+
* Unless required by applicable law or agreed to in writing, software
11+
* distributed under the License is distributed on an "AS IS" BASIS,
12+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
* See the License for the specific language governing permissions and
14+
* limitations under the License.
15+
*/
16+
17+
package com.example.spanner;
18+
19+
// [START spanner_add_numeric_column]
20+
import com.google.api.gax.longrunning.OperationFuture;
21+
import com.google.cloud.spanner.DatabaseAdminClient;
22+
import com.google.cloud.spanner.Spanner;
23+
import com.google.cloud.spanner.SpannerOptions;
24+
import com.google.common.collect.ImmutableList;
25+
import com.google.spanner.admin.database.v1.UpdateDatabaseDdlMetadata;
26+
import java.util.concurrent.ExecutionException;
27+
28+
class AddNumericColumnSample {
29+
30+
static void addNumericColumn() throws InterruptedException, ExecutionException {
31+
// TODO(developer): Replace these variables before running the sample.
32+
String projectId = "my-project";
33+
String instanceId = "my-instance";
34+
String databaseId = "my-database";
35+
36+
try (Spanner spanner =
37+
SpannerOptions.newBuilder().setProjectId(projectId).build().getService()) {
38+
DatabaseAdminClient adminClient = spanner.getDatabaseAdminClient();
39+
addNumericColumn(adminClient, instanceId, databaseId);
40+
}
41+
}
42+
43+
static void addNumericColumn(
44+
DatabaseAdminClient adminClient, String instanceId, String databaseId)
45+
throws InterruptedException, ExecutionException {
46+
OperationFuture<Void, UpdateDatabaseDdlMetadata> operation =
47+
adminClient.updateDatabaseDdl(
48+
instanceId,
49+
databaseId,
50+
ImmutableList.of("ALTER TABLE Venues ADD COLUMN Revenue NUMERIC"),
51+
null);
52+
// Wait for the operation to finish.
53+
// This will throw an ExecutionException if the operation fails.
54+
operation.get();
55+
System.out.printf("Successfully added column `Revenue`%n");
56+
}
57+
}
58+
// [END spanner_add_numeric_column]

†6D40 Žspanner/cloud-client/src/main/java/com/example/spanner/QueryWithNumericParameterSample.java

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
/*
2+
* Copyright 2020 Google Inc.
3+
*
4+
* Licensed under the Apache License, Version 2.0 (the "License");
5+
* you may not use this file except in compliance with the License.
6+
* You may obtain a copy of the License at
7+
*
8+
* http://www.apache.org/licenses/LICENSE-2.0
9+
*
10+
* Unless required by applicable law or agreed to in writing, software
11+
* distributed under the License is distributed on an "AS IS" BASIS,
12+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
* See the License for the specific language governing permissions and
14+
* limitations under the License.
15+
*/
16+
17+
package com.example.spanner;
18+
19+
// [START spanner_query_with_numeric_parameter]
20+
import com.google.cloud.spanner.DatabaseClient;
21+
import com.google.cloud.spanner.DatabaseId;
22+
import com.google.cloud.spanner.ResultSet;
23+
import com.google.cloud.spanner.Spanner;
24+
import com.google.cloud.spanner.SpannerOptions;
25+
import com.google.cloud.spanner.Statement;
26+
import java.math.BigDecimal;
27+
28+
class QueryWithNumericParameterSample {
29+
30+
static void queryWithNumericParameter() {
31+
// TODO(developer): Replace these variables before running the sample.
32+
String projectId = "my-project";
33+
String instanceId = "my-instance";
34+
String databaseId = "my-database";
35+
36+
try (Spanner spanner =
37+
SpannerOptions.newBuilder().setProjectId(projectId).build().getService()) {
38+
DatabaseClient client =
39+
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
40+
queryWithNumericParameter(client);
41+
}
42+
}
43+
44+
static void queryWithNumericParameter(DatabaseClient client) {
45+
Statement statement =
46+
Statement.newBuilder(
47+
"SELECT VenueId, Revenue FROM Venues WHERE Revenue < @numeric")
48+
.bind("numeric")
49+
.to(new BigDecimal("100000"))
50+
.build();
51+
try (ResultSet resultSet = client.singleUse().executeQuery(statement)) {
52+
while (resultSet.next()) {
53+
System.out.printf(
54+
"%d %s%n", resultSet.getLong("VenueId"), resultSet.getBigDecimal("Revenue"));
55+
}
56+
}
57+
}
58+
}
59+
// [END spanner_query_with_numeric_parameter]

‎spanner/cloud-client/src/main/java/com/example/spanner/UpdateNumericDataSample.java

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,68 @@
1+
/*
2+
* Copyright 2020 Google Inc.
3+
*
4+
* Licensed under the Apache License, Version 2.0 (the "License");
5+
* you may not use this file except in compliance with the License.
6+
* You may obtain a copy of the License at
7+
*
8+
* http://www.apache.org/licenses/LICENSE-2.0
9+
*
10+
* Unless required by applicable law or agreed to in writing, software
11+
* distributed under the License is distributed on an "AS IS" BASIS,
12+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
* See the License for the specific language governing permissions and
14+
* limitations under the License.
15+
*/
16+
17+
package com.example.spanner;
18+
19+
// [START spanner_update_data_with_numeric_column]
20+
import com.google.cloud.spanner.DatabaseClient;
21+
import com.google.cloud.spanner.DatabaseId;
22+
import com.google.cloud.spanner.Mutation;
23+
import com.google.cloud.spanner.Spanner;
24+
import com.google.cloud.spanner.SpannerOptions;
25+
import com.google.common.collect.ImmutableList;
26+
import java.math.BigDecimal;
27+
28+
class UpdateNumericDataSample {
29+
30+
static void updateNumericData() {
31+
// TODO(developer): Replace these variables before running the sample.
32+
String projectId = "my-project";
33+
String instanceId = "my-instance";
34+
String databaseId = "my-database";
35+
36+
try (Spanner spanner =
37+
SpannerOptions.newBuilder().setProjectId(projectId).build().getService()) {
38+
DatabaseClient client =
39+
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
40+
updateNumericData(client);
41+
}
42+
}
43+
44+
static void updateNumericData(DatabaseClient client) {
45+
client.write(
46+
ImmutableList.of(
47+
Mutation.newInsertOrUpdateBuilder("Venues")
48+
.set("VenueId")
49+
.to(4L)
50+
.set("Revenue")
51+
.to(new BigDecimal("35000"))
52+
.build(),
53+
Mutation.newInsertOrUpdateBuilder("Venues")
54+
.set("VenueId")
55+
.to(19L)
56+
.set("Revenue")
57+
.to(new BigDecimal("104500"))
58+
.build(),
59+
Mutation.newInsertOrUpdateBuilder("Venues")
60+
.set("VenueId")
61+
.to(42L)
62+
.set("Revenue")
63+
.to(new BigDecimal("99999999999999999999999999999.99"))
64+
.build()));
65+
System.out.println("Venues successfully updated");
66+
}
67+
}
68+
// [END spanner_update_data_with_numeric_column]

‎spanner/cloud-client/src/test/java/com/example/spanner/SpannerStandaloneExamplesIT.java

Lines changed: 91 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,17 +18,26 @@
1818

1919
import static com.google.common.truth.Truth.assertThat;
2020

21+
import com.google.api.gax.longrunning.OperationFuture;
2122
import com.google.cloud.spanner.DatabaseAdminClient;
23+
import com.google.cloud.spanner.DatabaseClient;
2224
import com.google.cloud.spanner.DatabaseId;
2325
import com.google.cloud.spanner.Instance;
26+
import com.google.cloud.spanner.KeySet;
27+
import com.google.cloud.spanner.Mutation;
2428
import com.google.cloud.spanner.Spanner;
2529
import com.google.cloud.spanner.SpannerOptions;
2630
import com.google.common.collect.ImmutableList;
31+
import com.google.spanner.admin.database.v1.UpdateDatabaseDdlMetadata;
2732
import java.io.ByteArrayOutputStream;
2833
import java.io.PrintStream;
34+
import java.math.BigDecimal;
35+
import java.util.Collections;
2936
import java.util.Iterator;
3037
import java.util.UUID;
38+
import java.util.concurrent.ExecutionException;
3139
import org.junit.AfterClass;
40+
import org.junit.Before;
3241
import org.junit.BeforeClass;
3342
import org.junit.Test;
3443
import org.junit.runner.RunWith;
@@ -79,7 +88,11 @@ public static void createTestDatabase() throws Exception {
7988
+ " FirstName STRING(1024),"
8089
+ " LastName STRING(1024),"
8190
+ " SingerInfo BYTES(MAX)"
82-
+ ") PRIMARY KEY (SingerId)"))
91+
+ ") PRIMARY KEY (SingerId)",
92+
"CREATE TABLE Venues ("
93+
+ "VenueId INT64 NOT NULL,"
94+
+ "Revenue NUMERIC"
95+
+ ") PRIMARY KEY (VenueId)"))
8396
.get();
8497
}
8598

@@ -89,6 +102,14 @@ public static void dropTestDatabase() throws Exception {
89102
spanner.close();
90103
}
91104

105+
@Before
106+
public void deleteTestData() {
107+
String projectId = spanner.getOptions().getProjectId();
108+
DatabaseClient client =
109+
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
110+
client.write(Collections.singleton(Mutation.delete("Venues", KeySet.all())));
111+
}
112+
92113
@Test
93114
public void executeSqlWithCustomTimeoutAndRetrySettings_shouldWriteData() {
94115
String projectId = spanner.getOptions().getProjectId();
@@ -100,6 +121,75 @@ public void executeSqlWithCustomTimeoutAndRetrySettings_shouldWriteData() {
100121
assertThat(out).contains("1 record inserted.");
101122
}
102123

124+
@Test
125+
public void addNumericColumn_shouldSuccessfullyAddColumn()
126+
throws InterruptedException, ExecutionException {
127+
OperationFuture<Void, UpdateDatabaseDdlMetadata> operation =
128+
spanner
129+
.getDatabaseAdminClient()
130+
.updateDatabaseDdl(
131+
instanceId,
132+
databaseId,
133+
ImmutableList.of("ALTER TABLE Venues DROP COLUMN Revenue"),
134+
null);
135+
operation.get();
136+
String out =
137+
runExample(
138+
() -> {
139+
try {
140+
AddNumericColumnSample.addNumericColumn(
141+
spanner.getDatabaseAdminClient(), instanceId, databaseId);
142+
} catch (ExecutionException e) {
143+
System.out.printf(
144+
"Adding column `Revenue` failed: %s%n", e.getCause().getMessage());
145+
} catch (InterruptedException e) {
146+
System.out.printf("Adding column `Revenue` was interrupted%n");
147+
}
148+
});
149+
assertThat(out).contains("Successfully added column `Revenue`");
150+
}
151+
152+
@Test
153+
public void updateNumericData_shouldWriteData() {
154+
String projectId = spanner.getOptions().getProjectId();
155+
String out =
156+
runExample(
157+
() ->
158+
UpdateNumericDataSample.updateNumericData(
159+
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId))));
160+
assertThat(out).contains("Venues successfully updated");
161+
}
162+
163+
@Test
164+
public void queryWithNumericParameter_shouldReturnResults() {
165+
String projectId = spanner.getOptions().getProjectId();
166+
DatabaseClient client =
167+
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
168+
client.write(
169+
ImmutableList.of(
170+
Mutation.newInsertOrUpdateBuilder("Venues")
171+
.set("VenueId")
172+
.to(4L)
173+
.set("Revenue")
174+
.to(new BigDecimal("35000"))
175+
.build(),
176+
Mutation.newInsertOrUpdateBuilder("Venues")
177+
.set("VenueId")
178+
.to(19L)
179+
.set("Revenue")
180+
.to(new BigDecimal("104500"))
181+
.build(),
182+
Mutation.newInsertOrUpdateBuilder("Venues")
183+
.set("VenueId")
184+
.to(42L)
185+
.set("Revenue")
186+
.to(new BigDecimal("99999999999999999999999999999.99"))
187+
.build()));
188+
String out =
189+
runExample(() -> QueryWithNumericParameterSample.queryWithNumericParameter(client));
190+
assertThat(out).contains("4 35000");
191+
}
192+
103193
static String formatForTest(String name) {
104194
return name + "-" + UUID.randomUUID().toString().substring(0, 20);
105195
}

0 commit comments

Comments
 (0)
0