SQL Demo

package org.example.sqldemo;

import static android.provider.BaseColumns._ID;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.widget.TextView;

public class SQLDemo extends Activity {
  EventDataSQLHelper eventsData;
  TextView output;

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

    output = (TextView) findViewById(R.id.output);

    eventsData = new EventDataSQLHelper(this);
    addEvent("Hello Android Event");
    Cursor cursor = getEvents();
    showEvents(cursor);
  }
  
  @Override
  public void onDestroy() {
    eventsData.close();
  }

  private void addEvent(String title) {
    SQLiteDatabase db = eventsData.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(EventDataSQLHelper.TIME, System.currentTimeMillis());
    values.put(EventDataSQLHelper.TITLE, title);
    db.insert(EventDataSQLHelper.TABLE, null, values);

  }

  private Cursor getEvents() {
    SQLiteDatabase db = eventsData.getReadableDatabase();
    Cursor cursor = db.query(EventDataSQLHelper.TABLE, null, null, null, null,
        null, null);
    
    startManagingCursor(cursor);
    return cursor;
  }

  private void showEvents(Cursor cursor) {
    StringBuilder ret = new StringBuilder("Saved Events:\n\n");
    while (cursor.moveToNext()) {
      long id = cursor.getLong(0);
      long time = cursor.getLong(1);
      String title = cursor.getString(2);
      ret.append(id + ": " + time + ": " + title + "\n");
    }
    output.setText(ret);
  }
}

package org.example.sqldemo;

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

/** Helper to the database, manages versions and creation */
public class EventDataSQLHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "events.db";
	private static final int DATABASE_VERSION = 1;

	// Table name
	public static final String TABLE = "events";

	// Columns
	public static final String TIME = "time";
	public static final String TITLE = "title";

	public EventDataSQLHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String sql = "create table " + TABLE + "( " + BaseColumns._ID
				+ " integer primary key autoincrement, " + TIME + " integer, "
				+ TITLE + " text not null);";
		Log.d("EventsData", "onCreate: " + sql);
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion >= newVersion)
			return;

		String sql = null;
		if (oldVersion == 1) 
			sql = "alter table " + TABLE + " add note text;";
		if (oldVersion == 2)
			sql = "";

		Log.d("EventsData", "onUpgrade	: " + sql);
		if (sql != null)
			db.execSQL(sql);
	}

}

package com.tg.zdelDB01a;

import android.app.Activity;
import android.os.Bundle;
import static android.provider.BaseColumns._ID;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.widget.TextView;

public class ZdelDB01aActivity extends Activity {
	SQLHelper eventsData;
	TextView output;

    /** Called when the activity is first created. */
	  @Override
	  public void onCreate(Bundle savedInstanceState) {
	    super.onCreate(savedInstanceState);
	    setContentView(R.layout.main);

	    output = (TextView) findViewById(R.id.output);

	    eventsData = new SQLHelper(this);
	    addEvent("Hello Android Event");
	    String zdelTempStr01 = "VN_"+(String.valueOf(System.currentTimeMillis())).substring(String.valueOf(System.currentTimeMillis()).length()-4);
	    addVenue(zdelTempStr01);
	    Cursor cursor = getEvents();
	    showEvents(cursor);
	    
	    
	  }
	  
	  @Override
	  public void onDestroy() {
	    eventsData.close();
	  }

	  private void addEvent(String title) {
		    SQLiteDatabase db = eventsData.getWritableDatabase();
		    ContentValues values = new ContentValues();
		    values.put(SQLHelper.TIME, System.currentTimeMillis());
		    values.put(SQLHelper.TITLE, title);
		    db.insert(SQLHelper.TABLE01, null, values);

	  }
	  
	  private void addVenue(String venue) {
			    SQLiteDatabase db = eventsData.getWritableDatabase();
			    ContentValues values = new ContentValues();
			    values.put(SQLHelper.VENUE, venue);
			    db.insert(SQLHelper.TABLE02, null, values);
	  }

	  private Cursor getEvents() {
	    SQLiteDatabase db = eventsData.getReadableDatabase();
	    
	    Cursor cursor = db.rawQuery("SELECT events._id, events.time, events.title, venues.venue FROM events LEFT JOIN venues ON events.[_id] = venues.[_id]", null);
	    
	    startManagingCursor(cursor);
	    return cursor;
	  }

	  private void showEvents(Cursor cursor) {
	    StringBuilder ret = new StringBuilder("Saved Events:\n\n");
	    while (cursor.moveToNext()) {
	      long id = cursor.getLong(0);
	      long time = cursor.getLong(1);
	      String title = cursor.getString(2);
	      String venue = cursor.getString(3);
	      ret.append(id + ": " + time + ": " + title + " AT " + venue + "\n");
	    }
	    output.setText(ret);
	  }
}

package com.tg.zdelDB01a;


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

/** Helper to the database, manages versions and creation */
public class SQLHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "events.db";
	private static final int DATABASE_VERSION = 1;

	// Table name
	public static final String TABLE01 = "events";
	public static final String TABLE02 = "venues";

	// Columns
	public static final String TIME = "time";
	public static final String TITLE = "title";
	public static final String VENUE = "venue";

	String sql01 = "create table " + TABLE01 + "( " + BaseColumns._ID
			+ " integer primary key autoincrement, " + TIME + " integer, "
			+ TITLE + " text not null);";	
	String sql02 = "create table " + TABLE02 + "( " + BaseColumns._ID
			+ " integer primary key autoincrement, "
			+ VENUE + " text not null);";
	
	
	public SQLHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.d("EventsData", "onCreate: SQL01 " + sql01);
		Log.d("EventsData", "onCreate: SQL02 " + sql02);
		db.execSQL(sql01);		
		db.execSQL(sql02);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion >= newVersion)
			return;

		String sql = null;
		if (oldVersion == 1) 
			sql = "alter table " + TABLE01 + " add note text;";
		if (oldVersion == 2)
			sql = "";

		Log.d("EventsData", "onUpgrade	: " + sql);
		if (sql != null)
			db.execSQL(sql);
	}

}