Exemples de Base de Données Android 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 ===");
    }
}