MySQL and Java

[jskopek]

Hey everyone. Been lurking around these forums for a while and finally decided to register. I'm working on a small program that should be relatively easy to create, but I'm having some problems getting started. The project in question is a java application that will connect to an exisitng mysql database and list the values of a certain talbe. It's essentially the simplest kind of databsae connection program you can make, but I'm having a few problems.
From the research I've done it seems like the following steps need to be taken to communicate with a database
1) A JDBC driver is loaded
2) A connection is made with the database
3) Queries are exectued
It seems simple enough (at least when the java documentation explains it: http://java.sun.com/docs/books/tutorial/jdbc/basics/connecting.html), but I haven't found any examples that communicate with MySQL. From my understanding, all that needs to be changed is the JDBC driver, but I have no clue what to load, where to begin, etc.
In short, I'm a little lost and very confused. Have any of you created a java application that communicates with a MySQL database? I promise that if one of you guys help me get an application working I'll post a tutorial on it in the tutorials section
Thanks

[Hikaru79]

Hehe... I'm working on the exact same thing ( a project involving a MySQL backend). First of all, You need to have at least Java 1.2 for JDBC to be in there. I'll assume you do. Then you need to get the JDBC driver for mySQL. If you're on Linux, lucky for you this driver is available on most distributions. On gentoo, I just did 'emerge jdbc-mysql'. For Windows you'll have to get the library yourself and add it to your classpath. It can be downloaded from: http://dev.mysql.com/doc/mysql/en/java-connector.html

If you're using an IDE such as Netbeans, you can use the Library Manager to get it recognized, too. Get this far, make sure that your Java can see the driver, and then reply here so I can go over the next step with you

[JackTruong]

code:

/**  * Make sure you download mmMySQL from the following site:  * http://www.mysql.com/downloads/api-jdbc.html  * Install it into the \lib\ext  * Only put the mysql-connector-java-3.1.8-bin.jar  *  */ import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.sql.*; public class MySQL {     static Connection connection = null;     public static void startConnection () throws Exception     {         try         {             // Load the JDBC driver             String driverName = "com.mysql.jdbc.Driver";             Class.forName (driverName);             // Create a connection to the database             String serverName = "xxx.xxx.xxx.xxx"; // The address to the server.             String mydatabase = "String"; // The database name             String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url             String username = "String";             String password = "String";             // The username and password to the MySQL server, make sure it has access.             connection = DriverManager.getConnection (url, username, password);         }         catch (SQLException e)         {             System.out.println ("Could not connect to the database");         }         finally         {             System.out.println ("Connected.");         }     }     public static void query () throws Exception     {         Statement stmt = null;         ResultSet rs = null;         String theQuery = "SELECT * FROM ibf_members order by id";         try         {             stmt = connection.createStatement ();             stmt.setMaxRows (20);             rs = stmt.executeQuery (theQuery);             if (stmt.execute (theQuery))             {                 rs = stmt.getResultSet ();             }             ResultSetMetaData rsmd = rs.getMetaData ();             int numColumns = rsmd.getColumnCount ();             System.out.println (numColumns);             while (rs.next ())             {                 for (int i = 1 ; i <= numColumns ; i++)                 {                     System.out.print (rs.getString (i));                     if (i != numColumns)                         System.out.print ("\t");                 }                 System.out.println ();             }         }         finally         {             if (rs != null)             {                 try                 {                     rs.close ();                 }                 catch (SQLException sqlEx)                 {                     rs = null;                 }             }             if (stmt != null)             {                 try                 {                     stmt.close ();                 }                 catch (SQLException sqlEx)                 {                     stmt = null;                 }             }         }     }     public static void main (String[] args) throws Exception     {         startConnection ();         query ();     } }

Code I shared with my classmates, with comments. Got this after doing a little search on Google and modified the code a wee bit.

[Hikaru79]

Wow, Jack, I really wish you'd registered about two weeks ago ^_^;; I was having lots of questions about JDBC back then. Welcome aboard to compsci.ca!

[rizzix]

JDBC is the easiest API to use in java.. database apps in java are very cool indeed.

[JackTruong]

Heh, I registered about a month ago, just didn't have time to post.

That's all I know about JDBC, though I do plan to read up on it for my final project.

[jskopek]

hey guys - sorry I wasn't able to update my experiences. I figured out the basic steps on connecting to a database and I have a simple model working (It looks a lot like the code posted earlier too ). All seems great, but then I get this problem:

Using the code:
Connection conn = DriverManager.getConnection
(
"jdbc:mysql:///backend.globalbookmark.com",
"jskopek2",
"PASSWORDREMOVED"
);

to communicate with my online server, I get the error:
Exception in thread "main" java.sql.SQLException: Access denied for user: 'jskopek2@localhost' (Using password: YES)
The obvious problem is the fact that it seems to want to use the username "jskopek2@localhost", a username I cannot set up. Does anyone know a way to get it to communicate with username "jskopek"?
BTW It works great with my local database - seems to just be an issue when communicating with the online database

Oh, and I'll post a tutorial on my escapades as soon as my crunch period is over (try 2 weeks?)

[rizzix]

thats not the problem with the jdbc.. its the way ur account on the online database is setup.. you can only access that database from within that server.. this is a MYSQL permission issue... you might want to contact you admin there.. if necessary.

[JackTruong]

Does your online server use cPanel? You need to set the access host to your IP in order to communicate with it. I'm at school right now, but I'll put up a screenshot when I get back. Most online MySQL servers are only meant to be access from localhost.

[jskopek]

that sounds logical, but the way my hosting company has it set up is kind of odd. It has it's own database servers (the one this particular database is hosted on is called 11xx2912 or something like that) and it's on web hosting servers (so my web page might be on server 32xx0932). It could be that their setup allows access on a LAN but not on a WAN
I'll send them an email and keep you guys posted