Elysia Tools

Browse tools, examples, visual explainers, and topic guides from one clear workspace.

Top Categories

Hubs

Language
ZHESPTRUFRDEEN

Elysia Tools

Navigation

Java

Android Database Java Samples

Android Java database examples including SQLite connection, SQL queries, and transaction processing

Samples

Entries inside this sample collection

CodejavaIntermediate

SQLite Connection

Connect to SQLite database, create database helper, and manage database lifecycle

Difficulty

7/10

Estimated time

35 min

Tags

java, android, database, sqlite

Prerequisites

Intermediate Java, Android SDK, SQL knowledge

java
// Android Java SQLite Connection Examples
// Using Android SQLiteOpenHelper and SQLite API

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.Cursor;
import android.content.ContentValues;
import java.io.File;

// 1. Database Helper Class
class UserDatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "UserDatabase.db";
    private static final int DATABASE_VERSION = 1;

    // Table names
    private static final String TABLE_USERS = "users";
    private static final String TABLE_PROFILES = "profiles";

    // Users table columns
    private static final String COLUMN_USER_ID = "user_id";
    private static final String COLUMN_USERNAME = "username";
    private static final String COLUMN_EMAIL = "email";
    private static final String COLUMN_PASSWORD = "password";
    private static final String COLUMN_CREATED_AT = "created_at";

    // Profiles table columns
    private static final String COLUMN_PROFILE_ID = "profile_id";
    private static final String COLUMN_USER_ID_FK = "user_id";
    private static final String COLUMN_FULL_NAME = "full_name";
    private static final String COLUMN_BIO = "bio";
    private static final String COLUMN_AGE = "age";

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

    public UserDatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // Create users table
        String createUsersTable = "CREATE TABLE " + TABLE_USERS + " (" +
            COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
            COLUMN_USERNAME + " TEXT UNIQUE NOT NULL," +
            COLUMN_EMAIL + " TEXT UNIQUE NOT NULL," +
            COLUMN_PASSWORD + " TEXT NOT NULL," +
            COLUMN_CREATED_AT + " DATETIME DEFAULT CURRENT_TIMESTAMP" +
            ")";

        // Create profiles table
        String createProfilesTable = "CREATE TABLE " + TABLE_PROFILES + " (" +
            COLUMN_PROFILE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
            COLUMN_USER_ID_FK + " INTEGER," +
            COLUMN_FULL_NAME + " TEXT," +
            COLUMN_BIO + " TEXT," +
            COLUMN_AGE + " INTEGER," +
            "FOREIGN KEY(" + COLUMN_USER_ID_FK + ") REFERENCES " + TABLE_USERS + "(" + COLUMN_USER_ID + ") ON DELETE CASCADE" +
            ")";

        // Create indexes
        String createUsernameIndex = "CREATE INDEX idx_username ON " + TABLE_USERS + "(" + COLUMN_USERNAME + ")";
        String createEmailIndex = "CREATE INDEX idx_email ON " + TABLE_USERS + "(" + COLUMN_EMAIL + ")";

        db.execSQL(createUsersTable);
        db.execSQL(createProfilesTable);
        db.execSQL(createUsernameIndex);
        db.execSQL(createEmailIndex);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Handle database upgrades
        if (oldVersion < 2) {
            upgradeToVersion2(db);
        }
        if (oldVersion < 3) {
            upgradeToVersion3(db);
        }
    }

    private void upgradeToVersion2(SQLiteDatabase db) {
        // Add new column
        db.execSQL("ALTER TABLE " + TABLE_USERS + " ADD COLUMN phone TEXT");
    }

    private void upgradeToVersion3(SQLiteDatabase db) {
        // Create new table
        db.execSQL("CREATE TABLE user_settings (" +
            "setting_id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "user_id INTEGER," +
            "setting_key TEXT," +
            "setting_value TEXT," +
            "FOREIGN KEY(user_id) REFERENCES users(user_id)" +
            ")");
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Handle database downgrades
        onCreate(db);
    }

    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        // Enable foreign key constraints
        db.setForeignKeyConstraintsEnabled(true);
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        // Perform any operations needed when database is opened
    }
}

// 2. Database Connection Manager
class DatabaseConnectionManager {
    private Context context;
    private UserDatabaseHelper dbHelper;
    private SQLiteDatabase database;

