[Java] ListBinder to bind Arrow List type to DB column

[asfimport]

Typical real life Arrow datasets contain List type vectors of primitive type. Looks logically implement default JDBC parameter binders for such vector type or implement some kind on binder code extensibility of core framework via MetaINF/ServiceLocator

Current implementation just [throws UnsupportedOperationException|#L80]]

 

  @Override
        
        
            public ColumnBinder visit(ArrowType.List type) {
        
        
              throw new UnsupportedOperationException("No column binder implemented for type " + type);
        
        
            } 

 

My current implementation patch ColumnBinderArrowTypeVisitor in classpath (to leverage Builder functionality instead of manual vector mapping in code) to return ListBinder

@Override
public ColumnBinder visit(ArrowType.List type) {
    return new ListBinder((ListVector) vector);
} 

and following code works for me with H2 database and in java stored PostgreSQL function in PL/Java to bind List parameter to JDBC:

package org.apache.arrow.adapter.jdbc.binder;

import org.apache.arrow.vector.complex.ListVector;
import org.apache.arrow.vector.complex.impl.UnionListReader;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;

public class ListBinder extends BaseColumnBinder<ListVector>{

    private UnionListReader reader;

    public ListBinder(ListVector vector) {
        this(vector, Types.ARRAY);
    }

    public ListBinder(ListVector vector, int jdbcType) {
        super(vector, jdbcType);
        reader = vector.getReader();
    }

    @Override
    public void bind(PreparedStatement statement, int parameterIndex, int rowIndex) throws SQLException {
        reader.setPosition(rowIndex);
        ArrayList sourceArray = (ArrayList) reader.readObject();
        Class aClass = sourceArray.get(0).getClass();
        if(aClass.isAssignableFrom(Long.class)){
            Long[] res = new Long[sourceArray.size()];
            Arrays.setAll(res, sourceArray::get);
            statement.setObject(parameterIndex, res);
        } else
        if(aClass.isAssignableFrom(Integer.class)){
            Integer[] res = new Integer[sourceArray.size()];
            Arrays.setAll(res, sourceArray::get);
            statement.setObject(parameterIndex, res);
        } else
        if(aClass.isAssignableFrom(Short.class)){
            Short[] res = new Short[sourceArray.size()];
            Arrays.setAll(res, sourceArray::get);
            statement.setObject(parameterIndex, res);
        } else
        if(aClass.isAssignableFrom(String.class)){
            String[] res = new String[sourceArray.size()];
            Arrays.setAll(res, sourceArray::get);
            statement.setObject(parameterIndex, res);
        }
    }
}
 

Reporter: Igor Suhorukov / @igor-suhorukov
Assignee: Igor Suhorukov / @igor-suhorukov

Related issues:

• [Java] MapBinder to bind Arrow Map type to DB column (is cloned by)

PRs and other links:

• GitHub Pull Request #13906

_{Note: This issue was originally created as ARROW-17430. Please see the migration documentation for further details.}

[asfimport]

David Li / @lidavidm:
You don't need to patch the default code: you can manually pass in a Binder instance when building the JdbcParameterBinder.

[asfimport]

Igor Suhorukov / @igor-suhorukov:
Thank you @lidavidm your approach works for manual coding for each dataset, but org.apache.arrow.adapter.jdbc.JdbcParameterBinder.Builder is super useful to reduce efforts to implement mapping of new type for dataset. The same difference like develop program to process data or just allow end users to write SQL querey

[asfimport]

David Li / @lidavidm:
We would definitely welcome a PR with List/MapBinder implementations! Otherwise, I don't think I'd have the time to get to this for a while.

[asfimport]

Igor Suhorukov / @igor-suhorukov:
@lidavidm   got it. Thanks for your time. Lets try from ListBinder. I also asked my friend to assist with PRs he'll look at code on weekends

[asfimport]

David Li / @lidavidm:
Issue resolved by pull request 13906
#13906