Cassandra：使用java方式执行sql操作

当前版本：apache-cassandra-3.0.24

1. 声明

当前内容主要为本人学习和测试使用java方式操作cassandra，当前内容借鉴官方文档

主要为使用datastax实现：

1. 增删改查keyspace
2. 增删改查table

pom依赖

<properties>
	<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	<driver.version>4.13.0</driver.version>
</properties>

<dependencies>
	<dependency>
		<groupId>com.datastax.oss</groupId>
		<artifactId>java-driver-core</artifactId>
		<version>${driver.version}</version>
	</dependency>

	<dependency>
		<groupId>com.datastax.oss</groupId>
		<artifactId>java-driver-query-builder</artifactId>
		<version>${driver.version}</version>
	</dependency>

	<dependency>
		<groupId>com.datastax.oss</groupId>
		<artifactId>java-driver-mapper-runtime</artifactId>
		<version>${driver.version}</version>
	</dependency>
</dependencies>

需要配置并开放linux中的cassandra让外界访问：参考文章

2. 基本的连接demo

public class CassandraConnectTest {
	private static final String DEFATUL_HOST = "192.168.1.103";
	private static final int DEFAULT_PORT = 9042;

	public static void main(String[] args) {
		// 查询当前cassandra的版本
		showVersion();
	}


	// 查出当前的版本
	private static void showVersion() {
		execute((session) -> {
			ResultSet rs = session.execute("select release_version from system.local");
			// Extract the first row (which is the only one in this case).
			Row row = rs.one();

			// Extract the value of the first (and only) column from the row.
			if (row != null) {
				String releaseVersion = row.getString("release_version");
				System.out.printf("Cassandra version is: %s%n", releaseVersion);
			}
		});
	}

	private static CqlSession createCqlSession() {
		return CqlSession.builder().addContactPoint(new InetSocketAddress(DEFATUL_HOST, DEFAULT_PORT)).build();
	}

	private static void execute(SessionHandler handler) {
		try (CqlSession session = createCqlSession()) {
			handler.handler(session);
		}
	}

	interface SessionHandler {
		void handler(CqlSession session);
	}
}

测试结果：

3. 操作keyspace

public class OperationKeyspaceTest {
	private static final String DEFATUL_HOST = "192.168.1.103";
	private static final int DEFAULT_PORT = 9042;

	public static void main(String[] args) {
		// 查询当前cassandra的版本
		// showVersion();
		// 创建keyspace,如果该keyspace已经存在了那么就会报错Keyspace test already exists
		createKeyspace("test");
		// 查看当前的keyspaces
		//showKeyspaces();
		// 修改当前的keyspace
		//updateKeyspace("test");
		//showKeyspaces();
		// 删除keyspace
		//deleteKeyspace("test");
		//showKeyspaces();
	}

	// 修改当前的keyspace的副本数量为1
	private static void updateKeyspace(String keyspace) {
		execute((session) -> {
			session.execute("ALTER KEYSPACE "+keyspace+"  WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 1};");
		});
	}

	// 展示所有的keyspace
	private static void showKeyspaces() {
		execute((session) -> {
			Metadata metadata = session.getMetadata();
			Optional<String> clusterName = metadata.getClusterName();
			if (clusterName.isPresent()) {
				System.out.println("clusterName:" + clusterName.get());
			}
			Iterator<Entry<UUID, Node>> iterator = metadata.getNodes().entrySet().iterator();
			while (iterator.hasNext()) {
				Entry<UUID, Node> next = iterator.next();
				System.out.println("key=" + next.getKey() + ",value=" + next.getValue());
			}
			// 这里没有任何的keyspace
			Map<CqlIdentifier, KeyspaceMetadata> keyspaces = metadata.getKeyspaces();
			keyspaces.forEach((k, v) -> {
				System.out.println("key=" + k + ",value=" + v);
			});
			
		});
	}
	
	// 手动创建keyspace
	private static void createKeyspace(String keyspace) {
		execute((session) -> {
			session.execute("CREATE KEYSPACE "+keyspace+" WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 3} ");
		});
	}
	
	// 删除keyspace
	private static void deleteKeyspace(String keyspace) {
		execute((session) -> {
			session.execute("DROP KEYSPACE "+keyspace);
		});
	}

	private static CqlSession createCqlSession() {
		return CqlSession.builder().addContactPoint(new InetSocketAddress(DEFATUL_HOST, DEFAULT_PORT)).build();
	}

	private static void execute(SessionHandler handler) {
		try (CqlSession session = createCqlSession()) {
			handler.handler(session);
		}
	}

	interface SessionHandler {
		void handler(CqlSession session);
	}
}

4. 操作table

public class OperationTableTest {
	private static final String DEFATUL_HOST = "192.168.1.103";
	private static final int DEFAULT_PORT = 9042;

	public static void main(String[] args) {
		createTable();
		System.out.println("create table success!");
		insertDataToTable();
		System.out.println("insert table data success!");
		selectDataFromTable();
		System.out.println("select data from table success!");
		//updateTableAddColumn();
		//System.out.println("add column in table success!");
		//updateTableDropColumn();
		//System.out.println("drop column in table success!");
		//truncateTable();
		//System.out.println("truncate table success!");
		//dropTable();
		//System.out.println("drop table success!");
	}
	
	// 创建用户表
	private static void createTable() {
		execute(session->{
			session.execute("CREATE TABLE test.user (\n" + 
					"    id INT PRIMARY KEY,\n" + 
					"    username VARCHAR,\n" + 
					"    password VARCHAR,\n" + 
					"    create_time TIMESTAMP,\n"+ 
					"	 update_time TIMESTAMP" + 
					") WITH comment='用户表';");
		});
	}
	// 向表中插入数据
	private static void insertDataToTable() {
		execute(session->{
			session.execute("insert into test.user(id,username,password) values(1,'admin','123456');");
		});
	}
	
	// 查询数据
	private static void selectDataFromTable() {
		execute(session->{
			ResultSet result = session.execute("select id,username,password from test.user");
			List<Row> all = result.all();
			for (Row row : all) {
				System.out.println(row.getInt("id")+","+row.getString("password")+","+row.getString("password"));
			}
		});
	}
	// 更新并为表中添加字段
	private static void updateTableAddColumn() {
		execute(session->{
			session.execute("ALTER TABLE test.user add birth timestamp;");
		});
	}
	// 更新并删除表中的字段
	private static void updateTableDropColumn() {
		execute(session->{
			session.execute("ALTER TABLE test.user drop birth;");
		});
	}
	// 删除表
	private static void dropTable() {
		execute(session->{
			session.execute("DROP TABLE IF EXISTS test.user ;");
		});
	}
	// 清空表数据
	private static void truncateTable() {
		execute(session->{
			session.execute("TRUNCATE TABLE test.user ;");
		});
	}
	
	private static CqlSession createCqlSession() {
		return CqlSession.builder().addContactPoint(new InetSocketAddress(DEFATUL_HOST, DEFAULT_PORT)).build();
	}

	private static void execute(SessionHandler handler) {
		try (CqlSession session = createCqlSession()) {
			handler.handler(session);
		}
	}

	interface SessionHandler {
		void handler(CqlSession session);
	}
}

5. 总结

1.使用java方式执行sql方式创建keyspace和table的语法和关系型数据库语法很像

2. 获取所有的keyspace需要通过session中的元数据才能得到

3.session连接有很大几率超时