    public DatabaseConnectionManager(Context context) {
        this.context = context;
    }

    // Open database for reading
    public SQLiteDatabase openReadable() {
        if (database == null || !database.isOpen()) {
            dbHelper = new UserDatabaseHelper(context);
            database = dbHelper.getReadableDatabase();
        }
        return database;
    }

    // Open database for writing
    public SQLiteDatabase openWritable() {
        if (database == null || !database.isOpen()) {
            dbHelper = new UserDatabaseHelper(context);
            database = dbHelper.getWritableDatabase();
        }
        return database;
    }

    // Close database connection
    public void close() {
        if (dbHelper != null) {
            dbHelper.close();
            database = null;
            dbHelper = null;
        }
    }

    // Check if database is open
    public boolean isOpen() {
        return database != null && database.isOpen();
    }

    // Get database path
    public String getDatabasePath() {
        return context.getDatabasePath("UserDatabase.db").getAbsolutePath();
    }

    // Get database size
    public long getDatabaseSize() {
        File file = context.getDatabasePath("UserDatabase.db");
        return file.exists() ? file.length() : 0L;
    }

    // Check if database exists
    public boolean databaseExists() {
        File file = context.getDatabasePath("UserDatabase.db");
        return file.exists();
    }

    // Delete database
    public boolean deleteDatabase() {
        close();
        return context.deleteDatabase("UserDatabase.db");
    }
}

// 3. Database Configuration
class DatabaseConfig {
    private Context context;

    public DatabaseConfig(Context context) {
        this.context = context;
    }

    // Configure database with specific settings
    public UserDatabaseHelper configureDatabase() {
        UserDatabaseHelper helper = new UserDatabaseHelper(context);
        SQLiteDatabase db = helper.getWritableDatabase();

        // Set database locale
        db.setLocale(java.util.Locale.getDefault());

        // Set maximum database size (in bytes)
        int maxSize = 10 * 1024 * 1024; // 10 MB
        db.setMaxSqlCacheSize(maxSize);

        return helper;
    }

    // Create in-memory database for testing
    public SQLiteDatabase createInMemoryDatabase() {
        return SQLiteDatabase.create(SQLiteDatabase.OpenParams.builder().build());
    }

    // Create database with specific configuration
    public UserDatabaseHelper createCustomDatabase(String name, int version) {
        UserDatabaseHelper helper = new UserDatabaseHelper(context, name, null, version);
        SQLiteDatabase db = helper.getWritableDatabase();

        db.setForeignKeyConstraintsEnabled(true);

        return helper;
    }
}

// 4. Database Lifecycle Management
class DatabaseLifecycleManager {
    private Context context;
    private java.util.List<DatabaseConnectionManager> connectionManagers = new java.util.ArrayList<>();

    public DatabaseLifecycleManager(Context context) {
        this.context = context;
    }

    // Register a connection manager
    public void registerConnection(DatabaseConnectionManager manager) {
        connectionManagers.add(manager);
    }

    // Close all connections
    public void closeAllConnections() {
        for (DatabaseConnectionManager manager : connectionManagers) {
            manager.close();
        }
        connectionManagers.clear();
    }

    // Backup database
    public boolean backupDatabase(String backupPath) {
        File dbFile = context.getDatabasePath("UserDatabase.db");
        File backupFile = new File(backupPath);

        try (java.io.FileInputStream input = new java.io.FileInputStream(dbFile);
             java.io.FileOutputStream output = new java.io.FileOutputStream(backupFile)) {

            byte[] buffer = new byte[8192];
            int bytesRead;
            while ((bytesRead = input.read(buffer)) != -1) {
                output.write(buffer, 0, bytesRead);
            }

            System.out.println("Backup completed to: " + backupPath);
            return true;
        } catch (Exception e) {
            System.out.println("Backup failed: " + e.getMessage());
            return false;
        }
    }

