Prepared Statements

可以对执行多次的 CQL 语句进行准备并将其存储在 PreparedStatement 对象中，以提高查询性能。驱动程序和 Cassandra 都维护了将 PreparedStatement 查询映射到其元数据的映射。您可以通过以下抽象使用已准备好的语句：

Using CqlTemplate

CqlTemplate 类（及其异步和反应式变体）提供各种接受静态 CQL、Statement 对象和 PreparedStatementCreator 的方法。通常，接受静态 CQL 而没有附加参数的方法会按原样运行 CQL 语句，而不会进行进一步处理。接受静态 CQL 和参数数组相结合的方法（例如 execute(String cql, Object…​ args) 和 queryForRows(String cql, Object…​ args)） 会使用已准备好的语句。在内部，这些方法会创建 PreparedStatementCreator 和 PreparedStatementBinder 对象来准备语句并稍后将值绑定到语句以运行它。Spring Data Cassandra 通常对已准备好的语句使用基于索引的参数绑定。

自 Cassandra 4 版以来，已准备好的语句已在驱动程序级别缓存，这就无需在应用程序中跟踪已准备好的语句。

以下示例演示如何使用带参数化的已准备语句发出查询：

  • Imperative

  • Reactive

/*
 * Copyright 2020-2024 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      https:://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.springframework.data.cassandra.example;

import java.util.List;

import org.junit.jupiter.api.Test;
import org.springframework.data.cassandra.core.cql.CqlTemplate;
import org.springframework.data.cassandra.core.cql.RowMapper;
import org.springframework.data.cassandra.core.cql.generator.DropTableCqlGenerator;
import org.springframework.data.cassandra.core.cql.keyspace.DropTableSpecification;

import com.datastax.oss.driver.api.core.cql.Row;

/**
 * @author Mark Paluch
 */
//@formatter:off
public class CqlTemplateExamples {

	private CqlTemplate cqlTemplate = null;

	void examples() {
		// tag::rowCount[]
		int rowCount = cqlTemplate.queryForObject("SELECT COUNT(*) FROM t_actor", Integer.class);
		// end::rowCount[]

		// tag::countOfActorsNamedJoe[]
		int countOfActorsNamedJoe = cqlTemplate.queryForObject(
				"SELECT COUNT(*) FROM t_actor WHERE first_name = ?", Integer.class, "Joe");
		// end::countOfActorsNamedJoe[]

		// tag::lastName[]
		String lastName = cqlTemplate.queryForObject(
				"SELECT last_name FROM t_actor WHERE id = ?",
				String.class, 1212L);
		// end::lastName[]

		// tag::rowMapper[]
		Actor actor = cqlTemplate.queryForObject("SELECT first_name, last_name FROM t_actor WHERE id = ?",
				new RowMapper<Actor>() {
					public Actor mapRow(Row row, int rowNum) {
						Actor actor = new Actor();
						actor.setFirstName(row.getString("first_name"));
						actor.setLastName(row.getString("last_name"));
						return actor;
					}
				}, 1212L);
		// end::rowMapper[]

		// tag::listOfRowMapper[]
		List<Actor> actors = cqlTemplate.query(
				"SELECT first_name, last_name FROM t_actor",
				new RowMapper<Actor>() {
					public Actor mapRow(Row row, int rowNum) {
						Actor actor = new Actor();
						actor.setFirstName(row.getString("first_name"));
						actor.setLastName(row.getString("last_name"));
						return actor;
					}
				});
		// end::listOfRowMapper[]

		// tag::preparedStatement[]
		List<String> lastNames = cqlTemplate.query(
				session -> session.prepare("SELECT last_name FROM t_actor WHERE id = ?"),
				ps -> ps.bind(1212L),
				(row, rowNum) -> row.getString(0));
		// end::preparedStatement[]
	}

	// tag::findAllActors[]
	List<Actor> findAllActors() {
		return cqlTemplate.query("SELECT first_name, last_name FROM t_actor", ActorMapper.INSTANCE);
	}

	enum ActorMapper implements RowMapper<Actor> {

		INSTANCE;

		public Actor mapRow(Row row, int rowNum) {
			Actor actor = new Actor();
			actor.setFirstName(row.getString("first_name"));
			actor.setLastName(row.getString("last_name"));
			return actor;
		}
	}
	// end::findAllActors[]

