Entity JPA Query - Return Multiple Entities

In this video, we will retrieve multiple JPA Entties via JPAQL. This video implements Get All Accounts flow.

Table of Contents

Remote Interface

package tube.codingexamples.ejb.statelessbean;

import java.util.List;

import javax.ejb.Remote;

import tube.coding.examples.jpa.entity.SavingsAccount;

@Remote
public interface SavingAcBeanRemote {
  //Sample 3.7: Declare the interface method
  public int newAccount(String accountHolderName, int initialBalance);
  
  //Sample 4.00a: Add Methods for Deposit, Withdraw and Account Closure
  public int depositMoney(int PersonalBankingId, int amount);
  public int withdrawMoney(int PersonalBankingId, int amount);
  public boolean closeSavingsAc(int PersonalBankingId);  
  
  //Sample 5.04: Find Account & return
  public SavingsAccount findById(int pid);
  public void TransferFund(
    SavingsAccount fromAc, 
    SavingsAccount toAc, 
    int amount);
  
  //Sample 6.04: Declare method to Search By Id
  public SavingsAccount queryById(int personalBankingId);  
  
  //Sample 6.11: Declare method to search by name
  public SavingsAccount queryByName(String personName);  
  
  //Sample 6.14: Declare method to List All accounts
  public List<SavingsAccount> queryAllAccounts();  
}

package tube.codingexamples.ejb.statelessbean;

import java.util.List;

import javax.ejb.Remote;

import tube.coding.examples.jpa.entity.SavingsAccount;

@Remote

public interface SavingAcBeanRemote {

//Sample 3.7: Declare the interface method

public int newAccount(String accountHolderName, int initialBalance);

//Sample 4.00a: Add Methods for Deposit, Withdraw and Account Closure

public int depositMoney(int PersonalBankingId, int amount);

public int withdrawMoney(int PersonalBankingId, int amount);

public boolean closeSavingsAc(int PersonalBankingId);

//Sample 5.04: Find Account & return

public SavingsAccount findById(int pid);

public void TransferFund(

SavingsAccount fromAc,

SavingsAccount toAc,

int amount);

//Sample 6.04: Declare method to Search By Id

public SavingsAccount queryById(int personalBankingId);

//Sample 6.11: Declare method to search by name

public SavingsAccount queryByName(String personName);

//Sample 6.14: Declare method to List All accounts

public List<SavingsAccount> queryAllAccounts();

}

Bean Implementation [JPAQL – Multiple Result]

package tube.codingexamples.ejb.statelessbean;

import java.util.List;

import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import tube.coding.examples.jpa.entity.SavingsAccount;
@Stateless(mappedName = "SavingAcBean")
@LocalBean
public class SavingAcBean implements SavingAcBeanRemote {
  //Sample 3.6: Create Entiry Manager
  //UnitName is from persistance.xml
  @PersistenceContext(unitName = "JPASavingAccount")
  EntityManager em;  
  
    public SavingAcBean() {}
    
    //Sample 3.8: Create New Account
  public int newAccount(String accountHolderName, int initialBalance) {
    SavingsAccount account = new SavingsAccount();
    account.setBal(initialBalance);
    account.setpName(accountHolderName);
    em.persist(account);
    return account.getPid();
  }   
  
  //Sample 4.00b: Deposit amount to Bank Account
  @Override
  public int depositMoney(int PersonalBankingId, int amount)
  {
    //Sample 4.20: Find the Account & call deposit on JPA
    SavingsAccount SavingAc = 
        em.find(SavingsAccount.class, 
        PersonalBankingId);
    return SavingAc.deposit(amount);
  }  
  
  //Sample 4.00c: Withdraw amount from Savings Account
  public int withdrawMoney(int PersonalBankingId, int amount)
  {
    //Sample 4.21: Find the account & Call withdraw on JPA
    SavingsAccount SavingAc = 
        em.find(SavingsAccount.class, 
        PersonalBankingId);
    return SavingAc.withdraw(amount);
  }
  
  //Sample 4.00d: Close the account. 
  public boolean closeSavingsAc(int PersonalBankingId)
  {
    //Sample 4.22: Find the account and delete the record. Note=> em.remove
    boolean ret = false;
    SavingsAccount SavingAc = 
        em.find(SavingsAccount.class, 
        PersonalBankingId);
    if (SavingAc != null)
    {
      em.remove(SavingAc);
      ret = true;
    }
    return ret;  
  }

  //Sample 5.05: Find account by id and return it
  @Override
  public SavingsAccount findById(int pid) {
    SavingsAccount SavingAc = em.find(SavingsAccount.class,pid);
    return SavingAc;
  }

  //Sample 5.06: Perform Fund Transfer
  @Override
  public void TransferFund(SavingsAccount fromAc, 
  SavingsAccount toAc, int amount) {
    //5.03.1: First Change the From Account
    SavingsAccount ac1 = em.merge(fromAc);
    ac1.withdraw(amount);
    
    //5.03.2: Now Change the To Account
    SavingsAccount ac2 = em.merge(toAc);
    ac2.deposit(amount);
  }
  
  //Sample 6.05: Use Entity manager to filter account by id
  public SavingsAccount queryById(int personalBankingId)
  {
    SavingsAccount SavingAc = 
        em.find(SavingsAccount.class, 
        personalBankingId);
    return SavingAc;
  }
  
  //Sample 6.12: Use JPA QL to filter by account name
  public SavingsAccount queryByName(String personName)
  {
    String JpaSql = "Select sac from SavingsAccount sac " +
        "where sac.pName = :PersonName";
    Query query = em.createQuery(JpaSql);
    query.setParameter("PersonName", personName);
    SavingsAccount sa = (SavingsAccount) query.getSingleResult();
    return sa;
  }  
  
  //Sample 6.15: Use JPA QL to fetch all accounts
  @SuppressWarnings("unchecked")
  public List<SavingsAccount> queryAllAccounts()
  {
    String JpaSql = "Select sac from SavingsAccount sac";
    Query query = em.createQuery(JpaSql);
    List<SavingsAccount> accounts= query.getResultList();
    return accounts;
  }  
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

package tube.codingexamples.ejb.statelessbean;

import java.util.List;

import javax.ejb.LocalBean;

import javax.ejb.Stateless;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import javax.persistence.Query;

import tube.coding.examples.jpa.entity.SavingsAccount;

@Stateless(mappedName = "SavingAcBean")

@LocalBean

public class SavingAcBean implements SavingAcBeanRemote {

//Sample 3.6: Create Entiry Manager

//UnitName is from persistance.xml

@PersistenceContext(unitName = "JPASavingAccount")

EntityManager em;

public SavingAcBean() {}

//Sample 3.8: Create New Account

public int newAccount(String accountHolderName, int initialBalance) {

SavingsAccount account = new SavingsAccount();

account.setBal(initialBalance);

account.setpName(accountHolderName);

em.persist(account);

return account.getPid();

}

//Sample 4.00b: Deposit amount to Bank Account

@Override

public int depositMoney(int PersonalBankingId, int amount)

{

//Sample 4.20: Find the Account & call deposit on JPA

SavingsAccount SavingAc =

em.find(SavingsAccount.class,

PersonalBankingId);

return SavingAc.deposit(amount);

}

//Sample 4.00c: Withdraw amount from Savings Account

public int withdrawMoney(int PersonalBankingId, int amount)

{

//Sample 4.21: Find the account & Call withdraw on JPA

SavingsAccount SavingAc =

em.find(SavingsAccount.class,

PersonalBankingId);

return SavingAc.withdraw(amount);

}

//Sample 4.00d: Close the account.

public boolean closeSavingsAc(int PersonalBankingId)

{

//Sample 4.22: Find the account and delete the record. Note=> em.remove

boolean ret = false;

SavingsAccount SavingAc =

em.find(SavingsAccount.class,

PersonalBankingId);

if (SavingAc != null)

{

em.remove(SavingAc);

ret = true;

}

return ret;

}

//Sample 5.05: Find account by id and return it

@Override

public SavingsAccount findById(int pid) {

SavingsAccount SavingAc = em.find(SavingsAccount.class,pid);

return SavingAc;

}

//Sample 5.06: Perform Fund Transfer

@Override

public void TransferFund(SavingsAccount fromAc,

SavingsAccount toAc, int amount) {

//5.03.1: First Change the From Account

SavingsAccount ac1 = em.merge(fromAc);

ac1.withdraw(amount);

//5.03.2: Now Change the To Account

SavingsAccount ac2 = em.merge(toAc);

ac2.deposit(amount);

}

//Sample 6.05: Use Entity manager to filter account by id

public SavingsAccount queryById(int personalBankingId)

{

SavingsAccount SavingAc =

em.find(SavingsAccount.class,

personalBankingId);

return SavingAc;

}

//Sample 6.12: Use JPA QL to filter by account name

public SavingsAccount queryByName(String personName)

{

String JpaSql = "Select sac from SavingsAccount sac " +

"where sac.pName = :PersonName";

Query query = em.createQuery(JpaSql);

query.setParameter("PersonName", personName);

SavingsAccount sa = (SavingsAccount) query.getSingleResult();

return sa;

}

//Sample 6.15: Use JPA QL to fetch all accounts

@SuppressWarnings("unchecked")

public List<SavingsAccount> queryAllAccounts()

{

String JpaSql = "Select sac from SavingsAccount sac";

Query query = em.createQuery(JpaSql);

List<SavingsAccount> accounts= query.getResultList();

return accounts;

}

Servlet Controller Layer

package tube.codingexamples.servlets;

import java.io.IOException;
import java.util.List;

import javax.ejb.EJB;
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 tube.coding.examples.jpa.entity.SavingsAccount;
import tube.codingexamples.ejb.statelessbean.SavingAcBeanRemote;

@WebServlet("/QuerySavingAc")
public class QuerySavingAc extends HttpServlet {
  //Sample 6.03: Have EJB Reference via Annotation
  @EJB(beanName="SavingAcBean")
  SavingAcBeanRemote SACBean;  
  
