How create a SQL array from a Java List?

23,364

Use "varchar" instead of "VARCHAR". See http://grepcode.com/file/repo1.maven.org/maven2/postgresql/postgresql/9.0-801.jdbc4/org/postgresql/jdbc2/TypeInfoCache.java#TypeInfoCache.0types

Share:
23,364
Renato Dinhani
Author by

Renato Dinhani

Solving real problems.

Updated on July 09, 2022

Comments

  • Renato Dinhani
    Renato Dinhani over 1 year

    I'm trying to insert an array in a table, but I need to convert the list to the SQL Array type. I'm using the Connection#createArrayOf() method, but I'm getting an exception.

    I need to pass a type name, but I don't know what is this and I always get an exception. The array is from VARCHAR.

    How I solve this to insert the array?

    The code

    Object[] array = new Object[token.getCategories().size()];
    array = token.getCategories().toArray();
    pstmTokenInsert.setArray(1, conn.createArrayOf("VARCHAR", array));
    

    The stacktrace

    org.postgresql.util.PSQLException: Could not find array type for data type VARCHAR
    at org.postgresql.jdbc4.AbstractJdbc4Connection.createArrayOf(AbstractJdbc4Connection.java:73)
    at org.postgresql.jdbc4.Jdbc4Connection.createArrayOf(Jdbc4Connection.java:21)
    at org.apache.commons.dbcp.DelegatingConnection.createArrayOf(DelegatingConnection.java:560)
    at br.ifsp.da.data.TokenDAO.insertTokens(TokenDAO.java:37)
    at br.ifsp.da.data.ProcessedPageInserter.loopInsertion(ProcessedPageInserter.java:44)
    at br.ifsp.da.data.ProcessedPageInserter.call(ProcessedPageInserter.java:27)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
    at java.util.concurrent.FutureTask.run(FutureTask.java:166)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
    at java.lang.Thread.run(Thread.java:722)
    
  • Renato Dinhani
    Renato Dinhani over 12 years
    Thank you, master. Helped me a lot. Was a simple mistake (that take me some hours), but I didn't found this information anywhere. Thanks.
  • Damith Ganegoda
    Damith Ganegoda over 9 years
    when I change to 'varchar' I got this error. org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = character varying[] Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts
  • JuliuszJ
    JuliuszJ over 6 years
    @Damith You can use =ANY operator instead of IN