JDBC connection to MYSQL... 2

[B14speedfreak]

Hi all,

I am trying to learn about how to connect to databases with java using JDBC. Anyhow I have got hold of the connector from mysql, and have produced a small class using the documentation - just connects.

I am using MYSQL 5, Netbeans 3.6, JDK 1.4.2_04, JConnector 5.0.4.

I have included the jar file for jdbc driver in the $JAVA_HOME/lib folder, as well as including the location in the class path and path enviromental OS variables.

Anyhow using netbeans 3.6 I have been able to connect to the database, by defining it under the runtime tab on the left hand frame of the GUI. I can select tables and even execute sql and get results from the database. When I install the driver, the driver is defined as org.gjt.mm.mysql.Driver, under the MYSQL documentation they think that I should be using com.mysql.jdbc.Driver.

This is my script:

package music_connect;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Connect {
public static void main(String[] args){
try{
Class.forName("com.mysql.jdbc.Driver");
}catch (Exception ex){

}try{
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/music?user=root&password=126120");
System.out.println("Connected");
conn.close();
System.out.println("connection closed");
}catch (SQLException exer){
System.out.println("SQLException: " + exer.getMessage());
System.out.println("SQLState: " + exer.getSQLState());
System.out.println("VendorError: " + exer.getErrorCode());
}
}
}

*****************////// results when executed////////////

SQLException: No suitable driver
SQLState: 08001
VendorError: 0



Has anyone got any ideas how I can resolve this?

Thanks,






B14... aka... Marky Mark... the frozen monkey in the server room...

 

[timw]

Try doing

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

instead of

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

I don't think I've ever needed to do this to get it working, but the J/Connector readme mentions it may be necessary depending upon which Java you're using.

Tim

 

[Diancecht]

Appart from that, I don't think it's a good idea to place application jar files into the lib folder of the JRE.

Keep them in a sepparate directory.

Cheers,
Dian

 

[timw]

I second that.

Tim

 

[timw]

... in fact, here's a idea. If you've got it in the JRE's lib directory AND you've put it onto the regular classpath, maybe this is confusing the Driver Manager.

Tim

 

[sedj]

You do not need to do a newInstance() - this will have no effect - this is fine :

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

The problem I suspect is that the driver jar file is not on the CLASSPATH.

The main issue I have with that code is that you hide the exception thrown from the above line - so I suspect you are getting a ClassNotFoundException - but you are hiding it.

Also I would say your getConnection() method should be defined as :

DriverManager.getConnection("jdbc:mysql://localhost:3306/music", "root", "126120");



--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[B14speedfreak]

Hi All,

Firstly, thanks for all the replys they are very useful to me.

Ok, I have taken the jar file out of the /lib on the Java home dir.

Being that I am on Win XP (would rather be using something else but what can you do), I have added the full path to the JConnect.jar file into System CLASSPATH and user CLASSPATH.

I have also altered my class as follows:

package music_connect;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Connect {
public static void main(String[] args){
try{
System.out.println("loading driver");
Class.forName("com.mysql.jdbc.Driver").newInstance();
}catch (Exception ex){
System.out.println("Message: " + ex.getMessage());
System.out.println("Cause: " + ex.getCause());
}try{
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/music", "root", "126120");
System.out.println("Connected");
conn.close();
System.out.println("connection closed");
}catch (SQLException exer){
System.out.println("SQLException: " + exer.getMessage());
System.out.println("SQLState: " + exer.getSQLState());
System.out.println("VendorError: " + exer.getErrorCode());
}
}

********output************************

loading driver
Message: com.mysql.jdbc.Driver
Cause: null
SQLException: No suitable driver
SQLState: 08001
VendorError: 0



I can still register the driver in netbeans and connect to it using the database schema.

Any ideas anyone?

Thanks again for all the replys,




B14... aka... Marky Mark... the frozen monkey in the server room...

 

[B14speedfreak]

Just changed the class again:



package music_connect;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Connect {
public static void main(String[] args){
try{
System.out.println("loading driver");
Class.forName("com.mysql.jdbc.Driver").newInstance();
}catch (Exception ex){
System.out.println("Message: " + ex.getMessage());
System.out.println("Cause: " + ex.getCause());
System.out.println(ex.getClass());
}try{
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/music", "root", "126120");
System.out.println("Connected");
conn.close();
System.out.println("connection closed");
}catch (SQLException exer){
System.out.println("SQLException: " + exer.getMessage());
System.out.println("SQLState: " + exer.getSQLState());
System.out.println("VendorError: " + exer.getErrorCode());
}
}
}

am now getting the following output:

loading driver
Message: com.mysql.jdbc.Driver
Cause: null
class java.lang.ClassNotFoundException
SQLException: No suitable driver
SQLState: 08001
VendorError: 0



I don't know why its not picking up the driver though, but I will look into it.

Thanks again everyone...

B14... aka... Marky Mark... the frozen monkey in the server room...

 

[B14speedfreak]

Hi guys,

thanks again for the all the replys. As discusssed above, it was a problem with the driver not being put into the class path correctly.

I can now connect to the databases.

Thanks again for all your help,


B14... aka... Marky Mark... the frozen monkey in the server room...