Create and connect to an SQL database using Java

Question

I have a Java program that connects to an SQL database, and can write and read from it, however, in order to use the program, I first have to open Workbench, and run an SQL query in there.

Obviously, this isn't a great solution, so how can I create and connect to a database all within the Java code?

I've searched online, including the Oracle site, but can't see to get it.

Below is my code.

public Connection ConnectNow() //Connect to the database
{
    try 
    {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
    } 

    catch (Exception ex) 
    {
        System.out.println("Error 1");
        ex.getMessage();
    }

    final String dbURL = "jdbc:mysql://localhost/game1"; //replace 'assignex' with database name
    Connection conn = null;

    try 
    {
        conn = DriverManager.getConnection(dbURL, "root", "password");

        System.out.println("\n== Connection Successful ==");

        return conn;
    } 

    catch (SQLException ex) 
    {
        System.out.println("Error 2");
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());

        return null;
    }
}


public void CreateDatabase()
{
    try
    {
        Connection conn = ConnectNow();
        Statement stmt = conn.createStatement();

        String createDatabase = "CREATE DATABASE IF NOT EXISTS game1"; //creates database
        stmt.executeUpdate(createDatabase);

        conn.close();
        stmt.close();

        System.out.println("<< Database created successfully >>");
    }

    catch (Exception ex)
    {
        System.out.println("Error 7");
        ex.getMessage();
        ex.printStackTrace();
    }
}

public void CreateTable() //creates a table within the database
{
    try
    {
        Connection conn = ConnectNow();
        Statement stmt = conn.createStatement();

        String createTable = "CREATE TABLE IF NOT EXISTS user" + 
                             "(firstname VARCHAR(255), " + //AUTO_INCREMENT to add numbers automatically
                             " surname VARCHAR(255), " +
                             " day INTEGER, " +
                             " month INTEGER, " +
                             " year INTEGER, " +
                             " username VARCHAR(255) NOT NULL, " +
                             " password VARCHAR(255), " +
                             " PRIMARY KEY (username))";
        stmt.executeUpdate(createTable);

        conn.close();
        stmt.close();

        System.out.println("<< Table created successfully >>");
    }

    catch (Exception ex)
    {
        System.out.println("Error 6");
        ex.getMessage();
        ex.printStackTrace();
    }
}

Presumably, it's something to do with me attempting to connect before creating the database, but I can't find the solution to implement this correctly.

Help is much appreciated.

Answer 1

maybe you could try declaring your port and host separately as strings like the following and create the database then you should be okay.

  `import java.sql.*; 

    public class Test {
    public static void main(String args[]) { 

    Connection con; 

    try { 
  **String server = "localhost";
    String port = "50000";**    //generic port for DB2 jdbc
    String url = "jdbc:db2://"+server+":"+port+"/sample"; String userid = ”userid";
    String password = ”password";`                

Answer 2

You already answered you own question. Your connection URL (jdbc:mysql://localhost/game1) specifies the database (game1) to connect to, but that database does not exist. If you look at your Exception output is is probably telling to exactly that. Your app should NOT be creating the database. It should just be connecting to it and modifying it. You can try to set the URL to not include the database name and then just use the MySQL USE command to specify a database but that is a lot of un-nessasary work. Just do not have the app create it's database.