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

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

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

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

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

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

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

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

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

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

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

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

  11. Pingback: Create Android App with SQLite using Phonegap + Jquery mobile | Nihal Khan Blog

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

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

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