Hello friend,
Tired of searching the Cordova SQLite storage example? No worries, I have written a step by step tutorial for integrating SQLite with your Cordova (PhoneGap) apps.
What is SQLite?
SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private.
What we’ll learn
- Create a new SQLite Database
- Create a New Table
- Insert Data
- Read Data
- Update Data
- Delete Data
- Drop table
- Drop Database
Step by Step Guide for PhoneGap SQLite tutorial
Step 1: Create a new PhoneGap / Apache Cordova Project
Cordova create SQLiteExample com.codesundar.sqlite SQLiteExample cd SQLiteExample
Step 2: Let’s add platform and plugin to your project
cordova platform add android cordova plugin add cordova-sqlite-storage
Dependancies : I used Ionic CSS Components (ionic.css) & Jquery (jquery.js) with this example
Now we’re going to perform CRUD operation with Apache Cordova / PhoneGap SQLite.
Database Name : mySQLite.db
Table Name : codesundar
Table Fields : id, title, desc
Functions / Methods for database Operations
openDatabase
– Create database connection
transaction
– create transactions
executeSql
– executes Query with database
SYNTAX
myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(transaction)</span> </span>{
<span class="hljs-keyword">var</span> executeQuery = <span class="hljs-string">"QUERY"</span>;
transaction.executeSql(executeQuery, [],
<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx, result)</span> </span>{
<span class="hljs-comment">//Success</span>
},
<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(error)</span> </span>{
<span class="hljs-comment">// Error</span>
});
})
Creating New Database using PhoneGap with SQLite Plugin
First, we need to create / open a database.
<span class="hljs-keyword">var</span> myDB = <span class="hljs-built_in">window</span>.sqlitePlugin.openDatabase({name: <span class="hljs-string">"mySQLite.db"</span>, location: <span class="hljs-string">'default'</span>});
Creating New Table using PhoneGap with SQLite Plugin
Using the myDB variable we need to execute the command.use CREATE
a query for creating a table
myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(transaction)</span> {</span>
transaction.executeSql('CREATE TABLE IF NOT EXISTS codesundar (id integer primary key, title <span class="hljs-built_in">text</span>, desc <span class="hljs-built_in">text</span>)', [],
<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx, result)</span> {</span>
alert(<span class="hljs-string">"Table created successfully"</span>);
},
<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(error)</span> {</span>
alert(<span class="hljs-string">"Error occurred while creating the table."</span>);
});
});
Insert data into codesundar
table
Inserting data to the table. Here I used static data, instead of that you can pass the data in dynamic. Use INSERT
query for inserting data to SQLite database
<span class="hljs-keyword">var</span> title = <span class="hljs-string">"sundaravel"</span>;
<span class="hljs-keyword">var</span> desc = <span class="hljs-string">"phonegap freelancer"</span>;
myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(transaction)</span> </span>{
<span class="hljs-keyword">var</span> executeQuery = <span class="hljs-string">"INSERT INTO codesundar (title, desc) VALUES (?, ?)"</span>;
transaction.executeSql(executeQuery, [title, desc], <span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx, result)</span> </span>{
alert(<span class="hljs-string">'Inserted'</span>);
},
<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(error)</span> </span>{
alert(<span class="hljs-string">'Error occurred'</span>);
});
});
View All Data
for reading all data from table execute SELECT
the query
myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">transaction</span>) </span>{
transaction.executeSql(<span class="hljs-string">'SELECT * FROM codesundar'</span>, [], <span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">tx, results</span>) </span>{
<span class="hljs-keyword">var</span> len = results.rows.length,
i;
$(<span class="hljs-string">"#rowCount"</span>).append(len);
<span class="hljs-keyword">for</span> (i = <span class="hljs-number">0</span>; i < len; i++) {
$(<span class="hljs-string">"#TableData"</span>).append(<span class="hljs-string">"<tr><td>"</span> + results.rows.item(i).id + <span class="hljs-string">"</td><td>"</span> + results.rows.item(i).title + <span class="hljs-string">"</td><td>"</span> + results.rows.item(i).desc + <span class="hljs-string">"</td></tr>"</span>);
}
}, <span class="hljs-literal">null</span>);
});
Update Data to SQLite Database Table
use UPDATE
Query for updating the database with id
$(<span class="hljs-string">"#update"</span>).click(<span class="hljs-function"><span class="hljs-keyword">function</span>() </span>{
<span class="hljs-keyword">var</span> id = $(<span class="hljs-string">"#id"</span>).text();
<span class="hljs-keyword">var</span> title = $(<span class="hljs-string">"#title"</span>).val();
<span class="hljs-keyword">var</span> desc = $(<span class="hljs-string">"#desc"</span>).val()
myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">transaction</span>) </span>{
<span class="hljs-keyword">var</span> executeQuery = <span class="hljs-string">"UPDATE codesundar SET title=?, desc=? WHERE id=?"</span>;
transaction.executeSql(executeQuery, [title, desc, id],
<span class="hljs-comment">//On Success</span>
<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">tx, result</span>) </span>{
alert(<span class="hljs-string">'Updated successfully'</span>);
},
<span class="hljs-comment">//On Error</span>
<span class="hljs-function"><span class="hljs-keyword">function</span>(<span class="hljs-params">error</span>) </span>{
alert(<span class="hljs-string">'Something went Wrong'</span>);
});
});
});
Delete Data from table
use DELETE
Query for updating the database with id
myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(transaction)</span> </span>{
<span class="hljs-keyword">var</span> executeQuery = <span class="hljs-string">"DELETE FROM codesundar where id=?"</span>;
transaction.executeSql(executeQuery, [id],
<span class="hljs-comment">//On Success</span>
<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx, result)</span> </span>{
alert(<span class="hljs-string">'Delete successfully'</span>);
},
<span class="hljs-comment">//On Error</span>
<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(error)</span> </span>{
alert(<span class="hljs-string">'Something went Wrong'</span>);
});
});
Delete entire table
use DROP
Query for updating the database with id
myDB.transaction(<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(transaction)</span> </span>{
<span class="hljs-keyword">var</span> executeQuery = <span class="hljs-string">"DROP TABLE IF EXISTS codesundar"</span>;
transaction.executeSql(executeQuery, [],
<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(tx, result)</span> </span>{
alert(<span class="hljs-string">'Table deleted successfully.'</span>);
},
<span class="hljs-function"><span class="hljs-keyword">function</span><span class="hljs-params">(error)</span> </span>{
alert(<span class="hljs-string">'Error occurred while droping the table.'</span>);
}
);
});