SQLite example

pet_records.sql

CREATE TABLE pet (id INTEGER PRIMARY KEY, name TEXT, image TEXT, tag TEXT, weight TEXT, age NUMERIC, heart_rate TEXT, tips TEXT, notes TEXT, vet_number TEXT, medication TEXT);

SQLiteManager.java

package com.cube.arc.lib.manager;

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

import com.cube.arc.model.Pet;
import com.cube.arc.pfa.R;

import java.io.BufferedInputStream;
import java.util.ArrayList;
import java.util.List;

import lombok.Getter;

public class SQLiteManager
{
	@Getter private static SQLiteManager instance;
	public static SQLiteManager getInstance(Context context)
	{
		if (instance == null)
		{
			instance = new SQLiteManager(context);
		}

		return instance;
	}

	@Getter private Context context;
	@Getter private SQLiteHelper sqliteHelper;

	public SQLiteManager(Context context)
	{
		this.context = context.getApplicationContext();
		sqliteHelper = new SQLiteHelper(getContext());
	}

	public long addPetRecord(Pet record)
	{
		SQLiteDatabase database = getSqliteHelper().getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(Pet.DB_KEY_IMAGE, record.getImage());
		values.put(Pet.DB_KEY_NAME, record.getName());
		values.put(Pet.DB_KEY_TAG, record.getTag());
		values.put(Pet.DB_KEY_WEIGHT, record.getWeight());
		values.put(Pet.DB_KEY_AGE, record.getAge());
		values.put(Pet.DB_KEY_HEART_RATE, record.getHeartRate());
		values.put(Pet.DB_KEY_TIPS, record.getTips());
		values.put(Pet.DB_KEY_NOTES, record.getNotes());
		values.put(Pet.DB_KEY_VET_NUMBER, record.getVetNumber());
		values.put(Pet.DB_KEY_MEDICATION, record.getMedicationNotes());

		long petId = database.insert("pet", null, values);

		return petId;
	}

	public long updatePetRecord(Pet record)
	{
		SQLiteDatabase database = getSqliteHelper().getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(Pet.DB_KEY_IMAGE, record.getImage());
		values.put(Pet.DB_KEY_NAME, record.getName());
		values.put(Pet.DB_KEY_TAG, record.getTag());
		values.put(Pet.DB_KEY_WEIGHT, record.getWeight());
		values.put(Pet.DB_KEY_AGE, record.getAge());
		values.put(Pet.DB_KEY_HEART_RATE, record.getHeartRate());
		values.put(Pet.DB_KEY_TIPS, record.getTips());
		values.put(Pet.DB_KEY_NOTES, record.getNotes());
		values.put(Pet.DB_KEY_VET_NUMBER, record.getVetNumber());
		values.put(Pet.DB_KEY_MEDICATION, record.getMedicationNotes());

		return database.update("pet", values, "id = ?", new String[]{String.valueOf(record.getId())});
	}

	public void deletePet(Pet record)
	{
		SQLiteDatabase database = getSqliteHelper().getReadableDatabase();
		database.delete("pet", "id = ?", new String[]{String.valueOf(record.getId())});
	}

	public List<Pet> getPetRecords()
	{
		SQLiteDatabase database = getSqliteHelper().getReadableDatabase();
		String query = "SELECT * FROM pet ORDER BY id DESC";
		Cursor cursor = database.rawQuery(query, null);
		List<Pet> pets = new ArrayList<Pet>(cursor.getCount());

		if (cursor != null)
		{
			while (cursor.moveToNext())
			{
				Pet pet = new Pet();
				pet.setId(cursor.getInt(Pet.DB_COLUMN_ID));
				pet.setImage(cursor.getString(Pet.DB_COLUMN_IMAGE));
				pet.setName(cursor.getString(Pet.DB_COLUMN_NAME));
				pet.setTag(cursor.getString(Pet.DB_COLUMN_TAG));
				pet.setWeight(cursor.getString(Pet.DB_COLUMN_WEIGHT));
				pet.setAge(cursor.getInt(Pet.DB_COLUMN_AGE));
				pet.setHeartRate(cursor.getString(Pet.DB_COLUMN_HEART_RATE));
				pet.setNotes(cursor.getString(Pet.DB_COLUMN_NOTES));
				pet.setVetNumber(cursor.getString(Pet.DB_COLUMN_VET_NUMBER));
				pet.setMedicationNotes(cursor.getString(Pet.DB_COLUMN_MEDICATION));
				pets.add(pet);
			}
		}

		return pets;
	}

	private class SQLiteHelper extends SQLiteOpenHelper
	{
		private static final int DATABASE_FILE = R.raw.pet_records;
		private static final String DATABASE_NAME = "pet_records.db";
		private static final int DATABASE_VERSION = 1;

		@Getter private Context context;

		public SQLiteHelper(Context context)
		{
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			this.context = context;
		}

		@Override public void onCreate(SQLiteDatabase db)
		{
			try
			{
				BufferedInputStream databaseCreatorFile = new BufferedInputStream(getContext().getResources().openRawResource(DATABASE_FILE), 1024);
				StringBuffer stringBuffer = new StringBuffer(1024);
				byte[] buffer = new byte[1024];
				int len;

				while ((len = databaseCreatorFile.read(buffer)) > -1)
				{
					stringBuffer.append(new String(buffer, 0, len));
				}

				databaseCreatorFile.close();
				String lines = stringBuffer.toString();
				String[] commands = lines.split("[\n]");

				for (String command : commands)
				{
					db.execSQL(command);
				}
			}
			catch (Exception e)
			{
				e.printStackTrace();
			}
		}

		@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
		{
		}
	}
}