Create Android App with SQLite using Phonegap + Jquery mobile

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/’

project tree android phonegap

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.

Jquery mobile

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

Phonegap Jquerymobile

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.

About these ads
Tagged , , , ,

43 thoughts on “Create Android App with SQLite using Phonegap + Jquery mobile

  1. Fauzan Qadri says:

    nice turorial

  2. Barock says:

    Mana nih thanks for nya …. hahaha

  3. asdf says:

    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).

  4. Nam Quoc Nguyen says:

    Thanks Luthfi Hariz,can you show me where is db store?

  5. sejaa says:

    Hello thx for the tutorial but i dont find the jquery.min.js file in the folder

  6. Luthfi says:

    Reblogged this on Operating System Class and commented:

    Create Android App with SQLite using Phonegap + Jquery mobile

  7. Scott Hummel says:

    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.

  8. Daniel Lemes says:

    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

  9. 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

  10. khalil says:

    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

  11. Vivek says:

    How do you add a search bar or filter items bar to this app?

  12. atif ali khan says:

    very nice thanx for share

  13. Rony says:

    Thank you for this GOOD tutorials

  14. Dan says:

    Hi,

    This looks really useful. Do you know if this created database is accessible via a phonegap plugin?

  15. wuih anak uin ada disini,,pake bahasa inggris lagi..hehee

  16. 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

  17. Khushboo says:

    where i should add this java script code..plz tel..???

  18. sabeesh says:

    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.

  19. CHANDU says:

    ooooooooooooo………………thanku.This is good site

  20. rikoy says:

    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???

  21. rikoy says:

    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..

  22. san says:

    i get this
    I/Database(689): sqlite returned: error code = 14, msg = cannot open file at source line 25467
    and my listview is empty..

  23. vikrant says:

    code dont run for me, emulator displays nothing but the whole database code starting from ondeviceready function

  24. abayomi says:

    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.

  25. Visitha says:

    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

  26. erumhannan says:

    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

  27. jina says:

    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?

  28. Jonathan says:

    Evertything is working, except the style for me =(

  29. Ander says:

    Excellent tutorial, just a query that is saved or generated sqlite file with the data that have been recorded.

  30. Ricky says:

    Mantap artikelnya. Ternyata bisa maen database dengan jQuerymobile.

  31. bird says:

    i also managed to solve the second problem by using for loop instead of the each loop. I referred to the phonegap api doc.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 492 other followers

%d bloggers like this: