|
|
|
| |
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:
- 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! |
|
See Voting Log |
| |
| Other User Comments |
5/4/2001 9:59:33 PM:ahfook
very good tutorial for JSP beginner.
|
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!
|
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..
|
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?
|
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?
|
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.
|
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.
|
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:\To
mcat
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.
|
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
|
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
|
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
|
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!
|
12/3/2002 9:20:34 PM:
Do we just need to
|
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!
|
12/8/2002 10:16:10 PM:
can i save my file to folder other than
the ROOT folder in tomcat?
|
12/10/2002 11:05:30 AM:
Nice and Concise!
|
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.
|
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. =)
|
12/21/2002 11:10:45 AM:
I got exceptions at first, but realized
in the connection URL, you need a '
|
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!
|
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!
|
3/4/2003 4:42:19 AM:
it is very helpul for begineer like me.
Thanks alot
|
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
|
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
|
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!
|
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
|
8/20/2003 4:51:59 PM:
Hi, thanks much for the clear
|
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?
|
|
| Add Your Feedback! |
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.
NOTICE: The author of this article has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.
For feedback not related to this particular article, please click here.
|
|
