Thread: Problems connecting to mysql database on network

Hi there,

I'm currently teaching myself Java from a for dummies book. However I have currently got stuck on the section to connect to a mysql database, in the book it assumes that the database is on a local machine however in my case it is on my server. Both machines are running Linux and my workstation has mysql-jdbc installed on it.

Here is a copy of the code with the user name and password changed

import java.sql.*;
import java.text.NumberFormat;
 
public class ListMovies
{
  public static void main(String[] args)
  {
    NumberFormat cf = NumberFormat.getCurrencyInstance();
 
    ResultSet movies = getMovies();
    try
    {
      while (movies.next())
      {
	Movie m = getMovie(movies);
	String msg = Integer.toString(m.year);
	msg += ": " +m.title;
	msg += " (" + cf.format(m.price) + ")";
	System.out.println(msg);
      }
    }
    catch (SQLException e)
    {
      System.out.println(e.getMessage());
    }
  }
 
  private static ResultSet getMovies()
  {
    Connection con = getConnection();
    try
    {
      Statement s = con.createStatement();
      String select = "Select title, year, price " + "from movie order by year";
      ResultSet rows;
      rows = s.executeQuery(select);
      return rows;
    }
    catch (SQLException e)
    {
      System.out.println(e.getMessage());
    }
    return null;
  }
 
  private static Connection getConnection()
  {
    Connection con = null;
 
    try 
    {
      Class.forName("com.mysql.jdbc.Driver");
      String url = "jdbc:mysql://myserver:3306/movies";
      String user = "user";
      String pw = "password";
      con = DriverManager.getConnection(url, user, pw);
    }
 
    catch (ClassNotFoundException e)
    {
      System.out.println(e.getMessage());
      System.exit(0);
    }
    catch (SQLException e)
    {
      System.out.println(e.getMessage());
      System.exit(0);
    }
    return con;
  }
 
  private static Movie getMovie(ResultSet movies)
  {
    try
    {
      String title = movies.getString("title");
      int year = movies.getInt ("year");
      double price = movies.getDouble("price");
      return new Movie(title, year, price);
    }
    catch (SQLException e)
    {
      System.out.println(e.getMessage());
    }
 
    return null;
  }
 
  private static class Movie
  {
    public String title;
    public int year;
    public double price;
    public Movie(String title, int year, double price)
    {
      this.title = title;
      this.year = year;
      this.price = price;
    }
  }
}

however when it is run the output i get is the following

com.mysql.jdbc.Driver

I realise that i'm probably missing something really simple but after several hours of looking i can't work it out

how do I go about doing that? sorry to sound thick!

right I think i get it

I have changed the code to show the following

 catch (ClassNotFoundException e)
    {
      System.out.println(e.getMessage());
      e.printStackTrace();
      System.exit(0);

and the output that I am receiving is the following

com.mysql.jdbc.Driver
java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
        at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
        at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:423)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:356)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:186)
        at ListMovies.getConnection(ListMovies.java:52)
        at ListMovies.getMovies(ListMovies.java:30)
        at ListMovies.main(ListMovies.java:10)

Now to hit google to find out what the problem is, assuming this is what the problem is!

Thanks for everyones help so far

It seems that it is an issue with the jdbc driver not being in the classpath. Now to work out how to get around this problem, there seems to be some debate as to whether or not it's wise to add it into the classpath as it's a global variable.

the relevent file appears to be /usr/share/java/mysql-jdbc/mysql-connector-java-5.1.18-bin.jar

Originally Posted by aussiemcgr

Did you download the jdbc driver and add it to your project's classpath?

I'm using a text editor and bash rather than a full blown IDE to do this as my book doesn't cover any editors and they look a bit scary!

I have got it working (well i have got it working to the point that the server rejects the request) by setting $CLASSPATH with the following

CLASSPATH=.:../:/usr/share/java/mysql-jdbc/*
export CLASSPATH

does anyone know how to get $CLASSPATH to be set on boot? because every time I reboot I have to reset $CLASSPATH

thank you for all your help so far