ZK Basic MVC Pattern with SelectorComposer

Introduction

From official site:
A composer analogous to GenericForwardComposer. Instead of wiring variables and adding event listeners by naming convention, this composer do the work by annotation and selectors.
...
SelectorComposer, as one of the most popular skeletons, wires components, variables and event listeners automatically based on Java annotations you specify.

This post practice the MVC pattern with SelectorComposer in ZK.

Note: As mentioned in the title, only very basic part in this article.

Pre-request

Please refer to http://ben-bai.blogspot.tw/2012/10/zk-basic-mvc-pattern-with.html
for the basic concept of Composer.

The ZUL Page

basic_SelectorComposer.zul

The selector composer can wire components/listen events by jquery-selector like way, it is not required to assign id to components

<zk>
    <!-- Tested with ZK 6.0.2 EE Eval -->

    <!-- A window, apply a custom SelectorComposer
        the selector composer can wire components/listen events
        by jquery-selector like way, it is not required to assign
        id to components -->
    <window apply="test.basic.mvc.BasicSelectorComposer">
        Input your name then click button:
        <textbox id="tb" />
        <button label="Say Hello" />
        <div height="50px" width="100px"
            style="margin: 10px; background-color: #A6C3EA;">
            <label />
        </div>
    </window>
</zk>

The Composer

BasicSelectorComposer.java

package test.basic.mvc;

import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.event.InputEvent;
import org.zkoss.zk.ui.event.MouseEvent;
import org.zkoss.zk.ui.select.SelectorComposer;
import org.zkoss.zk.ui.select.annotation.Listen;
import org.zkoss.zk.ui.select.annotation.Wire;
import org.zkoss.zul.Label;

/**
 * Tested with ZK 6.0.2 EE Eval
 *
 */
public class BasicSelectorComposer  extends SelectorComposer<Component> {

    private static final long serialVersionUID = -4527376919822049779L;

    private String _name;
    /**
     * Wire the Label with component type path "div > label"
     * this will wire the first label component under the first div component
     */
    @Wire("div > label")
    private Label lb;

    /**
     * Do some initiation task here
     */
    public void doAfterCompose (Component comp) throws Exception {
        super.doAfterCompose(comp);
        _name = "Default Name";
        lb.setValue("The message label");
    }

    /**
     * Listen to the onChange event of a component with id 'tb'
     * since wire/listen based on jquery-selector like way,
     * the function name can be changed
     */
    @Listen("onChange = #tb")
    public void doWhenTextboxChanged (InputEvent event) {
        _name = event.getValue();
        if (_name == null || _name.isEmpty()) {
            _name = "Default Name";
        }
    }

    /**
     * Listen to the onClick event of a button with label 'Say Hello'
     */
    @Listen("onClick = button[label='Say Hello']")
    public void afterButtonClicked (MouseEvent event) {
        // update label lb's value
        lb.setValue("Hello " + _name);
    }
}

The Result

initial page

after input name then click button

Reference

Envisage ZK 6: An Annotation Based Composer For MVC
http://books.zkoss.org/wiki/Small_Talks/2011/January/Envisage_ZK_6:_An_Annotation_Based_Composer_For_MVC

Download

Files at github:
basic_SelectorComposer.zul
https://github.com/benbai123/ZK_Practice/blob/master/Pattern/MVC/BasicMVC/WebContent/basic_SelectorComposer.zul

BasicSelectorComposer.java
https://github.com/benbai123/ZK_Practice/blob/master/Pattern/MVC/BasicMVC/src/test/basic/mvc/BasicSelectorComposer.java

ZK Basic MVC Pattern with GenericForwardComposer

Introduction

Regarding GenericForwardComposer, from official document:
A skeletal composer that you can extend and write intuitive onXxx$myid event handler methods with auto event forwarding and "auto-wired" accessible variable objects such as embedded objects, components, and external resolvable variables in a ZK zuml page; this class will add forward condition to the myid source component and forward source onXxx event received by the source myid component to the target onXxx$myid event (as defined in this composer) of the supervised target component; of course it will also registers onXxx$myid events to the supervised component and wire all accessible variable objects to this composer by calling setXxx() method or set xxx field value directly per the variable name.

This post practice the MVC pattern with GenericForwardComposer in ZK.

Note: As said in the title, only very basic part in this article.

Pre-request

(must)
Please refer to http://ben-bai.blogspot.tw/2012/06/zk-quick-start.html
for setting up the environment to run ZK Web Application.

(optional)
Regarding ID conflict and component namespace, please refer to
http://ben-bai.blogspot.tw/2012/11/zk-id-conflict-and-component-idspace.html

The ZUL Page

note: we usually use an component that is an Id Space
such like window to apply GenericForwardComposer
to prevent id conflict

basic_GenericForwardComposer.zul

<zk>
    <!-- Tested with ZK 6.0.2 EE Eval -->

    <!-- A window, apply a custom GenericForwardComposer
        we usually use an component that is an Id Space
        such like window to apply GenericForwardComposer
        so we can maintain the subset of components separately
        without the need to worry if there is any conflicts
        with other subsets
        -->
    <window apply="test.basic.mvc.BasicGenericForwardComposer">
        Input your name then click button:
        <textbox id="tb" />
        <button id="btn" label="Say Hello" />
        <div height="50px" width="100px"
            style="margin: 10px; background-color: #A6C3EA;">
            <label id="lb" />
        </div>
    </window>
</zk>

The Composer

BasicGenericForwardComposer.java

package test.basic.mvc;

import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.util.GenericForwardComposer;
import org.zkoss.zul.Label;
import org.zkoss.zk.ui.event.InputEvent;
import org.zkoss.zk.ui.event.MouseEvent;
/**
 * Tested with ZK 6.0.2 EE Eval
 *
 */
public class BasicGenericForwardComposer extends GenericForwardComposer<Component> {

    private static final long serialVersionUID = -4734679090474506071L;
    private String _name;
    /**
     * Wire the Label with id "lb" in zul page into composer
     * so you can control it by composer directly
     * 
     * The field name of declared component (Label here)
     * will be mapped to the component id in zul page automatically,
     * 
     * e.g., Label lb will be mapped to <label id="lb" ... />
     */
    private Label lb;

    /**
     * Official: With its GenericForwardComposer.doAfterCompose(Component) method,
     * the ZK components declared in mark up are wired with the component instances
     * declared in the controller for our manipulation, while the events fired are
     * automatically forwarded to this controller for event handling.
     * 
     * In short, you can do some initiation task here
     */
    public void doAfterCompose (Component comp) throws Exception {
        super.doAfterCompose(comp);
        _name = "Default Name";
        lb.setValue("The message label");
    }

    /**
     * Declare an event listener, listen to the "onChange" event
     * of the textbox with id "tb" in zul page,
     * 
     * the function name is combined by
     * event name (onChange) + "$" + component id in zul page(tb)
     * 
     * Do NOT need to wire it before listen its event
     * 
     * @param event the input event fired by the textbox
     * 
     */
    public void onChange$tb (InputEvent event) {
        _name = event.getValue();
        if (_name == null || _name.isEmpty()) {
            _name = "Default Name";
        }
    }

    /**
     * Listen to onClick event of the buttn with id "btn"
     * @param event
     */
    public void onClick$btn (MouseEvent event) {
        // update label lb's value
        lb.setValue("Hello " + _name);
    }
}

The Result

initial page

after input name then click button

References

Small Talks/2008/August/ZK MVC Made Easy
http://books.zkoss.org/wiki/Small_Talks/2008/August/ZK_MVC_Made_Easy

