Important alert: (current site time 10/21/2014 10:47:24 AM EDT)
 

article

MySQL Database Connectivity with JSP (Windows)

Email
Submitted on: 4/27/2001 4:08:25 PM
By: Daniel M. Hendricks  
Level: Intermediate
User Rating: By 31 Users
Compatibility: Java (JDK 1.1), Java (JDK 1.2)
Views: 258254
author picture
(About the author)
 
     A tutorial on how to get started with JavaServer pages using Sun's Tomcat web server (a Jakarta variant) and connecting to a MySQL database to retrieve data. Provided as a jumpstart for practicing with real-world applications. Tutorial is intended for users who may have had previous web/database experience but would like to get their feet wet in JSP. (Updated October 04, 2002)


 
 
Terms of Agreement:   
By using this article, you agree to the following terms...   
  1. You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this article from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
				

This tutorial will show you how to connect to a MySQL database using JSP (JavaServer Pages) under Windows and Tomcat web server.  If you run into problems or find errors, please let me know so I can fine-tune this document.  This document will probably be most useful for those who have done web scripting with MySQL databases before but would like to get started with JSP/Servlets programming.  Database connectivity provides a good foundation for learning any new language, as you can practice making real-world applications in a database environment.

Requirements:

  1. MySQL
    http://www.mysql.com
  2. Tomcat - version 4.1.12 Standard used for this tutorial
    http://jakarta.apache.org/site/binindex.html
  3. Java 2 JRE - version 1.4.1 used for this tutorial
    http://java.sun.com/j2se/1.4.1/download.html
  4. MySQL Connector/J - version 2 used for this tutorial
    http://www.mysql.com/downloads/api-jdbc.html

Assumptions:

  1. It is assumed that you already have a MySQL database installed and a table to pull data from.
  2. It assumes you understand SQL, and probably have done some web/database scripting with other languages.
  3. The author uses the folder C:\Tomcat as the folder where Tomcat will be extracted, however, you can place the distribution files anywhere you wish.
  4. You know the basics of programming Java.  If you do not, I highly recommend you check out Sun's Java Tutorial

Section A - Installation

  1. Install the Java 2 JRE.  I put mine in C:\java\jre, which will be used in this tutorial, but you can put your anywhere you like. 
  2. Extract the Tomcat distribution files to a folder.  The default is jakarta-tomcat-4.1.12, but I chose to put the files in C:\Tomcat.
  3. Copy the MySQL Connector JAR file to the C:\Tomcat\common\lib folder (ie, mysql-connector-java-2.0.14.jar).

Section B - Environmental Variables

Add the following environmental variables to Windows:

  JAVA_HOME=C:\java\jre
TOMCAT_HOME=C:\Tomcat

You can set environmental variables in Windows 2000/XP by going to:
Righy-click My Computer -> Properties -> Advanced -> Environmental Variables

You can set environmental variables in Windows NT 4 by going to:
Righy-click My Computer -> Properties -> Environment 

Section C - Working with Tomcat

To start the server, execute startup.bat.  To stop the server, execute shutdown.bat in the C:\Tomcat\bin folder.

By default, the Tomcat web server is access at this URL:
http://localhost:8080/

The root folder of the server is located in:
C:\Tomcat\webapps\ROOT

The root and default port can be changed in this file:
C:\Tomcat\conf\server.xml

Section D - Write Your Code!

You can now start writing your JSP scripts.  Save it in a file with a JSP extension and place it in the ROOT folder.  I have provided a simple JSP script that demonstrates how to connect to a list data from a MySQL database.

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

String connectionURL =
"jdbc:mysql://localhost:3306/mydatabase?user=;password=";
Connection connection =
null;
Statement statement =
null;
ResultSet rs =
null;
%>

<html><body>

<%
Class
.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection(connectionURL,
"", "");
statement = connection.createStatement();
rs = statement.executeQuery(
"SELECT * FROM mytable");

while (rs.next()) {
out.println(rs.getString(
"myfield")+"<br>");
}

rs.close();
%>

</body></html>

 
Obviously, you will want to change the username and password to match your database.  Also, the mydatabase value in the connectionURL represents the name of the MySQL database.  Change appropriately.  Finally, change the mytable and myfield values in the script to match a table and field that exist within your database.  If the public is granted access to the database, you can use this as your connectionURL:

String connectionURL = "jdbc:mysql://localhost:3306/mydatabase";