    // Restore database
    public boolean restoreDatabase(String backupPath) {
        File backupFile = new File(backupPath);
        File dbFile = context.getDatabasePath("UserDatabase.db");

        try {
            // Close existing connection
            closeAllConnections();

            try (java.io.FileInputStream input = new java.io.FileInputStream(backupFile);
                 java.io.FileOutputStream output = new java.io.FileOutputStream(dbFile)) {

                byte[] buffer = new byte[8192];
                int bytesRead;
                while ((bytesRead = input.read(buffer)) != -1) {
                    output.write(buffer, 0, bytesRead);
                }
            }

            System.out.println("Restore completed from: " + backupPath);
            return true;
        } catch (Exception e) {
            System.out.println("Restore failed: " + e.getMessage());
            return false;
        }
    }

    // Vacuum database (reclaim space)
    public boolean vacuumDatabase() {
        DatabaseConnectionManager manager = new DatabaseConnectionManager(context);
        SQLiteDatabase db = manager.openWritable();
        try {
            db.execSQL("VACUUM");
            System.out.println("Vacuum completed");
            return true;
        } catch (Exception e) {
            System.out.println("Vacuum failed: " + e.getMessage());
            return false;
        } finally {
            manager.close();
        }
    }

    // Analyze database (update statistics)
    public boolean analyzeDatabase() {
        DatabaseConnectionManager manager = new DatabaseConnectionManager(context);
        SQLiteDatabase db = manager.openWritable();
        try {
            db.execSQL("ANALYZE");
            System.out.println("Analyze completed");
            return true;
        } catch (Exception e) {
            System.out.println("Analyze failed: " + e.getMessage());
            return false;
        } finally {
            manager.close();
        }
    }
}

// 5. Database Information
class DatabaseInfo {
    private Context context;

    public DatabaseInfo(Context context) {
        this.context = context;
    }

    // Get database version
    public int getDatabaseVersion() {
        UserDatabaseHelper helper = new UserDatabaseHelper(context);
        return helper.getReadableDatabase().getVersion();
    }

    // Get database page size
    public int getDatabasePageSize() {
        DatabaseConnectionManager manager = new DatabaseConnectionManager(context);
        SQLiteDatabase db = manager.openReadable();
        Cursor cursor = db.rawQuery("PRAGMA page_size", null);
        int pageSize = 0;

        if (cursor != null && cursor.moveToFirst()) {
            pageSize = cursor.getInt(0);
            cursor.close();
        }

        manager.close();
        return pageSize;
    }

    // Get database encoding
    public String getDatabaseEncoding() {
        DatabaseConnectionManager manager = new DatabaseConnectionManager(context);
        SQLiteDatabase db = manager.openReadable();
        Cursor cursor = db.rawQuery("PRAGMA encoding", null);
        String encoding = "";

        if (cursor != null && cursor.moveToFirst()) {
            encoding = cursor.getString(0);
            cursor.close();
        }

        manager.close();
        return encoding;
    }

    // List all tables
    public java.util.List<String> listTables() {
        DatabaseConnectionManager manager = new DatabaseConnectionManager(context);
        SQLiteDatabase db = manager.openReadable();
        java.util.List<String> tables = new java.util.ArrayList<>();

        Cursor cursor = db.rawQuery(
            "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name",
            null
        );

        if (cursor != null) {
            while (cursor.moveToNext()) {
                tables.add(cursor.getString(0));
            }
            cursor.close();
        }

        manager.close();
        return tables;
    }

    // Get table info
    public void printTableInfo(String tableName) {
        DatabaseConnectionManager manager = new DatabaseConnectionManager(context);
        SQLiteDatabase db = manager.openReadable();

        Cursor cursor = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);

        System.out.println("\n--- Table Info: " + tableName + " ---");
        if (cursor != null) {
            while (cursor.moveToNext()) {
                int cid = cursor.getInt(cursor.getColumnIndexOrThrow("cid"));
                String name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
                String type = cursor.getString(cursor.getColumnIndexOrThrow("type"));
                int notNull = cursor.getInt(cursor.getColumnIndexOrThrow("notnull"));
                int pk = cursor.getInt(cursor.getColumnIndexOrThrow("pk"));

                System.out.println("Column: " + name + " | Type: " + type + " | Not Null: " + (notNull == 1) + " | PK: " + (pk == 1));
            }
            cursor.close();
        }

