Populate SQLite using XML and integrating inside Activity

Question

From all the tutorials I have found (including answers in Stackoverflow) I couldn't find a complete tutorial showing how to achieve XML to SQLite population. I created a DB helper but I don't know how to integrate it inside my activity. What I'm trying to do is; as soon as the application launches it should populate the DB with XML.

The XML

I have an XML file "amawal_posts.xml" in the "res\xml" folder that contains some entries that I want to populate inside a Database..

<?xml version="1.0" encoding="utf-8"?>
<database name="npma_amawal" >
    <!-- Table wp_posts -->
    <table name="wp_posts" >
        <column name="ID" >948</column>
        <column name="post_content" >اورغ</column>
        <column name="post_title" >ure</column>
    </table>
    <table name="wp_posts" >
        <column name="ID" >46</column>
        <column name="post_content" >adlis g llan iwaliwn FR: dictionnaire.</column>
        <column name="post_title" >amawal</column>
    </table>
</database>

This XML contains more than 4000 records.

The SQLiteOpenHelper

Here's the content of "XMLtoSQLite.java"

package com.np.amawalandroiddb;

import java.io.IOException;

import org.xmlpull.v1.XmlPullParserException;

import com.np.amawalandroiddb.R;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.content.res.XmlResourceParser;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class XMLtoSQLite extends SQLiteOpenHelper {

    private final Context fContext;

    // Set TAG for error catching
    public static String TAG = "XMLtoSQLite";

    // Set database columns
    public static String column_ID = null;
    public static String column_post_content = null;
    public static String column_post_title = null;

    public XMLtoSQLite(Context context) {
        super(context, "amawal", null, 1);
        fContext = context;
    }


    public void createDataBase (SQLiteDatabase db) throws IOException {
        db.execSQL("CREATE TABLE amawal_posts (" + "ID INTEGER PRIMARY KEY,"
                + "post_content TEXT," + "post_content TEXT" + ");");

        // Add default records amawal_posts
        ContentValues Columns = new ContentValues();

        // Get XML resource file
        Resources res = fContext.getResources();

        // Open XML file
        int eventType = -1;
        while (eventType != XmlResourceParser.END_DOCUMENT) {
            XmlResourceParser database = res.getXml(R.xml.amawal_posts);
            String name = database.getText();
            Log.d(TAG, name);

            try {
                if (database.getEventType() == XmlResourceParser.START_TAG) 
                {
                    String s = database.getName();

                    if (s.equals("table")) 
                    {
                        database.next(); // moving to the next node
                        if (database.getName() != null  && database.getName().equalsIgnoreCase ( "column")) 
                        {
                            column_ID = database.getText(); // to get  value getText() method should be used
                            database.next();

                            column_post_content = database.getText();
                            database.next();

                            column_post_title = database.getText(); 

                            // Insert the values inside the DB
                            Columns.put("ID", column_ID);
                            Columns.put("post_content", column_post_content);
                            Columns.put("post_title", column_post_title);

                            db.insert("amawal", null, Columns);

                        }

                        Log.d(TAG, column_ID);
                        Log.d(TAG, column_post_content);
                        Log.d(TAG, column_post_title);
                    }
                }
            } 
            //Catch errors
            catch (XmlPullParserException e)
            {       
                Log.e(TAG, e.getMessage(), e);
            }
            catch (IOException e)
            {
                Log.e(TAG, e.getMessage(), e);

            }           
            finally
            {           
                //Close the XML file
                database.close();
            }
        }
    }

    /* Update database to latest version */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Crude update, make sure to implement a correct one when needed.

        Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS animals");
        onCreate(db);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub

    }

}

My MainActivity

The content of "MainActivity.java"

package com.np.amawalandroiddb;


import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;

public class MainActivity extends Activity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        // Begin DB work
        XMLtoSQLite db = new XMLtoSQLite(this);

    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }

}

Being a beginner in Java programming makes relation between activities/layouts/services.. a bit difficult to understand :(

The question: How to call/execute this inside my Activity?

Answer 1

Thanks guys for all your answers that, among other resources, helped to find a solution to my problem.

Basically I needed to build a database out of XML file and then export this database into another project; this way I don't need to ship the XML directly with my final app nor would I risque the fact that the database population from XML when run on a cheap smartphone gets hung..

That said, lets dig on the code that worked for me :) In my main layout I created a button with ID "createButton".

main.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".XMLtoDB" >

<Button
    android:id="@+id/createButton"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:text="@string/create_database" />

<ListView
    android:id="@+id/list"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:layout_below="@+id/createButton" />

Then in my main activity java file I set the button when clicked it uses a SQLiteOpenHelper to populate the database from the XML.

XMLtoDB.java

ListView listView;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);



    final Button createbtn = (Button) findViewById(R.id.createButton);
    createbtn.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View v) {
            SQLiteDatabase db = new DbHelper(getApplicationContext()).getWritableDatabase();

            if (db != null) {
                Toast.makeText(XMLtoDB.this, "Database is created!", Toast.LENGTH_SHORT).show();
            } else {
                Toast.makeText(XMLtoDB.this, "Error creating database!", Toast.LENGTH_SHORT).show();
            }
        }
    });

}

The Dbhelper.java

/*
* How to extract the generated DataBase in DDMS?
 * First of all, delete the old database by going to the emulator app amanger
 * then clear cash.
 * Then in DDMS re-select the device in the left pane. For some reasons, 
 * it needs to be refreshed and the previous selection is invalid.
 * */
package com.np.amawalandroid.db;

import java.io.IOException;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

import com.np.amawalandroid.xml.XMLParser;
import com.np.amawalandroid.xml.XMLParserObject;

public class DbHelper extends SQLiteOpenHelper implements BaseColumns {

public static final String DB_TEST = "amawal.sqlite";
public static final String TABLE_NAME = "wp_posts";
public static final String ID = "ID";
public static final String post_content = "post_content";
public static final String post_title = "post_title";
private static Context mContext;
List<XMLParserObject> posts = null;

public DbHelper(Context context) {
    super(context, DB_TEST, null, 1);
    mContext = context;
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE " + TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " + ID + " INTEGER, " + post_content + " TEXT, "
            + post_title + " TEXT);");

    // Insert data from XML
    XMLParser parser = new XMLParser();
    try {
        posts = parser.parse(mContext.getAssets().open("wp_posts.xml"));

        for (XMLParserObject post : posts) {
            //Toast.makeText(mContext, post.getpostTitle(), Toast.LENGTH_SHORT).show();

            ContentValues values = new ContentValues();

            values.put(ID, post.getId());
            values.put(post_content, post.getPostContent());
            values.put(post_title, post.getpostTitle());
            db.insert(TABLE_NAME, ID, values);

        }
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    onCreate(db);
}

}

To parse my XML file that looks like:

<?xml version="1.0" encoding="utf-8"?>
<pma_xml_export version="1.0" xmlns:pma="http://www.phpmyadmin.net/some_doc_url/">
<database name="npma_amawal">
       <table name="wp_posts">
            <column name="ID">46</column>
            <column name="post_content">adlis g llan iwaliwn</column>
            <column name="post_title">amawal</column>
        </table>

I use

XMLParser.java

package com.np.amawalandroid.xml;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.xmlpull.v1.XmlPullParser;
import org.xmlpull.v1.XmlPullParserException;
import org.xmlpull.v1.XmlPullParserFactory;

public class XMLParser {
    List<XMLParserObject> posts;
    private XMLParserObject post_ref;

    public XMLParser() {
        posts = new ArrayList<XMLParserObject>();
    }

    public List<XMLParserObject> parse(InputStream is) {
        XmlPullParserFactory factory = null;
        XmlPullParser parser = null;
        try {
            factory = XmlPullParserFactory.newInstance();
            factory.setNamespaceAware(true);
            parser = factory.newPullParser();

            parser.setInput(is, null);

            int eventType = parser.getEventType();
            while (eventType != XmlPullParser.END_DOCUMENT) {
                String tagname = parser.getName();
                // System.out.println("=======" + tagname + "============");
                switch (eventType) {
                case XmlPullParser.START_TAG:
                    // if <table> create a new instance of post
                    if (tagname.equalsIgnoreCase("table")) {
                        post_ref = new XMLParserObject();
                    }
                    // if <column>
                    if (tagname.equalsIgnoreCase("column")) {
                        if (parser.getAttributeValue(null, "name").equalsIgnoreCase("ID")) {
                            // System.out.println("ID found! ");
                            post_ref.setId(Integer.parseInt(parser.nextText()));

                        } else if (parser.getAttributeValue(null, "name").equalsIgnoreCase("post_content")) {
                            // System.out.println("post_content found! ");
                            post_ref.setPostContent(parser.nextText());

                        } else if (parser.getAttributeValue(null, "name").equalsIgnoreCase("post_title")) {
                            // System.out.println("post_title found! ");
                            post_ref.setpostTitle(parser.nextText());
                        }
                    }
                    break;

                case XmlPullParser.END_TAG:
                    if (tagname.equalsIgnoreCase("table")) {
                        posts.add(post_ref);
                        // System.out.println("Posts so far " + post_ref);
                    }
                    break;

                default:
                    break;
                }
                eventType = parser.next();
            }

        } catch (XmlPullParserException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return posts;
    }
}

with

XMLParserObject.java

public class XMLParserObject {

    private int ID;
    private String post_title;
    private String post_content;

    public int getId() {
        return ID;
    }

    public void setId(int ID) {
        this.ID = ID;
    }

    public String getpostTitle() {
        return post_title;
    }

    public void setpostTitle(String post_title) {
        this.post_title = post_title;
    }

    public String getPostContent() {
        return post_content;
    }

    public void setPostContent(String post_content) {
        this.post_content = post_content;
    }

    @Override
    public String toString() {
        return ID + ": " + post_title + "\n" + post_content;
    }
}

Answer 2

I think that the best way for you is to follow some useful guides. Let's split the problem into more simple parts:

Database

IMHO you have to well create your database class, so follow Vogella's guide; it's very useful and explain in detail everything about database.

Downloading your data from web

In the future (see comments above) you will need to download your data from web, so use AsyncTask class in order to connect to a site and then download all you need; if you don't know this class, read this official guide.

Parsing XML

You have to parse your XML: you can use this guide, or this one. I use DocumentBuilder, but my XML files are different from yours:

...
String errorCode = null;
Document changes;
try {
    // URL were lies some xml-data
    URL url = new URL(params[0]);

    // Creating a document
    InputSource is = new InputSource(url.openStream());
    DocumentBuilder db = DocumentBuilderFactory.newInstance().newDocumentBuilder();
    changes = db.parse(is);
    changes.getDocumentElement().normalize();

    // Searching AUTHKEY in created document
    NodeList root = changes.getElementsByTagName("Auth");
    Element myRoot = (Element) root.item(0);
    errorCode = myRoot.getAttribute("ErrorCode");
    auth = myRoot.getAttribute("Key");
} catch (IOException e) {
    errorCode = null;
} catch (ParserConfigurationException e) {
    errorCode = null;
} catch (SAXException e) {
    errorCode = null;
} catch (Exception e) {
    errorCode = null;
}
...

I hope that you will find useful every link.

EDIT

On your MainActivity add this (of course after you have changed your code):

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    String url = "http://myUrlWhereDataIsStored.com";

    // Creating database
    MyDatabase database = new MyDatabase(this);
    // Populating database
    new MyAsyncTask().execute(url);
}

Answer 3

You should not put them inside an xml file.

Reason one : For the performance Reason two : For the clarity of your code

Usefull link : http://www.vogella.com/articles/AndroidSQLite/article.html

Architechture example :

AnObject.java file that represent a single table (onCreate and onUpdate method should be implemented custom here, toString(), ...) AnObjectManger.java file that manage that table, do the request, .. AnOpenHelperDataBase.java that simplify the use of sqlite and do the basics operations

And note that to create the database you only need this example:

public DatabaseOpenHelper(Context context, CursorFactory factory) {

        super(context, DATA_BASE_NAME, factory, DATABASE_VERSION);
    }

EDIT :

If you still want to keep your approach here is the order of operation

1 - Download the data

2 - Create de database and the tables empty

3 - Fill the tables

4 - Get the data you need in a simple cache (List, HashMap, ArrayList, ...)

5 - Display/use those data at the moment you need it if it is available

NOTE THAT :

1 - Downloading should be done in another thread (Thread, Asynctask, service ...)

2 - Operations on the database should generally also be done in another thread too

Answer 4

createDataBase() method should be called inside onCreate() method of XMLtoSQLite class