model
package com.dataSQL.basic;
import javax.faces.bean.ManagedBean;
@ManagedBean
public class Product {
private int id;
private String small_desc;
private String large_desc;
private String sku;
private String category;
private String barcode;
public Product() {
}
public Product(int id, String small_desc, String large_desc, String sku, String category, String barcode) {
this.id = id;
this.small_desc = small_desc;
this.large_desc = large_desc;
this.sku = sku;
this.category = category;
this.barcode = barcode;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getSmall_desc() {
return small_desc;
}
public void setSmall_desc(String small_desc) {
this.small_desc = small_desc;
}
public String getLarge_desc() {
return large_desc;
}
public void setLarge_desc(String large_desc) {
this.large_desc = large_desc;
}
public String getSku() {
return sku;
}
public void setSku(String sku) {
this.sku = sku;
}
public String getCategory() {
return category;
}
public void setCategory(String category) {
this.category = category;
}
public String getBarcode() {
return barcode;
}
public void setBarcode(String barcode) {
this.barcode = barcode;
}
@Override
public String toString() {
return "Product [id=" + id
+ ", small_desc=" + small_desc
+ ", large_desc=" + large_desc
+ ", sku=" + sku
+ ", category=" + category
+ ", barcode=" + barcode + "]";
}
}
Controller
package com.dataSQL.basic;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.ExternalContext;
import javax.faces.context.FacesContext;
@ManagedBean
@SessionScoped
public class ProductController {
private List<Product> products;
private ProductDbUtil productDbUtil;
private Logger logger = Logger.getLogger(getClass().getName());
public ProductController() throws Exception {
products = new ArrayList<>();
productDbUtil = ProductDbUtil.getInstance();
}
public List<Product> getProducts() {
return products;
}
public void loadProducts() {
logger.info("Loading products");
products.clear();
try {
// get all products from database
products = productDbUtil.getProducts();
} catch (Exception exc) {
// send this to server logs
logger.log(Level.SEVERE, "Error loading products", exc);
// add error message for JSF page
addErrorMessage(exc);
}
}
public String addProduct(Product theProduct) {
logger.info("Adding product: " + theProduct);
try {
// add product to the database
productDbUtil.addProduct(theProduct);
} catch (Exception exc) {
// send this to server logs
logger.log(Level.SEVERE, "Error adding products", exc);
// add error message for JSF page
addErrorMessage(exc);
return null;
}
return "list-products?faces-redirect=true";
}
public String loadProduct(int productId) {
logger.info("loading product: " + productId);
try {
// get product from database
Product theProduct = productDbUtil.getProduct(productId);
// put in the request attribute ... so we can use it on the form page
ExternalContext externalContext = FacesContext.getCurrentInstance().getExternalContext();
Map<String, Object> requestMap = externalContext.getRequestMap();
requestMap.put("product", theProduct);
} catch (Exception exc) {
// send this to server logs
logger.log(Level.SEVERE, "Error loading product id:" + productId, exc);
// add error message for JSF page
addErrorMessage(exc);
return null;
}
return "update-product-form.xhtml";
}
public String updateProduct(Product theProduct) {
logger.info("updating product: " + theProduct);
try {
// update product in the database
productDbUtil.updateProduct(theProduct);
} catch (Exception exc) {
// send this to server logs
logger.log(Level.SEVERE, "Error updating product: " + theProduct, exc);
// add error message for JSF page
addErrorMessage(exc);
return null;
}
return "list-products?faces-redirect=true";
}
public String deleteProduct(int productId) {
logger.info("Deleting product id: " + productId);
try {
// delete the product from the database
productDbUtil.deleteProduct(productId);
} catch (Exception exc) {
// send this to server logs
logger.log(Level.SEVERE, "Error deleting product id: " + productId, exc);
// add error message for JSF page
addErrorMessage(exc);
return null;
}
return "list-products";
}
private void addErrorMessage(Exception exc) {
FacesMessage message = new FacesMessage("Error: " + exc.getMessage());
FacesContext.getCurrentInstance().addMessage(null, message);
}
}
[/code]DAO
[code]
package com.dataSQL.basic;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class ProductDbUtil {
private static ProductDbUtil instance;
private DataSource dataSource;
private String jndiName = "java:comp/env/jdbc/webdata";
public static ProductDbUtil getInstance() throws Exception {
if (instance == null) {
instance = new ProductDbUtil();
}
return instance;
}
private ProductDbUtil() throws Exception {
dataSource = getDataSource();
}
private DataSource getDataSource() throws NamingException {
Context context = new InitialContext();
DataSource theDataSource = (DataSource) context.lookup(jndiName);
return theDataSource;
}
public List<Product> getProducts() throws Exception {
List<Product> products = new ArrayList<>();
Connection myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
try {
myConn = getConnection();
String sql = "select * from product order by category";
myStmt = myConn.createStatement();
myRs = myStmt.executeQuery(sql);
// process result set
while (myRs.next()) {
// retrieve data from result set row
int id = myRs.getInt("id");
String small_desc = myRs.getString("small_desc");
String large_desc = myRs.getString("large_desc");
String sku = myRs.getString("sku");
String category = myRs.getString("category");
String barcode = myRs.getString("barcode");
// create new product object
Product tempProduct = new Product(id, small_desc, large_desc, sku, category, barcode);
// add it to the list of products
products.add(tempProduct);
}
return products;
}
finally {
close (myConn, myStmt, myRs);
}
}
public void addProduct(Product theProduct) throws Exception {
Connection myConn = null;
PreparedStatement myStmt = null;
try {
myConn = getConnection();
String sql = "insert into product (small_desc, large_desc, sku, category, barcode) values (?, ?, ?, ?, ?)";
myStmt = myConn.prepareStatement(sql);
// set params
myStmt.setString(1, theProduct.getSmall_desc());
myStmt.setString(2, theProduct.getLarge_desc());
myStmt.setString(3, theProduct.getSku());
myStmt.setString(4, theProduct.getCategory());
myStmt.setString(5, theProduct.getBarcode());
myStmt.execute();
}
finally {
close (myConn, myStmt);
}
}
public Product getProduct(int productId) throws Exception {
Connection myConn = null;
PreparedStatement myStmt = null;
ResultSet myRs = null;
try {
myConn = getConnection();
String sql = "select * from product where id=?";
myStmt = myConn.prepareStatement(sql);
// set params
myStmt.setInt(1, productId);
myRs = myStmt.executeQuery();
Product theProduct = null;
// retrieve data from result set row
if (myRs.next()) {
int id = myRs.getInt("id");
String small_desc = myRs.getString("small_desc");
String large_desc = myRs.getString("large_desc");
String sku = myRs.getString("sku");
String category = myRs.getString("category");
String barcode = myRs.getString("barcode");
theProduct = new Product(id, small_desc, large_desc, sku, category, barcode);
}
else {
throw new Exception("Could not find product id: " + productId);
}
return theProduct;
}
finally {
close (myConn, myStmt, myRs);
}
}
public void updateProduct(Product theProduct) throws Exception {
Connection myConn = null;
PreparedStatement myStmt = null;
try {
myConn = getConnection();
String sql = "update product "
+ " set small_desc=?, large_desc=?, sku=?, category=?, barcode=?"
+ " where id=?";
myStmt = myConn.prepareStatement(sql);
// set params
myStmt.setString(1, theProduct.getSmall_desc());
myStmt.setString(2, theProduct.getLarge_desc());
myStmt.setString(3, theProduct.getSku());
myStmt.setString(4, theProduct.getCategory());
myStmt.setString(5, theProduct.getBarcode());
myStmt.execute();
}
finally {
close (myConn, myStmt);
}
}
public void deleteProduct(int productId) throws Exception {
Connection myConn = null;
PreparedStatement myStmt = null;
try {
myConn = getConnection();
String sql = "delete from product where id=?";
myStmt = myConn.prepareStatement(sql);
// set params
myStmt.setInt(1, productId);
myStmt.execute();
}
finally {
close (myConn, myStmt);
}
}
private Connection getConnection() throws Exception {
Connection theConn = dataSource.getConnection();
return theConn;
}
private void close(Connection theConn, Statement theStmt) {
close(theConn, theStmt, null);
}
private void close(Connection theConn, Statement theStmt, ResultSet theRs) {
try {
if (theRs != null) {
theRs.close();
}
if (theStmt != null) {
theStmt.close();
}
if (theConn != null) {
theConn.close();
}
} catch (Exception exc) {
exc.printStackTrace();
}
}
}
[/code]
Vaadin 8 Archetype MainUI.java[code]
package com.dataSQL.basic;
import java.util.List;
import javax.servlet.annotation.WebServlet;
import com.vaadin.annotations.Theme;
import com.vaadin.annotations.VaadinServletConfiguration;
import com.vaadin.server.VaadinRequest;
import com.vaadin.server.VaadinServlet;
import com.vaadin.ui.Grid;
import com.vaadin.ui.UI;
@Theme("maintheme")
public class MainUI extends UI {
@Override
protected void init(VaadinRequest vaadinRequest) {
List<Product> products = ProductDbUtil.getProducts();
Grid<Product> grid = new Grid<>(Product.class);
grid.setItems(products);
}
@WebServlet(urlPatterns = "/*", name = "MainUIServlet", asyncSupported = true)
@VaadinServletConfiguration(ui = MainUI.class, productionMode = false)
public static class MainUIServlet extends VaadinServlet {
}
}