JSP Wildfly and Database #10

In this JSP WildFly DB Example, we will retrieve data from the SQL Server ‘s Pubs database and show it to the browsing user.

Table of Contents

Web.xml

<resource-ref>
	<res-ref-name>MSSQLDS</res-ref-name>
	<res-type>javax.sql.DataSource</res-type>
	<res-auth>Container</res-auth>
	<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

<resource-ref>

<res-ref-name>MSSQLDS</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

<res-sharing-scope>Shareable</res-sharing-scope>

</resource-ref>

jboss-web.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jboss-web PUBLIC
    "-//JBoss//DTD Web Application 5.0//EN"
    "http://www.jboss.org/j2ee/dtd/jboss-web_5_0.dtd">
<jboss-web>
	<resource-ref>
		<res-ref-name>MSSQLDS</res-ref-name>
		<jndi-name>java:/MSSQLDS</jndi-name>
	</resource-ref>
</jboss-web>

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE jboss-web PUBLIC

"-//JBoss//DTD Web Application 5.0//EN"

"http://www.jboss.org/j2ee/dtd/jboss-web_5_0.dtd">

<jboss-web>

<resource-ref>

<res-ref-name>MSSQLDS</res-ref-name>

<jndi-name>java:/MSSQLDS</jndi-name>

</resource-ref>

</jboss-web>

DisplayBooks.java

package tube.codingexamples.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import tube.codingexamples.bean.Title;

/**
 * Servlet implementation class DisplayStores
 */
@WebServlet("/DisplayBooks")
public class DisplayBooks extends HttpServlet {
  //Sample 01: Refer the Datasource using JNDI Name
  @Resource(name="MSSQLDS")
  private DataSource ds;  
  @Override
  protected void doGet(HttpServletRequest req, HttpServletResponse resp) 
      throws ServletException, IOException {
    //Sample 02: SQL String to Get Data From Database
    String SQL = "Select Title, Type, Price from titles";
    
    try 
    {
      //Sample 03: Open Connection to Pubs DB
      Connection con = null;
      con = ds.getConnection();
      
      //Sample 04: Get Statement Object from the Connection
      Statement stmt = con.createStatement();  
      
      //Sample 05: Get the Result of Query
      ResultSet rsTitle = stmt.executeQuery(SQL);  
      
      //Sample 07: Iterate the ResultSet and Collect Title Beans
      List Titles = new ArrayList();
      while (rsTitle.next())
      {
        Title BookTitle = new Title();
        BookTitle.setBookTitle(rsTitle.getString("Title"));
        BookTitle.setType(rsTitle.getString("Type"));
        BookTitle.setBookPrice(rsTitle.getDouble("Price"));
        Titles.add(BookTitle);
      }
      
      //Sample 08: Cleanup DB Objects 
      rsTitle.close();
      stmt.close();
      con.close();
      
      //Sample 09: Set Titles Bean to Request Context
      //and Forward it to a JSP File
      req.setAttribute("BookTitles", Titles);
      RequestDispatcher reqD = req.getRequestDispatcher("DisplayBooks5.jsp");
      reqD.forward(req, resp);
    } 
    catch (SQLException e) 
    {
      e.printStackTrace();
    }
  }

  private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public DisplayBooks() {
        super();
        // TODO Auto-generated constructor stub
    }

}

package tube.codingexamples.servlet;

import java.io.IOException;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import javax.annotation.Resource;

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.sql.DataSource;

import tube.codingexamples.bean.Title;

