Scribd
Upload
32 views12 pages

CRUD Operations in Android with SQLite

The document outlines the steps to create an Android application using Kotlin for performing CRUD operations on a Course entity with SQLiteOpenHelper. It includes setting up the project, creating a data model, implementing a database helper class, designing the user interface, and handling user interactions. Additionally, it briefly describes a second lab focused on creating a 'goods.db' database and managing product data.

Uploaded by

chiendv.23ite
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
32 views12 pages

CRUD Operations in Android with SQLite

The document outlines the steps to create an Android application using Kotlin for performing CRUD operations on a Course entity with SQLiteOpenHelper. It includes setting up the project, creating a data model, implementing a database helper class, designing the user interface, and handling user interactions. Additionally, it briefly describes a second lab focused on creating a 'goods.db' database and managing product data.

Uploaded by

chiendv.23ite
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQLITE

*Lab 1:
Creating a Android application for CRUD operations on a Course (Int id, String
name, and String description), using SQLiteOpenHelper.

Solution:
To create a Kotlin Android application that performs CRUD (Create, Read, Update, Delete)
operations on a Course entity using SQLiteOpenHelper, you'll follow these steps:
1. Set up your Android project.
2. Create a data model for the Course.
3. Implement the SQLite database helper class.
4. Create the user interface for performing CRUD operations.
5. Implement the logic to handle user interactions and database operations.
Step 1: Set up your Android project
- File > New > New project > Empty Views Activity > Click Next
1
- Enter Project name, location, language (kotlin), min SDK, Groovy DSL > Click
Finish
Step 2: Create a data model for the Course
Right click on Package folder > New > Kotlin Class > Data Class: Course

data class Course(


val id: Int,
val name: String,
val description: String
)

Step 3: Implement the SQLite database helper class


Right click on Package folder > New > Kotlin Class > Class: DatabaseHelper
import [Link]
import [Link]
import [Link]
import [Link]
import [Link]
import [Link]

// Class and Constructor


