I'm using Spring JdbcTemplate, and I'm stuck at the point where I have a query that updates a column that is actually an array of int. The database is postgres 8.3.7. This is the code I'm using :
public int setUsersArray(int idUser, int idDevice, Collection<Integer> ids) {
int update = -666;
int[] tipi = new int[3];
tipi[0] = java.sql.Types.INTEGER;
tipi[1] = java.sql.Types.INTEGER;
tipi[2] = java.sql.Types.ARRAY;
try {
update = this.jdbcTemplate.update(setUsersArrayQuery, new Object[] {
ids, idUser, idDevice }, tipi);
} catch (Exception e) {
e.printStackTrace();
}
return update;
}
The query is "update table_name set array_column = ? where id_user = ? and id_device = ?". I get this exception :
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [update acotel_msp.users_mau set denied_sub_client = ? where id_users = ? and id_mau = ?]; The column index is out of range: 4, number of columns: 3.; nested exception is org.postgresql.util.PSQLException: The column index is out of range: 4, number of columns: 3.
Caused by: org.postgresql.util.PSQLException: The column index is out of range: 4, number of columns: 3.
I've looked into spring jdbc template docs but I can't find any help, I'll keep looking, anyway could someone point me to the right direction? Thanks!
EDIT :
Obviously the order was wrong, my fault...
I tried both your solutions, in the first case I had this :
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [update users set denied_sub_client = ? where id_users = ? and id_device = ?]; nested exception is org.postgresql.util.PSQLException: Cannot cast an instance of java.util.ArrayList to type Types.ARRAY
Trying the second solution I had this :
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [update users set denied_sub_client = ? where id_users = ? and id_device = ?]; nested exception is org.postgresql.util.PSQLException: Cannot cast an instance of [Ljava.lang.Object; to type Types.ARRAY
I suppose i need an instance of java.sql.Array, but how can I create it using JdbcTemplate?
After struggling with many attempts, we settled to use a little helper ArraySqlValue to create Spring SqlValue objects for Java Array Types.
usage is like this
jdbcTemplate.update(
"UPDATE sometable SET arraycolumn = ?",
ArraySqlValue.create(arrayValue))
The ArraySqlValue can also be used in MapSqlParameterSource for use with NamedParameterJdbcTemplate.
import static com.google.common.base.Preconditions.checkNotNull;
import java.sql.Array;
import java.sql.JDBCType;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Locale;
import org.springframework.jdbc.core.StatementCreatorUtils;
import org.springframework.jdbc.support.SqlValue;
public class ArraySqlValue implements SqlValue {
private final Object[] arr;
private final String dbTypeName;
public static ArraySqlValue create(final Object[] arr) {
return new ArraySqlValue(arr, determineDbTypeName(arr));
}
public static ArraySqlValue create(final Object[] arr, final String dbTypeName) {
return new ArraySqlValue(arr, dbTypeName);
}
private ArraySqlValue(final Object[] arr, final String dbTypeName) {
this.arr = checkNotNull(arr);
this.dbTypeName = checkNotNull(dbTypeName);
}
@Override
public void setValue(final PreparedStatement ps, final int paramIndex) throws SQLException {
final Array arrayValue = ps.getConnection().createArrayOf(dbTypeName, arr);
ps.setArray(paramIndex, arrayValue);
}
@Override
public void cleanup() {}
private static String determineDbTypeName(final Object[] arr) {
// use Spring Utils similar to normal JdbcTemplate inner workings
final int sqlParameterType =
StatementCreatorUtils.javaTypeToSqlParameterType(arr.getClass().getComponentType());
final JDBCType jdbcTypeToUse = JDBCType.valueOf(sqlParameterType);
// lowercasing typename for Postgres
final String typeNameToUse = jdbcTypeToUse.getName().toLowerCase(Locale.US);
return typeNameToUse;
}
}
this code is provided in the Public Domain
private static final String ARRAY_DATATYPE = "int4";
private static final String SQL_UPDATE = "UPDATE foo SET arr = ? WHERE d = ?";
final Integer[] existing = ...;
final DateTime dt = ...;
getJdbcTemplate().update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(final Connection con) throws SQLException {
final PreparedStatement ret = con.prepareStatement(SQL_UPDATE);
ret.setArray(1, con.createArrayOf(ARRAY_DATATYPE, existing));
ret.setDate(2, new java.sql.Date(dt.getMillis()));
return ret;
}
});
This solution is kind of workaround using postgreSQL built-in function, which definitely worked for me.
reference blog
1) Convert String Array to Comma Separated String
If you are using Java8, it's pretty easy. other options are 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); // assuming you have jdbctemplate instance
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With