cassandra java binding a collection of maps to a prepared statement

Question

I am using datastax' Cassandra Java driver. My Cassandra supports CQL 2 only.

I have a prepared INSERT statement that needs to accept a list of maps.

This is my schema:

CREATE TYPE test.snap_guid (
    l bigint,
    h bigint
);

CREATE TABLE test.t (
    id   bigint,
    snap list<frozen<snap_guid>>,
    PRIMARY KEY ((id))
)

This is the prepared statement:

PreparedStatement mysttmt = <client>.prepare("INSERT INTO test.t (id, snap) VALUES (?, ?)");

I know that in order to bind collections to the prepared statement I need to create a collection, and bind it. I cannot put the collection brackets in the prepared query. For instance (for list of text):

ArrayList<String> snaps = new ArrayList<>();
snaps.add("AEF34GF665");
// INSERT INTO test.t (id, snap) VALUES (?, ?)
BoundStatement bs = mysttmt.bind(12, snaps);

My question is: how do I bind a list of maps? How can I create a query like the next one?

INSERT INTO test.t (id, snap) VALUES (12, [{l:10,h:50}])

// I know it is impossible to create the following prepared statement:
INSERT INTO test.t (id, snap) VALUES (?, [{l:?,h:?}])

// The list of maps has to be a single bound variable... how???
INSERT INTO test.t (id, snap) VALUES (?, ?)

score 0 · Answer 1 · edited May 16 '19 at 18:58

Create tables:

CREATE TYPE my_type (
user_id text,
name text,
age bigint
);

CREATE TABLE my_table_with_set_of_udt (
my_key text PRIMARY KEY,
my_types set<frozen <my_type>>
);

CREATE INDEX ON my_table_with_set_of_udt(my_types);

Create mapping classes and beans:

@UDT(keyspace = "my_ks", name = "my_type")
public class MyType {
@Field(name = "user_id")
private String userId;

@Field(name = "name")
private String name;

@Field(name = "age")
private int age;

// getters, setters & constructors
// equals & hashCode are required for a set
}

@Table(keyspace = "my_ks", name = "my_table_with_set_of_udt")
public class MyTableWithSetOfUDT {

@PartitionKey
@Column(name = "my_key")
private String myKey;

@Column(name = "my_types")
@FrozenValue // because of --> set<frozen <my_type>>
private Set<MyType> myTypes;

// getters, setters & constructors
// equals & hashCode are required for a set
}

@Bean
public Mapper<MyTableWithSetOfUDT> myTableWithSetOfUDTMapper() {
return new MappingManager(cassandraClient.getSession()).mapper(MyTableWithSetOfUDT.class);
}

@Bean
public UDTMapper<MyType> myTypeMapper() {
return new MappingManager(cassandraClient.getSession()).udtMapper(MyType.class);
}

Create the prepared statements, for example:

addUpdateStatement = cassandraClient.getSession().prepare("UPDATE my_table_with_set_of_udt SET my_types = my_types + ? WHERE my_key=?;");
deleteUpdateStatement = cassandraClient.getSession().prepare("UPDATE my_table_with_set_of_udt SET my_types = my_types - ? WHERE my_key=?;");

Bind the parameters:

MyType myType = MyType(userId, name, age);
UDTValue value = myTypeMapper.toUDT(myType);
cassandraClient.getSession().execute(addUpdateStatement.bind(Sets.newHashSet(value), myKey));

or

cassandraClient.getSession().execute(deleteUpdateStatement.bind(Sets.newHashSet(value), myKey));

Insert a record into the table with a value for my_key (You can do this directly in cql).
Run the code, now we can add/delete elements to the set by updating the table with our UDT.

score -1 · Answer 2 · answered Nov 13 '16 at 09:26

I have the solution.

It seems that the question was wrong. I needed to add a list of UDT (user defined tap), not list of hashes. For that you need to use UserType, and UDTValue:

UserType myUDT = <client>.getSession().getCluster().
    getMetadata().getKeyspace("test").getUserType("snap_guid");
UDTValue mySnap = myUDT.newValue();
mySnap.setLong("h", 50);
mySnap.setLong("l", 10);

ArrayList<UDTValue> snaps = new ArrayList<>();
snaps.add(mySnap);
// INSERT INTO test.t (id, snap) VALUES (?, ?)
BoundStatement bs = mysttmt.bind(12, snaps);

cassandra java binding a collection of maps to a prepared statement

2 Answers2