In this example we will discuss how to insert and update data in SQLite using android.
activity_main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity"
android:orientation="vertical">
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Name"
android:id="@+id/nameEdit"
android:layout_margin="10dp"/>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Age"
android:id="@+id/ageEdit"
android:layout_margin="10dp"
android:inputType="number"/>
<EditText
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="City"
android:id="@+id/cityEdit"
android:layout_margin="10dp"/>
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center"
android:text="Submit"
android:id="@+id/submitBtn"/>
<ListView
android:layout_width="match_parent"
android:layout_height="match_parent"
android:id="@+id/listViewId"/>
</LinearLayout>
DBHelper.java
package com.example.sqliteexample;
import android.app.DownloadManager;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;
import java.util.ArrayList;
import java.util.List;
public class DBHelper extends SQLiteOpenHelper {
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "schoolDetails";
private static final String TABLE_STUDENTS = "students";
private static final String ID = "id";
private static final String NAME = "name";
private static final String AGE = "age";
private static final String CITY = "class";
public DBHelper(@Nullable Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_STUDENT_TABLE = " CREATE TABLE " + TABLE_STUDENTS + "(" + ID + " INTEGER PRIMARY KEY, "
+ NAME + " TEXT, " + AGE + " TEXT, " + CITY + " TEXT " + ")";
db.execSQL(CREATE_STUDENT_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(" DROP TABLE IF EXISTS "+ TABLE_STUDENTS);
onCreate(db);
}
public void addContacts(Students students){
SQLiteDatabase db = this.getReadableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put(NAME,students.getName());
contentValues.put(AGE,students.getAge());
contentValues.put(CITY,students.getCity());
db.insert(TABLE_STUDENTS,null,contentValues);
db.close();
}
// code to get the single contact
public Students getStudents(int id){
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from " + TABLE_STUDENTS + " where id="+id+"", null);
if(cursor != null)
cursor.moveToFirst();
Students students = new Students(Integer.parseInt(cursor.getString(0)),
cursor.getString(1), Integer.parseInt(cursor.getString(2)),cursor.getString(3));
return students;
}
// code to get all contacts in a list view
public List getAllStudents(){
List studentsList = new ArrayList<>();
String selectQuery = "SELECT * FROM " + TABLE_STUDENTS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery,null);
if(cursor.moveToFirst()){
do{
Students students = new Students();
students.setId(Integer.parseInt(cursor.getString(0)));
students.setName(cursor.getString(1));
students.setAge(Integer.parseInt(cursor.getString(2)));
students.setCity(cursor.getString(3));
studentsList.add(students);
}while(cursor.moveToNext());
}
return studentsList;
}
// code to update the single contact
public boolean updateStudents(int id, String name, int age, String city){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put(NAME,name);
contentValues.put(AGE,age);
contentValues.put(CITY,city);
db.update(TABLE_STUDENTS, contentValues, ID + " = ? " ,
new String[]{String.valueOf(id)});
return true;
}
// Deleting single contact
public void deleteStudents(Students students){
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_STUDENTS, ID + " = ? ",
new String[]{String.valueOf(students.getId())});
db.close();
}
// Getting contacts Count
public int getStudentsCount() {
int count=0;
String countQuery = "SELECT * FROM " + TABLE_STUDENTS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
if(cursor != null && !cursor.isClosed()){
count = cursor.getCount();
cursor.close();
}
return count;
}
public int getLastStudentsId(){
int count = 0;
SQLiteDatabase db = this.getReadableDatabase();
String query = "SELECT * FROM " + TABLE_STUDENTS;
Cursor cursor = db.rawQuery(query,null);
if(cursor != null && !cursor.isClosed()){
cursor.moveToLast();
if(cursor.getCount() == 0) {
count = 1;
}else{
count = cursor.getInt(cursor.getColumnIndex(ID));
}
}
return count;
}
}
MainActivity.java
package com.example.sqliteexample;
import androidx.appcompat.app.AlertDialog;
import androidx.appcompat.app.AppCompatActivity;
import android.content.DialogInterface;
import android.os.Bundle;
import android.text.TextUtils;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.Toast;
import java.util.ArrayList;
import java.util.List;
public class MainActivity extends AppCompatActivity{
private ListView listViewId;
private ArrayAdapter arrayAdapter;
private List studentLists = new ArrayList<>();
private ArrayList data = new ArrayList<>();
private EditText nameEdit, ageEdit, cityEdit;
private Button submitBtn;
private DBHelper dbHelper;
private Students student;
int count=0;
int currid = 0;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
listViewId = findViewById(R.id.listViewId);
nameEdit = findViewById(R.id.nameEdit);
ageEdit = findViewById(R.id.ageEdit);
cityEdit = findViewById(R.id.cityEdit);
submitBtn = findViewById(R.id.submitBtn);
dbHelper = new DBHelper(this);
showListData();
submitBtn.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
boolean checkName = TextUtils.isEmpty(nameEdit.getText().toString());
boolean checkAge = TextUtils.isEmpty(ageEdit.getText().toString());
boolean checkCity = TextUtils.isEmpty(cityEdit.getText().toString());
if(!checkName && !checkAge && !checkCity) {
Log.d("True", ((Button) v).getText().toString());
if (((Button) v).getText().toString().equals("Submit")) {
String name = nameEdit.getText().toString();
int age = Integer.parseInt(ageEdit.getText().toString());
String city = cityEdit.getText().toString();
count = dbHelper.getLastStudentsId();
Log.d("Count", String.valueOf(count));
// if(count == -1){
// count = 0;
// }
dbHelper.addContacts(new Students((count + 1), name, age, city));
showListData();
nameEdit.setText("");
ageEdit.setText("");
cityEdit.setText("");
} else {
String name = nameEdit.getText().toString();
int age = Integer.parseInt(ageEdit.getText().toString());
String city = cityEdit.getText().toString();
dbHelper.updateStudents(currid, name, age, city);
nameEdit.setText("");
ageEdit.setText("");
cityEdit.setText("");
submitBtn.setText("Submit");
showListData();
}
}else{
if(checkName == true){
nameEdit.setError("Please Enter a Name!");
}if(checkAge == true){
ageEdit.setError("Please enter the Age!");
}if(checkCity == true){
cityEdit.setError("Please Enter the City!");
}
}
}
});
}
public void showListData(){
studentLists.clear();
data.clear();
studentLists = dbHelper.getAllStudents();
// Toast.makeText(getApplicationContext(), String.valueOf(studentLists.size()),Toast.LENGTH_SHORT).show();
if(studentLists.size() != 0){
for(int i=0; i< studentLists.size(); i++){
data.add(studentLists.get(i).name+" , "+studentLists.get(i).age+" , "+studentLists.get(i).city);
}
}
arrayAdapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1, data);
listViewId.setAdapter(arrayAdapter);
listViewId.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
student = dbHelper.getStudents(position+1);
nameEdit.setText(student.getName());
ageEdit.setText(String.valueOf(student.getAge()));
cityEdit.setText(student.getCity());
submitBtn.setText("Update");
currid = position+1;
}
});
listViewId.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() {
@Override
public boolean onItemLongClick(AdapterView<?> parent, View view, int position, long id) {
student = dbHelper.getStudents(position+1);
new AlertDialog.Builder(MainActivity.this)
.setMessage("Are you sure You want to delete the Data?")
.setPositiveButton("Yes", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
dbHelper.deleteStudents(student);
showListData();
}
}).setNegativeButton("No",null).show();
return true;
}
});
}
}
Students.java
package com.example.sqliteexample;
public class Students {
int id;
String name;
int age;
String city;
public Students(){
}
public Students(int id, String name, int age, String city) {
this.id = id;
this.name = name;
this.age = age;
this.city = city;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
}
