Sql2o type converter to handle columns of type ARRAY. https://github.com/aaberg/sql2o/issues/199

ArrayConverter.java

// Written in 2015 by Thilo Planz 
// To the extent possible under law, I have dedicated all copyright and related and neighboring rights 
// to this software to the public domain worldwide. This software is distributed without any warranty. 
// http://creativecommons.org/publicdomain/zero/1.0/

package sql2oarrays;

import java.io.InputStream;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;

import org.sql2o.converters.Converter;
import org.sql2o.converters.ConverterException;
import org.sql2o.quirks.NoQuirks;
import org.sql2o.quirks.PostgresQuirks;
import org.sql2o.quirks.Quirks;
import org.sql2o.quirks.parameterparsing.SqlParameterParsingStrategy;

/**
 * Sql2o type converter to handle columns of type ARRAY.
 * 
 * To get the most out of it, install it as Quirks.
 * 
 * <pre>
 * Quirks arraySupport = ArrayConverter.arrayConvertingQuirks(yourNormalQuirks);
 * </pre>
 * 
 * @see https://github.com/aaberg/sql2o/issues/199
 * 
 * @author Thilo Planz
 */

public class ArrayConverter<T> implements Converter<T[]> {

	public final static ArrayConverter<String> STRING_ARRAY_CONVERTER = new ArrayConverter<String>(
			String.class);

	public final static ArrayConverter<Integer> INTEGER_ARRAY_CONVERTER = new ArrayConverter<Integer>(
			Integer.class);

	private final Class<T> componentType;

	private final Object[] emptyArray;

	public ArrayConverter(Class<T> componentType) {
		this.componentType = componentType;
		this.emptyArray = (Object[]) java.lang.reflect.Array.newInstance(
				componentType, 0);
	}

	@Override
	@SuppressWarnings("unchecked")
	public T[] convert(Object val) throws ConverterException {
		if (val == null)
			return null;

		if (val instanceof Array) {
			try {
				val = ((Array) val).getArray();
			} catch (Exception e) {
				throw new ConverterException(
						"failed to retrieve data from JDBC array", e);
			}
		}

		if (val.getClass().isArray()) {
			if (val.getClass() == emptyArray.getClass())
				return (T[]) val;
			int len = java.lang.reflect.Array.getLength(val);
			if (len == 0)
				return (T[]) emptyArray;
		}

		throw new ConverterException("Don't know how to convert type "
				+ val.getClass().getName() + " to " + componentType.getName()
				+ "[]");

	}

	@Override
	public Object toDatabaseParam(T[] val) {
		// https://github.com/aaberg/sql2o/issues/171
		// we cannot call JDBC's "conn#createArrayOf" here, as we have no
		// connection
		// so we just return the same array here.

		// Some databases (like H2DB) can handle that, for others, we need to
		// install a "quirk"
		// (which has access to the connection)

		// H2DB: can handle object arrays, but primitive arrays become OTHER
		// (not ARRAY)
		return val;
	}

	
	public static Quirks arrayConvertingQuirksForH2DB(){
		return arrayConvertingQuirks(new NoQuirks(), false, false);
	}
	
	public static Quirks arrayConvertingQuirksForPostgres(){
		return arrayConvertingQuirks(new PostgresQuirks(), true, false);
	}
	
	private final static Map<Class<?>, Converter<?>> arrayConverters;

	static {
		arrayConverters = new HashMap<Class<?>, Converter<?>>();
		arrayConverters.put(String[].class, STRING_ARRAY_CONVERTER);
		arrayConverters.put(Integer[].class, INTEGER_ARRAY_CONVERTER);
	}

