MySQL Database Connectivity with JSP (Windows)
2020 days ago  Quote('55852','55852','5','522')">Report spam 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) 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!
---
Joy
Comments: 0 Views: 2039
Tags: jsp | 
