Skip to main content

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

Posted by arungupta on November 4, 2007 at 3:43 PM PST

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

Related Topics >>