How do you connect to a MYSQL database from Eclipse?

[Menre]

Hi All,

I am learning Java and have now run into trouble. So, I need your help please.

My problems and questions are as follows. I am coding Java with Eclipse on a Mac. I have a database created and running on MYSQL (MAMP), inside that database there is a table called students and I have entered all the content. Everything is fine when I queried my database in MYSQL.

Now, I want to use Java to connect to that database in MYSQL. But when I run my program in Eclipse, I get an error message 'Driver not found'.

Which driver and where is he taking me to? I don't know.

I have put the code below. Could you look at it and tell me what I have done wrong, or how to connect to MYSQL successfully please.

Also, if there is a better way of using MYSQL with Eclipse could you let me know please.

//this is the code below

import java.sql.*;
import javax.swing.*;
import java.awt.*;

public class StudentList extends JFrame {

//the attributes
public static final String driver = "org.gjt.mm.mysql.Driver";
public static final String url = "jdbc:mysql://localhost/NewStudents";
public static final String username = "question";
public static final String password = "answer";

private Connection con;
private Statement st;
private ResultSet result;
private JTextArea display = new JTextArea(25,25);

//the constructor
public StudentList()
{
// now configure the frame and text area
setTitle("Registered New Students");
add("Center",display);
setSize(520,250);
setVisible(true);
display.setTabSize(16);
display.setFont (new Font ("DialogInput", Font.BOLD, 14));

try
{
// load the MYSQL jdbc driver
Class.forName(driver);
}

catch(ClassNotFoundException e)
{
display.setText("Driver not found");
}

try
{
// connect to the database
con = DriverManager.getConnection(url, username, password);

//create an SQL statement
st = con.createStatement();

//execute an SQL query
result = st.executeQuery("select * from students");

//The code continues, but I felt this might be all you would need to solve //my problem.


Thanks in advance.
Menre

[OrangeDog]

Have you downloaded the org.gjt.mm.mysql.Driver driver and followed the installation instructions for it?

[Menre]

Hello;

Thanks for your response to my message. I did not download the org.gjt.mm.mysql.Driver before. I have done it now and it is on my Desktop, but the problem I am having is where to install.

I dowloaded the file:
mysql-connector-java-5.1.7.tar
from mysql.org website. It was a zip file and I unzipped it to get this one: mysql-connector-java-5.1.7 and inside the src folder, I was able to find a folder pointing to the driver class; src org.gjt.mm.mysql.Driver

The documentation doesn't help matters at all. Shall I copy it to the Eclipse library or somewhere else? Could you point me to the right direction please?

Menre

[OrangeDog]

There should be a .jar in the package somewhere. You need to add that to your classpath. You can do this in eclipse:
Bulid Path -> Add External Archives...

[Menre]

Hello again,

I appreciate your effort so far, but thing is, the problem still hasn't been solved and is driving me crazy. I don't want to quit learning Java and this database has got to work for me.

In your last post, this 'Build Path -> Add External Archives...' was not quite clear to me. I did not find any button in Eclipse that says Build Part or did I fail to understand it clearly?

However, I followed your instructions and now, here is the latest. When I opened the folder 'mysql-connector-java-5.1.7' from my Desktop, I actually found the .jar file: 'mysql-connector-java-5.1.7-bin.jar', The next thing I did was to copy that file to the folder where I saved my .java files from Eclipse. Then I tested my work again only to receive the same message "Driver not found" and even with more errors reported below.

//I found these error messages below at the bottom of Eclipse window panel.
2009-04-11 14:48:53.917 java[210] CFLog (0): CFMessagePort: bootstrap_register(): failed 1103 (0x44f), port = 0xf203, name = 'java.ServiceProvider'
See /usr/include/servers/bootstrap_defs.h for the error codes.
2009-04-11 14:48:53.917 java[210] CFLog (99): CFMessagePortCreateLocal(): failed to name Mach port (java.ServiceProvider)
java.sql.SQLException: No suitable driver
at java.sql.DriverManager.getConnection(DriverManager .java:545)
at java.sql.DriverManager.getConnection(DriverManager .java:171)
at StudentList.<init>(StudentList.java:43)
at runStudentList.main(runStudentList.java:5)

Have a look at my code again please and see if there is something that I needed to add to it now that I have copied that .jar to somewhere.

Once again, thanks for your help.

Menre

[The Ghost 88]

Dear my friend:
you can add the jar to the class path in two way:
1) you can copy thee jar file in the this path : JDK --> JRE --> Lib --> ext.
2) in the eclipse go to : Run menu --> Run Configurations --> ClassPath --> Add External Jars
then you define the Definition of the Jar file.

[OrangeDog]

Build Path is in the right-click menu for your project - in the project explorer.

[Menre]

Hi,

Thanks for all you help. I did as you instructed and the problem became half-solved. This time, I get a blank page. It doesn't say 'Driver not found' It show me just an empty page with the title: 'Registered new Students'. Nothing shows on the page. Is that normal?

I did as you said. I opened my work in Eclipse; I clicked on Run -->Open Run Dialog... -->Classpath (here I selected the folder with my java files) -->Add External JARS... -->A new window opened and I selected the Driver.java file
-->I chose Open -->Finally, I chose Run.

Now, the results.

1) A blank page opened with a title bar text: 'Registered New Students'.
But nothing to read on the page and the cursor is blinking as, if it is waiting for me to enter some text on the page.