        manager.close();
    }

    // Get table row count
    public int getTableRowCount(String tableName) {
        DatabaseConnectionManager manager = new DatabaseConnectionManager(context);
        SQLiteDatabase db = manager.openReadable();
        int count = 0;

        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + tableName, null);
        if (cursor != null && cursor.moveToFirst()) {
            count = cursor.getInt(0);
            cursor.close();
        }

        manager.close();
        return count;
    }
}

// 6. Database Integrity
class DatabaseIntegrity {
    private Context context;

    public DatabaseIntegrity(Context context) {
        this.context = context;
    }

    // Check database integrity
    public boolean checkIntegrity() {
        DatabaseConnectionManager manager = new DatabaseConnectionManager(context);
        SQLiteDatabase db = manager.openReadable();
        boolean isValid = false;

        Cursor cursor = db.rawQuery("PRAGMA integrity_check", null);
        if (cursor != null && cursor.moveToFirst()) {
            String result = cursor.getString(0);
            isValid = result.equals("ok");
            cursor.close();
        }

        manager.close();
        return isValid;
    }

    // Get database size info
    public void printDatabaseSizeInfo() {
        DatabaseConnectionManager manager = new DatabaseConnectionManager(context);
        SQLiteDatabase db = manager.openReadable();

        Cursor cursor = db.rawQuery(
            "SELECT page_count, page_size * page_count as size FROM pragma_page_count(), pragma_page_size()",
            null
        );

        if (cursor != null && cursor.moveToFirst()) {
            int pageCount = cursor.getInt(0);
            long size = cursor.getLong(1);

            System.out.println("Database Size Info:");
            System.out.println("Page count: " + pageCount);
            System.out.println("Page size: " + size / pageCount + " bytes");
            System.out.println("Total size: " + size + " bytes (" + (size / 1024.0 / 1024.0) + " MB)");
            cursor.close();
        }

        manager.close();
    }
}

// Main demonstration
class SQLiteConnectionDemo {
    public static void demonstrateSQLiteConnection(Context context) {
        System.out.println("=== Android Java SQLite Connection Examples ===\n");

        // 1. Create database helper
        System.out.println("--- 1. Database Helper ---");
        UserDatabaseHelper dbHelper = new UserDatabaseHelper(context);
        System.out.println("Database helper created");

        // 2. Connection manager
        System.out.println("\n--- 2. Connection Manager ---");
        DatabaseConnectionManager connManager = new DatabaseConnectionManager(context);

        SQLiteDatabase db = connManager.openWritable();
        System.out.println("Database opened: " + connManager.isOpen());
        System.out.println("Database path: " + connManager.getDatabasePath());
        System.out.println("Database size: " + connManager.getDatabaseSize() + " bytes");

        connManager.close();

        // 3. Database configuration
        System.out.println("\n--- 3. Database Configuration ---");
        DatabaseConfig dbConfig = new DatabaseConfig(context);
        UserDatabaseHelper configuredHelper = dbConfig.configureDatabase();
        System.out.println("Database configured");

        // 4. Database lifecycle
        System.out.println("\n--- 4. Database Lifecycle ---");
        DatabaseLifecycleManager lifecycleManager = new DatabaseLifecycleManager(context);

        boolean vacuumResult = lifecycleManager.vacuumDatabase();
        System.out.println("Vacuum: " + (vacuumResult ? "Success" : "Failed"));

        boolean analyzeResult = lifecycleManager.analyzeDatabase();
        System.out.println("Analyze: " + (analyzeResult ? "Success" : "Failed"));

        // 5. Database information
        System.out.println("\n--- 5. Database Information ---");
        DatabaseInfo dbInfo = new DatabaseInfo(context);

        System.out.println("Database version: " + dbInfo.getDatabaseVersion());
        System.out.println("Page size: " + dbInfo.getDatabasePageSize() + " bytes");
        System.out.println("Encoding: " + dbInfo.getDatabaseEncoding());

        System.out.println("\nTables:");
        java.util.List<String> tables = dbInfo.listTables();
        for (String table : tables) {
            System.out.println("  - " + table);
            int rowCount = dbInfo.getTableRowCount(table);
            System.out.println("    Rows: " + rowCount);
        }

        if (tables.contains("users")) {
            dbInfo.printTableInfo("users");
        }

        // 6. Integrity check
        System.out.println("\n--- 6. Database Integrity ---");
        DatabaseIntegrity integrity = new DatabaseIntegrity(context);

        boolean isIntegrityValid = integrity.checkIntegrity();
        System.out.println("Integrity check: " + (isIntegrityValid ? "PASSED" : "FAILED"));

        integrity.printDatabaseSizeInfo();

        // 7. Cleanup
        System.out.println("\n--- 7. Cleanup ---");
        lifecycleManager.closeAllConnections();
        System.out.println("All connections closed");

        System.out.println("\n=== All SQLite Connection Examples Completed ===");
    }
}

