Arun Gupta, Miles to go ...

TOTD #15: Delete/Update Row from Database using jMaki Data Table

A Previous Entry explained how a Data Table widget can be populated from a database using Java Persistence API (JPA). This TOTD extends that entry and explains how a selected row from the Data Table can be deleted from the database. This entry is created based upon a requirement from Dave Briccetti at Silicon Valley Code Camp 2007 last weekend.

The first part of the entry is also a re-write of using NetBeans 6 and the latest jMaki NetBeans plugin.

1. Create the Web application project
   1. In NetBeans 6 IDE, create a new 'Web Application' project and name it as 'jmaki-database'.
   2. Choose GlassFish V2 as the server as shown below:
      
      
   3. Click on 'Next' button, add 'jMaki Ajax Framework' and choose 'Standard' layout as shown below:
      
      
      
      and click on 'Finish' button.
2. Configure the Database
   1. In NetBeans IDE, 'Runtime' tab, expand Databases, connect to the default database (with the URL 'jdbc:derby://localhost:1527/sample [app on APP]'). Specify the username 'app' and password 'app'.
   2. Right-click again on the URL and select 'Execute Command...' and issue the command:
      
      create table BOOKS (title varchar(255),
                    author varchar(255),
                    isbn varchar(255),
                    description varchar(255),
                    PRIMARY KEY (isbn))
      
      This will create the database table.
   3. Add data to the newly created table using the following command:
      
      INSERT INTO BOOKS VALUES('Galloway Book of Running', 'Jeff Galloway', 'ABC001', 'The best book on running');
INSERT INTO BOOKS VALUES('The Complete Book of Running', 'James Fixx', 'ABC002', 'Oldest book of running');
INSERT INTO BOOKS VALUES('The Runners Handbook', 'Bob Glover', 'ABC003', 'Bestselling Guide for Beginning and Intermediate Runners');
INSERT INTO BOOKS VALUES('Daniel Running Formula', 'Jack Tupper Daniels', 'ABC004', 'Proven programs 800m to Marathon');
INSERT INTO BOOKS VALUES('Chi Running', 'Danny Drever', 'ABC005', 'Revolutionary approach to effortless, injury-free running');
INSERT INTO BOOKS VALUES('Running for Mortals', 'John Bingham', 'ABC006', 'A common sense plan for changing your life through running');
INSERT INTO BOOKS VALUES('Marathoning for Mortals', 'John Bingham', 'ABC007', 'Regular person guide to marathon');
INSERT INTO BOOKS VALUES('Marathon', 'Hal Higdon', 'ABC008', 'The Ultimate Training Guide');
      
      This will add 8 rows to the table. You can enter additional rows if you like.
3. Create the JPA Entity class that maps to the database
   1. In the projects window, select the project 'jmaki-database', right-click and select 'New' and choose 'Entity Classes From Database...'.
   2. Select 'jdbc/sample' as 'Data Source'.
   3. Select 'BOOKS' in 'Available Tables' and click on 'Add' and enter the values as shown below:
      
      
      
      and click on 'Next'.
   4. Specify the package name as 'server' as shown below:
      
      
   5. Click on 'Create Persistence Unit...' to create the persistence unit and enter the values as shown below:
      
      
      and click on 'Create'.

   and click on 'Finish'.

4. Add the following named query to the generated JPA class:
   
   @NamedQuery(name = "Books.findAll", query = "SELECT b FROM Books b")
5. Configure Persistence Unit
   1. In your project, expand 'Configuration Files' and open 'persistence.xml'.
   2. Click on 'Add Class' button and  choose 'server.Books' class and click 'OK'. This will ensure that the generated entity class is explicitly recognized by the EntityManagerFactory.
6. In your project, right-click on 'Web Pages', select 'New' and then 'JSP...'. Give the name as 'data' as shown:
   
   
   
   and then click on 'Finish'.
7. Replace the entire content of template 'data.jsp' with the following:
   
   <%@ page import="java.util.*" %>
<%@ page import="server.Books" %>
<%@ page import="javax.persistence.*" %>

