Java/JDBC Example

JDBC, Java Database Connectivity, is the protocol that Java uses to connect to databases.  In order for it to work a JDBC driver for the database you want to connect to must be available in your CLASSPATH.  In Java the CLASSPATH is an environmental variable that has the OS specific directory paths to where your classes can be found.  The JDBC driver is in fact a class or several classes.  All Java classes have a ".class" extension".  In the event that the driver requires several classes they are often zipped together using the Java ARchiver (JAR) with an extension of ".jar".  In the case of MySQL, the JDBC driver is called:  mysql-connector-java-5.0.4-bin.jar  - Because it has a jar extension it is actually several classes zipped up together.  In order for you to connect to an MySQLdatabase, this file needs to be in your CLASSPATH.  For example if the above file exists /home/timlin/classes/mysql-connector-java-5.0.4-bin.jar, then this entire file name must be part of my CLASSPATH.  In Unix or Linux you set this as such.

Topics:

 

Setup:

Environmental Variables:

1.  JAVA_HOME -- Should already be setup when you install Java.

2.  PATH -- You will need to add the java.exe and javac.exe to your PATH.  Probably already done when you install Oracle.

3.  CLASSPATH -- Probably not setup yet.  You can set this dynamically at run time or you can set it up in your computer.

To test each from a command prompt.

From Windows:  [Start] ==> [Run] ==> cmd

C:\Windows>  echo %JAVA_HOME%   

C:\Windows>  PATH

To set any of these in Windows you will have to do so through Windows Control Panel

[Start] ==> [Settings] ==> [Control Panel] ==> [System] ==> [Advanced Tab] ==> [Environmental Variables]

A really good way to play with Java is to go to the Sun website (www.sun.com) and download the Java/NetBeans bundle. 

 

Next you will need to download the Oracle JDBC driver.

Windows, click here to download the mysql-connector-java-5.0.4-bin.jar

C:\classes> notepad ListCustomers.java

-- Copy and paste the following code:

import java.sql.*;  // Import the JDBC Driver

public class ListCustomers {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test";  // The database URL.

        listCustomers (url);
    }

    public static void listCustomers(String url)
    {
        Connection conn = null;
        Statement statement = null;
        try {
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());  // Register the JDBC driver.  Method One.

            conn = DriverManager.getConnection(url, null, null);  // Open the Database Connection.

            System.out.println ("Customer Table\n");

            statement = conn.createStatement();  // Create a JDBC Statement Object.
            String query = "SELECT customer_number, lname, fname FROM customer";

            ResultSet rst = statement.executeQuery(query);  // Create & Populate a ResultSet object.

            System.out.println();

            while(rst.next()) {  // Loop through the ResultSet row by row.
                System.out.print(rst.getInt(1) + "\t");
                System.out.print(rst.getString(2) + "\t");
                System.out.println(rst.getString(3) + "\t");
            }
        } catch (SQLException sqle) {
            System.out.println("SQL Error: " + sqle);
        } catch (Exception e) {
            System.out.println("Error: " + e);
        } finally {
            try {
                if (statement != null)
                    statement.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
    }
}
// End of File

-- Instructions continue below.

C:\classes> javac -classpath c:\classes\mysql-connector-java-5.0.4-bin.jar ListCustomers.java

C:\classes> java -cp c:\classes\mysql-connector-java-5.0.4-bin.jar;c:\classes ListCustomers

A More Dynamic Version:

-- Copy and paste the following code:

-- Instructions continue below.

import java.sql.*;

public class ListCustomers2 {
    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/test";  
        String driver="com.mysql.jdbc.Driver";

