CODEWITHSUNDEEP
cassandradatastax-java-driver
Mewken
Mewken

Reputation: 45

Cannot insert into Cassandra table, getting SyntaxError

I have an assignment where I have to build a Cassandra database. I have connected Cassandra with IntelliJ, i'm writing in java and the output is shown in the command line.

My keyspace farm_db contains a couple of tables in wish i'm would like to insert data. I would like to insert the data with two columns and a list all in one row, in the table 'farmers'. This is a part of my database so far:

cqlsh:farm_db> use farm_db;
cqlsh:farm_db> Describe tables;

farmers              foods_dairy_eggs        foods_meat
foods_bread_cookies  foods_fruit_vegetables

cqlsh:farm_db> select * from farmers;

 farmer_id | delivery | the_farmer
-----------+----------+------------

This is what i'm trying to do:

[Picture of what i'm trying to do][1]

I need to insert the collection types 'list' and 'map' in 'farmers' but after a couple of failed attempts with that I tried using hashmap and arraylist instead. I think this could work but i seem to have an error in my syntax and I have no idea what the problem seem to be:

Exception in thread "main" com.datastax.driver.core.exceptions.SyntaxError: line 1:31 mismatched input 'int' expecting ')' (INSERT INTO farmers (farmer_id [int]...)

Am I missing something or am I doing something wrong?

This is my code:

public class FarmersClass {

public static String serverIP = "127.0.0.1";
public static String keyspace = "";

//Create db
public void crateDatabase(String databaseName) {
    Cluster cluster = Cluster.builder()
            .addContactPoints(serverIP)
            .build();

    keyspace = databaseName;
    Session session = cluster.connect();
    String create_db_query = "CREATE KEYSPACE farm_db WITH replication "
            + "= {'class':'SimpleStrategy', 'replication_factor':1};";
    session.execute(create_db_query);


}

//Create table
public void createFarmersTable() {

    Cluster cluster = Cluster.builder()
            .addContactPoints(serverIP)
            .build();

    Session session = cluster.connect("farm_db");
    String create_farm_table_query = "CREATE TABLE farmers(farmer_id int PRIMARY KEY, the_farmer Map <text, text>, delivery list<text>); ";
    session.execute(create_farm_table_query);

}

//Insert data in table 'farmer'.
public void insertFarmers(int id, HashMap< String, String> the_farmer, ArrayList <String> delivery) {

    Cluster cluster = Cluster.builder()
            .addContactPoints(serverIP)
            .build();
    Session session = cluster.connect("farm_db");
    String insert_query = "INSERT INTO farmers (farmer_id int PRIMARY KEY, the_farmer, delivery) values (" + id + "," + the_farmer + "," + delivery + ");";
    System.out.println(insert_query);
    session.execute(insert_query);

    }
}

public static void main(String[] args) {

    FarmersClass farmersClass = new FarmersClass();

    //  FarmersClass.crateDatabase("farm_db");

    //  FarmersClass.createFarmersTable();

    //Collection type map
    HashMap<String, String> the_farmer = new HashMap<>();
    the_farmer.put("Name", "Ana Petersen ");
    the_farmer.put("Farmhouse", "The great farmhouse");
    the_farmer.put("Foods", "Fruits & Vegetables");

    //Collection type list
    ArrayList<String> delivery = new ArrayList<String>();
    String delivery_1 = "Village 1";
    String delivery_2 = "Village 2";
    delivery.add(delivery_1);
    delivery.add(delivery_2);

    FarmersClass.insertFarmers(1, the_farmer, delivery);
}

Upvotes: 1

Views: 678

Answers (1)

Erick Ramirez
Erick Ramirez

Reputation: 16403

The problem is the syntax of your CQL INSERT query:

    String insert_query = \
        "INSERT INTO farmers (farmer_id int PRIMARY KEY, the_farmer, delivery)  \
        values (" + id + "," + the_farmer + "," + delivery + ");";

You've incorrectly added int PRIMARY KEY in the list of columns.

The correct format is:

INSERT INTO table_name (pk, col2, col3) VALUES ( ... )

For details and examples, see CQL INSERT. Cheers!

Upvotes: 1

Related Questions