We will write a simple Web application that queries a MySQL database server and displays a list of databases. To write this Web application we will use Eclipse, an IDE, to deploy the Web application on Tomcat application server. To start with, ensure that you have installed JDK, Tomcat, Eclipse and MySQL. If you did not select these packages during the installation, you can find their RPMs on the PCQLinux 2004 CDs. The snippets below explain how to install each of them after a PCQLinux installation.
Install JDK
Mount PCQLinux 2004 CD 2 and install the RPM named pcqlinux-2004-java-installer-1.0.i386.rpm, found in the subdirectory PCQLinux/RPMS, using the following command.
rpm -ivh pcqlinux-2004-java-installer-1.0.i386.rpm
This will produce a file named j2sdk-1_4_2_03-linux-i586-rpm.bin in the directory /opt/java-installer. Change to this directory and execute the .bin file as:
./ j2sdk-1_4_2_03-linux-i586-rpm.bin
This will throw up a license agreement. Upon typing Yes, you will get an RPM file named j2sdk-1_4_2_03-linux-i586.rpm in the same directory. Install the RPM using the coommand:
rpm —ivh j2sdk-1_4_2_03-linux-i586.rpm
Install Tomcat
Install the RPM named tomcat-5.0.16-0.i386.rpm found in PCQLinux 2004 CD 2, in the directory PCQLinux/RPMS. This will install Tomcat application server in the directory /opt/tomcat.
Install Eclipse
Install the RPM named eclipse-2.1.2-0.i386.rpm found on PCQLinux 2004 CD 2. This will install Eclipse IDE in the directory /opt/eclipse.
Install MySQL Database
Install the RPMs named perl-DBI-1.37-1.i386.rpm, perl- DBD-MySQL-2.9002-1.i386.rpm and mysql-3.23.58-4.i386.rpm from PCQLinux 2004 CD 1 and mysql-server-3.23.58-4.i386.rpm from CD 2. If you get any dependency errors while installing, suffix –nodeps to the ‘rpm —ivh’ command.
Set up Eclipse
Eclipse IDE is extensible via plugins. That is, support for new features and even languages (besides Java) can be added to it using plug-ins. To code a Web app for Tomcat you need to download and install a small plugin called Sysdeo Eclipse Tomcat Launcher (250KB in size) plugin from www.sysdeo.com/eclipse/tomcatPluginV221.zip. Unzip the plugin to the Eclipse plugin directory as:
unzip tomcatPluginV221.zip -d /opt/eclipse/plugins
In X, launch Eclipse by typing in the following in the Run box or in a terminal window:
/opt/eclipse/eclipse
In Eclipse, click on Windows>Preferences>Tomcat. For Tomcat version, select Version 5.x. For Tomcat home and Tomcat base, type in /opt/tomcat. Click on OK.
Set up Web app
To start coding the Web app, click on File>New>Project>Java >Tomcat Project. Click on Next. For Project Name, enter mysql_databases. Click on Finish. When prompted to switch perspective, click on Yes. Subsequently, on the left pane of Eclipse, you should be able to see a project named mysql_databases. If you click on the arrow button besides the project name, you should be able to see the directory structure for a Java Web app.
The JSP and HTML files go under the directory mysql_databaes (the root directory of our Web app). The class files, which perform the business logic, should be kept under Web-INF/classes. Jar files, like the one containing database drivers, should be kept under WEB-INF/lib.
For our Web app, we need JDBC drivers for MySQL, which can be downloaded from the URL www.mysql.com/downloads/api-jdbc-stable.html. Download the tar.gz archive and extract the archive. In Eclipse, right click on the WEB-INF/lib directory and select Import>File system. Click on Next. Click on Browse and select the directory where you have extracted the downloaded MySQL drivers. On the right pane, select the file
mysql-connector-java-3.0.10-stable-bin.jar and click on Finish.
Business logic
For business logic we create a Java class named MySQLDatabases which has a method (or function) called getDatabases( ) which returns the names of all the databases. Right click on the directory WEB-INF/src (on Eclipse’s left pane) and select New>Class. For Package, type in pcquest (or any preferred package name). For Name, type in MySQLDatabases. Click on Finish.
This will create a template code for the MySQLDatabases class as shown in the screenshot. Append the following line after the package statement:
import java.sql.*;
Next, between the opening and closing braces of the class, type in the following code — the code for the method getDatabases( ):
public String getDatabases( ){
String result=””;
try
{
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
Connection con = DriverManager.getConnection(“jdbc:mysql://127.0.0.1/mysql?user=root&password=”);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(“show databases”);
while(rs.next())
{
result +=”
”+rs.getString(“Database”);
}
}
catch (Exception e)
{
return e.toString();
}
return result;
}
While typing in the code, Eclipse will constantly assist you by reporting syntax errors, making hand coding a breeze. To compile the code, click on Project>Rebuild All. This will compile MySQLDatabases Java file and place the compiled code namely MySQLDatabases.class under WEB-INF/classes, automatically.
And the frontend
Right click on mysql_databases and select New>File. For File name, enter index.jsp. On the left pane, click on index.jsp on the left pane and write the following code.
<%=dbs.getDatabases() %>
Run your Web app
Switch to Linux console or open a terminal window within X. Start MySQL as:
service mysqld start
Back in the Eclipse IDE, click on Tomcat>Start Tomcat. This will start the Tomcat application server from within Eclipse. Now launch the Web browser (Mozilla or Konqueror) and key in the following URL to access our Web app.
http://127.0.0.1/mysql_databases/index.jsp
You should be able to see a list of databases running on the MySQL server.
For more on Eclipse, refer to the URL www.eclipse.org. For a comprehensive site on Eclipse plugins refer to the URL
http://eclipse-plugins.2y.net.
Shekhar Govindarajan