To connect with a database, you need the following things.

1. A Database System.
2. A Database Connector
3. Apache Tomcat
4. jdk

The database system we use is MySQL. You can download it from the following location.

http://www.filehippo.com/download_mysql/

At the time of installation, you can configure the username and password.

After installation, you can go to Start->Programs->MySQL->MySQL Command Line Client. Give password to login.

You can create a database and table using the following commands in MySQL.

SQL> create database Company;
SQL> use Company;
SQL> create table Employee(Empid varchar(20),EmpName varchar(20),Designation varchar(20),primary key(Empid));
SQL> insert into Employee values('SCH001','Krishna','Software Engineer');
SQL> insert into Employee values('SCH002','Steve','Interface Designer');

Also we need an appropriate database connector which makes connection between jdk and MySQL.
You can download the connector from the following link.

http://mysqlmirror.netandhost.in/Downloads/Connector-J/mysql-connector-java-5.1.11.zip

Extract the contents of the zipped file to any location in your computer.

After doing this, you need to set class path in Envirornment variables. You can do that by following the steps below:

My Computer->Properties->Advanced->Environment Variables->User Variables.

You can click New and have 2 text fields: Variable Name and Variable Value.

You can give as follows:

Variable Name: classpath
Variable Value: .;C:\mysql-connector-java-5.0.8\mysql-connector-java-5.0.8-bin.jar;

Note: If you have a different version of Connector, you need to give appropriate connector name.
After this, create a JSP page with the following code. The Variable Value should also contain .; in the beginning and ; in the end. Also copy the jar file of Connector inside the lib directory of Tomcat.

Now you can write a JSP page with the following code as follows:

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”
“http://www.w3.org/TR/html4/loose.dtd”>

<%@ page import=”java.sql.*” %>
<%@ page import=”java.io.*” %>

<html>
<head>
<title>Connection with mysql database</title>
</head>
<body>

<%
try {

String connectionURL = “jdbc:mysql://localhost:3306/Company”;
Connection connection = null;
Class.forName(“com.mysql.jdbc.Driver”).newInstance();
connection = DriverManager.getConnection(connectionURL, “root”, “root”);
Statement st=connection.createStatement();
ResultSet rs=st.executeQuery(“select * from Employee”);
%>

<table cellpadding=”15″ border=”1″ style=”background-color: #ffffcc;”>

<%
while (rs.next()) {
%>
<tr>
<td><%=rs.getString(1)%></td>
<td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
</tr>
<% } %>
<% }

catch(Exception ex){
out.println(“Unable to connect to database.”);
}
%>

</body>
</html>

Place the JSP file inside a folder which should be resided in C:\Program Files\Apache Software Foundation\Tomcat 6.0\webapps\MyDatabase

Here I gave the folder name as “MyDatabase”.

Make sure that the Tomcat has been started, Take any browser, type the folowing link.

http://localhost:8080/MyDatabase/DatabaseDemo.jsp

You can see the values in the JSP Page in the form of a Table.

Tagged with:

Filed under: JSP

Like this post? Subscribe to my RSS feed and get loads more!