CRUD functions in SQLite in Android

This article depicts the CRUD(create, read, update, delete) functions on a database table in SQLite. Here I take the example of ‘user’ class which has few properties.

Screenshot_2016-08-31-23-34-41

CRUD in SQLite

First you need to create User class with its constructor and getter-setter methods.

public class User {
  private long id;
  private String firstName;
  private String lastName;
  private int age;

  public User() {
  }

  public long getId() {
    return id;
  }

  public void setId(long id) {
    this.id = id;
  }

  public String getFirstName() {
    return firstName;
  }

  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }

  public String getLastName() {
    return lastName;
  }

  public void setLastName(String lastName) {
    this.lastName = lastName;
  }

  public int getAge() {
    return age;
  }

  public void setAge(int age) {
    this.age = age;
  }
}

Now we will initialize DataBaseHelper class which will perform all the database related operation.

First we initial DatabaseHelper class by extending SQLiteOpenHelper.

public class DatabaseHelperNew extends SQLiteOpenHelper {

  @Override
  public void onCreate(SQLiteDatabase db) {

  }

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

  }

  public DatabaseHelperNew(Context c) {
    super(context, "db_name"/*db name*/, null, 1/*version*/);
  }
}

Now we initialize onCreate and onUpgrade methods with appropriate queries.

public class DatabaseHelperNew extends SQLiteOpenHelper {

  static String TABLE_USER = "user";
  static String KEY_USER_ID = "user_id";
  static String KEY_USER_FIRSTNAME = "user_first_name";
  static String KEY_USER_LASTNAME = "user_last_name";
  static String KEY_USER_AGE = "user_age";

  String CREATE_TABLE_USER = "CREATE TABLE " + TABLE_USER + "(" + KEY_USER_ID + " INTEGER PRIMARY KEY," + KEY_USER_FIRSTNAME + " TEXT," + KEY_USER_LASTNAME + " TEXT," + KEY_USER_AGE + " INTEGER" + ")";

  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_TABLE_USER);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
    onCreate(db);
  }

  public DatabaseHelperNew(Context c) {
    super(context, "db_name"/*db name*/, null, 1/*version*/);
  }
}

Now we will see CRUD functions one by one.

Create

//create user
public long createUser(User u) {
  SQLiteDatabase db = this.getWritableDatabase();
  ContentValues values = new ContentValues();
  values.put(KEY_USER_FIRSTNAME, u.getFirstName());
  values.put(KEY_USER_LASTNAME, u.getLastName());
  values.put(KEY_USER_AGE, u.getAge());
  long id = db.insert(TABLE_USER, null, values);
  db.close();
  return id;
}

Update

//update user
public void updateUser(User u) {
  SQLiteDatabase db = this.getWritableDatabase();
  ContentValues values = new ContentValues();
  values.put(KEY_USER_FIRSTNAME, u.getFirstName());
  values.put(KEY_USER_LASTNAME, u.getLastName());
  values.put(KEY_USER_AGE, u.getAge());
  db.update(TABLE_USER, values, KEY_USER_ID + " = ?", new String[]{String.valueOf(u.getId())});
  db.close();
}

Delete

//delete user
public void deleteUser(User u){
  SQLiteDatabase db = this.getWritableDatabase();
  db.delete(TABLE_USER, KEY_USER_ID + " = ?", new String[]{String.valueOf(u.getId())});
  db.close();
}

Read

//read all users
public ArrayList<User> readUsers(){
  ArrayList<User> users = new ArrayList<>();
  String selectQuery = "SELECT * FROM " + TABLE_USER;
  SQLiteDatabase db = this.getReadableDatabase();
  Cursor c = db.rawQuery(selectQuery, null);

  if (c.moveToFirst()) {
    do {
      User u = new User();
      u.setId(c.getInt((c.getColumnIndex(KEY_USER_ID))));
      u.setFirstName(c.getString(c.getColumnIndex(KEY_USER_FIRSTNAME)));
      u.setLastName(c.getString(c.getColumnIndex(KEY_USER_LASTNAME)));
      u.setAge(c.getInt(c.getColumnIndex(KEY_USER_AGE)));
      users.add(u);
    } while (c.moveToNext());
  }
  db.close();
  return users;
}

