Html5 Web SQLite Database Example

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.

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

Clicky