<%
  EntityManagerFactory emf = Persistence.createEntityManagerFactory("jmaki-databasePU");
  EntityManager em = emf.createEntityManager();

  List<Books> list = em.createNamedQuery("Books.findAll").getResultList();

  out.println("{columns : [" +
    "{ label : 'Title', id : 'title'}," +
    "{ label :'Author', id : 'author'}," +
    "{ label :'ISBN', id : 'isbn'}," +
    "{ label :'Description', id : 'description'}" +
    "],");

    out.println("rows: [");
    for (int i=0; i<list.size(); i++) {
      Books b = list.get(i);
      out.print("{ id: '" + b.getIsbn() + "', " +
        "title: '" + b.getTitle() + "'," +
        "author: '" + b.getAuthor() + "'," +
        "isbn: '" + b.getIsbn() + "'," +
        "description: '" + b.getDescription() + "'}");
      if (i < list.size()-1)
        out.println(",");
      else
        out.println();
    }
    out.println("] }");
  %>
8. Add and Configure a jMaki Data Table widget
   1. In the generated 'index.jsp', drag-and-drop a 'Yahoo Data Table' widget from the jMaki Palette in the 'Main Content Area'.
   2. Change the generated code fragment from:
      
      <a:widget name="yahoo.dataTable"
    value="{columns :
           [
               { label : 'Title', id : 'title'},
               { label :'Author', id : 'author'},
               { label : 'ISBN', id : 'isbn'},
               { label : 'Description', id : 'description'}
           ],
            rows :
           [
               { title : 'Book Title 1', author : 'Author 1', isbn: '4412', description : 'A Some long description'},
               { id : 'bar', title : 'Book Title 2', author : 'Author 2', isbn : '4412', description : 'A Some long description'}
           ]
           }" />
      
      to
      
<a:widget name="yahoo.dataTable" service="data.jsp" />

The 'service' attribute tells jMaki runtime to retrieve the data for DataTable widget from 'data.jsp' instead of the using static data.
   3. Click on the Green button in NetBeans IDE to run the project or default keyboard shortcut (F6). And your browser shows the application deployed as:
      
      
      
      This jMaki-wrapped Yahoo Table Table widget is pulling data from JavaDB.
9. Now update the project to enable deletion of rows from database based upon row selection. Expand 'Source Packages', 'server', edit 'Books.java' and add the following NamedQuery:
   
   @NamedQuery(name = "Books.deleteByIsbn", query = "DELETE FROM Books b WHERE b.isbn = :isbn")
10. In your project, right-click on 'Web Pages', select 'New' and then 'JSP...'. Give the name as shown:
    
    
    
    and then click on 'Finish'.
11. Replace the entire content of template 'delete.jsp' with the following:
    
    <%@ page import="javax.persistence.*" %>

<%
  String isbn = request.getParameter("isbn");
  EntityManagerFactory emf = Persistence.createEntityManagerFactory("jmaki-databasePU");
  EntityManager em = emf.createEntityManager();

  em.getTransaction().begin();
  em.createNamedQuery("Books.deleteByIsbn").
    setParameter("isbn", isbn).
    executeUpdate();
  em.getTransaction().commit();
%>
12. Expand 'Web pages' and edit 'glue.js' to add the following fragment in '*onSelect' subscribe method:
    
    jmaki.doAjax({method: "POST",
  url: "delete.jsp?isbn=" + encodeURIComponent(args.value.isbn),
  callback: function(_req) {
    jmaki.publish('/jmaki/table/removeRow', { targetId: args.value.isbn });
  }
});
13. Change the generated code fragment in 'index.jsp' as:
    
    <a:widget name="yahoo.dataTable" service="data.jsp" subscribe="/jmaki/table"/>

That's it! Now clicking on any row of the table will delete that particular row from the database and also from the table. If jMaki Debugger Console is enabled, then the messages are shown as below:

Using the similar steps described in bullet #9-13, a row can be updated in the database.

Please leave suggestions on other TOTD that you'd like to see. A complete archive is available here.

Technorati: totd jmaki glassfish netbeans jpa database

Posted by Arun Gupta in web2.0  |  Comments[44]