Tools

Tools frequently paired with this sample

Related

Related sample collections

Related

Related workflows

Hub

Infer JSON Schema from Samples and Spreadsheets

Derive a reviewable JSON Schema draft from representative JSON samples or XLSX rows, then inspect types, requiredness, nulls, and outliers before treating it as a contract.

Hub

Database Schema Migration

Plan a safe database migration by normalizing source data, reviewing schema changes, validating relationships, and preparing rollback assets.

Hub

Repair a Rejected CSV Import and Load It into Your Database

Diagnose why a CSV import keeps failing, map messy headers onto the target schema, remove duplicates under a stated policy, and emit typed INSERT statements or a bulk-load TSV.

Hub

XLSX ETL Ingestion and Warehouse Handoff

Turn mixed workbook and CSV sources into traceable, schema-aware outputs for SQL loading, Parquet pipelines, and object-storage batch handoff.

Home
Tools3208
Samples593
Visualizations469
Viewers103
Hubs237
Tags333
Images, Audio & Video657

Image, audio, and video processing, conversion, and optimization tools

Math, Date & Finance646

Calculators, numerics, date logic, statistics, and finance tools

Design & Color298

Color, layout, graphics, visual styling, and design helper tools

Text & Writing209

Writing, cleanup, formatting, extraction, and text analysis tools

Developer & Web193

Developer utilities, networking, web debugging, and automation helpers

Conversion & Encoding175

Format, file, archive, unit, and encoding conversion tools

Private Habit Records: Backup, Restore and Sharing Review
Article to X and LinkedIn Drafts: Fact Review and Handoff
Markdown Interactive Deck Review and Offline Handoff
Small-Room Furniture Layout Review and Move-In Handoff
FLAC/WAV Source Screening and Evidence Handoff

© 2026 Elysia Tools. All rights reserved.

DocsPricingAboutTerms of ServicePrivacy PolicyCookiesContact

Development

Database Backup Generator

Generate SQL backup scripts for database tables

Development

Database Schema Diff

Compare two database schemas and generate migration-oriented change reports

Development

Database Migration Generator

Generate SQL migration scripts for database schema changes

Data Processing

CSV to Database Migration Planner

Infer a relational schema from CSV data and generate create-table plus ALTER migration plans for PostgreSQL, MySQL, SQLite, or SQL Server

Data Processing

Multi-Table Joiner

Merge and join multiple tables with various join types (inner, left, right, full)

Development

SQL Dialect Bridge

Translate SQL between MySQL, PostgreSQL, SQLite, BigQuery, Snowflake, and Redshift — DDL, DML, functions and types, with UNSUPPORTED warnings

Security

SQL Injection Detector

Detect common SQL injection attack patterns in input strings

Format Conversion

CSV Flavor to SQL INSERT Emitter

Parse CSV/Excel-CSV/TSV (auto dialect, RFC 4180) into MySQL/PostgreSQL/SQLite/SQL Server INSERT batches with type inference and ON CONFLICT/IGNORE

Kotlin

Android Database Kotlin Samples

Android Kotlin database examples including SQLite connection, SQL queries, and transaction processing

Objective-C

macOS Database Objective-C Samples

macOS Objective-C database examples including SQLite CRUD operations, queries, transactions, and connection management

Swift

macOS Database Swift Samples

macOS Swift database examples including SQLite connection, query execution, and transaction processing

Go

Web Database Go Samples

Web Go database operations examples including SQLite connections, SQL queries, and transaction handling

Python

Web Database Python Samples

Web Python database examples including SQLite connection, SQL queries, and transaction handling

TypeScript

Web Database TypeScript Samples

Web TypeScript database operations examples including SQLite connection, SQL query execution, and transaction handling