2) At the bottom of the Eclipse page, Eclipse throws in the error messages below.

2009-04-11 19:16:16.431 java[221] CFLog (0): CFMessagePort: bootstrap_register(): failed 1103 (0x44f), port = 0x10f03, name = 'java.ServiceProvider'
See /usr/include/servers/bootstrap_defs.h for the error codes.
2009-04-11 19:16:16.431 java[221] CFLog (99): CFMessagePortCreateLocal(): failed to name Mach port (java.ServiceProvider)
com.mysql.jdbc.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
at com.mysql.jdbc.SQLError.createCommunicationsExcept ion(SQLError.java:1070)
at com.mysql.jdbc.ConnectionImpl.createNewIO(Connecti onImpl.java:2120)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImp l.java:723)
at com.mysql.jdbc.ConnectionImpl.getInstance(Connecti onImpl.java:298)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonReg isteringDriver.java:282)
at java.sql.DriverManager.getConnection(DriverManager .java:525)
at java.sql.DriverManager.getConnection(DriverManager .java:171)
at ProductQuery.<init>(ProductQuery.java:43)
at runProductQuery.main(runProductQuery.java:5)
Caused by: com.mysql.jdbc.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
at com.mysql.jdbc.SQLError.createCommunicationsExcept ion(SQLError.java:1070)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:335)
at com.mysql.jdbc.ConnectionImpl.createNewIO(Connecti onImpl.java:2043)
... 7 more
Caused by: java.net.ConnectException: Connection refused
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl .java:333)
at java.net.PlainSocketImpl.connectToAddress(PlainSoc ketImpl.java:195)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.j ava:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.j ava:430)
at java.net.Socket.connect(Socket.java:520)
at java.net.Socket.connect(Socket.java:470)
at java.net.Socket.<init>(Socket.java:367)
at java.net.Socket.<init>(Socket.java:209)
at com.mysql.jdbc.StandardSocketFactory.connect(Stand ardSocketFactory.java:253)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:284)
... 8 more

I won't give on this task. It is very important though everything looks more complicated to me now. However, I will appreciate it greatly if you guys can help me out. I really want to be able to use Java to communicate with a MYSQL database on my Apple Mac. If you guys have another way of doing this, I will like to hear it please.

Menre

[Tshegofatsom]

Hi
Everything looks complicated when you start it. Don't worry, you will be fine soon if you practice. Firstly I think its essential to understand why you did the "Run -->Open Run Dialog... -->Classpath (here I selected the folder with my java files) -->Add External JARS... -->A new window opened and I selected the Driver.java file" thing: Every type of database that you connect to should have a driver (some kind of a layer to help you connect to the database). In java this drivers are in a form of jar files and usually required during run time(That is why your program compiles but do not run as expected). You should add this jar file on your class path. This simply expose the contents of the jar file to the java virtual machine. so the vm will see the classes that you are using when connecting to your database.

For the current problem,here is my suggestion:
Your line of code for the database URL reads:
public static final String url = "jdbc:mysql://localhost/NewStudents";
and your exception's cause seems to be:
Caused by: com.mysql.jdbc.CommunicationsException: Communications link failure which is caused by
Caused by: java.net.ConnectException: Connection refused