// Constructor: Create Database
class DatabaseHelper(context: Context) :
SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION)
{

companion object {
private const val DATABASE_NAME = "[Link]"
private const val DATABASE_VERSION = 1
private const val TABLE_COURSES = "courses"
private const val COLUMN_ID = "id"
private const val COLUMN_NAME = "name"
private const val COLUMN_DESCRIPTION = "description"
}

override fun onCreate(db: SQLiteDatabase) {


// Create Table
val createTableStatement = ("CREATE TABLE $TABLE_COURSES

2
(" +
"$COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT, "
+
"$COLUMN_NAME TEXT, " +
"$COLUMN_DESCRIPTION TEXT)")
[Link](createTableStatement)
}

override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int,


newVersion: Int) {
//Drop Table
[Link]("DROP TABLE IF EXISTS $TABLE_COURSES")
onCreate(db)
}

fun addCourse(course: Course): Boolean {


val db = [Link]

// Get values
val contentValues = ContentValues().apply {
put(COLUMN_NAME, [Link])
put(COLUMN_DESCRIPTION, [Link])
}

// Insert
val result = [Link](TABLE_COURSES, null,
contentValues)

[Link]()
return result != -1L // Returns true if insert was
successful
}

@SuppressLint("Range")
fun getCourses(): List<Course> {
val courseList = mutableListOf<Course>()
val db = [Link]

// Read courses
val cursor: Cursor = [Link]("SELECT * FROM
$TABLE_COURSES", null)

// Add to List
if ([Link]()) {
do {
val id =
[Link]([Link](COLUMN_ID))

3
val name =
[Link]([Link](COLUMN_NAME))
val description =
[Link]([Link](COLUMN_DESCRIPTION))
[Link](Course(id, name, description))
} while ([Link]())
}

[Link]()
[Link]()
return courseList
}

fun updateCourse(course: Course): Boolean {


val db = [Link]

// Get values
val contentValues = ContentValues().apply {
put(COLUMN_NAME, [Link])
put(COLUMN_DESCRIPTION, [Link])
}

// Update course
val result = [Link](TABLE_COURSES, contentValues,
"$COLUMN_ID = ?", arrayOf([Link]()))

[Link]()
return result > 0 // Returns true if result > 0
}

fun deleteCourse(id: Int): Boolean {


val db = [Link]

// Delete course
val result = [Link](TABLE_COURSES, "$COLUMN_ID = ?",
arrayOf([Link]()))

[Link]()
return result > 0 // Returns true if result > 0
}
}

Step 4: Create a layout for RecyclerView Item


Right click on res/layout > New > Layout resources file > Name: item_course

4
<[Link]
xmlns:android="[Link]
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="8dp"
android:padding="16dp">

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

<LinearLayout
android:layout_width="0dp"
android:layout_height="wrap_content"
android:layout_weight="1"
android:orientation="vertical">

<TextView
android:id="@+id/textViewCourseName"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textStyle="bold"
android:textSize="18sp"/>

<TextView
android:id="@+id/textViewCourseDescription"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textSize="14sp"
android:layout_marginTop="4dp"
android:textColor="@android:color/darker_gray"/>
</LinearLayout>

<!-- Buttons Container -->


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

<Button
android:id="@+id/buttonUpdate"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Update"

5
android:textColor="@android:color/white" />

<Button
android:id="@+id/buttonDelete"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Delete"
android:textColor="@android:color/white"
android:layout_marginStart="8dp" />
</LinearLayout>

</LinearLayout>

</[Link]>

Step 5: Create Adapter class for RecyclerView


Right click on Package folder > New > Kotlin Class > Class: CourseAdapter

import [Link]
import [Link]
import [Link]
import [Link]
import [Link]
import [Link]
import [Link]

class CourseAdapter(
private val context: Context,
private var courses: List<Course>,
private val onUpdateClick: (Course) -> Unit,
private val onDeleteClick: (Int) -> Unit
) : [Link]<[Link]>() {

inner class CourseViewHolder(itemView: View) :


[Link](itemView) {
val textViewName: TextView =
[Link]([Link])
val textViewDescription: TextView =
[Link]([Link])
val buttonUpdate: Button =
[Link]([Link])
val buttonDelete: Button =
[Link]([Link])

init {
[Link] {

6
val course = courses[adapterPosition]
onUpdateClick(course)
}

[Link] {
val courseId = courses[adapterPosition].id
onDeleteClick(courseId)
}
}
}

override fun onCreateViewHolder(parent: ViewGroup, viewType:


Int): CourseViewHolder {
val view =
[Link](context).inflate([Link].item_course,
parent, false)
return CourseViewHolder(view)
}

override fun onBindViewHolder(holder: CourseViewHolder,


position: Int) {
val course = courses[position]
[Link] = [Link]
[Link] = [Link]
}

override fun getItemCount() = [Link]

fun updateCourses(newCourses: List<Course>) {


courses = newCourses
notifyDataSetChanged()
}
}

Step 6: MainActivity:
- Activity_Main.xml:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
xmlns:android="[Link]
xmlns:app="[Link]
xmlns:tools="[Link]
android:id="@+id/main"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
tools:context=".MainActivity">

7
<EditText
android:id="@+id/editTextCourseName"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Course Name"
android:padding="12dp"
android:layout_marginTop="16dp" />

<EditText
android:id="@+id/editTextCourseDescription"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Course Description"
android:padding="12dp"
android:layout_marginTop="8dp" />

<Button
android:id="@+id/buttonAddCourse"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Add Course"
android:layout_marginTop="16dp"
android:textColor="@android:color/white"
android:layout_gravity="center" />

<!-- RecyclerView wrapped in CardView for better aesthetics


-->
<[Link]
android:layout_width="match_parent"
android:layout_height="wrap_content"
app:cardElevation="4dp"
app:cardCornerRadius="8dp"
android:layout_marginTop="16dp">

<[Link]
android:id="@+id/recyclerViewCourses"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:padding="8dp" />
</[Link]>

</LinearLayout>
- [Link]
import [Link]
import [Link]

8
import [Link]
import [Link]
import [Link]
import [Link]
import [Link]
import [Link]
import [Link]
import [Link]

class MainActivity : AppCompatActivity() {

private lateinit var databaseHelper: DatabaseHelper


private lateinit var editTextCourseName: EditText
private lateinit var editTextCourseDescription: EditText
private lateinit var buttonAddCourse: Button
private lateinit var recyclerViewCourses: RecyclerView
private lateinit var courseAdapter: CourseAdapter
private var courses: MutableList<Course> = mutableListOf()
private var selectedCourseId: Int? = null

override fun onCreate(savedInstanceState: Bundle?) {


[Link](savedInstanceState)
enableEdgeToEdge()
setContentView([Link].activity_main)

[Link](findViewById([Link]
n)) { v, insets ->
val systemBars =
[Link]([Link]())
[Link]([Link], [Link],
[Link], [Link])
insets
}

// DatabaseHelper
databaseHelper = DatabaseHelper(this)

// Views
editTextCourseName =
findViewById([Link])
editTextCourseDescription =
findViewById([Link])
buttonAddCourse = findViewById([Link])
recyclerViewCourses =
findViewById([Link])

// RecyclerView

9
[Link] =
LinearLayoutManager(this)
courseAdapter = CourseAdapter(this, courses, { course -
> updateCourse(course) }, { id -> deleteCourse(id) })
[Link] = courseAdapter

// Add Course
[Link] {
addOrUpdateCourse() }
loadCourses()
}

// Add or Update
private fun addOrUpdateCourse() {
val name = [Link]()
val description =
[Link]()

if ([Link]() && [Link]()) {


// id == null > Add
if (selectedCourseId == null) {
// Add new course
val course = Course(0, name, description) // ID
will be auto-incremented
if ([Link](course)) {
loadCourses()
clearInputFields()
}
} else {
// id != null > Update existing course
val course = Course(selectedCourseId!!, name,
description)
if ([Link](course)) {
loadCourses() // Refresh the list after
update
clearInputFields()
selectedCourseId = null
[Link] = "Add Course" //
Reset button text
[Link] = [Link]
// Show the button again
}
}
}
}

// Update data

10
private fun loadCourses() {
[Link]()
[Link]([Link]())
[Link](courses)
}

// Clear views
private fun clearInputFields() {
[Link]()
[Link]()
}

// Click buton Update


private fun updateCourse(course: Course) {
[Link]([Link])
[Link]([Link])
selectedCourseId = [Link]

[Link] = "Update Course" // Change button


text to indicate updating
[Link] = [Link] // Show the
button during editing
}

// Click buton Delete


private fun deleteCourse(id: Int) {
if ([Link](id)) {
loadCourses()
}
}
}

*Lab 2:
- Create a database in sqlite, named “[Link]”
- Create a table, named “product”
Id ProductName Product Description ProductPrice

1 Laptop Abc… 20.000.000

2 Smartphone Xyz… 15.000.00

11
3 Headphone Kzz… 1000.000

- MainActivity: Load data into the recyclerview


- AddNewActivity: Insert data
- UpdateActivity: Update/Delete data

12

Common questions

Powered by AI

Using the Model-View-Controller (MVC) pattern in the Android application offers several advantages: 1) Separation of Concerns: MVC separates the application into three components—Model, View, and Controller. The Model (Course data class and DatabaseHelper) manages data and business logic, enhancing maintainability and scalability . The View (XML layouts and RecyclerView) handles UI representation, allowing easy updates without affecting other parts . 2) Reusability: Components in MVC are independent, promoting reusability. For instance, the DatabaseHelper can be reused across different activities . 3) Testability: With MVC, the model and controller logic can be tested in isolation, improving test coverage and facilitating bug detection early in development. 4) Flexibility: Changes to UI designs or logic components can be implemented independently, speeding up development cycles . Overall, MVC patterns structure applications in a way that improves code organization and productivity.