ZK Developer's Reference/UI Composing/ID Space
http://books.zkoss.org/wiki/ZK_Developer's_Reference/UI_Composing/ID_Space

Class GenericForwardComposer<T extends Component>
http://www.zkoss.org/javadoc/latest/zk/org/zkoss/zk/ui/util/GenericForwardComposer.html

Download

Full project at github
https://github.com/benbai123/ZK_Practice/tree/master/Pattern/MVC/BasicMVC

Use Custom Calculator in ZK Pivottable

Introduction

From official document:
In TabularPivotModel, Calculator is implemented by two segments: summarization and context. Given a Calculator as an aggregation operation, the context refers to the intermediate result we keep track of while iterating over the operands (accumulation), while summarization refers to the computation from the context to the end result.

This post practice use a custom calculator, say PercentageCalculator, to calculate a column field as the percentage value of other 2 column fields. Including some simple custom format style.

Pre-request

This post is based on Display Data in ZK Pivottable (http://ben-bai.blogspot.tw/2012/07/zk-pivottable-display-data-in-zk.html), please refer to it at first if you do not familiar with ZK Pivottable

The ZUL Page

index.zul

<zk>
    <!-- Tested with ZK 6.0.1 CE and ZK Pivottable 2.0.0 -->
    <!-- window, apply a SelectorComposer -->
    <window id="win" xmlns:w="client"
    apply="test.TestComposer">
    <!-- pivottable, get model and renderer from window's composer -->
    <pivottable id="pivottable" model="${win$composer.pivotModel}"
        pivotRenderer="${win$composer.pivotRenderer}" />
    </window>
</zk>

The Composer

TestComposer.java

package test;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Random;

import org.zkoss.pivot.PivotField;
import org.zkoss.pivot.PivotHeaderContext;
import org.zkoss.pivot.Pivottable;

import org.zkoss.pivot.impl.SimplePivotRenderer;
import org.zkoss.pivot.impl.TabularPivotField;
import org.zkoss.pivot.impl.TabularPivotModel;
import org.zkoss.pivot.impl.calc.Context;
import org.zkoss.pivot.impl.calc.ContextType;
import org.zkoss.pivot.impl.calc.ContextualCalculator;

import org.zkoss.zk.ui.select.SelectorComposer;

/**
* Tested with ZK 6.0.1 CE and ZK Pivottable 2.0.0
*
*/
@SuppressWarnings("rawtypes")
public class TestComposer extends SelectorComposer {
    /**
    * generated serial version UID
    */
    private static final long serialVersionUID = -2897873399288955635L;
    private TabularPivotModel _pivotModel;

    /**
    * Get pivottable's model
    * @return TabularPivotModel the pivottable's model
    * @throws Exception
    */
    public TabularPivotModel getPivotModel () throws Exception {
        if (_pivotModel == null) {
            _pivotModel = new TabularPivotModel(getData(), getColumns());
            
            // assign rows, the order matches to the level of row node field
            _pivotModel.setFieldType("Row_Level_001", PivotField.Type.ROW);
            
            // assign columns, the order matches to the level of column node field
            _pivotModel.setFieldType("Column_Level_001", PivotField.Type.COLUMN);
            
            // assign datas, the order matches to the order of data field
            _pivotModel.setFieldType("Data_Field_001", PivotField.Type.DATA);
            _pivotModel.setFieldType("Data_Field_002", PivotField.Type.DATA);
            _pivotModel.setFieldType("Data_Field_003", PivotField.Type.DATA);
            TabularPivotField field = _pivotModel.getField("Data_Field_003");
            _pivotModel.setFieldSummary(field, new PercentageCalculator());
        }
        return _pivotModel;
    }
    /**
     * Custom pivot renderer,
     * use custom format for column field "Data_Field_003"
     * @return
     */
    public SimplePivotRenderer getPivotRenderer () {
        return new SimplePivotRenderer() {
            public String renderCell(Number data, Pivottable table, 
                    PivotHeaderContext rowContext, PivotHeaderContext columnContext,
                    PivotField dataField) {
                DecimalFormat pf = new DecimalFormat("##,###.00 %");
                return "Data_Field_003".equals(dataField.getFieldName())?
                        pf.format(data.doubleValue()) : super.renderCell(data, table, rowContext, columnContext, dataField);
            }
        };
    }
    /**
    * prepare the data for pivottable's model
    * The order of object put into data list matches
    * the order of column name's order
    * @return
    * @throws Exception
    */
    public List<List<Object>> getData() throws Exception {
        List<List<Object>> result = new ArrayList<List<Object>>();
        Random r = new Random();
        
        for (int i = 0; i < 10000; i++) {
            List<Object> data = new ArrayList<Object>();

            Double dataOne = r.nextDouble() * 10000.0 + 1.0;
            Double dataTwo = r.nextDouble() * 10000.0 + 1.0;
            data.add("Row_Level_001 - " + (r.nextInt(10) + 1));
            data.add("Column_Level_001 - " + (r.nextInt(10) + 1));

            data.add(dataOne);
            data.add(dataTwo);
            data.add(new DataForPercentage(dataOne, dataTwo));
            result.add(data);
        }
        return result;
    }
    /**
    * prepare columns name for pivottable's model
    * @return
    */
    public List<String> getColumns() {
        return Arrays.asList(new String[]{
            "Row_Level_001",
            "Column_Level_001",
            "Data_Field_001", "Data_Field_002", "Data_Field_003"
        });
    }

    /**
     * The context for PercentageCalculator
     *
     */
    public static class PercentageContext implements Context<PercentageContext> {

        private Double _sumOfDataOne = 0.0;
        private Double _sumOfDataTwo = 0.0;

        // what to do when iterating over raw data.
        @Override
        public void add(Object item) {
            if (item instanceof DataForPercentage) {
                DataForPercentage _item = (DataForPercentage)item;
                _sumOfDataOne += _item.getDataOne();
                _sumOfDataTwo += _item.getDataTwo();
            } else {
                _sumOfDataOne += (Double)item;
                _sumOfDataTwo += (Double)item;
            }
        }
        // what to do when merging from contexts of a partition of its raw data set.
        @Override
        public void merge(PercentageContext ctx) {
            _sumOfDataOne += ctx.getSumOfDataOne();
            _sumOfDataTwo += ctx.getSumOfDataTwo();
        }
        public Double getSumOfDataOne () {
            return _sumOfDataOne;
        }
        public Double getSumOfDataTwo () {
            return _sumOfDataTwo;
        }

        public Double getPercentage() {
            return getSumOfDataOne() / getSumOfDataTwo();
        }

        public static final ContextType<PercentageContext> CONTEXT_TYPE = 
                new ContextType<PercentageContext>() {
            // ContextType has the responsibility as a Context factory
            @Override
            public PercentageContext create() {
                return new PercentageContext();
            }
        };
    }

    /**
     * The custom calculator to calculate percentage value
     * based on the given data
     *
     */
    public static class PercentageCalculator implements ContextualCalculator<PercentageContext> {

        public static final PercentageCalculator INSTANCE = new PercentageCalculator();
        private PercentageCalculator() {}

        // specify the context type
        @Override
        public ContextType<PercentageContext> getContextType() {
            return PercentageContext.CONTEXT_TYPE;
        }
        // summarize the end result from the context
        @Override
        public Number getResult(PercentageContext context) {
            return context.getPercentage();
        }
        @Override
        public String getLabel() {
            return "Percentage";
        }
        @Override
        public String getLabelKey() {
            return "percentage";
        }
    }

    /**
     * Store the data for calculating percentage value
     *
     */
    private class DataForPercentage {
        private Double _dataOne;
        private Double _dataTwo;
        public DataForPercentage(Double dataOne, Double dataTwo) {
            _dataOne = dataOne;
            _dataTwo = dataTwo;
        }
        public Double getDataOne () {
            return _dataOne;
        }
        public Double getDataTwo () {
            return _dataTwo;
        }
    }

}

The Result

Data_Field_003 = Data_Field_001 / Data_Field_002

Reference

ZK Pivottable Essentials/Customization/Custom Calculator
http://books.zkoss.org/wiki/ZK%20Pivottable%20Essentials/Customization/Custom%20Calculator

ZK Pivottable Essentials/Customization/Data Rendering
http://books.zkoss.org/wiki/ZK%20Pivottable%20Essentials/Customization/Data%20Rendering


Download

Full project at github
https://github.com/benbai123/ZK_Practice/tree/master/Components/projects/Addon_Practice/PivottableTest/UseCustomCalculator

Exporting Data to Excel by Apache POI

Introduction

From official site:
The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008).

