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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

View Poll Results: I think I chose this option by mistake, please ignore

Voters
0. You may not vote on this poll
  • a

    0 0%
  • b

    0 0%
  • c

    0 0%
  • d

    0 0%
Results 1 to 8 of 8

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

  1. #1
    Join Date
    May 2008
    Posts
    1

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

    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.
    Code:
    (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.
    Last edited by Hack; 11-03-2008 at 07:09 AM.

  2. #2
    Join Date
    Nov 2008
    Posts
    1
    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.

  3. #3
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    This might help.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  4. #4
    Join Date
    Nov 2008
    Posts
    1

    Smile hellooo

    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

  5. #5
    Join Date
    Nov 2008
    Posts
    8
    chees, same here... When do people start using CODE tags?

    Anyways... here it is. The POJO way:
    HTML Code:
    <&#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.
    HTML Code:
    <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.
    Last edited by xeel; 11-14-2008 at 11:41 AM.

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by xeel View Post
    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.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  7. #7
    Join Date
    Nov 2008
    Posts
    8
    Quote Originally Posted by Hack
    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.
    Last edited by xeel; 11-14-2008 at 07:36 PM.

  8. #8
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by xeel View Post
    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.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

Similar Threads

  1. Ajax - Cant pass value from jsp page to js
    By vitalstrike82 in forum AJAX
    Replies: 1
    Last Post: 10-12-2006, 03:09 AM
  2. unable to read data from database
    By dppalepu in forum Java
    Replies: 2
    Last Post: 07-27-2006, 03:15 AM
  3. Replies: 0
    Last Post: 08-23-2002, 04:25 AM
  4. Adding Data to a Database Field in VB6
    By Brian Higgins in forum VB Classic
    Replies: 1
    Last Post: 11-26-2001, 11:19 PM
  5. Replies: 3
    Last Post: 12-05-2000, 12:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center