How to Pass String[] to PostgreSQL using Spring JDBC (jdbcTemplate) and Datasource

So, today I had one situation where I need to store String[] in PostgreSQL text[], that too using jdbcTemplate and Datasource.

Solution when using JDBC Connection
When you are using Connection class, it is easy to convert a regular array to java.sql.Array


Connection connnection ; // Assuming you have the connection to Database
Array sqlArray = connection.createArrayOf("text",stringArray);

This is easy when you are using a Connection class, but how to do it when you are using Datasource ?
so That's where I was stuck for a long time and thought of writing about it.



Solution when using Datasource

How to Insert String[] in a table column of type text[] ?

My Solution is kind of workaround using postgreSQL built-in functions

1) Convert String Array to  Comma Separated String

If you are using Java8, it's pretty easy.
other option is here

String commaSeparatedString = String.join(",",stringArray); // Java8 feature


2) PostgreSQL built-in function string_to_array() 


you can find other postgreSQL array functions here


// tableName ( name text, string_array_column_name text[] )
String query = "insert into tableName(name,string_array_column_name ) values(?, string_to_array(?,',') )";


int[] types = new int[] { Types.VARCHAR, Types.VARCHAR};


Object[] psParams = new Object[] {"Dhruvil Thaker",commaSeparatedString };
jdbcTemplate.batchUpdate(query, psParams ,types);


3)  Retrieving array from database

same way you can use array_to_string() built-in function to retrieve it.