Try putting a port in your connection string's URL. Normally a default port for my SQL is 3306. So your line of code will read: public static final String url = "jdbc:mysql://localhost:3306/NewStudents";

Don't get intimidated by exceptions, look at the stack trace and make sense of it, That way you can have a clue what the problem might be.
Good luck

[Menre]

Hi Everyone,

Thanks for all your help and replies so far. Thing is, my problem still hasn't been resolved yet, and it has taken all my Easter break time as I have to seat in front of the computer to find a solution to this problem of using Java to connect to the MYSQL database. I do not want to give up. I still need everyone's help to fully solve this problem please.

I have now made some changes to my code and database. Certain things still look very much similar. But now, when I run my program, I do not see anything at all. When I run it from Eclipse, a small java tea cup just pops up at the bottom an disappears quickly. No window opens and nothing for me to see or read. I do not even get any error message anymore except a warning at the bottom of my file in Eclipse, which is the one below.

'The serializable class NewStudents does not declare a static final serialVersionUID field of type long' .

Last time I started this thread, I posted half of the code in this forum. Now I have decided to paste the full code so that you can look at it and see if you can find where thing are going wrong.

I have pasted two code below. One is for the NewStudent class. And the second one is for the runNewStudent class.

1)
//This code below is for the NewStudents class

import java.sql.*;
import javax.swing.*;
import java.awt.*;

public class NewStudents extends JFrame {

//the set attributes
public static final String driver = "org.gjt.mm.mysql.Driver";
public static final String url = "com.mysql.jdbc:mysql://localhost/NewStudents"; //I have added com.mysql.to the start of this line
public static final String username = "question";
public static final String password = "answer";

private Connection con;
private Statement st;
private ResultSet result;
private JTextArea display = new JTextArea(25,25);

//the constructor
public void StudentList() // I have now added void to this line
{
// now configure the frame and text area
setTitle("Registered New Students");
add("Center",display);
setSize(520,250);
setVisible(true);
display.setTabSize(16);
display.setFont (new Font ("DialogInput", Font.BOLD, 14));

try
{
// load the MYSQL jdbc driver
Class.forName("com.mysql.jdbc.Driver"); //I have also added com.mysql.to the start of this line
}

catch(ClassNotFoundException e)
{
display.setText("Driver not found");
}

try
{
// connect to the database
con = DriverManager.getConnection("com.mysql.jdbc:mysql://localhost:3306/NewStudents", "root", "root");
//I have added com.mysql. and port number to the line above

//create an SQL statement
st = con.createStatement();

//execute an SQL query
result = st.executeQuery("select * from students");

// create a heading
display.setText("Student number" + "\t" + "Hall of residence" + "\t" +
"Description" + "\t" + "Fee" + "\n");
display.append("_______" + "\t" + "_____" + "\t" +
"_____" + "\t" + "_____" + "\n");

// display results
while(result.next()) // move to the next record
{
// retrieve and display first field
display.append(result.getString(1) + "\t");
// retrieve and display second field
display.append(result.getString(2) + "\t");
//retrieve and display third field
display.append(result.getString(4) + "\t");
//retrieve and display fourth field
display.append(result.getString(5) + "\t");
}
}

catch(SQLException e) // handle the SQL exception
{
e.printStackTrace();
}

}

}


//That is the end of the NewStudents class


2)
//This code is for the runNewStudent class

public class runNewStudents
{
public static void main(String[] args) {
new NewStudents();
}
}

//the runNewStudents class ends here


I will appreciate if you could help me out please.

Menre

[OrangeDog]

Please post your code in [CODE] tags.

1) You haven't called validate() on the JFrame.
2) It would be much easier to test the database using the console - calling System.out.println() to display things.

[Menre]

Hi,
Thanks, I have learned a new thing today. I didn't know before you could use the code tag. Ok, I have now put my code in the code tag and it is below. If I miss out some thing, could you add it to the code and let me have it please?

Menre

import java.sql.*;
import javax.swing.*;
import java.awt.*;

public class NewStudents extends JFrame {


	//the attributes
	public static final String driver = "org.gjt.mm.mysql.Driver";
	public static final String url = "com.mysql.jdbc:mysql://localhost/NewStudents"; //I have added com.mysql.to the start of this line
	public static final String username = "root";
	public static final String password = "root";
	
