Daten mithilfe eines Parameters abfragen.
Dokumentationsseiten mit diesem Codebeispiel
Die folgenden Dokumente enthalten das Codebeispiel im Kontext:
- Erste Schritte mit Cloud Spanner in C#
- Erste Schritte mit Cloud Spanner in C++
- Erste Schritte mit Cloud Spanner in Go
- Erste Schritte mit Cloud Spanner in Java
- Erste Schritte mit Cloud Spanner in Node.js
- Erste Schritte mit Cloud Spanner in PHP
- Erste Schritte mit Cloud Spanner in Python
- Erste Schritte mit Cloud Spanner in Ruby
Codebeispiel
C#
using Google.Cloud.Spanner.Data;
using System.Collections.Generic;
using System.Threading.Tasks;
public class QueryWithParameterAsyncSample
{
public class Singer
{
public int SingerId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
public async Task<List<Singer>> QueryWithParameterAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
using var cmd = connection.CreateSelectCommand(
$"SELECT SingerId, FirstName, LastName FROM Singers WHERE LastName = @lastName",
new SpannerParameterCollection { { "lastName", SpannerDbType.String, "Garcia" } });
var singers = new List<Singer>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
singers.Add(new Singer
{
SingerId = reader.GetFieldValue<int>("SingerId"),
FirstName = reader.GetFieldValue<string>("FirstName"),
LastName = reader.GetFieldValue<string>("LastName")
});
}
return singers;
}
}
C++
void QueryWithParameter(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
spanner::SqlStatement select(
"SELECT SingerId, FirstName, LastName FROM Singers"
" WHERE LastName = @last_name",
{{"last_name", spanner::Value("Garcia")}});
using RowType = std::tuple<std::int64_t, std::string, std::string>;
auto rows = client.ExecuteQuery(std::move(select));
for (auto const& row : spanner::StreamOf<RowType>(rows)) {
if (!row) throw std::runtime_error(row.status().message());
std::cout << "SingerId: " << std::get<0>(*row) << "\t";
std::cout << "FirstName: " << std::get<1>(*row) << "\t";
std::cout << "LastName: " << std::get<2>(*row) << "\n";
}
std::cout << "Query completed for [spanner_query_with_parameter]\n";
}
Go
func queryWithParameter(ctx context.Context, w io.Writer, client *spanner.Client) error {
stmt := spanner.Statement{
SQL: `SELECT SingerId, FirstName, LastName FROM Singers
WHERE LastName = @lastName`,
Params: map[string]interface{}{
"lastName": "Garcia",
},
}
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
var firstName, lastName string
if err := row.Columns(&singerID, &firstName, &lastName); err != nil {
return err
}
fmt.Fprintf(w, "%d %s %s\n", singerID, firstName, lastName)
}
}
Java
static void queryWithParameter(DatabaseClient dbClient) {
Statement statement =
Statement.newBuilder(
"SELECT SingerId, FirstName, LastName "
+ "FROM Singers "
+ "WHERE LastName = @lastName")
.bind("lastName")
.to("Garcia")
.build();
try (ResultSet resultSet = dbClient.singleUse().executeQuery(statement)) {
while (resultSet.next()) {
System.out.printf(
"%d %s %s\n",
resultSet.getLong("SingerId"),
resultSet.getString("FirstName"),
resultSet.getString("LastName"));
}
}
}
Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* 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, FirstName, LastName
FROM Singers WHERE LastName = @lastName`,
params: {
lastName: 'Garcia',
},
};
// Queries rows from the Albums table
try {
const [rows] = await database.run(query);
rows.forEach(row => {
const json = row.toJSON();
console.log(
`SingerId: ${json.SingerId}, FirstName: ${json.FirstName}, LastName: ${json.LastName}`
);
});
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Queries sample data from the database using SQL with a parameter.
* Example:
* ```
* query_data_with_parameter($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function query_data_with_parameter($instanceId, $databaseId)
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$results = $database->execute(
'SELECT SingerId, FirstName, LastName FROM Singers ' .
'WHERE LastName = @lastName',
['parameters' => ['lastName' => 'Garcia']]
);
foreach ($results as $row) {
printf('SingerId: %s, FirstName: %s, LastName: %s' . PHP_EOL,
$row['SingerId'], $row['FirstName'], $row['LastName']);
}
}
Python
# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
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, FirstName, LastName FROM Singers "
"WHERE LastName = @lastName",
params={"lastName": "Garcia"},
param_types={"lastName": spanner.param_types.STRING},
)
for row in results:
print(u"SingerId: {}, FirstName: {}, LastName: {}".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
sql_query = "SELECT SingerId, FirstName, LastName
FROM Singers
WHERE LastName = @lastName"
params = { lastName: "Garcia" }
param_types = { lastName: :STRING }
client.execute(sql_query, params: params, types: param_types).rows.each do |row|
puts "#{row[:SingerId]} #{row[:FirstName]} #{row[:LastName]}"
end