SQLite Database Management

CRUD Operations Using SQL Statements

The SQLiteOpenHelper class manages database creation and version management. When calling getWritableDatabase() or getReadableDatabase(), the system creates the database if it doesn't exist and returns an existing database instance otherwise.

public class DatabaseActivity extends AppCompatActivity {
    private DatabaseConnector dbConnector;
    private TextView resultView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_database);
        
        dbConnector = new DatabaseConnector(getApplicationContext());
        resultView = findViewById(R.id.result_view);
    }

    public void onAddRecord(View view) {
        SQLiteDatabase db = dbConnector.getWritableDatabase();
        db.execSQL("INSERT INTO users(username, score) VALUES(?,?)", 
                   new Object[]{"Alex", 95});
        db.close();
    }

    public void onRemoveRecord(View view) {
        SQLiteDatabase db = dbConnector.getWritableDatabase();
        db.execSQL("DELETE FROM users WHERE username=?", 
                   new Object[]{"Alex"});
        db.close();
    }

    public void onModifyRecord(View view) {
        SQLiteDatabase db = dbConnector.getWritableDatabase();
        db.execSQL("UPDATE users SET score=? WHERE username=?", 
                   new Object[]{88, "Alex"});
        db.close();
    }

    public void onQueryRecords(View view) {
        SQLiteDatabase db = dbConnector.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM users", null);
        StringBuilder builder = new StringBuilder();
        builder.append("[");
        
        if (cursor != null && cursor.getCount() > 0) {
            boolean firstEntry = true;
            while (cursor.moveToNext()) {
                if (!firstEntry) {
                    builder.append(",");
                }
                firstEntry = false;
                builder.append("{id:").append(cursor.getInt(0))
                       .append(",name:").append(cursor.getString(1))
                       .append(",score:").append(cursor.getInt(2)).append("}");
            }
        }
        builder.append("]");
        resultView.setText(builder.toString());
        db.close();
    }
}

public class DatabaseConnector extends SQLiteOpenHelper {
    
    public DatabaseConnector(Context context) {
        super(context, "app_data.db", null, 2);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE users(_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                   "username VARCHAR(50));");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("ALTER TABLE users ADD score INTEGER DEFAULT 0");
    }
}

CRUD Operations Using Android's Built-in APIs

Android provides convenietn wrapper methods that abstract away raw SQL syntax. The ContentValues class wraps key-value pairs for insert and update operations.

public class DatabaseActivity extends AppCompatActivity {
    private DatabaseConnector dbConnector;
    private TextView resultView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_database);
        
        dbConnector = new DatabaseConnector(getApplicationContext());
        resultView = findViewById(R.id.result_view);
    }

    public void onAddRecord(View view) {
        SQLiteDatabase db = dbConnector.getWritableDatabase();
        ContentValues data = new ContentValues();
        data.put("username", "Sarah");
        data.put("score", 78);
        long insertedId = db.insert("users", null, data);
        Log.d("DB_OP", "Inserted record ID: " + insertedId);
        db.close();
    }

    public void onRemoveRecord(View view) {
        SQLiteDatabase db = dbConnector.getWritableDatabase();
        int rowsAffected = db.delete("users", "username=?", 
                                      new String[]{"Sarah"});
        Log.d("DB_OP", "Deleted rows: " + rowsAffected);
        db.close();
    }

    public void onModifyRecord(View view) {
        SQLiteDatabase db = dbConnector.getWritableDatabase();
        ContentValues data = new ContentValues();
        data.put("score", 82);
        int rowsAffected = db.update("users", data, "username=?", 
                                     new String[]{"Sarah"});
        Log.d("DB_OP", "Updated rows: " + rowsAffected);
        db.close();
    }

    public void onQueryRecords(View view) {
        SQLiteDatabase db = dbConnector.getReadableDatabase();
        Cursor cursor = db.query("users", 
                                  new String[]{"_id", "username", "score"},
                                  "username=?", 
                                  new String[]{"Sarah"}, 
                                  null, null, null);
        StringBuilder builder = new StringBuilder();
        builder.append("[");
        
        if (cursor != null && cursor.getCount() > 0) {
            boolean firstEntry = true;
            while (cursor.moveToNext()) {
                if (!firstEntry) {
                    builder.append(",");
                }
                firstEntry = false;
                builder.append("{id:").append(cursor.getInt(0))
                       .append(",name:").append(cursor.getString(1))
                       .append(",score:").append(cursor.getInt(2)).append("}");
            }
        }
        builder.append("]");
        resultView.setText(builder.toString());
        db.close();
    }
}

Database Transactions

Transactions ensure atomic operations where all statements succeed or none take effect. The beginTransaction() method starts a transaction, setTransactionSuccessful() marks the operation as successful, and endTransaction() commits or rolls back based on that flag.