/**

* Servlet implementation class DisplayStores

@WebServlet("/DisplayBooks")

public class DisplayBooks extends HttpServlet {

//Sample 01: Refer the Datasource using JNDI Name

@Resource(name="MSSQLDS")

private DataSource ds;

@Override

protected void doGet(HttpServletRequest req, HttpServletResponse resp)

throws ServletException, IOException {

//Sample 02: SQL String to Get Data From Database

String SQL = "Select Title, Type, Price from titles";

try

{

//Sample 03: Open Connection to Pubs DB

Connection con = null;

con = ds.getConnection();

//Sample 04: Get Statement Object from the Connection

Statement stmt = con.createStatement();

//Sample 05: Get the Result of Query

ResultSet rsTitle = stmt.executeQuery(SQL);

//Sample 07: Iterate the ResultSet and Collect Title Beans

List Titles = new ArrayList();

while (rsTitle.next())

{

Title BookTitle = new Title();

BookTitle.setBookTitle(rsTitle.getString("Title"));

BookTitle.setType(rsTitle.getString("Type"));

BookTitle.setBookPrice(rsTitle.getDouble("Price"));

Titles.add(BookTitle);

}

//Sample 08: Cleanup DB Objects

rsTitle.close();

stmt.close();

con.close();

//Sample 09: Set Titles Bean to Request Context

//and Forward it to a JSP File

req.setAttribute("BookTitles", Titles);

RequestDispatcher reqD = req.getRequestDispatcher("DisplayBooks5.jsp");

reqD.forward(req, resp);

}

catch (SQLException e)

{

e.printStackTrace();

}

private static final long serialVersionUID = 1L;

/**

* @see HttpServlet#HttpServlet()

public DisplayBooks() {

super();

// TODO Auto-generated constructor stub

}

DisplayBooks.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!-- Sample 10: Import the required header Files -->
<%@page import="tube.codingexamples.bean.Title"%>
<%@page import="java.util.ArrayList"%>    
<%@page import="java.util.List"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
  "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Books List from Pubs DB</title>
</head>
<!-- Sample 11: Make of useBean Tag to refer bean collection -->
<jsp:useBean id="BookTitles" class="java.util.ArrayList" scope="request" />
<body>
<!-- Sample 12: Now Iterate the Bean Collection and Display Result -->
<%
	for(int i=0; i<BookTitles.size(); i++)
	{
		Title BookTitle = (Title) BookTitles.get(i);
%>
	<h4>Book Name: <%= BookTitle.getBookTitle()%></h4>
	<p>Type     : <%= BookTitle.getType()%></p>
	<p>Price    : <%= BookTitle.getBookPrice()%></p>
<%		
	}
%>
<p>--------------------------- x ------------------------------- </p>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

pageEncoding="ISO-8859-1"%>

<%@page import="tube.codingexamples.bean.Title"%>

<%@page import="java.util.ArrayList"%>

<%@page import="java.util.List"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

"http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>Books List from Pubs DB</title>

</head>

<jsp:useBean id="BookTitles" class="java.util.ArrayList" scope="request" />

<body>

for(int i=0; i<BookTitles.size(); i++)

{

Title BookTitle = (Title) BookTitles.get(i);

<p>Price : <%= BookTitle.getBookPrice()%></p>

}

</body>

</html>

Title.Java (Title Bean)

package tube.codingexamples.bean;
//Sample 06: Title Bean Class
public class Title 
{
	//6.1: Bean Fields
	private String bookTitle = "";
	private String type = "";
	double bookPrice = 0.0;
	
	//6.2: Bean Properties
	public String getBookTitle() 
	{
		return bookTitle;
	}
	public void setBookTitle(String bookTitle) 
	{
		this.bookTitle = bookTitle;
	}
	public String getType() 
	{
		return type;
	}
	public void setType(String type) 
	{
		this.type = type;
	}
	public double getBookPrice() 
	{
		return bookPrice;
	}
	public void setBookPrice(double bookPrice) 
	{
		this.bookPrice = bookPrice;
	}
}

package tube.codingexamples.bean;

//Sample 06: Title Bean Class

public class Title

{

//6.1: Bean Fields

private String bookTitle = "";

private String type = "";

double bookPrice = 0.0;

//6.2: Bean Properties

public String getBookTitle()

{

return bookTitle;

}

public void setBookTitle(String bookTitle)

{

this.bookTitle = bookTitle;

}

public String getType()

{

return type;

}

public void setType(String type)

{

this.type = type;

}

public double getBookPrice()

{

return bookPrice;

}

public void setBookPrice(double bookPrice)

{

this.bookPrice = bookPrice;

}

YouTube

Categories: JSP

Do you like this Example? Please comment about it for others!! Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Programming Examples

Are you a Programmer or Application Developer or a DBA? Take a cup of coffee, sit back and spend few minutes here :)

Web.xml

jboss-web.xml

DisplayBooks.java

DisplayBooks.jsp

Title.Java (Title Bean)

Like this:

Related

Do you like this Example? Please comment about it for others!! Cancel reply

Programming Examples

Are you a Programmer or Application Developer or a DBA? Take a cup of coffee, sit back and spend few minutes here :)

JSP Wildfly and Database #10

Web.xml

jboss-web.xml

DisplayBooks.java

DisplayBooks.jsp

Title.Java (Title Bean)

Share this:

Like this:

Related

Do you like this Example? Please comment about it for others!! Cancel reply