Ticket #130: SqlBuilder.java

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.com.pconline.core.product.service;

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

/**
 *
 * @author pc
 */
public class SqlBuilder {
protected StringBuilder sqlBuf = new StringBuilder();
    protected List values = new ArrayList();
    protected boolean isFinished = false;

    public SqlBuilder appendSql(String sql) {
        checkFinished();
        sqlBuf.append(sql);
        return this;
    }    

    public SqlBuilder appendValue(Object value) {
        checkFinished();
        sqlBuf.append('?');
        values.add(value);
        return this;
    }

    public SqlBuilder appendValues(Object[] values) {
        checkFinished();
        sqlBuf.append('(');
        for (int i = 0, c = values.length; i < c; ++i) {
            sqlBuf.append('?').append(','); 
            this.values.add(values);
        }
        int last = sqlBuf.length() - 1;
        if (last > 0 && sqlBuf.charAt(last) == ',') {
            sqlBuf.setCharAt(last, ')');
        }
 
        return this;
    }

    void checkFinished(){
        if(isFinished){
            throw new java.lang.IllegalStateException("Try to append a finished sql");
        }
    }
    
    public SqlBuilder page(int pageNo, int pageSize) {
        int offset = (pageNo - 1) * pageSize;
        int limit = pageSize;
        sqlBuf.insert(1, "SELECT B.* FROM (SELECT A.*, rownum AS r__n FROM (");
        sqlBuf.append(" ) A  ) B WHERE ");
        
        if (offset > 0) {
            this.appendSql(" B.r__n > ").appendValue(offset);
            if (limit > 0) {
                this.appendSql(" AND B.r__n <= ").appendValue(offset + limit);
            }
        } else {
            this.appendSql(" B.r__n <= ").appendValue(limit);
        }
        isFinished = true;
        return this;
    }

    public String getSql() {
        return sqlBuf.toString();
    }

    public Object[] getValues() {
        return values.toArray();
    }
}