Skip to main content

SQLite Database With Android


Android provides several ways to store user and app data. SQLite is one way of storing user data. SQLite is a very light weight database which comes with Android OS. In this tutorial I’ll be discussing how to write classes to handle all SQLite operations.
SQLite is a opensource  SQL database that stores data to a text file on a device. Android comes in
with built in SQLite database implementation.
SQLite supports all the relation database features. In order to access this database you don't need
to establish any kind of connections for it like JDBCODBC etc.

In this tutorial I am taking an example of storing user Detail in SQLite database. I am using a table called UserMaster to store user detail. This table contains three columns id (INT), userid (VARCHAR), username (VARCHAR), password (VARCHAR) , address (VARCHAR).

Database- Package
The main package is android.database.sqlite that contains the classes to mange your own database.
In this Class we are creating all CRUD(Create, Read, Update, Delete) operations in android
1. Create a new project by going to File ⇒ New Android Project.
2. Once the project is created, create a new class in your project src directory and name it as DataBaseHelper.java  ( Right Click on src/package ⇒ New ⇒ Class)
3. Now extend your DatabaseHandler.java class from SQLiteOpenHelper.

public class DataBaseHelper extends SQLiteOpenHelper {
4. After extending your class from SQLiteOpenHelper you need to override two methods onCreate() and onUpgrage()
onCreate() – These is where we need to write create table statements. This is called when database is created.
onUpgrade() – This method is called when database is upgraded like modifying the table structure, adding constraints to database etc.,

public class DataBaseHelper extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "Testdatabase";
    Context context;

    public DataBaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    /*Method to Create Table in Local Database*/
    @Override
    public void onCreate(SQLiteDatabase db) {
    try {
             db.execSQL("CREATE TABLE IF NOT EXISTS " + Userdetail.TABLE_NAME + " ( " +
                    Userdetail.COLUMN_NAME_ID + " INTEGER PRIMARY KEY ," +
                    Userdetail.COLUMN_NAME_USERID + " VARCHAR(100) ," +
                    Userdetail.COLUMN_NAME_USERNAME + " VARCHAR(100) ," +
                    Userdetail.COLUMN_NAME_PASSWORD + " VARCHAR(100) ," +
                    Userdetail.COLUMN_NAME_ADDRESS + " VARCHAR(25) )");
        } catch (SQLiteException e) {
            Log.d("SQLEXCEPTION", e.getMessage());
            Toast.makeText(context, "Exception in Table Creation" + e.getMessage(), Toast.LENGTH_SHORT).show();
            e.printStackTrace();
        }
    }

   /*Method to upgrate database . This method  is run when we change the Database Version*/
   @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Toast.makeText(context, "Upgrade method is running", Toast.LENGTH_SHORT).show();
        db.execSQL("DROP TABLE IF EXISTS " + Userdetail.TABLE_NAME);
        onCreate(db);
    }
    public void insertuserdeatil(Userdetail obj) {
        Log.d("username", obj.getUserName().toString());
        Log.d("userId", obj.getUserId().toString());
        Log.d("password", obj.getPassword().toString());
        Log.d("address", obj.getAddress().toString());
        SQLiteDatabase db = getWritableDatabase();
         try {
            String query = "INSERT INTO " + Userdetail.TABLE_NAME + "(" +
                    Userdetail.COLUMN_NAME_USERID + "," + Userdetail.COLUMN_NAME_USERNAME
                    + "," + Userdetail.COLUMN_NAME_PASSWORD + "," + Userdetail.COLUMN_NAME_ADDRESS + ") VALUES ('" +
                    obj.getUserId() + "','" + obj.getUserName() + "','" +
                    obj.getPassword() + "','" + obj.getAddress() + "')";

            Log.d("MYINSERTQUERY", query);
            db.execSQL(query);
           } catch (SQLiteException e) {
            e.printStackTrace();
        }
      }
     /* Method to get All data stored in Database Table */
     public ArrayList<Userdetail> getalldata() {
        SQLiteDatabase db = getReadableDatabase();
        String query = "";
        ArrayList<Userdetail> sourceArray = new ArrayList<>();
        query = "SELECT * FROM " + Userdetail.TABLE_NAME;
        Log.d("MYQUERY", query);
        Cursor cursor = db.rawQuery(query, null);
        if (cursor.moveToFirst()) {
            do {
                Userdetail sgs = new Userdetail();
                sgs.setUserId(cursor.getString(cursor.getColumnIndex(Userdetail.COLUMN_NAME_USERID)));
                sgs.setUserName(cursor.getString(cursor.getColumnIndex(Userdetail.COLUMN_NAME_USERNAME)));
                sgs.setPassword(cursor.getString(cursor.getColumnIndex(Userdetail.COLUMN_NAME_PASSWORD)));
                sgs.setAddress(cursor.getString(cursor.getColumnIndex(Userdetail.COLUMN_NAME_ADDRESS)));
                sourceArray.add(sgs);
            } while (cursor.moveToNext());
        }
        cursor.close();
        Log.d("mysourcearray",sourceArray.toString());
        return sourceArray;
      }
    /*Method to Delete Table Data*/
    public void deletemeterreading(Context context, String userid) {
        DataBaseHelper dbHelper = new DataBaseHelper(context);
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        try {
            db.execSQL("DELETE FROM " + Userdetail.TABLE_NAME + " WHERE " + COLUMN_NAME_USERID + " = '" + userid + "' ");
        } catch (SQLiteException e) {
            e.printStackTrace();
        }
    }
}
Create UserDetail Class

