|
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: |
248409 |
|
(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...
- 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.
- 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.
- You may link to this article from another website, but ONLY if it is not wrapped in a frame.
- 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:
- MySQL
http://www.mysql.com
- Tomcat - version 4.1.12 Standard
used for this tutorial
http://jakarta.apache.org/site/binindex.html
- Java 2 JRE - version 1.4.1 used for
this tutorial
http://java.sun.com/j2se/1.4.1/download.html
- MySQL Connector/J
- version 2 used for this tutorial
http://www.mysql.com/downloads/api-jdbc.html
Assumptions:
- It is assumed that you already have
a MySQL database installed and a table to pull data from.
- It assumes you understand SQL, and
probably have done some web/database scripting with other languages.
- 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.
- You know the basics of programming
Java. If you do not, I highly recommend you check out Sun's
Java
Tutorial.
Section A - Installation
- 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.
- 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.
- 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
|
Your Vote
|
| |
Other User Comments
|
 5/4/2001 9:59:33 PM: ahfook
very good tutorial for JSP beginner.
(If this comment was disrespectful, please report it.)
|
11/13/2001 12:57:44 PM: hui
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 PM: Shawn
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 PM: allaine
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 AM: Me
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 AM: Vincent 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 AM: Missing 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 PM: Bogdan 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 PM: Mike
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 PM: Tristan
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 AM: sudhir 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 AM: pradeep
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 AM: sat
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 PM: zyd
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 AM: ebele
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 AM: A 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 AM: abdellatif
the code have many faults !!
(If this comment was disrespectful, please report it.)
|
9/12/2007 7:16:02 AM: abdellatif
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 AM: malatesh024
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 PM: Dragan 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 PM: sudha
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 PM: sudha
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.
|
