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);
    }
}

Java PDFParser

之前寫文件搜尋時候使用的PDF擷取器, 功能為將PDF內容抽出存成文字檔, 發布出來給大家參考。

首先要下載所需套件pdfBox: http://www.pdfbox.org/, 下載後將src/PDFBox-0.7.3.jar與external/FontBox-0.1.0-dev.jar置放到classpath中。

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.net.MalformedURLException;
import java.net.URL;
import org.pdfbox.pdmodel.PDDocument;
import org.pdfbox.util.PDFTextStripper;

public class PDFParser
{
    public void readPdf(String file) throws Exception
    {
        boolean sort = false;             // is sort.
        String pdfFile = file;            // pdf file name.
        String textFile = null;           // output file.
        String encoding = "UTF-8";        // encode type.
        int startPage = 1;                // parse start page no.
        int endPage = Integer.MAX_VALUE;  // parse end page no.
        Writer output = null;             // output writer.
        PDDocument document = null;       // PDF Document.
        try {
            URL url = new URL(pdfFile);   // create connection from source file.
            document = PDDocument.load(pdfFile);  // use PDDocument to load file.
            String fileName = url.getFile();  // get pdf's name.
            
            if (fileName.length() > 4) {  // name output file.
                File outputFile = new File(fileName.substring(0, fileName.length() - 4) + ".txt");
                textFile = outputFile.getName();
            }
            
            // create connection to output file.
            output = new OutputStreamWriter(new FileOutputStream(textFile), encoding);
            
            // use PDFTextStripper to get content.
            PDFTextStripper stripper = new PDFTextStripper();
            stripper.setSortByPosition(sort); // set sort.
            stripper.setStartPage(startPage); // set start page.
            stripper.setEndPage(endPage);     // set end page.
            
            // write to output file.
            stripper.writeText(document, output);

        } catch (MalformedURLException e) {
            e.printStackTrace();
        } finally {
            // close all stream.
            if (output != null) {
                output.close();
            }
            if (document != null) {
                document.close();
            }
        }
    }

    public static void main(String[] args)
    {
        PDFParser pdfReader = new PDFParser();
        try {
            // read pdf content.
            pdfReader.readPdf("place your pdf location here");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}