8000 Cloud Spanner: Fix samples that transfer money between albums (#1438) · llozano100/java-docs-samples@1ff443f · GitHub
[go: up one dir, main page]
Skip to content

Commit 1ff443f

Browse files
hegemonicjsimonweb
hegemonic
authored and
jsimonweb
committed
Cloud Spanner: Fix samples that transfer money between albums (GoogleCloudPlatform#1438)
* Cloud Spanner: Fix samples that transfer money between albums The `writeWithTransactionUsingDml` method moved $200,000 from Album1 to Album2. However, Album1 is initialized with a budget of $100,000. As a result, you couldn't work through the getting started doc [1] from start to finish. Also, there were some unnecessary inconsistencies between `writeWithTransaction` and `writeWithTransactionUsingDml`, and neither method explicitly compared the transfer amount with the source album's existing balance. I changed the methods so they both transfer $200,000 from Album2 to Album1. I also added explicit checks against the existing balance, and I updated the tests as needed. (This is an updated version of GoogleCloudPlatform#1432.) [1]: https://cloud.google.com/spanner/docs/getting-started/java/ * get the budget from the correct result set; update tests
1 parent f27121b commit 1ff443f

File tree

2 files changed

+67
-76
lines changed

2 files changed

+67
-76
lines changed

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

Lines changed: 60 additions & 66 deletions
Original file line numberDiff line numberDiff line change
@@ -60,8 +60,8 @@
6060
* <li>Writing data using a read-write transaction.
6161
* <li>Using an index to read and execute SQL queries over data.
6262
* <li>Using commit timestamp for tracking when a record was last updated.
63-
* <li>Using Google API Extensions for Java to make thread-safe requests via
64-
* long-running operations. http://googleapis.github.io/gax-java/
63+
* <li>Using Google API Extensions for Java to make thread-safe requests via long-running
64+
* operations. http://googleapis.github.io/gax-java/
6565
* </ul>
6666
*/
6767
public class SpannerSample {
@@ -262,13 +262,13 @@ static void deleteExampleData(DatabaseClient dbClient) {
262262
// KeySet.singleKey() can be used to delete one row at a time.
263263
for (Singer singer : SINGERS) {
264264
mutations.add(
265-
Mutation.delete("Singers",
266-
KeySet.singleKey(Key.newBuilder().append(singer.singerId).build())));
265+
Mutation.delete(
266+
"Singers", KeySet.singleKey(Key.newBuilder().append(singer.singerId).build())));
267267
}
268268

269269
dbClient.write(mutations);
270270
System.out.printf("Records deleted.\n");
271-
}
271+
}
272272
// [END spanner_delete_data]
273273

274274
// [START spanner_query_data]
@@ -306,12 +306,11 @@ static void read(DatabaseClient dbClient) {
306306
// [START spanner_add_column]
307307
static void addMarketingBudget(DatabaseAdminClient adminClient, DatabaseId dbId) {
308308
OperationFuture<Void, UpdateDatabaseDdlMetadata> op =
309-
adminClient
310-
.updateDatabaseDdl(
311-
dbId.getInstanceId().getInstance(),
312-
dbId.getDatabase(),
313-
Arrays.asList("ALTER TABLE Albums ADD COLUMN MarketingBudget INT64"),
314-
null);
309+
adminClient.updateDatabaseDdl(
310+
dbId.getInstanceId().getInstance(),
311+
dbId.getDatabase(),
312+
Arrays.asList("ALTER TABLE Albums ADD COLUMN MarketingBudget INT64"),
313+
null);
315314
try {
316315
// Initiate the request which returns an OperationFuture.
317316
op.get();
@@ -372,12 +371,12 @@ public Void run(TransactionContext transaction) throws Exception {
372371
// Transaction will only be committed if this condition still holds at the time of
373372
// commit. Otherwise it will be aborted and the callable will be rerun by the
374373
// client library.
375-
if (album2Budget >= 300000) {
374+
long transfer = 200000;
375+
if (album2Budget >= transfer) {
376376
long album1Budget =
377377
transaction
378378
.readRow("Albums", Key.of(1, 1), Arrays.asList("MarketingBudget"))
379379
.getLong(0);
380-
long transfer = 200000;
381380
album1Budget += transfer;
382381
album2Budget -= transfer;
383382
transaction.buffer(
@@ -428,12 +427,11 @@ static void queryMarketingBudget(DatabaseClient dbClient) {
428427
// [START spanner_create_index]
429428
static void addIndex(DatabaseAdminClient adminClient, DatabaseId dbId) {
430429
OperationFuture<Void, UpdateDatabaseDdlMetadata> op =
431-
adminClient
432-
.updateDatabaseDdl(
433-
dbId.getInstanceId().getInstance(),
434-
dbId.getDatabase(),
435-
Arrays.asList("CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)"),
436-
null);
430+
adminClient.updateDatabaseDdl(
431+
dbId.getInstanceId().getInstance(),
432+
dbId.getDatabase(),
433+
Arrays.asList("CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)"),
434+
null);
437435
try {
438436
// Initiate the request which returns an OperationFuture.
439437
op.get();
@@ -499,14 +497,13 @@ static void readUsingIndex(DatabaseClient dbClient) {
499497
// [START spanner_create_storing_index]
500498
static void addStoringIndex(DatabaseAdminClient adminClient, DatabaseId dbId) {
501499
OperationFuture<Void, UpdateDatabaseDdlMetadata> op =
502-
adminClient
503-
.updateDatabaseDdl(
504-
dbId.getInstanceId().getInstance(),
505-
dbId.getDatabase(),
506-
Arrays.asList(
507-
"CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) "
508-
+ "STORING (MarketingBudget)"),
509-
null);
500+
adminClient.updateDatabaseDdl(
501+
dbId.getInstanceId().getInstance(),
502+
dbId.getDatabase(),
503+
Arrays.asList(
504+
"CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) "
505+
+ "STORING (MarketingBudget)"),
506+
null);
510507
try {
511508
// Initiate the request which returns an OperationFuture.
512509
op.get();
@@ -589,14 +586,13 @@ static void readStaleData(DatabaseClient dbClient) {
589586
// [START spanner_add_timestamp_column]
590587
static void addCommitTimestamp(DatabaseAdminClient adminClient, DatabaseId dbId) {
591588
OperationFuture<Void, UpdateDatabaseDdlMetadata> op =
592-
adminClient
593-
.updateDatabaseDdl(
594-
dbId.getInstanceId().getInstance(),
595-
dbId.getDatabase(),
596-
Arrays.asList(
597-
"ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP "
598-
+ "OPTIONS (allow_commit_timestamp=true)"),
599-
null);
589+
adminClient.updateDatabaseDdl(
590+
dbId.getInstanceId().getInstance(),
591+
dbId.getDatabase(),
592+
Arrays.asList(
593+
"ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP "
594+
+ "OPTIONS (allow_commit_timestamp=true)"),
595+
null);
600596
try {
601597
// Initiate the request which returns an OperationFuture.
602598
op.get();
@@ -675,9 +671,7 @@ static void querySingersTable(DatabaseClient dbClient) {
675671
ResultSet resultSet =
676672
dbClient
677673
.singleUse()
678-
.executeQuery(
679-
Statement.of(
680-
"SELECT SingerId, FirstName, LastName FROM Singers"));
674+
.executeQuery(Statement.of("SELECT SingerId, FirstName, LastName FROM Singers"));
681675
while (resultSet.next()) {
682676
System.out.printf(
683677
"%s %s %s\n",
@@ -1016,8 +1010,7 @@ public Void run(TransactionContext transaction) throws Exception {
10161010
// [START spanner_query_with_parameter]
10171011
static void queryWithParameter(DatabaseClient dbClient) {
10181012
Statement statement =
1019-
Statement
1020-
.newBuilder(
1013+
Statement.newBuilder(
10211014
"SELECT SingerId, FirstName, LastName\n"
10221015
+ "FROM Singers\n"
10231016
+ "WHERE LastName = @lastName")
@@ -1047,40 +1040,40 @@ public Void run(TransactionContext transaction) throws Exception {
10471040
// Transfer marketing budget from one album to another. We do it in a transaction to
10481041
// ensure that the transfer is atomic.
10491042
String sql1 =
1050-
"SELECT MarketingBudget from Albums WHERE SingerId = 1 and AlbumId = 1";
1043+
"SELECT MarketingBudget from Albums WHERE SingerId = 2 and AlbumId = 2";
10511044
ResultSet resultSet = transaction.executeQuery(Statement.of(sql1));
1052-
long album1Budget = 0;
1045+
long album2Budget = 0;
10531046
while (resultSet.next()) {
1054-
album1Budget = resultSet.getLong("MarketingBudget");
1047+
album2Budget = resultSet.getLong("MarketingBudget");
10551048
}
10561049
// Transaction will only be committed if this condition still holds at the time of
10571050
// commit. Otherwise it will be aborted and the callable will be rerun by the
10581051
// client library.
1059-
if (album1Budget >= 300000) {
1052+
long transfer = 200000;
1053+
if (album2Budget >= transfer) {
10601054
String sql2 =
1061-
"SELECT MarketingBudget from Albums WHERE SingerId = 2 and AlbumId = 2";
1055+
"SELECT MarketingBudget from Albums WHERE SingerId = 1 and AlbumId = 1";
10621056
ResultSet resultSet2 = transaction.executeQuery(Statement.of(sql2));
1063-
long album2Budget = 0;
1064-
while (resultSet.next()) {
1065-
album2Budget = resultSet2.getLong("MarketingBudget");
1057+
long album1Budget = 0;
1058+
while (resultSet2.next()) {
1059+
album1Budget = resultSet2.getLong("MarketingBudget");
10661060
}
1067-
long transfer = 200000;
1068-
album2Budget += transfer;
1069-
album1Budget -= transfer;
1061+
album1Budget += transfer;
1062+
album2Budget -= transfer;
10701063
Statement updateStatement =
10711064
Statement.newBuilder(
1072-
"UPDATE Albums "
1073-
+ "SET MarketingBudget = @AlbumBudget "
1074-
+ "WHERE SingerId = 1 and AlbumId = 1")
1065+
"UPDATE Albums "
1066+
+ "SET MarketingBudget = @AlbumBudget "
1067+
+ "WHERE SingerId = 1 and AlbumId = 1")
10751068
.bind("AlbumBudget")
10761069
.to(album1Budget)
10771070
.build();
10781071
transaction.executeUpdate(updateStatement);
10791072
Statement updateStatement2 =
10801073
Statement.newBuilder(
1081-
"UPDATE Albums "
1082-
+ "SET MarketingBudget = @AlbumBudget "
1083-
+ "WHERE SingerId = 2 and AlbumId = 2")
1074+
"UPDATE Albums "
1075+
+ "SET MarketingBudget = @AlbumBudget "
1076+
+ "WHERE SingerId = 2 and AlbumId = 2")
10841077
.bind("AlbumBudget")
10851078
.to(album2Budget)
10861079
.build();
@@ -1108,7 +1101,7 @@ static void deleteUsingPartitionedDml(DatabaseClient dbClient) {
11081101
}
11091102
// [END spanner_dml_partitioned_delete]
11101103

1111-
// [START spanner_dml_batch_update]
1104+
// [START spanner_dml_batch_update]
11121105
static void updateUsingBatchDml(DatabaseClient dbClient) {
11131106
dbClient
11141107
.readWriteTransaction()
@@ -1117,23 +1110,24 @@ static void updateUsingBatchDml(DatabaseClient dbClient) {
11171110
@Override
11181111
public Void run(TransactionContext transaction) throws Exception {
11191112
List<Statement> stmts = new ArrayList<Statement>();
1120-
String sql = "INSERT INTO Albums "
1121-
+ "(SingerId, AlbumId, AlbumTitle, MarketingBudget) "
1122-
+ "VALUES (1, 3, 'Test Album Title', 10000) ";
1113+
String sql =
1114+
"INSERT INTO Albums "
1115+
+ "(SingerId, AlbumId, AlbumTitle, MarketingBudget) "
1116+
+ "VALUES (1, 3, 'Test Album Title', 10000) ";
11231117
stmts.add(Statement.of(sql));
1124-
sql = "UPDATE Albums "
1118+
sql =
1119+
"UPDATE Albums "
11251120
+ "SET MarketingBudget = MarketingBudget * 2 "
11261121
+ "WHERE SingerId = 1 and AlbumId = 3";
11271122
stmts.add(Statement.of(sql));
1128-
long [] rowCounts;
1123+
long[] rowCounts;
11291124
try {
11301125
rowCounts = transaction.batchUpdate(stmts);
11311126
} catch (SpannerBatchUpdateException e) {
11321127
rowCounts = e.getUpdateCounts();
11331128
}
11341129
for (int i = 0; i < rowCounts.length; i++) {
1135-
System.out.printf(
1136-
"%d record updated by stmt %d.\n", rowCounts[i], i);
1130+
System.out.printf("%d record updated by stmt %d.\n", rowCounts[i], i);
11371131
}
11381132
return null;
11391133
}
@@ -1298,7 +1292,7 @@ static void printUsageAndExit() {
12981292
System.err.println(" SpannerExample querywithtimestamp my-instance example-db");
12991293
System.err.println(" SpannerExample createtablewithtimestamp my-instance example-db");
13001294
System.err.println(" SpannerExample writewithtimestamp my-instance example-db");
1301-
System.err.println(" SpannerExample querysingerstable my-instance example-db");
1295+
System.err.println(" SpannerExample querysingerstable my-instance example-db");
13021296
System.err.println(" SpannerExample queryperformancestable my-instance example-db");
13031297
System.err.println(" SpannerExample writestructdata my-instance example-db");
13041298
System.err.println(" SpannerExample querywithstruct my-instance example-db");

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

Lines changed: 7 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,6 @@
2222
import com.google.cloud.spanner.DatabaseId;
2323
import com.google.cloud.spanner.Spanner;
2424
import com.google.cloud.spanner.SpannerOptions;
25-
2625
import java.io.ByteArrayOutputStream;
2726
import java.io.PrintStream;
2827
import java.util.UUID;
@@ -32,9 +31,7 @@
3231
import org.junit.runner.RunWith;
3332
import org.junit.runners.JUnit4;
3433

35-
/**
36-
* Unit tests for {@code SpannerSample}
37-
*/
34+
/** Unit tests for {@code SpannerSample} */
3835
@RunWith(JUnit4.class)
3936
@SuppressWarnings("checkstyle:abbreviationaswordinname")
4037
public class SpannerSampleIT {
@@ -50,7 +47,7 @@ private String runSample(String command) throws Exception {
5047
ByteArrayOutputStream bout = new ByteArrayOutputStream();
5148
PrintStream out = new PrintStream(bout);
5249
System.setOut(out);
53-
SpannerSample.main(new String[]{command, instanceId, databaseId});
50+
SpannerSample.main(new String[] {command, instanceId, databaseId});
5451
System.setOut(stdOut);
5552
return bout.toString();
5653
}
@@ -185,13 +182,13 @@ public void testSample() throws Exception {
185182
assertThat(out).contains("12 Melissa Garcia");
186183

187184
runSample("writewithtransactionusingdml");
188-
out = runSample("querymarketingbudget");
189-
assertThat(out).contains("1 1 1800000");
190-
assertThat(out).contains("2 2 200000");
191-
185+
out = runSample("querymarketingbudget");
186+
assertThat(out).contains("1 1 2200000");
187+
assertThat(out).contains("2 2 550000");
188+
192189
runSample("updateusingpartitioneddml");
193190
out = runSample("querymarketingbudget");
194-
assertThat(out).contains("1 1 1800000");
191+
assertThat(out).contains("1 1 2200000");
195192
assertThat(out).contains("2 2 100000");
196193

197194
runSample("deleteusingpartitioneddml");

0 commit comments

Comments
 (0)
0