How To Store Html Files Directory in SQL Database?

Question

I have few html files placed in the assets folder and i have their directory in the java file but i want to store the directorys into the SQLite database, with "pos" as the primary key. Then i'll need only a single line of code, i.e. something like

web.loadUrl(sqlDb.load("select url FROM mytable where pos = " + pos)); to open the html files

but i dont know

1. how to create an SQL database in eclipse
2. how to refer my activity to the database
3. how to store the directories in the sql database.

below is my code

import android.app.Activity;
import android.graphics.Bitmap;
import android.os.Bundle;
import android.webkit.WebView;
import android.webkit.WebViewClient;


public class WebViewActivity extends Activity {
WebView web;
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_web_view);
    web = (WebView) findViewById(R.id.webView1);
    web.setWebViewClient(new myWebClient());
    web.getSettings().setJavaScriptEnabled(true);
    int pos = getIntent().getIntExtra("key",0);
 if(pos==0){    web.loadUrl("file:///android_asset/1.html");
else if(pos==1){    web.loadUrl("file:///android_asset/2.html");}   
else if(pos==2){    web.loadUrl("file:///android_asset/3.html");}       
else if(pos==3){    web.loadUrl("file:///android_asset/4.html");}        
else if(pos==4){    web.loadUrl("file:///android_asset/5.html");}

  // similarly for 4 and 5 and so on.
}

public class myWebClient extends WebViewClient
{
    @Override
    public void onPageStarted(WebView view, String url, Bitmap favicon) {
        // TODO Auto-generated method stub
        super.onPageStarted(view, url, favicon);
    }

    @Override
    public boolean shouldOverrideUrlLoading(WebView view, String url) {
        // TODO Auto-generated method stub

        view.loadUrl(url);
        return true;

    }
    @Override
    public void onReceivedError(WebView view, int errorCode,
            String description, String failingUrl) {
    }
    @Override
    public void onPageFinished(WebView view, String url) {
        // TODO Auto-generated method stub
        super.onPageFinished(view, url);

    }
 }
 }

Answer 1

About questions 1 - 2 there is 2 options read the following tutorial1 and tutorial2

About question 3 as I understand you mean to save the path to these files. I think the best way is to store these pathes in one of your tables as SQLite string.

Hope that this is what you need.

Answer 2

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper{

    // Database Version
    private static final int VERSION = 1;

    // Database Name
    private static final String NAME = "MyDatabase.db";

    // SqliteDatabase
    SQLiteDatabase db = null;

    public DatabaseHelper(Context context) {
        super(context, NAME, null, VERSION);
    }

    public void open() throws SQLException {
        db = this.getWritableDatabase();
    }

    public void close() {
        db.close();
    }

    //table names
    public static final String TABLE_URLS   = "TABLE_URLS";


    // table schema
    private static final String SCHEMA_TABLE_URLS    = "CREATE TABLE "+ TABLE_URLS    +" (POS INTEGER PRIMARY KEY, URL TEXT);";
        @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SCHEMA_TABLE_URLS);
        System.out.println("database created.");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(SCHEMA_TABLE_URLS);
        System.out.println("database is updated");
    }

    public long insertURL(ContentValues values){
        return db.insert(TABLE_URLS, null, values);
    }

    public Cursor getURL(String pos){
        return db.query(TABLE_URLS, null, "POS =?", new String[]{pos}, null, null, null);
    }
}

MainActivity.java

public class MainActivity extends Activity {

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

        // create database helper object
        DatabaseHelper helper = new DatabaseHelper(this);
        helper.open();

        // insert url to database
        ContentValues values;

        values = new ContentValues();
        values.put("POS", "1");
        values.put("URL", "file:///android_asset/1.html");
        helper.insertURL(values);


        values = new ContentValues();
        values.put("POS", "2");
        values.put("URL", "file:///android_asset/2.html");
        helper.insertURL(values);

        values = new ContentValues();
        values.put("POS", "3");
        values.put("URL", "file:///android_asset/3.html");
        helper.insertURL(values);

        // so on

        // get url using position
        String pos = "1"; 
        Cursor c = helper.getURL(pos);
        c.moveToFirst();
        String url = c.getString(c.getColumnIndex("URL"));
        System.out.println("URL "+url);
        c.close();

        helper.close();
    }
}