creating a database in mysql from java

Question

Could you help with this problem.

I'm trying to create and then use a database called TIGER.

I have no problem if I create the database in MySQL and it runs perfectly.

What I would like to do is create it from Java. So that when the code is being run for the first time it creates the database as part of the initial launch. I would like to box it up in a nice clean method if possible.

Could it be possible for someone to show me where you actually position the code

Here is the code

    private String jdbcDriver = "com.mysql.jdbc.Driver";
    private String dbAddress = "jdbc:mysql://localhost:3306/";
    private String dbName = "TIGER";
    private String userName = "root";
    private String password = "";

    private PreparedStatement statement;
    private ResultSet result;
    private Connection con;

    public DbStuff() {
        try {
            Class.forName(jdbcDriver);
            con = DriverManager.getConnection(dbAddress + dbName, userName, password);
        } 

        catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

and here is the code to create the database

    con = DriverManager.getConnection("jdbc:mysql://localhost/?user=root&password=rootpassword"); 
    statement = Conn.createStatement();
    int myResult = statement.executeUpdate("CREATE DATABASE TIGER");

Thanks in advance and any and all help is appreciated

Sorry for the mistakes as I'm a long term reader but a new writer.

When I try do run the main part of the code it generates an SQLException because the database doesn't exist. At this point I would like to catch the exception and create the database at that point. But when I try this it doesn't create the database.

possible duplicated http://stackoverflow.com/questions/717436/create-mysql-database-from-java — Matteo Gatto, Sep 25 '13 at 15:33
Not sure what the question is. Do you get en error when executing the code above or you don't know how to execute it? — Denys Kniazhev-Support Ukraine, Sep 25 '13 at 15:36

score 17 · Answer 1 · answered Mar 21 '17 at 08:26

I suggest to use this snippet of code

private String jdbcDriver = "com.mysql.jdbc.Driver";
private String dbAddress = "jdbc:mysql://localhost:3306/TIGER?createDatabaseIfNotExist=true";
private String userName = "root";
private String password = "";

private PreparedStatement statement;
private ResultSet result;
private Connection con;

public DbStuff() {
    try {
        Class.forName(jdbcDriver);
        con = DriverManager.getConnection(dbAddress, userName, password);
    } 

    catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } 
    catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

I think this is the best way to create database. We don't need to execute command using this createDatabaseIfNotExist=true — Panduka Nandara, Aug 12 '18 at 14:54

score 14 · Answer 2 · answered Sep 25 '13 at 15:40

You could always run

int myResult = statement.executeUpdate("CREATE DATABASE IF NOT EXISTS TIGER;")

If this is ran on project start up it will simply check if the database exists and if it doesn't it will create the new database.

For reference: http://dev.mysql.com/doc/refman/5.0/en/create-database.html

score 6 · Accepted Answer · answered Sep 25 '13 at 15:39

Try with this code.

public class DbStuff {

    private static String jdbcDriver = "com.mysql.jdbc.Driver";
    private static String dbName = "TIGER";


    public static void main(String[] args) throws Exception {
        Class.forName(jdbcDriver);
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/?user=root&password=");
        Statement s = conn.createStatement();
        int Result = s.executeUpdate("CREATE DATABASE "+dbName);
    }
}

it doesnt seem to work for users other root for me – hidden_machine Aug 27 '22 at 14:27 — hidden_machine, Aug 27 '22 at 14:27

score 1 · Answer 4 · answered Sep 26 '13 at 06:47

First of all,

I would like to thank all those who answered it was very helpful indeed to get different views and opinions.

Here is the solution that I have together.

    public class DbStuff {
    private String jdbcDriver = "com.mysql.jdbc.Driver";
    private String dbAddress = "jdbc:mysql://localhost:3306/";
    private String userPass = "?user=root&password=";
    private String dbName = "TIGER";
    private String userName = "root";
    private String password = "";

    private PreparedStatement statement;
    private ResultSet result;
    private Connection con;

    public DbStuff() {

        try {
            Class.forName(jdbcDriver);
            con = DriverManager.getConnection(dbAddress + dbName, userName, password);
        } 

        catch (ClassNotFoundException e) {
            e.printStackTrace();
        } 
        catch (SQLException e) {
            createDatabase();
        }
    }

    private void createDatabase() {
        try {
        Class.forName(jdbcDriver);
        con = DriverManager.getConnection(dbAddress + userPass);
        Statement s = con.createStatement();
        int myResult = s.executeUpdate("CREATE DATABASE " + dbName);
        } 
        catch (ClassNotFoundException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    }

Feel free to comment on any of the code. I know using the e.printStackTrace() is not the best way forward, don't worry It will be modified later on.

score 1 · Answer 5 · edited Jun 25 '16 at 03:40

The easy approach to problem is :

package Database;
import java.sql.*;

public class Database {

 public static void main(String[] args) {
    final  String URl = "JDBC:mysql:/localhost:3306/info";
    final  String id = "root";
    final  String  password = "";

    try
    {
        Connection con1 = DriverManager.getConnection(URl,id,password);
        Statement s1 = con1.createStatement();
        String s = "CREATE DATABASE CLASSIFIED";
        s1.executeUpdate(s);

    }
    catch(SQLException err)
    {
        System.out.println(err.getMessage());
    }

 }

}

score 0 · Answer 6 · answered Sep 25 '13 at 18:55

On an unrelated (to the question) note:

I don't think it’s ever a good idea to programmatically generate the database. You will be better off using the utility tool that comes with your database. Everyone that works with MySQL will almost certainly be familiar with the utility tool and will not have to become familiar with your Java code, change it, compile it, and run it to make a change. Also, the utility has a rich set of features that your Java code probably doesn't have such as assigning permissions, indexing, setting up foreign keys, etc.

score 0 · Answer 7 · answered Feb 14 '15 at 05:41

Few points to note and correct-

You have declared Connection Object as private Connection con; but you are using it as statement = Conn.createStatement();

You have declared a reference to Prepared Statement private PreparedStatement statement; but you are creating instance of Statement statement = Conn.createStatement();. Your code should be -

try {
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost/?user=root&password=rootpassword");
    Statement  statement = con.createStatement();
    int myResult = statement.executeUpdate("CREATE DATABASE IF NOT EXISTS TIGER"); //should get 0
}
catch (SQLException e) {
    System.out.println("Database creation failed");
    e.printStackTrace();
}

Note that when the return value for executeUpdate is 0, it can mean one of two things:

The statement executed was an update statement that affected zero rows.
The statement executed was a DDL statement.

Documentation

score 0 · Answer 8 · answered Oct 09 '22 at 22:28

0

I know this is a bit old. I think the solution of Zaknafein is the best. I just wanted to add, for newbies, if you are having one String for everything to add an & before user.

answered Oct 09 '22 at 22:28

akallali

29
5

creating a database in mysql from java

8 Answers8

Linked