Using MySQL 5.0 with Java

In this article, I show you how to connect to MySQL 5.0 from Java.  We will see how to install MySQL 5.0 and use JDBC driver for MySQL 5.0.

Installing MySql 5.0

Follow the steps given below to download and install MySql 5.0 :
  1. Go to www.mysql.com website.
  2. Select MySQL 5.0 Community Server
  3. Select download link and select "Windows Essentials(x86)"
  4. After file is downloaded, double click on .msi file and start installing MySQL 5.0
  5. Connect to MySQL using MySQL -> MySQL Server 5.0 -> MySQl Command Client
  6. When it prompt you to enter password, just enter password that you gave at the time of installation for "root" user
  7. Give "Use MySQL" command at "MySql>" command prompt
  8. You are now connected to MySQL database
  9. Create some tables and other objects. For syntax details, see MySQL Manual using MySQL->MySQL Server 5.0-> MySql Manaual.

Once you installed and created required objects in MySQL, use the following steps to download .NET provider for MySQL.

  1. Select Download link in  mysql.com home page and then select Connectors link in the home page of MySql.com
  2. Select "Connector/J" link
  3. Select Source and Binaries(ZIP) link. It will download  mysql-connector-java-5.0.6.zip, which is approximately  8 MB"
  4. Unzip .ZIP file to get  mysql-connector-java-5.0.6-bin.jar file.
  5. If you are using IDEs such as NetBeans then add this .jar file to libraries of the project. If you are using command line to compile Java programs then use classpath  to point to this .jar file.

The following program loads JDBC driver and connects to mysql database in MySQL 5.0 running on the localhost (current system).

import java.sql.Connection;
import java.sql.DriverManager;

public class ConnectToMySql {
    
    public static void main(String[] args) throws Exception {
    
       Class.forName("com.mysql.jdbc.Driver");
       /* mysql is the name of the database.
          Substitute mysql with name of database, in case you want to connect to a different database
       */   
       Connection con =  DriverManager.getConnection("jdbc:mysql://localhost/mysql?user=root&password=srikanth");
       System.out.println("Connected To MySql 5.0");
       con.close();
    }
}

The following program is used to retrieve data from ADDRESSES table and displays NAME and EMAIL columns of the table.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ListAddresses {
    public static void main(String[] args) throws Exception {
       Class.forName("com.mysql.jdbc.Driver");
       Connection con =  DriverManager.getConnection("jdbc:mysql://localhost/mysql?user=root&password=srikanth");
       Statement st = con.createStatement();
       ResultSet rs = st.executeQuery("select * from addresses");
       while ( rs.next()) {
           System.out.println( rs.getString("name") + ":" + rs.getString("email"));
       }
       rs.close();
       st.close();
       con.close();
    }
}
The next program shows how to call a stored procedure of MySQL. Stored procedure is a program that is stored in the database. It can be called from front-ends like Java program.

First we have to create a stored procedure by connecting to MySQL. Create a procedure with the following code:

DELIMITER $$

CREATE PROCEDURE ChangeEmail(p_name varchar(30), p_email varchar(50))
BEGIN
    UPDATE addresses SET email= p_email where name = p_name;
END $$

DELIMITER ;
First and last lines in the above code are used to change the delimiter in MySQL Command Line.

Procedure ChangeEmail is used to change email address of the given name. Now, let us see how to call this procedure from Java.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class CallProcedure {
    public static void main(String[] args) throws Exception {
       Class.forName("com.mysql.jdbc.Driver");
       Connection con =  DriverManager.getConnection("jdbc:mysql://localhost/mysql?user=root&password=srikanth");
       CallableStatement cs = con.prepareCall("{ call ChangeEmail(?,?)}");
       cs.setString(1,"P.Srikanth");
       cs.setString(2,"srikanthpragada@yahoo.com");
       cs.execute();
       cs.close();
       con.close();
    }
}

The above sample programs have given some idea about how to use MySQL from Java. For more details, visit mysql.com.

NOTE: You can download MySQL Query Browser from MySQL GUI Tools link. It will make working with objects like table, procedure etc. easy.

Keep Learning,
Srikanth.