Working with Databases

Working with Databases

Introduction to Databases in Android

When you need to store a large amount of structured data (like a list of users, products, or records), files or SharedPreferences are not efficient.

SQLite Database: Android provides built-in support for storing structured data using a database called SQLite. SQLite is a lightweight, serverless, transactional SQL database engine that is perfect for mobile devices.

The data is organized into tables, rows, and columns, and you can manage it using SQL (Structured Query Language).

Key Components for Database Operations

To work with databases in Android, you use classes from the `android.database.sqlite` package.

• SQLiteOpenHelper: This is a helper class that handles database creation and version management.
  • `onCreate(SQLiteDatabase db)`: This is called when the database is created for the first time. This is where you create your tables.
  • `onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)`: This is called when the database version changes. It is used to modify tables.
• SQLiteDatabase: This represents the actual database object. You use it to execute SQL commands (like insert, update, delete, query).
• ContentValues: This stores key-value pairs. It is used to insert a new row or update an existing row in the database. The key is the column name.
• Cursor: This represents the result set of a database query. You use it to iterate over the rows returned by a query.

Example of Creating a Database

Step 1: Create a Database Helper Class

Create a new Java class that extends `SQLiteOpenHelper`.

public class DatabaseHelper extends SQLiteOpenHelper {
    // Database Info
    private static final String DATABASE_NAME = "UserManager.db";
    private static final int DATABASE_VERSION = 1;

    // Table Name
    private static final String TABLE_USERS = "users";

    // Table Columns
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_EMAIL = "email";

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

    // Called when the database is created for the first time
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_USERS_TABLE = "CREATE TABLE " + TABLE_USERS + "("
                + KEY_ID + " INTEGER PRIMARY KEY," 
                + KEY_NAME + " TEXT,"
                + KEY_EMAIL + " TEXT" + ")";
        db.execSQL(CREATE_USERS_TABLE);
    }

    // Called when the database needs to be upgraded
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
        // Create tables again
        onCreate(db);
    }
    
    // Method to add a user
    public void addUser(String name, String email) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(KEY_NAME, name);
        values.put(KEY_EMAIL, email);
        
        // Inserting Row
        db.insert(TABLE_USERS, null, values);
        db.close(); // Closing database connection
    }

    // Method to get all users
    public Cursor getAllUsers() {
        SQLiteDatabase db = this.getReadableDatabase();
        return db.rawQuery("SELECT * FROM " + TABLE_USERS, null);
    }
}

Step 2: Use the Helper in an Activity

public class MainActivity extends AppCompatActivity {
    DatabaseHelper dbHelper;
    EditText nameEditText, emailEditText;
    TextView resultTextView;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbHelper = new DatabaseHelper(this);
        nameEditText = findViewById(R.id.name_et);
        emailEditText = findViewById(R.id.email_et);
        resultTextView = findViewById(R.id.result_tv);
    }

    // Button click to add user
    public void addUserToDb(View view) {
        String name = nameEditText.getText().toString();
        String email = emailEditText.getText().toString();
        dbHelper.addUser(name, email);
        Toast.makeText(this, "User added!", Toast.LENGTH_SHORT).show();
    }
    
    // Button click to load users
    public void loadUsers(View view) {
        Cursor cursor = dbHelper.getAllUsers();
        StringBuilder stringBuilder = new StringBuilder();

        if (cursor.moveToFirst()) {
            do {
                int id = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.KEY_ID));
                String name = cursor.getString(cursor.getColumnIndex(DatabaseHelper.KEY_NAME));
                String email = cursor.getString(cursor.getColumnIndex(DatabaseHelper.KEY_EMAIL));
                stringBuilder.append("ID: ").append(id)
                             .append(", Name: ").append(name)
                             .append(", Email: ").append(email).append("
");
            } while (cursor.moveToNext());
        }
        cursor.close();
        resultTextView.setText(stringBuilder.toString());
    }
}