Create database if not exists just once

Question

This is my code, this code should be execute just one time, But when my database already exist, The database created message has been displayed!

I want to see message just when database really created, not every time.

public static boolean createDatabase() throws Exception {
    String query = "CREATE DATABASE IF NOT EXISTS LIBRARY3";
    Statement st = null;
    Connection con = DriverManager.getConnection(dbUrl, "root", "2000");
    st = con.createStatement();
    if (st.executeUpdate(query) == 1) { // Then database created
        JOptionPane.showMessageDialog(null, "Database created");
        return true;
    }
    return false;
}

This code always returns true, Why?

score 0 · Accepted Answer · answered Aug 20 '14 at 20:26

0

If its MySQL you need to check if database exists to take decision weather the database was really created

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'LIBRARY3'

answered Aug 20 '14 at 20:26

jmj

237,923
42
401
438

What is schema name of my database? – Aug 20 '14 at 20:31
UNKNOWN COLUMN 'LIBRARY4' IN 'field list' – Aug 20 '14 at 21:05
`SCHEMA_NAME` should be the same, copy and paste the same query – jmj Aug 20 '14 at 21:07
I try this: `SELECT LIBRARY3 FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'LIBRARY3'`, But has same error. – Aug 20 '14 at 21:09
try this `SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'LIBRARY3'` – jmj Aug 20 '14 at 22:06

score 0 · Answer 2 · answered Aug 20 '14 at 20:26

It returns true because you are asking mysql to create the database only if it exists, so, if it exists there is no error, it just does not create it. What you can do is try to create it and, if it fails, check that the reason is that the database alreyady existed.

try {
    statement = connection.createStatement();
    String sql = "CREATE DATABASE DBNAME";
    //To delete database: sql = "DROP DATABASE DBNAME";
    statement.executeUpdate(sql);
    System.out.println("Database created!");
} catch (SQLException sqlException) {
    if (sqlException.getErrorCode() == 1007) {
        // Database already exists error
        System.out.println(sqlException.getMessage());
    } else {
        // Some other problems, e.g. Server down, no permission, etc
        sqlException.printStackTrace();
    }
} catch (ClassNotFoundException e) {
    // No driver class found!
}

score 0 · Answer 3 · edited May 23 '17 at 12:22

0

The query is successful each time. Do a separate query to check if the table exists and return message based on that result.

Read more about how to check whether a table exists here

edited May 23 '17 at 12:22

Community

1
1

answered Aug 20 '14 at 20:26

msfoster

2,474
1
17
19

score 0 · Answer 4 · answered Aug 20 '14 at 20:29

0

Shorter way to do it:

SHOW DATABASES LIKE 'LIBRARY3';

If LIBRARY3 dont exist, you will get an empty string.

answered Aug 20 '14 at 20:29

Touk

459
2
14

Create database if not exists just once

4 Answers4