STRUCT オブジェクトを使用してデータをクエリします。
このコードサンプルが含まれるドキュメント ページ
コンテキストで使用されているコードサンプルを見るには、次のドキュメントをご覧ください。
コードサンプル
C#
using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;
public class QueryDataWithStructAsyncSample
{
public async Task<List<int>> QueryDataWithStructAsync(string projectId, string instanceId, string databaseId)
{
var nameStruct = new SpannerStruct
{
{ "FirstName", SpannerDbType.String, "Elena" },
{ "LastName", SpannerDbType.String, "Campbell" },
};
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
var singerIds = new List<int>();
using var connection = new SpannerConnection(connectionString);
using var cmd = connection.CreateSelectCommand(
"SELECT SingerId FROM Singers "
+ "WHERE STRUCT<FirstName STRING, LastName STRING>"
+ "(FirstName, LastName) = @name");
cmd.Parameters.Add("name", nameStruct.GetSpannerDbType(), nameStruct);
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
singerIds.Add(reader.GetFieldValue<int>("SingerId"));
}
return singerIds;
}
}
C++
void QueryDataWithStruct(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
// Cloud Spanner STRUCT<> types are represented by std::tuple<...>. The
// following represents a STRUCT<> with two unnamed STRING fields.
using NameType = std::tuple<std::string, std::string>;
auto singer_info = NameType{"Elena", "Campbell"};
auto rows = client.ExecuteQuery(spanner::SqlStatement(
"SELECT SingerId FROM Singers WHERE (FirstName, LastName) = @name",
{{"name", spanner::Value(singer_info)}}));
//! [spanner-sql-statement-params]
for (auto const& row : spanner::StreamOf<std::tuple<std::int64_t>>(rows)) {
if (!row) throw std::runtime_error(row.status().message());
std::cout << "SingerId: " << std::get<0>(*row) << "\n";
}
std::cout << "Query completed for [spanner_query_data_with_struct]\n";
}
Go
stmt := spanner.Statement{
SQL: `SELECT SingerId FROM SINGERS
WHERE (FirstName, LastName) = @singerinfo`,
Params: map[string]interface{}{"singerinfo": singerInfo},
}
iter := client.Single().Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
return nil
}
if err != nil {
return err
}
var singerID int64
if err := row.Columns(&singerID); err != nil {
return err
}
fmt.Fprintf(w, "%d\n", singerID)
}
Java
Statement s =
Statement.newBuilder(
"SELECT SingerId FROM Singers "
+ "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
+ "= @name")
.bind("name")
.to(name)
.build();
try (ResultSet resultSet = dbClient.singleUse().executeQuery(s)) {
while (resultSet.next()) {
System.out.printf("%d\n", resultSet.getLong("SingerId"));
}
}
Node.js
/**
* TODO(developer): Uncomment the following lines before running the sample.
*/
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';
// Creates a client
const spanner = new Spanner({
projectId: projectId,
});
// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
const query = {
sql:
'SELECT SingerId FROM Singers WHERE ' +
'STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name',
params: {
name: nameStruct,
},
};
// Queries rows from the Singers table
try {
const [rows] = await database.run(query);
rows.forEach(row => {
const json = row.toJSON();
console.log(`SingerId: ${json.SingerId}`);
});
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
PHP
$results = $database->execute(
'SELECT SingerId FROM Singers ' .
'WHERE STRUCT<FirstName STRING, LastName STRING>' .
'(FirstName, LastName) = @name',
[
'parameters' => [
'name' => $nameValue
],
'types' => [
'name' => $nameType
]
]
);
foreach ($results as $row) {
printf('SingerId: %s' . PHP_EOL,
$row['SingerId']);
}
Python
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)
with database.snapshot() as snapshot:
results = snapshot.execute_sql(
"SELECT SingerId FROM Singers WHERE " "(FirstName, LastName) = @name",
params={"name": record_value},
param_types={"name": record_type},
)
for row in results:
print(u"SingerId: {}".format(*row))
Ruby
# project_id = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"
require "google/cloud/spanner"
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.execute(
"SELECT SingerId FROM Singers WHERE " +
"(FirstName, LastName) = @name",
params: { name: name_struct }
).rows.each do |row|
puts row[:SingerId].to_s
end