Hello, geeks. Welcome to SQLite with multiple tables in Android Studio example.
SQLite with multiple tables in Android example guides you to create multiple tables with simple source code.
We will implement crud operations like insert, update, delete and display data with multiple tables.
If you have not basic idea about SQLite, then check SQLite In Android Example first.
If you want to do all crud operations with one table, then visit Crud Operations In SQLite in Android.
You will need to develop SQLite with multiple tables in Android for complex projects.
First, check the output of SQLite with multiple tables in Android example, then we will develop it.
Download Source Code For SQLite With Multiple Tables In Android
[sociallocker] Download SQLiteMultiTableDEmonuts [/sociallocker]
Creating SQLite With Multiple Tables In Android Studio Tutorial step by step
Understanding Table Structure Of SQLite With Multiple Tables In Android
We have created three tables.
- users – User’s name is stored in this table.
- users_hobby – User’s hobby is stored here.
- users_city – User’s city is stored here.
Now a column “id” is the key thing. We will reference all the values across the tables by “id.”
In users table, id for peter is 1. In users_hobby table, a hobby corresponds to id 1 is “spider man.” Thus, a hobby for peter is spider man.
In the users_city table, a city corresponds to id 1 is “new york.” Thus, a city for peter is new york.
Similarly, you can see in above image that hobby for bruce is batman and city is gotham.
Now come to Android Studio and follow steps to create SQLite with multiple tables in Android.
Step 1: Create a new project in Android Studio.
Step 2: Creating UserModel class
Make a Java class named “UserModel” and add following source code
/** * Created by Parsania Hardik on 26-Apr-17. */ public class UserModel implements Serializable{ private String name, hobby, city; private int id; public String getCity() { return city; } public void setCity(String city) { 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 String getHobby() { return hobby; } public void setHobby(String hobby) { this.hobby = hobby; } }
Step 3: Making DatabaseHelper class
Prepare a class named “DatabaseHelper” and all following source code
import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; import java.util.ArrayList; /** * Created by Parsania Hardik on 11/01/2016. */ public class DatabaseHelper extends SQLiteOpenHelper { public static String DATABASE_NAME = "user_database"; private static final int DATABASE_VERSION = 1; private static final String TABLE_USER = "users"; private static final String TABLE_USER_HOBBY = "users_hobby"; private static final String TABLE_USER_CITY = "users_city"; private static final String KEY_ID = "id"; private static final String KEY_FIRSTNAME = "name"; private static final String KEY_HOBBY = "hobby"; private static final String KEY_CITY = "city"; /*CREATE TABLE students ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone_number TEXT......);*/ private static final String CREATE_TABLE_STUDENTS = "CREATE TABLE " + TABLE_USER + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_FIRSTNAME + " TEXT );"; private static final String CREATE_TABLE_USER_HOBBY = "CREATE TABLE " + TABLE_USER_HOBBY + "(" + KEY_ID + " INTEGER,"+ KEY_HOBBY + " TEXT );"; private static final String CREATE_TABLE_USER_CITY = "CREATE TABLE " + TABLE_USER_CITY + "(" + KEY_ID + " INTEGER,"+ KEY_CITY + " TEXT );"; public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); Log.d("table", CREATE_TABLE_STUDENTS); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_TABLE_STUDENTS); db.execSQL(CREATE_TABLE_USER_HOBBY); db.execSQL(CREATE_TABLE_USER_CITY); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS '" + TABLE_USER + "'"); db.execSQL("DROP TABLE IF EXISTS '" + TABLE_USER_HOBBY + "'"); db.execSQL("DROP TABLE IF EXISTS '" + TABLE_USER_CITY + "'"); onCreate(db); } public void addUser(String name, String hobby, String city) { SQLiteDatabase db = this.getWritableDatabase(); //adding user name in users table ContentValues values = new ContentValues(); values.put(KEY_FIRSTNAME, name); // db.insert(TABLE_USER, null, values); long id = db.insertWithOnConflict(TABLE_USER, null, values, SQLiteDatabase.CONFLICT_IGNORE); //adding user hobby in users_hobby table ContentValues valuesHobby = new ContentValues(); valuesHobby.put(KEY_ID, id); valuesHobby.put(KEY_HOBBY, hobby); db.insert(TABLE_USER_HOBBY, null, valuesHobby); //adding user city in users_city table ContentValues valuesCity = new ContentValues(); valuesCity.put(KEY_ID, id); valuesCity.put(KEY_CITY, city); db.insert(TABLE_USER_CITY, null, valuesCity); } public ArrayList<UserModel> getAllUsers() { ArrayList<UserModel> userModelArrayList = new ArrayList<UserModel>(); String selectQuery = "SELECT * FROM " + TABLE_USER; SQLiteDatabase db = this.getReadableDatabase(); Cursor c = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (c.moveToFirst()) { do { UserModel userModel = new UserModel(); userModel.setId(c.getInt(c.getColumnIndex(KEY_ID))); userModel.setName(c.getString(c.getColumnIndex(KEY_FIRSTNAME))); //getting user hobby where id = id from user_hobby table String selectHobbyQuery = "SELECT * FROM " + TABLE_USER_HOBBY +" WHERE "+KEY_ID+" = "+ userModel.getId(); Log.d("oppp",selectHobbyQuery); //SQLiteDatabase dbhobby = this.getReadableDatabase(); Cursor cHobby = db.rawQuery(selectHobbyQuery, null); if (cHobby.moveToFirst()) { do { userModel.setHobby(cHobby.getString(cHobby.getColumnIndex(KEY_HOBBY))); } while (cHobby.moveToNext()); } //getting user city where id = id from user_city table String selectCityQuery = "SELECT * FROM " + TABLE_USER_CITY+" WHERE "+KEY_ID+" = "+ userModel.getId();; //SQLiteDatabase dbCity = this.getReadableDatabase(); Cursor cCity = db.rawQuery(selectCityQuery, null); if (cCity.moveToFirst()) { do { userModel.setCity(cCity.getString(cCity.getColumnIndex(KEY_CITY))); } while (cCity.moveToNext()); } // adding to Students list userModelArrayList.add(userModel); } while (c.moveToNext()); } return userModelArrayList; } public void updateUser(int id, String name, String hobby, String city) { SQLiteDatabase db = this.getWritableDatabase(); // updating name in users table ContentValues values = new ContentValues(); values.put(KEY_FIRSTNAME, name); db.update(TABLE_USER, values, KEY_ID + " = ?", new String[]{String.valueOf(id)}); // updating hobby in users_hobby table ContentValues valuesHobby = new ContentValues(); valuesHobby.put(KEY_HOBBY, hobby); db.update(TABLE_USER_HOBBY, valuesHobby, KEY_ID + " = ?", new String[]{String.valueOf(id)}); // updating city in users_city table ContentValues valuesCity = new ContentValues(); valuesCity.put(KEY_CITY, city); db.update(TABLE_USER_CITY, valuesCity, KEY_ID + " = ?", new String[]{String.valueOf(id)}); } public void deleteUSer(int id) { // delete row in students table based on id SQLiteDatabase db = this.getWritableDatabase(); //deleting from users table db.delete(TABLE_USER, KEY_ID + " = ?",new String[]{String.valueOf(id)}); //deleting from users_hobby table db.delete(TABLE_USER_HOBBY, KEY_ID + " = ?", new String[]{String.valueOf(id)}); //deleting from users_city table db.delete(TABLE_USER_CITY, KEY_ID + " = ?",new String[]{String.valueOf(id)}); } }
Step 4: Description of DatabaseHelper
Database name, Database version, table name and column names of tables are written as below.
public static String DATABASE_NAME = "user_database"; private static final int DATABASE_VERSION = 1; private static final String TABLE_USER = "users"; private static final String TABLE_USER_HOBBY = "users_hobby"; private static final String TABLE_USER_CITY = "users_city"; private static final String KEY_ID = "id"; private static final String KEY_FIRSTNAME = "name"; private static final String KEY_HOBBY = "hobby"; private static final String KEY_CITY = "city";
Understanding most important part of SQLite Database
In onCreate() method, create statement for the table is written.
@Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_TABLE_STUDENTS); db.execSQL(CREATE_TABLE_USER_HOBBY); db.execSQL(CREATE_TABLE_USER_CITY); }
In onUpgrade() method, already exist tables are dropped and then all the tables are recreated.
onCreate() method is called into onUpgrade() method to create tables.
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS '" + TABLE_USER + "'"); db.execSQL("DROP TABLE IF EXISTS '" + TABLE_USER_HOBBY + "'"); db.execSQL("DROP TABLE IF EXISTS '" + TABLE_USER_CITY + "'"); onCreate(db); }
In following scenarios, you need to upgrade DATABASE_VERSION number in increment order.
- When you add, update or delete any column of any table in the whole database.
- When you update any column name of any table.
- When you add, update or delete any table.
- When you update table name.
When DATABASE_VERSION is updated in increment order, onUpgrade() method is called.
addUser() method will add the user to SQLite Database.
getAllUsers() method will fetch all the users from SQLite Database.
updateUser() method will update the user’s information.
deleteUser() method will delete the user from SQLite.
Step 5: Preparing lv_item.xml file
Create a layout resource file named “lv_item.xml” and add the following
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:padding="10dp" android:orientation="vertical"> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:background="#000" android:orientation="vertical"> <TextView android:id="@+id/name" android:layout_width="match_parent" android:layout_height="wrap_content" android:textColor="#fff" android:layout_marginTop="10dp" android:gravity="center_vertical" android:textAppearance="?android:attr/textAppearanceMedium" android:paddingLeft="10dp" android:text="Name" /> <TextView android:id="@+id/hobby" android:layout_width="match_parent" android:layout_height="wrap_content" android:textColor="#fff" android:gravity="center_vertical" android:textAppearance="?android:attr/textAppearanceMedium" android:paddingLeft="10dp" android:text="Hobby" /> <TextView android:id="@+id/city" android:layout_width="match_parent" android:layout_height="wrap_content" android:textColor="#fff" android:gravity="center_vertical" android:textAppearance="?android:attr/textAppearanceMedium" android:paddingLeft="10dp" android:text="City" /> </LinearLayout> <View android:layout_width="match_parent" android:layout_height="1dp" android:layout_marginTop="10dp" android:layout_marginLeft="10dp" android:layout_marginRight="10dp" android:background="@color/colorAccent"/> </LinearLayout>
Step 6: Preparing CustomAdapter class
Create a Java class named “CustomAdapter” and paste below
import android.content.Context; import android.view.LayoutInflater; import android.view.View; import android.view.ViewGroup; import android.widget.BaseAdapter; import android.widget.TextView; import java.util.ArrayList; /** * Created by Parsania Hardik on 26-Apr-17. */ public class CustomAdapter extends BaseAdapter { private Context context; private ArrayList<UserModel> userModelArrayList; public CustomAdapter(Context context, ArrayList<UserModel> userModelArrayList) { this.context = context; this.userModelArrayList = userModelArrayList; } @Override public int getCount() { return userModelArrayList.size(); } @Override public Object getItem(int position) { return userModelArrayList.get(position); } @Override public long getItemId(int position) { return 0; } @Override public View getView(int position, View convertView, ViewGroup parent) { ViewHolder holder; if (convertView == null) { holder = new ViewHolder(); LayoutInflater inflater = (LayoutInflater) context .getSystemService(Context.LAYOUT_INFLATER_SERVICE); convertView = inflater.inflate(R.layout.lv_item, null, true); holder.tvname = (TextView) convertView.findViewById(R.id.name); holder.tvhobby = (TextView) convertView.findViewById(R.id.hobby); holder.tvcity = (TextView) convertView.findViewById(R.id.city); convertView.setTag(holder); }else { // the getTag returns the viewHolder object set as a tag to the view holder = (ViewHolder)convertView.getTag(); } holder.tvname.setText("Name: "+userModelArrayList.get(position).getName()); holder.tvhobby.setText("Hobby: "+userModelArrayList.get(position).getHobby()); holder.tvcity.setText("City: "+userModelArrayList.get(position).getCity()); return convertView; } private class ViewHolder { protected TextView tvname, tvhobby, tvcity; } }
Step 7: Getting All Users From SQLite Database
Make a new activity named “GetAllUsersActivity.“
Add below source code in GetAllUsersActivity.java
import android.content.Intent; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.view.View; import android.widget.AdapterView; import android.widget.ListView; import java.util.ArrayList; public class GetAllUsersActivity extends AppCompatActivity { private ListView listView; private ArrayList<UserModel> userModelArrayList; private CustomAdapter customAdapter; private DatabaseHelper databaseHelper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_get_all_users); listView = (ListView) findViewById(R.id.lv); databaseHelper = new DatabaseHelper(this); userModelArrayList = databaseHelper.getAllUsers(); customAdapter = new CustomAdapter(this,userModelArrayList); listView.setAdapter(customAdapter); listView.setOnItemClickListener(new AdapterView.OnItemClickListener() { @Override public void onItemClick(AdapterView<?> parent, View view, int position, long id) { Intent intent = new Intent(GetAllUsersActivity.this, UpdateDeleteActivity.class); intent.putExtra("user", userModelArrayList.get(position)); startActivity(intent); } }); } }
Paste below code in activity_get_all_users.xml
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:background="@color/colorAccent" android:orientation="vertical" tools:context="com.exampledemo.parsaniahardik.updatesqlitedemonuts.GetAllUsersActivity"> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:gravity="center" android:layout_marginTop="10dp" android:textSize="20sp" android:textColor="#000" android:text="Data from SQLite"/> <ListView android:layout_width="match_parent" android:layout_height="match_parent" android:id="@+id/lv"> </ListView> </LinearLayout>
Step 8: Updating and deleting user information
Create a new activity named “UpdateDeleteActivity”
Add below source code in UpdateDeleteActivity.java
import android.content.Intent; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.Toast; public class UpdateDeleteActivity extends AppCompatActivity { private UserModel userModel; private EditText etname, ethobby, etcity; private Button btnupdate, btndelete; private DatabaseHelper databaseHelper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_update_delete); Intent intent = getIntent(); userModel = (UserModel) intent.getSerializableExtra("user"); databaseHelper = new DatabaseHelper(this); etname = (EditText) findViewById(R.id.etname); ethobby = (EditText) findViewById(R.id.ethobby); etcity = (EditText) findViewById(R.id.etcity); btndelete = (Button) findViewById(R.id.btndelete); btnupdate = (Button) findViewById(R.id.btnupdate); etname.setText(userModel.getName()); ethobby.setText(userModel.getHobby()); etcity.setText(userModel.getCity()); btnupdate.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { databaseHelper.updateUser(userModel.getId(),etname.getText().toString(),ethobby.getText().toString(),etcity.getText().toString()); Toast.makeText(UpdateDeleteActivity.this, "Updated Successfully!", Toast.LENGTH_SHORT).show(); Intent intent = new Intent(UpdateDeleteActivity.this,MainActivity.class); intent.addFlags(Intent.FLAG_ACTIVITY_CLEAR_TASK | Intent.FLAG_ACTIVITY_NEW_TASK); startActivity(intent); } }); btndelete.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { databaseHelper.deleteUSer(userModel.getId()); Toast.makeText(UpdateDeleteActivity.this, "Deleted Successfully!", Toast.LENGTH_SHORT).show(); Intent intent = new Intent(UpdateDeleteActivity.this,MainActivity.class); intent.addFlags(Intent.FLAG_ACTIVITY_CLEAR_TASK | Intent.FLAG_ACTIVITY_NEW_TASK); startActivity(intent); } }); } }
Copy following code in activity_update_delete.xml
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:background="@color/colorAccent" android:orientation="vertical" tools:context="com.exampledemo.parsaniahardik.updatesqlitedemonuts.UpdateDeleteActivity"> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:text="Name" android:layout_marginLeft="10dp" android:layout_marginTop="10dp" android:textColor="#000" android:textSize="20sp"/> <EditText android:layout_width="match_parent" android:layout_height="40dp" android:id="@+id/etname" android:background="#fff" android:layout_marginLeft="10dp" android:layout_marginTop="10dp" android:layout_marginRight="10dp" android:hint="Enter Name"/> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:text="Hobby" android:layout_marginLeft="10dp" android:layout_marginTop="10dp" android:textColor="#000" android:textSize="20sp"/> <EditText android:layout_width="match_parent" android:layout_height="40dp" android:id="@+id/ethobby" android:background="#fff" android:layout_marginLeft="10dp" android:layout_marginTop="10dp" android:layout_marginRight="10dp" android:hint="Enter Hobby"/> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:text="City" android:layout_marginLeft="10dp" android:layout_marginTop="10dp" android:textColor="#000" android:textSize="20sp"/> <EditText android:layout_width="match_parent" android:layout_height="40dp" android:id="@+id/etcity" android:background="#fff" android:layout_marginLeft="10dp" android:layout_marginTop="10dp" android:layout_marginRight="10dp" android:hint="Enter City"/> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginTop="10dp" android:layout_marginLeft="10dp" android:id="@+id/btnupdate" android:text="update"/> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginTop="10dp" android:layout_marginLeft="10dp" android:id="@+id/btndelete" android:text="delete"/> </LinearLayout>
Step 9: Preparing MainActivity
Add below code in MainActivity.java
import android.content.Intent; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.Toast; public class MainActivity extends AppCompatActivity { private Button btnStore, btnGetall; private EditText etname, ethobby, etcity; private DatabaseHelper databaseHelper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); databaseHelper = new DatabaseHelper(this); btnStore = (Button) findViewById(R.id.btnstore); btnGetall = (Button) findViewById(R.id.btnget); etname = (EditText) findViewById(R.id.etname); ethobby = (EditText) findViewById(R.id.ethobby); etcity = (EditText) findViewById(R.id.etcity); btnStore.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { databaseHelper.addUser(etname.getText().toString(), ethobby.getText().toString(), etcity.getText().toString()); etname.setText(""); ethobby.setText(""); etcity.setText(""); Toast.makeText(MainActivity.this, "Stored Successfully!", Toast.LENGTH_SHORT).show(); } }); btnGetall.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { Intent intent = new Intent(MainActivity.this, GetAllUsersActivity.class); startActivity(intent); } }); } }
Copy following in activity_main.xml
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" android:background="@color/colorAccent" tools:context="com.exampledemo.parsaniahardik.simplesqlitedemonuts.MainActivity"> <TextView android:layout_width="match_parent" android:layout_height="wrap_content" android:gravity="center" android:textColor="#fff" android:textSize="20sp" android:layout_marginTop="10dp" android:text="Enter Name, Hobby,City to store in SQLite" /> <EditText android:layout_width="match_parent" android:layout_height="40dp" android:id="@+id/etname" android:background="#fff" android:layout_marginTop="20dp" android:layout_marginLeft="20dp" android:hint="Enter Name"/> <EditText android:layout_width="match_parent" android:layout_height="40dp" android:id="@+id/ethobby" android:background="#fff" android:layout_marginTop="20dp" android:layout_marginLeft="20dp" android:hint="Enter Hobby"/> <EditText android:layout_width="match_parent" android:layout_height="40dp" android:id="@+id/etcity" android:background="#fff" android:layout_marginTop="20dp" android:layout_marginLeft="20dp" android:hint="Enter City"/> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:id="@+id/btnstore" android:layout_marginLeft="20dp" android:layout_marginTop="10dp" android:text="Store"/> <Button android:layout_width="wrap_content" android:layout_height="wrap_content" android:id="@+id/btnget" android:layout_marginLeft="20dp" android:layout_marginTop="10dp" android:text="Get All Users from SQLite"/> </LinearLayout>
So that is all for SQLite with multiple tables in Android example.
Feel free to comment your queries and reviews in the comment section. Thank you.
Course Recommendation
If you are new to android then you can take the below course enhance your knowledge.