How to use jsp page to display data from a database table

[LearningJava]

Hello Everyone,

I'm a beginner in JSP and trying to create a JSP page that will display data from MySQL database. I'm using Tomcat for my application server.

Here are my code files.

(DBConn.java)
package tryjsp;
import java.sql.*;
public class DBConn {
	Connection db_conn;
	public Connection getConnection()
	{
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String url = "jdbc:mysql:///" + "trylnp";
			String user = "root";
			String pass = "welcome";
		    	db_conn = DriverManager.getConnection(url,user,pass);  
		    	if((db_conn != null)&&(!db_conn.isClosed()))
			{
				System.out.println("Connected...");
			}
			return db_conn;
		}
		catch(Exception e)
		{
			//System.out.println("Error in connecting to database ");
			e.printStackTrace();

		}
		return null;
	}
}

(ReleaseData.java)
package tryjsp;

public class ReleaseData {
	private String useraction;
	private String releaseno;
	private int ninetyfivepercentile;
	private int ninetypercentile;
	private int fiftypercentile;
	
	public ReleaseData(){
	}
	
	public String getUseraction()
	{
		return useraction;
	}
	
	public void setUseraction(String value)
	{
		useraction = value;
	}
	
	public String getReleaseno()
	{
		return releaseno;
	} 
	
	public void setReleaseno(String value)
	{
		releaseno = value;
	}
	
	public int  getNinetyfivepercentile()
	{
		return ninetyfivepercentile;
	} 
	
	public void setNinetyfivepercentile(int value)
	{
		ninetyfivepercentile = value;
	}
	
	public int  getNinetypercentile()
	{
		return ninetypercentile;
	} 
	
	public void setNinetypercentile(int value)
	{
		ninetypercentile = value;
	}
	
	public int  getFiftypercentilee()
	{
		return fiftypercentile;
	} 
	
	public void setFiftypercentile(int value)
	{
		fiftypercentile = value;
	}
}

(ReleaseDataDAO.java)
package tryjsp;

import java.sql.*;
import java.util.*;

public class ReleaseDataDAO{
	private static DBConn db1;
	private static Connection connection;
	
	public List<ReleaseData> getReleaseData(){
		List<ReleaseData> myDataList = new ArrayList<ReleaseData>();
		ResultSet resultSet = null;
		Statement statement = null;
		try{
			db1 = new DBConn();
			connection = db1.getConnection();
			String releaseno="3.1.48A";
			String query = "Select * from releasedata where releaseno='" + releaseno + "'; ";
			statement = connection.createStatement();
			resultSet = statement.executeQuery(query);
			while(resultSet.next())
			{
				ReleaseData releaseData = new ReleaseData();
				releaseData.setUseraction(resultSet.getString("useraction"));
				releaseData.setReleaseno(resultSet.getString("releaseno"));
				releaseData.setNinetyfivepercentile(resultSet.getInt("ninetyfivepercentile"));
				releaseData.setNinetypercentile(resultSet.getInt("ninetypercentile"));
				releaseData.setFiftypercentile(resultSet.getInt("fiftypercentile"));	
				myDataList.add(releaseData);
			}
		}
		catch(Exception e){
			e.printStackTrace(); 
		}
		finally {
			if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } }
            if (statement != null) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } }
            if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } }
			
		}
		return myDataList;
	}
	
	/*public static void main(String args [])
	{
		ReleaseDataDAO rdao = new ReleaseDataDAO();
		List<ReleaseData> releaseDataList = rdao.getReleaseData();
		Iterator itr = releaseDataList.iterator();
		while(itr.hasNext())
		{
			ReleaseData rd = (ReleaseData) itr.next();
			System.out.println(" " + rd.getFiftypercentilee());
		}
		
	}*/
}

(DisplayServlet.java)
package tryjsp;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.*;

public class DisplayServlet extends HttpServlet {
	  public void doGet(HttpServletRequest request, 
		 HttpServletResponse response)
	        throws ServletException, IOException
	  {
	    	List<ReleaseData> myDataList = new ReleaseDataDAO().getReleaseData();
		request.setAttribute("myDataList", myDataList);
		request.getRequestDispatcher("showReleaseData.jsp").forward(request, response);
		
	  }
}

(showReleaseData.jsp)
<HTML>
<BODY>
Here is your data <BR>
<table>
<c:forEach items="${myDataList}" var="releaseData">
<tr>
<td>${releaseData.useraction}</td>
<td>${releaseData.releaseno}</td>
<td>${releaseData.ninetyfivepercentile}</td>
<td>${releaseData.ninetypercentile}</td>
<td>${releaseData.fiftypercentile}</td>
</tr>
</c:forEach>
</table>
</BODY>
</HTML>

