How to split sql queries using Java

Question

I sowed these queries:Queries are on the same line.

INSERT INTO data_location ( `latitude`, `updated`, `id`, `longitude`, `created`)
VALUES( '213.2000000', '2014-08-25 11:07:42+00:00', '1', '321.0000000', 
    '2014-08-25 11:07:42+00:00');
INSERT INTO data_location ( `latitude`, `updated`, `id`, `longitude`, `created`) 
VALUES( '42.7191000', '2014-09-17 12:53:49+00:00', '2', '23.0834000', 
    '2014-09-17 12:53:49+00:00');
INSERT INTO data_news (`id`, `title`, `date`, `short_content`, `content`, 
    `created`, `updated`) 
VALUES(10, 'fdsafsda.', 'fdsafafa>', '2014-09-26 08:10:55', '2014-09-26 08:10:55');
INSERT INTO data_news (`id`, `title`, `date`, `short_content`, `content`, 
    `created`, `updated`) 
VALUES(11, 'fdsafdsafd „THE THROWAWAYS”', '2014-09-26 11:22:00', 
    'fdsafdsafdsafda(dsa);', '2014-09-26 09:05:09', '2014-09-26 09:05:09');

I want to separate them. At this stage, use the following method to divide them:

String[] parts = sql.split("(?<=\\);)");
db.beginTransaction();
for (String entry : parts) {
    SQLiteStatement stmt = db.compileStatement(entry);
    stmt.execute();
    stmt.clearBindings();
}
db.setTransactionSuccessful();
db.endTransaction();

The problem is that in the last query data has the following ...safda(dsa);... It is accepted and becomes invalid query. Is there a way to split smart not to receive such problems ?

danielkelemen · Accepted Answer · 2014-09-30 12:15:07Z

2

If all the queries look like these ones in your example then you could match the end with the closing parenthes, too: ');

So the split becomes: sql.split("(?<='\\);)");

answered Sep 30, 2014 at 12:15

danielkelemen

1,69911 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dursun · Accepted Answer · 2014-09-30 11:53:53Z

you create SqlRunner class as follow and use it; for details visit http://allstarnix.blogspot.com.tr/2013/03/how-to-execute-sql-script-file-using.html;

import java.io.IOException;
import java.io.LineNumberReader;
import java.io.PrintWriter;
import java.io.Reader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;

public class SqlRunner {
    public static final String DELIMITER_LINE_REGEX = "(?i)DELIMITER.+", DELIMITER_LINE_SPLIT_REGEX = "(?i)DELIMITER", DEFAULT_DELIMITER = ";";
    private final boolean autoCommit, stopOnError;
    private final Connection connection;
    private String delimiter = SqlRunner.DEFAULT_DELIMITER;
    private final PrintWriter out, err;

    public SqlRunner(final Connection connection, final PrintWriter out, final PrintWriter err, final boolean autoCommit, final boolean stopOnError) {
        if (connection == null) {
            throw new RuntimeException("SqlRunner requires an SQL Connection");
        }
        if (err == null || out == null) {
            throw new RuntimeException("SqlRunner requires both out and err PrintWriters");
        }
        this.connection = connection;
        this.autoCommit = autoCommit;
        this.stopOnError = stopOnError;
        this.out = out;
        this.err = err;
    }

    public void runScript(final Reader reader) throws SQLException {
        final boolean originalAutoCommit = this.connection.getAutoCommit();
        try {
            if (originalAutoCommit != this.autoCommit) {
                this.connection.setAutoCommit(this.autoCommit);
            }
            this.runScript(this.connection, reader);
        } finally {
            this.connection.setAutoCommit(originalAutoCommit);
        }
    }

