MySQL Database Connectivity with JSP
This tutorial will show you how to connect to a MySQL database using JSP (JavaServer Pages) under Windows and Tomcatweb 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
Apache Tomcat - version 5.5 is used for this tutorial http://tomcat.apache.org/download-55.cgi
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 5.1.18 used for this tutorial.MySQL Connector/J is the official JDBC
driver for MySQL. http://www.mysql.com/downloads/connector/j/
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, "username", "password"); 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";
No comments:
Post a Comment