JSF + Primefaces + CRUD: Running a custom query

JSF + Primefaces + CRUD: Running a custom query

OOTB I've got two database tables. They have several columns mostly of
varchar types, each have an int id column as the primary key. I'm using
Netbeans and JPA and have a pretty cool PrimeFaces generic app that I've
altered to use the auto generated Entity.java, Controller.java and
Facade.java pages. However, I'm PrimeFaces and JPA... I would like to
write a "new" query and run it. I've spent hours googling and testing
different ways with zero success. I simply want to know the easiest way to
either add a new query to the Entity's NamedQueries list for example:
Here's the first table called t_units as it's formal name in mysql.
@Named("tUnits")
@Entity
@Table(name = "t_units")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "TUnits.findAll", query = "SELECT t FROM TUnits t"),
@NamedQuery(name = "TUnits.findById", query = "SELECT t FROM TUnits t
WHERE t.id = :id"),
@NamedQuery(name = "TUnits.findByMake", query = "SELECT t FROM TUnits
t WHERE t.make = :make"),
@NamedQuery(name = "TUnits.findByModel", query = "SELECT t FROM TUnits
t WHERE t.model = :model"),
@NamedQuery(name = "TUnits.findByYear", query = "SELECT t FROM TUnits
t WHERE t.year = :year"),
@NamedQuery(name = "TUnits.findByFuelType", query = "SELECT t FROM
TUnits t WHERE t.fuelType = :fuelType"),
@NamedQuery(name = "TUnits.findByOrigPurchaseDate", query = "SELECT t
FROM TUnits t WHERE t.origPurchaseDate = :origPurchaseDate"),
@NamedQuery(name = "TUnits.findByOwner", query = "SELECT t FROM TUnits
t WHERE t.owner = :owner"),
@NamedQuery(name = "TUnits.findByOperator", query = "SELECT t FROM
TUnits t WHERE t.operator = :operator"),
@NamedQuery(name = "TUnits.findByDecommFlag", query = "SELECT t FROM
TUnits t WHERE t.decommFlag = :decommFlag"),
@NamedQuery(name = "TUnits.findByPoNum", query = "SELECT t FROM TUnits
t WHERE t.poNum = :poNum"),
@NamedQuery(name = "TUnits.findByServiceNum", query = "SELECT t FROM
TUnits t WHERE t.serviceNum = :serviceNum"),
@NamedQuery(name = "TUnits.findByOrigPurchasePrice", query = "SELECT t
FROM TUnits t WHERE t.origPurchasePrice = :origPurchasePrice"),
@NamedQuery(name = "TUnits.myNewQuery", query = "select t from TUnits
t where t.decommFlag = 'false'")})
public class TUnits implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Integer id;
@Size(max = 255)
@Column(name = "make")
private String make;
@Size(max = 255)
@Column(name = "model")
private String model;
@Size(max = 255)
@Column(name = "year")
private String year;
@Size(max = 255)
@Column(name = "fuel_type")
private String fuelType;
@Basic(optional = false)
@NotNull
@Column(name = "orig_purchase_date")
@Temporal(TemporalType.DATE)
private Date origPurchaseDate;
@Size(max = 255)
@Column(name = "owner")
private String owner;
@Size(max = 255)
@Column(name = "operator")
private String operator;
@Basic(optional = false)
@NotNull
@Column(name = "decomm_flag")
private boolean decommFlag;
@Size(max = 255)
@Column(name = "po_num")
private String poNum;
@Size(max = 255)
@Column(name = "service_num")
private String serviceNum;
// @Max(value=?) @Min(value=?)//if you know range of your decimal
fields consider using these annotations to enforce field validation
@Basic(optional = false)
@NotNull
@Column(name = "orig_purchase_price")
private BigDecimal origPurchasePrice;
public TUnits() {
}
public TUnits(Integer id) {
this.id = id;
}
public TUnits(Integer id, Date origPurchaseDate, boolean decommFlag,
BigDecimal origPurchasePrice) {
this.id = id;
this.origPurchaseDate = origPurchaseDate;
this.decommFlag = decommFlag;
this.origPurchasePrice = origPurchasePrice;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getMake() {
return make;
}
public void setMake(String make) {
this.make = make;
}
public String getModel() {
return model;
}
public void setModel(String model) {
this.model = model;
}
public String getYear() {
return year;
}
public void setYear(String year) {
this.year = year;
}
public String getFuelType() {
return fuelType;
}
public void setFuelType(String fuelType) {
this.fuelType = fuelType;
}
public Date getOrigPurchaseDate() {
return origPurchaseDate;
}
public void setOrigPurchaseDate(Date origPurchaseDate) {
this.origPurchaseDate = origPurchaseDate;
}
public String getOwner() {
return owner;
}
public void setOwner(String owner) {
this.owner = owner;
}
public String getOperator() {
return operator;
}
public void setOperator(String operator) {
this.operator = operator;
}
public boolean getDecommFlag() {
return decommFlag;
}
public void setDecommFlag(boolean decommFlag) {
this.decommFlag = decommFlag;
}
public String getPoNum() {
return poNum;
}
public void setPoNum(String poNum) {
this.poNum = poNum;
}
public String getServiceNum() {
return serviceNum;
}
public void setServiceNum(String serviceNum) {
this.serviceNum = serviceNum;
}
public BigDecimal getOrigPurchasePrice() {
return origPurchasePrice;
}
public void setOrigPurchasePrice(BigDecimal origPurchasePrice) {
this.origPurchasePrice = origPurchasePrice;
}
@Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id
fields are not set
if (!(object instanceof TUnits)) {
return false;
}
TUnits other = (TUnits) object;
if ((this.id == null && other.id != null) || (this.id != null &&
!this.id.equals(other.id))) {
return false;
}
return true;
}
@Override
public String toString() {
return "entities.TUnits[ id=" + id + " ]";
}
}
The last line "TUnits.myNewQuery" is the query I added that I'd like to
run and display in a new PrimeFaces Datatable. Im not 100% sure it will
even run.
Here's the TUnitsController.java Class. AGAIN, these were autogen classes
by NetBeans. I have made a few basic changes to get the current tables
populating just like the auto gen List pages do...
@Named("tUnitsController")
@RequestScoped
public class TUnitsController implements Serializable {
EntityManager em;
private TUnits current;
private DataModel items = null;
@EJB
private controllers.TUnitsFacade ejbFacade;
private PaginationHelper pagination;
private int selectedItemIndex;
public TUnitsController() {
}
public TUnits getSelected() {
if (current == null) {
current = new TUnits();
selectedItemIndex = -1;
}
return current;
}
private TUnitsFacade getFacade() {
return ejbFacade;
}
public PaginationHelper getPagination() {
if (pagination == null) {
pagination = new PaginationHelper(10) {
@Override
public int getItemsCount() {
return getFacade().count();
}
@Override
public DataModel createPageDataModel() {
return new ListDataModel(getFacade().findRange(new
int[]{getPageFirstItem(), getPageFirstItem() +
getPageSize()}));
}
};
}
return pagination;
}
public String prepareList() {
recreateModel();
return "List";
}
public String prepareView() {
current = (TUnits) getItems().getRowData();
selectedItemIndex = pagination.getPageFirstItem() +
getItems().getRowIndex();
return "View";
}
public String prepareCreate() {
current = new TUnits();
selectedItemIndex = -1;
return "Create";
}
public String create() {
try {
getFacade().create(current);
JsfUtil.addSuccessMessage(ResourceBundle.getBundle("/Bundle").getString("TUnitsCreated"));
return prepareCreate();
} catch (Exception e) {
JsfUtil.addErrorMessage(e,
ResourceBundle.getBundle("/Bundle").getString("PersistenceErrorOccured"));
return null;
}
}
public String prepareEdit() {
current = (TUnits) getItems().getRowData();
selectedItemIndex = pagination.getPageFirstItem() +
getItems().getRowIndex();
return "Edit";
}
public String prepareEditt() {
current = (TUnits) getItems().getRowData();
System.out.println("Current: " + current);
updatee();
//selectedItemIndex = pagination.getPageFirstItem() +
getItems().getRowIndex();
return "viewUnitDetails";
}
public String updatee() {
try {
System.out.println("updatee");
getFacade().edit(current);
JsfUtil.addSuccessMessage(ResourceBundle.getBundle("/Bundle").getString("TUnitsUpdated"));
//FacesMessage msg = new FacesMessage("Unit Edited");
//FacesContext.getCurrentInstance().addMessage(null, msg);
return "viewUnitDetails";
} catch (Exception e) {
JsfUtil.addErrorMessage(e,
ResourceBundle.getBundle("/Bundle").getString("PersistenceErrorOccured"));
return null;
}
}
public void onCancel(RowEditEvent event) {
FacesContext context = FacesContext.getCurrentInstance();
context.addMessage(null, new FacesMessage("Cancelled",""));
}
public String update() {
try {
getFacade().edit(current);
JsfUtil.addSuccessMessage(ResourceBundle.getBundle("/Bundle").getString("TUnitsUpdated"));
return "View";
} catch (Exception e) {
JsfUtil.addErrorMessage(e,
ResourceBundle.getBundle("/Bundle").getString("PersistenceErrorOccured"));
return null;
}
}
public String destroy() {
current = (TUnits) getItems().getRowData();
selectedItemIndex = pagination.getPageFirstItem() +
getItems().getRowIndex();
performDestroy();
recreatePagination();
recreateModel();
return "List";
}
public String destroyAndView() {
performDestroy();
recreateModel();
updateCurrentItem();
if (selectedItemIndex >= 0) {
return "View";
} else {
// all items were removed - go back to list
recreateModel();
return "List";
}
}
private void performDestroy() {
try {
getFacade().remove(current);
JsfUtil.addSuccessMessage(ResourceBundle.getBundle("/Bundle").getString("TUnitsDeleted"));
} catch (Exception e) {
JsfUtil.addErrorMessage(e,
ResourceBundle.getBundle("/Bundle").getString("PersistenceErrorOccured"));
}
}
private void updateCurrentItem() {
int count = getFacade().count();
if (selectedItemIndex >= count) {
// selected index cannot be bigger than number of items:
selectedItemIndex = count - 1;
// go to previous page if last page disappeared:
if (pagination.getPageFirstItem() >= count) {
pagination.previousPage();
}
}
if (selectedItemIndex >= 0) {
current = getFacade().findRange(new int[]{selectedItemIndex,
selectedItemIndex + 1}).get(0);
}
}
public DataModel getItems() {
if (items == null) {
items = getPagination().createPageDataModel();
}
return items;
}
private void recreateModel() {
items = null;
}
private void recreatePagination() {
pagination = null;
}
public String next() {
getPagination().nextPage();
recreateModel();
return "List";
}
public String previous() {
getPagination().previousPage();
recreateModel();
return "List";
}
Here is my xhtml page viewTireInventory.xhtml. This web page simply uses a
template and displays one primefaces datatable.
<?xml version='1.0' encoding='UTF-8' ?>
<!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"
xmlns:ui="http://xmlns.jcp.org/jsf/facelets"
xmlns:h="http://xmlns.jcp.org/jsf/html"
xmlns:f="http://xmlns.jcp.org/jsf/core"
xmlns:p="http://primefaces.org/ui">
<h:head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link href="./resources/css/default.css" rel="stylesheet"
type="text/css" />
<link href="./resources/css/cssLayout.css" rel="stylesheet"
type="text/css" />
</h:head>
<body>
<ui:composition template="./WEB-INF/templates/template.xhtml">
<ui:define name="content">
<!-- Datatable goes here -->
<h:form id="form">
<p:growl id="messages" showDetail="true" life="1100"/>
<h:panelGroup
rendered="#{tUnitsController.items.rowCount > 0}">
<h:outputText
value="#{tUnitsController.pagination.pageFirstItem
+ 1}..#{tUnitsController.pagination.pageLastItem +
1}/#{tUnitsController.pagination.itemsCount}"/>&nbsp;
<h:commandLink
action="#{tUnitsController.previous}"
value="#{bundle.Previous}
#{tUnitsController.pagination.pageSize}"
rendered="#{tUnitsController.pagination.hasPreviousPage}"/>&nbsp;
<h:commandLink action="#{tUnitsController.next}"
value="#{bundle.Next}
#{tUnitsController.pagination.pageSize}"
rendered="#{tUnitsController.pagination.hasNextPage}"/>&nbsp;
<p:dataTable var="item"
value="#{tUnitsController.items}" id="items"
editable="true" resizableColumns="true"
paginator="false" rows="20" sortMode="multiple">
<f:facet name="header">
View All Units
</f:facet>
<p:ajax event="rowEdit"
listener="#{tUnitsController.prepareEditt}"
update=":form:messages" />
<p:ajax event="rowEditCancel"
listener="#{tUnitsController.onCancel}"
update=":form:messages" />
<p:column headerText="Unit ID"
sortBy="#{item.id}" style="font-size: 12px">
<p:cellEditor>
<f:facet name="output">
<h:outputText value="#{item.id}" />
</f:facet>
<f:facet name="input">
<p:inputText value="#{item.id}" />
</f:facet>
</p:cellEditor>
</p:column>
<p:column headerText="Make"
sortBy="#{item.make}" style="font-size: 12px">
<p:cellEditor>
<f:facet name="output">
<h:outputText value="#{item.make}" />
</f:facet>
<f:facet name="input">
<p:inputText value="#{item.make}" />
</f:facet>
</p:cellEditor>
</p:column>
<p:column headerText="Model"
sortBy="#{item.model}" style="font-size:
12px">
<p:cellEditor>
<f:facet name="output">
<h:outputText
value="#{item.model}" />
</f:facet>
<f:facet name="input">
<p:inputText value="#{item.model}"
label="Model"/>
</f:facet>
</p:cellEditor>
</p:column>
<p:column headerText="Year"
sortBy="#{item.year}" style="font-size: 12px">
<p:cellEditor>
<f:facet name="output">
<h:outputText value="#{item.year}" />
</f:facet>
<f:facet name="input">
<p:inputText value="#{item.year}"
label="Year"/>
</f:facet>
</p:cellEditor>
</p:column>
<p:column headerText="Purchase Price"
style="font-size: 12px">
<p:cellEditor>
<f:facet name="output">
<h:outputText
value="#{item.origPurchasePrice}"
/>
</f:facet>
<f:facet name="input">
<p:inputText
value="#{item.origPurchasePrice}"
label="Purchase Price"/>
</f:facet>
</p:cellEditor>
</p:column>
<p:column headerText="Fuel Type"
style="font-size: 12px">
<p:cellEditor>
<f:facet name="output">
<h:outputText
value="#{item.fuelType}" />
</f:facet>
<f:facet name="input">
<p:inputText
value="#{item.fuelType}"
label="Fuel Type"/>
</f:facet>
</p:cellEditor>
</p:column>
<p:column headerText="Owner" style="font-size:
12px">
<p:cellEditor>
<f:facet name="output">
<h:outputText
value="#{item.owner}" />
</f:facet>
<f:facet name="input">
<p:inputText value="#{item.owner}"
label="Owner"/>
</f:facet>
</p:cellEditor>
</p:column>
<p:column headerText="Operator"
style="font-size: 12px">
<p:cellEditor>
<f:facet name="output">
<h:outputText
value="#{item.operator}" />
</f:facet>
<f:facet name="input">
<p:inputText
value="#{item.operator}"
label="Operator"/>
</f:facet>
</p:cellEditor>
</p:column>
<p:column headerText="Out of Stock"
style="font-size: 12px">
<p:cellEditor>
<f:facet name="output">
<h:outputText
value="#{item.decommFlag}" />
</f:facet>
<f:facet name="input">
<p:inputText
value="#{item.decommFlag}"
label="Remove From Stock"/>
</f:facet>
</p:cellEditor>
</p:column>
<p:column headerText="PO #" style="font-size:
12px">
<p:cellEditor>
<f:facet name="output">
<h:outputText
value="#{item.poNum}" />
</f:facet>
<f:facet name="input">
<p:inputText value="#{item.poNum}"
label="PO #"/>
</f:facet>
</p:cellEditor>
</p:column>
<p:column headerText="Service #"
style="font-size: 12px">
<p:cellEditor>
<f:facet name="output">
<h:outputText
value="#{item.serviceNum}" />
</f:facet>
<f:facet name="input">
<p:inputText
value="#{item.serviceNum}"
label="Service #"/>
</f:facet>
</p:cellEditor>
</p:column>
<p:column headerText="Edit &amp; Tires"
style="width: 6%; font-size: 12px">
<p:rowEditor/>
<p:commandLink id="viewInv" value="Tires"/>
</p:column>
</p:dataTable>
</h:panelGroup>
</h:form>
<!-- End of Datatable -->
</ui:define>
</ui:composition>
</body>
</html>
So how do I get my custom query to populate this data table? Currently,
using the default "items" from TUnitsController WILL populate this table,
but I have found it EXTREMELY frustrating to try and customize anything
with JPA or Jquery. The CRUD auto generated pages are really complicated
and restricting.