This class defines all the Geter-Seter methods

public class Userdetail {
    public static final String TABLE_NAME = "UserMaster";
    public static final String COLUMN_NAME_ID = "id";
    public static final String COLUMN_NAME_USERNAME = "Username";
    public static final String COLUMN_NAME_PASSWORD = "Password";
    public static final String COLUMN_NAME_ADDRESS = "Address";
    public static final String COLUMN_NAME_USERID = "UserId";

    private int ID;
    private String Username = "";
    private String Password = "";
    private String Address = "";
    private String UserId = "";

    public int getID() {
        return ID;
    }
    public void setID(int ID) {
        ID = ID;
    }
    public String getUserName() {
        return Username;
    }
    public void setUserName(String userName) {
        this.Username = userName;
    }
    public String getPassword() {
        return Password;
    }
    public void setPassword(String password) {
        Password = password;
    }
    public String getAddress() {
         return Address;
    }
    public void setAddress(String address) {
         Address = address;
    }
    public String getUserId() {
         return UserId;
    }
   public void setUserId(String userId) {
        UserId = userId;
    }
}
Insert these lines in activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout 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"    
android:padding="20dp"    tools:context="com.example.sqlliteexample.MainActivity">


    <TextView        
android:id="@+id/title"        
android:layout_width="wrap_content"        
android:layout_height="wrap_content"        
android:layout_centerHorizontal="true"        
android:layout_marginTop="20dp"        
android:text="Local DataBase Example"        
android:textSize="16dp"        
android:textStyle="bold" />
    <EditText        
android:id="@+id/userid"        
android:layout_width="match_parent"        
android:layout_height="wrap_content"        
android:layout_below="@+id/title"        
android:layout_marginTop="30dp"        
android:hint="UserId" />
    <EditText        
android:id="@+id/username"        
android:layout_width="match_parent"        
android:layout_height="wrap_content"        
android:layout_below="@+id/userid"        
android:layout_marginTop="10dp"        
android:hint="Username" />
    <EditText        android:id="@+id/password"        
android:layout_width="match_parent"        
android:layout_height="wrap_content"        
android:layout_below="@+id/username"        
android:layout_marginTop="10dp"        
android:hint="Password" />
    <EditText        
android:id="@+id/address"        
android:layout_width="match_parent"        
android:layout_height="wrap_content"        
android:layout_below="@+id/password"        
android:layout_marginTop="10dp"        
android:hint="Address" />
    <Button        
android:id="@+id/save"        
android:layout_width="match_parent"        
android:layout_height="wrap_content"        
android:layout_below="@+id/address"        
android:layout_marginTop="30dp"        
android:text="Save"        
android:textStyle="bold" />
    <Button        
android:id="@+id/records"        
android:layout_width="match_parent"        
android:layout_height="wrap_content"        
android:layout_below="@+id/save"        
android:layout_marginTop="10dp"        
android:text="Get All Records"        
android:textStyle="bold" />
</RelativeLayout>
CustomAdapter File Named as CustomAdapter.java
public class CustomAdapter extends BaseAdapter {
    Context mcontext;
    List<Userdetail> datalist;
    LayoutInflater inflater;
    Userdetail myuser;
 public CustomAdapter(Context context,List<Userdetail> array) {
        this.mcontext = context;
        this.datalist = array;
        Log.d("datalist",datalist.toString());
        inflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
    }
    @Override
    public int getCount() {
        return datalist.size();
    }
    @Override
    public Object getItem(int position) {
       return datalist.get(position);
    }
    @Override
    public long getItemId(int position) {
        return position;
    }
    public class Holder {
        TextView userid, username, password, address;
    }
    @Override
    public View getView(int position, View view, ViewGroup viewGroup) {
        Holder holder = new Holder();
        View rowView;
        rowView = inflater.inflate(R.layout.liststyle, null);
        myuser = datalist.get(position);
        holder.userid = (TextView) rowView.findViewById(R.id.user_id);
        holder.username = (TextView) rowView.findViewById(R.id.user_name);
        holder.password = (TextView) rowView.findViewById(R.id.user_password);
        holder.address = (TextView) rowView.findViewById(R.id.user_address);
        holder.userid.setText(myuser.getUserId());
        holder.username.setText(myuser.getUserName());
        holder.password.setText(myuser.getPassword());
        holder.address.setText(myuser.getAddress());
        return rowView;
    }
}



