How to perform DML(Update, Insert, Delete) operations on google spanner from a java application?

Question

Need your help!

I am trying to perform DML operation using a driver class(com.simba.cloudspanner.core.jdbc42.CloudSpanner42Driver), but I am getting exception like

"Caused by: shaded.com.google.cloud.spanner.SpannerException: INVALID_ARGUMENT: DML statements(INSERT, UPDATE and DELETE) are not supported." 

But SELECT query is working fine. Below is my java code.

Please let me know how can I perform DML operations on spanner from a java app.

I tried Mutation.newInsertBuilder , Mutation.newUpdateBuilder, Mutation.delete to achieve DML operations using com.google.cloud.spanner.Mutation; libraries, but I am actually looking for some kind of implementation where user can run SQL statements to do DML operations.

public class SimbuDriverInsert {

   static final  String CONNECTION_URL = "jdbc:cloudspanner://localhost;Project=optimistic-leaf-197820;Instance=testspanner01;Database=students;PvtKeyPath=C:\\MuleWorkspace\\test-driver\\src\\main\\resources\\gcloudPrivateKey.json";

   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   try{
      Class.forName("com.simba.cloudspanner.core.jdbc42.CloudSpanner42Driver");
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(CONNECTION_URL);
      stmt = conn.createStatement();


      String sql = "INSERT INTO studentdetails (id,age,name) " +
              "VALUES (100, 30, 'Ali')";
       stmt.executeUpdate(sql);
       System.out.println("Inserted record into the table...");
      stmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }
}//end main

}

Answer 1

The official JDBC driver supplied by Oracle (together with Simba) does not support DML and DDL statements. This open source driver does support both. If you include this driver in you project and change the following line

Class.forName("com.simba.cloudspanner.core.jdbc42.CloudSpanner42Driver");

into

Class.forName("nl.topicus.jdbc.CloudSpannerDriver");

the code should work. The driver uses the same URL syntax as the official driver, but also adds a number of extra possibilities. Have a look at the Wiki page of the driver for more information.

The driver can be added as a maven dependency or downloaded from the releases page of the project.

Have a look here for more examples on how to use the driver: http://www.googlecloudspanner.com/ with different frameworks and tools.