Array Support

LeanXcale enhances the standard JDBC functionality by seamlessly integrating array support, providing advanced data manipulation capabilities. This powerful feature simplifies data modeling and is particularly useful when dealing with structured data where you have to aggregate in relationship 1:n. By incorporating array columns into your database schema, you can manage all the array complexity by accessing any of the fields of the array individually, retrieving all the array values, or aggregating in relationships 1:n while maintaining compatibility with standard JDBC practices.

To know more about aggregations, read this.

Create a Table with Array Columns

With LeanXcale, creating a table with array columns is straightforward. You can define an array column in your create table statement using the array data type, allowing you to store collections of values in a single column.

String createTable = "CREATE TABLE tarry (" +
	"id INT, " +
	"longs BIGINT ARRAY, " +
	"doubles DOUBLE ARRAY, " +
	"strings VARCHAR ARRAY, " +
	"PRIMARY KEY(id))";
try (Statement st = conn.createStatement()) {
	st.execute(createTable);
}

Insert in a Table with Array Columns

This code snippet will illustrate how to harness array support to efficiently manage collections of values within your database.

String sql = "INSERT INTO tarry VALUES (" +
	"1, " +
	"array[-4367937811447089352, 7, 5575876413993699465], " +
	"array[0.023533916958532797, 5.476721734666199, 1.0], " +
	"array['MADRID','MURCIA','JEREZ'])";
 try (Statement st = conn.createStatement()) {
	 st.executeUpdate(sql); 
	conn.commit();
}

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.