Databricks JDBC Insert into Array field

Dp15 — Wed, 16 Oct 2024 20:08:41 GMT

hi,

I am trying to insert some data into a databricks table which has Array<String> fields (field1 & field2). I am using JDBC for the connection and my POJO class looks like this

public class A{
    private Long id;
    private String[] field1;
    private String[] field2;
}

I am using

jdbcTemplate.batchUpdate(String sql, final List<Object[]> batchArgs, final int[] argTypes)

where I have a list of Objects for class A.

the argtype code for the fields, field1 & field2 are 2003 since I am using the java.sql.Type.Arrray for these fields.

While executing, I am getting the following error
[Databricks][JDBC](11500) Given type does not match given object: [Ljava.lang.String;@3e1346b0.

Should I be using some other code for these fields?

How do I insert data into a Array field using JDBCTemplate

Re: Databricks JDBC Insert into Array field

VZLA — Thu, 31 Oct 2024 18:05:39 GMT

The error you're encountering, [Databricks][JDBC](11500) Given type does not match given object: [Ljava.lang.String;@3e1346b0, indicates that the JDBC driver is not recognizing the Java String[] array as a valid SQL array type. This is a common issue when working with array types in JDBC.

To resolve this, you need to ensure that the array is properly converted to a SQL array type before passing it to the jdbcTemplate.batchUpdate method. Here are the steps you can follow:

Convert Java Array to SQL Array: Use the Connection.createArrayOf method to convert your Java String[] array to a SQL array.
Modify Your Code: Update your code to create SQL arrays for field1 and field2 before executing the batch update

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Types; import java.util.List; import org.springframework.jdbc.core.JdbcTemplate; public class YourClass { private JdbcTemplate jdbcTemplate; public void insertData(List<A> dataList) throws SQLException { String sql = "INSERT INTO your_table (id, field1, field2) VALUES (?, ?, ?)"; jdbcTemplate.batchUpdate(sql, dataList, dataList.size(), (PreparedStatement ps, A data) -> { ps.setLong(1, data.getId()); Connection conn = ps.getConnection(); ps.setArray(2, conn.createArrayOf("VARCHAR", data.getField1())); ps.setArray(3, conn.createArrayOf("VARCHAR", data.getField2())); }); } }

topic Databricks JDBC Insert into Array field in Data Engineering

Databricks JDBC Insert into Array field

Re: Databricks JDBC Insert into Array field