	@Test
	void prepared() {
		long actorId = 1;

		// tag::insert[]
		cqlTemplate.execute(
				"INSERT INTO t_actor (first_name, last_name) VALUES (?, ?)",
				"Leonor", "Watling");
		// end::insert[]

		// tag::update[]
		cqlTemplate.execute(
				"UPDATE t_actor SET last_name = ? WHERE id = ?",
				"Banjo", 5276L);
		// end::update[]

		// tag::delete[]
		cqlTemplate.execute(
				"DELETE FROM t_actor WHERE id = ?",
				5276L);
		// end::delete[]
	}

	@Test
	void other() {
		// tag::other[]
		cqlTemplate.execute("CREATE TABLE test_table (id uuid primary key, event text)");

		DropTableSpecification dropper = DropTableSpecification.dropTable("test_table");
		String cql = DropTableCqlGenerator.toCql(dropper);

		cqlTemplate.execute(cql);
		// end::other[]
	}

	static class Actor {

		void setFirstName(String first_name) {

		}

		void setLastName(String last_name) {}
	}
}
/*
 * Copyright 2023-2024 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.springframework.data.cassandra.example;

import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

import org.junit.jupiter.api.Test;
import org.springframework.data.cassandra.core.cql.ReactiveCqlTemplate;
import org.springframework.data.cassandra.core.cql.RowMapper;

import com.datastax.oss.driver.api.core.cql.Row;

/**
 * @author Mark Paluch
 */
//@formatter:off
public class ReactiveCqlTemplateExamples {

	private ReactiveCqlTemplate reactiveCqlTemplate = null;

	void examples() {
		// tag::rowCount[]
		Mono<Integer> rowCount = reactiveCqlTemplate.queryForObject("SELECT COUNT(*) FROM t_actor", Integer.class);
		// end::rowCount[]

		// tag::countOfActorsNamedJoe[]
		Mono<Integer> countOfActorsNamedJoe = reactiveCqlTemplate.queryForObject(
			"SELECT COUNT(*) FROM t_actor WHERE first_name = ?", Integer.class, "Joe");
		// end::countOfActorsNamedJoe[]

		// tag::lastName[]
		Mono<String> lastName = reactiveCqlTemplate.queryForObject(
			"SELECT last_name FROM t_actor WHERE id = ?",
			String.class, 1212L);
		// end::lastName[]

		// tag::rowMapper[]
		Mono<Actor> actor = reactiveCqlTemplate.queryForObject(
			"SELECT first_name, last_name FROM t_actor WHERE id = ?",
			new RowMapper<Actor>() {
				public Actor mapRow(Row row, int rowNum) {
					Actor actor = new Actor();
					actor.setFirstName(row.getString("first_name"));
					actor.setLastName(row.getString("last_name"));
					return actor;
				}},
			1212L);
		// end::rowMapper[]

		// tag::listOfRowMapper[]
		Flux<Actor> actors = reactiveCqlTemplate.query(
		"SELECT first_name, last_name FROM t_actor",
			new RowMapper<Actor>() {
				public Actor mapRow(Row row, int rowNum) {
					Actor actor = new Actor();
					actor.setFirstName(row.getString("first_name"));
					actor.setLastName(row.getString("last_name"));
					return actor;
				}
		});
		// end::listOfRowMapper[]

		// tag::preparedStatement[]
		Flux<String> lastNames = reactiveCqlTemplate.query(
				session -> session.prepare("SELECT last_name FROM t_actor WHERE id = ?"),
				ps -> ps.bind(1212L),
				(row, rowNum) -> row.getString(0));
		// end::preparedStatement[]
	}

	// tag::findAllActors[]
	Flux<Actor> findAllActors() {
		return reactiveCqlTemplate.query("SELECT first_name, last_name FROM t_actor", ActorMapper.INSTANCE);
	}

	enum ActorMapper implements RowMapper<Actor> {

		INSTANCE;

		public Actor mapRow(Row row, int rowNum) {
			Actor actor = new Actor();
			actor.setFirstName(row.getString("first_name"));
			actor.setLastName(row.getString("last_name"));
			return actor;
		}
	}
	// end::findAllActors[]

