### jQuery UI DataTable

This example shows how to display records from a SQLite database using DataTables.
Information about jQuery DataTables:
http://datatables.net or
http://legacy.datatables.net/usage/features
How it works?
After the app is started, the SQLite database is initialized (see dbhelper.bas), followed by the server.
The TableHelper handler (see TableHelper.bas) is added to the server.
The server is started and can be accessed using port 51042, like http://localhost:51042 or use an IP address instead of localhost
When connected the file index.html stored in the objects/www folder is shown. Index.html redirects to objects/www/tablehelper/index.html
Finally the data is selected from the database contacts, table contacts and shown in the table on the browser.
Database: contacts
Table: CREATE TABLE [contacts] ([ID] INTEGER PRIMARY KEY, [Name] TEXT, [Address] TEXT, [Phone] TEXT, [EMail] TEXT, [URL] TEXT, [Notes] TEXT);
Libraries used: sqlite-jdbc-3.7.2
HTML CDN reference is used:
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.5/css/jquery.dataTables.css">
<script type="text/javascript" language="javascript" src="//code.jquery.com/jquery-1.11.1.min.js"></script>
<script type="text/javascript" language="javascript" charset="utf8" src="//cdn.datatables.net/1.10.5/js/jquery.dataTables.min.js"></script>
Folder Structure
webapptableex1.b4j = The main app
DBHelper.bas = Class Helper for the SQLite DB
DBUtils.bas = Class DB Helper
TableHelper.bas = Class Helper to select records from the database table and respond records in json format to the server
webapptableex1.b4j.meta = The app meta file
WebUtils.bas = Not used
Objects\contacts.db = The database with one table contacts
Objects\webapptableex1.jar = The app
Objects\www\index.html = This html file is loaded when the calling the server, like http://localhost:51042
Objects\www\tablehelper\index.html = This html file contains the datatable which is populated by TableHelper.bas
B4J Main App
#Region Project Attributes
#CommandLineArgs:
#MergeLibraries: True
#AdditionalJar: sqlite-jdbc-3.7.2
#End Region
`Sub Process_Globals
Public srvr As Server 'The webserver
Public DB As DBHelper 'Helper for the database operations
End Sub`
'Access this app by http://localhost:51042 or use an IP address instead of localhost
`Sub AppStart (Args() As String)
'Init the sqlite database - see dbhelper.bas
DB.Initialize
srvr.Initialize("srvr")
'Add the table handler - see tablehelper.bas
srvr.AddHandler("/tablehelper/TableHelper", "TableHelper", False)
'Assign the serverport and start the server
srvr.Port = 51042
srvr.Start
'Do no forget to start the messageloop
StartMessageLoop
End Sub`
Class DBHelper
`Sub Class_Globals
Public sqlObj As SQL
Private Const DBName As String = "contacts.db"
End Sub`
`Public Sub Initialize
sqlObj.InitializeSQLite(File.DirApp, DBName, False)
End Sub`
`Public Sub GetContacts As List
Dim l As List : l.Initialize
l = DBUtils.ExecuteMemoryTable(sqlObj, "SELECT * FROM CONTACTS ORDER BY Name ASC", Null, 0)
Return l
End Sub`
Class TableHelper
`Sub Class_Globals
End Sub`
`Public Sub Initialize
End Sub`
'Handle server requests and respond accordingly
'All records are selected from the table and stored in a list of maps (see dbhelper which uses dbutils)
'The list of records are assigned to a map. The map is converted into JSON format.
'Respond back to the server, the records in JSON format
`Private Sub Handle(req As ServletRequest, resp As ServletResponse)
'Put all records in a map called aaData.
'By default DataTables use the "aaData" property of the returned data which Is an Array of arrays with one entry For Each column In the table.
Dim m As Map
m.Initialize
m.Put("aaData", Main.DB.GetContacts)
'Create the JSON response
'This will be used by the DataTable in the html file to load data from an external source by using parameter sAjaxSource in document get ready.
'See the script section at the bottom of of the file objects/www/tablehelper/index.html
Dim jg As JSONGenerator
jg.Initialize(m)
resp.ContentType = "application/json"
resp.Write(jg.ToString)
End Sub`
HTML Files
Index in www Folder
<!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">
<head>
<meta http-equiv="refresh" content="1; URL=/tablehelper/index.html">
</head>
<body>
</body>
Index in TableHelper Folder
<!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">
<head>
<title>Contact List</title>
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.5/css/jquery.dataTables.css">
<script type="text/javascript" language="javascript" src="//code.jquery.com/jquery-1.11.1.min.js"></script>
<script type="text/javascript" language="javascript" charset="utf8" src="//cdn.datatables.net/1.10.5/js/jquery.dataTables.min.js"></script>
</head>
<body>
<a href="index.html">Refesh</a>
<hr>
<h1>Contact List</h1>
<div id="tableDiv">
<!--- Note: the class display used to display with formatting --->
<!--- Note: Adding the thead is a must else nothing is displayed --->
<table id="table1" class="display">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Address</th>
<th>Phone</th>
<th>EMail</th>
<th>URL</th>
<th>Notes</th>
</tr>
</thead>
</table>
</div>
<script>
$( document ).ready(function() {
//This is the method being called by the B4J AddHandler method. Important to ensure case sensitivity for TableHelper.
//Sorting is disabled means the records are displayed according the selection
//See srvr.AddHandler("/tablehelper/TableHelper", "TableHelper", False)
$('#table1').dataTable( {
"bProcessing": true,
"bPaginate": false,
"bSort": false,
"sAjaxSource": 'TableHelper?method=show'
} );
} );
</script>
</body>
</head>

Changed: , Category: WebApps jQuery, File: T6C280M2093.htm, ID: 2093