What's wrong? RapidClipse, XdevTable shows no data, but data are still in container

Question

I tried to generate a crosstab by using native query in RapidClipse4.
The data were collected and output on console still available, but assigning to a xDevTable did not work.

I created following code:

private void button_buttonClick(final Button.ClickEvent event) {

        try {
            final String selJahr = this.cmbJahr.getSelectedItem().getBean().getJahr();
            final StringBuilder sb = new StringBuilder();
            sb.append("select FLOOR(RAND() * 999999) as rid, l1_Id as `l1id`, l1_GroupName as `l1GroupName`, l2_Id as `l2id`, l2_GroupName as `l2GroupName`, ");
            sb.append("sum( case when (monat = 1) then dbetrag end ) AS `Jan`, ");
            sb.append("sum( case when monat = 2 then dbetrag end ) AS `Feb`, ");
            sb.append("sum( case when monat = 3 then dbetrag end ) AS `Mrz`, ");
            sb.append("sum( case when monat = 4 then dbetrag end ) AS `Apr`, ");
            sb.append("sum( case when monat = 5 then dbetrag end ) AS `Mai`, ");
            sb.append("sum( case when monat = 6 then dbetrag end ) AS `Jun`, ");
            sb.append("sum( case when monat = 7 then dbetrag end ) AS `Jul`, ");
            sb.append("sum( case when monat = 8 then dbetrag end ) AS `Aug`, ");
            sb.append("sum( case when monat = 9 then dbetrag end ) AS `Sep`, ");
            sb.append("sum( case when monat = 10 then dbetrag end ) AS `Okt`, ");
            sb.append("sum( case when monat = 11 then dbetrag end ) AS `Nov`, ");
            sb.append("sum( case when monat = 12 then dbetrag end ) AS `Dez`, ");
            sb.append("sum(dbetrag) as `Sum` ");
            sb.append("from V_HibCrosUmsGiroOpaOmaReport ");
            sb.append("where  jahr='2019' and konto_Id in ('7,8') ");
            sb.append("group by l1_Id, l1_GroupName, l2_Id, l2_GroupName; ");

            final String sqlq = sb.toString();
            final EntityManagerFactory emf = Persistence.createEntityManagerFactory("RC_07");
            final EntityManager em = emf.createEntityManager();

            System.out.print( "SQL String: " + sqlq + "/n");

            final Query q = em.createNativeQuery(sqlq);
            // will be later used:
            //q.setParameter(1, selJahr);
            //q.setParameter(2, "7,8");
            // final List<VUmsCrosstbl> VUmsCrosstbl = q.getResultList();


            final XdevBeanItemContainer<VUmsCrosstbl> myContainer = new XdevBeanItemContainer<>(VUmsCrosstbl.class);
            myContainer.addAll(q.getResultList());

            this.tblUmsCross.setContainerDataSource(myContainer);
            this.tblUmsCross.setVisibleColumns(VUmsCrosstbl_.rid.getName(),VUmsCrosstbl_.l1Id.getName(),VUmsCrosstbl_.l1GroupName.getName(),VUmsCrosstbl_.l2Id.getName(),VUmsCrosstbl_.l2GroupName.getName(),VUmsCrosstbl_.jan.getName(),VUmsCrosstbl_.feb.getName(),VUmsCrosstbl_.mrz.getName(),VUmsCrosstbl_.apr.getName(),VUmsCrosstbl_.mai.getName(),VUmsCrosstbl_.jun.getName(),VUmsCrosstbl_.jul.getName(),VUmsCrosstbl_.aug.getName(),VUmsCrosstbl_.sep.getName(),VUmsCrosstbl_.okt.getName(),VUmsCrosstbl_.nov.getName(),VUmsCrosstbl_.dez.getName(),VUmsCrosstbl_.sum.getName());
            this.tblUmsCross.setId(VUmsCrosstbl_.rid.getName());
            this.tblUmsCross.setColumnHeaders(new String[] {"rid", "l1_Id", "l1_GroupName", "l2_Id", "l2_GroupName", "Jan","Feb","Mrz","Apr","Mai","Jun","Jul","Aug","Sep","Okt","Nov","Dez", "Sum"});
            this.tblUmsCross.addItems(q.getResultList());
            this.tblUmsCross.getBeanContainerDataSource().refresh();


            final List<Object[]> VUmsCrosstbl = q.getResultList();
            for (final Object[] a : VUmsCrosstbl) {
                System.out.println("Ds: " + a[0] + " " + a[1] + " " + a[2] + " " + a[3] + " " + a[4] + " " + a[5] + " " + a[6] + " " + a[7] + " " + a[8] + " " + a[9] + " " + a[10] + " " + a[11] + " " + a[12] + " " + a[13] + " " + a[14] + " " + a[15]);
            }

            em.close();

        } catch (final Exception e) {
            e.printStackTrace();
            Notification.show("Do isch was falsch bei button_buttonClick  ", e.getMessage(),
                    Notification.Type.ERROR_MESSAGE);
        }

    }

