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.

Maybe you need to specify the port somewhere.

Jean-François Savard
– Jean-François Savard

2015-02-21 21:21:32 +00:00
Commented Feb 21, 2015 at 21:21 — Jean-François Savard
– Jean-François Savard, Commented Feb 21, 2015 at 21:21

BrianC · Accepted Answer · 2015-02-21 22:04:56Z

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.

answered Feb 21, 2015 at 22:04

BrianC

1,8321 gold badge19 silver badges28 bronze badges

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

2 Comments

Jon O'Dowd Over a year ago

I get what you're saying, but when a person goes to use the program the database is to be stored on their local machine, so it would kind of have to create the database.

BrianC Over a year ago

Ok, then you need to define your connection to you can't include the database name. Then create the schema name, and use the MySQL USE command. You can then run a SQL file to create all your tables, views, etc.

user3690249 · Accepted Answer · 2015-02-21 23:08:45Z

0

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";`

answered Feb 21, 2015 at 23:08

user3690249

631 silver badge8 bronze badges

1 Comment

BrianC Over a year ago

That in no way addresses the problem of having the name of a database that does not exist in the connection string. How he defines the host and port names is not relevant.

Collectives™ on Stack Overflow

Create and connect to an SQL database using Java

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related