Select from a Table with Array Columns

The following code snippets will showcase how you can leverage the array support to retrieve and manipulate data.

Querying Arrays Values
Query Arrays Using Statement

You can get the array with a JDBC statement:

try (Statement s = conn.createStatement()) {
	try (ResultSet rs = s.executeQuery("SELECT * FROM tarry WHERE ROWNUM = 1")) {
		if (rs.next()) {
			int id = rs.getInt("id");
			String typeLong = rs.getMetaData().getColumnTypeName(2);
			System.out.println("columnType :" + typeLong);      //BIGINT ARRAY
			Object[] longs = (Object[]) ((ArrayImpl) rs.getObject(2)).getArray();
			for (int i = 0; i < longs.length; i++) {
				long item = (long) longs[i];
				System.out.println("long[" + i + "]" + item);
			}
			String typeDouble = rs.getMetaData().getColumnTypeName(3);
			System.out.println("columnType :" + typeDouble);    //DOUBLE ARRAY
			Object[] doubles = (Object[]) ((ArrayImpl) rs.getObject(3)).getArray();
			for (int i = 0; i < doubles.length; i++) {
				double item = (double) doubles[i];
				System.out.println("double[" + i + "]" + item);
			}
			String typeString = rs.getMetaData().getColumnTypeName(4);
			System.out.println("columnType :" + typeString);    //VARCHAR ARRAY
			Object[] strings = (Object[]) ((ArrayImpl) rs.getObject(4)).getArray();
			for (int i = 0; i < strings.length; i++) {
				String item = (String) strings[i];
				System.out.println("string[" + i + "]" + item);
			}
		}
	}
}
Query Arrays Using PreparedStatement

This example shows how you would bind an array value using a Prepared Statement, making it easier to parameterize your queries.

try (PreparedStatement pst = conn.prepareStatement("SELECT id FROM tarry WHERE doubles = ?")) {
	Double[] values = {0.023533916958532797, 5.476721734666199, 1.0};
	Array sqlArray = conn.createArrayOf("DOUBLE", values);
	pst.setArray(1, sqlArray);
	try (ResultSet rs = pst.executeQuery()) {
		while (rs.next()) {
			int id = rs.getInt("id");
			System.out.println("id " + id);
		}
	}
}
Recovery of the Array Values as String

You can retrieve the values of arrays as strings, which can be particularly useful if you need to process or display them in a human-readable format.

try (Statement s = conn.createStatement()) {
	try (ResultSet rs = s.executeQuery("SELECT id, TO_string(longs), TO_string(doubles), TO_string(strings) FROM tarry")) {
		while (rs.next()) {
			int id = rs.getInt("id");
			String longs = rs.getString(2);
			String doubles = rs.getString(3);
			String strings = rs.getString(4);
		}
	}
}
Filter Using the Array Column
Filter Using the Entire Column

You can filter all the records with an array column containing a specific array by constructing SQL queries that match the desired array values.

try (Statement s = conn.createStatement()) {
	try (ResultSet rs = s.executeQuery("SELECT * FROM tarry WHERE strings = array['CUENCA', 'BILBAO']")) {
		while (rs.next()) {
			int id = rs.getInt(1);
			Array longs = rs.getArray(2);
			Array doubles = rs.getArray(3);
			Array strings = rs.getArray(4);
		}
	}
}
Filter all the Records with an Array Column Containing a Specific Array
try (Statement s = conn.createStatement()) {
	try (ResultSet rs = s.executeQuery("SELECT id FROM tarry, UNNEST(tarry.strings) AS t(flattened_string) where flattened_string='MADRID]")) {
		while (rs.next()) {
			int id = rs.getInt(1);
			System.out.println("id " + id);
		}
	}
}
Filter by the Array Column Position

To filter records based on the value of a position of an array within a column, you can utilize LeanXcale’s features to retrieve them.

try (Statement s = conn.createStatement()) {
	try (ResultSet rs = s.executeQuery("SELECT id FROM tarry WHERE longs[3] = 5575876413993699465")) {
		while (rs.next()) {
			int id = rs.getInt(1);
			System.out.println("id " + id);
		}
	}
}

These capabilities empower you to work effectively with array columns in LeanXcale, enabling you to handle complex data structures and perform operations straightforwardly.