Create Layout file for for Custom List View


<?xml version="1.0" encoding="utf-8"?>



<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"    



android:layout_width="match_parent"    



android:layout_height="match_parent">





<TextView        



android:id="@+id/user_id"        



android:layout_width="match_parent"        



android:layout_height="wrap_content"        



android:layout_marginTop="10dp"        



android:textColor="#000" />
<TextView        



android:id="@+id/user_name"        



android:layout_width="match_parent"        



android:layout_height="wrap_content"        



android:layout_below="@+id/user_id"        



android:layout_marginTop="10dp"        



android:textColor="#000" />
<TextView        



android:id="@+id/user_password"        



android:layout_width="match_parent"        



android:layout_height="wrap_content"        



android:layout_below="@+id/user_name"        



android:layout_marginTop="10dp"        


android:textColor="#000" />



<TextView        



android:id="@+id/user_address"        



android:layout_width="match_parent"        



android:layout_height="wrap_content"        



android:layout_below="@+id/user_password"        



android:layout_marginTop="10dp"        



android:textColor="#000" />
</RelativeLayout>












 Insert these line of Code in MainActivity2.java










public class Main2Activity extends AppCompatActivity {
    ListView listView;
    DataBaseHelper dataBaseHalper;
    ArrayList myarray;
@Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main2);
        listView = (ListView) findViewById(R.id.listview);
        dataBaseHalper =new DataBaseHelper(getApplicationContext());
        myarray= new ArrayList();
        myarray=dataBaseHalper.getalldata();
        Log.d("myarraysourcearraydata",myarray.toString());
        CustomAdapter adapter = new CustomAdapter(getApplicationContext(),myarray);
        listView.setAdapter(adapter);
        adapter.notifyDataSetChanged();
    }
}











Insert these line of code in activity_main2.xml




<RelativeLayout 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="com.example.sqlliteexample.Main2Activity">
    <ListView        


android:id="@+id/listview"        


android:layout_width="match_parent"        


android:layout_height="match_parent"/>
</RelativeLayout>













































































































Comments















Post a Comment































Popular posts from this blog



















How to align Title at center of ActionBar in Android




























  How to Align Title At Center of Action Bar in Android                                                                                                                                                    @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); setTitle("DashBoard"); }      Activity    public  void  setTitle ( String  title ){      getSupportActionBar (). setHomeButtonEnabled ( true );      getSupportActionBar (). setDisplayHomeAsUpEnabled ( true );      TextView  textView =  new  TextView ( this );      textView . setText ( title );      textView . setTextSize ( 20 );      textView . setTypeface ( null ,  Typeface . BOLD );      textView . setLayoutParams ( new  LinearLayout . LayoutParams ( LinearLayout . LayoutParams . FILL_PARENT ,  LinearLayout . LayoutParams . WRAP_CONTENT ));      textView . setGravity ( Gravity . CENTER );      textView . setTextColor ( getResources (). ge




















1 comment









Read more




























Difference Between Pending Intent And Intent in Android




























                            Normal Intent         Normal Intent will die as soon as the app being killed.      An Android Intent is an object carrying an intent, i.e a message from one Component to another       Component either inside or outside of the application.Intent can communicate message among       any of the three core Components of an application -- Activities, Services,and BroadcastReceivers.       Two types of Intent in Android    1. Explicit Intent.    2.Implicit Intent    Explicit Intent is an Intent which is used to Call the another component Explicitly in your application   Like :We are calling  Next activity on button click of First activity using Intent    Example   // Explicit Intent by specifying its class name     Intent  i =  new  Intent ( this ,  TargetActivity . class );     i . putExtra ( "Key1" ,  "ABC" );     i . putExtra ( "Key2" ,  "123" );   // Starts TargetActivity     startActivity ( i );   Implicit Intent Intent 




















3 comments









Read more




























Expected a key while parsing a block mapping (Flutter)




























  Flutter makes use of the Dart packaging system, pub. Via your applications  pubspec.yam l file (or simple pubspec), you can pull down packages from the flutter ecosystem, or the broader dart community.   Anyway, i need to add some images to my flutter application, and so had to add an assets section to the pubspec .the default Android Studio generated apps  pubspec  has a lot of commented out code with explainations about what is going on, e.g  # To add assets to your application, add an assets section, like this: # assets:   # - images/a_dot_burr.jpeg   # - images/a_dot_ham.jpeg # An image asset can refer to one or more resolution-specific "variants", see  # https://flutter.io/assets-and-images/#resolution-aware.    So I uncommented these lines   # assest :  -  -  -  #  -  images/a_dot_ham.jpeg   with the idea from these comment.  that i would just edit it to suit my particular needs. Once you have edited your  pubspec, you need to click on the "Get dependencies"




















2 comments









Read more