MYSQL script in java

Question

I have this list of queries for a local db on my computer:

String[] SQLscript=new String[]{
    "CREATE DATABASE IF NOT EXISTS MapDB2;",
    "CREATE USER 'MapUser'@'localhost' IDENTIFIED BY 'map';",
    "GRANT CREATE, SELECT, INSERT, DELETE ON MapDB2.* TO MapUser@localhost IDENTIFIED BY 'map';",
    "CREATE TABLE MapDB2.playtennis(outlook varchar(10),temperature float(5,2),umidity varchar(10),wind varchar(10),play varchar(10));",
    "insert into MapDB2.playtennis values('sunny',30.3,'high','weak','no');",
    "insert into MapDB2.playtennis values('sunny',30.3,'high','strong','no');",
    "insert into MapDB2.playtennis values('overcast',30.0,'high','weak','yes');",
    "insert into MapDB2.playtennis values('rain',13.0,'high','weak','yes');",
    "insert into MapDB2.playtennis values('rain',0.0,'normal','weak','yes');",
    "insert into MapDB2.playtennis values('rain',0.0,'normal','strong','no');",
    "insert into MapDB2.playtennis values('overcast',0.1,'normal','strong','yes');",
    "insert into MapDB2.playtennis values('sunny',13.0,'high','weak','no');",
    "insert into MapDB2.playtennis values('sunny',0.1,'normal','weak','yes');",
    "insert into MapDB2.playtennis values('rain',12.0,'normal','weak','yes');",
    "insert into MapDB2.playtennis values('sunny',12.5,'normal','strong','yes');",
    "insert into MapDB2.playtennis values('overcast',12.5,'high','strong','yes');",
    "insert into MapDB2.playtennis values('overcast',29.21,'normal','weak','yes');",
    "insert into MapDB2.playtennis values('rain',12.5,'high','strong','no');"
};

My question is, how can I get a result of success (or failure) for each query?

Should I use statement.execute() method or statement.executeUpdate()?

PLEASE NOTE: all I want is a simple result that gives me an idea about the success of the query executed, such as true if query was executed correctly, false vice versa.

Davide Lorenzo MARINO · Accepted Answer · 2015-09-22T15:30:10.190

Simply loop through the elements of SQLscript

// Note you need to handle exceptions to see if errors occurs
for (String sql : SQLscript) {
    int result = statement.executeUpdate(sql);
    // Use result to check if data were updated
}

There are two different approach to handle exceptions:

At first error block all (rollback all operations if an error occurs)
At any error continue to the next statement (commit success operations)

Depending on how you like to handle them you have to add the try catch inside the for loop or surrounding the for loop.

To check if an insert/update statement has really inserted/updated a record check the value of result. It represent the number of records affected by the executeStatement command.

From javadoc:

either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

Note: DDL (Data Definition Language) statement should returns always 0 because no record is affected by the statement. So you have to split your array in two main blocks (DDL and DML Data Manipulation Language) and execute different checks for both parts. For DDL checking for no exception thrown. For DML check for at least a record updated/inserted.

Here are the two different exception handling approach:

for (String sql : SQLscript) { 
    boolean error = false;
    try {
        int result = statement.executeUpdate(sql);
    } catch (SQLException e) {
        error = true;
        e.printStackTrace();
    }
    if (error) {
        // Do something special
    }

}

Second approach

Connection conn = ...

boolean error = false;
try {
    for (String sql : SQLscript) { 
        int result = statement.executeUpdate(sql);
    }
    conn.commit();
} catch (SQLException e) {
    e.printStackTrace();
    conn.rollback();
    error = true;
}

if (error) {
    // Do something special
}

Thanks, but i want to be sure that each operation was successfull, is there a way to retrieve a kind of "boolean" result? — Francesco Rizzi, Sep 22 '15 at 15:18
If no exception is thrown all is ok. If you need to check if an update as really updated rows use the variable result. Result is the number of records updated (or inserted) by the executeUpdate statement — Davide Lorenzo MARINO, Sep 22 '15 at 15:19

score 0 · Answer 2 · edited May 23 '17 at 11:51

0

It will be better to create a stored procedure to execute batch statements.

tutorial here
http://www.tutorialspoint.com/jdbc/callablestatement-object-example.htm

OR refer :

https://stackoverflow.com/a/10929404/1811348
and
http://www.tutorialspoint.com/javaexamples/jdbc_executebatch.htm

to execute batch of statements.

edited May 23 '17 at 11:51

Community

1
1

answered Sep 22 '15 at 15:17

swapyonubuntu

1,952
3
25
34

MYSQL script in java

2 Answers2