Java - Execute SQL queries only once

Question

I'm developing a J2EE application with Spring framework and MySQL database. I want to execute the SQL script from java (probably with a request mapping) only once. I have store the sql statements in a properties file as a key-value pair and I loop through each key and execute the statement.

    Connection con = ds.getConnection();
    Statement stmt = con.createStatement();

    Enumeration enuKeys = properties.keys();
    while (enuKeys.hasMoreElements()) {
        String key = (String) enuKeys.nextElement();
        String value = properties.getProperty(key);
        stmt.execute(value);
    }

Is this the correct way of doing? or is there any other way doing the same? Thanks in advance.

Update:

As mentioned in the comment, I tried Spring jdbc intialize database but it is not executing all the queries in the sql file. Only the first 'create' query is executed successfully and it throws an execption "Table 'table_name' already exists". Other queries in the .sql file is not executed. This happens every time I run the server. Kindly help

I think a stored sql procedure can help you. Did you check it? — Keerthivasan
– Keerthivasan, Commented Feb 4, 2014 at 6:08
Keeping the sql statements in a properites might not be a good way. Its better to have it as a procedure. For example, if you have insert sql statement, put it into procedure, call it. — Radhamani Muthusamy
– Radhamani Muthusamy, Commented Feb 4, 2014 at 6:15
docs.spring.io/spring/docs/current/spring-framework-reference/… might be worth a read — user180100
– user180100, Commented Feb 4, 2014 at 6:20
I will not have "insert" queries in the property file. Only the one time activity like creating table or database. I will run this in different environment when it is needed. — Raja Asthana
– Raja Asthana, Commented Feb 4, 2014 at 6:20
@RC Thanks for your quick reply. Seems to be very useful. How I need to place the queries inside the .sql file in order to work? — Raja Asthana
– Raja Asthana, Commented Feb 4, 2014 at 6:37

gerrytan · Accepted Answer · 2014-02-04 09:51:48Z

1

By the sound of it you're trying to setup database schema when your code starts and one of the statement couldn't be run because the table already exist. You need to use statements that checks beforehand (eg: CREATE TABLE IF NOT EXISTS) or catch the exception after executing each statement so it can proceed to the next one.

try {
  stmt.execute(value);
} catch (Exception e) {
  //.. raise some warning and continue ..
}

answered Feb 4, 2014 at 9:51

gerrytan

41.2k12 gold badges88 silver badges99 bronze badges

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

1 Comment

Raja Asthana Over a year ago

Thanks. I re-visited my statement and added IF NOT EXISTS. Now everything works fine.

Collectives™ on Stack Overflow

Java - Execute SQL queries only once

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related