Array of native query to Java object

Question

Array of native query to Java object

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (-2 votes)

2

I have a problem when performing a native query, where the result in SQLManager is this:

Query: "select p.cod_producto, p.deno_producto, array(SELECT t.cod_tanque FROM estagas.tanqu063 t where t.cod_producto = p.cod_producto) as tanks, array(SELECT m.cod_manguera as varchar FROM estagas.tanqu063 t inner join estagas.mangu063 m on m.cod_tanque = t.cod_tanque where t.cod_producto = p.cod_producto) as hoses from estagas.produ063 p"

In my code I made the query:

List<ProductsTanksHosesDTO> dto = null;
String query = "select p.cod_producto, p.deno_producto, "
            + "array(SELECT t.cod_tanque FROM estagas.tanqu063 t where t.cod_producto = p.cod_producto) as tanks, "
            + "array(SELECT m.cod_manguera as varchar FROM estagas.tanqu063 t inner join estagas.mangu063 m on m.cod_tanque = t.cod_tanque where t.cod_producto = p.cod_producto) as hoses "
            + "from estagas.produ063 p";

dto = em.createNativeQuery(query).getResultList();

Which gives me the following error:

27-11-2017 12:33:26.090 [JavaFX Application Thread] ERROR java.lang.NullPointerException: null
at org.hibernate.loader.custom.ScalarResultColumnProcessor.extract(ScalarResultColumnProcessor.java:54) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.loader.custom.ResultRowProcessor.buildResultRow(ResultRowProcessor.java:83) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.loader.custom.ResultRowProcessor.buildResultRow(ResultRowProcessor.java:60) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:413) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:769) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.loader.Loader.processResultSet(Loader.java:985) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.loader.Loader.doQuery(Loader.java:943) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2615) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.loader.Loader.doList(Loader.java:2598) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.loader.Loader.list(Loader.java:2425) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2160) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:992) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:148) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at org.hibernate.query.Query.getResultList(Query.java:146) ~[hibernate-core-5.2.10.Final.jar:5.2.10.Final]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_131]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:372) ~[spring-orm-4.3.7.RELEASE.jar:4.3.7.RELEASE]
at com.sun.proxy.$Proxy217.getResultList(Unknown Source) ~[na:na]
at com.avalon.localbos.service.impl.ProductServiceImpl.findAllProductsTanksHosesDTO(ProductServiceImpl.java:82) ~[classes/:na]

Being the method that causes the error:

@Override
public Object extract(Object[] data, ResultSet resultSet, SharedSessionContractImplementor session)
        throws SQLException, HibernateException {
    return type.nullSafeGet( resultSet, alias, session, null );
}

The problem is the arrays, since when performing the same query without them, it works correctly.

My class ProductsTanksHosesDTO:

public class ProductsTanksHosesDTO {

private String productCode;
private String productName;
private Object[] tanks;
private Object[] hoses;

public ProductsTanksHosesDTO(String productCode, String productName, Object[] tanks, Object[] hoses) {
    this.productCode = productCode;
    this.productName = productName;
    this.tanks = tanks;
    this.hoses = hoses;
}
...

Thank you very much.

sql java query hibernate

asked by accnono 27.11.2017 в 12:38

source

3 answers

Argument is not a function, got undefined Magento 2, checkout error Copy does not exist

score 0 · Answer 1

to solve your error, you have to do double parse in the query because the truth, pretending that the native query is passed to a list of objects is not possible, so what you have to do is the following:

List<Object> listaObjetosNativos = em.createNativeQuery(query).getResultList();

That you can go through like this:

Object[] fila;

for(Object item : listaObjetosNativos){
  fila = (Object[]) item;
  //Desde esta linea empiezas a utlizar el contenido de cada fila para pasarlo a tu POJO
}

I hope you get the answer but please let me know, good morning.

score 0 · Answer 2

Finally I have managed to solve it by doing the native query without Spring, I guess it does not allow the query of arrays for any reason.

The query was like this:

Statement st = ApplicationConfiguration.dataSource().getConnection().createStatement();
ResultSet resultSet = st.executeQuery(query);

Where dataSource() makes the connection to the database without the use of Spring.

score -2 · Answer 3

-2

It gives you an error because you declare the list as null. Try to do:

List<ProductsTanksHosesDTO> dto = new List<ProductsTanksHosesDTO>();

answered by 27.11.2017 в 12:41