public class DatabaseActivity extends AppCompatActivity {
    private DatabaseConnector dbConnector;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_database);
        dbConnector = new DatabaseConnector(getApplicationContext());
    }

    public void onExecuteBatch(View view) {
        SQLiteDatabase db = dbConnector.getWritableDatabase();
        db.beginTransaction();
        try {
            db.execSQL("UPDATE users SET score=? WHERE username=?", 
                       new Object[]{75, "Alex"});
            int divisionByZero = 10 / 0;
            db.execSQL("UPDATE users SET score=? WHERE username=?", 
                       new Object[]{90, "Sarah"});
            db.setTransactionSuccessful();
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            db.endTransaction();
        }
        db.close();
    }
}

HTTP Network Communication

Basic Request-Response Pattern with UI Updates

Network operations must execute on background threads. The Handler class facilitates communication between background threads and the main UI thread.

<uses-permission android:name="android.permission.INTERNET" />

public class NetworkActivity extends AppCompatActivity {
    private EditText urlInput;
    private TextView contentDisplay;
    private final Handler messageHandler = new Handler(new Handler.Callback() {
        @Override
        public boolean handleMessage(@NonNull Message msg) {
            switch (msg.what) {
                case HttpStatus.SC_OK:
                    String response = (String) msg.obj;
                    contentDisplay.setText(response);
                    break;
                case HttpStatus.SC_BAD_REQUEST:
                    Toast.makeText(getApplicationContext(), 
                                  (String) msg.obj, 
                                  Toast.LENGTH_SHORT).show();
                    break;
            }
            return false;
        }
    });

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_network);
        urlInput = findViewById(R.id.url_input);
        contentDisplay = findViewById(R.id.content_display);
    }

    public void fetchContent(View view) {
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    String address = urlInput.getText().toString().trim();
                    URL endpoint = new URL(address);
                    HttpURLConnection connection = (HttpURLConnection) 
                        endpoint.openConnection();
                    connection.setRequestMethod("GET");
                    connection.setConnectTimeout(5000);
                    
                    int statusCode = connection.getResponseCode();
                    if (statusCode == HttpURLConnection.HTTP_OK) {
                        InputStream stream = connection.getInputStream();
                        String content = StreamUtils.readToString(stream);
                        
                        Message response = Message.obtain();
                        response.what = HttpStatus.SC_OK;
                        response.obj = content;
                        messageHandler.sendMessage(response);
                    }
                } catch (Exception ex) {
                    Message error = Message.obtain();
                    error.what = HttpStatus.SC_BAD_REQUEST;
                    error.obj = ex.getMessage();
                    messageHandler.sendMessage(error);
                }
            }
        }).start();
    }
}

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <EditText
            android:id="@+id/url_input"
            android:layout_width="0dp"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:singleLine="true"
            android:text="https://example.com/api/data" />

        <Button
            android:id="@+id/fetch_button"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:onClick="fetchContent"
            android:text="Fetch" />
    </LinearLayout>

    <ScrollView
        android:layout_width="match_parent"
        android:layout_height="match_parent">

        <TextView
            android:id="@+id/content_display"
            android:layout_width="match_parent"
            android:layout_height="wrap_content" />
    </ScrollView>
</LinearLayout>

public class StreamUtils {
    public static String readToString(InputStream inputStream) throws Exception {
        ByteArrayOutputStream buffer = new ByteArrayOutputStream();
        byte[] data = new byte[1024];
        int bytesRead;
        
        while ((bytesRead = inputStream.read(data)) != -1) {
            buffer.write(data, 0, bytesRead);
        }
        inputStream.close();
        return buffer.toString();
    }
}

Image Loading with Caching Strategy

Implementing a two-tier cache using memory and disk storage improves performance and reduces network bandwidth consumption.

