Android Open Source - Simple-Android-SQL-DB S Q L Helper

• Back to Database/table  ↑

From Project

Back to project page Simple-Android-SQL-DB.

License

The source code is released under:

Apache License

Java Source Code

package io.github.darkprayer93.database;
/*from   w ww  .  j  a va 2  s  . c o  m*/
import java.util.LinkedList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLHelper extends SQLiteOpenHelper {

  // ====================================================================================
  // TODO <!--1 Defining Variables -->
  // ====================================================================================

  // Database Version
  private static final int DATABASE_VERSION = 1;
  // Database Name
  private static final String DATABASE_NAME = "SQLBasicDB";
  // Database Table Name
  private static final String table_name = "SQLBasicTable";
  // Database Table Column Names
  private static final String KEY_ID = "id";
  private static final String KEY_SHORTNAME = "shortNAME";
  private static final String KEY_SHORTURL = "shortURL";

  private static final String[] COLUMNS = {KEY_ID,KEY_SHORTNAME,KEY_SHORTURL};

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

  // ====================================================================================
  // TODO <!--2 OnCreate Table Method -->
  // ====================================================================================
  @Override
  public void onCreate(SQLiteDatabase db) {
    // SQL statement to create table_name table
    String CREATE_SQLBASICDB_TABLE = "CREATE TABLE " + 
        table_name + " ( " +
        "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
        KEY_SHORTNAME + " TEXT, " +
        KEY_SHORTURL + " TEXT )";

    // create table_name table
    db.execSQL(CREATE_SQLBASICDB_TABLE);
  }

  // ====================================================================================
  // TODO <!--3 OnUpgrade Table Method -->
  // ====================================================================================
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // Drop older table_name table if existed
    db.execSQL("DROP TABLE IF EXISTS " + table_name);

    // create fresh table_name table
    this.onCreate(db);
  }

  /**
   * CRUD operations (create "add", read "get", update, delete)
   */


  // ====================================================================================
  // TODO <!--4 addSQLBasicRow Row Method -->
  // ====================================================================================
  public void addSQLBasicRow(SQLBasicRow sqlBasicRow){
    //for logging
    Log.d("addSQLBasicRow", sqlBasicRow.toString());

    // 1. get reference to writable DB
    SQLiteDatabase db = this.getWritableDatabase();

    // 2. create ContentValues to add key "column"/value
    ContentValues values = new ContentValues();
    values.put(KEY_SHORTNAME, sqlBasicRow.getShortNAME()); // get shortName
    values.put(KEY_SHORTURL, sqlBasicRow.getShortURL()); // get shortURL

    // 3. insert
    db.insert(table_name, // table
        null, //nullColumnHack
        values); // key/value -> keys = column names/ values = column values

    // 4. close
    db.close();
  }

  // ====================================================================================
  // TODO <!--5 getSQLBasicRow Row Method -->
  // ====================================================================================
  public SQLBasicRow getSQLBasicRow(int id){

    // 1. get reference to readable DB
    SQLiteDatabase db = this.getReadableDatabase();

    // 2. build query
    Cursor cursor =
        db.query(table_name, // a. table
            COLUMNS, // b. column names
            " id = ?", // c. selections
            new String[] { String.valueOf(id) }, // d. selections args
            null, // e. group by
            null, // f. having
            null, // g. order by
            null); // h. limit

    // 3. if we got results get the first one
    if (cursor != null)
      cursor.moveToFirst();

    // 4. build sqlBasicRow object
    SQLBasicRow sqlBasicRow = new SQLBasicRow();
    sqlBasicRow.setId(Integer.parseInt(cursor.getString(0)));
    sqlBasicRow.setShortNAME(cursor.getString(1));
    sqlBasicRow.setShortURL(cursor.getString(2));

    //log
    Log.d("getSQLBasicRow("+id+")", sqlBasicRow.toString());

    // 5. return sqlBasicRow
    return sqlBasicRow;
  }

  // ====================================================================================
  // TODO <!--6 getAllSQLBasicRows Row Method -->
  // ====================================================================================
  public List<SQLBasicRow> getAllSQLBasicRows() {
    List<SQLBasicRow> sqlBasicRows = new LinkedList<SQLBasicRow>();

    // 1. build the query
    String query = "SELECT  * FROM " + table_name;

    // 2. get reference to writable DB
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(query, null);

    // 3. go over each row, build sqlBasicRow and add it to list
    SQLBasicRow sqlBasicRow = null;
    if (cursor.moveToFirst()) {
      do {
        sqlBasicRow = new SQLBasicRow();
        sqlBasicRow.setId(Integer.parseInt(cursor.getString(0)));
        sqlBasicRow.setShortNAME(cursor.getString(1));
        sqlBasicRow.setShortURL(cursor.getString(2));

        // Add sqlBasicRow to sqlBasicRows
        sqlBasicRows.add(sqlBasicRow);
      } while (cursor.moveToNext());
    }

    Log.d("getAllSQLBasicRows()", sqlBasicRows.toString());

    // return sqlBasicRows
    return sqlBasicRows;
  }

  // ====================================================================================
  // TODO <!--7 updateSQLBasicRow Row Method -->
  // ====================================================================================
  public int updateSQLBasicRow(SQLBasicRow sqlBasicRow) {

    // 1. get reference to writable DB
    SQLiteDatabase db = this.getWritableDatabase();

    // 2. create ContentValues to add key "column"/value
    ContentValues values = new ContentValues();
    values.put("shortNAME", sqlBasicRow.getShortNAME()); // get shortNAME
    values.put("shortURL", sqlBasicRow.getShortURL()); // get shortURL

    // 3. updating row
    int i = db.update(table_name, //table
        values, // column/value
        KEY_ID+" = ?", // selections
        new String[] { String.valueOf(sqlBasicRow.getId()) }); //selection args

    // 4. close
    db.close();

    return i;
  }

  // ====================================================================================
  // TODO <!--8 deleteSQLBasicRow Row Method -->
  // ====================================================================================
  public void deleteSQLBasicRow(SQLBasicRow sqlBasicRow) {

    // 1. get reference to writable DB
    SQLiteDatabase db = this.getWritableDatabase();

    // 2. delete
    db.delete(table_name, //table name
        KEY_ID+" = ?",  // selections
        new String[] { String.valueOf(sqlBasicRow.getId()) }); //selections args

    // 3. close
    db.close();

    //log
    Log.d("deleteSQLBasicRow", sqlBasicRow.toString());

  }




}

Java Source Code List

• Back to Database/table  ↑