print all data in paginated table/grid

direct Tabular data display

Recently our project has a page need to show tabular data from 30-6000 rows with 3-4 columns. At first, I thought this is pretty reasonable data to show in one page so I just throw the data into a ng-repeat table with my own implementation of filtering/sorting which is pretty straightforward in angular. Every time user select new category/type, fetch data from backend and replace the data in vm/$scope. Also with this implementation, it is quite easy to fulfill our another requirement which is export/print the page content. For export I just need to get the DOM content to the server and return as downloadable. For print, even easier, just call window.print() ,that’s it.

Performance issue with IE

Everything works fine until  our QA hits IE which is super slow when the data in the list is replaced from backend. Did some profiling in IE11, turns out the appendChild and removeChild calls are taking forever when it tries to clear the rows in the dom and put the new elements into dom. Also another slowness is from styleCalculation which it does for every column/row. Overall, IE takes 20s to render a page with 5000 rows and FF/safari/chrome need only 1-2 seconds. This forces us to abandon the straightforward way to use the more IE friendly way which is pagination with angular ui-grid. But this brings us to another problem which is print since data is now paginated and DOM only has 20 rows.

Server side render and client side print

What I eventually did is sending the model data back to server and do server side rendering and eventually send back to browser where an iFrame is created on the fly for printing. The pros of doing this is we have a lot of flexibility on content/layout by whatever manipulation/styling etc… The cons is we added more stuff to the stack and one more round trip comparing to the direct print.

server side

So on server side, when we get the REST call for print, we have a Thymeleaf template there for generating the html. I compared different java server side rendering engines like Velocity/Freemaker/Rythm etc, looks like Thymeleaf has the best Spring integration and most active development/release.

@Configuration
public class ThymeleafConfig
{
    @Autowired
    private Environment env;

    @Bean
    @Description("Thymeleaf template rendering HTML ")
    public ClassLoaderTemplateResolver exportTemplateResolver() {
        ClassLoaderTemplateResolver exportTemplateResolver = new ClassLoaderTemplateResolver();
        exportTemplateResolver.setPrefix("thymeleaf/");
        exportTemplateResolver.setSuffix(".html");
        exportTemplateResolver.setTemplateMode("HTML5");
        exportTemplateResolver.setCharacterEncoding(CharEncoding.UTF_8);
        exportTemplateResolver.setOrder(1);
        //for local development, we do not want template being cached so that we could do hot reload.
        if ("local".equals(env.getProperty("APP_ENV")))
        {
            exportTemplateResolver.setCacheable(false);
        }
        return exportTemplateResolver;
    }

    @Bean
    public SpringTemplateEngine templateEngine() {
        final SpringTemplateEngine engine = new SpringTemplateEngine();
        final Set<ITemplateResolver> templateResolvers = new HashSet<>();
        templateResolvers.add(exportTemplateResolver());
        engine.setTemplateResolvers(templateResolvers);
        return engine;
    }
}

With the engine we confined, we could used like:

            Context context = new Context();
            context.setVariable("firms", firms);
            context.setVariable("period", period);
            context.setVariable("rptName", rptName);
            context.setVariable("hasFirmId", hasFirmId);
            if (hasFirmId)
            {
                context.setVariable("firmIdType", FirmIdType.getFirmIdType(maybeFirmId).get());
            }

            return templateEngine.process("sroPrint", context);

Template with name sroPrint has some basic Theymleaf directives:

<html xmlns:th="http://www.thymeleaf.org">
<head>
<style>
    table thead tr th, table tbody tr td {
      border: 1px solid black;
      text-align: center;
    }
  </style>

</head>
<body>
<div>
<h4 th:text="${rptName}">report name</h4>
<div style="margin: 10px 0;"><b>Period:</b> <span th:text="${period}"></span>
<div>
  <h4 th:text="${rptName}">report name</h4>
  <div style="margin: 10px 0;"><b>Period:</b> <span th:text="${period}"></span></div>
  <table style="width: 100%; ">
    <thead>
    <tr>
      <th th:if="${hasFirmId}" th:text="${firmIdType}"></th>
      <th>crd #</th>
      <th>Firm Name</th>
    </tr>
    </thead>
    <tbody>
    <tr th:each="firm : ${firms}">
      <td th:if="${hasFirmId}" th:text="${firm.firmId}"></td>
      <td th:text="${firm.crdId}">CRD</td>
      <td th:text="${firm.firmName}">firm name</td>
    </tr>
    </tbody>
  </table>
