Tag: Got SQL

POI provides all-around functionalities for parsing and generating xls file. The problem is that it has a too low-level API that makes users write a lot of code from scratch even for the simple read/write actions. And to be all-around means that every detail needs to be taken care of, leading to complicated, heavy, hard-coded details handling process. Another issue is that POI invests too much in configuring too many appearance properties, such as font, color, thickness and alignment, yet its data processing capabilities are not as professional as expected when data parsing and generation should have been the focus

Parsing, generating, querying and computing Excel files are common tasks Java often handles. As Excel has complicated file format, it is hard to read /write Excel files by writing your own code. But this is made convenient by various class libraries like POI, EasyExcel and JExcel, where POI is the best. POI has all-around and comprehensive abilities to read/write Excel files POI can read/write Excel files of various formats, from the older binary format (xls) to the current OOXML (xlsx) format. It supports both large memory jobs by reading/writing the whole Excel file at once and small memory jobs by

SQL has good computing ability. It is widely used during Java development to handle structured data. Yet the language is tightly bounded with databases and has architectural defects, resulting in code migration difficulty, non-hot-swappable business logic, and cost-ineffective efforts in increasing performance. Today’s application frameworks prefer to implement business logics directly in Java while using the database only for data persistence. This calls for techniques for computing structured data outside databases. Java in its early days didn’t offer class libraries for structured data computations. Hardcoding was the only choice and it was really hard. Later the high-level language released Stream

It is not rare that during Java development we encounter structured data computation scenarios where databases are unavailable or inconvenient to use. Java did not provide special class libraries to compute structured data in its early versions. We had to hardcode even basic computations, such as sorting and grouping. The development efficiency was extremely low. It later released Stream in Java8. The package features Lambda expression, chain coding and set-oriented functions, finally equipping the language with structured data computation class libraries. Stream simplifies structured data computations For example, sorting: In the above code, sorted is a set-oriented function for sorting

The general trend of contemporary Java application frameworks is towards the separation of data storage from data processing, aiming to be more maintainable, more scalable and more migratable. One typical example is the currently hot microservices. The new framework requires that business logic, rather than performed in the database as the conventional framework designs, should be implemented within the Java program. On most occasions, the business logic in an application involves structured data processing. Databases (based on SQL) give a lot of supports for the processing, enabling to implement the business logic in a relatively simple way. Such supports, however,

SQL syntax is close to natural language, with low learning threshold and the bonus of first mover advantage, it soon became popular between database manufacturers and users. After years of development, SQL has become the most widely used and most mature structured data computing language. However, SQL must work based on RDB, and there is no RDB in many scenarios, such as encountering CSV \ restful JSON \ MongoDB and other data sources, or performing mixed calculations between these data sources, such as CSV and XLS. In these scenarios, many people will choose to hard code algorithms in high-level languages

An indirect way is through a Java embedded database. We first load xls to it and then execute SQL. The advantage is that it makes full use of SQL’s computing ability, and disadvantages include extremely low real-time capability and a complicated loading process that involves checking if the same table name already exists, deleting and creating a table, parsing the file, importing data, creating the index, and so on. Moreover, data loading would fail if the source file has an irregular format. The direct way is to invoke the xls-based JDBC driver in Java, such as XLSJDBC, xlSQL and CDATA

POI does not have further query and computing capabilities after it parses xls. Programmers need to import data and hardcode a specific computing task. Embedded databases are exactly the opposite with their query and computing abilities and the inability to parse. Besides, they boast time-consuming data loading process and heavy frameworks. A fast and lightweight solution is to execute SQL directly on an xls file. But there are only a few open-source, rudimentary libraries for achieving the solution. Their practical uses are rather limited. DataFrame-style open-source libraries are more mature, having no need to interpret SQL and at the same