Problems connecting to MySQL from Java

Magown3

Hi,

I'm trying to connect a java program to a MySQL file. The problem I keep getting is that no driver is supported!

For MySQL databases, do you have to register them with the ODBC Data Source Administration like you have to with Access? I tried this but I couldn't find a MySQL driver.

Here is the java code I'm using to load the driver along with the Exception that is returned:

try {
	Class.forName("com.mysql.jdbc.Driver").newInstance();
} 
catch (Exception ex) {
	System.out.println("Problem loading the driver!");
// handle the error
}

I've also tried using Connector-j without success.

Cheers.

OMcGovern

You have to download the JDBC driver class from the MySQL site, and add it to your classpath. eg..

java -cp .;mysql-connector-java-3.1.12-bin.jar MyDatabaseClass

Then, register the driver programatically...

regards,
Owen

try
{
        Connection conn = null;
	Object driverObject = Class.forName("com.mysql.jdbc.Driver").newInstance();
    if ( driverObject instanceof java.sql.Driver )
    {
          Driver driver = (Driver)driverObject;   
          DriverManager.registerDriver ( driver );
          String url = "jdbc:mysql://localhost:3306/mysql";
          String username = "root";
          String password = "password";   //  <-- change to your MySQL password

          conn = DriverManager.getConnection ( url, username, password );
    }
    else
    {
          System.out.println ("That class was not a Jdbc Driver");
    }
        
} 
catch (Exception ex)
{
	System.out.println("Database problem [" + ex.getMessage( )+ "]");
// handle the error
}

OMcGovern

PS. JDBC database access has NOTHING to do with ODBC.

Okay... for the pedantic nerds out there... there are rare case where you want to access an ODBC datasource through JDBC, via the JDBC-ODBC bridge.
But 99% of the time, you will want to use a JDBC driver to talk directly to the database, as my example above shows.

regards,
Owen

Magown3

Thanks for the reply. Unfortunately I'm still catching an error!

I've used your code from above except for this line:

String url = "jdbc:sql://localhost:3306/ASSIGNMENT02";

ASSIGNMENT02 is the name of my database.... I presume this is correct!

The output in the dos window says:
"Database problem [com.mysql.jdbc.Driver]"

M

pH

jdbc:mysql
!

Magown3

pH wrote:

jdbc:mysql
!

Thanks very much OMcG AND pH...
Greatly appreciated.:D

Magown3

Is it possible to enter sql commands into a txt file and then read them from java like the second example below? I thought this would be possible because it's the same code that you enter from the mysql> command line.

Statement st = conn.createStatement();

st.executeUpdate("INSERT INTO CALLS " +
	"VALUES ('1001', 'Simpson', 'Mr.', 'Springfield', '2001','test')");

st.executeUpdate("\\. C:\\sqlcommands.txt");

pH

No that won't work, but it's relatively trivial to open a text file and "execute" every line. Parsing multiple lines for a specific end of command delimiter ( ; ) will be a small bit more work.

Magown3

pH wrote:

No that won't work, but it's relatively trivial to open a text file and "execute" every line. Parsing multiple lines for a specific end of command delimiter ( ; ) will be a small bit more work.

I've been trying to do this all last night and all today but my java skills just dont seem to be up to it.

Could anyone give me a hand here??

Thanks

pH

The easiest way to process a text file is to use a BufferedReader on a FileReader:

String fileName = "/tmp/commands.sql";
BufferedReader b = new BufferedReader( new FileReader(fileName) );

Process the file until readLine is null

     String line = "";
     while( ( line = b.readLine() ) != null) {
            statement.executeQuery(line);
        }

There's a few exceptions to catch but you should get the general idea. If a command spans multiple lines then you will need some form of buffer checking for lines ending with a ";"