Pages

Saturday 19 November 2011

JSP and MySQL Connectivity


I remember when I first started using Tomcat, it took me a while to make my JSP script connect to MySQL using Connector/J.

1. Tomcat is a free, open-source implementation of Java Servlet and JavaServer Pages technologies developed under the Jakarta project at the Apache Software Foundation. Download and install Tomcat.

2. Install MySQL.

3. Create a database, say 'ProductSpec', and a table 'products' with three fields- id, name, brand.

CREATE DATABASE  `ProductSpec` ;

CREATE TABLE  `products` (
 `id` INT( 3 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `name` VARCHAR( 50 ) NOT NULL ,
 `brand` VARCHAR( 50 ) NOT NULL
) ENGINE = INNODB;

4. I populate the the database with a few values next:

INSERT INTO  `ProductSpec`.`products`
VALUES ( 1 ,  'Hard Disk',  'Seagate');

INSERT INTO  `ProductSpec`.`products`
VALUES ( 2 ,  'iPhone 3G',  'Apple');

5. Set up two environment variables as follows:
(a) Right click on Computer, go to Properties. Click on 'Advanced System Settings on the left. A dialog box appears as shown below. Go to the 'Advanced Tab' and click on 'Environment Variables'.



(b)Under User Variables, add the two new variables. The first is the path to your jre folder, the second is the path to the the Tomcat folder. For instance, if your jre folder path is D:/jdk1.6.0_12/jre, add the following-

Variable Name: JAVA_HOME, Variable Value: D:/jdk1.6.0_12/jre

Variable Name: TOMCAT_HOME, Variable Value: C:\Program Files\Apache Software Foundation\Tomcat 7.0



6. Download Connector/J at "http://dev.mysql.com/downloads/connector/j/5.1.html">http://dev.mysql.com/downloads/connector/j/5.1.html
Unzip or Untar the file, and copy the file mysql-connector-java-5.1.6-bin.jar to Tomcat/lib or Tomcat/common/lib

This completes the setup! Now for the jsp program that connects to the database and retrieves and displays values. Make sure Tomcat and wamp are running!

7. Here's db.jsp:


<%@ page import="java.sql.*" %>

<%

String connectionURL = "jdbc:mysql://localhost:3306/ProductSpec?username=root;password=";

Connection connection = null;

Statement statement = null;

ResultSet rs = null;

%>

<html><body>

<%

Class.forName("com.mysql.jdbc.Driver").newInstance();

connection = DriverManager.getConnection(connectionURL, "root", "");

statement = connection.createStatement();

rs = statement.executeQuery("SELECT * FROM products");

while (rs.next()) {

out.println(rs.getString("name")+" "+rs.getString("brand")+"<br />");

}

rs.close();

%>

</body></html>


Change your database name, username and password on line 3: (Default username for wamp is root and password is blank.

String connectionURL = "jdbc:mysql://localhost:3306/ProductSpec?username=root;password=";

8. Add db.jsp to Tomcat\webapps\ROOT folder.

9. Now go to your browser and type localhost/db.jsp
If you had set a port number for Apache during installation like I had (8088), type localhost:8088/db.jsp