Phonegap as a framework allows mobile developers to develop their apps using HTML 5 + CSS 3 + Javascript that can run in 6 platforms (iOS, Android, BlackBerry, WebOS, Symbian, and Bada).
If you are new in using Phonegap Mobile Framework for developing android application, go here to get started.
Now download Jquery mobile and place the js, css and images under folder ‘projectname/asset/www/’
In this tutorial we will create a table in local android database (SQLite), retrieve and show it in listview using Jquery mobile. The name of database will be Dummy_DB and the table will be FootballPlayer. The table will consist of three coloumns. First is ID which is auto increment, the second is Name and the last is Club.
Ok, let’s get a cup of coffee..
Open your index.html and edit it
<!DOCTYPE html>
<html>
<head>
<title>Page Title</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="jquery.mobile-1.0rc2.min.css" />
<script type="text/javascript" src="js/phonegap-1.1.0.js"></script>
<script type="text/javascript" src="js/jquery.min.js"/></script>
<script type="text/javascript" src="js/jquery.mobile-1.0rc2.min.js"></script>
</head>
<body>
<div data-role="page">
<div data-role="header" data-position="fixed" data-theme="b">
<h1>Soccer Player</h1>
</div>
<div data-role="content">
<ul id="SoccerPlayerList">
</ul>
</div>
</div>
<!--end of Soccer Player Page--->
</body>
</html>
Now you have a page that only contain header and empty content, you can run the code in browser instead of run it in android emulator or real device, before we add any code for connecting the app to SQLite.
Next, add some javascript code within <head> tag
//add listener when device ready
document.addEventListener("deviceready", onDeviceReady, false);
var db = window.openDatabase("Dummy_DB", "1.0", "Just a Dummy DB", 200000); //will create database Dummy_DB or open it
//function will be called when device ready
function onDeviceReady(){
db.transaction(populateDB, errorCB, successCB);
}
//create table and insert some record
function populateDB(tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS SoccerPlayer (id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Club TEXT NOT NULL)');
tx.executeSql('INSERT INTO SoccerPlayer(Name,Club) VALUES ("Alexandre Pato", "AC Milan")');
tx.executeSql('INSERT INTO SoccerPlayer(Name,Club) VALUES ("Van Persie", "Arsenal")');
}
//function will be called when an error occurred
function errorCB(err) {
alert("Error processing SQL: "+err.code);
}
//function will be called when process succeed
function successCB() {
alert("success!");
db.transaction(queryDB,errorCB);
}
//select all from SoccerPlayer
function queryDB(tx){
tx.executeSql('SELECT * FROM SoccerPlayer',[],querySuccess,errorCB);
}
function querySuccess(tx,result){
$('#SoccerPlayerList').empty();
$.each(result.rows,function(index){
var row = result.rows.item(index);
$('#SoccerPlayerList').append('<li><a href="#"><h3 class="ui-li-heading">'+row['Name']+'</h3><p class="ui-li-desc">Club '+row['Club']+'</p></a></li>');
});
$('#SoccerPlayerList').listview();
}
This javascript code are used for connecting the app to the SQLite Database. Ok, then how this thing work ?
First thing first, you’ll have to add listener when the device is ready. Then when the device ready it create connection to the database and do the transaction.
//add listener when device ready
document.addEventListener("deviceready", onDeviceReady, false);
var db = window.openDatabase("Dummy_DB", "1.0", "Just a Dummy DB", 200000); //will create database Dummy_DB or open it
//function will be called when device ready
function onDeviceReady(){
db.transaction(populateDB, errorCB, successCB);
}
Function populateDB() run to create the table and insert some record into it. If it failed, errorCB() function will be called, otherwise successCB() will.
//create table and insert some record
function populateDB(tx) {
tx.executeSql('DROP TABLE IF EXISTS SoccerPlayer');
tx.executeSql('CREATE TABLE IF NOT EXISTS SoccerPlayer (id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Club TEXT NOT NULL)');
tx.executeSql('INSERT INTO SoccerPlayer(Name,Club) VALUES ("Alexandre Pato", "AC Milan")');
tx.executeSql('INSERT INTO SoccerPlayer(Name,Club) VALUES ("Van Persie", "Arsenal")');
}
//function will be called when an error occurred
function errorCB(err) {
alert("Error processing SQL: "+err.code);
}
//function will be called when process succeed
function successCB() {
alert("success!");
db.transaction(queryDB,errorCB);
}
When successCB() run, it does another transaction to the database which will retrieve all record from SoccerPlayer table. And the query result stored in array which then will be displayed in html with listview style.
function queryDB(tx){
tx.executeSql('SELECT * FROM SoccerPlayer',[],querySuccess,errorCB);
}
function querySuccess(tx,result){
$('#SoccerPlayerList').empty();
$.each(result.rows,function(index){
var row = result.rows.item(index);
$('#SoccerPlayerList').append('<li><a href="#"><h3 class="ui-li-heading">'+row['Name']+'</h3><p class="ui-li-desc">Club '+row['Club']+'</p></a></li>');
});
$('#SoccerPlayerList').listview();
}
Finally, you can run it on your android device or emulator. This is the app gonna be look like in real device running Android 2.3 OS
Viva la Coding !
Thanks to om barock and ojankill
Please introduce my first Android Application, Sharee (Sharia Economics Education) now on Play Store.
and here is My post (in Bahasa) about Sharee.
Luthfi Hariz 
nice turorial
Mana nih thanks for nya …. hahaha
hahaha beresss
Copied from phonegaps tutorial, which either way they both don’t suffice as a real database application since you can’t save changes (as it re-populates the db every time the app starts).
Thanks Luthfi Hariz,can you show me where is db store?
Hello thx for the tutorial but i dont find the jquery.min.js file in the folder
you can download it from here http://jquery.com/
Reblogged this on Operating System Class and commented:
Create Android App with SQLite using Phonegap + Jquery mobile
Is there any way to do this where the database is not created each time. So that the data could be stored in a SQLite database file? That way all of your data would not be embedded in the app. It would also let you edit the DB offline.
Hi Luthfi,
very nice tutorial.
I am a Brazilian developer.
I wonder if I can translate the tutorial and post on my blog?
keeping the original credits, of course.
Thanks
go ahead 🙂
can u send me the downloadable file
illa mahse pattilla.. work hard
i had followed all the steps and while running the application
-> first i got an alert message “success”
-> second i got a warning “error processing sql:0”
so i have understood that
-> creating a table and inserting 2 rows is successful
-> then in successCB() -> when this method calls db.transaction() for another transaction it is showing error
-> so there is a problem with queryDB() for selecting rows and displaying it in a listview ..
so how to tackle this problem 🙂
i got with my problem 🙂 but ..,
– at a glance
– i have not edited my manifest file i.e AndroidManifest.xml with the user permissions for creating a database -sqllite, internet permission …. so i have error logs in my logcat(console)
thats the end of the matter.
but one more hurdle to pass on .
* i cannot add the elements to the listview i am getting an error message
‘ ERROR Processing SQL:0’
so i commented the listview() method & can view 1 by 1 but not in a listview
hello,
plz tell me how the database permission can be set ?
Thanks.
you can try something like this.
var len = result.rows.length;
for (var i=0; i<len; i++){
var row = result.rows.item(i);
$('#SoccerPlayerList').append('‘+row[‘Name’]+’Club ‘+row[‘Club’]+’‘);
}
instead of the each loop at querySucess function
hi,
this code don’t work for me the listview is still empty!!!
how i call the function in the listview to display the data ???
thanks
How do you add a search bar or filter items bar to this app?
very nice thanx for share
Thank you for this GOOD tutorials
Hi,
This looks really useful. Do you know if this created database is accessible via a phonegap plugin?
wuih anak uin ada disini,,pake bahasa inggris lagi..hehee
wah ada senior.. makasih udah mampir mas wahyu :))
hehee..sama-sama….^_^
saya sudah coba dengan cara yang sama percis tapi gak nampil apa2 gan ??
nampil cuman header nya aja, terus pas saya check di sqlitenya pun ga ada databases nya,
apa memang harus dibuat dulu databasesnya atau bisa langsung tergenerate sendiri, hehe
malum gan newbie
salam kenal
where i should add this java script code..plz tel..???
Hi,
Thank you for your tutorial. It is grate. But I have one doubt.
I think in this tutorial you use the “W3C Web SQL Database” ( http://docs.phonegap.com/en/2.0.0/cordova_storage_storage.md.html#Storage ) for store the values. How can we use the native database ( SQLite ) in PhoneGap. I am using Eclipse IDE for develop the application.
ooooooooooooo………………thanku.This is good site
hatur nuhun gan tutorialnya, tp kok ngga muncul ya? database nya udah ter-create cuman datanya blum ke insert itu kenapa ya, klo boleh minta file agan nya dong???
okay gan udah muncul, ane mau tanya nih klo di list nya mau di tambahin link ke satu halaman dan isi halaman itu diambil dari database local juga, maksudnya ada du table (1)tabel_lagu (2)tabel_lirik, ada relasi di dua tabel itu, jd klo kita pilih lagu -> maka muncul lirik nya. 🙂
mohon info nya ya gan, makasiih sebelumnya..
i get this
I/Database(689): sqlite returned: error code = 14, msg = cannot open file at source line 25467
and my listview is empty..
code dont run for me, emulator displays nothing but the whole database code starting from ondeviceready function
Hello there.I pray someone out there respond to my comment urgently.This is an urgent SOS. Thank you for your blog and for taking out time to impact knowledge.However,I appreciate your work with phone gap api for local storage-it has helped in my understanding of the subject.I came accross a problem recently cos I was trying to insert into my database in my local language with this text(Odò kan si ti Edeni ṣàn wá lati rin ọgbà na; lati ibẹ̀ li o gbé yà, o si di ipa ori mẹrin. ) but on display the result after select query gave was different(Odò kan si ti Edeni ṣà n wá lati rin á»gbà n…).Can you help.How do I get the exact text I inserted in the database.
[…] https://luthfihariz.wordpress.com/2011/10/23/android-sqlite-phonegap-jquerymobile/ […]
Hey,,Great Tutoria,,=) Thanks for the post. It works well here. If you can please tell me where the Database is located. I have to send the created database for someone and add data to it.
Thank you..!!
Best of luck
BTW I found it and found the way to export it. Thanks for the post again. =)
a nice tutorial but i m not getting any result on emulator no error and no result pls tell me how to check either database has been created or not
hi.. thank you for this tuto.. it’s very helpfull
but i have a question plz.. is this code obligatory requires to use phoneGap and android? i’m new to web mobile development (especially jquery) and i’m developping a web mobile application using jquery mobile, and i want to create and connect to an html 5 database (sqlite), so i can execute some queries later.. i tried to use this code with jquery.mobile-1.0rc2 and jquery-1.6.4, and i tried later by adding phoneGap 1.1.0.js, and it still not work.. the database Dummy_DB is created, but the table is not created and any of the queries (insert) is executed.
do you have any idea why it’s not working?
Evertything is working, except the style for me =(
Excellent tutorial, just a query that is saved or generated sqlite file with the data that have been recorded.
Mantap artikelnya. Ternyata bisa maen database dengan jQuerymobile.