Examples of SQL building

Building a simple select

Select select = SelectUtils.buildSelectFromTable(new Table("mytable"));

select contains now select * from mytable.

Select select = SelectUtils.buildSelectFromTableAndExpressions(new Table("mytable"), new Column("a"), new Column("b"));

select contains now select a, b from mytable.

Or even simpler, if you do not want to build the right expression tree you can provide simple textual expressions, that will be parsed and included in your select.

Select select = SelectUtils.buildSelectFromTableAndExpressions(new Table("mytable"), "a+b", "test");

Extending a simple insert

Insert insert = (Insert)CCJSqlParserUtil.parse("insert into mytable (col1) values (1)");
System.out.println(insert.toString());

//adding a column
insert.getColumns().add(new Column("col2"));

//adding a value using a visitor
insert.getItemsList().accept(new ItemsListVisitor() {

    public void visit(SubSelect subSelect) {
	throw new UnsupportedOperationException("Not supported yet.");
    }

    public void visit(ExpressionList expressionList) {
	expressionList.getExpressions().add(new LongValue(5));
    }

    public void visit(MultiExpressionList multiExprList) {
	throw new UnsupportedOperationException("Not supported yet.");
    }
});
System.out.println(insert.toString());

//adding another column
insert.getColumns().add(new Column("col3"));

//adding another value (the easy way)
((ExpressionList)insert.getItemsList()).getExpressions().add(new LongValue(10));

System.out.println(insert.toString());

Replacing String values

Somebody wanted to publish some SQLs but wanted to scramble all concrete values. So here is a little example of how to achieve this. In short a visitor scans through the complete tree, finds all StringValues and replaces the current value with XXXX.

String sql ="SELECT NAME, ADDRESS, COL1 FROM USER WHERE SSN IN ('11111111111111', '22222222222222');";
Select select = (Select) CCJSqlParserUtil.parse(sql);

//Start of value modification
StringBuilder buffer = new StringBuilder();
ExpressionDeParser expressionDeParser = new ExpressionDeParser() {

    @Override
    public void visit(StringValue stringValue) {
	this.getBuffer().append("XXXX");
    }
    
};
SelectDeParser deparser = new SelectDeParser(expressionDeParser,buffer );
expressionDeParser.setSelectVisitor(deparser);
expressionDeParser.setBuffer(buffer);
select.getSelectBody().accept(deparser);
//End of value modification


System.out.println(buffer.toString());
//Result is: SELECT NAME, ADDRESS, COL1 FROM USER WHERE SSN IN (XXXX, XXXX)

More general replacing of String values in Statements

This is a more general approach for replacing string and long values in all kinds of statements.

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.util.deparser.ExpressionDeParser;
import net.sf.jsqlparser.util.deparser.SelectDeParser;
import net.sf.jsqlparser.util.deparser.StatementDeParser;

public class ReplaceColumnValues {

    static class ReplaceColumnAndLongValues extends ExpressionDeParser {

        @Override
        public void visit(StringValue stringValue) {
            this.getBuffer().append("?");
        }

        @Override
        public void visit(LongValue longValue) {
            this.getBuffer().append("?");
        }
    }

    public static String cleanStatement(String sql) throws JSQLParserException {
        StringBuilder buffer = new StringBuilder();
        ExpressionDeParser expr = new ReplaceColumnAndLongValues();

        SelectDeParser selectDeparser = new SelectDeParser(expr, buffer);
        expr.setSelectVisitor(selectDeparser);
        expr.setBuffer(buffer);
        StatementDeParser stmtDeparser = new StatementDeParser(expr, selectDeparser, buffer);

        Statement stmt = CCJSqlParserUtil.parse(sql);

        stmt.accept(stmtDeparser);
        return stmtDeparser.getBuffer().toString();
    }

    public static void main(String[] args) throws JSQLParserException {
        System.out.println(cleanStatement("SELECT 'abc', 5 FROM mytable WHERE col='test'"));
        System.out.println(cleanStatement("UPDATE table1 A SET A.columna = 'XXX' WHERE A.cod_table = 'YYY'"));
        System.out.println(cleanStatement("INSERT INTO example (num, name, address, tel) VALUES (1, 'name', 'test ', '1234-1234')"));
        System.out.println(cleanStatement("DELETE FROM table1 where col=5 and col2=4"));
    }
}

it outputs:

SELECT ?, ? FROM mytable WHERE col = ?
UPDATE table1 A SET A.columna = ? WHERE A.cod_table = ?
INSERT INTO example (num, name, address, tel) VALUES (?, ?, ?, ?)
DELETE FROM table1 WHERE col = ? AND col2 = ?