mysql java driver JDBC


mysql-connector-java-5.1.22.zip


mysql-connector-java-5.1.22.zip

Here's a step by step explanation how to install MySQL and JDBC and how to use it:

  1. Download and install the MySQL server. Just do it the usual way. Remember the port number whenever you've changed it. It's by default 3306.

  2. Download the JDBC driver and put in classpath, extract the ZIP file and put the containing JAR file in the classpath. The vendor-specific JDBC driver is a concrete implementation of the JDBC API(tutorial here).

    If you're using an IDE like Eclipse or Netbeans, then you can add it to the classpath by adding the JAR file as Library to the Build Path in project's properties.

    If you're doing it "plain vanilla" in the command console, then you need to specify the path to the JAR file in the -cp or -classpath argument when executing your Java application.

    java -cp .;/path/to/mysql-connector.jar com.example.YourClass

    The . is just there to add the current directory to the classpath as well so that it can locatecom.example.YourClass and the ; is the classpath separator as it is in Windows. In Unix and clones : should be used.

  3. Create a database in MySQL. Let's create a database javabase.

    CREATE DATABASE javabase DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  4. Create an user for Java and grant it access. Simply because using root is a bad practice.

    CREATE USER 'java'@'localhost' IDENTIFIED BY 'd$7hF_r!9Y';
    GRANT ALL ON javabase.* TO 'java'@'localhost' IDENTIFIED BY 'd$7hF_r!9Y';

    Yes, java is the username and d$7hF_r!9Y is the password here.

  5. Determine the JDBC URL. To connect the MySQL database using Java you need an JDBC URL in the following syntax:

    jdbc:mysql://hostname:port/databasename
    • hostname: The hostname where MySQL server is installed. If it's installed at the same machine where you run the Java code, then you can just use localhost. It can also be an IP address like 127.0.0.1. If you encounter connectivity problems and using 127.0.0.1instead of localhost solved it, then you've a problem in your network/DNS/hosts config.

    • port: The TCP/IP port where MySQL server listens on. This is by default 3306.

    • databasename: The name of the database you'd like to connect to. That's javabase.

    So the final URL should look like:

    jdbc:mysql://localhost:3306/javabase
  6. Test the connection to MySQL using Java. Create a simple Java class with a main() method to test the connection.

    I. First we need to load the JDBC driver:

    try {
        System.out.println("Loading driver...");
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Driver loaded!");
    } catch (ClassNotFoundException e) {
        throw new RuntimeException("Cannot find the driver in the classpath!", e);
    }

    Note that the newInstance() call is not needed here. It's just to fix the old and buggyorg.gjt.mm.mysql.DriverExplanation here. If this line throws ClassNotFoundException, then the JAR file containing the JDBC driver class is simply not been placed in the classpath.

    Note that you don't need to load the driver everytime before connecting. Just only once during application startup is enough.

    II. Then we can get a connection:

    String url = "jdbc:mysql://localhost:3306/javabase";
    String username = "java";
    String password = "d$7hF_r!9Y"
    Connection connection = null;
    try {
        System.out.println("Connecting database...");
        connection = DriverManager.getConnection(url, username, password);
        System.out.println("Database connected!");
    } catch (SQLException e) {
        throw new RuntimeException("Cannot connect the database!", e);
    } finally {
        System.out.println("Closing the connection.");
        if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
    }

    If you get a SQLException: No suitable driver, then it means that either the JDBC driver wasn't loaded at all or that the JDBC URL is wrong (i.e. it wasn't recognized by any of the loaded drivers).

    If you get a SQLException: Connection refused or Connection timed out or a MySQL specific CommunicationsException: Communications link failure, then it means that the DB isn't reachable at all. This can have one or more of the following causes:

    1. IP address or hostname in JDBC URL is wrong.
    2. Hostname in JDBC URL is not recognized by local DNS server.
    3. Port number is missing or wrong in JDBC URL.
    4. DB server is down.
    5. DB server doesn't accept TCP/IP connections.
    6. DB server has run out of connections.
    7. Something in between Java and DB is blocking connections, e.g. a firewall or proxy. 

    To solve the one or the other, follow the following advices:

    1. Verify and test them with ping.
    2. Refresh DNS or use IP address in JDBC URL instead.
    3. Verify it based on my.cnf of MySQL DB.
    4. Start the DB.
    5. Verify if mysqld is started without the --skip-networking option.
    6. Restart the DB and fix your code accordingly that it closes connections in finally.
    7. Disable firewall and/or configure firewall/proxy to allow/forward the port. 

    Note that closing the Connection is extremely important. If you don't close connections and keep getting a lot of them in a short time, then the database may run out of connections and your application may break. Always acquire and close the Connection in the shortest possible scope in a try-catch-finally block. Closing in finally is just to ensure that it get closed as well in case of an exception. This also applies to StatementPreparedStatement and ResultSet.

That was it as far the connectivity concerns. You can find here a more advanced tutorial how to load and store fullworthy Java model objects in a database with help of a basic DAO class.

share|improve this answer
5 
Great answer. +1 – Thiago Silveira Jun 6 '10 at 17:41
there are some ERRORS with mysql-5.1.49-win32 installation , so you need to do the following before installation 1. turn off ur firewall. 2. go to run command(win+R) type "services.msc" 3. select mysql 4. usually it maybe off. start the service. 5. change manual to automatic. 6. turn off your Antivirus(was required in my case) 7. save and exit. 8. While creating an instance configuration for MYSQL (done after installing) , do the following :- a)change the port no to 3309 from 3306 b)add firewall exception to the port . 9. signin to mysql. 10. Sucessfully loged in. – Gaurav Aug 13 '10 at 21:37
Can't we use Singleton pattern for creating connections? – Bhavik Shah Feb 1 at 6:03

Here's the very minimum you need to get data out of a MySQL database:

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection
   ("jdbc:mysql://localhost:3306/foo", "root", "password");

Statement stmt = conn.createStatement();
stmt.execute("SELECT * FROM `FOO.BAR`");
stmt.close();
conn.close();

Add exception handling, configuration etc. to taste.

share|improve this answer
String url = "jdbc:mysql://127.0.0.1:3306/yourdatabase";
String user = "username";
String password = "password";

// Load the Connector/J driver
Class.forName("com.mysql.jdbc.Driver").newInstance();
// Establish connection to MySQL
Connection conn = DriverManager.getConnection(url, user, password);
share|improve this answer
what is yourdatabase in here? database name? – Koray Tugay Mar 24 at 0:04
I will answer my own question: It is "yourdatabase" – Koray Tugay 3 hours ago

You can see all steps to connect MySQL database from JAVA application here. For other database, you just need to change the driver in first step only. Please make sure that you provide right path to database and correct username and password.

Visit http://apekshit.com/t/51/Steps-to-connect-Database-using-JAVA

share|improve this answer