Entity JPA Query - Where Condition - Programming Examples

In this EJB JPA Tutorial, we will search SavingsAc by its balance amount. This completes our sub-series example.

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();  
  
  //Sample 6.20: Declare method to query by balance
  public List<SavingsAccount> queryByBal(int balance, int mode);  
}

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();

//Sample 6.20: Declare method to query by balance

public List<SavingsAccount> queryByBal(int balance, int mode);

}

Bean Implementation

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;
  }  
  
  //Sample 6.21: Use JPA QL to fetch by balance
  public List<SavingsAccount> queryByBal(int balance, int mode)
  {
    //Balance equal
    String JpaSql = "Select sac from SavingsAccount sac " +
        "where sac.bal = :Balance";
    //Balance less than
    if (mode == -1)
      JpaSql = JpaSql.replace('=', '<');
    
    //Balance greater than
    if (mode == 1)
      JpaSql = JpaSql.replace('=', '>');

    //All set! Get result
    Query query = em.createQuery(JpaSql);
    query.setParameter("Balance", balance);  
    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

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

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;

}

//Sample 6.21: Use JPA QL to fetch by balance

public List<SavingsAccount> queryByBal(int balance, int mode)

{

//Balance equal

String JpaSql = "Select sac from SavingsAccount sac " +

"where sac.bal = :Balance";

//Balance less than

if (mode == -1)

JpaSql = JpaSql.replace('=', '<');

//Balance greater than

if (mode == 1)

JpaSql = JpaSql.replace('=', '>');

//All set! Get result

Query query = em.createQuery(JpaSql);

query.setParameter("Balance", balance);

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

return accounts;

}

Servlet 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);        
    }
    
    //Sample 6.22: Search By Balance
    if ( (balance != null && balance.trim().length() > 0) &&
        (submit3 != null && submit3.trim().length() > 0) &&
        (submit3.equalsIgnoreCase("Search") == true) )
    {
      int bal = new Integer(balance).intValue();
      int mode = new Integer(BalSearchOption).intValue();
      List<SavingsAccount> accounts = SACBean.queryByBal(bal, mode);
      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);

}

//Sample 6.22: Search By Balance

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

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

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

{

int bal = new Integer(balance).intValue();

int mode = new Integer(BalSearchOption).intValue();

List<SavingsAccount> accounts = SACBean.queryByBal(bal, mode);

request.setAttribute("Accounts", accounts);

RequestDispatcher respJSP =

request.getRequestDispatcher("QueryResultMultiple.jsp");

respJSP.forward(request, response);

}

YouTube

Categories: JavaEE-EJB

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 – Where Condition

Remote Interface

Bean Implementation

Servlet 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 – Where Condition

Remote Interface

Bean Implementation

Servlet Layer

Share this:

Like this:

Related

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