StringArrayType.INSTANCE is not working with a two dimensional String array parameter on PostgreSQL

[AlexisJehan]

Hibernate ORM core version: 5.6.8.Final
Hibernate Types 55 version: 2.16.2

StringArrayType is expected to work with multidimensional arrays based on the Javadoc, but that isn't the case when used with Hibernate native query parameters and PostgreSQL.

One dimensional array parameter is working as expected:

public Customer findWithOneDimensionalArrayParameter() {
	return (Customer) entityManager
			.createNativeQuery(
					"SELECT 1 AS id, 'foo' AS first_name, 'bar' AS last_name WHERE :param = ARRAY['foo'];",
					Customer.class
			)
			.unwrap(Query.class)
			.setParameter("param", new String[] {"foo"}, StringArrayType.INSTANCE)
			.getSingleResult();
}

But two dimensional array parameter isn't working:

public Customer findWithTwoDimensionalArrayParameter() {
	return (Customer) entityManager
			.createNativeQuery(
					"SELECT 1 AS id, 'foo' AS first_name, 'bar' AS last_name WHERE :param = ARRAY[ARRAY['foo'], ARRAY['bar']];",
					Customer.class
			)
			.unwrap(Query.class)
			.setParameter("param", new String[][] {new String[] {"foo"}, new String[] {"bar"}}, StringArrayType.INSTANCE)
			.getSingleResult();
}

The Exception:

java.lang.IllegalArgumentException: Array-valued parameter value element [[Ljava.lang.String;@f10d055] did not match expected type [[Ljava.lang.String; (n/a)]

Demo:
demo.zip

Thanks having a look.

[vladmihalcea]

Check out this test case that shows what's supported since it rund just fine.

You can compare it to your use case and see why mine works and yours doesn't.

[AlexisJehan]

Check out this test case that shows what's supported since it rund just fine.

You can compare it to your use case and see why mine works and yours doesn't.

Maybe I'm wrong, but your test case is covering the use of a multidimensional array as an entity attribute, not as a query parameter like what I'm aiming for.

[vladmihalcea]

Then, it could be an issue. Unfortunately, I don't have time at the moment to investigate it in my spare time, but thanks to OSS, this could be fixed by anyone who's interested in this.

This is what's going to be needed:

• First, a replicating test case in the MultiDimensionalStringArrayTypeTest class
• Second, one could try to debug it, find the root cause of the issue, and provide a fix
• The provided test case should pass then
• Once all that's done, send me a Pull Request

Or, if a company is in a hurry and wants this issue to be done sooner than later, then I could try to fix it via consulting.

[priyadarshan85]

@vladmihalcea I will pick this up for resolution

[ecopsdee]

@priyadarshan85 @AlexisJehan i would like to email you. would you mind help me to resolve some configuration issues. your assistance would be helpful. we could connect via twitter or instagram.

[AlexisJehan]

@priyadarshan85 @AlexisJehan i would like to email you. would you mind help me to resolve some configuration issues. your assistance would be helpful. we could connect via twitter or instagram.

I sent you an email.

[priyadarshan85]

Sure ..let me know how to connect

[priyadarshan24]

@vladmihalcea I have been trying to compile the project through maven but getting following errors:

Cannot resolve Failure to transfer com.sun.istack:istack-commons-runtime:pom:3.0.6 from http://bits.netbeans.org/nexus/content/groups/netbeans was cached in the local repository, resolution will not be reattempted until the update interval of netbeans has elapsed or updates are forced. Original error: Could not transfer artifact com.sun.istack:istack-commons-runtime:pom:3.0.6 from/to netbeans (http://bits.netbeans.org/nexus/content/groups/netbeans): Not authorized

Cannot resolve Failure to transfer javax.xml.bind:jaxb-api:pom:2.3.0-b161121.1438 from http://bits.netbeans.org/nexus/content/groups/netbeans was cached in the local repository, resolution will not be reattempted until the update interval of netbeans has elapsed or updates are forced. Original error: Could not transfer artifact javax.xml.bind:jaxb-api:pom:2.3.0-b161121.1438 from/to netbeans (http://bits.netbeans.org/nexus/content/groups/netbeans): Not authorized

Cannot resolve Failure to transfer com.sun.xml.fastinfoset:FastInfoset:pom:1.2.14 from http://bits.netbeans.org/nexus/content/groups/netbeans was cached in the local repository, resolution will not be reattempted until the update interval of netbeans has elapsed or updates are forced. Original error: Could not transfer artifact com.sun.xml.fastinfoset:FastInfoset:pom:1.2.14 from/to netbeans (http://bits.netbeans.org/nexus/content/groups/netbeans): Not authorized

Can u pls help here if this is a known issue and there is a workaround available?

[priyadarshan24]

@vladmihalcea Fixed the issue .. problem is that netbeans repo has moved

Adding the following to parent pom fixed the issue

[vladmihalcea]

The library should not need any istack-common library or NetBeans repository. Try running Maven from the command line instead. Most likely, it's a Maven issue.

[priyadarshan85]

@vladmihalcea
I debugged the issue by adding a test case. Following is the code snippet:

doInJPA(entityManager -> {
List tuples = entityManager
.createNativeQuery(
"Select * from plane WHERE seat_grid = :param", Tuple.class)
.unwrap(NativeQuery.class)
.addScalar(
"seat_grid",
new StringArrayType(
ReflectionUtils.getField(Plane.class, "seatGrid").getType()
)
)
.addScalar("name", StringType.INSTANCE)
.addScalar("id", LongType.INSTANCE)
//.setParameter("param", 1)
.setParameter("param", new TypedParameterValue(new StringArrayType(ReflectionUtils.getField(Plane.class, "seatGrid").getType()), new String[][] {
{"BLOCKED", "BLOCKED", "BLOCKED", "BLOCKED"}
}))
.getResultList();

I noticed that unwrap method is not called when setParameter for setting the :param value. In all other cases, while insert of data in DB, unwrap method is been called.

I tried different ways to call setParameter but still no avail.

Any hints here to further debug this

[priyadarshan85]

Following is the call stack when insert operation happens:

Thus, insert statement with parameter binding works perfectly fine but the same does not work with the issue under test. Exception is thrown during "SetParameter" call itself

There seems to be problem with how param value is set

[priyadarshan85]

@vladmihalcea is it possible to get some guidance here?