	@Test
	void insert() {

		// tag::insert[]
		Mono<Boolean> applied = reactiveCqlTemplate.execute(
			"INSERT INTO t_actor (first_name, last_name) VALUES (?, ?)",
			"Leonor", "Watling");
		// end::insert[]
	}

	@Test
	void update() {
		// tag::update[]
		Mono<Boolean> applied = reactiveCqlTemplate.execute(
			"UPDATE t_actor SET last_name = ? WHERE id = ?",
			"Banjo", 5276L);
		// end::update[]
	}

	@Test
	void delete() {
		long actorId = 1;

		// tag::delete[]
		Mono<Boolean> applied = reactiveCqlTemplate.execute(
			"DELETE FROM actor WHERE id = ?",
			actorId);
		// end::delete[]
	}

	static class Actor {

		void setFirstName(String first_name) {

		}

		void setLastName(String last_name) {}
	}
}

在需要对语句准备和参数绑定有更多控制的情况下（例如使用命名的绑定参数），您可以通过使用带 PreparedStatementCreator 和 PreparedStatementBinder 参数调用查询方法来完全控制已准备好的语句的创建和参数绑定：

  • Imperative

  • Reactive

/*
 * Copyright 2020-2024 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      https:://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.springframework.data.cassandra.example;

import java.util.List;

import org.junit.jupiter.api.Test;
import org.springframework.data.cassandra.core.cql.CqlTemplate;
import org.springframework.data.cassandra.core.cql.RowMapper;
import org.springframework.data.cassandra.core.cql.generator.DropTableCqlGenerator;
import org.springframework.data.cassandra.core.cql.keyspace.DropTableSpecification;

import com.datastax.oss.driver.api.core.cql.Row;

/**
 * @author Mark Paluch
 */
//@formatter:off
public class CqlTemplateExamples {

	private CqlTemplate cqlTemplate = null;

	void examples() {
		// tag::rowCount[]
		int rowCount = cqlTemplate.queryForObject("SELECT COUNT(*) FROM t_actor", Integer.class);
		// end::rowCount[]

		// tag::countOfActorsNamedJoe[]
		int countOfActorsNamedJoe = cqlTemplate.queryForObject(
				"SELECT COUNT(*) FROM t_actor WHERE first_name = ?", Integer.class, "Joe");
		// end::countOfActorsNamedJoe[]

		// tag::lastName[]
		String lastName = cqlTemplate.queryForObject(
				"SELECT last_name FROM t_actor WHERE id = ?",
				String.class, 1212L);
		// end::lastName[]

		// tag::rowMapper[]
		Actor actor = cqlTemplate.queryForObject("SELECT first_name, last_name FROM t_actor WHERE id = ?",
				new RowMapper<Actor>() {
					public Actor mapRow(Row row, int rowNum) {
						Actor actor = new Actor();
						actor.setFirstName(row.getString("first_name"));
						actor.setLastName(row.getString("last_name"));
						return actor;
					}
				}, 1212L);
		// end::rowMapper[]

		// tag::listOfRowMapper[]
		List<Actor> actors = cqlTemplate.query(
				"SELECT first_name, last_name FROM t_actor",
				new RowMapper<Actor>() {
					public Actor mapRow(Row row, int rowNum) {
						Actor actor = new Actor();
						actor.setFirstName(row.getString("first_name"));
						actor.setLastName(row.getString("last_name"));
						return actor;
					}
				});
		// end::listOfRowMapper[]

		// tag::preparedStatement[]
		List<String> lastNames = cqlTemplate.query(
				session -> session.prepare("SELECT last_name FROM t_actor WHERE id = ?"),
				ps -> ps.bind(1212L),
				(row, rowNum) -> row.getString(0));
		// end::preparedStatement[]
	}

	// tag::findAllActors[]
	List<Actor> findAllActors() {
		return cqlTemplate.query("SELECT first_name, last_name FROM t_actor", ActorMapper.INSTANCE);
	}

	enum ActorMapper implements RowMapper<Actor> {

		INSTANCE;

		public Actor mapRow(Row row, int rowNum) {
			Actor actor = new Actor();
			actor.setFirstName(row.getString("first_name"));
			actor.setLastName(row.getString("last_name"));
			return actor;
		}
	}
	// end::findAllActors[]