Happy coding!


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments

5/4/2001 9:59:33 PMahfook

very good tutorial for JSP beginner.
(If this comment was disrespectful, please report it.)

 
11/13/2001 12:57:44 PMhui

it would be perfect if there is some concise explaination on each line of code since the codes are not too much. Thanks and keep up the good work!
(If this comment was disrespectful, please report it.)

 
11/16/2001 12:00:54 PMShawn

hi, i'm new to MySQL and JSP, i am currently using JSWDK instead of tomcat, but the codes doesn't seem to work for me..
(If this comment was disrespectful, please report it.)

 
1/3/2002 8:34:16 PMallaine

Hi! I'm using jdk1.2 and win98. How can I set environment variables for win 98?
(If this comment was disrespectful, please report it.)

 
4/16/2002 1:58:16 AMMe

Hi, I've tried to test the above codes, but somehow I got this error message "javax.servlet.ServletException: org.gjt.mm.mysql.Driver" on the first line. Do you know what is wrong?
(If this comment was disrespectful, please report it.)

 
5/12/2002 3:58:05 AMVincent Chan

Excellent tutorial for beginners who want to setup their web-based application. Thank you very much.
(If this comment was disrespectful, please report it.)

 
6/2/2002 9:41:38 AMMissing Info

The only thing missing is you need to put the mm.mysql jar files in the /WEB-INF/lib directory. This is probably why people are getting the ServletException.
(If this comment was disrespectful, please report it.)

 
6/13/2002 3:31:52 PMBogdan Pomerlyan

You are the man:
Add the following environmental variables to Windows:

JAVA_HOME=C:\java\jdk
TOMCAT_HOME=C:\Tomcat

You can set environmental variables in Windows 2000 by going to:
Righy-click My Computer -> Properties -> Advanced -> Environmental Variables

I have been searching where I could add that for the past hour, thanks for putting that in your work. I greatly appreciate it.
(If this comment was disrespectful, please report it.)

 
8/1/2002 2:28:24 PMMike

Hi I've seemed to have followed those instructions above correctly but i'm still get that same error "javax.servlet.ServletException: org.gjt.mm.mysql.Driver" does anyone know why? I think it's the placement of the .jar file. I'm not exactly sure what this file does or where it's supposed to go, and if there is anything else i'm supposed to do to get this file to work with MySQL and Tomcat. Any help or ideas would be appreciated . THanks
(If this comment was disrespectful, please report it.)

 
10/28/2002 8:01:04 AM

hi, the code does not work with my server.i think that is because of the older version of tomcat and jre. but i am not sure. can you help me?
thanx
(If this comment was disrespectful, please report it.)

 
11/6/2002 10:31:00 AM

I can use this driver code for select query but when I use Add query it's show me error that Unable to load driver can you help me? Thank you
(If this comment was disrespectful, please report it.)

 
11/17/2002 8:29:30 PM