</div>
</body>
</html>

client side

Now on the client side we need to consume the HTML string from the client side. The flow is we create an iFrame, write the html into it and call browser print on that iFrame and remove the element from DOM. The below implementation is inside the success callback of $http call for getting that dom string. It is in pure js without jQuery, with which it might be a bit more concise.


var printIFrame = document.createElement('iframe');
document.body.appendChild(printIFrame);
printIFrame.style.position = 'absolute';
printIFrame.style.top = '-9999px';
printIFrame.style.left = '-9999px';
var frameWindow = printIFrame.contentWindow || printIFrame.contentDocument || printIFrame;
var wdoc = frameWindow.document || frameWindow.contentDocument || frameWindow;
wdoc.write(res.data);
// tell browser write finished
wdoc.close();
$scope.$emit('UNLOAD');
// Fix for IE : Allow it to render the iframe
frameWindow.focus();
try {
    // Fix for IE11 - printng the whole page instead of the iframe content
    if (!frameWindow.document.execCommand('print', false, null)) {
        // document.execCommand returns false if it failed -http://stackoverflow.com/a/21336448/937891
        frameWindow.print();
    }
    // focus body as it is losing focus in iPad and content not getting printed
    document.body.focus();
}
catch (e) {
    frameWindow.print();
}
frameWindow.close();
setTimeout(function() {
    printIFrame.parentElement.removeChild(printIFrame);
}, 0);

PDF/XLS Export

For xls/pdf export, it is similar to the other POST that I have before. The only difference is the dom string was passed from client there. Here we generate the dom string in server side.

Oracle partition table

We have an oracle table which used to use store proc to populate from other oracle table. Now since our upstream data source migrated to Hive, we need to do it thru jdbc and persist with JPA. And we hit a problem with: ‘inserted partition key does not map to any partition’.

Turns out that in the store proc, before we insert the data, the store proc would create a new partition(month+year) for the table. Googled a lot, it seems there is no good way of handling it in the java world, i guess we have to go to the native sql way to tackle this.

Some notes on what learnt during the research.

Good oracle doc about table partitioning.

command for checking partition info


--view all partitioned tables
SELECT DISTINCT TABLE_NAME FROM ALL_TAB_PARTITIONS;
--user partitioned tables
SELECT DISTINCT TABLE_NAME FROM user_tab_partitions;
--look at all partitions inside a specific table
select * from ALL_TAB_PARTITIONS where table_name = 'YOUR_PAR_TABLE _NAME';
--check partition key
select * from ALL_PART_KEY_COLUMNS;

create table / sequence if not exist.

it’s quite uncommon to check first and then create the table. If you want to have a running code according to your method, this will be:

BEGIN
  DECLARE
    nCount NUMBER;
    v_sql LONG;
  BEGIN -- try to execute sql for UFI_RQST table
    SELECT count(*) into nCount FROM dba_tables where table_name = 'UFI_RQST';
    IF(nCount <= 0)
    THEN
      v_sql:='create table UFI_RQST (UFI_RQST_ID NUMBER(5) PRIMARY KEY, RQST CLOB, RSPNS VARCHAR2(15), CRTD_DT DATE DEFAULT (sysdate))';
      EXECUTE IMMEDIATE v_sql;
      EXECUTE IMMEDIATE 'GRANT SELECT ON UFI_RQST_SEQ TO SNAPADMIN';
      EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON UFI_RQST TO SNAPADMIN';

    END IF;
  END;

  DECLARE v_dummy NUMBER;
  BEGIN -- try to find sequence in data dictionary
    SELECT
      1
    INTO v_dummy
    FROM user_sequences
    WHERE sequence_name = 'UFI_RQST_SEQ';

    -- if sequence found, do nothing
    EXCEPTION WHEN no_data_found THEN -- sequence not found, create it
    EXECUTE IMMEDIATE 'CREATE SEQUENCE UFI_RQST_SEQ
                       START WITH     1
                       INCREMENT BY   1
                       NOCACHE
                       NOCYCLE';
  END;