	@Test
	void prepared() {
		long actorId = 1;

		// tag::insert[]
		cqlTemplate.execute(
				"INSERT INTO t_actor (first_name, last_name) VALUES (?, ?)",
				"Leonor", "Watling");
		// end::insert[]

		// tag::update[]
		cqlTemplate.execute(
				"UPDATE t_actor SET last_name = ? WHERE id = ?",
				"Banjo", 5276L);
		// end::update[]

		// tag::delete[]
		cqlTemplate.execute(
				"DELETE FROM t_actor WHERE id = ?",
				5276L);
		// end::delete[]
	}

	@Test
	void other() {
		// tag::other[]
		cqlTemplate.execute("CREATE TABLE test_table (id uuid primary key, event text)");

		DropTableSpecification dropper = DropTableSpecification.dropTable("test_table");
		String cql = DropTableCqlGenerator.toCql(dropper);

		cqlTemplate.execute(cql);
		// end::other[]
	}

	static class Actor {

		void setFirstName(String first_name) {

		}

		void setLastName(String last_name) {}
	}
}
/*
 * Copyright 2023-2024 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.springframework.data.cassandra.example;

import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

import org.junit.jupiter.api.Test;
import org.springframework.data.cassandra.core.cql.ReactiveCqlTemplate;
import org.springframework.data.cassandra.core.cql.RowMapper;

import com.datastax.oss.driver.api.core.cql.Row;

/**
 * @author Mark Paluch
 */
//@formatter:off
public class ReactiveCqlTemplateExamples {

	private ReactiveCqlTemplate reactiveCqlTemplate = null;

	void examples() {
		// tag::rowCount[]
		Mono<Integer> rowCount = reactiveCqlTemplate.queryForObject("SELECT COUNT(*) FROM t_actor", Integer.class);
		// end::rowCount[]

		// tag::countOfActorsNamedJoe[]
		Mono<Integer> countOfActorsNamedJoe = reactiveCqlTemplate.queryForObject(
			"SELECT COUNT(*) FROM t_actor WHERE first_name = ?", Integer.class, "Joe");
		// end::countOfActorsNamedJoe[]

		// tag::lastName[]
		Mono<String> lastName = reactiveCqlTemplate.queryForObject(
			"SELECT last_name FROM t_actor WHERE id = ?",
			String.class, 1212L);
		// end::lastName[]

		// tag::rowMapper[]
		Mono<Actor> actor = reactiveCqlTemplate.queryForObject(
			"SELECT first_name, last_name FROM t_actor WHERE id = ?",
			new RowMapper<Actor>() {
				public Actor mapRow(Row row, int rowNum) {
					Actor actor = new Actor();
					actor.setFirstName(row.getString("first_name"));
					actor.setLastName(row.getString("last_name"));
					return actor;
				}},
			1212L);
		// end::rowMapper[]

		// tag::listOfRowMapper[]
		Flux<Actor> actors = reactiveCqlTemplate.query(
		"SELECT first_name, last_name FROM t_actor",
			new RowMapper<Actor>() {
				public Actor mapRow(Row row, int rowNum) {
					Actor actor = new Actor();
					actor.setFirstName(row.getString("first_name"));
					actor.setLastName(row.getString("last_name"));
					return actor;
				}
		});
		// end::listOfRowMapper[]

		// tag::preparedStatement[]
		Flux<String> lastNames = reactiveCqlTemplate.query(
				session -> session.prepare("SELECT last_name FROM t_actor WHERE id = ?"),
				ps -> ps.bind(1212L),
				(row, rowNum) -> row.getString(0));
		// end::preparedStatement[]
	}

	// tag::findAllActors[]
	Flux<Actor> findAllActors() {
		return reactiveCqlTemplate.query("SELECT first_name, last_name FROM t_actor", ActorMapper.INSTANCE);
	}

	enum ActorMapper implements RowMapper<Actor> {

		INSTANCE;

		public Actor mapRow(Row row, int rowNum) {
			Actor actor = new Actor();
			actor.setFirstName(row.getString("first_name"));
			actor.setLastName(row.getString("last_name"));
			return actor;
		}
	}
	// end::findAllActors[]

