Skip to content

Commit 89ca142

Browse files
jsimonwebjsimonweb
authored
Add Cloud Spanner DML/PDML samples. (GoogleCloudPlatform#1227)
1 parent 8a351da commit 89ca142

File tree

3 files changed

+335
-5
lines changed

3 files changed

+335
-5
lines changed

‎spanner/cloud-client/pom.xml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -42,7 +42,7 @@ limitations under the License.
4242
<dependency>
4343
<groupId>com.google.cloud</groupId>
4444
<artifactId>google-cloud-bom</artifactId>
45-
<version>0.52.0-alpha</version>
45+
<version>0.66.0-alpha</version>
4646
<type>pom</type>
4747
<scope>import</scope>
4848
</dependency>

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

Lines changed: 287 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -581,6 +581,22 @@ static void queryMarketingBudgetWithTimestamp(DatabaseClient dbClient) {
581581
}
582582
// [END spanner_query_data_with_timestamp_column]
583583

584+
static void querySingersTable(DatabaseClient dbClient) {
585+
ResultSet resultSet =
586+
dbClient
587+
.singleUse()
588+
.executeQuery(
589+
Statement.of(
590+
"SELECT SingerId, FirstName, LastName FROM Singers"));
591+
while (resultSet.next()) {
592+
System.out.printf(
593+
"%s %s %s\n",
594+
resultSet.getLong("SingerId"),
595+
resultSet.getString("FirstName"),
596+
resultSet.getString("LastName"));
597+
}
598+
}
599+
584600
static void queryPerformancesTable(DatabaseClient dbClient) {
585601
// Rows without an explicit value for Revenue will have a Revenue equal to
586602
// null.
@@ -640,8 +656,8 @@ static void queryWithStruct(DatabaseClient dbClient) {
640656
Statement s =
641657
Statement.newBuilder(
642658
"SELECT SingerId FROM Singers "
643-
+ "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
644-
+ "= @name")
659+
+ "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
660+
+ "= @name")
645661
.bind("name")
646662
.to(name)
647663
.build();
@@ -676,8 +692,8 @@ static void queryWithArrayOfStruct(DatabaseClient dbClient) {
676692
Statement s =
677693
Statement.newBuilder(
678694
"SELECT SingerId FROM Singers WHERE "
679-
+ "STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
680-
+ "IN UNNEST(@names)")
695+
+ "STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
696+
+ "IN UNNEST(@names)")
681697
.bind("names")
682698
.toStructArray(nameType, bandMembers)
683699
.build();
@@ -756,6 +772,229 @@ static void queryNestedStructField(DatabaseClient dbClient) {
756772
}
757773
// [END spanner_field_access_on_nested_struct_parameters]
758774

775+
// [START spanner_dml_standard_insert]
776+
static void insertUsingDml(DatabaseClient dbClient) {
777+
dbClient
778+
.readWriteTransaction()
779+
.run(
780+
new TransactionCallable<Void>() {
781+
@Override
782+
public Void run(TransactionContext transaction) throws Exception {
783+
String sql =
784+
"INSERT INTO Singers (SingerId, FirstName, LastName) "
785+
+ " VALUES (10, 'Virginia', 'Watson')";
786+
long rowCount = transaction.executeUpdate(Statement.of(sql));
787+
System.out.printf("%d record inserted.\n", rowCount);
788+
return null;
789+
}
790+
});
791+
}
792+
// [END spanner_dml_standard_insert]
793+
794+
// [START spanner_dml_standard_update]
795+
static void updateUsingDml(DatabaseClient dbClient) {
796+
dbClient
797+
.readWriteTransaction()
798+
.run(
799+
new TransactionCallable<Void>() {
800+
@Override
801+
public Void run(TransactionContext transaction) throws Exception {
802+
String sql =
803+
"UPDATE Albums "
804+
+ "SET MarketingBudget = MarketingBudget * 2 "
805+
+ "WHERE SingerId = 1 and AlbumId = 1";
806+
long rowCount = transaction.executeUpdate(Statement.of(sql));
807+
System.out.printf("%d record updated.\n", rowCount);
808+
return null;
809+
}
810+
});
811+
}
812+
// [END spanner_dml_standard_update]
813+
814+
// [START spanner_dml_standard_delete]
815+
static void deleteUsingDml(DatabaseClient dbClient) {
816+
dbClient
817+
.readWriteTransaction()
818+
.run(
819+
new TransactionCallable<Void>() {
820+
@Override
821+
public Void run(TransactionContext transaction) throws Exception {
822+
String sql = "DELETE FROM Singers WHERE FirstName = 'Alice'";
823+
long rowCount = transaction.executeUpdate(Statement.of(sql));
824+
System.out.printf("%d record deleted.\n", rowCount);
825+
return null;
826+
}
827+
});
828+
}
829+
// [END spanner_dml_standard_delete]
830+
831+
// [START spanner_dml_standard_update_with_timestamp]
832+
static void updateUsingDmlWithTimestamp(DatabaseClient dbClient) {
833+
dbClient
834+
.readWriteTransaction()
835+
.run(
836+
new TransactionCallable<Void>() {
837+
@Override
838+
public Void run(TransactionContext transaction) throws Exception {
839+
String sql =
840+
"UPDATE Albums "
841+
+ "SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1";
842+
long rowCount = transaction.executeUpdate(Statement.of(sql));
843+
System.out.printf("%d records updated.\n", rowCount);
844+
return null;
845+
}
846+
});
847+
}
848+
// [END spanner_dml_standard_update_with_timestamp]
849+
850+
// [START spanner_dml_write_then_read]
851+
static void writeAndReadUsingDml(DatabaseClient dbClient) {
852+
dbClient
853+
.readWriteTransaction()
854+
.run(
855+
new TransactionCallable<Void>() {
856+
@Override
857+
public Void run(TransactionContext transaction) throws Exception {
858+
// Insert record.
859+
String sql =
860+
"INSERT INTO Singers (SingerId, FirstName, LastName) "
861+
+ " VALUES (11, 'Timothy', 'Campbell')";
862+
long rowCount = transaction.executeUpdate(Statement.of(sql));
863+
System.out.printf("%d record inserted.\n", rowCount);
864+
// Read newly inserted record.
865+
sql = "SELECT FirstName, LastName FROM Singers WHERE SingerId = 11";
866+
ResultSet resultSet = transaction.executeQuery(Statement.of(sql));
867+
while (resultSet.next()) {
868+
System.out.printf(
869+
"%s %s\n", resultSet.getString("FirstName"), resultSet.getString("LastName"));
870+
}
871+
return null;
872+
}
873+
});
874+
}
875+
// [END spanner_dml_write_then_read]
876+
877+
// [START spanner_dml_structs]
878+
static void updateUsingDmlWithStruct(DatabaseClient dbClient) {
879+
Struct name =
880+
Struct.newBuilder().set("FirstName").to("Timothy").set("LastName").to("Campbell").build();
881+
Statement s =
882+
Statement.newBuilder(
883+
"UPDATE Singers SET LastName = 'Grant' "
884+
+ "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
885+
+ "= @name")
886+
.bind("name")
887+
.to(name)
888+
.build();
889+
dbClient
890+
.readWriteTransaction()
891+
.run(
892+
new TransactionCallable<Void>() {
893+
@Override
894+
public Void run(TransactionContext transaction) throws Exception {
895+
long rowCount = transaction.executeUpdate(s);
896+
System.out.printf("%d record updated.\n", rowCount);
897+
return null;
898+
}
899+
});
900+
}
901+
// [END spanner_dml_structs]
902+
903+
// [START spanner_dml_getting_started_insert]
904+
static void writeUsingDml(DatabaseClient dbClient) {
905+
// Insert 4 singer records
906+
dbClient
907+
.readWriteTransaction()
908+
.run(
909+
new TransactionCallable<Void>() {
910+
@Override
911+
public Void run(TransactionContext transaction) throws Exception {
912+
String sql =
913+
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
914+
+ "(12, 'Melissa', 'Garcia'), "
915+
+ "(13, 'Russell', 'Morales'), "
916+
+ "(14, 'Jacqueline', 'Long'), "
917+
+ "(15, 'Dylan', 'Shaw')";
918+
long rowCount = transaction.executeUpdate(Statement.of(sql));
919+
System.out.printf("%d records inserted.\n", rowCount);
920+
return null;
921+
}
922+
});
923+
}
924+
// [END spanner_dml_getting_started_insert]
925+
926+
// [START spanner_dml_getting_started_update]
927+
static void writeWithTransactionUsingDml(DatabaseClient dbClient) {
928+
dbClient
929+
.readWriteTransaction()
930+
.run(
931+
new TransactionCallable<Void>() {
932+
@Override
933+
public Void run(TransactionContext transaction) throws Exception {
934+
// Transfer marketing budget from one album to another. We do it in a transaction to
935+
// ensure that the transfer is atomic.
936+
String sql1 =
937+
"SELECT MarketingBudget from Albums WHERE SingerId = 1 and AlbumId = 1";
938+
ResultSet resultSet = transaction.executeQuery(Statement.of(sql1));
939+
long album1Budget = 0;
940+
while (resultSet.next()) {
941+
album1Budget = resultSet.getLong("MarketingBudget");
942+
}
943+
// Transaction will only be committed if this condition still holds at the time of
944+
// commit. Otherwise it will be aborted and the callable will be rerun by the
945+
// client library.
946+
if (album1Budget >= 300000) {
947+
String sql2 =
948+
"SELECT MarketingBudget from Albums WHERE SingerId = 2 and AlbumId = 2";
949+
ResultSet resultSet2 = transaction.executeQuery(Statement.of(sql2));
950+
long album2Budget = 0;
951+
while (resultSet.next()) {
952+
album2Budget = resultSet2.getLong("MarketingBudget");
953+
}
954+
long transfer = 200000;
955+
album2Budget += transfer;
956+
album1Budget -= transfer;
957+
Statement updateStatement =
958+
Statement.newBuilder(
959+
"UPDATE Albums "
960+
+ "SET MarketingBudget = @AlbumBudget "
961+
+ "WHERE SingerId = 1 and AlbumId = 1")
962+
.bind("AlbumBudget")
963+
.to(album1Budget)
964+
.build();
965+
transaction.executeUpdate(updateStatement);
966+
Statement updateStatement2 =
967+
Statement.newBuilder(
968+
"UPDATE Albums "
969+
+ "SET MarketingBudget = @AlbumBudget "
970+
+ "WHERE SingerId = 2 and AlbumId = 2")
971+
.bind("AlbumBudget")
972+
.to(album2Budget)
973+
.build();
974+
transaction.executeUpdate(updateStatement2);
975+
}
976+
return null;
977+
}
978+
});
979+
}
980+
// [END spanner_dml_getting_started_update]
981+
982+
// [START spanner_dml_partitioned_update]
983+
static void updateUsingPartitionedDml(DatabaseClient dbClient) {
984+
String sql = "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1";
985+
long rowCount = dbClient.executePartitionedUpdate(Statement.of(sql));
986+
System.out.printf("%d records updated.\n", rowCount);
987+
}
988+
// [END spanner_dml_partitioned_update]
989+
990+
// [START spanner_dml_partitioned_delete]
991+
static void deleteUsingPartitionedDml(DatabaseClient dbClient) {
992+
String sql = "DELETE FROM Singers WHERE SingerId > 10";
993+
long rowCount = dbClient.executePartitionedUpdate(Statement.of(sql));
994+
System.out.printf("%d records deleted.\n", rowCount);
995+
}
996+
// [END spanner_dml_partitioned_delete]
997+
759998
static void run(
760999
DatabaseClient dbClient,
7611000
DatabaseAdminClient dbAdminClient,
@@ -822,6 +1061,9 @@ static void run(
8221061
case "writewithtimestamp":
8231062
writeExampleDataWithTimestamp(dbClient);
8241063
break;
1064+
case "querysingerstable":
1065+
querySingersTable(dbClient);
1066+
break;
8251067
case "queryperformancestable":
8261068
queryPerformancesTable(dbClient);
8271069
break;
@@ -840,6 +1082,36 @@ static void run(
8401082
case "querynestedstructfield":
8411083
queryNestedStructField(dbClient);
8421084
break;
1085+
case "insertusingdml":
1086+
insertUsingDml(dbClient);
1087+
break;
1088+
case "updateusingdml":
1089+
updateUsingDml(dbClient);
1090+
break;
1091+
case "deleteusingdml":
1092+
deleteUsingDml(dbClient);
1093+
break;
1094+
case "updateusingdmlwithtimestamp":
1095+
updateUsingDmlWithTimestamp(dbClient);
1096+
break;
1097+
case "writeandreadusingdml":
1098+
writeAndReadUsingDml(dbClient);
1099+
break;
1100+
case "updateusingdmlwithstruct":
1101+
updateUsingDmlWithStruct(dbClient);
1102+
break;
1103+
case "writeusingdml":
1104+
writeUsingDml(dbClient);
1105+
break;
1106+
case "writewithtransactionusingdml":
1107+
writeWithTransactionUsingDml(dbClient);
1108+
break;
1109+
case "updateusingpartitioneddml":
1110+
updateUsingPartitionedDml(dbClient);
1111+
break;
1112+
case "deleteusingpartitioneddml":
1113+
deleteUsingPartitionedDml(dbClient);
1114+
break;
8431115
default:
8441116
printUsageAndExit();
8451117
}
@@ -870,12 +1142,23 @@ static void printUsageAndExit() {
8701142
System.err.println(" SpannerExample querywithtimestamp my-instance example-db");
8711143
System.err.println(" SpannerExample createtablewithtimestamp my-instance example-db");
8721144
System.err.println(" SpannerExample writewithtimestamp my-instance example-db");
1145+
System.err.println(" SpannerExample querysingerstable my-instance example-db");
8731146
System.err.println(" SpannerExample queryperformancestable my-instance example-db");
8741147
System.err.println(" SpannerExample writestructdata my-instance example-db");
8751148
System.err.println(" SpannerExample querywithstruct my-instance example-db");
8761149
System.err.println(" SpannerExample querywitharrayofstruct my-instance example-db");
8771150
System.err.println(" SpannerExample querystructfield my-instance example-db");
8781151
System.err.println(" SpannerExample querynestedstructfield my-instance example-db");
1152+
System.err.println(" SpannerExample insertusingdml my-instance example-db");
1153+
System.err.println(" SpannerExample updateusingdml my-instance example-db");
1154+
System.err.println(" SpannerExample deleteusingdml my-instance example-db");
1155+
System.err.println(" SpannerExample updateusingdmlwithtimestamp my-instance example-db");
1156+
System.err.println(" SpannerExample writeandreadusingdml my-instance example-db");
1157+
System.err.println(" SpannerExample updateusingdmlwithstruct my-instance example-db");
1158+
System.err.println(" SpannerExample writeusingdml my-instance example-db");
1159+
System.err.println(" SpannerExample writewithtransactionusingdml my-instance example-db");
1160+
System.err.println(" SpannerExample updateusingpartitioneddml my-instance example-db");
1161+
System.err.println(" SpannerExample deleteusingpartitioneddml my-instance example-db");
8791162
System.exit(1);
8801163
}
8811164

0 commit comments

Comments
 (0)