Make your own free website on Tripod.com

JDBC and MySQL Connectivity

Java programs, applets, servlets, and Java server pages (.jsp) can access MySQL databases using MySQL Connector/J 3.0.6 (formerly MM.MySQL) --MySQL AB's JDBC driver for MySQL. The latest stable driver can be obtained from MySQL.com. In order to allow jsp and servlets to run on your server, you will need to install a servlet engine. This page has instructions on how to configuring Apache 1.3.27 to use mod_perl-1.27, PHP-4.3.1, mod_ssl-2.8.11, OpenSSL-0.9.7a, and the Tomcat 4.1.24 servlet engine.

Sections


Setup the MySQL Database  TOC

A MySQL database is needed for connectivity testing and retrieving data. The commands to create the database, create the database user, and insert the data are not included, but the following shows how to log into MySQL, displays the specifics for the table, and displays the test data, which will be retrieved by a Java program and a Java servlet below.

The command to log into MySQL uses several command line options: -u to specify the username (java_user), -h for the hostname (green), -p to have a password prompt, and specifies which database to use (java_test).

# /usr/local/bin/mysql -u java_user -h green -p java_test
Enter password:
     [MySQL Greeting/Header Cut]
mysql> DESCRIBE java_table;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| item1 | int(10) unsigned | YES  |     | NULL    |       |
| item2 | char(20)         | YES  |     | NULL    |       |
| item3 | char(10)         | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM java_table;
+-------+-------------+------------+
| item1 | item2       | item3      |
+-------+-------------+------------+
|  1233 | Hello World | Item3 text |
|  4545 | DB Works    | Item3 text |
+-------+-------------+------------+
2 rows in set (0.00 sec)

mysql>

Installing Connector/J   TOC

To install the driver, download the driver, untar and decompress it, and install the driver to $JAVA_HOME/jre/lib/ext.

# gzip -cd mysql-connector-java-3.0.7-stable.tar.gz | tar xvf -
# cd mysql-connector-java-3.0.7-stable/
# echo $JAVA_HOME
/usr/java/j2sdk1.4.1
# cp mysql-connector-java-3.0.7-stable-bin.jar $JAVA_HOME/jre/lib/ext

Connecting with a Java Program   TOC

The following is an example Java program to test JDBC connectivity with a MySQL database. Save the following Java code in a file called JDBCtest.java. Replace the variables, such as $HOST and $DB_NAME, with valid values.

import java.io.*;
import java.sql.*;
import javax.sql.*;
import java.util.*;

class JDBCtest {
    
