SQLite in Android

a more easy way to use SQL in Android:

sugar orm

http://satyan.github.io/sugar/index.html

First we need to create a contract

contract is what column should we have in the database, use to define the constant in the database

CREATE TABLE table_name(

   column1 INTEGER AUTOINCREMENT,   must have

   column2 datatype,

   column3 datatype,

   .....

   columnN datatype,

);

Concept:

Foreign Keys 
Inner Join

Example of contract:

/**
 * Defines table and column names for the weather database.
 */
public class WeatherContract {

    // To make it easy to query for the exact date, we normalize all dates that go into
    // the database to the start of the the Julian day at UTC.
    public static long normalizeDate(long startDate) {
        // normalize the start date to the beginning of the (UTC) day
        Time time = new Time();
        time.set(startDate);
        int julianDay = Time.getJulianDay(startDate, time.gmtoff);
        return time.setJulianDay(julianDay);
    }

    /*
        Inner class that defines the table contents of the location table
        Students: This is where you will add the strings.  (Similar to what has been
        done for WeatherEntry)
     */
    public static final class LocationEntry implements BaseColumns {
        public static final String TABLE_NAME = "location";

        //store the seeting of location
        public static final String COLUMN_LOC_SET = "location_set";

        //store the city name, stored as string
        public static final String COLUMN_CITY_NAME = "city_name";

        //store as float
        public static final String COLUMN_COORD_LAT = "coord_lat";
        public static final String COLUMN_COORD_LONG = "coord_long";


    }

    /* Inner class that defines the table contents of the weather table */
    public static final class WeatherEntry implements BaseColumns {

        public static final String TABLE_NAME = "weather";

        // Column with the foreign key into the location table.
        public static final String COLUMN_LOC_KEY = "location_id";
        // Date, stored as long in milliseconds since the epoch
        public static final String COLUMN_DATE = "date";
        // Weather id as returned by API, to identify the icon to be used
        public static final String COLUMN_WEATHER_ID = "weather_id";

        // Short description and long description of the weather, as provided by API.
        // e.g "clear" vs "sky is clear".
        public static final String COLUMN_SHORT_DESC = "short_desc";

        // Min and max temperatures for the day (stored as floats)
        public static final String COLUMN_MIN_TEMP = "min";
        public static final String COLUMN_MAX_TEMP = "max";

        // Humidity is stored as a float representing percentage
        public static final String COLUMN_HUMIDITY = "humidity";

        // Humidity is stored as a float representing percentage
        public static final String COLUMN_PRESSURE = "pressure";

        // Windspeed is stored as a float representing windspeed  mph
        public static final String COLUMN_WIND_SPEED = "wind";

        // Degrees are meteorological degrees (e.g, 0 is north, 180 is south).  Stored as floats.
        public static final String COLUMN_DEGREES = "degrees";
    }
}

Seond,create a SQLiteOpenHelper

Use to help to create and modify the SQLite database

Example:

public class WeatherDbHelper extends SQLiteOpenHelper {

    // If you change the database schema, you must increment the database version.
    private static final int DATABASE_VERSION = 2;

    static final String DATABASE_NAME = "weather.db";

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

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        final String SQL_CREATE_WEATHER_TABLE = "CREATE TABLE " + WeatherEntry.TABLE_NAME + " (" +
                // Why AutoIncrement here, and not above?
                // Unique keys will be auto-generated in either case.  But for weather
                // forecasting, it's reasonable to assume the user will want information
                // for a certain date and all dates *following*, so the forecast data
                // should be sorted accordingly.
                WeatherEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +

                // the ID of the location entry associated with this weather data
                WeatherEntry.COLUMN_LOC_KEY + " INTEGER NOT NULL, " +
                WeatherEntry.COLUMN_DATE + " INTEGER NOT NULL, " +
                WeatherEntry.COLUMN_SHORT_DESC + " TEXT NOT NULL, " +
                WeatherEntry.COLUMN_WEATHER_ID + " INTEGER NOT NULL," +

                WeatherEntry.COLUMN_MIN_TEMP + " REAL NOT NULL, " +
                WeatherEntry.COLUMN_MAX_TEMP + " REAL NOT NULL, " +

                WeatherEntry.COLUMN_HUMIDITY + " REAL NOT NULL, " +
                WeatherEntry.COLUMN_PRESSURE + " REAL NOT NULL, " +
                WeatherEntry.COLUMN_WIND_SPEED + " REAL NOT NULL, " +
                WeatherEntry.COLUMN_DEGREES + " REAL NOT NULL, " +

                // Set up the location column as a foreign key to location table.
                " FOREIGN KEY (" + WeatherEntry.COLUMN_LOC_KEY + ") REFERENCES " +
                LocationEntry.TABLE_NAME + " (" + LocationEntry._ID + "), " +

                // To assure the application have just one weather entry per day
                // per location, it's created a UNIQUE constraint with REPLACE strategy
                " UNIQUE (" + WeatherEntry.COLUMN_DATE + ", " +
                WeatherEntry.COLUMN_LOC_KEY + ") ON CONFLICT REPLACE);";

        sqLiteDatabase.execSQL(SQL_CREATE_WEATHER_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        // Note that this only fires if you change the version number for your database.
        // It does NOT depend on the version number for your application.
        // If you want to update the schema without wiping data, commenting out the next 2 lines
        // should be your top priority before modifying this method.
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + LocationEntry.TABLE_NAME);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + WeatherEntry.TABLE_NAME);
        onCreate(sqLiteDatabase);
    }
}

Thied How to read and query from sql

First ,get reference to writable/readable database:

SQLiteDatabase db = new WeatherDbHelper(

        this.mContext).getWritableDatabase();

Second, create content value of what want to insert:

ContentValues weatherValues = new ContentValues();
weatherValues.put(WeatherContract.WeatherEntry.COLUMN_LOC_KEY, locationRowId);
weatherValues.put(WeatherContract.WeatherEntry.COLUMN_DATE, TEST_DATE);
weatherValues.put(WeatherContract.WeatherEntry.COLUMN_DEGREES, 1.1);
weatherValues.put(WeatherContract.WeatherEntry.COLUMN_HUMIDITY, 1.2);
weatherValues.put(WeatherContract.WeatherEntry.COLUMN_PRESSURE, 1.3);
weatherValues.put(WeatherContract.WeatherEntry.COLUMN_MAX_TEMP, 75);
weatherValues.put(WeatherContract.WeatherEntry.COLUMN_MIN_TEMP, 65);
weatherValues.put(WeatherContract.WeatherEntry.COLUMN_SHORT_DESC, "Asteroids");
weatherValues.put(WeatherContract.WeatherEntry.COLUMN_WIND_SPEED, 5.5);
weatherValues.put(WeatherContract.WeatherEntry.COLUMN_WEATHER_ID, 321);

Third,insert:

ContentValues cv=new ContentValues();
cv.put(WeatherContract.LocationEntry.COLUMN_CITY_NAME,"North Pole");
cv.put(WeatherContract.LocationEntry.COLUMN_COORD_LAT,64.7488);
cv.put(WeatherContract.LocationEntry.COLUMN_COORD_LONG,-147.353);
cv.put(WeatherContract.LocationEntry.COLUMN_LOCATION_SETTING,"99705");


// Insert ContentValues into database and get a row ID back
long r_id = sqlb.insert(WeatherContract.LocationEntry.TABLE_NAME, null, cv);

Fourth,query:

Delete 
Update

Fifth,close the cursor and database:

cursor.close();

db.close();