Fast Displaying Large CSV File, Filter And Save Filtered Data Using SQLite & Java

CSV file often used for download or upload data from/to database. If table’s records count is large or very large then downloaded data to CSV file will have large in size.
Opening that large CSV file using spreadsheet application requires a lot of memory and CPU resources. Depending on user computer specifications, some consume much time to open.

It is much faster using Notepad++ to open large CSV file but if we want to filter data and save it to another CSV then using Notepad++ will not be efficient especially for some complex filtering criteria.

==> The workaround of this situation is to import that large CSV to SQLite. After importing to SQLite, we can filter it using SQL Query and save it back to CSV file.

According to documentation, SQLite is an embedded SQL database engine. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.
Think of SQLite not as a replacement for Oracle but as a replacement for fopen(). Its performance is usually quite good even in low-memory environments.

This blog post gives example on basic connection to SQLite, Sql Query, importing CSV, save query to CSV. Also example on using SQLite in Java so you can build your own application.

MVC Implementation in Java Swing By JFrame & JInternalFrame Approach

MVC Brief

MVC (Model View Controller) is a layer representation to build an application. MVC must separate source code files structure into 3 layers which are Model, View and Controller.
MVC is needed to build large application so that it is easier to maintain and develop. Changes in one layer doesn’t effect to another layers so it is loose coupling.

I will write short explanation about MVC. Model layer is a business logic. It contains entity of a business object i.e customer, product, operation, sales details etc.
Please note that Model is not database access object. Database access is closely related to Model but it is not Model itself. Model uses Database access object to produces business objects.
View layer is simply an UI presentation. Controller layer contains methods to handle user events such as click, keypress, etc. This controller layer sometimes confusing since user events is inside View layer.

Although user events is inside View layer but the handler method itself could be as/in Controller. So controller plays as a bridge between Model & View. It chooses which model object should be accessed and which view sould be presented for that related user event.

To apply complete MVC layers we need 3 source code files at minimum. These 3 represent each one of Model/Controller/View.
I said ‘complete MVC’ because of sometimes application Controller and View combined into one layer only. This simpler M(VC) only separates Model and its View. For this kind of method, it requires 2 files only.
I think most of Swing Application uses this M(VC) layer. View & User events directly access Model objects without a Controller.

Swing MVC with JFrame & JInternaFrame

To think about MVC in Swing, I need to figure out how to separate each one of MVC layers efficiently. I came accross to JFrame & JInternalFrame approach.

Practically, I have main JFrame with child/internal JInternalFrame. Main JFrame acts as a Controller and JInternalFrame is a View. Model represents business logic objects.

Here in this blog post, I will show you a MVC implementation with simple example. This example just shows list of products and also has add new product functionality.
As I said earlier that Main JFrame is a Controller and so between main JFrame & JInternalFrame as View class source code must be separated. Also Model layer off course.

If you want to download my NetBeans Project for build this app then please go to Download Swing MVC Project Source.
However, I will write this projcct inside ‘sourcecode’ post.

Exporting Java DefaultTableModel To Excel Example

Description

DefaultTableModel is one of swing JTable component. This DefaultTableModel class populate JTable data. In other words, JTable data (rows, columns, cells) is a DefaultTableModel instance / object.

However, this post example will show how to populate DefaultTableModel’s column headers, rows, and cells value only. So this example will not showing JTable presentation. FYI, building JTable is pretty easy.
Also DefaultTableModel can be used for storing any kind of tabular data for application purposes without need to presentate its data to JTable.
This post intended to show example for exporting data to excel from DefaultTableModel object.

The library that can build an excel file is ‘Apache POI’. You need to download it through this link http://poi.apache.org/download.html.
The title of this component is ‘the Java API for Microsoft Documents’. I think this title is quite self explanatory. In short, you can write & read MS Documents, including Excel using this component.

The Example

Easy Work With File Stream Processing Using Custom Java Class To Store & Retrieve File

Introduction

Oftentimes, application needs to store documents i.e files or images on database record or file copy under specific folder.

Developers use Streaming class i.e OutputStream, InputStream, FileOutputStream, FileInputStream, BufferedOutputStream, BufferedInputStream.
Count of code’s line is a kind of lot. Although there are good examples spread out in a web, sometimes java beginners might get confused.

So, I made custom java class to make file stream processing simple. I call the class with ‘FileStreamProc’. This class can stored documents to database or file via ftp or copy method. Also retrieving documents from data source.
We don’t have to bother anymore about those streaming classes above when we use FileStreamProc class. However, those classes are inside FileStreamProc off course. Main functions of FileStreamProc are Upload, Download, and Delete for storing, retrieving and deleting respectively.
We only have to write 2 lines of codes to store a file to database.
In example of FileStreamProc usage:

FileStreamProc fp = new FileStreamProc("doc1");
fp.Upload("D:\\folder\\SOP.pdf", ProcessMethod.Database);

‘doc1’ refers to document ID value. ‘D:\folder\SOP.pdf’ is file path value. Pretty simple right ?