This post do some practice of Apache POI, including set value to cell, style cell with Date format, Number format, align and font color, merge a range of cells, use Formula to calculate value, evaluate Formula result by FormulaEvaluator.

Required jar

poi-3.8-20120326.jar - http://poi.apache.org/download.html

The Program

POITest.java

Export some Fake data to excel file

package test.poi;

import java.io.FileOutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Random;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class POITest {
    private static SimpleDateFormat _shortDateformatter = new SimpleDateFormat("yyyy-MM-dd");
    // the style for Date column
    private static CellStyle _dateCellStyle;
    // the style for Money cells
    private static CellStyle _moneyCells;
    // the style for Money cells with negative value
    private static CellStyle _moneyCellsNegative;
    // evaluator to evaluate formula cell
    private static FormulaEvaluator _evaluator;

    public static void main (String[] args) {
        try {
            // create a new workbook
            Workbook wb = new HSSFWorkbook();
            // create a sheet with name "Balance"
            Sheet sheet = wb.createSheet("Balance");
            // get fake datas
            List<DateInOut> datas = generateFakeData();

            // add title row
            addTitle(sheet);
            // create cell styles as needed
            createCellStylesAndEvaluator(wb);
            // add datas
            addDatas(sheet, datas);
            
            // adjust column width
            autosizeColumns(sheet);

            // save workbook as .xls file
            saveBalanceReport(wb, "workbook.xls");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private static List<DateInOut> generateFakeData () throws ParseException {
        Calendar c = Calendar.getInstance();
        Random rand = new Random();
        List<DateInOut> datas = new ArrayList<DateInOut>();
        // set start date
        c.setTime(_shortDateformatter.parse("2012-01-01"));
        // generate fake datas within 200 days
        while (c.get(Calendar.DAY_OF_YEAR) < 200) {
            DateInOut dio = new DateInOut(c.getTime());
            // add 1-5 records
            for (int i = 0; i < (rand.nextInt(5) + 1); i++) {
                dio.addInOut(rand.nextInt(1000), rand.nextInt(1000));
            }
            datas.add(dio);
            // increase date
            c.add(Calendar.DAY_OF_YEAR, rand.nextInt(3) + 1);
        }
        return datas;
    }
    // add titles
    private static void addTitle (Sheet sheet) {
        // create row (3rd row)
        Row row = sheet.createRow(2);
        // add value to 3rd cell
        row.createCell(2).setCellValue("Ben Bai's Balance (not real)");
        // merge cells
        sheet.addMergedRegion(// first row (0-based), last row (0-based), first column (0-based), last column (0-based)
            new CellRangeAddress(2, 2, 2, 4));
        // go to 4th row
        row = sheet.createRow(3);
        // add values to cells
        row.createCell(1).setCellValue("Date");
        row.createCell(2).setCellValue("Income");
        row.createCell(3).setCellValue("Expenditure");
        row.createCell(4).setCellValue("Balance");
        row.createCell(6).setCellValue("Grand Total of Balance");
    }
    // create style for Date cell
    private static void createCellStylesAndEvaluator(Workbook wb) {
        // CreationHelper for create CellStyle
        CreationHelper createHelper = wb.getCreationHelper();
        _dateCellStyle = wb.createCellStyle();
        // add date format
        _dateCellStyle.setDataFormat(
            createHelper.createDataFormat().getFormat("yyyy-MM-dd"));
        // vertical align top
        _dateCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);

        // money style ( >= 0)
        _moneyCells = wb.createCellStyle();
        _moneyCells.setDataFormat(
                createHelper.createDataFormat().getFormat("$__##,##0.##"));
        // money style ( < 0)
        Font font = wb.createFont();
        font.setColor(Font.COLOR_RED);
        _moneyCellsNegative = wb.createCellStyle();
        _moneyCellsNegative.setDataFormat(
                createHelper.createDataFormat().getFormat("$__##,##0.##"));
        _moneyCellsNegative.setFont(font);

        _evaluator = wb.getCreationHelper().createFormulaEvaluator();
    }
    private static void addDatas (Sheet sheet, List<DateInOut> datas) {
        int rowIdx = 4; // start from 5th row
        Row firstDataRow = null;
        for (DateInOut dio : datas) {
            Date date = dio.getDate();
            List<InOut> inoutList = dio.getInOut();
            int size = inoutList.size();

            // merge "Date" column as needed
            sheet.addMergedRegion( // first row (0-based), last row (0-based), first column (0-based), last column (0-based)
                new CellRangeAddress(rowIdx, rowIdx + size - 1, 1, 1));
            Row row = sheet.createRow(rowIdx);
            // keep first row for later use
            if (firstDataRow == null)
                firstDataRow = row;
            // set date value
            Cell c = row.createCell(1);
            c.setCellStyle(_dateCellStyle);
            c.setCellValue(date);

            for (InOut io : inoutList) {
                // income and expenditure
                Cell cell = row.createCell(2);
                cell.setCellValue(io.getIncome());
                setNumericStyle(cell, false);

                cell = row.createCell(3);
                cell.setCellValue(io.getExpenditure());
                setNumericStyle(cell, false);

                // formula for calculate balance of one data row
                String formula = "C"+(rowIdx+1) + "-D"+(rowIdx+1);
                cell = row.createCell(4);
                cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                cell.setCellFormula(formula);
                setNumericStyle(cell, true);
                
                // move to next row
                rowIdx++;
                row = sheet.createRow(rowIdx);
            }
            // add two empty column before next date
            rowIdx += 2;
        }
        // formula for calculate grand total of balance column
        String formula = "SUM(E5:E"+(rowIdx)+")";
        Cell gtb = firstDataRow.createCell(6);
        gtb.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        gtb.setCellFormula(formula);
        setNumericStyle(gtb, true);
    }
    // set style to numeric cell
    private static void setNumericStyle (Cell cell, boolean isFormula) {
        double value = isFormula? getFormulaCellValue(cell) : cell.getNumericCellValue();
        if (value >= 0) {
            cell.setCellStyle(_moneyCells);
        } else {
            cell.setCellStyle(_moneyCellsNegative);
        }
    }
    // evaluate formula cell value
    private static double getFormulaCellValue (Cell cell) {
        _evaluator.evaluateFormulaCell(cell);
        return cell.getNumericCellValue();
    }
    // adjust column width
    private static void autosizeColumns (Sheet sheet) {
        // auto size not work with date
        sheet.setColumnWidth(1, 3000);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);
        sheet.autoSizeColumn(6);
    }
    private static void saveBalanceReport (Workbook wb, String fileName) throws Exception {
        // create a new file
        FileOutputStream out = new FileOutputStream(fileName);
        // Write out the workbook
        wb.write(out);
        out.close();
    }
}
// one day with several income/expenditure pare
class DateInOut {
    private Date _date;
    private List<InOut> _inOut;
    public DateInOut (Date date) {
        _date = date;
        _inOut = new ArrayList<InOut>();
    }
    public void addInOut (int income, int expenditure) {
        _inOut.add(new InOut(income, expenditure));
    }

