Html5 Web SQLite Database Example

1 Comment / Html Tutorial / Html5 Web Storage

This article will tell you how to use SQLite embedded database in the javascript client application.

1. How To Manage SQLite Database In Javascript Client-Side Application Steps.

  1. Call the function openDatabase(dbName, dbVersion, dbDesc, dbSize) in javascript code to get the database object. 
    database = openDatabase(dbName, dbVersion, dbDesc, dbSize);
  2. Then call the above database object’s transaction(callback_function(tx){}) function to run the callback_function in the SQLite database transaction.
  3. The tx parameter is the database transaction object, you can call the tx object’s executeSql(sql, parameters, callback_function_success(tx, result){}, callback_function_error(tx, error){} ) function to run SQL statement.
  4. The SQL statement can be any DDL or DML SQL statement.
  5. Below is an example of how to use the above methods. 
    database.transaction(function(tx){

        // select the record by username case insensetive.
        var selectSql = 'select * from ' + tableNameUserData + ' where name = \'' + userName +'\' COLLATE NOCASE';
        console.log('selectSql = ' + selectSql);

        tx.executeSql(selectSql, [], function(tx, result){

            console.log('result.rows.length = ' + result.rows.length);

            if(result.rows.length > 0){

                var message = 'The user name exist, please input another one.'
                alert(message);
                console.log(message);
                
            }else{
                // if not exist then insert the user data.
                insertFuncName(userName, userEmail, userNote);
            }

        }, function(tx, error){
            alert(error);
        });
    });

2. How To Manage SQLite Database In Javascript Client-Side Example.

 

  1. This example is similar to the example in the article How To Implement A Database Using Html5 Web Storage, the difference is that this example uses an SQLite database to save the user data in a database table.
  2. This example contains 2 source files, html5-sqlite-database-operation-example.html, html5-sqlite-database-operation-example.js.
  3. html5-sqlite-database-operation-example.html. 
    <!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Html5 Web SQLite Database Example</title>
<script type="text/javascript" src="html5-sqlite-database-operation-example.js" charset="utf-8"></script>
<style>
.block-class{
    display:block;
    margin-top:10px;
}

.label-class{
    width: 50px;
    display: inline-block;
    text-align: right;
    vertical-align: top;
}
</style>
</head>
<body onload="initApp()">
<h3>Html5 Web SQLite Database Example.</h3>
<div class="block-class">
    <div class="block-class">
        <label class="label-class">
            Name:
        </label>
        <input type="text" id="userName" placeholder="Input user name." />
    </div>

    <div class="block-class">
        <label class="label-class">
            Email:
        </label>
        <input type="text" id="userEmail" placeholder="Input user email." />
    </div>    

    <div class="block-class">
        <label class="label-class">
            Note:
        </label>
        <textarea id="userNote" placeholder="Input user note."></textarea>
    </div>

    <div class="block-class">
        <label class="label-class">
            
        </label>
        <input type="button" id="saveUserInfoBtn" value="Save" onclick="saveUserData('userName', 'userEmail', 'userNote')" />
    </div>
</div>
<hr>
<div class="block-class">
    <input type="text" id="searchByNameKeyword" placeholder="Input the user name to search." size="30" />
    <input type="button" id="searchByNameBtn" value="Search" onclick="searchByName('searchByNameKeyword')" />
    <input type="button" id="clearLocalStorageBtn" value="Clear UserData Table" onclick="clearUserDataTable()" />
</div>
<output id="outputList" style="display:block;margin-top: 10px;"></output>
</body>
</html>
  4. html5-sqlite-database-operation-example.js. 
    var outputListElement = null;
var outputListID = 'outputList';

var dbName = 'UserDB';
var dbVersion = '1.0';
var dbDesc = 'User Info Database.';
var dbSize = 2 * 1024 * 1024;
var database = null;

var tableNameUserData = 'UserData';


function initApp(){
    outputListElement = document.getElementById(outputListID);

    database = openDatabase(dbName, dbVersion, dbDesc, dbSize);

    createUserDataTable();

    searchAll(tableNameUserData);
}

function saveUserData(userNameId, userEmailId, userNoteId){

    var userNameValue = '';
    var userEmailValue = '';
    var userNoteValue = '';

    var userNameObject = document.getElementById(userNameId);
    if(userNameObject==null){
        alert('The user name input text box does not exist.');
        return;
    }else{
        userNameValue = userNameObject.value;
        if(userNameValue.trim().length == 0){
            alert('The user name can not be empty.');
            return;
        }
    }

    var userEmailObject = document.getElementById(userEmailId);
    if(userEmailObject==null){
        alert('The user email input text box does not exist.');
        return;
    }else{
        userEmailValue = userEmailObject.value;
        if(userEmailValue.trim().length == 0){
            alert('The user email can not be empty.');
            return;
        }
    }

    var userNoteObject = document.getElementById(userNoteId);
    if(userNoteObject==null){
        alert('The user note input text area does not exist.');
        return;
    }else{
        userNoteValue = userNoteObject.value;
        if(userNoteValue.trim().length == 0){
            alert('The user note input text area can not be empty.');
            return;
        }
    }

    isUserExist(userNameValue, userEmailValue, userNoteValue, insertUserData);
}

