Java SQLParser SQL語法解析器

最近案子因為寫了一個輕便型的小型資料庫, 所以要用的SQL指令解析來動作, 基本上這支程式解析簡單的一層SQL指令沒有問題, 多層則是不行使用的。

剛好順便複習一下Regular Expression, 都快不認識它了哈哈!!

Sample Code:

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SqlParser
{
    public static String type;
    public static String conditions;
    public static String tables;
    public static String order;
    public static String where;

    public static void parse(String sql)
    {
        String regex = "";

        type = "";
        conditions = "";
        tables = "";
        order = "";
        where = "";

        if (sql.indexOf("select") == 0) {

            type = "select";
            if (isContains(sql, "\\s+where\\s+")) {
                regex = "(select)(.+)(from)(.+)(where*)";
            } else if (isContains(sql, "\\s+order\\s+")) {
                regex = "(select)(.+)(from)(.+)(order*)";
            } else {
                regex = "(select)(.+)(from)(.+)($)";
            }
            tables = getMatchedPosition(regex, sql, 4);
            conditions = getMatchedPosition(regex, sql, 2);
            order = getOrderIndex(sql);
            where = getWhere(sql);

        } else if (sql.indexOf("insert") == 0) {

            type = "insert";
            regex = "(insert.*into)(.+)(values)(.+)";
            tables = getMatchedPosition(regex, sql, 2);
            conditions = getMatchedPosition(regex, sql, 4);

        } else if (sql.indexOf("update") == 0) {

            type = "update";
            regex = "(update)(.+)(set)(.+)(where*)";
            tables = getMatchedPosition(regex, sql, 2);
            conditions = getMatchedPosition(regex, sql, 4);
            where = getWhere(sql);

        } else if (sql.indexOf("delete") == 0) {

            type = "delete";
            if (isContains(sql, "\\s+where\\s+")) {
                regex = "(delete.*from)(.+)(where)(.+)";
                where = getMatchedPosition(regex, sql, 4);
            } else {
                regex = "(delete.*from)(.+)($)";
            }
            tables = getMatchedPosition(regex, sql, 2);
        }
    }

    // get matched string from position.
    private static String getMatchedPosition(String regex, String text, int position)
    {
        try {
            // ignore string case.
            Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
            Matcher matcher = pattern.matcher(text);
            while (matcher.find()) {
                return matcher.group(position).trim();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    // get where condition.
    private static String getWhere(String sql)
    {
        String regex = "";

        if (isContains(sql, "where\\s+")) {
            if (isContains(sql, "order\\s+by")) {
                regex = "(where\\s+)(.+)(order)";
            } else {
                regex = "(where\\s+)(.+)($)";
            }
        } else {
            return null;
        }

        return getMatchedPosition(regex, sql, 2).trim();
    }

    // get order by content.
    private static String getOrderIndex(String sql)
    {
        String regex = "";

        if (isContains(sql, "order\\s+by")) {
            regex = "(order\\s+by)(.+)($)";
        } else {
            return null;
        }

        return getMatchedPosition(regex, sql, 2).trim();
    }

    // check if contains.
    private static boolean isContains(String lineText, String word)
    {
        // ignore string case.
        Pattern pattern = Pattern.compile(word, Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(lineText);
        return matcher.find();
    }

    public static void main(String[] args)
    {
        SqlParser.parse("select * from ADMIN_MSTR order by ID desc");
        System.out.println("tables: " + tables + ", condition: " + conditions);

        SqlParser.parse("select AM_NO, AM_PASSWD from ADMIN_MSTR where ID=3");
        System.out.println("tables: " + tables + ", condition: " + conditions + ", where: " + where);

        SqlParser.parse("select AM_NO, AM_PASSWD from ADMIN_MSTR where ID>0 order by ID desc");
        System.out.println("tables: " + tables + ", condition: " + conditions + ", where: " + where + ", order by: " + order);

        SqlParser.parse("insert into ADMIN_MSTR(ID, NAME, PASSWD) values ('1', 'Sam', 'abcd1234')");
        System.out.println("tables: " + tables + ", condition: " + conditions);

        SqlParser.parse("update ADMIN_MSTR set NAME='ABCD', PASSWD='123465' where ID=1");
        System.out.println("tables: " + tables + ", condition: " + conditions + ", where: " + where);

        SqlParser.parse("delete from ADMIN_MSTR where ID=2");
        System.out.println("tables: " + tables + ", where: " + where);
    }
}