 public static void main(String[] args)   {
   Connection        conn = null;
   Statement         st = null;
   ResultSet         rs = null;
   ResultSetMetaData rmd = null;
   String            query = null;
   
   try{
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      System.out.println("Driver Registration Successful.");
   } catch (InstantiationException ie){
      System.out.println("Class Instantiation Exception: " + ie);
   } catch (ClassNotFoundException cnf){
      System.out.println("Class Not Found Exception: " + cnf);
   } catch (IllegalAccessException iae){
      System.out.println("Illegal Access Exception: " + iae);
   }
  
   try {
      conn = DriverManager.getConnection("jdbc:mysql://$HOST/$DB_NAME
                                  ?user=$USERNAME&password=$PASSWORD");
      System.out.println("Connection to MySQL Database Successful");
   } catch (SQLException sqe1){
      System.out.println("Caught SQL Exception: " + sqe1);
   }
   try {
      st = conn.createStatement();
      query = "SELECT * FROM java_table" ;
      rs = st.executeQuery(query);
      rmd = rs.getMetaData();
      System.out.println("Meta (# of columns): "+rmd.getColumnCount());
         
      while(rs.next()){
          int my_int = rs.getInt("item1");
          System.out.println("Item1 = " + my_int );
          String str1 = rs.getString("item2");
          System.out.println("Item2 = " + str1 );
          String str2 = rs.getString("item3");
          System.out.println("Item3 = " + str2 );
      }
   } catch (SQLException sqe2){
      System.out.println("Caught SQL Exception: " + sqe2);
   }
   
 }//end main

}//end class

Next, compile the program.

# javac JDBCtest.java

Finally, execute the program.

# java JDBCtest
Driver Registration Successful.
Connection to MySQL Database Successful
Meta (# of columns): 3
Item1 = 1233
Item2 = Hello World
Item3 = Item3 text
Item1 = 4545
Item2 = DB Works
Item3 = Item3 text
#

As you can see, the result of this program is to select all the data from the table and display it.


Connecting with a Java Servlet   TOC

The following is an example of a simple Java servlet to test JDBC connectivity with a MySQL database. Save the following Java code in a file called JDBC_servlet.java. Replace the variables, such as $HOST and $DB_NAME, with valid values. This program will only work if you have a servlet engine configured and running -Sun ONE Studio (formerly Forte) has a Tomcat servlet engine included with it and can be used to test this servlet.

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import javax.sql.*;
import java.util.Date;

public class JDBC_servlet extends HttpServlet {
    Connection          conn = null;
    Statement           st = null;
    ResultSet           rs = null;
    ResultSetMetaData   rmd = null;
    String              query = null;

    /** Initializes the servlet. */
    public void init(ServletConfig config) throws ServletException {
        super.init(config);
    }

    private void createConnection(PrintWriter out){
        try{
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            out.println("Driver Registration Successful<br>");
        } catch (InstantiationException ie){
            out.println("class instantiation exception: " + ie);
        } catch (ClassNotFoundException nf){
            out.println("class not found exception: " + nf);
        } catch (IllegalAccessException iae){
            out.println("illegal access exception: " + iae);
        }

        try {
            conn = DriverManager.getConnection("jdbc:mysql://$HOST/"+
                                "$DB_NAME?user=$USERNAME&password=$PASSWORD");
            out.println("Connection Successful<br>");
        } catch (SQLException sq1){
            out.println("Caught SQL exception "+sq1);
        }
    }// End createConnection()

    private void runQuery(PrintWriter out) throws SQLException {
        st = conn.createStatement();
        query = "SELECT * FROM java_table" ;
        rs = st.executeQuery(query);
        rmd = rs.getMetaData();
        out.println("<br>MetaData (# of Columns): "+rmd.getColumnCount()+"<br>");

        while(rs.next()){
            int int1 = rs.getInt("item1");
            out.println("Item1 = " +int1 + "<br>");
            String str1 = rs.getString("item2");
            out.println("Item2 = " +str1  + "<br>");
            String str2 = rs.getString("item3");
            out.println("Item3 = " +str2 + "<br><br>");
        }
    }//end runQuery()

    /** Destroys the servlet. */
    public void destroy() {    }

    /** Processes requests for both HTTP GET and POST methods.*/
    protected void processRequest(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, IOException {
        resp.setContentType("text/html");
        PrintWriter out = resp.getWriter();

        out.println("<html>");
        out.println("<head>");
        out.println("<title>Servlet Conection Example</title>");
        out.println("<style type=\"text/css\">");
        out.println("h1{text-align:center; background:#dddddd;color:#000000}");
        out.println("body{font-family:sans-serif, arial; font-weight:normal}");
        out.println("</style>");
        out.println("</head>");
        out.println("<body>");
        out.println("<h1>Test Database Connectivity</h1>");
        createConnection( out );
        try {
           runQuery(out);
        }catch (SQLException sqe){
           out.println("Caught SQLException from runQuery(): " + sqe);
        }
        out.println("<br><br><hr>" + new Date().toString() );
        out.println("</body>");
        out.println("</html>");
        out.close();
    } //end processRequest()

    /** Handles the HTTP GET method. */
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, IOException {
        processRequest(req, resp);
    } //end doGet()

    /** Handles the HTTP POST method. */
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, java.io.IOException {
        processRequest(req, resp);
    } //end doPost()

    /** Returns a short description of the servlet. */
    public String getServletInfo() {
        return "Short description";
    } //end getServletInfo()

}//end class JDBC_servlet

After the source is compiled and has no errors, copy the .java and .class files to the servlet directory for your servlet engine. If you are uncertain as to this location, consult the documentation for your servlet engine as there is probably a defalut directory for servlets. For the Tomcat server configured on this page, the directory would be ${TOMCAT_HOME}/webapps/examples/WEB-INF/classes with the $TOMCAT_HOME variable being equal to where you installed the Tomcat binary.

If the above configuration is used, the resulting html page produced from this servlet could be accessed at http://localhost/examples/servlet/JDBC_servlet, and will look like the output on this page (this page is the saved output and is not running through a servlet engine on this server).