Saturday, December 4, 2010

Setup connector/j for JDBC



Setting up MySQL driver connector/j for JDBC in Windows


This is basically not a hard task. Make sure you already have the following things installed and configured in your system, (this demonstration is targeted on the latest versions of the components):

If you don't have these components installed yet, you might try taking some online help on how to install and configure those, not hard as well.

Now, to install java to mysql connector, which is know as connector/j, go to this page and download the windows ZIP package. Unzip the archive anywhere in your pc, it won't matter at all. Now, follow these steps:
  1. Copy the file "mysql-connector-java-5.1.13-bin.jar" to your java installation directory and place it in "..\jdk1.6.0_21\jre\lib\ext".
  2. Now, you have to add this runtime library to your systems classpath environment variable. Go to the properties of My Computer and select the Advanced tab, there you'll see Environment Variables. You will find all your system variables here. Find out the name CLASSPATH and append the location of connector/j i.e. where you just pasted. In my pc, its "C:\Program Files\Java\jdk1.6.0_21\jre\lib\ext\mysql-connector-java-5.1.13-bin.jar". Don't forget to separate this entry from the previous one with e semi-colon. Now click ok and close the dialogue boxes, you are done! But if you don't have the CLASSPATH variable, you need to create it yourself.

That's pretty much all of the work, now we are going to test if it works.

Lets assume that you already have a mysql database named "mysqltest" and you are the "root" user with password "adminadmin" using default host "localhost" with default http port 80, the following code should work then:

import java.sql.*;

public class Main {
public static void main(String[] args) {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver") ;
System.out.println("MySQL JDBC driver loaded ok.");

conn = DriverManager.getConnection("jdbc:mysql://localhost/mysqltest?user=root&password=adminadmin");
System.out.println("Connected with default port.");

DatabaseMetaData meta = conn.getMetaData();
System.out.println("Server name: " + meta.getDatabaseProductName());
System.out.println("Server version: " + meta.getDatabaseProductVersion());
System.out.println("Driver name: " + meta.getDriverName());
System.out.println("Driver version: " + meta.getDriverVersion());
System.out.println("JDBC major version: " + meta.getJDBCMajorVersion());
System.out.println("JDBC minor version: " + meta.getJDBCMinorVersion());

Statement query = conn.createStatement();
int count = query.executeUpdate(
"CREATE TABLE test (" +
"id INT PRIMARY KEY NOT NULL AUTO_INCREMENT," +
"username VARCHAR(20) NOT NULL," +
"password CHAR(40) NOT NULL );"
);
System.out.println("Table created.");

conn.close();
} catch(Exception e) {
System.err.println("Exception: " + e.getMessage());
}
}
}

As you see, you are using "DriverManager.getConnection()" method to connect it using connector/j which you previously specified by "Class.forName("com.mysql.jdbc.Driver") ;" So, make proper changes to test various things. Here, you will get some more methods of connecting and testing.

Well, if this doesn't work for you, then there must be something which is beyond the scope of this post, else "congratulations!".

11 comments:

  1. Thank you so much, it worked!!

    ReplyDelete
  2. Thank you very much. Works perfect.

    ReplyDelete
  3. Do you have any idea why I can't install the mysql java connector.
    I am able to download the windows installer package: mysql-connector-java-gpl-5.1.30

    but when I double click it to install some windows come up for a few seconds and then that's it.
    When I do a search for the jar file that I need or anything else related to the connector I can't find it (i.e. it hasn't installed).

    Any suggestions?

    Thank you

    ReplyDelete
    Replies
    1. Never used the windows installer package myself. just make sure both javac and java commands are accessible via command prompt. Also check if you need to set the CLASSPATH variable.

      Delete
    2. It doesn't even install. there are no files other than the windows installer package

      Delete
    3. Have you tried the zip version and extracting it in the correct directory? I have to check the windows installer, so can't tell right now.

      Delete
    4. my response isn't uploading.

      Delete
  4. I only see the option for installing a Windows Installer Package (.msi) file:
    http://dev.mysql.com/downloads/connector/j/

    Where can I find the zip that you're mentioning?

    Thanks

    ReplyDelete
  5. I am not able to download a zip version, I can just download a windows installer package (.msi), from the following address:

    http://dev.mysql.com/downloads/connector/j/

    Can you tell me where I can find the zip file online and then maybe I can try that option.
    Or maybe I already installed it with some bundle and that's why I'm having trouble now.

    Thanks
    _

    ReplyDelete
  6. I am not able to download a zip version, I can just download a windows installer package (.msi), from the following address:

    (type "download mysql java connector" in google and click on the first result).

    Can you tell me where I can find the zip file online and then maybe I can try that option.
    Or maybe I already installed it with some bundle and that's why I'm having trouble now.

    Thanks

    ReplyDelete