Aswin Balaji

Aswin Balaji

186 Sadasivam Nagar,Madipakkam,Chennai, TN 600091
Aswin Balaji

SQlite Database in android

September 17, 2015, by Aswin Balaji, category ANDROID
SQlite Database in android handle the more than one table in database

Basically It's included the create database and
upgrade,insert, delete,get all
data from table in the database
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;

import java.util.ArrayList;
import java.util.List;

/** * Created by Windows8 on 12-Sep-15. */public class DatabaseHandler extends SQLiteOpenHelper {

    // Database Version    private static final int DATABASE_VERSION = 3;

    // Database Name    private static final String DATABASE_NAME = "Scoreboard";

    // Three table name
 private static final String TABLE_LEVELS = "level";
    private static final String TABLE_SCORE = "score";
    private static final String TABLE_LEVEL_PUZZLES = "puzzle";

    // Labels first table
 private static final String KEY_ID = "id";
    public static final String KEY_SCORE = "Time";
    //second table    private static final String LEVEL_ID = "id";
    public static final String KEY_LEVEL = "Level";

    //third table    private static final String PUZZLE_ID = "id";
    public static final String  PUZZLE_NAME = "name";
    public static final String PUZZLE_LEVEL = "Puzzle";


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

    @Override    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        // Category table create query
 String CREATE_LEVEL_TABLE = "CREATE TABLE " + TABLE_LEVELS + "("
 + LEVEL_ID + " INTEGER PRIMARY KEY," + KEY_LEVEL + " TEXT" + ")";
        String CREATE_TIME_TABLE = "CREATE TABLE " + TABLE_SCORE + "("
 + KEY_ID + " INTEGER PRIMARY KEY," + KEY_SCORE + " TEXT" + ")";
        String CREATE_PUZZLE_TABLE = "CREATE TABLE " + TABLE_LEVEL_PUZZLES + "("
 + PUZZLE_ID + " INTEGER PRIMARY KEY," + PUZZLE_NAME + " TEXT," + PUZZLE_LEVEL + " TEXT" + ")";
        sqLiteDatabase.execSQL(CREATE_LEVEL_TABLE);
        sqLiteDatabase.execSQL(CREATE_TIME_TABLE);
        sqLiteDatabase.execSQL(CREATE_PUZZLE_TABLE);
    }

    @Override    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int j) {

        // Drop older table if existed
 sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_LEVELS);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_SCORE);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_LEVEL_PUZZLES);

        // Create tables again        onCreate(sqLiteDatabase);

    }

    /**     * Inserting new lable into lables table
 */    public void insertScore(String label) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();

        values.put(KEY_SCORE, label);

        // Inserting Row        db.insert(TABLE_SCORE, null, values);
        db.close(); // Closing database connection    }

    public void insertlevel(int level) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();

        values.put(KEY_LEVEL, level);

        // Inserting Row        db.insert(TABLE_LEVELS, null, values);
        db.close(); // Closing database connection    }
    //get single Level    public String getlevelcom(int id){
        SQLiteDatabase db=this.getReadableDatabase();
        Cursor cursor = null;
        String Level = "";
        try{

            cursor = db.query(TABLE_LEVELS, new String[] {LEVEL_ID,
                            KEY_LEVEL }, KEY_LEVEL + "=?",
                    new String[] { String.valueOf(id) }, null, null, null, null);
            if(cursor.getCount() > 0) {
                cursor.moveToFirst();
                Level = cursor.getString(cursor.getColumnIndex(KEY_LEVEL));
            }
        }catch (NumberFormatException  e) {
            e.printStackTrace();
            Log.i("Error", String.valueOf(e)+"You start play with easy");
        }finally {

            if (cursor != null) {
                cursor.close();
            }
        }
        return Level;
    }



    public void insertPuzzle(int level,String puzzle){
        SQLiteDatabase db=this.getWritableDatabase();
        ContentValues puzzles=new ContentValues();
        puzzles.put(PUZZLE_NAME,level);
        puzzles.put(PUZZLE_LEVEL,puzzle);
        db.insert(TABLE_LEVEL_PUZZLES,null,puzzles);
        db.close();
    }

    //single contact    PuzzleClassDB getPuzzle(int id){

        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_LEVEL_PUZZLES, new String[] { PUZZLE_ID,
                        PUZZLE_NAME, PUZZLE_LEVEL }, PUZZLE_NAME + "=?",
                new String[] { String.valueOf(id) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        PuzzleClassDB contact= null;
        if (cursor != null) {
            contact = new PuzzleClassDB(Integer.parseInt(cursor.getString(0)),
                    cursor.getString(1),cursor.getString(2));
        }
        return contact;

    }

    public int getPuzzleCount(){

        int count = 0;
        String countQuery = "SELECT  * FROM " + TABLE_LEVEL_PUZZLES;
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();
        if(!cursor.isClosed()){
            count = cursor.getCount();
            cursor.close();
        }
        // return count        return count;

    }
    //getting all puzzles    public List<String> getAllPuzzle() {
        List<String> labels = new ArrayList<String>();

        // Select All Query
 String selectQuery = "SELECT  * FROM " + TABLE_LEVEL_PUZZLES;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list        if (cursor.moveToFirst()) {
            do {
                labels.add(cursor.getString(cursor.getColumnIndex(PUZZLE_LEVEL)));
            } while (cursor.moveToNext());
        }

        // closing connection        cursor.close();
        db.close();

        // returning lables        return labels;
    }

    public void deletePuzzle() {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_LEVEL_PUZZLES, null, null);
        db.close();
    }

    /**     * Getting all labels     * returns list of labels     */

    public List<String> getAllLevel() {
        List<String> labels = new ArrayList<String>();

        // Select All Query
 String selectQuery = "SELECT  * FROM " + TABLE_LEVELS;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
 if (cursor.moveToFirst()) {
            do {
                labels.add(String.valueOf(cursor.getInt(cursor.getColumnIndex(KEY_LEVEL))));
            } while (cursor.moveToNext());
        }

        // closing connection        cursor.close();
        db.close();

        // returning lables        return labels;
    }

    public List<String> getAllScore() {
        List<String> labels = new ArrayList<String>();

        // Select All Query
 String selectQuery = "SELECT  * FROM " + TABLE_SCORE;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
 if (cursor.moveToFirst()) {
            do {
                labels.add(cursor.getString(cursor.getColumnIndex(KEY_SCORE)));
            } while (cursor.moveToNext());
        }

        // closing connection        cursor.close();
        db.close();

        // returning lables        return labels;
    }

    public void deleteAll() {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_LEVELS, null, null);
        db.delete(TABLE_SCORE, null, null);
        db.close();
    }

    public Cursor getData()
{
SQLiteDatabase db = this.getReadableDatabase();
 Cursor res =  db.rawQuery( "SELECT  * FROM " + TABLE_SCORE,null );
 return res;    }
}