//read single user
public User readUser(long id){
  ArrayList<User> users = new ArrayList<>();
  String selectQuery = "SELECT * FROM " + TABLE_USER + " WHERE " + KEY_USER_ID + " = ?";
  SQLiteDatabase db = this.getReadableDatabase();
  Cursor c = db.rawQuery(selectQuery, new String[]{String.valueOf(id)});

  if (c.moveToFirst()) {
    do {
      User u = new User();
      u.setId(c.getInt((c.getColumnIndex(KEY_USER_ID))));
      u.setFirstName(c.getString(c.getColumnIndex(KEY_USER_FIRSTNAME)));
      u.setLastName(c.getString(c.getColumnIndex(KEY_USER_LASTNAME)));
      u.setAge(c.getInt(c.getColumnIndex(KEY_USER_AGE)));
      users.add(u);
    } while (c.moveToNext());
  }
  db.close();
  return users.get(0);
}

Now the complete DatabaseHelper file looks like below.

public class DatabaseHelper extends SQLiteOpenHelper {

  static String TABLE_USER = "user";
  static String KEY_USER_ID = "user_id";
  static String KEY_USER_FIRSTNAME = "user_first_name";
  static String KEY_USER_LASTNAME = "user_last_name";
  static String KEY_USER_AGE = "user_age";

  String CREATE_TABLE_USER = "CREATE TABLE " + TABLE_USER + "(" + KEY_USER_ID + " INTEGER PRIMARY KEY," + KEY_USER_FIRSTNAME + " TEXT," + KEY_USER_LASTNAME + " TEXT," + KEY_USER_AGE + " INTEGER" + ")";

  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_TABLE_USER);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
    onCreate(db);
  }

  public DatabaseHelper(Context c) {
    super(c, "db_name"/*db name*/, null, 1/*version*/);
  }

  //create user
  public long createUser(User u) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_USER_FIRSTNAME, u.getFirstName());
    values.put(KEY_USER_LASTNAME, u.getLastName());
    values.put(KEY_USER_AGE, u.getAge());
    long id = db.insert(TABLE_USER, null, values);
    db.close();
    return id;
  }

  //update user
  public void updateUser(User u) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_USER_FIRSTNAME, u.getFirstName());
    values.put(KEY_USER_LASTNAME, u.getLastName());
    values.put(KEY_USER_AGE, u.getAge());
    db.update(TABLE_USER, values, KEY_USER_ID + " = ?", new String[]{String.valueOf(u.getId())});
    db.close();
  }

  //delete user
  public void deleteUser(User u){
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_USER, KEY_USER_ID + " = ?", new String[]{String.valueOf(u.getId())});
    db.close();
  }

  //read all users
  public ArrayList<User> readUsers(){
    ArrayList<User> users = new ArrayList<>();
    String selectQuery = "SELECT * FROM " + TABLE_USER;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, null);

    if (c.moveToFirst()) {
      do {
        User u = new User();
        u.setId(c.getInt((c.getColumnIndex(KEY_USER_ID))));
        u.setFirstName(c.getString(c.getColumnIndex(KEY_USER_FIRSTNAME)));
        u.setLastName(c.getString(c.getColumnIndex(KEY_USER_LASTNAME)));
        u.setAge(c.getInt(c.getColumnIndex(KEY_USER_AGE)));
        users.add(u);
      } while (c.moveToNext());
    }
    db.close();
    return users;
  }

  //read single user
  public User readUser(long id){
    ArrayList<User> users = new ArrayList<>();
    String selectQuery = "SELECT * FROM " + TABLE_USER + " WHERE " + KEY_USER_ID + " = ?";
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, new String[]{String.valueOf(id)});

    if (c.moveToFirst()) {
      do {
        User u = new User();
        u.setId(c.getInt((c.getColumnIndex(KEY_USER_ID))));
        u.setFirstName(c.getString(c.getColumnIndex(KEY_USER_FIRSTNAME)));
        u.setLastName(c.getString(c.getColumnIndex(KEY_USER_LASTNAME)));
        u.setAge(c.getInt(c.getColumnIndex(KEY_USER_AGE)));
        users.add(u);
      } while (c.moveToNext());
    }
    db.close();
    return users.get(0);
  }

  //user class
  public class User {
    private long id;
    private String firstName;
    private String lastName;
    private int age;

    public User() {
    }

    public long getId() {
      return id;
    }

    public void setId(long id) {
      this.id = id;
    }

    public String getFirstName() {
      return firstName;
    }

    public void setFirstName(String firstName) {
      this.firstName = firstName;
    }

    public String getLastName() {
      return lastName;
    }

    public void setLastName(String lastName) {
      this.lastName = lastName;
    }

    public int getAge() {
      return age;
    }

    public void setAge(int age) {
      this.age = age;
    }
  }
}