END;

But I’d rather go catch on the Exception, saves you some unnecessary lines of code:

declare
v_sql LONG;
begin

v_sql:='create table EMPLOYEE
  (
  ID NUMBER(3),
  NAME VARCHAR2(30) NOT NULL
  )';
execute immediate v_sql;

EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -955 THEN
        NULL; -- suppresses ORA-00955 exception
      ELSE
         RAISE;
      END IF;
END; 
/

gwt table td tr getOffsetHeight() inconsistency if border is set

In Gxt3 grid’s column header, if you set any border to the header row, your column header will grow. Also if you have filter in the header, even just hovering on the filter will cause the growth.

After digging into the code, the following code in ColumnHeader causes this: (my current version is 3.0.6)

This is the reason for the growth of hovering on filter .

    protected void activate() {
      if (!cm.isMenuDisabled(indexOf(this))) {
        XElement td = getElement().findParent(&quot;td&quot;, 3);
        int h = td.getHeight(true);
        if (!GXT.isChrome()) {
          getElement().setHeight(h, true);
        }
        if (btn != null) {
          XElement.as(btn).setHeight(h, true);
        }
        td.addClassName(styles.headOver());
      }
    }

This is the code in the protected void adjustColumnWidths(int[] columnWidths). which cause the growth when resize.

        if (rendered) {
          inner.setHeight(cell.getOffsetHeight(), true);
        }

The root cause is from the “getOffsetHeight()” method, for some reason if a border-top/bottom is set to tr,  when getting the OffsetHeight of the td, the result will be (real height + half of the tr border). Notice here i am talking about getting the offset height of td, not tr. However it still includes the border set in tr.  Theoretically, offsetHeight should be the height of the visible area for an object, in pixels. The value contains the height with the padding, scrollBar, and the border, but does not include the margin. Not sure whether this is a bug or a normal behavior.

A example here.

primeface update current table

I have a data table. Each row of the table has a commandButton called ‘Remove’, which is supposed to remove that row from the model and the view and perform an update in-place.

when I click a button on one of the rows, to remove it, it partially works. The corresponding element is removed from the model but the view is not updated.

update=”userTable” is the way in this situation.

I tried : update=”:usersForm:userTable” not working.

Finally the update=”@form” works for me.

 

Another issue is if the commandButton and the update component are not in the same form, the explicit location must be specified just like : update=”:otherFormId:tableToBeUpdatedId”. Even if the component is in the top level, the colon is still necessary for the id. For example, the growl is something very common, we need to updated this way: update=”:growl”

 

JPA,在@OneToMany里加入mappedBy属性

JPA,在@OneToMany里加入mappedBy属性避免生成中间表

使用JPA的时候,如果A B两个实体间是一对多,多对一的关系,如果不在@OneToMany里加入mappedBy属性(相当于inverse=”true”)会导致自动生成一个多余的中间表。比如:

 

@Entity

public class A {

    @OneToMany(mappedBy="a")

    public Set<B> bs = new HashSet<B>(0);

}

@Entity

public class B {

    @ManyToOne    
    public A a;

}
这样写会只成生成表A和表B,B中会有一个到表A的外键。但是如果不加mappedBy=”a”, 那么就会再生成一张A_B表。
又如:
In Customer class

@OneToMany(cascade=ALL, mappedBy="customer")
public  Set getOrders() { 
	return orders; 
}

In Order class

@ManyToOne
@JoinColumn(name="CUST_ID", nullable=false)
public Customer getCustomer() { 
	return customer; 
}

mappedBy=”customer”
说明在两个关联的实体Bean中,orders这一端是关系的拥有者,Orders一方的表中生成到关联类的外键
且mappedBy只有在双向关联时,才会使用这个属性
cascade=ALL
说明,当Orders做CRUD操作时,Customer都会进行相应操作