Step 1: Create the SQLite Database
To create an SQLite database for our e-commerce app, we need to create a new class that extends SQLiteOpenHelper. This class will handle the creation and upgrading of the SQLite database schema. Here is an example of how to create the SQLite database:
java
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class MySQLiteOpenHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "ecommerce.db";
private static final int DATABASE_VERSION = 1;
public MySQLiteOpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
// Create the users table.
String createUsersTableQuery "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT, password TEXT)";
try (Statement statement db.createStatement()) {
statement.execute(createUsersTableQuery);
} catch (SQLException e) {e.printStackTrace();
}
// Create the items table.
String createItemsTableQuery "CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY AUTOINCREMENT, product_id TEXT, name TEXT, description TEXT, price REAL, quantity INTEGER)";try (Statement statement db.createStatement()) {
statement.execute(createItemsTableQuery);
} catch (SQLException e) {e.printStackTrace();
}
// Create the orders table.
String createOrdersTableQuery "CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, total_cost REAL)";try (Statement statement db.createStatement()) {
statement.execute(createOrdersTableQuery);
} catch (SQLException e) {e.printStackTrace();
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Handle database upgrades here.
}}
In this example, we are creating three tables: users, items, and orders. We are also specifying the column names and data types for each table. The onCreate method is used to create the SQLite database schema when the app is first run.
Step 2: Add Data to the SQLite Database
Once we have created the SQLite database, we can add data to it using the INSERT INTO statement in our Android Studio project. Here is an example of how to add users, items, and orders to the SQLite database for our e-commerce app:
java
import androidx.sqlite.SQLiteDatabase;
public class MySQLiteExample extends AppCompatActivity {
private SQLiteDatabase db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);setContentView(R.layout.activity_main);
// Create the SQLite database with a default version of 1.0.db new MySQLiteOpenHelper(this, DATABASE_NAME, null, 1).getReadableDatabase();
// Insert a user into the users table.
String insertUserQuery "INSERT INTO users (name, email, password) VALUES ('John Doe', 'john@example.com', 'password123')";try (SQLiteStatement statement db.beginTransaction()) {
statement.execute(insertUserQuery);
statement.setTransactionSuccessful();
} finally {statement.endTransaction();
} catch (SQLException e) {e.printStackTrace();
}
// Insert an item into the items table.
String insertItemQuery "INSERT INTO items (product_id, name, description, price, quantity) VALUES (1, 'iPhone', 'Apple iPhone 12', 999.99, 1)";try (SQLiteStatement statement db.beginTransaction()) {
statement.execute(insertItemQuery);
statement.setTransactionSuccessful();
} finally {statement.endTransaction();
} catch (SQLException e) {e.printStackTrace();
}
// Insert an order into the orders table.String insertOrderQuery “INSERT INTO orders (user_id, total_cost) VALUES (1, 999.99)”;
try (SQLiteStatement statement db.beginTransaction()) {
statement.execute(insertOrderQuery);
statement.setTransactionSuccessful();
} finally {statement.endTransaction();
} catch (SQLException e) {e.printStackTrace();
}
}}
In this example, we are inserting a user named John Doe, an item called iPhone, and an order with a total cost of 999.99. We are using the INSERT INTO statement to add these items to their respective tables in the SQLite database. We are also using transactions to ensure that the database operations are atomic.
Step 3: Retrieve Data from the SQLite Database
To retrieve data from the SQLite database, we can use the SELECT statement in our Android Studio project. Here is an example of how to retrieve all users and orders from their respective tables for our e-commerce app:
java
import androidx.sqlite.SQLiteDatabase;
public class MySQLiteExample extends AppCompatActivity {
private SQLiteDatabase db;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);setContentView(R.layout.activity_main);
// Create the SQLite database with a default version of 1.0.db new MySQLiteOpenHelper(this, DATABASE_NAME, null, 1).getReadableDatabase();
// Retrieve all users from the users table.String selectUsersQuery “SELECT * FROM users”;
try (SQLiteStatement statement db.rawQuery(selectUsersQuery)) {
while (statement.moveToNext()) {
int id statement.getInt(0);
String name statement.getString(1);
String email statement.getString(2);
String password statement.getString(3);System.out.println(“User: ” + id + “, ” + name + “, ” + email + “, ” + password);
}
} catch (SQLException e) {e.printStackTrace();
}
// Retrieve all orders from the orders table.String selectOrdersQuery “SELECT * FROM orders”;
try (SQLiteStatement statement db.rawQuery(selectOrdersQuery)) {
while (statement.moveToNext()) {
int id statement.getInt(0);
int user_id statement.getInt(1);
double total_cost statement.getDouble(2);System.out.println(“Order: ” + id + “, ” + user_id + “, ” + total_cost);
}
} catch (SQLException e) {e.printStackTrace();
}
}}
In this example, we are using the rawQuery method to execute the SELECT statement and retrieve data from the SQLite database. We are also using the moveToNext method to iterate over the results of the query.