Changeset 34511


Ignore:
Timestamp:
2020-10-23T17:41:18+13:00 (4 years ago)
Author:
davidb
Message:

Evolution of code away from Company model to one that uses RecordHashmap to represent the rows coming out of the JDBC database

Location:
main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/EditableDatabaseTable
Files:
65 added
2 deleted
10 edited

Legend:

Unmodified
Added
Removed

  • main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/EditableDatabaseTable/COMPILE.sh

    r34493 r34511  
    11#!/bin/bash
     2
     3webxml="WebContent/WEB-INF/web.xml"
     4
     5if [ "x$GSDL3HOME" = "x" ] ; then
     6    echo "Error: Environment variable GSDL3HOME not set" >&2
     7    echo "       Unable to generate $webxml from $webxml.in" >&2
     8    exit 1
     9else
     10    echo "****"
     11    echo "* Regenerating $webxml from $webxml.in"
     12    echo "****"
     13    cat $webxml.in \
     14    | sed "s%@GSDL3HOME@%$GSDL3HOME%g" \
     15          > $webxml
     16fi
    217
    318mvn install

  • main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/EditableDatabaseTable/DEPLOY.sh

    r34505 r34511  
    11#!/bin/bash
    22
    3 cp target/EditableDataTable-0.0.1-SNAPSHOT.war  ../../../../../../../packages/tomcat/webapps/EditableDataTable.war
     3webapps_dir=../../../../../../../packages/tomcat/webapps
     4
     5webapp_context=EditableDatabaseTable
     6
     7if [ -d "$webapps_dir/$webapp_context" ] ; then
     8    echo "Removing: $webapps_dir/$webapp_context"
     9    /bin/rm -rf "$webapps_dir/$webapp_context"
     10fi
     11
     12echo "Deploying $webapp_context.war to $webapps_dir"
     13
     14cp target/EditableDatabaseTable-0.0.1-SNAPSHOT.war  "$webapps_dir/$webapp_context".war
     15

  • main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/EditableDatabaseTable/WebContent/WEB-INF/web.xml

    r34493 r34511  
    11<?xml version="1.0" encoding="UTF-8"?>
    2 <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
    3   <display-name>EditableDataTable</display-name>
     2<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     3     xmlns="http://java.sun.com/xml/ns/javaee"
     4     xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
     5     xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
     6     metadata-complete="false"
     7     id="WebApp_ID"
     8     version="3.0">
     9  <display-name>EditableDatabaseTable</display-name>
    410  <welcome-file-list>
    511    <welcome-file>index.html</welcome-file>
    … …  
    1016    <welcome-file>default.jsp</welcome-file>
    1117  </welcome-file-list>
     18
     19  <servlet>
     20    <servlet-name>JDBCAjaxDataSource</servlet-name>
     21    <servlet-class>jquery.datatables.controller.JDBCAjaxDataSourceServlet</servlet-class>
     22    <init-param>
     23      <param-name>gsdl.home</param-name>
     24      <param-value>/home/davidb/research/code-managed/cambridge-museum/greenstone3-svn/web</param-value>
     25    </init-param>
     26    <init-param>
     27      <param-name>jdbc.uri</param-name>
     28      <param-value>jdbc:sqlite:/home/davidb/research/code-managed/cambridge-museum/greenstone3-svn/web/sites/cambridge-museum/collect/waikato-independent/pre-import/MSAccessToCSV/Waikato-Independent.sqlite</param-value>
     29    </init-param>
     30    <init-param>
     31      <param-name>jdbc.db.table</param-name>
     32      <param-value>WAIKATO_INDEPENDENT</param-value>
     33    </init-param>
     34  </servlet>
     35 
     36  <servlet-mapping>
     37    <servlet-name>JDBCAjaxDataSource</servlet-name>
     38    <url-pattern>/JDBCAjaxDataSource</url-pattern>
     39  </servlet-mapping>
     40
     41
     42  <servlet>
     43    <servlet-name>JDBCAjaxDataSource2</servlet-name>
     44    <servlet-class>jquery.datatables.controller.JDBCAjaxDataSourceServlet2</servlet-class>
     45    <init-param>
     46      <param-name>gsdl.home</param-name>
     47      <param-value>/home/davidb/research/code-managed/cambridge-museum/greenstone3-svn/web</param-value>
     48    </init-param>
     49    <init-param>
     50      <param-name>jdbc.uri</param-name>
     51      <param-value>jdbc:sqlite:/home/davidb/research/code-managed/cambridge-museum/greenstone3-svn/web/sites/cambridge-museum/collect/waikato-independent/pre-import/MSAccessToCSV/Waikato-Independent.sqlite</param-value>
     52    </init-param>
     53    <init-param>
     54      <param-name>jdbc.db.table</param-name>
     55      <param-value>WAIKATO_INDEPENDENT</param-value>
     56    </init-param>
     57  </servlet>
     58 
     59  <servlet-mapping>
     60    <servlet-name>JDBCAjaxDataSource2</servlet-name>
     61    <url-pattern>/JDBCAjaxDataSource2</url-pattern>
     62  </servlet-mapping>
     63
     64
     65 
    1266</web-app>

  • main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/EditableDatabaseTable/WebContent/index.html

    r34493 r34511  
    11<!DOCTYPE html>
    22<html>
    3     <head>
    4         <title>Making DataTable fully editable</title>
    5         <link href="media/dataTables/demo_page.css" rel="stylesheet" type="text/css" />
    6         <link href="media/dataTables/demo_table.css" rel="stylesheet" type="text/css" />
    7         <link href="media/dataTables/demo_table_jui.css" rel="stylesheet" type="text/css" />
    8         <link href="media/themes/base/jquery-ui.css" rel="stylesheet" type="text/css" media="all" />
    9         <link href="media/themes/smoothness/jquery-ui-1.7.2.custom.css" rel="stylesheet" type="text/css" media="all" />
    10         <script src="scripts/jquery-1.4.4.min.js" type="text/javascript"></script>
    11         <script src="scripts/jquery.dataTables.min.js" type="text/javascript"></script>
    12         <script src="scripts/jquery.dataTables.editable.js" type="text/javascript"></script>
    13         <script src="scripts/jquery.jeditable.js" type="text/javascript"></script>
    14         <script src="scripts/jquery.validate.js" type="text/javascript"></script>
    15         <script src="scripts/jquery-ui.js" type="text/javascript"></script>
    16         <script type="text/javascript">
    17         $(document).ready(function () {
    18             $("#companies").dataTable({
    19                 "bServerSide": true,
    20                 "sAjaxSource": "/EditableDataTable/CompanyAjaxDataSource",
    21                 "bProcessing": true,
    22                 "sPaginationType": "full_numbers",
    23                 "bJQueryUI": true,
    24                 "aoColumns": [
    25                               {  "sName": "ID",
    26                                  "bSearchable": false,
    27                                  "bSortable": false,
    28                                  "bVisible": false
    29                                      },
    30                       { "sName": "COMPANY_NAME" },
    31                       { "sName": "ADDRESS" },
    32                       { "sName": "TOWN" }
    33                      ]
    34          }).makeEditable({
    35                 "aoColumns": [
    36                               {
    37                                   cssclass: "required"
    38                               },
    39                               null,//null for read-only columns
    40                               {
    41                                   indicator: 'Saving...',
    42                                   tooltip: 'Click to select town',
    43                                   loadtext: 'loading...',
    44                                   type: 'select',
    45                                   onblur: 'submit',
    46                                   data: '{"London":"London","Liverpool":"Liverpool","Portsmouth": "Portsmouth","Edinburgh":"Edinburgh", "Blackburn":"Blackburn","Kent":"Kent","Essex":"Essex","Oxon":"Oxon","Lothian":"Lothian", "West Sussex":"West Sussex","Lanarkshire":"Lanarkshire", "Birmingham":"Birmingham","East Sussex":"East Sussex","Surrey":"Surrey"}'
    47                               }
    48                           ]
    49                           });
    50         });
    51         </script>
    52     </head>
    53     <body id="dt_example">
    54         <div id="container">
    55             <div id="demo_jui">
    56             <button id="btnAddNewRow" value="Ok">Add new company...</button>
    57             <button id="btnDeleteRow" value="cancel">Delete selected company</button>
    58                 <table id="companies" class="display">
    59                     <thead>
    60                         <tr>
    61                             <th>ID</th>
    62                             <th>Company name</th>
    63                             <th>Address</th>
    64                             <th>Town</th>
    65                         </tr>
    66                     </thead>
    67                     <tbody>
    68                  
    69                     </tbody>
    70                 </table>
    71             </div>
    72            
    73            
    74             <form id="formAddNewRow" action="#" title="Add new company">
    75                 <input type="hidden" id="id" name="id" value="-1" rel="0" />
    76                 <label for="name">Name</label><input type="text" name="name" id="name" class="required" rel="1" />
    77                 <br />
    78                 <label for="name">Address</label><input type="text" name="address" id="address" rel="2" />
    79                 <br />
    80                 <label for="name">Postcode</label><input type="text" name="postcode" id="postcode"/>
    81                 <br />
    82                 <label for="name">Town</label><input type="text" name="town" id="town" rel="3"/>
    83                 <br />
    84                 <label for="name">Country</label><select name="country" id="country">
    85                                                     <option value="1">Serbia</option>
    86                                                     <option value="2">France</option>
    87                                                     <option value="3">Italy</option>
    88                                                     </select>   
    89                 <br />         
    90             </form>
    91 
    92         </div>
    93     </body>
    94 </html>
    95 
    96 <!--
    97 
    98   <tr>
    99                     <td>Emkay Entertainments</td>
    100                     <td>Nobel House, Regent Centre</td>
    101                     <td>Lothian</td>
    102                 </tr>
    103                 <tr>
    104                     <td>The Empire</td>
    105                     <td>Milton Keynes Leisure Plaza</td>
    106                     <td>Buckinghamshire</td>
    107                 </tr>
    108                 <tr>
    109                     <td>Asadul Ltd</td>
    110                     <td>Hophouse</td>
    111                     <td>Essex</td>
    112                 </tr>
    113                 <tr>
    114                     <td>Ashley Mark Publishing Company</td>
    115                     <td>1-2 Vance Court</td>
    116                     <td>Tyne &amp; Wear</td>
    117                 </tr>
    118                 <tr>
    119                     <td>MuchMoreMusic Studios</td>
    120                     <td>Unit 29</td>
    121                     <td>London</td>
    122                 </tr>
    123                 <tr>
    124                     <td>Audio Records Studios</td>
    125                     <td>Oxford Street</td>
    126                     <td>London</td>
    127                 </tr>
     3  <head>
     4    <meta http-equiv="Content-type" content="text/html; charset=utf-8" />
     5    <meta name="viewport" content="width=device-width,initial-scale=1,user-scalable=no" />
     6   
     7    <title>Making DataTable fully editable for a JDBC backed database</title>
    1288
    1299
    130  -->
     10    <!-- If running with 'native' DataTable styling -->
     11    <!--
     12    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.22/css/jquery.dataTables.css"/>
     13    -->
     14   
     15    <link rel="stylesheet" type="text/css" href="header-media/jQueryUI-1.12.1/themes/base/jquery-ui.css"/>
     16    <link rel="stylesheet" type="text/css" href="header-media/DataTables-1.10.22/css/dataTables.jqueryui.css"/>
     17 
     18    <script type="text/javascript" src="header-media/jQuery-3.3.1/jquery-3.3.1.js"></script>
     19    <script type="text/javascript" src="header-media/jQueryUI-1.12.1/jquery-ui.js"></script>
     20    <script type="text/javascript" src="header-media/DataTables-1.10.22/js/jquery.dataTables.js"></script>
     21    <script type="text/javascript" src="header-media/DataTables-1.10.22/js/dataTables.jqueryui.js"></script>
     22
     23    <script type="text/javascript" src="scripts/jquery.dataTables.editable-2.3.3.js"></script>
     24    <script type="text/javascript" src="scripts/jquery.jeditable-1.7.1.js"></script>
     25    <script type="text/javascript" src="scripts/jquery.validate-1.7.js"></script>
     26
     27 
     28    <script>
     29      $(document).ready( function () {
     30      $('#database-table-view').dataTable({
     31          //"keys": true,
     32          "pageLength": 25,
     33          "bServerSide": true,
     34          //"serverSide": true,
     35              "sAjaxSource": "/EditableDatabaseTable/JDBCAjaxDataSource",
     36          "sAjaxMethod": "POST",
     37          "bProcessing": true,
     38          "processing": true,
     39              "columns":
     40          [
     41                  {  "name": "ID",
     42                     "searchable": false,
     43                     "sortable": false,
     44                     "visible": false
     45                  },
     46                  { "name": "Year" },
     47                  { "name": "Name" },
     48                  { "name": "Address" },
     49                  { "name": "Field4" },
     50                  { "name": "Field5" }
     51              ]
     52      }).makeEditable({
     53              "aoColumns": [
     54              { type: "text",  event: 'dblclick' },
     55              { type: "text",  event: 'dblclick' },
     56              { type: "text",  event: 'dblclick', placeholder: "Double-click to edit" },
     57              { type: "text",  event: 'dblclick', placeholder: "" },
     58              { type: "text",  event: 'dblclick', placeholder: "" }
     59          ],
     60          "oAddNewRowFormOptions": {
     61          "width": "500px"
     62          }
     63         
     64      });
     65      });
     66    </script>
     67  </head>
     68
     69  <style>
     70    .dataTables_wrapper .dataTables_length select {
     71    background-color: #fff;
     72    }
     73
     74    .dataTables_wrapper .dataTables_filter input {
     75    background-color: #fff;
     76    }
     77
     78    #dt_example {
     79    font: 80%/1.45em "Lucida Grande", Verdana, Arial, Helvetica, sans-serif;
     80    color: #333;
     81    }
     82
     83    table.display tr.even.row_selected td {
     84    background-color: #B0BED9;
     85}
     86
     87    table.display tr.odd.row_selected td {
     88    background-color: #9FAFD1;
     89    }
     90
     91    table.dataTable tbody th, table.dataTable tbody td {
     92    padding: 5px 7px; /* used to be 8px 10px */
     93    }
     94
     95
     96    table.dataTable.display tbody tr.even.row_selected > .sorting_1, table.dataTable.order-column.stripe tbody tr.even.row_selected > .sorting_1 {
     97    background-color:#A0AEC9;
     98    }
     99
     100    table.dataTable.display tbody tr.odd.row_selected > .sorting_1, table.dataTable.order-column.stripe tbody tr.odd.row_selected > .sorting_1 {
     101    background-color: #989FC1;
     102    }
     103
     104    table.display form input {                                                               
     105        margin: -4px -4px -4px -0.55em;                                           
     106        padding: 0px 0px 0px 0px;                                                 
     107        /*border: solid 1px; */                                                 
     108        font: 100% "Lucida Grande", Verdana, Arial, Helvetica, sans-serif;       
     109    }
     110       
     111
     112    .css_right {
     113    float: right;
     114    }
     115   
     116    .css_left {
     117    float: left;
     118    }
     119   
     120    #formAddNewRow label {
     121    /*float: left;*/
     122    display: inline-block;
     123    width: 100px;
     124    font-weight: bold;
     125    }
     126   
     127    #formAddNewRow label.error{
     128    /*float: left; */
     129    /*clear: both; */
     130    display: inline-block;
     131    color: red;
     132    width: 200px;
     133    margin-bottom: 5px;
     134    }
     135   
     136    #formAddNewRow input, #formAddNewRow textarea, #formAddNewRow select {
     137    width: 240px;
     138    }
     139
     140  </style>
     141  <body id="dt_example">
     142   
     143    <div id="container">
     144      <div id="demo_jui" style="width:900px; margin: 10px 40px 10px 40px;">
     145        <button id="btnAddNewRow" value="Ok">Add new row...</button>
     146        <button id="btnDeleteRow" value="cancel">Delete selected row</button>
     147
     148    <table id="database-table-view" class="display" style="width: 100%">
     149      <thead>
     150        <tr>
     151          <th>ID</th>
     152          <th>Year</th>
     153          <th>Name</th>
     154          <th>Address</th>
     155          <th>Field4</th>
     156          <th>Field5</th>
     157        </tr>
     158      </thead>
     159      <tbody>
     160       
     161      </tbody>
     162    </table>
     163      </div>
     164     
     165     
     166      <form id="formAddNewRow" action="#" title="Add new row">
     167    <input type="hidden" id="id" name="id" value="-1" rel="0" />
     168    <div><label for="year">Year</label><input type="text" name="year" id="year" class="required" rel="1" /></div>
     169    <div><label for="name">Name</label><input type="text" name="name" id="name" class="required" rel="2" /></div>
     170    <div><label for="address">Address</label><input type="text" name="address" id="address" rel="3"/></div>
     171    <div><label for="field4">Field4</label><input type="text" name="field4" id="field4" rel="4"/></div>
     172    <div><label for="field5">Field5</label><input type="text" name="field5" id="field5" rel="5"/></div>
     173      </form>
     174     
     175    </div>
     176  </body>
     177 
     178</html>

  • main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/EditableDatabaseTable/pom.xml

    r34493 r34511  
    11<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    22  <modelVersion>4.0.0</modelVersion>
    3   <groupId>EditableDataTable</groupId>
    4   <artifactId>EditableDataTable</artifactId>
     3  <groupId>EditableDatabaseTable</groupId>
     4  <artifactId>EditableDatabaseTable</artifactId>
    55  <version>0.0.1-SNAPSHOT</version>
    66  <packaging>war</packaging>
    7   <name>EditableDataTable</name>
    8   <description>Maven compilable versoin of EditableDataTable Servlet</description>
     7  <name>EditableDatabaseTable</name>
     8  <description>Maven compilable versoin of EditableDatabaseTable Servlet</description>
    99
    1010  <dependencies>
     11    <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
     12    <dependency>
     13      <groupId>javax.servlet</groupId>
     14      <artifactId>javax.servlet-api</artifactId>
     15      <!-- 4.x is bleeding edge -->
     16      <!-- <version>4.0.1</version> -->
     17      <version>3.1.0</version>
     18      <scope>provided</scope>
     19    </dependency>
     20
     21    <!--
    1122    <dependency>
    1223      <groupId>javax.servlet</groupId>
    … …  
    1526      <scope>provided</scope>
    1627    </dependency>
     28-->
     29
     30    <!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
     31    <dependency>
     32      <groupId>org.xerial</groupId>
     33      <artifactId>sqlite-jdbc</artifactId>
     34      <version>3.32.3.2</version>
     35    </dependency>
     36
     37   
    1738  </dependencies>
    1839 
    … …  
    3253        <version>3.3</version>
    3354        <configuration>
    34           <source>1.6</source>
    35           <target>1.6</target>
     55          <source>1.8</source>
     56          <target>1.8</target>
    3657        </configuration>
    3758      </plugin>

  • main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/EditableDatabaseTable/src/jquery/datatables/controller/AddData.java

    r34493 r34511  
    22
    33import java.io.IOException;
     4import java.util.Enumeration;
     5
    46import javax.servlet.ServletException;
    57import javax.servlet.annotation.WebServlet;
    … …  
    810import javax.servlet.http.HttpServletResponse;
    911
    10 import jquery.datatables.model.Company;
     12import jquery.datatables.model.RecordHashmap;
    1113import jquery.datatables.model.DataRepository;
    1214
    … …  
    1517 */
    1618@WebServlet("/AddData")
    17 public class AddData extends HttpServlet {
    18     private static final long serialVersionUID = 1L;
     19public class AddData extends HttpServlet
     20{
     21    private static final long serialVersionUID = 1L;
    1922       
    2023    /**
    2124     * @see HttpServlet#HttpServlet()
    2225     */
    23     public AddData() {
     26    public AddData()
     27    {
     28    // Nothing special to do for AddData servlet at construction time
    2429        super();
    25         // TODO Auto-generated constructor stub
    2630    }
     31   
     32    /**
     33     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     34     */
     35    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
     36    {
     37    RecordHashmap rh = new RecordHashmap();
    2738
    28     /**
    29      * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
    30      */
    31     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    32        
    33         String name = request.getParameter("name");
    34         String address = request.getParameter("address");
    35         String town = request.getParameter("town");
    36         int country = Integer.parseInt(request.getParameter("country"));
    37        
    38         Company c = new Company(name, address, town, country);
    39         DataRepository.GetCompanies().add(c);
    40        
    41         response.getWriter().print(c.getId());
     39    Enumeration<String> parameter_names = request.getParameterNames();
     40
     41        while (parameter_names.hasMoreElements()) {
     42             String param_name = parameter_names.nextElement();
     43
     44         if (!param_name.equals("id")) {
     45         // Want to avoid 'id' param, but every other param
     46         // we want stored as a field in the record
     47         String field_name = param_name;
     48         String field_value = request.getParameter(field_name);
     49         
     50         rh.setFieldValue(field_name,field_value);
     51         }
    4252    }
    43     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    44         doPost(request, response);
    45     }
     53
     54    /*
     55       
     56    String year = request.getParameter("year");
     57    String name = request.getParameter("name");
     58    String address = request.getParameter("address");
     59    //int country = Integer.parseInt(request.getParameter("country"));
     60   
     61    RecordHashmap rh = new RecordHashmap();
     62    rh.setFieldValue("Year",year);
     63    rh.setFieldValue("Name",name);
     64    rh.setFieldValue("Address",address);
     65    */
     66   
     67    DataRepository.GetRecords().add(rh);
     68   
     69    response.getWriter().print(rh.getId());
     70    }
     71   
     72    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
     73    {
     74    doPost(request, response);
     75    }
    4676}

  • main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/EditableDatabaseTable/src/jquery/datatables/controller/DataTableRequestParam.java

    r34493 r34511  
    33public class DataTableRequestParam {
    44
    5     /**
     5    /**
    66     * Request sequence number sent by DataTable, same value must be returned in response
    77     */     

  • main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/EditableDatabaseTable/src/jquery/datatables/controller/DeleteData.java

    r34493 r34511  
    22
    33import java.io.IOException;
     4
    45import javax.servlet.ServletException;
    56import javax.servlet.annotation.WebServlet;
    … …  
    89import javax.servlet.http.HttpServletResponse;
    910
    10 import jquery.datatables.model.Company;
     11import jquery.datatables.model.RecordHashmap;
    1112import jquery.datatables.model.DataRepository;
    1213
    1314/**
    14  * Class that handles the delete company request
     15 * Class that handles the delete record request
    1516 */
    1617@WebServlet("/DeleteData")
    17 public class DeleteData extends HttpServlet {
    18     private static final long serialVersionUID = 1L;
     18public class DeleteData extends HttpServlet
     19{
     20    private static final long serialVersionUID = 1L;
    1921       
    2022    /**
    2123     * @see HttpServlet#HttpServlet()
    2224     */
    23     public DeleteData() {
     25    public DeleteData()
     26    {
     27    // Nothing special to do for DeleteData servlet at construction time
    2428        super();
    25         // TODO Auto-generated constructor stub
    2629    }
    2730
    28     /**
    29     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
    30     */
    31     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    32        
    33         int id = Integer.parseInt(request.getParameter("id"));
    34         for(Company c: DataRepository.GetCompanies())
    35         {
    36             if(c.getId()==id)
    37             {
    38                 DataRepository.GetCompanies().remove(c);
    39                 break;
    40             }
    41         }
    42     }
     31    /**
     32    * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     33    */
     34    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
     35    {
     36    int id = Integer.parseInt(request.getParameter("id"));
     37    for (RecordHashmap rh: DataRepository.GetRecords())
     38        {
     39        if (rh.getId() == id)
     40            {
     41            DataRepository.GetRecords().remove(rh);
     42            break;
     43            }
     44        }
     45    }
    4346
    44     /**
    45      * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
    46      */
    47     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    48         doGet(request, response);
    49     }
     47    /**
     48     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     49     */
     50    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
     51    {
     52    doGet(request, response);
     53    }
    5054
    5155}

  • main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/EditableDatabaseTable/src/jquery/datatables/controller/UpdateData.java

    r34493 r34511  
    88import javax.servlet.http.HttpServletResponse;
    99
    10 import jquery.datatables.model.Company;
     10import jquery.datatables.model.RecordHashmap;
    1111import jquery.datatables.model.DataRepository;
    1212
    … …  
    1515 */
    1616@WebServlet("/UpdateData")
    17 public class UpdateData extends HttpServlet {
    18     private static final long serialVersionUID = 1L;
     17public class UpdateData extends HttpServlet
     18{
     19    private static final long serialVersionUID = 1L;
    1920
    20     /**
    21      * This servlet handles post request from the JEditable and updates company property that is edited
    22      */
    23     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
     21    /**
     22     * @see HttpServlet#HttpServlet()
     23     */
     24    public UpdateData()
     25    {
     26    // Nothing special to do for UpdateData servlet at construction time
     27        super();
     28    }
     29   
     30
     31    /**
     32     * This servlet handles post request from the JEditable and updates the (database) record entry that was edited
     33     */
     34    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
     35    {
     36    int id = Integer.parseInt(request.getParameter("id"));
     37    // //int columnId = Integer.parseInt(request.getParameter("columnId"));
     38    // int columnPosition = Integer.parseInt(request.getParameter("columnPosition"));
     39    String columnName = request.getParameter("columnName");
     40   
     41    //int rowId = Integer.parseInt(request.getParameter("rowId"));
     42    String value = request.getParameter("value");
     43    //String columnName = request.getParameter("columnName");
     44   
     45    for (RecordHashmap rh: DataRepository.GetRecords()) {
     46        if (rh.getId() == id) {
     47
     48        rh.setFieldValue(columnName,value);
    2449       
    25         int id = Integer.parseInt(request.getParameter("id"));
    26         //int columnId = Integer.parseInt(request.getParameter("columnId"));
    27         int columnPosition = Integer.parseInt(request.getParameter("columnPosition"));
    28         //int rowId = Integer.parseInt(request.getParameter("rowId"));
    29         String value = request.getParameter("value");
    30         //String columnName = request.getParameter("columnName");
     50        /*                 
     51        switch (columnPosition) {
     52        case 0:
     53            rh.setFieldValue("Year",value);
     54            break;
     55        case 1:
     56            rh.setFieldValue("Name",value);
     57            break;
     58        case 2:
     59            rh.setFieldValue("Address",value);
     60            break;
     61        default:
     62            break;
     63        }
     64        */
    3165       
    32         for(Company company: DataRepository.GetCompanies())
    33         {
    34             if(company.getId()==id)
    35             {
    36                 switch (columnPosition)
    37                 {
    38                     case 0:
    39                         company.setName(value);
    40                         break;
    41                     case 1:
    42                         company.setAddress(value);
    43                         break;
    44                     case 2:
    45                         company.setTown(value);
    46                         break;
    47                     default:
    48                         break;
    49                 }
    50                 response.getWriter().print(value);
    51                 return;
    52             }
    53         }
    54         response.getWriter().print("Error - company cannot be found");
     66        response.getWriter().print(value);
     67        return;
     68        }
    5569    }
    56 
     70    response.getWriter().print("Error - database record cannot be found");
     71    }
     72   
     73    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
     74    {
     75    doPost(request, response);
     76    }
    5777
    5878}

  • main/trunk/model-sites-dev/cambridge-museum/collect/waikato-independent/pre-import/EditableDatabaseTable/src/jquery/datatables/model/DataRepository.java

    r34493 r34511  
    11package jquery.datatables.model;
    22
     3import java.sql.*;
     4import java.util.ArrayList;
    35import java.util.LinkedList;
    46import java.util.List;
    57
    6 public class DataRepository {
     8public class DataRepository
     9{
     10    private static String JDBC_URI      = null;
     11    private static String JDBC_DB_TABLE = null;
     12   
     13    static {
     14    try {
     15        // Included to trigger maven including the necessary Java class and native files
     16        // in the generated 'with-dependencies.jar' file
     17
     18        // Looks like this step could be avoided if more details are stored in the maven
     19        // pom.xml file:
     20        //    https://stackoverflow.com/questions/16725377/no-suitable-driver-found-sqlite
     21       
     22        Class.forName("org.sqlite.JDBC");
     23    }
     24    catch (Exception e) {
     25        e.printStackTrace();
     26    }
    727   
    8     /// <summary>
     28    }
     29   
     30    /// <summary>
    931    /// Singleton collection of companies
    1032    /// </summary>
    11     private static List<Company> CompanyData = null;
     33    private static List<RecordHashmap> RecordDataList = null;
     34
     35    private static ArrayList<String> _columnLabels = null;
     36   
     37    protected static ArrayList<String> initJDBCColumnLabels(Connection connection, String table_name)
     38    {
     39    // Read in column labels into ArrayList
     40    ArrayList<String> column_labels = null;
     41
     42    try {
     43        Statement st = connection.createStatement();
     44        ResultSet rs = st.executeQuery("SELECT * from " + table_name);
     45       
     46        ResultSetMetaData rs_md = rs.getMetaData();
     47       
     48        int columnCount = rs_md.getColumnCount();                     
     49
     50        column_labels = new ArrayList<String>(columnCount);
     51   
     52        for (int i=1; i<=columnCount; i++) {
     53        String heading = rs_md.getColumnLabel(i);
     54        column_labels.add(heading); // add even if null, so can keep count
     55        if (heading == null) {
     56            System.err.println("*** Encountered null column label at column postion: " + i);
     57        }
     58        }
     59    }
     60    catch (Exception e) {
     61        e.printStackTrace();
     62    }
     63   
     64    return column_labels;
     65    }
     66   
     67    public static ArrayList<String> getColumnHeadings()
     68    {
     69    return _columnLabels;
     70    }
     71   
     72    public static void init(String jdbc_uri, String jdbc_db_table)
     73    {
     74    JDBC_URI = jdbc_uri;
     75    JDBC_DB_TABLE = jdbc_db_table;
     76
     77    System.err.println("Opening: '" + JDBC_URI + "' ...");
     78    try (Connection connection = DriverManager.getConnection(JDBC_URI)) {
     79        System.err.println("... connnected");
     80
     81        _columnLabels =  initJDBCColumnLabels(connection, JDBC_DB_TABLE);
     82
     83        connection.close();
     84    }
     85    catch (SQLException ex) {
     86        ex.printStackTrace();
     87    }
     88    }
     89   
    1290
    1391    /// <summary>
    … …  
    1593    /// </summary>
    1694    /// <returns>List of companies</returns>
    17     public static List<Company> GetCompanies()
     95    public static List<RecordHashmap> GetRecords()
    1896    {
    19         if (CompanyData == null)
    20         {
    21             CompanyData = new LinkedList<Company>();
    22             CompanyData.add(new Company("Emkay Entertainments", "Nobel House, Regent Centre", "Lothian" ));
    23             CompanyData.add(new Company("The Empire", "Milton Keynes Leisure Plaza", "Buckinghamshire" ));
    24             CompanyData.add(new Company("Asadul Ltd", "Hophouse", "Essex" ));
    25             CompanyData.add(new Company("Gargamel ltd", "", "" ));
    26             CompanyData.add(new Company("Ashley Mark Publishing Company", "1-2 Vance Court", "Tyne & Wear" ));
    27             CompanyData.add(new Company("MuchMoreMusic Studios", "Unit 29", "London" ));
    28             CompanyData.add(new Company("Victoria Music Ltd", "Unit 215", "London" ));
    29             CompanyData.add(new Company("Abacus Agent", "Regent Street", "London" ));
    30             CompanyData.add(new Company("Atomic", "133 Longacre", "London" ));
    31             CompanyData.add(new Company("Pyramid Posters", "The Works", "Leicester" ));
    32             CompanyData.add(new Company("Kingston Smith Financial Services Ltd", "105 St Peter's Street", "Herts" ));
    33             CompanyData.add(new Company("Garrett Axford PR", "Harbour House", "West Sussex" ));
    34             CompanyData.add(new Company("Derek Boulton Management", "76 Carlisle Mansions", "London" ));
    35             CompanyData.add(new Company("Total Concept Management (TCM)", "PO Box 128", "West Yorks" ));
    36             CompanyData.add(new Company("Billy Russell Management", "Binny Estate", "Edinburgh" ));
    37             CompanyData.add(new Company("Stage Audio Services", "Unit 2", "Stourbridge" ));
    38             CompanyData.add(new Company("Windsong International", "Heather Court", "Kent" ));
    39             CompanyData.add(new Company("Vivante Music Ltd", "32 The Netherlands", "Surrey" ));
    40             CompanyData.add(new Company("Way to Blue", "First Floor", "London" ));
    41             CompanyData.add(new Company("Glasgow City Halls", "32 Albion Street", "Lanarkshire" ));
    42             CompanyData.add(new Company("The List", "14 High St", "Edinburgh" ));
    43             CompanyData.add(new Company("Wilkinson Turner King", "10A London Road", "Cheshire" ));
    44             CompanyData.add(new Company("GSC Solicitors", "31-32 Ely Place", "London" ));
    45             CompanyData.add(new Company("Vanessa Music Co", "35 Tower Way", "Devon" ));
    46             CompanyData.add(new Company("Regent Records", "PO Box 528", "West Midlands" ));
    47             CompanyData.add(new Company("BBC Radio Lancashire", "20-26 Darwen St", "Blackburn" ));
    48             CompanyData.add(new Company("The Citadel Arts Centre", "Waterloo Street", "Merseyside" ));
    49             CompanyData.add(new Company("Villa Audio Ltd", "Baileys Yard", "Essex" ));
    50             CompanyData.add(new Company("Astra travel", "", "" ));
    51             CompanyData.add(new Company("Idle Eyes Printshop", "81 Sheen Court", "Surrey" ));
    52             CompanyData.add(new Company("Miggins Music (UK)", "33 Mandarin Place", "Oxon" ));
    53             CompanyData.add(new Company("Magic 999", "St Paul's Square", "Lancashire" ));
    54             CompanyData.add(new Company("Delga Group", "Seaplane House, Riverside Est.", "Kent" ));
    55             CompanyData.add(new Company("Zane Music", "162 Castle Hill", "Berkshire" ));
    56             CompanyData.add(new Company("Universal Music Operations", "Chippenham Drive", "Milton Keynes" ));
    57             CompanyData.add(new Company("Gotham Records", "PO Box 6003", "Birmingham" ));
    58             CompanyData.add(new Company("Timbuktu Music Ltd", "99C Talbot Road", "London" ));
    59             CompanyData.add(new Company("Online Music", "Unit 18, Croydon House", "Surrey" ));
    60             CompanyData.add(new Company("Irish Music Magazine", "11 Clare St", "Ireland" ));
    61             CompanyData.add(new Company("Savoy Records", "PO Box 271", "Surrey" ));
    62             CompanyData.add(new Company("Temple Studios", "97A Kenilworth Road", "Middlesex" ));
    63             CompanyData.add(new Company("Gravity Shack Studio", "Unit 3 ", "London" ));
    64             CompanyData.add(new Company("Dovehouse Records", "Crabtree Cottage", "Oxon" ));
    65             CompanyData.add(new Company("Citysounds Ltd", "5 Kirby Street", "London" ));
    66             CompanyData.add(new Company("Revolver Music Publishing", "152 Goldthorn Hill", "West Midlands" ));
    67             CompanyData.add(new Company("Jug Of Ale", "43 Alcester Road", "West Midlands" ));
    68             CompanyData.add(new Company("Isles FM 103", "PO Box 333", "Western Isles" ));
    69             CompanyData.add(new Company("Headscope", "Headrest", "East Sussex" ));
    70             CompanyData.add(new Company("Universal Music Ireland", "9 Whitefriars", "Ireland" ));
    71             CompanyData.add(new Company("Zander Exports", "34 Sapcote Trading Centre", "London" ));
    72             CompanyData.add(new Company("Midem (UK)", "Walmar House", "London" ));
    73             CompanyData.add(new Company("La Rocka Studios", "Post Mark House", "London" ));
    74             CompanyData.add(new Company("Warner Home DVD", "Warner House", "London" ));
    75             CompanyData.add(new Company("Music Room", "The Old Library", "London" ));
    76             CompanyData.add(new Company("Blue Planet", "96 York Street", "London" ));
    77             CompanyData.add(new Company("Dream 107.7FM", "Cater House", "Chelmsford" ));
    78             CompanyData.add(new Company("Moneypenny Agency", "The Stables, Westwood House", "East Yorks" ));
    79             CompanyData.add(new Company("Artsun", "18 Sparkle Street", "Manchester" ));
    80             CompanyData.add(new Company("Clyde 2", "Clydebank Business Park", "Glasgow" ));
    81             CompanyData.add(new Company("9PR", "65-69 White Lion Street", "London" ));
    82             CompanyData.add(new Company("River Studio's", "3 Grange Yard", "London" ));
    83             CompanyData.add(new Company("Start Entertainments Ltd", "3 Warmair House", "Middx" ));
    84             CompanyData.add(new Company("Vinyl Tap Mail Order Music", "1 Minerva Works", "West Yorkshire" ));
    85             CompanyData.add(new Company("Passion Music", "20 Blyth  Rd", "Middlesex" ));
    86             CompanyData.add(new Company("SuperVision Management", "Zeppelin Building", "London" ));
    87             CompanyData.add(new Company("Lite FM", "2nd Floor", "Peterborough" ));
    88             CompanyData.add(new Company("ISIS Duplicating Company", "Sales & Production", "Merseyside" ));
    89             CompanyData.add(new Company("Vanderbeek & Imrie Ltd", "15 Marvig", "Scotland" ));
    90             CompanyData.add(new Company("Glamorgan University", "Student Union", "Mid Glamorgan" ));
    91             CompanyData.add(new Company("Web User", "IPC Media", "London " ));
    92             CompanyData.add(new Company("Farnborough Recreation Centre", "1 Westmead", "Hampshire" ));
    93             CompanyData.add(new Company("Robert Owens/Musical Directions", "352A Kilburn Lane", "London" ));
    94             CompanyData.add(new Company("Magick Eye Records", "PO Box 3037", "Berks" ));
    95             CompanyData.add(new Company("Alexandra Theatre", "Station Street", "West Midlands" ));
    96             CompanyData.add(new Company("Keda Records", "The Sight And Sound Centre", "Middlesex" ));
    97             CompanyData.add(new Company("Independiente Ltd", "The Drill Hall", "London" ));
    98             CompanyData.add(new Company("Shurwood Management", "Tote Hill Cottage", "West Sussex" ));
    99             CompanyData.add(new Company("Fury Records", "PO Box 52", "Kent" ));
    100             CompanyData.add(new Company("Northumbria University", "Union Building", "Newcastle upon Tyne" ));
    101             CompanyData.add(new Company("Pop Muzik", "Haslemere", "W. Sussex" ));
    102             CompanyData.add(new Company("Jonsongs Music", "3 Farrers Place", "Surrey" ));
    103             CompanyData.add(new Company("Hermana PR", "Unit 244, Bon Marche Centre", "London" ));
    104             CompanyData.add(new Company("Sugarcane Music", "32 Blackmore Avenue", "Middlesex" ));
    105             CompanyData.add(new Company("JFM Records", "11 Alexander House", "London" ));
    106             CompanyData.add(new Company("Black Market Records", "25 D'Arblay Street", "London" ));
    107             CompanyData.add(new Company("Float Your Boat Productions", "5 Ralphs Retreat", "Bucks" ));
    108             CompanyData.add(new Company("Creation Management", "2 Berkley Grove", "London" ));
    109             CompanyData.add(new Company("Bryter Music", "Marlinspike Hall", "Suffolk" ));
    110             CompanyData.add(new Company("The Headline Agency", "39 Churchfields", "Ireland" ));
    111             CompanyData.add(new Company("MP Promotions", "13 Greave", "Cheshire" ));
    112             CompanyData.add(new Company("Modo Production Ltd", "Ground Floor", "London" ));
    113             CompanyData.add(new Company("Nomadic Music", "Unit 18", "London" ));
    114             CompanyData.add(new Company("Reverb Records Ltd", "Reverb House", "London" ));
    115             CompanyData.add(new Company("SIBC", "Market Street", "Lerwick" ));
    116             CompanyData.add(new Company("Marken Time Critical Express", "Unit 2", "Isleworth" ));
    117             CompanyData.add(new Company("102.2 Smooth FM", "26-27 Castlereagh Street", "London" ));
    118             CompanyData.add(new Company("Chesterfield Arts Centre", "Chesterfield College", "Derbyshire" ));
    119             CompanyData.add(new Company("The National Indoor Arena", "King Edward's Road", "West Midlands" ));
    120             CompanyData.add(new Company("Salisbury City Hall", "Malthouse Lane", "Wiltshire" ));
    121             CompanyData.add(new Company("Minder Music", "", "" ));
    122         }
    123         return CompanyData;
     97    if (RecordDataList == null) {
     98        System.err.println("Opening: '" + JDBC_URI + "' to query ...");
     99       
     100        try (Connection connection = DriverManager.getConnection(JDBC_URI)) {
     101        System.err.println("... connnected");
     102
     103        if (_columnLabels == null) {
     104            System.err.println("Error: Column Labels should already be initialized, but are null");
     105            return null;
     106        }
     107        else {
     108            int column_count = _columnLabels.size();
     109
     110            if (column_count>=2) {
     111            Statement input_statement = connection.createStatement();
     112            ResultSet input_resultset = input_statement.executeQuery("SELECT * from " + JDBC_DB_TABLE);
     113           
     114            RecordDataList = new LinkedList<RecordHashmap>();
     115           
     116            int line_count = 0;
     117           
     118            while (input_resultset.next()) {
     119               
     120                int id = input_resultset.getInt(1);
     121                RecordHashmap record_hashmap = new RecordHashmap(id, column_count);
     122               
     123                for (int i=2; i<=column_count; i++) {
     124                String val = input_resultset.getString(i);
     125                String key = _columnLabels.get(i-1); // columnLabels arraylist is from 0, not 1 like sql columns
     126               
     127                record_hashmap.setFieldValue(key,val);
     128                }
     129               
     130                RecordDataList.add(record_hashmap);
     131               
     132                line_count++;
     133                if ((line_count % 1000) == 0) {
     134                System.out.println("Processed lines " + (line_count - 999) + "-" + line_count);
     135                }
     136               
     137                if (line_count == 5000) {
     138                // ******
     139                System.err.println("******* During development, artifically cutting off the Database read in at 5000 entries");
     140                break;
     141                }
     142            }
     143            }
     144            else {
     145            System.err.println("Warning: ignoring database table as it only has one column to it");
     146            }
     147           
     148        }
     149       
     150        connection.close();
     151        }
     152        catch (SQLException ex) {
     153            ex.printStackTrace();
     154        }
     155
     156    }
     157   
     158        return RecordDataList;
    124159    }
    125160
Note: See TracChangeset for help on using the changeset viewer.