wood burning stoves*
The moose likes JDBC and the fly likes Connecting a Java app. to a MySql DB Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connecting a Java app. to a MySql DB" Watch "Connecting a Java app. to a MySql DB" New topic
Author

Connecting a Java app. to a MySql DB

Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802
i know to import java.sql.*;


but how do you "connect" the application to the database??
I need some help big time... i looked at the sun tutorial, but that
drivermanager and driver stuff isnt very clear when you try to use
generic names.

please help, thanks

Justin


You down with OOP? Yeah you know me!
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

It looks confusing because there is a separation between the driver and connection when first connecting to a database. This ends up being very useful in the long run since it helps write code which is independent of the database, but that doesn't help the initial confusion.

To connect to a database (in general) you need these things:

1) The fully qualified class name of the Driver class
2) The Database URL - more in a minute
3) The DB username and password.

(see http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html)

For MySQL the driver class should usually be "com.mysql.jdbc.Driver", but sometimes you'll still see "org.gjt.mm.mysql.Driver"

The username and password you set up yourself.

The MySQL database URL is in the form jdbc:mysql://[host:port],[host:port].../[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

On localhost, on the default port for a database called 'myDatabase' this gives the DB URL:

jdbc:mysql://localhost/myDatabase
although I tend to include the port even if it is standard, as I have sometimes had issues:
jdbc:mysql://localhost:3306/myDatabase

Now to get a connection you do this:

Class.forName(driver_name);
Connection conn = DriverManager.getConnection(DB_URL, username, password);

I haven't put the specific strings into the last part, as it is exactly the same for every DB once you have parts 1, 2 and 3.

I mentioned a separation above, and this is where the DB URL ends up telling the DriverManager which Driver to use to get a Connection. If you go straight to the specific Driver then your code becomes coupled to a Database. If you keep them as Strings - and use the correct values - then they find each other and you can easily move the Strings to a Properties file and suddenly you can change to almost any DB. I theory, anyway.

Dave
Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802

Class.forName(driver_name);
Connection conn = DriverManager.getConnection(DB_URL, username, password);


ok so the driver_name is com.mysql.jdbc.Driver (or the other one) right?

and for the DB_URL, thats the jdbc:mysql://localhost:3306/myDatabase right?
and if this is right, can i create a DB_URL object that points to
"jdbc:mysql://localhost:3306/myDatabase"? and if so would it be like this:

URL DB_URL = new URL("jdbc:mysql://localhost:3306/myDatabase",localhost);
something along those lines, cant remember if the host come first or last.

but for the name and password, do you set that up with the dbms?

Thanks for all the help,

Justin
Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802
this is the code i wrote:



now when i run the program it prints cannont connect to db and
that it cant load the driver...

i imported java.sql.*; and java.sql.Driver.*;

i dont know why it wont work. i have the database folder on the desktop.
so technically the database is in the same folder as the application.

I have no idea what to do.
Thanks,

Justin
Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802
i added .newInstance() to the class.forname thing.

I downloaded the driver "org.gjt.mm.mysql.Driver" but i dont know
how to put the -jar file or the folder containing /org and all of its
components in my classpath... is the classpath the same as path? if
so, its not working..


Justin
Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802
ok i got the driver to load... i just put the downloaded -jar file for the driver in the jre\lib\ext folder

but now i need to get connected to the database. for some reason when i put

jdbc:mysql:://localhost/univreg as the URL

i have the database in the same directory as the application could i just put univreg instead of localhost/univreg ?

Justin
Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802
unable to connect to databasejava.sql.SQLException: Communication failure during handshake. Is there a server running on localhost:3306?


im getting this error when i try to connect to mysql database...
i dont understand really..

i connected to the mysql with..

c:\>mysql -u root -p
password: **********

and then i got in... and use this "syntax"?

mysql> GRANT ALL PRIVILEGES ON *.* TO username@localhost
IDENTIFIED BY 'password' WITH GRANT OPTION;

but when i run the application i get the above first sql error.

help please!!!

Justin
Chad Clites
Ranch Hand

Joined: Aug 16, 2005
Posts: 134
jdbc:mysql:://localhost/univreg


You may want to try something like the following:
private final String DBCONNECT = "jdbc:mysql://localhost/database_name?user=db_user&password=db_password";
(I have a simple utility class that I wrote, and this is how I do it)

Where database_name is the name of the database to which you are trying to connect, db_user is the user that you created, and db_pass is the password.

Your application shouldn't care where the database is. It really doesn't need to. It just hits the port on which Mysql is running. Did you change the default port?
[ August 12, 2007: Message edited by: Chad Clites ]
Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802
nah evidently the port is 3306, i have no idea what to do, ill try do do what you said.

Justin
Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802
i used that string for the url in the connection, but i get the same error.



my string looked liket this "jdbc:mysql://localhost/UNIVREG?user=root&password=adidas"

i dont know if i'm doing this right, or do i need to create a new user in mysql..

maybe root is unaccessable...

please help more if you can,

Justin
Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802
what i dont understand is why i have to go through the server to access a database that is saved on my computer... thats a waste..

Justin
Chad Clites
Ranch Hand

Joined: Aug 16, 2005
Posts: 134
Where did you get your JDBC connector?
Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802
JDBC connector? what is that? the Driver?

i downloaded the org.gjt.mm.mysql.Driver "DRIVER"

my prof said to do this:

mysql> USE UNIVREG;

mysql> GRANT ALL PRIVILEGES ON UNIVREG.* TO username@localhost IDENTIFIED BY 'password';

and that will make it to were i can use (url,"username","password"); as the connection parameters.

but he also said i need to make sure the port 3306 is open... i dont know how to do that, but ill try now..

Thanks chad,

Justin
Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802
i tried those two things, but im still getting the:

connection failure during handshake. is there a server running on localhost:3306?

Error.

Justin
Justin Fox
Ranch Hand

Joined: Jan 24, 2006
Posts: 802
ok i got it to work!!!

the org.gjt.mm.mysql.driver is out of date w/ the newer versions of the mysql server.

but now i have another question...

say i have a login.. and i want to compare the username and password they input to textfields, to their register name and id.

how do i integrate outer strings and inner queries?

thanks,
Justin
[ August 13, 2007: Message edited by: Justin Fox ]
Chad Clites
Ranch Hand

Joined: Aug 16, 2005
Posts: 134
I was thinking that your JDBC driver was out of date. Every thread I looked at was able to correct that particular error by updating their driver.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Connecting a Java app. to a MySql DB
 
Similar Threads
implicit conversion
anyone here a psychiatrist?
PASSED SCWCD EXAM
WA #1.....word association
websphere 6