Now we will take a look at how to use above class to perform various database operation.

DatabaseHelper dh;
User u;

dh = new DatabaseHelper(MainActivity.this);
u = dh.new User();

u.setFirstName("John");
u.setLastName("Doe");
u.setAge(25);

//create user
long id = dh.createUser(u);

u.setId(id);
//update user
u.setAge(30);
dh.updateUser(u);

//read all users
ArrayList<User> alUser = dh.readUsers();

//read single user
User u1 = dh.readUser(u.getId());

//delete user
dh.deleteUser(u);

Download Source Code: https://github.com/dakshbhatt21/a-computer-engineer

Draw line on Canvas in Android

You can draw different type of line on Canvas in Android. You can change it’s color, stroke, effect, etc. Here we will see the basics of drawing line on Canvas.

Checkout the basics of Canvas if you are new to Canvas: Draw Canvas on ImageView

Here is the example of different parameters you can apply to draw a line on Canvas in Android.

device-2016-08-23-003351

Draw line on Canvas

//simple line
Paint p1 = new Paint();
p1.setAntiAlias(true);
p1.setColor(Color.BLACK);
p1.setStrokeWidth(10);
canvas.drawLine(10, 40, 400, 40, p1);

//line with color
Paint p2 = new Paint();
p2.setAntiAlias(true);
p2.setColor(Color.RED);
p2.setStrokeWidth(10);
canvas.drawLine(10, 80, 400, 80, p2);

//line with round ends
Paint p3 = new Paint();
p3.setAntiAlias(true);
p3.setColor(Color.BLUE);
p3.setStrokeWidth(10);
p3.setStrokeCap(Paint.Cap.ROUND);
canvas.drawLine(10, 120, 400, 120, p3);

//slanted line
Paint p4 = new Paint();
p4.setAntiAlias(true);
p4.setColor(Color.GREEN);
p4.setStrokeWidth(10);
canvas.drawLine(10, 160, 400, 200, p4);

Here we use AntiAlias flag to true to remove distortion in the lines.

Use Custom font in title in toolbar(action bar) in Android

device-2016-08-21-191040

toolbar with custom font

When we use different font in our Android app, it is necessary to use the same font throughout the application. So you have to change the font in the toolbar(action bar).

For that you have to create TextView and set the TypeFace(font) of your choice to that TextView and set it to the action bar as custom view. Checkout the below code for the same.