	/**
	 * decorates the given Quirks with additional quirks that handle creating
	 * SQL arrays.
	 * 
	 * As a result, you should be able to addParameter("name", javaArray).
	 * 
	 * 
	 **/
	public static Quirks arrayConvertingQuirks(final Quirks databaseQuirks,
			final boolean useCreateArrayOf, final boolean promotePrimitiveArrays) {

		return new Quirks() {
			public void closeStatement(Statement arg0) throws SQLException {
				databaseQuirks.closeStatement(arg0);
			}

			@SuppressWarnings("unchecked")
			public <E> Converter<E> converterOf(Class<E> arg0) {
				if (arg0.isArray()) {
					Converter<?> c = arrayConverters.get(arg0);
					if (c != null)
						return (Converter<E>) c;
				}
				return databaseQuirks.converterOf(arg0);
			}

			public String getColumnName(ResultSetMetaData arg0, int arg1)
					throws SQLException {
				return databaseQuirks.getColumnName(arg0, arg1);
			}

			public Object getRSVal(ResultSet arg0, int arg1)
					throws SQLException {
				return databaseQuirks.getRSVal(arg0, arg1);
			}

			public SqlParameterParsingStrategy getSqlParameterParsingStrategy() {
				return databaseQuirks.getSqlParameterParsingStrategy();
			}

			public boolean returnGeneratedKeysByDefault() {
				return databaseQuirks.returnGeneratedKeysByDefault();
			}

			public void setParameter(PreparedStatement arg0, int arg1,
					InputStream arg2) throws SQLException {
				databaseQuirks.setParameter(arg0, arg1, arg2);
			}

			public void setParameter(PreparedStatement arg0, int arg1, int arg2)
					throws SQLException {
				databaseQuirks.setParameter(arg0, arg1, arg2);
			}

			public void setParameter(PreparedStatement arg0, int arg1,
					Integer arg2) throws SQLException {
				databaseQuirks.setParameter(arg0, arg1, arg2);
			}

			public void setParameter(PreparedStatement arg0, int arg1, long arg2)
					throws SQLException {
				databaseQuirks.setParameter(arg0, arg1, arg2);
			}

			public void setParameter(PreparedStatement arg0, int arg1, Long arg2)
					throws SQLException {
				databaseQuirks.setParameter(arg0, arg1, arg2);
			}

			public void setParameter(PreparedStatement st, int pos, Object val)
					throws SQLException {
				if (val != null && val.getClass().isArray()) {
					Class<?> componentType = val.getClass().getComponentType();
					if (componentType.isPrimitive() && promotePrimitiveArrays){
						// TODO: convert to wrapper instance array
						throw new UnsupportedOperationException(
								"primitive arrays are not supported yet");
					}
					
					if (useCreateArrayOf) {
						if (componentType.isPrimitive()) {
							// TODO: convert to wrapper instance array
							throw new UnsupportedOperationException(
									"primitive arrays are not supported yet");
						}
						Connection conn = st.getConnection();

						String type;
						if (componentType == String.class) {
							type = "varchar";
						} else if (componentType == Integer.class) {
							type = "integer";
						} else {
							throw new UnsupportedOperationException(
									"do not know the SQL type for "
											+ componentType);
						}

						st.setArray(pos,
								conn.createArrayOf(type, (Object[]) val));
						return;
					}
				}

				databaseQuirks.setParameter(st, pos, val);
			}

			public void setParameter(PreparedStatement arg0, int arg1,
					String arg2) throws SQLException {
				databaseQuirks.setParameter(arg0, arg1, arg2);
			}

			public void setParameter(PreparedStatement arg0, int arg1, Time arg2)
					throws SQLException {
				databaseQuirks.setParameter(arg0, arg1, arg2);
			}

			public void setParameter(PreparedStatement arg0, int arg1,
					Timestamp arg2) throws SQLException {
				databaseQuirks.setParameter(arg0, arg1, arg2);
			}

		};
	}

}

H2DBTester.java

// Written in 2015 by Thilo Planz 
// To the extent possible under law, I have dedicated all copyright and related and neighboring rights 
// to this software to the public domain worldwide. This software is distributed without any warranty. 
// http://creativecommons.org/publicdomain/zero/1.0/

package sql2oarrays;

import static org.junit.Assert.assertArrayEquals;
import static org.junit.Assert.assertEquals;

import java.util.List;

import org.junit.After;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import org.sql2o.Connection;
import org.sql2o.Sql2o;

public class H2DBTester {

	public String[] strings;

	public Integer[] integers;

	public int[] ints;

	@Test
	public void testStrings() {
		try {
			conn.createQuery("create table arraystest(strings ARRAY not null)")
					.executeUpdate();

			strings = new String[] { "a", "b", "c" };
			conn.createQuery("insert into arraystest values (:strings)")
					.bind(this).executeUpdate();

			List<H2DBTester> l = conn.createQuery("select * from arraystest")
					.executeAndFetch(H2DBTester.class);
			for (H2DBTester t : l) {
				assertArrayEquals(strings, t.strings);
			}
			assertEquals(1, l.size());
		} finally {
			conn.close();
		}

	}

	@Test
	public void testIntegers() {
		try {
			conn.createQuery("create table arraystest(integers ARRAY not null)")
					.executeUpdate();

			integers = new Integer[] { 1, 2, 3 };
			conn.createQuery("insert into arraystest values (:integers)")
					.bind(this).executeUpdate();

			List<H2DBTester> l = conn.createQuery("select * from arraystest")
					.executeAndFetch(H2DBTester.class);
			for (H2DBTester t : l) {
				assertArrayEquals(integers, t.integers);
			}
			assertEquals(1, l.size());
		} finally {
			conn.close();
		}

	}

	// object arrays become Object, but primitive arrays become OTHER

	@Test
	public void testInts() {
		try {
			conn.createQuery("create table arraystest(ints OTHER not null)")
					.executeUpdate();

			ints = new int[] { 1, 2, 3 };
			conn.createQuery("insert into arraystest values (:ints)")
					.bind(this).executeUpdate();

			List<H2DBTester> l = conn.createQuery("select * from arraystest")
					.executeAndFetch(H2DBTester.class);
			for (H2DBTester t : l) {
				assertArrayEquals(ints, t.ints);
			}
			assertEquals(1, l.size());
		} finally {
			conn.close();
		}

	}

	private static Connection conn;

	@BeforeClass
	public static void setup() {

		Sql2o sql = new Sql2o("jdbc:h2:mem:", "", "",
				ArrayConverter.arrayConvertingQuirksForH2DB());
		conn = sql.open();
	}

	@AfterClass
	public static void tearDown() {
		if (conn != null)
			conn.close();
	}

	@After
	public void dropTable() {
		if (conn != null)
			conn.createQuery("drop table if exists arraystest").executeUpdate();
	}

}

Could you make a PR for the postgres extended quirk? It works :)

I suppose this would be the appropriate place for it...

For people who might want to use that, not working with 1.6.0-RC3, works nicely with 1.5.4, also with other datatypes like Date :)
👍

@dabrowskid how did you manage with 1.6.0 and array types?