#StackBounty: #java #sql #sql-insert #sql-delete #jsqlparser How to convert insert sql statement to delete sql statement using JSQLParser

Bounty: 50

I want to convert insert sql statement to delete sql statement using JSQLParser, How this can be achieved ?
for example here is sample insert sql

INSERT INTO emp (empno, ename, job, sal, comm, deptno, joinedon) VALUES (4160, 'STURDEVIN', 'SECURITY GUARD', 2045, NULL, 30, TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF'));

post conversion to delete sql will be following

DELETE FROM emp WHERE empno=4160 AND ename='STURDEVIN' AND job='SECURITY GUARD' AND sal=2045 AND comm=NULL AND deptno=30 AND joinedon=TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF');

Here is code thus far

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;

import java.util.List;

public class Main {

    public static void main(String[] args) {
        String insertSQL = "INSERT INTO emp (empno, ename, job, sal, comm, deptno, joinedon) VALUES (4160, 'STURDEVIN', 'SECURITY GUARD', 2045, NULL, 30, TO_TIMESTAMP('2014-07-02 06:14:00.742000000', 'YYYY-MM-DD HH24:MI:SS.FF'));";
        final Statement statement;
        try {
            statement = CCJSqlParserUtil.parse(insertSQL);
            if (statement instanceof Insert) {
                final Insert insertStatement = (Insert) statement;
                System.out.println("insertStatement: " + insertStatement);
                List<Column> columns = insertStatement.getColumns();
                for (final Column column : columns) {
                    System.out.println("column: " + column);
                }
                ItemsList itemsList = insertStatement.getItemsList();
                System.out.println("itemsList: " + itemsList);
                final Delete deleteStatement = new Delete();
                deleteStatement.setTable(insertStatement.getTable());
                System.out.println("deleteStatement: " + deleteStatement);
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }
}

I am building this project using maven here is pom dependency.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>oracle.insert.delete.jsqlparser</groupId>
    <artifactId>oracle_from_insert_to_delete_using_JSqlParser</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>1.2</version>
        </dependency>
    </dependencies>


</project>

Part of legacy code, These insert statement are written for setting up test data, I want to clean up test data automatically once test is complete. And not depend on poorly written delete statement in teardown.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.