Testing JAVA with MySQL

What is MySQL?

         

MySQL is a popular Database Management System (RDBMS), from the open source domain. It is extensively used by communities that wish to establish a dynamic Web presence.

          MySQL they have dual licensing policy that helps support open source values while being a profitable, sustainable business. All software that wishes to participate in the open source game plan must abide by the GNU public license (GPL). The GPL indicates that all software, (in this case MySQL) can be used at no cost and that its source code is also made available under GPL.

Testing JAVA with MySQL:

Focus:

To scan a set of tables, belonging to the SMS database and display a report about all the products available in the store.

Preparation:

Login into MySQL as required. Create and populate the required tables.

The Java program will:

·         Connect to the SMS Database

·         Fetch data from the table based on an SQL SELECT statement.

·         Display complete information about product held within these tables

Before any scripting in Java can be done, Java must be able to connect to a MySQL database. If Java cannot connect to the MySQL database then all the commands forwarded to the database will fail.

Here is the general order of events that take place during the Java/MySQL DB server communications process.

·         Java establishes a connection with the MySQL DB engine. If the connection attempts fails, an appropriate message is displayed and the Java process exits.

·         Once a successful connection to the MySQL DB engine is complete, a connection to a specific database is done. It is possible to simultaneously have several databases open for querying.

·         Perform necessary queries on selected database(s) to retrieve the table data.

·         Once querying is complete, the connection to the MySQL DB engine is closed.

Connecting and Selecting the Database

The following are the variables defined to hold MySQL DB connection details:

                   /* Definition of the variables */

                   /* A Connection Object       */

                           Connection Conn;

                   /* A Statement Object. */

                           Statement stmt;

                   /* A string holding the driver to be used */

A variable URL is declared that contains the name of the jdbc: Mysql Bridge, a server name and the MYSQL Db name. These are connection details which java uses to connect to a MYSQL DB. Local host indicates that the MYSQL Db engine is running on the same computer that the java interpreter is running on. SMS is the name of the MYSQL database.

A variable query is declared that holds the SELECT query, which will be fired to the MYSQL DB engine to retrieve the required table data.

/* Prepare the query for selecting the records from table ProductMaster */

String query = “SELECT ProductMaster.ProductID,

ProductMaster.Name ProdName, CompanyMaster.Name CmpName,

CategoryMaster.Name CatName, SchemeMaster.Description SchName,Cost, QtyInHand

FROM ProductMaster,CompanyMaster,CategoryMaster,SchemeMaster

WHERE ProductMaster.CompanyID=CategoryMaster.CompanyID

AND ProductMaster.CategoryId=CategoryMaster.CategoryID

AND ProductMaster.SchemeId=SchemeMaster.SchemeID”;

A function Class.forName () is used that explicitly loads a driver class. The function newInstance() tells the driver Manager to use the MM.MySQL JDBC Server. If the driver class is not found, an exception will be thrown which informs the user of this issue.

The Connection object attempts to establish a connection to a MySQL DB via the same database URL using the getConnection() function.

try

    {

      /* The class .forname method tries to dynamically locate and load a java.lang .class object corresponding to a given class name */

                Class.forName(driver).NewInstance();

     }

Catch(Exception clsnotfndExcep)

     {

         System.out.print(“ClassNotFoundException : ”);

         System.out.println(clsnotfndExcep.getMessage ( ) );

     }

The connection object attempts to establish a connection to a MySQL DB via the given Database url using the getconnection( ) function.

          /* Connection path is set in connection object */

              Conn=DriverManager.getConnectin(url,”root”,”sct2306”);

                System.out.println(“The Mysql Database Connection Established è”);

    Stmt=conn.createstatement( );

Executing Commands

          Since a Successful connection to the MySQL DB engine has been established and the database to be used is selected, MySQL commands can be fired to specific database table(s).

A Command looks like this:

/* Execute the query using the result set object */

    ResultSet rs = stmt.executeQuery(query);

A function executeQuery() is used that fills the result set with data.

Outputting Data

         

Since there is at least one record (if not many more), returned by the query, this data can be retrieved using Java and displayed.

Setting up the Loop

