Connecting JSP with MySQL
- Friday, January 2, 2009, 23:04
- PHP & MySQL, Web Security
- 2 comments
Here is a tutorial which will show you how to connect to a MySQL database using JSP under windows and using Tomcat web server. Before getting into the code, i would just put a notice to the requirements needed.
Requirements:
1. MySQL
http://www.mysql.com
2. Tomcat – version 5.5 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
Now let us assume that you know the basics of java programming and MySQL. It is also assumed that you have already installed MySQL database and a table to pull data form. By defualt Tomcat will be extracted to C:\Tomcat, however you can place the distribution files anywhere you wish.
Installation
In the Installation process , you have three things to do with. First Install the Java 2 JRE. Next Extract the Tomcat distribution files to a folder and copy the MySQL Connector JAR file to the C:\Tomcat\common\lib folder (ie, mysql-connector-java-2.0.14.jar).
Environmental Variables
Add the following environmental variables to Windows:
JAVA_HOME=C:\java\jre TOMCAT_HOME=C:\Tomcat
If you have installed JAVA in some other path, then replace C:\java\jre with your path of installation.
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
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
Coding Section
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/databasename?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>
In the above code you have to change the username and password to match your database username and password. This would explain you much better in configuration
String connectionURL = “jdbc:mysql://localhost:3306/databasename?user=someuser;password=somepass”;
databasename = your created new database
user = someuser
// the name of the username which you have allocated for your database.
password=somepass
// password for your database.
Finally, change the mytable and myfield values in the script to match a table and field that exist within your database.
I hope that this tutorial has helped you in connecting your database using JSP.
Popularity: 16% [?]
About the Author
Vinothbabu has written 24 stories on this site.
In short, my name is Vinoth babu { perra kettala summa authrithala } a UI expert from India, Chennai.
2 Comments on “Connecting JSP with MySQL”
Write a Comment
Gravatars are small images that can show your personality. You can get your gravatar for free today!
Hi there,
This is a great article but i’m a bit concerned with storing clear text username and password inside the JSP page. Is it secure? Does it work similar to PHP?
Thank you
Prashant
this is not secure. u r implementing the business logic in the presentation area. its outdated. u can try MVC framework.