How create a SQL array from a Java List?

Question

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)

Are you using Oracle? The SQL array type highly depends on the underlying database. — ManuPK
– ManuPK, Commented Oct 28, 2011 at 15:53
I guess this would be helpful for you: download.oracle.com/javase/tutorial/jdbc/basics/array.html — Konstantin Yovkov
– Konstantin Yovkov, Commented Oct 28, 2011 at 15:59

JB Nizet · Accepted Answer · 2011-10-28 22:02:40Z

18

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

answered Oct 28, 2011 at 22:02

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Renato Dinhani Over a year ago

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 Over a year ago

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 Over a year ago

@Damith You can use =ANY operator instead of IN

Deepak Sharma · Accepted Answer · 2024-02-29 22:35:51Z

0

I prefer to used the enum. Use PostgresSqlType.STRING.getValue() instead.

Below are the possible values of different type. These are case sensitive so check the case.

public enum PostgresSqlType {
    SHORT("int2"),
    INT("int4"),
    LONG("int8"),
    FLOAT("float4"),
    DOUBLE("float8"),
    BOOLEAN("bool"),
    STRING("varchar"), // or "text"
    BYTE("bytea");

answered Feb 29, 2024 at 22:35

Deepak Sharma

514 bronze badges

Collectives™ on Stack Overflow

How create a SQL array from a Java List?

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related