Snippet from web.xml (C:\apache-tomcat-6.0.16\webapps\examples\WEB-INF)
 
<servlet>
        <servlet-name>DisplayServlet</servlet-name>
        <servlet-class>tryjsp.DisplayServlet</servlet-class>
</servlet>
<servlet-mapping>
        <servlet-name>DisplayServlet</servlet-name>
        <url-pattern>/getReleaseData</url-pattern>
</servlet-mapping>

I've my four java classes in the directory C:\apache-tomcat-6.0.16\webapps\examples\WEB-INF\classes\tryjsp and my jsp file in C:\apache-tomcat-6.0.16\webapps\examples.

When I point my browser to http://localhost:8080/examples/getReleaseData, I do see the static text from my jsp file but NOT the contents from db.

Please help me out.

[anaid]

Hello,
I am also a beginer in JSP and I have encounterd the same problem when trying to dysplay data from the database.
If anyone has an answer to this problem please let us know.

Thanks for your support.

[Hack]

This might help.

[saichandv]

Insted of that settter and getter methods class simply create of data base table and by using the jdbc statements in JSP u can get the data......



Only one jsp file is needed for all those.........



<html>
<head><title>updateclient</title></head>
<body>
<%@ page import="java.sql.*"%>
<%@ page import="java.io.*"%>
<%!
Statement st;
String qua,qua1;
Connection conn;
%>
<%

try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("Jdbcdbc:eris","scott","tiger");
st=con.createStatement();

}catch(Exception e){

}
%>




simple example for displaying the data from data base

[xeel]

chees, same here... When do people start using CODE tags?

Anyways... here it is. The POJO way:

<&#37;@ page contentType="text/html; charset=iso-8859-1" pageEncoding="ISO-8859-1" language="java" import="java.sql.*;"%>
<%!
public Connection cOpen() throws SQLException {
	Connection con = null;
	try {
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		con = DriverManager.getConnection("jdbc:mysql://hostname/database", "user", "pwd");
	} catch (Exception e) {
		e.printStackTrace(System.out);
	}
	return con;
}%>

<%
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String SQL = "";
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
	<meta http-equiv="Content-Type" content="application/xhtml+xml; charset=iso-8859-1" />
	<title>title</title>
</head>

<body>

<table>
	<tr>
		<th>&lt;Name</th>
		<th>Surname</th>
	</tr>
	<%
	try {
		con = cOpen();
		SQL = "SELECT name,surname FROM people WHERE age > ?";
		stmt = con.prepareStatement(SQL);
		stmt.setInt(1, 25);
		rs = stmt.executeQuery();
		while (rs.next()) {%>
			<tr>
				<td><%=rs.getString(1)%></td>
				<td><%=rs.getString(2)%></td>
			</tr>
		<%}
	} catch (SQLException e) {
		e.printStackTrace(System.out);
	} finally {
		try {stmt.close();} catch (Exception e) {
		}
		try {rs.close();} catch (Exception e) {
		}
		try {
			if (null != con) {con.close();}
		} catch (SQLException ex) {
			ex.printStackTrace(System.out);
		}
	}
	%>
</table>
</body>
</html>

the example uses MySQL java driver, XHTML standard for webpage and some random query from an imagined database...

Though my personal recommendation would be not to to this. Connector class and the method that gets the data should be in separate classes/files. So you just include the class where the method is and display it.

<table>
	<tr>
		<th>Name</th>
		<th>Surname</th>
	</tr>
	<% MyLib lib = new MyLib();
	int age = 25;
	ArrayList<String[]> people = lib.getPeople(age);
	for(String[] pData : people){%>
		<tr>
			<td><%=pData[0]%></td>
			<td><%=pData[1]%></td>
		</tr>
	<% }%>
</table>

supposing that the getPeople() returns an ArrayList of String arrays filled with some data like people names in the previous example.

[Hack]

chees, same here... When do people start using CODE tags?

Usually after they learn they exist. The overwhelming majority of first time posters don't know about them.

[xeel]

Usually after they learn they exist. The overwhelming majority of first time posters don't know about them.

There's a freaking fight on codeguru because of this.

Btw, since you're super-moder, could you set code in HTML tags to be monospaced? It's kinda difficult to read... Also it would be better to increase code frames width. Standard lower resolution is 1024px these days.

[Hack]

Btw, since you're super-moder, could you set code in HTML tags to be monospaced? It's kinda difficult to read... Also it would be better to increase code frames width. Standard lower resolution is 1024px these days.

I can make modifications and edits to threads, but not the site itself.