public class ImageActivity extends AppCompatActivity {
    private EditText imageUrlInput;
    private ImageView imagePreview;
    private final Handler imageHandler = new Handler(new Handler.Callback() {
        @Override
        public boolean handleMessage(@NonNull Message msg) {
            Bitmap loadedImage = (Bitmap) msg.obj;
            imagePreview.setImageBitmap(loadedImage);
            return false;
        }
    });

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_image);
        
        imageUrlInput = findViewById(R.id.image_url_input);
        imagePreview = findViewById(R.id.image_preview);
    }

    public void loadImage(View view) {
        new Thread(new Runnable() {
            @Override
            public void run() {
                String remoteUrl = imageUrlInput.getText().toString().trim();
                String cacheKey = UUID.nameUUIDFromBytes(
                    remoteUrl.getBytes()).toString();
                
                File cacheFile = new File(getCacheDir(), cacheKey);
                
                if (cacheFile.exists() && cacheFile.length() > 0) {
                    Log.d("CACHE", "Loading from disk cache");
                    Bitmap cachedBitmap = BitmapFactory.decodeFile(
                        cacheFile.getAbsolutePath());
                    
                    Message response = Message.obtain();
                    response.obj = cachedBitmap;
                    imageHandler.sendMessage(response);
                } else {
                    Log.d("CACHE", "Fetching from network");
                    try {
                        URL url = new URL(remoteUrl);
                        HttpURLConnection connection = (HttpURLConnection) 
                            url.openConnection();
                        connection.setRequestMethod("GET");
                        connection.setConnectTimeout(5000);
                        
                        if (connection.getResponseCode() == 
                            HttpURLConnection.HTTP_OK) {
                            InputStream networkStream = 
                                connection.getInputStream();
                            FileOutputStream fileWriter = 
                                new FileOutputStream(cacheFile);
                            
                            byte[] buffer = new byte[1024];
                            int bytesReceived;
                            while ((bytesReceived = 
                                   networkStream.read(buffer)) != -1) {
                                fileWriter.write(buffer, 0, bytesReceived);
                            }
                            
                            fileWriter.close();
                            networkStream.close();
                            
                            Bitmap downloadedBitmap = BitmapFactory.decodeFile(
                                cacheFile.getAbsolutePath());
                            
                            Message response = Message.obtain();
                            response.obj = downloadedBitmap;
                            imageHandler.sendMessage(response);
                        }
                    } catch (Exception ex) {
                        ex.printStackTrace();
                    }
                }
            }
        }).start();
    }
}

Updating UI with runOnUiThread

The runOnUiThread() method provides a convenient way to post code that modifies UI components from background threads.

public class UiUpdateActivity extends AppCompatActivity {
    private EditText urlField;
    private TextView outputField;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_ui_update);
        
        urlField = findViewById(R.id.url_field);
        outputField = findViewById(R.id.output_field);
    }

    public void downloadData(View view) {
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    String address = urlField.getText().toString().trim();
                    URL endpoint = new URL(address);
                    HttpURLConnection connection = (HttpURLConnection) 
                        endpoint.openConnection();
                    connection.setRequestMethod("GET");
                    connection.setConnectTimeout(5000);
                    
                    int responseCode = connection.getResponseCode();
                    if (responseCode == HttpURLConnection.HTTP_OK) {
                        InputStream stream = connection.getInputStream();
                        ByteArrayOutputStream buffer = 
                            new ByteArrayOutputStream();
                        byte[] chunk = new byte[1024];
                        int bytesRead;
                        
                        while ((bytesRead = stream.read(chunk)) != -1) {
                            buffer.write(chunk, 0, bytesRead);
                        }
                        stream.close();
                        
                        final String content = buffer.toString();
                        runOnUiThread(new Runnable() {
                            @Override
                            public void run() {
                                outputField.setText(content);
                            }
                        });
                    }
                } catch (Exception exception) {
                    exception.printStackTrace();
                }
            }
        }).start();
    }
}

Alternative Message Handling Mechanisms

Android provides several mechanisms for scheduling delayed operations and periodic tasks.

public class TimerActivity extends AppCompatActivity {
    private TextView timestampDisplay;
    private Timer executionTimer;
    private TimerTask recurringTask;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_timer);
        
        timestampDisplay = findViewById(R.id.timestamp_display);
        
        executionTimer = new Timer();
        recurringTask = new TimerTask() {
            @Override
            public void run() {
                runOnUiThread(new Runnable() {
                    @Override
                    public void run() {
                        String formattedTime = new SimpleDateFormat(
                            "yyyy-MM-dd HH:mm:ss").format(new Date());
                        timestampDisplay.setText(formattedTime);
                    }
                });
            }
        };
        
        executionTimer.schedule(recurringTask, 3000, 1000);
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        if (executionTimer != null) {
            executionTimer.cancel();
        }
        if (recurringTask != null) {
            recurringTask.cancel();
        }
    }
}

Multi-threaded Download with Resume Capability

Implementing a download manager that splits files into segments and supports resuming interrupted downloads.

<uses-permission android:name="android.permission.INTERNET" />
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