function insertUserData(userNameValue, userEmailValue, userNoteValue){

        // execute insert sql to insert the user data to the UserData table.
        database.transaction(function(tx){

            var insertSql = 'insert into UserData(id, name, email, note, time) values(?, ?, ?, ?, ?)';
    
            id = Math.floor(Math.random() * 10000);
    
            pubTime = Date.now();
    
            valueArray = [id, userNameValue, userEmailValue, userNoteValue, pubTime];
    
            console.log('insertSql = ' + insertSql);
    
            tx.executeSql(insertSql, valueArray, function(tx, result){
    
                var message = 'Save user data to local SQLite database table successfully.';
                alert(message);
    
                console.log('message = ' + message);
    
                searchAll(tableNameUserData)
    
            }, function(tx, error){
    
                var message = 'Save user data to local SQLite database table fail, the error message is ' + error
                alert(message);
    
                console.log('message = ' + message);
            });
    
        });

}

// need to implement the below function later.
function isUserExist(userName, userEmail, userNote, insertFuncName){

    database.transaction(function(tx){

        // select the record by username case insensetive.
        var selectSql = 'select * from ' + tableNameUserData + ' where name = \'' + userName +'\' COLLATE NOCASE';

        console.log('selectSql = ' + selectSql);

        tx.executeSql(selectSql, [], function(tx, result){

            console.log('result.rows.length = ' + result.rows.length);

            if(result.rows.length > 0){

                var message = 'The user name exist, please input another one.'
                alert(message);
                console.log(message);
                
            }else{
                // if not exist then insert the user data.
                insertFuncName(userName, userEmail, userNote);
            }

        }, function(tx, error){
            alert(error);
        });
    });
}

function searchByName(searchByNameKeywordId){

    var searchByNameKeywordObject = document.getElementById(searchByNameKeywordId);
    if(searchByNameKeywordObject==null){
        alert('The search user by name keyword input text box does not exist.');
        return;
    }

    var searchName = searchByNameKeywordObject.value;

    if(searchName.trim().length == 0){
        searchAll(tableNameUserData);
    }else{

        database.transaction(function(tx){

            // select all the data from the database table by the name condition.
            var selectByNameSql = 'select * from ' + tableNameUserData + ' where name like "%' + searchName + '%"';
            tx.executeSql(selectByNameSql, [], function(tx, results){
    
                var rowsNumber = results.rows.length;

                console.log('selectByNameSql = ' + selectByNameSql);
                console.log('rowsNumber = ' + rowsNumber);
    
                if(rowsNumber > 0){
    
                    // remove all the child notes in the web page message list. 
                    outputListElement.innerHTML = '';
    
                    for(var i=0; i<rowsNumber; i++){
    
                        rowData = results.rows[i];
    
                        addUserDataOnWebPage(rowData);
    
                    }
                    
                }
    
            }, function(tx, error){
    
    
            });
    
        });
    }
}


function createUserDataTable(){

    database.transaction(function(tx){

        // create the UserData table if not exist.
        var createTableSql = 'create table if not exists UserData(id unique, name TEXT, email TEXT, note TEXT, time INTEGER)';
        tx.executeSql(createTableSql);

    });
}

function searchAll(tableName){

    database.transaction(function(tx){

        // select all the data from the database table.
        var selectAllSql = 'select * from ' + tableName;
        tx.executeSql(selectAllSql, [], function(tx, results){

            var rowsNumber = results.rows.length;

            console.log('selectAllSql = ' + selectAllSql);

            console.log('rowsNumber = ' + rowsNumber);

            // first empty all the user data list on the web page. 
            outputListElement.innerHTML = '';

            if(rowsNumber > 0){

                for(var i=0; i<rowsNumber; i++){

                    rowData = results.rows[i];

                    addUserDataOnWebPage(rowData);

                }
                
            }

        }, function(tx, error){


        });

    });
}


function addUserDataOnWebPage(rowData){

    var labelUserId = document.createElement('label');
    labelUserId.style.display = 'block';
    var labelUserIdText = document.createTextNode('Id:' + rowData.id);
    labelUserId.append(labelUserIdText);
    outputListElement.append(labelUserId);

    var labelUserName = document.createElement('label');
    labelUserName.style.display = 'block';
    var labelUserNameText = document.createTextNode('Name:' + rowData.name);
    labelUserName.append(labelUserNameText);
    outputListElement.append(labelUserName);

    var labelUserEmail = document.createElement('label');
    labelUserEmail.style.display = 'block';
    var labelUserEmailText = document.createTextNode('Email:' + rowData.email);
    labelUserEmail.append(labelUserEmailText);
    outputListElement.append(labelUserEmail);

    var labelUserNote = document.createElement('label');
    labelUserNote.style.display = 'block';
    var labelUserNoteText = document.createTextNode('Note:' + rowData.note);
    labelUserNote.append(labelUserNoteText);
    outputListElement.append(labelUserNote);

    var labelPubTime = document.createElement('label');
    labelPubTime.style.display = 'block';

    var date = new Date();
    date.setTime(rowData.time);

    var labelPubTimeText = document.createTextNode('Publish Time:' + date.toLocaleDateString() + ' ' + date.toLocaleTimeString());
    labelPubTime.append(labelPubTimeText);
    outputListElement.append(labelPubTime);

    var hr = document.createElement('hr');
    outputListElement.append(hr);
}


function clearUserDataTable(){

    database.transaction(function(tx){

        var delSql = 'delete from ' + tableNameUserData;

        tx.executeSql(delSql, [], function(tx, result){

            var message = 'Delete all data from UserData table successfully.';

            alert(message);

            console.log(message);

            console.log(delSql);

            searchAll(tableNameUserData);
        }, function(tx, error){


        });
    });

}

1 thought on “Html5 Web SQLite Database Example”

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.