MySQL and Java: table doesn't exist

Question

I have created MySQL DB using MySQL Workbench 5.2.40 CE. At first, I created New EER Model and named it test. Then created schema named: testdb, then table named: table1. Also, created a connection.

The following class to create connection with my DB. It is successful

import java.sql.*;

public class DBConnection {

public static Connection con = null;

public static Object Connect;

public static void ConnectDB () {

System.out.println("--- MySQL JDBC Connection Testing -----");

try {

     Class.forName("com.mysql.jdbc.Driver"); //loading the driver

    } catch (ClassNotFoundException e) {

    System.out.println("Where is your MySQL JDBC Driver?");
    e.printStackTrace();
    return;

    }

System.out.println("MySQL JDBC Driver Registered!");

try {

     //Connect to the database
     con = DriverManager.getConnection
     ("jdbc:mysql://localhost:3306/test", "root", "password");

    } catch (SQLException e) {

    System.out.println("Connection Failed! Check output console");
    e.printStackTrace();
    return;
    }

if (con != null) {
System.out.println("You made it, take control your database now!");

} else {
System.out.println("Failed to make connection!");
}
}
} //end class

Then, I made another class that contains the Main function that calls the "ConnectDB" from the the above "DBConnection" class. The problem is that I get an error saying: Got an exception! Table 'test.table1' doesn't exist

I'm sure that table1 exists. I copied its name from the workbench.

This is the code for the Main class:

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.PreparedStatement;
import java.util.Scanner;
import java.sql.*;

public class Main {

public static void main(String[] argv) {

String name=null;                           
DBConnection.ConnectDB(); //connect to database

//Read the inputs from the user
System.out.println("Enter the name: ");
Scanner userInput=new Scanner(System.in);
name=userInput.next();

//Confirmation messages
System.out.println("You entered: "+ name);

// BEGIN INSERT TO DB 
try{
// the mysql insert statement
String query=null;
query = " insert into table1 (name)"+ " values (?)";

// create the mysql insert preparedstatement

PreparedStatement preparedStmt = DBConnection.con.prepareStatement(query);
preparedStmt.setString    (1, name);

// execute the preparedstatement
preparedStmt.execute();
System.out.println("Inserted");
DBConnection.con.close();

} 
catch (Exception e)
{
  System.err.println("Got an exception!");
System.err.println(e.getMessage());
} 
}
}

So, where is the problem if the table exists and I used all small letters in Java and MySQL?? and am I doing the right thing by calling the "ConnectDB" function in the Main only and define an object of type connection as public static in my "DBConnection" class?? I'm going to insert values throughout many classes later?? I'm sorry for the question, but I have been searching a lot and want to make sure I got it right, and this is my first Java application that connects to database.

EDIT

Multipurpose

does database "test" contain table "table1"? – Peahen

if you created a schema called testdb how come your jdbc url is /test ? – Mickimickie

have you confirmed for some GUI tool even work bench that test DB and table1 in it exists? – Brodeur

If I used /test I get error: Unknown database. So, I tried test (which is the name that I entered for EER when I clicked Create New ERR model. and the hence, the connection was successful but the problem appears when trying to indert values. – Multipurpose

@naresh: I added screen shot. – Multipurpose

@mtariq: I added screen shot. – Multipurpose

did you try testdb.table1 instead of table1 in your insert statement? Aslo, try to execute a select query on table1, what do you get? – Shaefer

@Adel: Just tried and it gives: Table 'testdb.table1' does not exist. – Multipurpose

@JuryA Your screen shot shows testdb.table1 exists. I get a doubt if you are connecting to different instance of MySQL ! – Cubiform

Answer 1

Two alternatives:

1. Connect to testdb
2. Access your table using database qualifier.

Connecting to testdb:

con = DriverManager.getConnection
     ("jdbc:mysql://localhost:3306/testdb", "root", "password");

Using database qualifier:

query = " insert into testdb.table1 (name)"+ " values (?)";

Cubiform

I have tried to connect to testdb but I got: Unknown database: 'testdb'. – Multipurpose

@JuryA Are the connections made through MWB and java are to the same MySQL database instance? – Cubiform

@JuryA My question is "Is MWB also connecting to the same hostname, port?" – Cubiform

Yes. If I'm correct, I checked 'Manage connections' in the MWB and checked the 'Local instance MySQL55' and found that it is connected to the same hostname, port. – Multipurpose

Answer 2

You JDBC url is not correct use jdbc:mysql://localhost:3306/testdb in your code.

Brodeur