TextView tv = new TextView(getApplicationContext());
RelativeLayout.LayoutParams lp = new RelativeLayout.LayoutParams(ActionBar.LayoutParams.WRAP_CONTENT, ActionBar.LayoutParams.WRAP_CONTENT);
tv.setLayoutParams(lp);
tv.setText("Welcome!);
tv.setTextSize(20);
tv.setTextColor(Color.parseColor("#FFFFFF"));
Typeface tf = Typeface.createFromAsset(getAssets(), "Asap-Medium.otf");
tv.setTypeface(tf);
getSupportActionBar().setDisplayOptions(ActionBar.DISPLAY_SHOW_CUSTOM);
getSupportActionBar().setCustomView(tv);

Add border to RelativeLayout(or any view) in Android

Screen Shot 2016-08-18 at 3.56.16 PM

relativelayout with border

 

To add border to your RelativeLayout, first you need to create a xml resource file in your drawable folder.

Then add the following code to that xml file.

<?xml version="1.0" encoding="utf-8"?>
<shape
  xmlns:android="http://schemas.android.com/apk/res/android"
  android:shape="rectangle">

  <stroke
    android:width="5dp"
    android:color="#757575” /> 
</shape> 

The above code will create black border with 5dp thickness. You can change thickness using ‘width’ parameter and color using ‘color’ parameter in above code.

Sample code to apply above border to a RelativeLayout. Here we assume that the name of above file is border.xml in drawable folder.

<RelativeLayout
  android:layout_width="match_parent"
  android:layout_height="match_parent"
  android:layout_margin="25dp"
  android:background="@drawable/border">
</RelativeLayout>

Extract colors from image(bitmap) using Palette in Android

When open the profile page of any person or group in Whatsapp, you will find that the color of the toolbar at top uses the color from the DP of the user or group. It looks good when you use extracted colors from image in your layout. Your layout will blend with the image and other content.

So here we can extract different colors from any image(bitmap) used in our application and use those colors effectively in our app throughout.

Screenshot_1468609451

Main Color Profiles

First of all there are main 6 type of color profiles in the image that we can extract.

  • Vibrant
  • Vibrant Dark
  • Vibrant Light
  • Muted
  • Muted Dark
  • Muted Light

Most used profile from above are Vibrant and Vibrant Dark. The reason is, vibrant colors are pure, bright, intense, saturated and high chroma color. They stand out in the image and easy to point out. Now if we use those colors in the layout along with the image, it will give a great look. Whatsapp is the best example of it as we discussed earlier.

Every profile(swatch) gives us following information that we can use in the application.

  • population of that color in image
  • hue, saturation, lightness
  • body text color with sufficient contrast with the main color
  • title text color with sufficient contrast with the main color

Now we look at the code to get the Palette from bitmap.

Bitmap b = null;
b = BitmapFactory.decodeResource(getResources(), R.drawable.img1);
Palette p = Palette.from(b).generate();

Here we get the Palette from image(bitmap). Now we extract main 6 different kind of color profile(swatch) from it.

Palette.Swatch psVibrant = p.getVibrantSwatch();
Palette.Swatch psVibrantLight = p.getLightVibrantSwatch();
Palette.Swatch psVibrantDark = p.getDarkVibrantSwatch();
Palette.Swatch psMuted = p.getMutedSwatch();
Palette.Swatch psMutedLight = p.getLightMutedSwatch();
Palette.Swatch psMutedDark = p.getDarkMutedSwatch();

Now we will extract 4 different values from these swatch as described earlier in the article. Here we will take psVibrant as an example.

int color = psVibrant.getRgb();
int population = psVibrant.getPopulation();
float[] hsl = psVibrant.getHsl();
int bodyTextColor = psVibrant.getBodyTextColor();
int titleTextColor = psVibrant.getTitleTextColor();

You can also get other colors from image other than above 6 by following code snippet.

List&lt;Palette.Swatch&gt; pss;
pss = p.getSwatches();
for(int j = 0; j &lt; pss.size(); j++) {
  Palette.Swatch ps = pss.get(j);
  int color = ps.getRgb();
  int population = ps.getPopulation();
  float[] hsl = ps.getHsl();
  int bodyTextColor = ps.getBodyTextColor();
  int titleTextColor = ps.getTitleTextColor();
}
Screenshot_1468609457

Other Color Profiles

Various file operations(create, rename, delete, list files) in Android

You can perform many file operation in Android. I described few of them here.

  • Create directory
  • Create directory and sub-directories in one instance
  • Rename directory
  • Delete directory
  • List the contain of that directory

The code for the first one, create a single directory at the root of your external storage directory.

File dirMain = new File(extdir + File.separator + "com.acomputerengineer");
if(!dirMain.exists())   {
  boolean mkdir = dirMain.mkdir();
  if(mkdir)   {
    Log.e("test", "Directory " + dirMain.getPath() + " created successfully");
  }
  else if(!mkdir) {
    Log.e("test", "Directory " + dirMain.getPath() + " not created");
  }
}

Result:

Screenshot of directory created

directory created

 

Now creating directory and sub-directories at the same time.

//example 1
File dirWithSubdir1 = new File(extdir + File.separator + "com.acomputerengineer" + File.separator + "testDir1" + File.separator + "testSubdir1");
if(!dirWithSubdir1.exists()) {
  dirWithSubdir1.mkdirs();
}

//example 2
File dirWithSubdir2 = new File(extdir + File.separator + "com.acomputerengineer" + File.separator + "testDir2" + File.separator + "testSubdir2" + File.separator + "testSubSubdir2");
if(!dirWithSubdir2.exists()) {
  dirWithSubdir2.mkdirs();
}

Result:

Screenshot of subdirectory created

subdirectory created

 

Now rename the directory ‘dirWithSubdir1’ from the above example.

File renameDir = new File(extdir + File.separator + "com.acomputerengineer" + File.separator + "newTestDir1");
dirWithSubdir1.renameTo(renameDir);

Result:

Screenshot of rename directory

rename directory

 

Now deleting the directory.

dirWithSubdir1.delete();

Result:

Screenshot of delete directory

delete directory

 

Now listing the content of the directory by logging them into logcat.

String[] dirMainFiles = dirMain.list();
for (int i = 0; i < dirMainFiles.length; i++)   {
  Log.e("test", "file " + i + " :" + dirMainFiles[i]);
}

Result:

Screenshot of list content of directory

list content of directory

 

Screen Shot of list content of directory

list content of directory

Convert pixel to dp or dip and dp to pixel in Android

If we need to set dimension of any view from the java file, it will use pixel as default unit. Where as in XML file we use dp or dip(density independent pixel). So 100 dp is not equal to 100 pixel.

Screenshot_20160623-151106

200dp = 525pixel in this case

Android uses density factor to provide support to different devices with different screensize. So the conversion works as explained below.

First we will check the conversion of dp to pixel. Here dp is density independent and pixel is dependent on density. So if we need to find pixel from give dp, we need to multiply it with the density of the device.

So the equation will be like this

pixel = dp * (density of the device);

Here is the function to convert dp to pixel.

public static float dpTopixel(Context c, float dp) {
  float density = c.getResources().getDisplayMetrics().density;
  float pixel = dp * density;
  return pixel;
}

Now we will convert pixel to dp. Here we need to divide the pixel with the density of the device so the result will become independent of density(dp).

So the equation will be like this

dp = pixel / (density of the device);

Here is the function to convert pixel to dp.

public static float pixelTodp(Context c, float pixel) {
  float density = c.getResources().getDisplayMetrics().density;
  float dp = pixel / density;
  return dp;
}

Now if you want to use this value as width or height in any of your view’s LayoutParams, it will ask you for Integer(int) value and above functions will return you Float(float) value. So you have to do following thing to convert it to Integer(int).

int width = (int) dpTopixel(MainActivity.this, 200);

Please comment if you find any mistake or want to give feedback.