public class DownloadActivity extends AppCompatActivity {
    private static final String REMOTE_FILE = 
        "https://example.com/downloads/large-file.zip";
    private static final int SEGMENT_COUNT = 3;
    private LinearLayout progressContainer;
    private List<ProgressBar> progressBars;
    private int activeSegments;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_download);
        
        progressContainer = findViewById(R.id.progress_container);
        progressBars = new ArrayList<>();
    }

    private class DownloadSegment extends Thread {
        private final int startPosition;
        private final int endPosition;
        private final int segmentId;
        private final int totalSize;
        private int lastDownloadedPosition;

        public DownloadSegment(int start, int end, int id) {
            this.startPosition = start;
            this.endPosition = end;
            this.segmentId = id;
            this.totalSize = end - start;
        }

        @Override
        public void run() {
            try {
                File checkpointFile = new File(
                    getCacheDir(), "download_" + segmentId + ".chk");
                
                if (checkpointFile.exists() && checkpointFile.length() > 0) {
                    FileInputStream fis = new FileInputStream(checkpointFile);
                    BufferedReader reader = new BufferedReader(
                        new InputStreamReader(fis));
                    int savedPosition = Integer.parseInt(reader.readLine());
                    lastDownloadedPosition = savedPosition - startPosition;
                    startPosition = savedPosition + 1;
                    fis.close();
                }

                URL url = new URL(REMOTE_FILE);
                HttpURLConnection connection = (HttpURLConnection) 
                    url.openConnection();
                connection.setRequestMethod("GET");
                connection.setConnectTimeout(5000);
                connection.setRequestProperty("Range", 
                    "bytes=" + startPosition + "-" + endPosition);
                
                if (connection.getResponseCode() == 
                    HttpURLConnection.HTTP_PARTIAL) {
                    RandomAccessFile fileWriter = new RandomAccessFile(
                        getDownloadPath(REMOTE_FILE), "rw");
                    fileWriter.seek(startPosition);
                    
                    InputStream input = connection.getInputStream();
                    byte[] buffer = new byte[1024 * 1024];
                    int bytesReceived;
                    int downloadedBytes = 0;
                    
                    while ((bytesReceived = input.read(buffer)) != -1) {
                        fileWriter.write(buffer, 0, bytesReceived);
                        downloadedBytes += bytesReceived;
                        
                        int currentPosition = startPosition + downloadedBytes;
                        FileOutputStream checkpointWriter = 
                            new FileOutputStream(checkpointFile);
                        checkpointWriter.write(
                            String.valueOf(currentPosition).getBytes());
                        checkpointWriter.close();
                        
                        ProgressBar progress = progressBars.get(segmentId);
                        progress.setMax(totalSize);
                        progress.setProgress(lastDownloadedPosition + 
                                            downloadedBytes);
                    }
                    
                    fileWriter.close();
                    
                    synchronized (DownloadActivity.this) {
                        activeSegments--;
                        if (activeSegments == 0) {
                            for (int i = 0; i < SEGMENT_COUNT; i++) {
                                File checkpoint = new File(
                                    getCacheDir(), "download_" + i + ".chk");
                                checkpoint.delete();
                            }
                        }
                    }
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    public void startDownload(View view) {
        progressContainer.removeAllViews();
        progressBars.clear();
        
        for (int i = 0; i < SEGMENT_COUNT; i++) {
            ProgressBar bar = (ProgressBar) View.inflate(
                getApplicationContext(), 
                R.layout.individual_progress, 
                null);
            progressBars.add(bar);
            progressContainer.addView(bar);
        }

        new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    URL url = new URL(REMOTE_FILE);
                    HttpURLConnection connection = (HttpURLConnection) 
                        url.openConnection();
                    connection.setRequestMethod("GET");
                    connection.setConnectTimeout(5000);
                    
                    if (connection.getResponseCode() == 
                        HttpURLConnection.HTTP_OK) {
                        activeSegments = SEGMENT_COUNT;
                        int fileSize = connection.getContentLength();
                        
                        RandomAccessFile emptyFile = new RandomAccessFile(
                            getDownloadPath(REMOTE_FILE), "rw");
                        emptyFile.setLength(fileSize);
                        
                        int segmentSize = fileSize / SEGMENT_COUNT;
                        
                        for (int i = 0; i < SEGMENT_COUNT; i++) {
                            int start = i * segmentSize;
                            int end = (i == SEGMENT_COUNT - 1) 
                                ? fileSize - 1 
                                : (i + 1) * segmentSize - 1;
                            
                            DownloadSegment segment = 
                                new DownloadSegment(start, end, i);
                            segment.start();
                        }
                    }
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }).start();
    }

    private String getDownloadPath(String remoteAddress) {
        int separatorIndex = remoteAddress.lastIndexOf("/") + 1;
        String filename = remoteAddress.substring(separatorIndex);
        return Environment.getExternalStorageDirectory().getPath() + 
               "/" + filename;
    }
}

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:gravity="center_horizontal"
    android:orientation="vertical">

    <Button
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:onClick="startDownload"
        android:text="Download" />

    <LinearLayout
        android:id="@+id/progress_container"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="vertical" />

</LinearLayout>

<?xml version="1.0" encoding="utf-8"?>
<ProgressBar xmlns:android="http://schemas.android.com/apk/res/android"
    android:id="@+id/progress_bar"
    style="?android:attr/progressBarStyleHorizontal"
    android:layout_width="match_parent"
    android:layout_height="wrap_content" />