Connect MySQL From Java (JDBC)


Download MySQL Server: 

The main download page for both the database server and the connector as of September 2004 is http://dev.mysql.com/downloads/
Several different versions of the database server are available for downloading:

MySQL 5.0 — Generally Available (GA) release for production use

Upcoming Releases:
MySQL 5.1 — Release Candidate release, Test new features early!
MySQL 6.0 — Alpha – Falcon Preview New!
Snapshots — source code snapshots of the development trees
Older Releases:
MySQL 4.1 — Previous GA release
Archives of Older Releases

This list can be expected to change over time as new versions of the database server are released.  Thus, the links in the above list will become obsolete.  When that happens, you should revert back to the download page at http://dev.mysql.com/downloads/ and download the version that best suits your needs at that time.

Download MySQL Connector J:http://dev.mysql.com/downloads/index.html 

MySQL Connector/J — for connecting to MySQL from Java

I think you will find many tutorials which will guide you through the MySQL Server installation process. Istallation of MySQL Server is very much easy. But one thing I like to add that donot forgot to add mysql bin directory to the Windows PATH variable.

Eclipse: For Java editing

You must have some Java editors. But I like ecplise (http://www.eclipse.org). Download and install ecplise. http://www.eclipse.org/downloads/download.php?file=/technology/epp/downloads/release/20070927/eclipse-java-europa-fall-win32.zip

Installation of ecplise is very easy. Just upzip the package anywhere and run ecplise.exe.

Now we will create a Project named DemoMySQLJavaConnectivity . (I think you will find it very easy to create a project in ecplise. I will think about writing a blog that will show us how to create a Java Project in ecplise. )

First create a package named:  demo.mysql.java.connectivity

Now I will create a Java source file named ConnectToMySQLSimple.java

package demo.mysql.java.connectivity;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectToMySQLSimple {

 public static void main(String args[]) {
  Connection con = null;

  try {
   Class.forName(“com.mysql.jdbc.Driver”);
   String url = “jdbc:mysql://localhost:3306/mytestdb”;

   // Get a connection to the database for a
   // user named auser with the password
   // drowssap, which is password spelled
   // backwards.
   con = DriverManager.getConnection(url, “root”,”mypass”);
   Statement st = con.createStatement();
   ResultSet rs = st.executeQuery(“select * from mytable”);
   while (rs.next()) {
    System.out.println(“Id ” + rs.getString(1));
    System.out.println(“Val ” + rs.getString(2));
   }
  } catch (Exception e) {
   e.printStackTrace();
   System.out.println(“Exception: ” + e.getMessage());
  } finally {
   try {
    if (con != null)
     con.close();
   } catch (SQLException e) {
    
   }
  }
 }
}
Now complie this code and run.
Explanation: First we have to add mysql-connector-java-5.1.5-bin.jar to the CLASSPATH.
Then we should look at the code ->

Class.forName(“com.mysql.jdbc.Driver”);
We know that this line returns the Class object associated with the class or interface with the given string name, using the given class loader, which is mysql is this example.

String url = “jdbc:mysql://localhost:3306/mytestdb”;

Now we will define a JDBC URL. Now MySQL Server runs on port 3306 by default. If your server runs on another port other that 3306 then you should put that port instead of 3306. Now localhost is  for your local machine. If your mysql server runs on another host then you should put that in place of localhost. Like jdbc:127.0.0.1:3306/mytestdb

And lastly mytestdb is the database we want to connect using this code.

And now we will gett he connection by 
   con = DriverManager.getConnection(url, “root”,”mypass”);
where root is my mysql server user name and mypass  is my mysql server password for root.

And now,

Statement st = con.createStatement();
ResultSet rs = st.executeQuery(“select * from mytable”);

here we create a statement object and executes the query which will return a Resultset object.

Now if I create the table : mytable as,

CREATE TABLE mytable (id INT(4) AUTO_INCREMENT, Value VARCHAR(20));
and then insert some rows there,

INSERT INTO mytable(Value) VALUES(“Root”);
INSERT INTO mytable(Value) VALUES(“UBK”);
INSERT INTO mytable(Value) VALUES(“MySQL”);

Now if we execute the source java code given earlier, then it will return all the ID and Values stored in mytable of Database mytestdb, using the portion:
while (rs.next()) {
    System.out.println(“Id ” + rs.getString(1));
    System.out.println(“Val ” + rs.getString(2));
   }

Next we will see some advanced examples:

package demo.mysql.java.connectivity;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DemoMySQLJavaAdvanced {
 public static void main(String args[]) {
  try {
   Statement stmt;
   ResultSet rs;

   // Register the JDBC driver for MySQL.
   Class.forName(“com.mysql.jdbc.Driver”);

   // Define URL of database server for
   // database named JunkDB on the localhost
   // with the default port number 3306.
   String url = “jdbc:mysql://localhost:3306/mytestdb”;

   // Get a connection to the database for a
   // user named auser with the password
   // drowssap, which is password spelled
   // backwards.
   Connection con = DriverManager.getConnection(url, “root”,”mypass”);

   // Display URL and connection information
   System.out.println(“URL: ” + url);
   System.out.println(“Connection: ” + con);

   // Get a Statement object
   stmt = con.createStatement();

   // As a precaution, delete myTable if it
   // already exists as residue from a
   // previous run. Otherwise, if the table
   // already exists and an attempt is made
   // to create it, an exception will be
   // thrown.
   
   //Query to drop table;
   try {
    stmt.executeUpdate(“DROP TABLE myTable”);
   } catch (Exception e) {
    System.out.print(e);
    System.out.println(“No existing table to delete”);
   }// end catch
   
   // Create a table in the database named
   // myTable.
   
  stmt.executeUpdate(“CREATE TABLE myTable(test_id int,”+ “test_val char(15) not null)”);
           
   // Insert some values into the table
   stmt.executeUpdate(“INSERT INTO myTable(test_id, ”
     + “test_val) VALUES(1,’One’)”);
   stmt.executeUpdate(“INSERT INTO myTable(test_id, ”
     + “test_val) VALUES(2,’Two’)”);
   stmt.executeUpdate(“INSERT INTO myTable(test_id, ”
     + “test_val) VALUES(3,’Three’)”);
   stmt.executeUpdate(“INSERT INTO myTable(test_id, ”
     + “test_val) VALUES(4,’Four’)”);
   stmt.executeUpdate(“INSERT INTO myTable(test_id, ”
     + “test_val) VALUES(5,’Five’)”);

   // Get another statement object initialized
   // as shown.
   stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
     ResultSet.CONCUR_READ_ONLY);

   // Query the database, storing the result
   // in an object of type ResultSet
   rs = stmt.executeQuery(“SELECT * ”
     + “from myTable ORDER BY test_id”);

   // Use the methods of class ResultSet in a
   // loop to display all of the data in the
   // database.
   System.out.println(“Display all results:”);
   while (rs.next()) {
    int theInt = rs.getInt(“test_id”);
    String str = rs.getString(“test_val”);
    System.out.println(“\ttest_id= ” + theInt + “\tstr = ” + str);
   }// end while loop

   // Display the data in a specific row using
   // the rs.absolute method.
   System.out.println(“Display row number 2:”);
   if (rs.absolute(2)) {
    int theInt = rs.getInt(“test_id”);
    String str = rs.getString(“test_val”);
    System.out.println(“\ttest_id= ” + theInt + “\tstr = ” + str);
   }// end if

   // Delete the table and close the connection
   // to the database
   //stmt.executeUpdate(“DROP TABLE myTable”);
   con.close();
  } catch (Exception e) {
   e.printStackTrace();
  }//end catch
 }//end main
}//end class DemoMySQLJavaAdvanced

I think this will help you to get what you need primarily about mysql java connectivity.
You will find this project here.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s