Wondrful! I implanted it into java code (I call it Sql.java)using in Tomcat Servlet engine. It works very well. (I spent long time on the web and didn't figure it out until I read this article.) Thanks a lot!
(If this comment was disrespectful, please report it.)

 
12/3/2002 9:20:34 PM

Do we just need to
(If this comment was disrespectful, please report it.)

 
12/3/2002 9:22:41 PM

Do I just need to "Copy the MySQL Connector JAR file to the C:\Tomcat\common\lib folder (ie, mysql-connector-java-2.0.14.jar)."? How about the rest of the files in the "mysql-connector-java-2.0.14" folder? pls help!
(If this comment was disrespectful, please report it.)

 
12/8/2002 10:16:10 PM

can i save my file to folder other than the ROOT folder in tomcat?
(If this comment was disrespectful, please report it.)

 
12/10/2002 11:05:30 AM

Nice and Concise!
(If this comment was disrespectful, please report it.)

 
12/14/2002 3:40:43 PM

It worked for me perfect!! altho I didnt have a common/lib folder. I figured that out though. Probably different versions. YAY I can do some coding now.
(If this comment was disrespectful, please report it.)

 
12/18/2002 1:06:08 PM

Its great. I'm a new and I manage to get this thing works at once. Keep up the good work. This sample is easy to understand. =)
(If this comment was disrespectful, please report it.)

 
12/21/2002 11:10:45 AM

I got exceptions at first, but realized in the connection URL, you need a '
(If this comment was disrespectful, please report it.)

 
12/21/2002 11:12:00 AM

I was getting exceptions until I realized that the connection URL requires a & between the user=username;password=password, not a semicolon. Great tutorial, thanks!
(If this comment was disrespectful, please report it.)

 
12/30/2002 4:03:47 AM

Good Job! It was very helpful in getting started with jsp, mySQL and Tomcat. I had to place the mySQL Connector jar in the jre\lib\ext subdir of j2sdk to work though. For some reason it just wouldn't work from tomcat common\lib subdir. Thanks!
(If this comment was disrespectful, please report it.)

 
3/4/2003 4:42:19 AM

it is very helpul for begineer like me. Thanks alot
(If this comment was disrespectful, please report it.)

 
5/21/2003 7:08:03 PM

Hi, I keep getting error when I try to start tomcat. Something is missing from the java.endorsed.dirs and I don't know what's else I need. Originally, the fold is empty when i download Java 2 JRE - version 1.4.1
What else do I have to put into that directory? Please help
(If this comment was disrespectful, please report it.)

 
5/30/2003 12:58:26 PMTristan

set the java.endorsed.dir environment variable. In xp i have it as

variable: java.endorsed.dir
value: C:\jwsdp-1.1\jaxp-1.2.2\lib\endorsed


if you use tomcat that would be,
value: C:\TOMCAT_HOME\jaxp-1.2.2\lib\endorsed
(If this comment was disrespectful, please report it.)

 
6/12/2003 10:37:44 AM

This is a very good tutorial...I had a problem where tomcat would shut itself as soon as I access mysql...but now everything works fine as I put the connector jar files in the told directories. Even the feedback from the other users is so helpful!Thanks!
(If this comment was disrespectful, please report it.)

 
7/31/2003 10:43:44 AM

This is a very good Article Thanks. If any of you need more information go to www.bivius.com
(If this comment was disrespectful, please report it.)

 
8/20/2003 4:51:59 PM

Hi, thanks much for the clear
(If this comment was disrespectful, please report it.)

 
8/31/2003 12:59:28 PM

Hi I found this code very easy to use the only other thing is where can i find similar tutorial on using Jsps that work for varying queries of a database?
(If this comment was disrespectful, please report it.)

 
3/16/2004 12:29:56 AM

Soon write a tutorial on the same, for Linux platform
(If this comment was disrespectful, please report it.)

 
7/2/2004 5:57:35 AM

i am getting the following error:
please help me in sorting it out

javax.servlet.ServletException: Communication link failure: java.io.EOFException, underlying cause: null

** BEGIN NESTED EXCEPTION **

java.io.EOFException
(If this comment was disrespectful, please report it.)

 
7/24/2004 3:31:26 AM

Hello, I am using JSP with mySql-4.0.12
Tomcat-3.1.24 with jdk1.3.1/jdk1.1.1.
Can you suggest me how to code for database retrieval and its compatibilities with each other.
(If this comment was disrespectful, please report it.)

 
8/4/2004 8:31:44 AM

I'm working mysql along with JSP. Can anyone helpme how exactly should I proceed for connectivity.I've downloaded the jar but I don't know where should I place these files and what setting should be done in server.xml and wb.xml.Pls help me.
(If this comment was disrespectful, please report it.)

 
8/4/2004 8:34:08 AM

I've started using mysql as database.But the problem is that I really don't know how to connect to the database. Can anyone give me the detailed steps required to connect to the database. And pls let me know what changes I shoul do for the Web.xml and server.xml.Pls I need it badly.
(If this comment was disrespectful, please report it.)

 
8/27/2004 4:17:31 AM

Very nice and concise artice. Was off great help to me!! thanks
(If this comment was disrespectful, please report it.)

 
8/27/2004 4:18:31 AM

Very simple and straight to the point! was of great help to me!
(If this comment was disrespectful, please report it.)

 
10/27/2004 6:17:33 PM

I still got errors :
javax.servlet.ServletException: com.mysql.jdbc.Driver
how can i fix this. please help me!!
(If this comment was disrespectful, please report it.)

 
2/24/2005 6:04:16 AM

javax.servlet.ServletException: com.mysql.jdbc.Driver
I am using jdk 1.4 , and tomcat 4.0 . JAVA_HOME is set to c:\jdk1.4 , if I change to c:\jdk1.4\jre then it doesn't run my other jsp code. I think the problem is some where else. can you help me with the solution
(If this comment was disrespectful, please report it.)

 
3/24/2005 11:20:14 AM

i am getting the following error: please help me in sorting it out javax.servlet.ServletException: Communication link failure: java.io.EOFException, underlying cause: null ** BEGIN NESTED EXCEPTION ** java.io.EOFException
(If this comment was disrespectful, please report it.)

 
7/5/2005 12:20:09 PM

so at last i can connect to mysql ! but i have to copy the .jar files into folder jre/lib/ext like someone mentioned above ! but why i don't give the username and password, just leave them "" and "", but still connect and get the data ! maybe it's local connect and windows authenticated ! anyone know any tutorial to connecto to sqlserver by using jsp and jdbc
(If this comment was disrespectful, please report it.)

 
7/7/2005 1:50:34 PM

nice..
works very well in my jwsdp 1.5 ..
thx a lot ..
it helps me much..


(If this comment was disrespectful, please report it.)

 
9/22/2005 3:16:48 AMsudhir venugopal

not able to connect to MySQL via JSP. Found a few codes but none useful. Can Somebody help out.

Sudhir Venugopal
(If this comment was disrespectful, please report it.)

 
4/3/2006 5:52:59 AMpradeep

super work man,i was searching for this since a while,,,thanks
(If this comment was disrespectful, please report it.)

 
4/25/2006 8:13:36 AMsat

i try this code in this code i made simple change its working fine here is code <%@ page import="java.sql.*" %> <% String connectionURL = "jdbc:mysql://localhost:3306/mydatabas"; Connection connection = null; Statement statement = null; ResultSet rs = null; %> <% Class.forName("com.mysql.jdbc.Driver").newInstance(); connection = DriverManager.getConnection(connectionURL, "username", "password"); statement = connection.createStatement(); rs = statement.executeQuery("SELECT * FROM mytable"); while (rs.next()) { out.println(rs.getString("myfield")+"
"); } rs.close(); %>
(If this comment was disrespectful, please report it.)

 
6/26/2006 10:29:47 PMzyd

Appreicate the tutorial very much.
But there are 2 place I would like to share with people.
First:
String connectionURL = "jdbc:mysql://localhost:3306/mydatabase?user=;password=";
Looks something not right. Should be rewriten as
String connectionURL = "jdbc:mysql://localhost:3306/mydatabase";
Second:
Go to Tomcat bin and edit setclasspath.bat so that could add that JDBC jar file, otherwise TOMCAT cannot see the JDBC driver anymore.
(If this comment was disrespectful, please report it.)

 
9/12/2006 11:48:41 AMebele

The code is very excellent. I was able to connect on first use. no issues at all, very straight-forward. Thanks a million
(If this comment was disrespectful, please report it.)

 
3/29/2007 5:20:23 AMA Yasir

I have tried his code but its giving me error:

javax.servlet.ServletException: Communication link failure: java.io.EOFException, underlying cause: null

Can u please guide me whats going wrong at my end
(If this comment was disrespectful, please report it.)

 
9/12/2007 7:12:11 AMabdellatif

the code have many faults !!
(If this comment was disrespectful, please report it.)

 
9/12/2007 7:16:02 AMabdellatif

the code have many faults & tomcat will not work if you don't add somme folder like : xercesImpl, xml-apis , ..
it existes in jjakarta site whene you telecharge the tomkate
thinks : Haj Abdellatif
(If this comment was disrespectful, please report it.)

 
10/15/2007 6:53:00 AMmalatesh024

hi your code is excellnt thanks for contribution..........
i am bigenner in jsp... can you please send me some tutorial on jsp please to
malatesh024@yahoo.co.in
(If this comment was disrespectful, please report it.)

 
6/16/2010 12:45:08 PMDragan Gojkovic

Thank you for this quick-and-easy tutorial, just what i was looking!
Greetings from Serbia!
(If this comment was disrespectful, please report it.)

 
2/11/2011 10:24:56 PMsudha

i am not getting the output ... am not able to extract the records ... i used mysql workbench to create my database and tables .... can u help me ... its really urgent
(If this comment was disrespectful, please report it.)

 
2/11/2011 10:40:32 PMsudha

sir, i am getting this error - how do i fix it :
org.apache.jasper.JasperException: An exception occurred processing JSP page /1.jsp at line 15

12:
13:
14: <%
15: Class.forName("com.mysql.jdbc.Driver").newInstance();
(If this comment was disrespectful, please report it.)

 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.