Sql Tutorial

Displaying a table with records

Blended Admin supports the following relational databases

  • Sqlite
  • MySql
  • MariaDb
  • Postgres
  • Oracle
  • SqlServer
  • DB2
  • Odbc

Examples in the tutorial use Sqlite database but the same code can be re-used for other databases.

Let’s start with displaying a list of items. The following code renders first 200 albums in the table.

function main(arg)
{
  var sqlClient = new  SqlClient({provider:'Sqlite',connectionString:'Data Source=chinook.db;'});
  var albums = sqlClient.query('select * from albums');
  var tableView = new TableView(albums);
  return [tableView];
}
main(arg);

First, the instance of SqlClient is initialized with the name of provider and connection string. Then, the query returns an array of albums. At the end, the TableView is returned with the items to display. The result is the following:

Sql - Show List Of Items

The code above always displays the first 200 item of the array. The reason for that is the defualt size of TableView. You can change it by specifying the size prameter of TableView. For example: new TableView({rows: albums, size: 300}).

However, in most cases, you want to implement paging to be able to browse tables with thousands of records. TableView displays the “Next” button belows the rows. When you click it, the HTTP GET is triggered and the parameter p is added to the query string of url (eg. /Default/items/11?p=2). The value of the parameter can be read like that arg.queryString.p and used to implement pagination.

function main(arg)
{
  var page = arg.queryString.p ? parseInt(arg.queryString.p) : 1;
  var pageSize = 20;
  var begin = (page - 1) * pageSize;
  var end = begin + pageSize;
  
  var sqlClient = new  SqlClient({provider:'Sqlite',connectionString:'Data Source=chinook.db;'});
  var albums = sqlClient
     .query('select * from albums')
     .slice(begin, end);
  
  var tableView = new TableView(albums);
  return [tableView];
}
main(arg);

The code above, first read entire list of albums from the database, then slice a piece of the array. It is not very effective and will be slow for a table with big number of rows. Depending on the database, the server side rendering can be implemented in different way. In Sqlite, we can use limit keyword to limit the number of records returned by query.

function main(arg)
{
  var page = arg.queryString.p ? parseInt(arg.queryString.p) : 1;
  var pageSize = 20;
  var begin = (page - 1) * pageSize;
  var end = begin + pageSize;
  
  var sqlClient = new  SqlClient({provider:'Sqlite',connectionString:'Data Source=chinook.db;'});
  var albums = sqlClient
     .query('select * from albums limit' + end)
     .slice(begin, end);
  
  var tableView = new TableView(albums);
  return [tableView];
}
main(arg);

That improves the speed significantly as we read less elements form the database. It’s possible to do more optimizations, but in the article we stop here.

Next, let’s look at filtering.

function main(arg)
{
  var page = arg.queryString.p ? parseInt(arg.queryString.p) : 1;
  var pageSize = 20;
  var begin = (page - 1) * pageSize;
  var end = begin + pageSize;
 
  var title = arg.queryString.title ? arg.queryString.title : "";
  var formView = new FormView({
    method:'get',
    controls: [[{name:'title', value: title},null]]
  });
  
  var sqlClient = new  SqlClient({provider:'Sqlite',connectionString:'Data Source=chinook.db;'});
  var albums = sqlClient
     .query({
         sql: `select * from albums 
               where title like @title 
               limit ` + end,
         parameters: {title: title + '%'}
     })
     .slice(begin, end);
  var tableView = new TableView(albums);
  
  return [formView,tableView];
}
main(arg);

FormView is used to display a form. The form in the example, render title control that value is used to filter albums.

Sql - Show List Of Items