        listCustomers (driver, url, null, null);
    }

    public static void listCustomers(String driver, String url,
        String username, String password)
    {
    	Connection conn = null;
    	Statement statement = null;
        try {
            Class.forName(driver).newInstance();  // Register the JDBC driver.  Method Two

            conn = DriverManager.getConnection(
                url, username, password);

            System.out.println ("Customer Table\n");

            statement = conn.createStatement();
            String query = "SELECT * FROM customer";

            ResultSet rst = statement.executeQuery(query);

            ResultSetMetaData rstMetaData = rst.getMetaData();

            int columnCount = rstMetaData.getColumnCount();

            for (int i = 1; i < columnCount+1; i++) {
                System.out.print(rstMetaData.getColumnName(i) + " ");
            }

            System.out.println();

            while(rst.next()) {
	            for (int i = 1; i < columnCount+1; i++) 
                	System.out.print(rst.getString(i) + "\t");

            }
        } catch (ClassNotFoundException cnfe) {
            System.out.println("Error Loading Driver: " + cnfe);
        } catch (SQLException sqle) {
            System.out.println("SQL Error: " + sqle);
        } catch (Exception e) {
            System.out.println("Error: " + e);
        } finally {
            try {
                if (statement != null)
                    statement.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
    }
}
// End of File

C:\classes> javac -classpath javac -classpath c:\classes\mysql-connector-java-5.0.4-bin.jar ListCustomers2.java

C:\classes> java -cp javac -classpath c:\classes\mysql-connector-java-5.0.4-bin.jar;c:\classes ListCustomers2 root test

Prepared Statements:

import java.sql.*;

public class AddCustPS {
    public static void main(String[] args) {
        if (args.length < 4)
        {
            System.out.println("USAGE: java ListCustomers username password last-name first-name");
            return;
        }
        String url = "jdbc:mysql://localhost:3306/test";  
        String driver="com.mysql.jdbc.Driver";

        addCust (driver, url, args[0], args[1], args[2], args[3]);
    }

    public static void addCust (String driver, String url,
        String username, String password, String lname, String fname)
    {
    	String sql = "INSERT INTO customer (lname, fname) VALUES (?, ?)";
    	Connection conn = null;

password=null;  // We haven't set one for MySQL root user.

    	PreparedStatement prep = null;
        try {
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());

            conn = DriverManager.getConnection(url, username, password);

	    	conn.setAutoCommit(false);    // SET AUTOCOMMIT OFF!!
            prep = conn.prepareStatement(sql);

            prep.setString(1, lname);
            prep.setString(2, fname);
            prep.execute();
            conn.commit();
        } catch (SQLException sqle) {
            System.out.println("SQL Error: " + sqle);
            //conn.rollback();  // ROLLBACK!!
        } catch (Exception e) {
            System.out.println("Error: " + e);
        } finally {
            try {
                if (prep != null)
                    prep.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
    }
}

C:\classes>javac -classpath c:\classes\mysql-connector-java-5.0.4-bin.jar AddCustPS.java

C:\classes>java -cp javac -classpath c:\classes\mysql-connector-java-5.0.4-bin.jar;c:\classes AddCustPS root test Jolie Angelia

Calling a Stored Procedure or Function from Java:

import java.sql.*;

public class AddCustSP {
    public static void main(String[] args) {
        if (args.length < 4)
        {
            System.out.println("USAGE: java ListCustomers username password last-name first-name");
            return;
        }
        String url = "jdbc:mysql://localhost:3306/test";  

        addCust (url, args[0], args[1], args[2], args[3]);
    }

    public static void addCust (String url,
        String username, String password, String lname, String fname)
    {
    	Connection conn = null;
    	CallableStatement call = null;
        try {
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
password=null;  // We haven't set one for MySQL root user.
            conn = DriverManager.getConnection(url, username, password);

            call = conn.prepareCall("{CALL add_customer (?,?,?) }");

            // Find out who all the sales people are. line 24
            call.registerOutParameter(1, Types.INTEGER);
            call.setString(2, lname);
            call.setString(3, fname);
            call.executeUpdate();
            int  iRetVal = call.getInt(1);
            System.out.println("New Customer # "+iRetVal);
        } catch (SQLException sqle) {
            // This is our little Hack Fix for MySQL 5.0 my_signal Hack!!!  Can't wait til MySQL 5.2 to fix this.
            String msg = sqle.toString();  
            System.err.println ("SQL Error: " + 
                msg.substring(msg.indexOf("Table 'test.")+12, 
                msg.indexOf("' doesn't exist"))
            );
        } catch (Exception e) {
            System.out.println("Error: " + e);
        } finally {
            try {
                if (call != null)
                    call.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
    }
}


C:\classes> java -cp javac -classpath c:\classes\mysql-connector-java-5.0.4-bin.jar;c:\classes AddCustPS root test Jolie Angelia

C:\classes>javac -classpath c:\classes\mysql-connector-java-5.0.4-bin.jar AddCustSP.java

C:\classes> java -cp javac -classpath c:\classes\mysql-connector-java-5.0.4-bin.jar;c:\classes AddCustSP root test Pitt Brad
New Customer # 8
-- HERE IS The Stored Procedure we called.
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`add_customer` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_customer`(
    INOUT p_customer_number  int,
    IN p_lname varchar(50),
    IN p_fname varchar(50))
BEGIN
    IF (p_lname = 'Junk') THEN
        CALL my_signal ('Junk Not Allowed, Go Away!!!');
    END IF;

    IF (p_customer_number = 0 OR p_customer_number IS NULL)
    THEN
        INSERT INTO customer (customer_number, lname, fname)
            VALUES (p_customer_number, p_lname, p_fname);

        SELECT last_insert_id() INTO p_customer_number;
    ELSE
        UPDATE customer SET
            lname = p_lname,
            fname = p_fname
        WHERE customer_number = p_customer_number;
    END IF;
END $$

DELIMITER ;
C:\classes>java -cp javac -classpath c:\classes\mysql-connector-java-5.0.4-bin.jar;c:\classes AddCustSP root test junk junk
SQL Error: junk not allowed, go away!!!




Example 3: Returning a ResultSet from MySQL Stored Procedure to Java

-- Below is the stored procedure to create in MySQL
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`getEmpData` $$
CREATE PROCEDURE `test`.`getEmpData` ()
BEGIN
  SELECT * FROM employees;
END $$

DELIMITER ;

-- Now the Java Part:


import java.sql.*;

public class ListEmp {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test";

        listEmp (url, args[0], args[1]);
    }

    public static void listEmp (String url, String username, String password)
    {
        Connection conn = null;
        CallableStatement call = null;
        try {
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
password=null;  // We haven't set one for MySQL root user.

            conn = DriverManager.getConnection(url, username, password);
            System.out.println("DB Name: "+conn.getMetaData().getDatabaseProductName());	
            System.out.println("Driver: "+conn.getMetaData().getDriverName());

            System.out.println ("Employee Table\n");

            call = conn.prepareCall("{CALL getEmpData() }");

            if (!call.execute()) // No ResultSet returned...
                return;

            ResultSet rst = call.getResultSet();

            System.out.println("\n=======================\n");

            while(rst.next()) {
                System.out.print(rst.getInt("EMPLOYEE_ID") + "\t");
                System.out.print(rst.getInt("MANAGER_ID") + "\t");
                System.out.print(rst.getString("LAST_NAME") + "\t");
                System.out.print(rst.getString("FIRST_NAME") + "\t\n");
            }
        } catch (SQLException sqle) {
            System.out.println("SQL-Error: " + sqle);
        } catch (Exception e) {
            System.out.println("Error: " + e);
        } finally {
            try {
                if (call != null)
                    call.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
    }
}


/*

C:\classes>javac -classpath c:\classes\mysql-connector-java-5.0.4-bin.jar ListEmp.java

C:\classes>java -cp javac -classpath c:\classes\mysql-connector-java-5.0.4-bin.jar;c:\classes ListEmp root test
DB Name: MySQL
Driver: MySQL-AB JDBC Driver
Employee Table


=======================

1       0       Simpson Bart
2       1       Jackson Janet
3       1       Flintstone      Fred
4       2       Norris  Chuck
5       4       Lee     Bruce

*/



 

Example 4: Returning Multiple ResultSet from MySQL Stored Procedure to Java

/** Stored Proecedure in MySQL

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`world_record_count` $$
CREATE DEFINER=``@`localhost` PROCEDURE `world_record_count`()
BEGIN
	SELECT 'Country', COUNT(*) FROM Country;
	SELECT 'City', CoUNT(*) FROM City;
	SELECT 'CountryLanguage', COUNT(*) FROM CountryLanguage;
END $$

DELIMITER ;


*/



import java.sql.*;

public class ManyRS {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test";

        listManyResultSets (url, args[0], args[1]);
    }

    public static void listManyResultSets (String url, String username, String password)
    {
        Connection conn = null;
        CallableStatement call = null;
        try {
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
password=null;  // We haven't set one for MySQL root user.

            conn = DriverManager.getConnection(url, username, password);
            
            System.out.println("DB Name: "+conn.getMetaData().getDatabaseProductName());	
            System.out.println("Driver: "+conn.getMetaData().getDriverName());

            call = conn.prepareCall("{CALL world_record_count() }");

            boolean moreResultSets = call.execute(); 

            while (moreResultSets)
            {
                ResultSet rst = call.getResultSet();

                System.out.println("\n=======================\n");

                ResultSetMetaData rstMetaData = rst.getMetaData();

      	      	int columnCount = rstMetaData.getColumnCount();

                for (int i = 1; i < columnCount+1; i++) {
                    System.out.print(rstMetaData.getColumnName(i) + " ");
                }

                System.out.println();

      	      	while(rst.next()) 
                {
                    for (int i = 1; i < columnCount+1; i++) 
                        System.out.print(rst.getString(i) + "\t"); 

                    System.out.print("\n");
                }
                moreResultSets = call.getMoreResults();
            }
        } catch (SQLException sqle) {
            System.out.println("SQL-Error: " + sqle);
        } catch (Exception e) {
            System.out.println("Error: " + e);
        } finally {
            try {
                if (call != null)
                    call.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException sqle) {
                sqle.printStackTrace();
            }
        }
    }
}


/*

C:\classes>javac -classpath c:\classes\mysql-connector-java-5.0.4-bin.jar ManyRS.java

C:\classes>java -cp javac -classpath c:\classes\mysql-connector-java-5.0.4-bin.jar;c:\classes ManyRS root test
DB Name: MySQL
Driver: MySQL-AB JDBC Driver

=======================

Country COUNT(*)
Country 239
=======================

City CoUNT(*)
City    4079
=======================

CountryLanguage COUNT(*)
CountryLanguage 984
*/

Java Server Pages (JSP) Example:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<HTML>
<HEAD>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*"%>

<META http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<META name="GENERATOR" content="IBM Software Development Platform">
<META http-equiv="Content-Style-Type" content="text/css">
<LINK href="../theme/Master.css" rel="stylesheet" type="text/css">
<TITLE>List Customers</TITLE>
</HEAD>
<BODY>
<P>Place content here.</P>
<table border><tr><th>Customer #</th><th>Last Name</th><th>First Name</th><th>Street Address</th><th>City</th><th>State</th></tr>
<%

    String url = "jdbc:mysql://localhost:3306/test"; 
    String cnum = request.getParameter("cnum");
	String lname = request.getParameter ("lname");
	String fname = request.getParameter ("fname");
	
    Connection conn = null;
    Statement stmt = null;
    CallableStatement call = null;
    try {
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());

        conn = DriverManager.getConnection(url, "root", "");

        System.out.println ("Customer Orders\n");

        String query = "SELECT * FROM customer";

		if (cnum != null)
		{
			if (lname != null && fname != null)
			{
            	call = conn.prepareCall("{CALL add_customer (?,?,?) }");

	            call.registerOutParameter(1, Types.INTEGER);

				call.setInt(1, Integer.parseInt(cnum));
	            call.setString(2, lname);

    	        call.setString(3, fname);
        	    call.executeUpdate();
            	int  iRetVal = call.getInt(1);
			}
			query+=" WHERE customer_number = "+cnum;
		}
        stmt = conn.createStatement();

        ResultSet rst = stmt.executeQuery(query);

        while(rst.next()) 
        {
            out.print("<tr><td>"+rst.getString(1) + "</td>");
            out.print("<td>"+rst.getString(2) + "</td>");
            out.print("<td>"+rst.getString(3) + "</td>");
            out.print("<td>"+rst.getString(4) + "</td>");
            out.print("<td>"+rst.getString(5) + "</td>");
            out.println("<td>"+rst.getString(6)+ "</td></tr>");
        }
    } catch (SQLException sqle) {
        out.println("<h1>Error Connecting: " + sqle+"</h1>");
    } catch (Exception e) {
        out.println("<h1>Error Connecting: " + e+"</h1>");
    } finally {
        try {
            if (stmt != null)
                stmt.close();
            if (call != null)
                call.close();
            if (conn != null)
                conn.close();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }
%>
</table>
</body>
</html>