	private Connection con;
	private Statement st;
	private ResultSet result;
	private JTextArea display = new JTextArea(25,25);
	
	//the constructor
	public void StudentList()
	{
		// now configure the frame and text area
		setTitle("Registered New Students");
		add("Center",display);
		setSize(520,250);
		setVisible(true);
		display.setTabSize(16);
		display.setFont (new Font ("DialogInput", Font.BOLD, 14));
		
		try
		{
			// load the MYSQL jdbc driver
			Class.forName("com.mysql.jdbc.Driver");		//I have also added com.mysql.to the start of this line
		}
		
		catch(ClassNotFoundException e)
		{
			display.setText("Driver not found");
		}
		
		try
		{
			// connect to the database
			con = DriverManager.getConnection("com.mysql.jdbc:mysql://localhost:3306/NewStudents", "root", "root");
			//I have added com.mysql.to the line above
			
			//create an SQL statement
			st = con.createStatement();
			
			//execute an SQL query
			result = st.executeQuery("select * from students");
			
			// create a heading
			display.setText("Student number" + "\t" + "Hall of residence" + "\t" +
					"Description" + "\t" + "Fee" + "\n");
			display.append("_______" + "\t" + "_____" + "\t" +
					  "_____" + "\t" + "_____" + "\n");
			
			// display results
			while(result.next())  // move to the next record
			{
				// retrieve and display first field
				display.append(result.getString(1) + "\t");
				// retrieve and display second field
				display.append(result.getString(2) + "\t");
				//retrieve and display third field
				display.append(result.getString(4) + "\t");
				//retrieve and display fourth field
				display.append(result.getString(5) + "\t");			
			}		
		  }
		
		catch(SQLException e) // handle the SQL exception
		{
			 e.printStackTrace();
		}
			
	  }
		
   }

//this is for the runNewStudents class

public class runNewStudents 
    {
      public static void main(String[] args) {
    	  new NewStudents();
      }
}

[OrangeDog]

You should really have edited your previous post to include the code tags, instead of posting again. Go back and do that now, then edit your latest post to tell us what happens when you follow either 1) or 2) from my last post.

[Menre]

Hello,

Your suggestions 1 and 2 look somehow too much for me to understand. However, I attempted number one by adding validate () right after JFrame. But when I ran the program, it says syntax errors. Funny thing about it all now is I don't see a thing at all. A tea cup just appears at the bottom of Eclipse and disappears straight away.

I have a feeling the Driver has now been found and that is why it doesn't say Driver not found anymore. But something else is wrong.

If you don't mind, could you amend my code and help add the missing stuff to it so that I can have a look?

Menre

[Menre]

Hello,

Thanks for your help so far. But your suggestions 1 and 2 look somehow too much for me to understand. However, I attempted number one by adding validate () right after JFrame. But when I ran the program, it says syntax errors. Funny thing about it all now is I don't see a thing at all. A tea cup just appears at the bottom of Eclipse and disappears straight away.

I have a feeling the Driver has now been found and that is why it doesn't say Driver not found anymore. But something else is wrong.

If you don't mind, could you amend my code and help add the missing stuff to it so that I can have a look?

Menre

[OrangeDog]

1) If you don't understand what validate() does then read Java Swing Tutorial before you start trying to make graphical applications.

2) If you don't understand what System.out.println() does then start reading here before you do any Java programming.

You appear to have changed which driver you are using. This code uses the paths and values from your latest code post.

import java.sql.*;

public class JDBCTest {

    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("com.mysql.jdbc:mysql://localhost:3306/NewStudents", "root", "root");
            System.out.println("Connection established.");
            Statement st = con.createStatement();
            ResultSet result = st.executeQuery("select * from students");
            System.out.println("SQL query executed.");
            while (result.next()) {
                System.out.printf("%s\t%s\t%s\t%s%n",
                        result.getString(1),
                        result.getString(2),
                        result.getString(4),
                        result.getString(5));
            }
        }
        catch (ClassNotFoundException e) {
            System.out.println("Driver not found");
            return;
        }
        catch (SQLException e) {
            e.printStackTrace();
            return();
        }
    }

}

N.B. I may have made some typos or spelling mistakes so check it first.