java.sql.SQLSyntaxErrorException: Unknown database 'userinfo'

Question

I clearly have a database named userinfo with a table named userName. I am using XAMPP

public class DatabaseHelper {
    private static final String dbName = "userinfo";
    Connection connection;
    Statement stmt = null;
    Timestamp date;

public  Connection getConnection(){

        String dbName = "userinfo";
    String userName="root";
    String password="12345678";

    try {
        Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
            connection= DriverManager.getConnection("jdbc:mysql://localhost/"+dbName,userName,password);


    } catch (Exception e) {
        e.printStackTrace();
     System.err.println(e.getClass().getName() + ": " + e.getMessage());
        System.exit(0);
    }
    createUsersTable();
    return connection;

}

public void createUsersTable() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost/"+dbName);
            stmt = connection.createStatement();
            String sql = "CREATE TABLE IF NOT EXISTS Users"
                    + "(Id          INTEGER      PRIMARY KEY        AUTOINCREMENT,"
                    + " Firstname   TEXT                    NOT NULL,"
                    + " Lastname        TEXT                    NOT NULL,"
                    + " Username        TEXT                    NOT NULL,"
                    + " Password        TEXT                    NOT NULL,"
                    + " TotalAmount DOUBLE                  NOT NULL,"
                    + " StockAmount DOUBLE                  NOT NULL,"
                    + " Email       TEXT                    NOT NULL" + ");";
            stmt.executeUpdate(sql);
            stmt.close();
            connection.close();
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("User table creation successful");
    }

The error I am getting : WARNING: Loading FXML document with JavaFX API of version 8.0.171 by JavaFX runtime of version 8.0.131 java.sql.SQLSyntaxErrorException: Unknown database 'userinfo'

undate: this is the error i am getting now Mar 07, 2020 2:58:56 PM javafx.fxml.FXMLLoader$ValueElement processValue WARNING: Loading FXML document with JavaFX API of version 8.0.171 by JavaFX runtime of version 8.0.131 java.sql.SQLException: Access denied for user ''@'localhost' (using password: NO)

Answer 1

This question is incomplete, so we can't give you a definitive answer. I have commented to point out the extra information that you need to provide .... if you want a complete answer / solution.

Here are some initial problems that you need to fix.

1. These statements are unnecessary and should be removed:

   Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
   Class.forName("com.mysql.cj.jdbc.Driver");
   

   It has not been necessary since Java 6 and JDBC 4.0 compliant drivers for MySQL were released. Roughly 2007! You only need to use DriverManager.getConnection.

   Why does this matter? Well, the driver class name is database and driver version dependent. If you hardwire a driver classname into your code, it is liable to break¹. (Also, an unnecessary Class.forName call is inefficient.)

2. Your code is not using the account name and password. You will be connecting to the database with no account name and no password. Depending on the way that the MySQL access control has been configured, this might cause it to say that a particular database does not exist. (Possibly ... though I doubt it.)

3. Your code is managing the connection incorrectly:

   • Your getConnection calls DriverManager.getConnection and assigns the result to connection.
   • Then it calls createUserTable ... which calls DriverManager.getConnection again, and assigns it to the same connection variable.
   • Then createUserTable executes some SQL and closes connection.
   • As a result, your getConnection will ultimately return a Connection that has been closed.

   The createUserTable should not be calling DriverManager.getConnection, and it should not be closing connection.

4. Calling System.exit deep in your code is generally a bad idea. It is better to allow the exception to propagate so that something further up can deal with it.

---

^{1 - This is not a theoretical problem. The driver class name did change between MySQL Connector/J 5.x and MySQL Connector/J 8.0.}