    private void runScript(final Connection conn, final Reader reader) {
        StringBuffer command = null;
        try {
            final LineNumberReader lineReader = new LineNumberReader(reader);
            String line = null;
            while ((line = lineReader.readLine()) != null) {
                if (command == null) {
                    command = new StringBuffer();
                }
                String trimmedLine = line.trim();

                if (trimmedLine.startsWith("--") || trimmedLine.startsWith("//") || trimmedLine.startsWith("#")) {

                    // Line is a comment
                    out.println(trimmedLine);
                    out.flush();

                } else if (trimmedLine.endsWith(this.delimiter)) {

                    // Line is end of statement

                    // Support new delimiter
                    final Pattern pattern = Pattern.compile(SqlRunner.DELIMITER_LINE_REGEX);
                    final Matcher matcher = pattern.matcher(trimmedLine);
                    if (matcher.matches()) {
                        delimiter = trimmedLine.split(SqlRunner.DELIMITER_LINE_SPLIT_REGEX)[1].trim();

                        // New delimiter is processed, continue on next
                        // statement
                        line = lineReader.readLine();
                        if (line == null) {
                            break;
                        }
                        trimmedLine = line.trim();
                    }

                    // Append
                    command.append(line.substring(0, line.lastIndexOf(this.delimiter)));
                    command.append(" ");

                    Statement stmt = null;
                    ResultSet rs = null;
                    try {
                        stmt = conn.createStatement();
                        out.println();
                        out.println(command);
                        out.flush();
                        boolean hasResults = false;
                        if (this.stopOnError) {
                            hasResults = stmt.execute(command.toString());
                        } else {
                            try {
                                stmt.execute(command.toString());
                            } catch (final SQLException e) {
                                e.fillInStackTrace();
                                err.println("Error on command: " + command);
                                err.println(e);
                                err.flush();
                            }
                        }
                        if (this.autoCommit && !conn.getAutoCommit()) {
                            conn.commit();
                        }
                        rs = stmt.getResultSet();
                        if (hasResults && rs != null) {

                            // Print result column names
                            final ResultSetMetaData md = rs.getMetaData();
                            final int cols = md.getColumnCount();
                            for (int i = 0; i < cols; i++) {
                                final String name = md.getColumnLabel(i + 1);
                                out.print(name + "\t");
                            }
                            out.println("");
                            out.println(StringUtils.repeat("---------", md.getColumnCount()));
                            out.flush();

                            // Print result rows
                            while (rs.next()) {
                                for (int i = 1; i <= cols; i++) {
                                    final String value = rs.getString(i);
                                    out.print(value + "\t");
                                }
                                out.println("");
                            }
                            out.flush();
                        } else {
                            out.println("Updated: " + stmt.getUpdateCount());
                            out.flush();
                        }
                        command = null;
                    } finally {
                        if (rs != null)
                            try {
                                rs.close();
                            } catch (final Exception e) {
                                err.println("Failed to close result: " + e.getMessage());
                                err.flush();
                            }
                        if (stmt != null)
                            try {
                                stmt.close();
                            } catch (final Exception e) {
                                err.println("Failed to close statement: " + e.getMessage());
                                err.flush();
                            }
                    }
                } else {

                    // Line is middle of a statement

                    // Support new delimiter
                    final Pattern pattern = Pattern.compile(SqlRunner.DELIMITER_LINE_REGEX);
                    final Matcher matcher = pattern.matcher(trimmedLine);
                    if (matcher.matches()) {
                        delimiter = trimmedLine.split(SqlRunner.DELIMITER_LINE_SPLIT_REGEX)[1].trim();
                        line = lineReader.readLine();
                        if (line == null) {
                            break;
                        }
                        trimmedLine = line.trim();
                    }
                    command.append(line);
                    command.append(" ");
                }
            }
            if (!this.autoCommit) {
                conn.commit();
            }
        } catch (final SQLException e) {
            e.fillInStackTrace();
            err.println("Error on command: " + command);
            err.println(e);
            err.flush();
        } catch (final IOException e) {
            e.fillInStackTrace();
            err.println("Error on command: " + command);
            err.println(e);
            err.flush();
        }
    }
}

I think there is a simple way as a regex than to make class to tackle this problem.
ok, but sometimes regex solutions becomes more complex, I think an obvious solution is more simple.
It breaks when the script includes a DECLARE @variable (Transact-sql)

Shisholik · Accepted Answer · 2014-09-30 11:48:27Z

0

You can separate them using "new line" symbol.

String[] parts = sql.split(System.lineSeparator());

answered Sep 30, 2014 at 11:48

Shisholik

1

Comments

Teetrinker · Accepted Answer · 2014-09-30 12:09:47Z

0

If your sample data is displaying all corner cases, what you need to find is all ; not followed by a '. This will work with the regular expression ;[^']. Hence, the split command would be: String[] parts = sql.split(";[^']"); In my test, this gives one sql query per line.

answered Sep 30, 2014 at 12:09

Teetrinker

5755 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to split sql queries using Java

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related