  private static final long serialVersionUID = 1L;
       
    public QuerySavingAc() {
        super();
    }

  protected void doPost(HttpServletRequest request, 
      HttpServletResponse response) 
          throws ServletException, IOException 
  {
    //Sample 6.02: Get the request Parameters
    SavingsAccount account = null;
    String pid = request.getParameter("Pid");
    String submit1 = request.getParameter("Submit1");
    String pname = request.getParameter("Pname");
    String submit2 = request.getParameter("Submit2");
    String balance = request.getParameter("Balance");
    String submit3 = request.getParameter("Submit3");
    String BalSearchOption = request.getParameter("BalSearch");  
    String submit4 = request.getParameter("Submit4");
    
    //Sample 6.06: Search By Customer Id
    if ( 
        (pid != null && pid.trim().length() > 0) &&
        (submit1 != null && submit1.trim().length() > 0) &&
        (submit1.equalsIgnoreCase("Search") == true) )
    {
      int personalBankingId = new Integer(pid).intValue();
      account = SACBean.queryById(personalBankingId);
      
      //Sample 6.07: Delegate Display output to Query Result JSP
      request.setAttribute("Account", account);
      RequestDispatcher respJSP = 
        request.getRequestDispatcher("QueryResultByIdorName.jsp");
      respJSP.forward(request, response);      
    }
    
    //Sample 6.13: Search by Owner Name
    if ( (pname != null && pname.trim().length() > 0) &&
        (submit2 != null && submit2.trim().length() > 0) &&
        (submit2.equalsIgnoreCase("Search") == true) )
    {
      account = SACBean.queryByName(pname);
      request.setAttribute("Account", account);
      RequestDispatcher respJSP = 
        request.getRequestDispatcher("QueryResultByIdorName.jsp");
      respJSP.forward(request, response);      
    }
    
    //Sample 6.16: List all accounts
    if (  (submit4 != null && submit4.trim().length() > 0) &&
        submit4.equalsIgnoreCase("Get All Accounts") == true)
    {
      List<SavingsAccount> accounts = SACBean.queryAllAccounts();
      request.setAttribute("Accounts", accounts);
      RequestDispatcher respJSP = 
        request.getRequestDispatcher("QueryResultMultiple.jsp");
      respJSP.forward(request, response);        
    }    
  }
}

package tube.codingexamples.servlets;

import java.io.IOException;

import java.util.List;

import javax.ejb.EJB;

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 tube.coding.examples.jpa.entity.SavingsAccount;

import tube.codingexamples.ejb.statelessbean.SavingAcBeanRemote;

@WebServlet("/QuerySavingAc")

public class QuerySavingAc extends HttpServlet {

//Sample 6.03: Have EJB Reference via Annotation

@EJB(beanName="SavingAcBean")

SavingAcBeanRemote SACBean;

private static final long serialVersionUID = 1L;

public QuerySavingAc() {

super();

}

protected void doPost(HttpServletRequest request,

HttpServletResponse response)

throws ServletException, IOException

{

//Sample 6.02: Get the request Parameters

SavingsAccount account = null;

String pid = request.getParameter("Pid");

String submit1 = request.getParameter("Submit1");

String pname = request.getParameter("Pname");

String submit2 = request.getParameter("Submit2");

String balance = request.getParameter("Balance");

String submit3 = request.getParameter("Submit3");

String BalSearchOption = request.getParameter("BalSearch");

String submit4 = request.getParameter("Submit4");

//Sample 6.06: Search By Customer Id

if (

(pid != null && pid.trim().length() > 0) &&

(submit1 != null && submit1.trim().length() > 0) &&

(submit1.equalsIgnoreCase("Search") == true) )

{

int personalBankingId = new Integer(pid).intValue();

account = SACBean.queryById(personalBankingId);

//Sample 6.07: Delegate Display output to Query Result JSP

request.setAttribute("Account", account);

RequestDispatcher respJSP =

request.getRequestDispatcher("QueryResultByIdorName.jsp");

respJSP.forward(request, response);

}

//Sample 6.13: Search by Owner Name

if ( (pname != null && pname.trim().length() > 0) &&

(submit2 != null && submit2.trim().length() > 0) &&

(submit2.equalsIgnoreCase("Search") == true) )

{

account = SACBean.queryByName(pname);

request.setAttribute("Account", account);

RequestDispatcher respJSP =

request.getRequestDispatcher("QueryResultByIdorName.jsp");

respJSP.forward(request, response);

}

//Sample 6.16: List all accounts

if ( (submit4 != null && submit4.trim().length() > 0) &&

submit4.equalsIgnoreCase("Get All Accounts") == true)

{

List<SavingsAccount> accounts = SACBean.queryAllAccounts();

request.setAttribute("Accounts", accounts);

RequestDispatcher respJSP =

request.getRequestDispatcher("QueryResultMultiple.jsp");

respJSP.forward(request, response);

}

JSP Presentation Layer

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!-- Sample 6.17: Required Imports -->
<%@ page import="java.util.List"%>
<%@ page import="tube.coding.examples.jpa.entity.SavingsAccount"%>    
<!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>Insert title here</title>
</head>
<body>
<%
    //Sample 6.18: Retrieve the accounts List from request attribute
    List<SavingsAccount> accounts = null;
    accounts = (List<SavingsAccount>) request.getAttribute("Accounts");
%>
  <h1>List of Accounts</h1>
<%
    //Sample 6.19: Iterate the List and display result with 
    //alternate div tag style
    int count = accounts.size();
    for (int index = 0; index < count; index++) 
    {
      SavingsAccount account = accounts.get(index);
      if ((index+1) % 2 == 0) 
      {
%>
        <div
          style="font-size: large; color: blue; 
          background-color: aliceblue; font-family: Verdana;">
          The account id
          <%=account.getPid()%>
          is owned by
          <%=account.getpName()%>  <br>
          Balance is:
          <%=account.getBal()%>
        </div>
<%
      } 
      else 
      {
%>
        <div
          style="font-size: large; color: blue; 
          background-color: lemonchiffon; font-family: Verdana;">
          The account id
          <%=account.getPid()%>
          is owned by
          <%=account.getpName()%>  <br>
          Balance is:
          <%=account.getBal()%></div>
<%
      }
    }
%>
</body>
</html>

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

pageEncoding="ISO-8859-1"%>

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

<%@ page import="tube.coding.examples.jpa.entity.SavingsAccount"%>

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

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

<html>

<head>

<title>Insert title here</title>

</head>

<body>

//Sample 6.18: Retrieve the accounts List from request attribute

List<SavingsAccount> accounts = null;

accounts = (List<SavingsAccount>) request.getAttribute("Accounts");

<h1>List of Accounts</h1>

//Sample 6.19: Iterate the List and display result with

//alternate div tag style

int count = accounts.size();

for (int index = 0; index < count; index++)

{

SavingsAccount account = accounts.get(index);

if ((index+1) % 2 == 0)

{

<div

style="font-size: large; color: blue;

background-color: aliceblue; font-family: Verdana;">

The account id

<%=account.getPid()%>

is owned by

<%=account.getpName()%> <br>

Balance is:

<%=account.getBal()%>

</div>

}

else

{

<div

style="font-size: large; color: blue;

background-color: lemonchiffon; font-family: Verdana;">

The account id

<%=account.getPid()%>

is owned by

<%=account.getpName()%> <br>

Balance is:

<%=account.getBal()%></div>

}

</body>

</html>

YouTube

Categories: JavaEE-EJB-Tube

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 :)

Entity JPA Query – Return Multiple Entities

Remote Interface

Bean Implementation [JPAQL – Multiple Result]

Servlet Controller Layer

JSP Presentation Layer

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 :)

Entity JPA Query – Return Multiple Entities

Remote Interface

Bean Implementation [JPAQL – Multiple Result]

Servlet Controller Layer

JSP Presentation Layer

Share this:

Like this:

Related

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