    public Date getDate () {
        return _date;
    }
    public List<InOut> getInOut () {
        return _inOut;
    }
}
// income/expenditure pare
class InOut {
    private int _income;
    private int _expenditure;
    public InOut (int income, int expenditure) {
        _income = income;
        _expenditure = expenditure;
    }

    public int getIncome () {
        return _income;
    }
    public int getExpenditure () {
        return _expenditure;
    }
}

The Result

References

Busy Developers' Guide to HSSF and XSSF Features
http://poi.apache.org/spreadsheet/quick-guide.html#WorkingWithFonts

Formula Evaluation
http://poi.apache.org/spreadsheet/eval.html

POI API Documentation
http://poi.apache.org/apidocs/overview-summary.html


Download

Full project at github
https://github.com/benbai123/JSP_Servlet_Practice/tree/master/Practice/JAVA/POI/POITest

Get Address Information by IP

Introduction

There are many IP to Address services such like http://www.hostip.info/, http://ip-lookup.net/, http://www.iplocation.net/ and so on, this post will talking about how to get address information by given IP via the API provided by Hostip.info.

Prerequest

The sample of this post is based on http://ben-bai.blogspot.tw/2012/02/java-practice-javanet-practice-use.html

Required jar files:


 json-lib: http://sourceforge.net/projects/json-lib/files/json-lib/
 commons-beanutils-1.8.3.jar http://commons.apache.org/beanutils/download_beanutils.cgi
 commons-collections-3.2.1.jar http://commons.apache.org/collections/download_collections.cgi
 commons-lang-2.5.jar http://commons.apache.org/lang/download_lang.cgi
 commons-logging-1.1.1.jar http://commons.apache.org/logging/download_logging.cgi
 ezmorph-1.0.6.jar http://sourceforge.net/projects/ezmorph/files/ezmorph/



The Program

IPLookupService.java

Get position by given IP and then get formatted address by given lat/lng

package test;

import java.io.InputStreamReader;

import net.sf.json.JSONObject;

/**
 * required jar files:
 * json-lib: http://sourceforge.net/projects/json-lib/files/json-lib/
 * commons-beanutils-1.8.3.jar http://commons.apache.org/beanutils/download_beanutils.cgi
 * commons-collections-3.2.1.jar http://commons.apache.org/collections/download_collections.cgi
 * commons-lang-2.5.jar http://commons.apache.org/lang/download_lang.cgi
 * commons-logging-1.1.1.jar http://commons.apache.org/logging/download_logging.cgi
 * ezmorph-1.0.6.jar http://sourceforge.net/projects/ezmorph/files/ezmorph/
 *
 */
public class IPLookupService {
    public static void main (String[] args) {
        AddressInfo info = ipToLocation("98.76.54.32");
        System.out.println(info.getCountryName());
        System.out.println(info.getCountryCode());
        System.out.println(info.getCity());
        System.out.println(info.getLat());
        System.out.println(info.getLng());
        System.out.println(info.getFormattedAddress());
    }
    /**
     * return country, city, address by given IP
     * @param ip
     * @return
     */
    public static AddressInfo ipToLocation (String ip) {
        String country;
        AddressInfo info = null;
        double lat;
        double lng;
        StringBuilder sb = getResponse(
                "http://api.hostip.info/get_json.php?ip=" + ip + "&position=true");
        JSONObject jobj = JSONObject.fromObject(sb.toString());
        lat = jobj.getDouble("lat");
        lng = jobj.getDouble("lng");
        info = new AddressInfo(jobj.getString("country_name"), jobj.getString("country_code"),
                jobj.getString("city"), lat, lng,
                GeocodeService.getAddressByLatLng(lat, lng, "ja"));
        return info;
    }
    public static StringBuilder getResponse(String path){
        try {
            java.net.URL url = new java.net.URL(path);
            java.net.HttpURLConnection uc = (java.net.HttpURLConnection) url.openConnection();
            uc.setRequestProperty("User-agent", "Mozilla/5.0");

            uc.setRequestProperty("Accept-Charset", "UTF-8"); // encoding
            uc.setReadTimeout(30000);// timeout limit
            uc.connect();// connect
            int status = uc.getResponseCode();

            switch (status) {
                case java.net.HttpURLConnection.HTTP_GATEWAY_TIMEOUT://504 timeout
                    break;
                case java.net.HttpURLConnection.HTTP_FORBIDDEN://403 forbidden
                    break;
                case java.net.HttpURLConnection.HTTP_INTERNAL_ERROR://500 server error
                    break;
                case java.net.HttpURLConnection.HTTP_NOT_FOUND://404 not exist
                    break;
                case java.net.HttpURLConnection.HTTP_OK: // ok
                    InputStreamReader reader = new InputStreamReader(uc.getInputStream(), "UTF-8");

                    int ch;
                    StringBuilder sb = new StringBuilder("");
                    while((ch = reader.read())!= -1){
                        sb.append((char)ch);
                    }
                    return sb;
            }

        } catch (java.net.MalformedURLException e) { // invalid address format
            e.printStackTrace();
        } catch (java.io.IOException e) { // connection broken
            e.printStackTrace();
        }
        return null;
    }
}
class AddressInfo {
    private String _countryName;
    private String _countryCode;
    private String _city;
    private double _lat;
    private double _lng;
    private String _formattedAddress;
    AddressInfo (String countryName, String countryCode,
        String city, double lat, double lng, String formattedAddress) {
        _countryName = countryName;
        _countryCode = countryCode;
        _city = city;
        _lat = lat;
        _lng = lng;
        _formattedAddress = formattedAddress;
    }
    public String getCountryName () {
        return _countryName;
    }
    public String getCountryCode () {
        return _countryCode;
    }
    public String getCity () {
        return _city;
    }
    public double getLat () {
        return _lat;
    }
    public double getLng () {
        return _lng;
    }
    public String getFormattedAddress () {
        return _formattedAddress;
    }
}

function added to GeocodeService.java

The new function returns formatted address by given lat/lng

    /**
     * return formatted address by given lat/lng
     * @param lat
     * @param lng
     * @param lang
     * @return
     */
    public static String getAddressByLatLng (double lat, double lng, String lang) {
        StringBuilder sb = getResponse("http://maps.googleapis.com/maps/api/geocode/json?"
                + "latlng="+lat+","+lng
                + "&sensor=true&language="+lang);
        JSONObject jobj = JSONObject.fromObject(sb.toString());
        return jobj.getJSONArray("results").getJSONObject(0).getString("formatted_address");
    }

The Result

http://maps.googleapis.com/maps/api/geocode/json?latlng=33.7629,-84.4226&sensor=true&language=ja
UNITED STATES
US
Atlanta, GA
33.7629
-84.4226
1068 Simpson Rd NW, アトランタ ジョージア 30314 アメリカ合衆国

Reference

http://stackoverflow.com/questions/2362413/any-reliable-api-available-to-determine-users-city-and-country-from-ip-address


Download

