SAP Hana database - JDBC batchUpdate

[former_member474688]

We are using Hana MDC version - 1.00.122.18.1531375173 in NEO environment. Using java application we have to update bulk of records in Hana database. For this, we used JDBC PreparedStatement -> executeBatch() method.

If any query have some issue in batch, then instead of stopping at that query, all queries are reverted. I tried with Connection autocommit TRUE/FALSE.

But still we are getting -2 status.

Same code when we tried with H2 database, then we got result like [1,1,-3,1]

Is sap hana jdbc driver support batch insert or update, if and only there are no issues with all queries?

Below is the sample code snippet.

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;




public class H2JdbcBatchUpdate {
	// JDBC driver name and database URL 
	   static final String JDBC_DRIVER = "com.sap.db.jdbc.Driver";   
	   static final String DB_URL = "jdbc:sap://localhost:30015/";  
	   
	   //  Database credentials 
	   static final String USER = "username"; 
	   static final String PASS = "password"; 
	   
	   public static void main(String[] args) { 
	      Connection conn = null; 
	      Statement stmt = null; 
	      try { 
	         // STEP 1: Register JDBC driver 
	         Class.forName(JDBC_DRIVER); 
	         
	         // STEP 2: Open a connection 
	         System.out.println("Connecting to database..."); 
	         conn = DriverManager.getConnection(DB_URL,USER,PASS);  
	         //conn.setAutoCommit(true);
	         // STEP 3: Execute a query 
	         System.out.println("Connected database successfully..."); 
	         
                /*
                    CREATE TABLE   REGISTRATION (id INTEGER not NULL,  first VARCHAR(10),  last VARCHAR(10),  age INTEGER,  PRIMARY KEY ( id ))
*/

	         String sql = "UPDATE Registration 	SET first = ? "
						+ " WHERE id=?";
				int[] affectedRecords = null;
				try (PreparedStatement st = conn.prepareStatement(sql)) {
					
					st.setString(1, "NEWNAME1");


					st.setInt(2, 100);
					st.addBatch();
					
					st.setString(1, "NEWNAME2");


					st.setInt(2, 101);
					st.addBatch();
					
					st.setString(1, "asas");


					st.setInt(2, 102);
					st.addBatch();
					
					st.setString(1, "NEWNAMEdsfsdfdsfdsfdsfdsffds3");


					st.setInt(2, 103);
					st.addBatch();
				
					affectedRecords = st.executeBatch();
				} catch (BatchUpdateException e) {
					e.printStackTrace();
					int[] counts = e.getUpdateCounts();
					for(int each : counts) {
						System.out.println("each::"+each);
					}
					System.out.println(Arrays.toString(e.getUpdateCounts()));
					System.out.println("Batch has managed to process {} entries:" + e.getUpdateCounts().length);
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					if (affectedRecords != null) {
						System.out.println(Arrays.toString(affectedRecords));
					} else {
						System.out.println("Its nullll");
					}


				}
	          
	      } catch(BatchUpdateException se) { 
	    	  int[] updateCounts = se.getUpdateCounts();
		      System.out.println("count2:::"+Arrays.toString(updateCounts)); 
	         se.printStackTrace(); 
	      } catch(Exception e) { 
	         // Handle errors for Class.forName 
	         e.printStackTrace(); 
	      } finally { 
	         // finally block used to close resources 
	         try { 
	            if(stmt!=null) stmt.close();  
	         } catch(SQLException se2) { 
	         } // nothing we can do 
	         try { 
	            if(conn!=null) conn.close(); 
	         } catch(SQLException se) { 
	            se.printStackTrace(); 
	         } // end finally try 
	      } // end try 
	      System.out.println("Goodbye!"); 
	   } 
	   
}

Result:-
--------

Connecting to database...
Connected database successfully...
count2:::[-2, -2, -2, -2]

[Ivan-Mirisola]

Why don't you do this in a Stored Procedure? It would make more sense since you would take the advantage of in-memory processing. I don't think JDBC driver for HANA was intended for mass database processing.

[muzaffer_gokbudak]

ı agree IVAN