Set up a loop to take each row of the ResultSet array and print out the data held there to the VDU. Use the function next () to loop through all the rows in the Result Set.

/* This code uses a While loop which uses column names to extract a column value from the Result set.

While(rs.next())

{

…

}

This is a basic Java loop, which will execute a specific code block the correct number of times.

Extracting Values from Result Set

            Once a data row’s data extraction is I place, the data needs to be displayed on the VDU in the form of the grid. This can be achieved as

Import java.sql.*;

Public class ProductMaster

{

    Public static void main (String args[ ] )

     {

      /* Definition of Variables*/

     /* A Connection Object. */

         Connection conn;

    /* A Statement Object. */

          Statement stmt;

    /* A String holding the driver to be used */

          String driver = “Com.mysql.jdbc.Driver”;

/* A variable holding the database details. Since the database already exists its name is mentioned in the url i.e. SMS*/

          String url= “jdbc:mysql://localhost/SMS”;

/* prepare the query for selecting the records from table ProductionMaster */

String query = “SELECT ProductMaster.ProductID, ProductMaster.Name

      ProdName,CompanyMaster.Name CmpName,

     CategoryMaster.Name CatName,

      SchemeMaster.Description SchName, Cost, QtyInHand

      FROM ProductMaster, CompanyMaster, CategoryMaster, Scheme Master

      WHERE ProductMaster.CompanyId = CompanyMaster.CompanyID

AND ProductMaster.CategoryId = CategoryMaster.CategoryID

AND ProductMaster.SchemeId = SchemeMaster.SchemeID;

try

{

  /* The Class.forName method try to dynamically locate and load a java.lang.class object corresponding to a given class name. */

            Class.for.Name(driver).newInstance();

}

Catch(Exception clsnotfndExcep)

{

            System.out.print (“ClassNotFoundException: “);

            System.out.println (clsnotfndExcep.getMessage ());

}

            try

            {

                        /*Connection path is set in Connection object*/

                   Conn=DriverManager.getConnection (url,”root”,”sct2306”);

                   System.out.println (“The Mysql Database Connection Establishedè”);

                        Stmt=conn.createstatement ( );

                        /* Execute the query using the resultset object*/

                        ResultSet rs = stmt.executeQuery (query);

                       /* Retrieving records from the ResultSet rs and displaying them at the system

Prompt. This code uses a while loop in which column name extracts a column value extracts a column value from the ResultSet */

While(rs.next())

{

String ProductID = rs.getstring(“ ProductID”);

String ProdName = rs.getstring(“ProdName”);

String CmpName= rs.getstring(“CmpName”);

String Cost = rs.getstring(“Cost”);

String QtyInHand =  rs.getstring(“QtyinHand”);

/* Printing the data along with column header */

System.out.println(“-----------------------------------“);

 System.out.println(“Product Identity = “ +ProductID);

 System.out.println(“ProductName= “ +ProdName);

 System.out.println(“CompanyName = “ +CmpName);

 System.out.println(“CategoryName = “+CatName);

 System.out.println(“SchemeName = “ +SchName);

System.out.println(“Cost = “+Cost);

System.out.println(“Quantity = “ + QtyInHand);

System.out.println(“------------------------------------“);

}

Closing the Resources:

Now the final step is to close all the connection resources, which is done as follows:

/* Closing the statement object stmt */

      Stmt.close( );

/* Closing the connection object conn */

      Conn.close( );

/* End of try block */

}

/* Minimal Exception handling done here */

    Catch(SQLException sqlExcep)

   {

           System.out.println(“SQLException: “ +sqlExcep.getMessage( ));

}

/* End of main() method */

}

/* End of Class*/

}

Running the Java Program:

REMINDER

·         Use an ASCII editor such as Notepad or any of the other excellent windows based ASCII editors or Kate on Linux to create the Java Program

·         Type in the Java Program paying careful attention to its syntax

·         Save this file as <filename>.java in an appropriate folder (i.e. ProductionMaster.java)

·         Now compile the code at the windows command prompt or in an Terminal window Linux by using:

<System Prompt> javac ProductionMaster.java

·         Ensure that errors(if any) thrown by the compiler is rectified

·         Run the code using the following command

<System Prompt> java Production Master