Setting up an Android project for CRUD operations involves several key components: 1) Establish the Android project and choose relevant settings such as language (Kotlin), and project configurations . 2) Develop a data model representing the entity (e.g., a Course with id, name, and description). 3) Implement a SQLite database helper class to manage database creation, and define CRUD methods . 4) Design and implement the user interface, including layouts and RecyclerViews for displaying data . 5) Create an adapter class for handling data binding necessary for displaying course information in RecyclerViews . These components operate together to enable efficient interaction and manipulation of Course data.

Setting up an 'Add New' activity involves several steps: 1) UI Design: First, a layout file is created containing input fields for course name and description and a 'Submit' button . 2) Data Collection: User inputs are collected from these fields when the 'Submit' button is clicked, validated for completeness, and any necessary formatting is applied . 3) Database Interaction: The activity uses an instance of DatabaseHelper to insert data. A Course object is created with input values, and the addCourse() method is called to insert this data into the SQLite database . 4) Feedback and Navigation: Upon successful insertion, confirmatory feedback is provided, and optionally, the user is navigated back to the main interface listing all courses, often with a refresh to display the updated data . These steps ensure seamless integration of new data into the application, enhancing functionality and data management.

The CourseAdapter class in the provided code is designed to bridge the data between the RecyclerView and the underlying data list. It holds a list of Course objects and binds data to views for each list item through the onBindViewHolder method. This method assigns course names and descriptions to the corresponding TextView elements . The adapter also supports user interaction by binding button click events for updating and deleting courses. It does this through the ViewHolder inner class, where the button setOnClickListener methods are set to trigger update and delete actions with the respective course data or ID . Consequently, the CourseAdapter handles data rendering and captures user interactions for input to CRUD operations.

