create

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, &quot;db_name&quot;/*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

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

A Computer Engineer

Just another geek.

CRUD functions in SQLite in Android

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