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

Advertisements

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

How to delete folder with contents in it in PHP

Many times in PHP, we need to delete the folder containing subfolders and files. So we have to check for depth and according to that we have to delete files first and then folders. But if we don’t know the content of it then it will be very difficult task to delete it. I am working on some stuff that need to be delete once it is utilized so I make the function that check for the subfolders and files and recursively delete the contents of it.

Here is the function for delete.

function delFolder($folder) 
{ 
	foreach(glob($folder . '/*') as $file) 
	{ 
		if(is_dir($file)) 
			delFolder($file); 
		else 
			unlink($file); 
	} 
	rmdir($folder); 
}

Here delFolder is the function that perform delete task.

Here we use following of the PHP function.

  • glob — Find pathnames matching a pattern
  • is_dir — Tells whether the filename is a directory
  • unlink — Deletes a file
  • rmdir — Removes directory

Download the full example with comments here : https://www.mediafire.com/view/winyqdt2fr1jsll/delFolder.php