sample code at github
https://github.com/benbai123/JSP_Servlet_Practice/blob/master/Practice/JAVA/Net/src/test/IPLookupService.java
https://github.com/benbai123/JSP_Servlet_Practice/blob/master/Practice/JAVA/Net/src/test/GeocodeService.java

Generate Report by ZK Jasperreport Programmatically

Introduction

From the Related Posts below:

JasperReports is a powerful reporting engine which supports various formats including pdf, html, rtf, xls, doc etc, ZK has integrated JasperReports to leverage these powers for ease of reporting.

This post is about how to generate report programmtically, you can also find some different way in the Related Post below.

The Environment

Basic ZK 5.0.11 EE eval jars plus commons-digester-2.1.jar (can be found at http://archive.apache.org/dist/commons/digester/binaries/) and commons-beanutils-1.8.3.jar (downloaded from http://commons.apache.org/beanutils/download_beanutils.cgi, for generate template)

Note: the version of commons-digester should be 2.x or you may see NoClassDefFoundError with respect to org.apache.commons.digester.Rule

The Code

InfoResource.java

This class do two things (this is not good, but just for this post...)

1. call JasperCompileManager.compileReportToFile to compile the template.jrxml to template.jasper (the template used to generate report)

2. provide data to report

package test.report;

import java.util.ArrayList;
import java.util.List;

import net.sf.jasperreports.engine.JRDataSource;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRField;
import net.sf.jasperreports.engine.JasperCompileManager;

public class InfoResource implements JRDataSource {
    private List<FoodInfo> dataList = new ArrayList<FoodInfo>();
    private int index = -1;
    public static void main(String args[]) {
        generateTemplateSrc();
    }
    /**
     * compile the template.jrxml to generate template.jasper
     */
    public static void generateTemplateSrc() {
        try {
            JasperCompileManager.compileReportToFile("WebContent/WEB-INF/template/template.jrxml", "WebContent/WEB-INF/template/template.jasper");
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

    public InfoResource()
    {
        // datas to report
        dataList.add(new FoodInfo("pizza", 1000, 200));
        dataList.add(new FoodInfo("ham",  500, 300));
        dataList.add(new FoodInfo("banana", 200, 20));
    }

    /**
     * method defined in interface JRDataSource,
     * return whether has next data
     */
    public boolean next() throws JRException
    {
        index++;
        return (index < dataList.size());
    }
    /**
     * method defined in interface JRDataSource,
     * return value based on field name
     */
    public Object getFieldValue(JRField field) throws JRException
    {
        Object value = null;
        
        String fieldName = field.getName();
        
        if ("product_name".equals(fieldName))
        {
            value = dataList.get(index).getName();
        }
        else if ("cal".equals(fieldName))
        {
            value = dataList.get(index).getCal();
        }
        else if ("price".equals(fieldName))
        {
            value = dataList.get(index).getPrice();
        }
        
        return value;
    }
}
/**
 * the data class
 *
 */
class FoodInfo {
    private String name;
    private int cal;
    private int price;
    
    public FoodInfo() {
        
    }
    public FoodInfo(String name, int cal, int price) {
        this.name = name;
        this.cal = cal;
        this.price = price;
    }

    public void setName(String name) {
        this.name = name;
    }
    public String getName() {
        return name;
    }
    public void setCal(int cal) {
        this.cal = cal;
    }
    public int getCal() {
        return cal;
    }
    public void setPrice(int price) {
        this.price = price;
    }
    public int getPrice() {
        return price;
    }
}

template.jrxml

This is the human readable xml file for design the report template, run InfoResource.java as java application will compile it to template file template.jasper

<?xml version="1.0"?>
<!DOCTYPE jasperReport
  PUBLIC "-//JasperReports//DTD Report Design//EN"
  "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
 
<jasperReport name="report1">
    <!-- parameters are from test.zul --> 
    <parameter name="reportTitle" class="java.lang.String"/>
    <parameter name="author" class="java.lang.String"/>
    <parameter name="startDate" class="java.lang.String"/>
    <parameter name="image" class="java.io.File"/>
 
     <parameter name="C1" class="java.lang.String">
        <defaultValueExpression>
            new java.lang.String("")
        </defaultValueExpression>
    </parameter>
 
    <parameter name="C2" class="java.lang.String">
        <defaultValueExpression>
            new java.lang.String("")
        </defaultValueExpression>
    </parameter>
 
    <parameter name="C3" class="java.lang.String">
        <defaultValueExpression>
            new java.lang.String("")
        </defaultValueExpression>
    </parameter>
 
     <!-- define fields -->
    <field name="product_name" class="java.lang.String"/>
    <field name="cal" class="java.lang.Integer"/>
    <field name="price" class="java.lang.Integer"/>
 
    <title>
        <band height="120">
            <textField>
                <reportElement x="0" y="0" width="225" height="45"/>
                <textElement textAlignment="Center">
                    <font size="18"/>
                </textElement>
                <textFieldExpression class="java.lang.String">
                    <![CDATA[$P{reportTitle}]]>
                </textFieldExpression>
            </textField>
            <textField>
                <reportElement x="225" y="0" width="225" height="30"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression class="java.lang.String">
                    <![CDATA["Run by: " + $P{author}
                        + " on " + $P{startDate}]]>
                </textFieldExpression>
            </textField>
            <image hyperlinkType="None">  
                <reportElement x="0" y="50" width="50" height="50"/>  
                <imageExpression class="java.io.File">
                    <![CDATA[$P{image}]]>  
                </imageExpression>
            </image>  
        </band>
    </title>
 
 
    <columnHeader>
        <band height="15">

            <textField>
                <reportElement x="0" y="0" width="150" height="15"/>
                <box>
                    <pen lineWidth="2" lineColor="#000000"/>
                    <topPen lineWidth="2" lineColor="#000000"/>
                    <leftPen lineWidth="2" lineColor="#000000"/>
                    <bottomPen lineWidth="0" lineColor="#000000"/>
                    <rightPen lineWidth="0" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" />
                <textFieldExpression class="java.lang.String">
                    <![CDATA[$P{C1}]]>
                </textFieldExpression>
            </textField>
 
            <textField>
                <reportElement x="150" y="0" width="150" height="15"/>
                <box>
                    <pen lineWidth="2" lineColor="#000000"/>
                    <topPen lineWidth="2" lineColor="#000000"/>
                    <leftPen lineWidth="0" lineColor="#000000"/>
                    <bottomPen lineWidth="0" lineColor="#000000"/>
                    <rightPen lineWidth="0" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" />
              <textFieldExpression class="java.lang.String">
                    <![CDATA[$P{C2}]]>
                </textFieldExpression>
            </textField>
 
            <textField>
                <reportElement x="300" y="0" width="150" height="15"/>
                <box>
                    <pen lineWidth="2" lineColor="#000000"/>
                    <topPen lineWidth="2" lineColor="#000000"/>
                    <leftPen lineWidth="0" lineColor="#000000"/>
                    <bottomPen lineWidth="0" lineColor="#000000"/>
                    <rightPen lineWidth="2" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" />
              <textFieldExpression class="java.lang.String">
                    <![CDATA[$P{C3}]]>
                </textFieldExpression>
            </textField>
 
            </band>
    </columnHeader>
 
    <!-- output datas, for more details, please refer to the official document -->
    <detail>
        <band height="15">
            <textField>
                <reportElement x="0" y="0" width="150" height="15" mode="Opaque" forecolor="#ffffff" backcolor="#FF0000"/>
                <box>
                    <pen lineWidth="2" lineColor="#000000"/>
                    <topPen lineWidth="2" lineColor="#000000"/>
                    <leftPen lineWidth="2" lineColor="#000000"/>
                    <bottomPen lineWidth="2" lineColor="#000000"/>
                    <rightPen lineWidth="2" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" />
                <textFieldExpression class="java.lang.String">
                    <![CDATA[$F{product_name}]]>
                </textFieldExpression>
            </textField>
 
            <textField>
                <reportElement x="150" y="0" width="150" height="15" mode="Opaque" forecolor="#ffffff" backcolor="#FF0000"/>
                <box>
                    <pen lineWidth="2" lineColor="#000000"/>
                    <topPen lineWidth="2" lineColor="#000000"/>
                    <leftPen lineWidth="2" lineColor="#000000"/>
                    <bottomPen lineWidth="2" lineColor="#000000"/>
                    <rightPen lineWidth="2" lineColor="#000000"/>
                </box>
                <textElement textAlignment="Center" />
                <textFieldExpression class="java.lang.Integer">
                    <![CDATA[$F{cal}]]>
                </textFieldExpression>
            </textField>
 
            <textField>
                <reportElement x="300" y="0" width="150" height="15" mode="Opaque" forecolor="#ffffff" backcolor="#FF0000"/>
                <box>
                    <pen lineWidth="2" lineColor="#000000"/>
                    <topPen lineWidth="2" lineColor="#000000"/>
                    <leftPen lineWidth="2" lineColor="#000000"/>
                    <bottomPen lineWidth="2" lineColor="#000000"/>
                    <rightPen lineWidth="2" lineColor="#000000"/>
                </box> 
                <textElement textAlignment="Center" />
                <textFieldExpression class="java.lang.Integer">
                    <![CDATA[$F{price}]]>
                </textFieldExpression>
            </textField>
 
        </band>
    </detail>
 
</jasperReport> 

test.zul

The zul page for report generation

<zk>
    <vbox>
        <hbox>
            <!-- choose the file type for export report -->
            Choose a File Type :
            <listbox id="format" mold="select" >
                <listitem label="PDF" value="pdf" selected="true" />
                <listitem label="HTML" value="html" />
                <listitem label="Word (RTF)" value="rtf" />
                <listitem label="Excel" value="xls" />
                <listitem label="Excel (JXL)" value="jxl" />
                <listitem label="CSV" value="csv" />
                <listitem label="OpenOffice (ODT)" value="odt" unless="false" />
            </listbox>
            <button label="Get Report">
                <attribute name="onClick">
                    String title = titleValue.getValue();
                    String author = authorValue.getValue();
                    Date date = new Date();
                    showReport(title, author, date);
                </attribute>
            </button>
        </hbox>
        <hbox>
            <!-- input field to set title and author param -->
            Input Title: <textbox id="titleValue" />
            Input Author: <textbox id="authorValue" />
        </hbox>
        <hbox>
            <radiogroup id="rgroup">
                <hlayout>
                    <!-- for choose image file using in report -->
                    <radio label="circle" value="testimg.png" selected="true"/>
                    <radio label="cube" value="testimg2.png"/>
                </hlayout>
            </radiogroup>
        </hbox>
        <!-- the report component, used to generate report -->
        <jasperreport id="report" width="1024px" height="768px" />
    </vbox>
    <zscript><![CDATA[
        import test.report.InfoResource;
        import java.io.*;
        
        void showReport(String title, String author, Date date) {
            String realPath = Executions.getCurrent().getDesktop().getSession().getWebApp().getRealPath("/");
            File img = new File(realPath+File.separator+"WEB-INF"+File.separator+"images"+File.separator+rgroup.getSelectedItem().getValue());
            //Preparing parameters
            Map parameters = new HashMap();
            
            // set parameters for generate report
            parameters.put("reportTitle", title);
            parameters.put("author", author);
            parameters.put("startDate", date.toString());
            parameters.put("image", img);
            parameters.put("C1", "product");
            parameters.put("C2", "total cal");
            parameters.put("C3", "product price");

            // get report template
            report.setSrc("/WEB-INF/template/template.jasper");
            // set params
            report.setParameters(parameters);
            // set data (InfoResource.java)
            report.setDatasource(new InfoResource());
            // set report type (pdf, xls, ...)
            report.setType((String) format.getSelectedItem().getValue());
        }
        ]]>
    </zscript>
</zk>

The Result

view demo online
http://screencast.com/t/3aK8Kb2BTU

Related Post

Create a Report with ZK using iReport and JasperReports
http://books.zkoss.org/wiki/Small_Talks/2012/April/Create_a_Report_with_ZK_using_iReport_and_JasperReports

References

ZK Component Reference/Diagrams and Reports/Jasperreport
http://books.zkoss.org/wiki/ZK_Component_Reference/Diagrams_and_Reports/Jasperreport

Jasperreports tutorial
http://community.jaspersoft.com/wiki/jasperreports-library-tutorial

Downloads

full project
https://github.com/benbai123/ZK_Practice/tree/master/Components/projects/Integrated_Components/jasperreportTest

demo flash
https://github.com/benbai123/ZK_Practice/blob/master/Components/demos/Integrated_Components/jasper_report_test.swf

personal sql query notes

simple note

Join 3 tables and use subquery as condition

assume RoleID=2 denotes banned user and Deleted='1' denotes deleted comment, this query will return all deleted comments that posted by banned user

SELECT c.Name, b.Name, a.DateDeleted FROM forum.comment_table AS a
    LEFT JOIN forum.user_table AS b
        ON a.DeleteUserID = b.UserID
    LEFT JOIN forum.discussion_table AS c
        ON a.DiscussionID = c.DiscussionID
    WHERE a.Deleted='1'
        AND a.DateDeleted>='2012-09-01 00:00:00'
        AND a.DateDeleted<='2012-09-30 23:59:59'
        AND a.AuthUserID in (
            SELECT d.UserID FROM forum.user_table AS d
                WHERE d.RoleID=2)
    ORDER BY a.DateDeleted DESC;

Create table if not exists

CREATE TABLE IF NOT EXISTS test_table ( id BIGINT NOT NULL IDENTITY,
    firstName VARCHAR(32),
    lastName VARCHAR(32));

Use Embedded Jetty to Create 'Executable' Web App

Introduction

From Official Site:
Jetty provides an HTTP server, HTTP client, and javax.servlet container. These components are open source and available for commercial use and distribution.

Jetty is used in a wide variety of projects and products. Jetty can be embedded in devices, tools, frameworks, application servers, and clusters. See the Jetty Powered page for more uses of Jetty.

----

This post will talking about how to use embedded jetty to create an 'executable' web app --
double click on a batch file,
then the server started,
and then the browser is opened to a web page in that web app

You can find the binary distribution from official site (http://dist.codehaus.org/jetty/), all required jars are in it.

The Program

JettyClass.java

Config a jetty server and start it

package jetty;


import org.mortbay.jetty.Server;
import org.mortbay.jetty.webapp.WebAppContext;

/**
 * start an embedded jetty server
 *
 */
public class JettyClass {
    public static void main(String[] args) throws Exception {
        // use 8080 port
        Server server = new Server(8080);

        WebAppContext context = new WebAppContext();

        // use relative path, start from project root
        context.setResourceBase("src/webapp/content");
        // page address: http://localhost:8080/EmbeddingJettyTest
        context.setContextPath("/EmbeddingJettyTest");
        context.setParentLoaderPriority(true);

        server.setHandler(context);

        server.start();
        server.join();
    }
}

TestListener.java

Just print some message when server startup to make sure web.xml works fine

package test;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

/**
 * print message when server start,
 * to make sure web.xml works fine
 *
 */
public class TestListener implements ServletContextListener {

    @Override
    public void contextDestroyed(ServletContextEvent arg0) {
        // TODO Auto-generated method stub
        
    }

    @Override
    public void contextInitialized(ServletContextEvent arg0) {
        // TODO Auto-generated method stub
        System.out.println("contextInitialized");
    }

}

web.xml

Define the TestListener in it

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
    version="3.0">
    <!-- add a listener to make sure this config file works -->
    <listener>
        <listener-class>test.TestListener</listener-class>
    </listener>
</web-app>

index.jsp

Use some scriptlet to output some message to make sure jsp works fine

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<html>
    <head>
        <meta http-equiv="Content-Type"
            content="text/html; charset=UTF-8">
    </head>
    <body>
        <!-- write some scriptlet to make sure jsp works -->
        <% String msg = "Hello World"; %>
        <%= msg %>
    </body>
</html>

start.bat

Execute the JettyClass and open browser (rem is keyword for comment)

rem /* define library folder */
set lib=.\lib
rem /* folder bin as classpath */
set NEWPATH=bin
rem /* for each *.jar in library folder, append it after classpath */
for %%1 in (%lib%\*.jar) do call :concat %%1

rem /* start chrome before server start */
start chrome http://localhost:8080/EmbeddingJettyTest/
rem /* Execute JettyClass */
java -Xmx128m -classpath %NEWPATH% jetty.JettyClass
goto :eof

rem /* sub function to append string */
:concat
set NEWPATH=%NEWPATH%;%1
goto :eof

The Result

View the demo flash on line
http://screencast.com/t/SAgNUwtilf4

Reference

Embedding Jetty
https://docs.codehaus.org/display/JETTY/Embedding+Jetty

batch file string concatenation
http://stackoverflow.com/questions/2027070/batch-file-string-concatenation

Download

Demo flash
https://github.com/benbai123/JSP_Servlet_Practice/blob/master/demo_src/Server/Jetty/EmbeddedJettyTest.swf

Full project is at github
https://github.com/benbai123/JSP_Servlet_Practice/tree/master/Practice/WebServer/JettyTest

Embedding HSQLDB in JAVA Application Project

Introduction

Sometimes we may want to test some simple database operation, or let the database portable within our application. In these cases, we can try to use the embedding hsqldb.

Pre-request

The only required jar is hsqldb.jar, you can get the latest version from official site http://hsqldb.org/.

The Program

Test.java:

Start a hsqldb server, execute some sql and show all data in db.

package test;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;

import org.hsqldb.Server;

public class Test {

    private static Connection _connection = null;
    /**
     * create a writer, log hsqldb server info to a file
     * @param logFileName String, the log file name
     * @param append boolean, true: append new content; false: clear old content 
     * @param autoFlush boolean, true: auto flush; false: not auto flush
     * @return PrintWriter
     * @throws IOException
     */
    private static PrintWriter createLogWriter (String logFileName,
        boolean append, boolean autoFlush)
        throws IOException {
        File f = new File(logFileName);

        // create file if not exists
        if (!f.exists()) {
            String logFilePath = f.getAbsolutePath();

            // create parent folders
            File folder = new File(logFilePath.substring(0, logFilePath.indexOf(logFileName)));
            folder.mkdirs();

            // create file
            f.createNewFile();
        }
        FileWriter fw = new FileWriter(f, append);
        return new PrintWriter(fw, autoFlush);
    }
    /**
     * get a db connection
     * @param driverName
     * @param dbUrl
     * @param userName
     * @param password
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    private static Connection getConnection (String dbUrl,
        String userName, String password)
        throws SQLException, ClassNotFoundException {
        if (_connection == null
            || _connection.isClosed()) {
            // Getting a connection to the newly started database
            Class.forName("org.hsqldb.jdbcDriver");
            // Default user of the HSQLDB is 'sa'
            // with an empty password
            return DriverManager.getConnection(dbUrl
                ,userName , password);
        } else {
            return _connection;
        }
    }
    public static void main(String[] args)
        throws ClassNotFoundException, SQLException, IOException {
        String dbName = "testName";
        String path = "testPath";

        
        PrintWriter logWriter =
            createLogWriter(dbName+"_"+path+".log",
                            true, true);
        // start the hsqldb server
        Server hsqlServer = HSQLDBClass.startServer(dbName, path, logWriter);
        Connection connection =
            getConnection("jdbc:hsqldb:hsql://localhost/"+dbName, "sa", "");
        try {

            Random rand = new Random();
            Statement stmt = connection.createStatement();
            // test several SQL operation
            // create table if not exists
            stmt.execute(
                "CREATE TABLE IF NOT EXISTS testTable ( id BIGINT NOT NULL IDENTITY,"
                + "firstName VARCHAR(32),"
                + "lastName VARCHAR(32));");

            // insert data
            stmt.executeUpdate(
                "INSERT INTO testTable (firstName, lastName) VALUES("
                + "'firstName_"+rand.nextInt()+"', 'lastName_"+rand.nextInt()+"')", Statement.RETURN_GENERATED_KEYS);

            // query data
            ResultSet rs = stmt.executeQuery(
                "select * from testTable;");

            while (rs.next()) {
                // show all data
                System.out.println(rs.getBigDecimal(1) + "\t"
                    + rs.getString(2) + "\t"
                    + rs.getString(3) + "\t");
            }
        } finally {
            System.out.println("close connection");
            // Closing the connection
            if (connection != null) {
                connection.close();
            }
            // Stop the server
            if (hsqlServer != null) {
                HSQLDBClass.stopServer(dbName);
            }
        }
    }
}

HSQLDBClass.java

Handle the hsqldb server, start, stop and get instance.

package test;

import java.io.PrintWriter;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

import org.hsqldb.Server;

/**
 * A class to handle hsqldb server start/stop and get server instance
 *
 */
public class HSQLDBClass {
    // to store the database server instance
    private static Map<String, Server> _dbMap = new HashMap<String, Server>();
    private static Lock lock = new ReentrantLock();

    /**
     * srart a hsqldb server
     * @param dbName database name
     * @param path the location to store database information
     * @param logWriter where to log hsqldb server log
     * @return Server, a hsqldb Server instance
     */
    public static Server startServer (String dbName, String path, PrintWriter logWriter) {
        lock.lock();
        // 'Server' is a class of HSQLDB representing
        // the database server
        Server hsqlServer = null;
        try {
            if (isServerExist(dbName)) {
                stopServer(dbName);
            } else {
                hsqlServer = new Server();
    
                // The database will be named [dbName]
                // the settings and data of this database
                // will be stored in files
                // [path].properties and [path].script
                hsqlServer.setLogWriter(logWriter);
                hsqlServer.setDatabaseName(0, dbName);
                hsqlServer.setDatabasePath(0, "file:"+path);
    
                // Start the database!
                hsqlServer.start();
                _dbMap.put(dbName, hsqlServer);
            }
        } finally {
            lock.unlock();
        }
        return hsqlServer;
    }
    /**
     * stop a hsqldb server
     * @param dbName String, the key of a hsqldb server
     */
    public static void stopServer (String dbName) {
        lock.lock();
        try {
            Server hsqlServer = _dbMap.get(dbName);
            if (hsqlServer != null) {
                System.out.println("stop server");
                hsqlServer.stop();
                _dbMap.remove(dbName);
            }
        } finally {
            lock.unlock();
        }
    }
    /**
     * get a hsqldb server instance
     * @param dbName String, the key of a hsqldb server
     * @return Server, the hsqldb server instance
     */
    public static Server getServer (String dbName) {
        lock.lock();
        Server hsqlServer = null;
        try {
            hsqlServer = _dbMap.get(dbName);
        } finally {
            lock.unlock();
        }
        return hsqlServer;
    }
    private static boolean isServerExist (String dbName) {
        if (_dbMap.containsKey(dbName))
            return true;
        return false;
    }
}

The Result

Reference

the official document
http://hsqldb.org/doc/2.0/guide/listeners-chapt.html#lsc_app_start


Download

Full project at github
https://github.com/benbai123/JSP_Servlet_Practice/tree/master/Practice/DBPractice/HSQLDB/EmbeddingHSQLDB

C++ Type Casting

Introduction

There are 4 specific casting operators in C++, this post is a simple note with respect to implicit conversion, explicit conversion and the 4 specific casting operators.

The Program

type_casting.cpp

/*
 * tested with Dev-C++ 4.9.9.2
 */
#include <iostream>
#include "type_casting.h"

using namespace std;

int main () {
    Base baseInstance;
    Extended extendedInstance;
    Unrelated unrelatedInstance;
    const char* strOne = "text one";

    testImplicitConversion(baseInstance, extendedInstance);
    testExplicitConversion(baseInstance, extendedInstance, unrelatedInstance);
    testDynamicCast(baseInstance, extendedInstance, unrelatedInstance);
    testStaticCast(baseInstance, extendedInstance, unrelatedInstance);
    testReinterpretCast(baseInstance, extendedInstance, unrelatedInstance);
    testConstCast(const_cast<char *>(strOne));

    
    system("PAUSE");
}

/**
 **** **** Implicit conversions **** ****
 * do not require any operator
 * they are automatically performed when a value is
 * copied to a compatible type.
 */
void testImplicitConversion(Base baseInstance, Extended extendedInstance) {
    // correct, sub class to super class
    Base b = extendedInstance;
    cout << "test implicit conversions" << endl;
    cout << b.a << endl << endl;
    // wrong, no 'b' in Base class
    // cout << b.b << endl

    // wrong, super class to sub class
    // Extended e = baseInstance;
}

/**
 **** **** Explicit conversion **** ****
 * for most needs with fundamental data types
 * can be applied indiscriminately on classes and pointers to classes,
 * which can * lead to code that while being syntactically correct
 * can cause runtime errors
 */

void testExplicitConversion (Base baseInstance, Extended extendedInstance, Unrelated unrelatedInstance) {
    // correct, sub class to super class
    Base b = (Base)extendedInstance;
    cout << "test explicit conversion" << endl;
    cout << b.a << endl << endl;
    // wrong, no 'b' in Base class
    // cout << b.b << endl

    // syntactically correct, super class pointer to sub class pointer
    Extended *pe = (Extended*)&baseInstance;
    // runtime error, no 'b' in Base class
    // cout << pe->b << endl;

    // wrong, Unrelated to Base
    // Base *pb = (Unrelated*)&unrelatedInstance;
}

/**
 **** **** dynamic_cast **** ****
 *
 * can be used only with pointers and references to objects.
 *
 * Its purpose is to ensure that the result of the type conversion
 * is a valid complete object of the requested class.
 *
 * has overhead of the type-safety checks at run time
 */
void testDynamicCast(Base baseInstance, Extended extendedInstance, Unrelated unrelatedInstance) {
    cout << "test dynamic cast" << endl;
    // correct, cast sub class pointer to super class pointer
    Base* pb = dynamic_cast<Base*>(&extendedInstance);
    Extended* pe;

    // output: 1
    cout << pb->a << endl << endl;
    
    // wrong, cast between two unrelated class pointer
    // pb = dynamic_cast<Base*>(&unrelatedInstance);

    // wrong, cast super class pointer to sub class pointer
    // pe = dynamic_cast<Extended*>(&baseInstance);

}

/**
 **** **** static_cast **** ****
 *
 * can perform conversions between pointers to related classes,
 * can also be used to perform any other non-pointer conversion that
 * could also be performed implicitly,
 * or any conversion between classes with explicit constructors or
 * operator functions as described in "implicit conversions" above.
 *
 * ensures that at least the classes are compatible if the proper
 * object is converted, but no safety check is performed during runtime
 * to check if the object being converted is in fact a full object of
 * the destination type
 *
 * the overhead of the type-safety checks of dynamic_cast is avoided
 */
void testStaticCast(Base baseInstance, Extended extendedInstance, Unrelated unrelatedInstance) {
    cout << "test static cast" << endl;
    // correct, cast sub class pointer to super class pointer
    Base* pb = static_cast<Base*>(&extendedInstance);
    // output: 1
    cout << pb->a << endl;
    // wrong, no 'b' in Base
    // cout << pb->b << endl;

    // correct, cast super class pointer to sub class pointer
    Extended* pe = static_cast<Extended*>(&baseInstance);
    // output: 1
    cout << pe->a << endl << endl;
    // wrong, there is 'b' in Extended but the real object that pe points to is a Base object
    // can run it but the value is unexpectable
    // cout << pe->b << endl;

    // wrong, cast between unrelated class
    // pe = static_cast<Extended*>(&unrelatedInstance);

    // correct, valid Implicit conversion -> valid static_cast
    Base biTwo = static_cast<Base>(extendedInstance);
    // wrong, invalid Implicit conversion -> invalid static_cast
    // Extended eiTwo = static_cast<Extended>(baseInstance);
}
/**
 **** **** reinterpret_cast **** ****
 * converts any pointer type to any other pointer type
 *
 * can also cast pointers to or from integer types
 */
void testReinterpretCast(Base baseInstance, Extended extendedInstance, Unrelated unrelatedInstance) {
    cout << "test reinterpret cast" << endl;
    // cast between unrelated class
    Extended* pe = reinterpret_cast<Extended*>(&unrelatedInstance);
    // maybe unsafe
    cout << pe->a << endl << pe->b << endl;

    // cast pointer to integer
    int nptr = reinterpret_cast<int>(&unrelatedInstance);
    cout << nptr << endl;
    // cast int to pointer
    Base *pb = reinterpret_cast<Base*>(nptr);
    cout << pb->a << endl << endl;
}

/**
 **** **** const_cast **** ****
 * manipulates the constness of an object, either to be set or to be removed
 */
void testConstCast (char* strOne) {
    cout << "test const cast" << endl;
    cout << strOne << endl << endl;
}

type_casting.h

class Base {
    public:
        int a;
        Base ()
        {
            a = 1;
        }
};
class Extended : public Base {
    public:
        float b;
        Extended ()
        {
            b = 2.2;
        }
};
class Unrelated {
    public:
        int c;
        float d;
        Unrelated ()
        {
            c = 3;
            d = 4.4;
        }
};
void testImplicitConversion(Base baseInstance, Extended extendedInstance);
void testExplicitConversion (Base baseInstance, Extended extendedInstance, Unrelated unrelatedInstance);
void testDynamicCast(Base baseInstance, Extended extendedInstance, Unrelated unrelatedInstance);
void testStaticCast(Base baseInstance, Extended extendedInstance, Unrelated unrelatedInstance);
void testReinterpretCast(Base baseInstance, Extended extendedInstance, Unrelated unrelatedInstance);
void testConstCast (char* strOne);

Reference

http://www.cplusplus.com/doc/tutorial/typecasting/


Download

files at github
https://github.com/benbai123/C_Cplusplus_Practice/tree/master/CPP/CPP_Basic/CPP_Type_casting