	@Test
	void insert() {

		// tag::insert[]
		Mono<Boolean> applied = reactiveCqlTemplate.execute(
			"INSERT INTO t_actor (first_name, last_name) VALUES (?, ?)",
			"Leonor", "Watling");
		// end::insert[]
	}

	@Test
	void update() {
		// tag::update[]
		Mono<Boolean> applied = reactiveCqlTemplate.execute(
			"UPDATE t_actor SET last_name = ? WHERE id = ?",
			"Banjo", 5276L);
		// end::update[]
	}

	@Test
	void delete() {
		long actorId = 1;

		// tag::delete[]
		Mono<Boolean> applied = reactiveCqlTemplate.execute(
			"DELETE FROM actor WHERE id = ?",
			actorId);
		// end::delete[]
	}

	static class Actor {

		void setFirstName(String first_name) {

		}

		void setLastName(String last_name) {}
	}
}

Spring Data Cassandra 随附支持该模式的类，位于 cql 包中：

  • SimplePreparedStatementCreator - 用于创建准备好的语句的实用程序类。

  • ArgumentPreparedStatementBinder - 用于将参数绑定到准备好的语句的实用程序类。

Using CassandraTemplate

CassandraTemplate 类建立在 CqlTemplate 之上，以提供更高级别的抽象。可以通过调用 setUsePreparedStatements(false) 或 setUsePreparedStatements(true) 直接在 CassandraTemplate（及其异步和反应式变体）上控制已准备语句的使用。请注意，CassandraTemplate 默认启用已准备语句的使用。

以下示例演示生成并接受 CQL 的方法的使用情况：

  • Imperative

  • Reactive

/*
 * Copyright 2020-2024 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      https:://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.springframework.data.cassandra.example;

import static org.springframework.data.cassandra.core.query.Criteria.*;
import static org.springframework.data.cassandra.core.query.Query.*;

import org.springframework.data.cassandra.core.CassandraTemplate;

import com.datastax.oss.driver.api.core.cql.SimpleStatement;

/**
 * @author Mark Paluch
 */
public class CassandraTemplateExamples {

	private CassandraTemplate template = null;

	void examples() {
		// tag::preparedStatement[]
		template.setUsePreparedStatements(true);

		Actor actorByQuery = template.selectOne(query(where("id").is(42)), Actor.class);

		Actor actorByStatement = template.selectOne(
				SimpleStatement.newInstance("SELECT id, name FROM actor WHERE id = ?", 42),
				Actor.class);
		// end::preparedStatement[]
	}

	static class Actor {

	}

}
/*
 * Copyright 2020-2024 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      https:://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.springframework.data.cassandra.example;

import static org.springframework.data.cassandra.core.query.Criteria.*;
import static org.springframework.data.cassandra.core.query.Query.*;

import org.springframework.data.cassandra.core.CassandraTemplate;

import com.datastax.oss.driver.api.core.cql.SimpleStatement;

/**
 * @author Mark Paluch
 */
public class ReactiveCassandraTemplateExamples {

	private ReactiveCassandraTemplate template = null;

	void examples() {
		// tag::preparedStatement[]
		template.setUsePreparedStatements(true);

		Mono<Actor> actorByQuery = template.selectOne(query(where("id").is(42)), Actor.class);

		Mono<Actor> actorByStatement = template.selectOne(
				SimpleStatement.newInstance("SELECT id, name FROM actor WHERE id = ?", 42),
				Actor.class);
		// end::preparedStatement[]
	}

	static class Actor {

	}

}

调用实体绑定方法（如 select(Query, Class<T>) 或 update(Query, Update, Class<T>)）本身构建 CQL 语句来执行预期的操作。一些 CassandraTemplate 方法（如 select(Statement<?>, Class<T>)） 也接受 CQL Statement 对象作为其 API 的一部分。

通过使用带有 SimpleStatement 对象的 Statement 调用接受方法时，可以参与已准备好的语句。模板 API 提取查询字符串和参数（位置参数和命名参数），并使用它们来准备、绑定和运行语句。非 SimpleStatement 对象不能用于已准备好的语句。

Caching Prepared Statements

自 Cassandra 驱动程序 4.0 以来，准备好的语句由 CqlSession 缓存，因此准备相同的字符串两次是可以的。早期版本要求在驱动程序外部缓存准备好的语句。有关更多参考，请参阅 Driver documentation on Prepared Statements。