In MainActivity, the update functionality is managed by the addOrUpdateCourse method, which checks if a selectedCourseId is set. When a user taps an 'update' button for a specific course within the interface, the updateCourse method populates the input fields with the current data of the selected course and changes the 'Add Course' button's text to 'Update Course' . Upon submission, if selectedCourseId is not null, it signifies an update, and the method constructs a Course object with this ID to invoke databaseHelper.updateCourse() with the modified name and description. After updating, the method reloads the updated list by calling loadCourses(). This process reflects modifications in the database and refreshes the UI to show revised data.

When designing the RecyclerView layout for displaying courses, several considerations come into play: 1) Layout and Styling: Use of CardView enhances visual aesthetics by providing elevation and corner radius properties that create a paper-like texture and shadow, offering a modern look . Text elements are styled differently to distinguish headings from descriptions, aiding readability. 2) Efficient Use of Space: A vertical LinearLayout within the CardView is employed for compact logical grouping of data, while horizontal LinearLayouts manage interactive buttons efficiently without clutter . 3) Interactive Elements: Buttons for 'update' and 'delete' are highly visible and labeled clearly, enabling easy access to further actions . 4) Accessibility: Adequate spacing and touch-friendly elements are considered to accommodate varied screen sizes and enhance interaction comfort. These considerations ensure that the interface is not only visually appealing but also functional, intuitive, and accessible, leading to enhanced user experience by promoting engagement and ease of navigation.

Using SQLiteOpenHelper for data persistence involves numerous considerations and challenges. 1) Performance: SQLite can slow down with large datasets. Implementing indexes, optimizing queries, and managing transactions can mitigate performance issues . 2) Data Integrity: Ensuring data integrity requires handling concurrent writes and reads properly. SQLite provides mechanisms like transactions to help with this, but developers need to design with race conditions in mind . 3) Schema Changes: Altering database structure requires careful handling; incrementing the DATABASE_VERSION and managing onUpgrade() is necessary for such changes . 4) External Access: SQLite databases are stored on the device, posing privacy and security challenges. Encryption and proper permissions handling are critical to protect user data. Each of these considerations requires deliberate planning and implementation strategies to ensure robust and reliable data management.

A failure in inserting a new Course into the SQLite database can occur due to several reasons: insufficient permissions, database corruption, or the absence of values for non-null fields. The insert operation returns a long value, which is -1 if the insertion fails . The logic implemented checks if the result of the db.insert() method is not equal to -1 to confirm a successful insertion. If the insertion returns -1, it indicates a failure, which can further be debugged by checking database integrity, and required field presence, and verifying the SQL syntax . Proper error handling, including logging errors or prompting user messages, can aid in identifying the root cause of such failures.

The clearInputFields method in MainActivity enhances user interface behavior by resetting the input fields for course name and description after a course is added or updated, preventing stale data from remaining visible and reducing user confusion . This method ensures that after an operation, the input fields are ready for a new entry or modification without the need for manual deletion by the user, thereby optimizing experience and usability. Moreover, it provides consistent UI appearance and encourages forward navigation flow in user interactions.

The SQLite database helper class in an Android application provides essential functions to facilitate CRUD operations. It defines constants for the database name, version, and table structure, which ensures consistency in database operations. The helper class's methods such as addCourse(), getCourses(), updateCourse(), and deleteCourse() encapsulate the logic for creating, reading, updating, and deleting records. This compartmentalization allows each method to open a writable or readable database connection, perform its specific SQL operation, and close the connection efficiently . As such, the helper class abstracts the complexity of direct SQL manipulations and provides reusable methods which maintain the integrity and efficiency of database operations.

You might also like