The process runs through without any error
the rows near the end of code " System.out.println("Ds: ..." are only for checking if container has data. These rows prints on console exactly expected output and all expected data

in the application, the colums(Headers) are still created and visible, but no rows with data. The cells are empty.

Where are my error/ what did I wrong? Any idea/ help would be appreciated.

I assume, you should also know:

The table-name which I use in the query is different to the entity name "VUmsCrosstbl"

Why?

The entity name "VUmsCrosstbl" which I use is a db-view, which includes exactly the fields which I get as a result set by the query.

With this trick I could use the entity builder from rapidclipse which generates entity and dao automaticaly.

For the View V_HibCrosUmsGiroOpaOmaReport which I use in the query, the month names Jan-Dez are still not in entity or dao which I first used and produced an other error.

Thank you in advance rgds OpaHeinz

Answer 1

I found the solution by my self:

1) I am still not sure why the above code did not work, I can only assume, that there is something wrong in assignment process:

    myContainer.addAll(q.getResultList()); 

to get it working i changed the DAU and placed following code:

            public List<VUmsCrosstbl> getAllCrossDataforYearKonto2 ()
    {  //final String pjahr, final String pKontoIDs

        final StringBuilder sb = new StringBuilder();
                sb.append("select FLOOR(RAND() * 999999) as rid, l1_Id, L1_GroupName, l2_Id, L2_GroupName, ");
                sb.append("sum( case when (monat = 1) then dbetrag end ) AS `Jan`, ");
                sb.append("sum( case when monat = 2 then dbetrag end ) AS `Feb`, ");
                sb.append("sum( case when monat = 3 then dbetrag end ) AS `Mrz`, ");
                sb.append("sum( case when monat = 4 then dbetrag end ) AS `Apr`, ");
                sb.append("sum( case when monat = 5 then dbetrag end ) AS `Mai`, ");
                sb.append("sum( case when monat = 6 then dbetrag end ) AS `Jun`, ");
                sb.append("sum( case when monat = 7 then dbetrag end ) AS `Jul`, ");
                sb.append("sum( case when monat = 8 then dbetrag end ) AS `Aug`, ");
                sb.append("sum( case when monat = 9 then dbetrag end ) AS `Sep`, ");
                sb.append("sum( case when monat = 10 then dbetrag end ) AS `Okt`, ");
                sb.append("sum( case when monat = 11 then dbetrag end ) AS `Nov`, ");
                sb.append("sum( case when monat = 12 then dbetrag end ) AS `Dez`, ");
                sb.append("sum(dbetrag) as `Sum` ");
                sb.append("from V_HibCrosUmsGiroOpaOmaReport ");
                sb.append("where  jahr= ? and konto_Id in ( ? ) ");
                sb.append("group by l1_Id, L1_GroupName, l2_Id, L2_GroupName; ");
                final String sqlq = sb.toString();


        final Query nativeQuery = em().createNativeQuery(sqlq, VUmsCrosstbl.class);
        nativeQuery.setParameter(1, "2019");
        nativeQuery.setParameter(2, "7,8");
        final List<VUmsCrosstbl> resultList = nativeQuery.getResultList();

        return resultList;
    }

Then I used this function in the click event of the button, to assign the result into XDevBeanitemContainer and into table:

        private void button_buttonClick(final Button.ClickEvent event) {

            final XdevBeanItemContainer<VUmsCrosstbl> myContainer = new XdevBeanItemContainer<>(VUmsCrosstbl.class);
            myContainer.addAll(new VUmsCrosstblDAO().getAllCrossDataforYearKonto2());

            this.tblUmsCross.setContainerDataSource(myContainer);
            this.tblUmsCross.setId(VUmsCrosstbl_.rid.getName());
            this.tblUmsCross.setVisibleColumns(VUmsCrosstbl_.rid.getName(),VUmsCrosstbl_.l1Id.getName(),VUmsCrosstbl_.l1GroupName.getName(),VUmsCrosstbl_.l2Id.getName(),VUmsCrosstbl_.l2GroupName.getName(),VUmsCrosstbl_.jan.getName(),VUmsCrosstbl_.feb.getName(),VUmsCrosstbl_.mrz.getName(),VUmsCrosstbl_.apr.getName(),VUmsCrosstbl_.mai.getName(),VUmsCrosstbl_.jun.getName(),VUmsCrosstbl_.jul.getName(),VUmsCrosstbl_.aug.getName(),VUmsCrosstbl_.sep.getName(),VUmsCrosstbl_.okt.getName(),VUmsCrosstbl_.nov.getName(),VUmsCrosstbl_.dez.getName(),VUmsCrosstbl_.sum.getName());
            this.tblUmsCross.setColumnHeaders(new String[] {"rid", "l1_Id", "l1_GroupName", "l2_Id", "l2_GroupName", "Jan","Feb","Mrz","Apr","Mai","Jun","Jul","Aug","Sep","Okt","Nov","Dez", "Sum"});


        }

After this, the data was visible in the table.
I am happy to got this solved.
rgds
OpaHeinz