Saturday 1 April 2017

Simple Database Insert/update/delete/serach/show opeartion using Sqlite

package com.example.jyotibabhosale.practicedemo1database;

import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AlertDialog;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;

public class MainActivity extends Activity {


    SQLiteDatabase db;
    TextView roll,name,marks;
    Button add,delete,update,view,viewall;

    @Override
    protected void onCreate(final Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        roll = (TextView) findViewById(R.id.editRoll);
        name = (TextView) findViewById(R.id.editName);
        marks = (TextView) findViewById(R.id.editMarks);

        add = (Button) findViewById(R.id.btnAdd);
        delete = (Button) findViewById(R.id.btnDele);
        update = (Button) findViewById(R.id.btnUpda);
        view = (Button) findViewById(R.id.btnView);
        viewall = (Button) findViewById(R.id.btnViewAll);


        db = openOrCreateDatabase("jyo", Context.MODE_PRIVATE, null);
        db.execSQL("CREATE TABLE IF NOT EXISTS kaj(roll_no INTEGER,name VARCHAR,marks INTEGER);");

        add.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if (roll.getText().toString().trim().length() == 0 ||
                        name.getText().toString().trim().length() == 0 ||
                        marks.getText().toString().trim().length() == 0) {
                    showMessage("Error", "plz enter a name");
                    return;
                }

                db.execSQL("INSERT INTO kaj VALUES('" + roll.getText() + "','" + name.getText() + "','" + marks.getText() + "');");
                showMessage("Success", "Record Inserted successfully");
                clearTest();
            }
        });

        update.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {
                if (roll.getText().toString().trim().length() == 0) {
                    showMessage("Error", "Enter a Roll_no");
                }

                Cursor c = db.rawQuery("SELECT * FROM kaj WHERE roll='" + roll.getText() + "'", null);
                if (c.moveToFirst()) {
                    db.execSQL("UPDATE kaj SET name='" + name.getText() + "',marks='" + marks.getText() + "' WHERE roll='" + roll.getText() + "'");
                    showMessage("Success", "record updated");

                } else {
                    showMessage("Error", "plz enter valid data");
                }
                clearTest();

            }
        });

        delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                if (roll.getText().toString().length() == 0) {
                    showMessage("Error", "enter a roll_no");
                }
                Cursor c = db.rawQuery("SELECT * FROM kaj WHERE roll='" + roll.getText() + "'", null);
                if (c.moveToFirst()) {
                    db.execSQL("DELETE FROM kaj WHERE roll='" + roll.getText() + "'");
                    showMessage("success", "record deleted");

                }
                clearTest();

            }
        });
        view.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                if (roll.getText().toString().length() == 0) {
                    showMessage("Error", "plz enter a roll");
                }
                Cursor c = db.rawQuery("SELECT * FROM kaj WHERE roll='" + roll.getText() + "'", null);
                if (c.moveToFirst()) {
                    name.setText(c.getString(1));
                    marks.setText(c.getString(2));
                } else {
                    showMessage("error", "enter valid data");
                }

            }
        });


        view.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                if (roll.getText().toString().length() == 0) {
                    showMessage("Error", "plz enter a valid data");
                }
                Cursor c = db.rawQuery("SELECT * FROM kaj WHERE roll='" + roll.getText() + "'", null);
                if (c.getCount() == 0) {
                    showMessage("error", "no data found");
                }
                StringBuffer buffer = new StringBuffer();
                while (c.moveToFirst()) {
                    buffer.append("roll_no:" + c.getString(0));
                    buffer.append("name:" + c.getString(1));
                    buffer.append("marks:" + c.getString(2));
                }
                showMessage("Success", buffer.toString());


            }
        });
/*        db=openOrCreateDatabase("Mydb", Context.MODE_PRIVATE,null);
        db.execSQL("CREATE TABLE IF NOT EXISTS student(roll_no INTEGER,name VARCHAR,marks INTEGER);");

        add.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                if(roll.getText().toString().trim().length()==0||
                        name.getText().toString().trim().length()==0||
                        marks.getText().toString().trim().length()==0)
                {

                    showMessage("Error","Plz enter valid data");
                    return;

                }

                db.execSQL("INSERT INTO student VALUES('"+roll.getText()+"','"+name.getText()+"','"+marks.getText()+"');");
                showMessage("Success","Record Insert Succssfully");
                clearTest();
            }
        });


        delete.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                if(roll.getText().toString().trim().length()==0)
                {
                    showMessage("Error","Plz Enter a Roll_no");
                    return;
                }
                Cursor c=db.rawQuery("SELECT * FROM student WHERE roll_no='"+roll.getText()+"'",null);
                if(c.moveToFirst())
                {
                    db.execSQL("DELETE FROM student WHERE roll_no='"+roll.getText()+"'");
                    showMessage("Success","Record Deleted");
                }
                else
                {
                  showMessage("Error","Invalid Roll_no");
                }
                clearTest();
            }
        });

     update.setOnClickListener(new View.OnClickListener() {
         @Override
         public void onClick(View view) {
             if(roll.getText().toString().trim().length()==0)
             {
                 showMessage("Error","Plz Enter Roll_no");
                 return;
             }

             Cursor c=db.rawQuery("SELECT * FROM student WHERE roll_no='"+roll.getText()+"'",null);
             if(c.moveToFirst())
             {

                 db.execSQL("UPDATE student SET name='"+name.getText()+"',marks='"+marks.getText()+"' WHERE roll_no='"+roll.getText()+"'");
                  showMessage("Success","Record Updated");
             }
             else
             {
                 showMessage("Error","Invlid Roll_no");
             }
             clearTest();

         }
     });


      view.setOnClickListener(new View.OnClickListener() {
          @Override
          public void onClick(View view) {

            if(roll.getText().toString().trim().length()==0)
            {
              showMessage("Error","plz Enter Roll_no");
                return;
            }
            Cursor c=db.rawQuery("SELECT * FROM student WHERE roll_no='"+roll.getText()+"'",null);
              if(c.moveToFirst())
              {
               name.setText(c.getString(1));
                  marks.setText(c.getString(2));
              }
              else
              {
                  showMessage("Error","Invalid Roll_no");
              }
              clearTest();

          }


      });


        viewall.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View view) {

                if(roll.getText().toString().length()==0)

                {
                    showMessage("Error","Plz Enter Valid Data");
                    return;
                }
                Cursor c=db.rawQuery("SELECT * FROM STUDENT",null);
                if(c.getCount()==0)
                {
                    showMessage("Error","No Record Found");
                    return;
                }
                StringBuffer buffer=new StringBuffer();
                while (c.moveToFirst())
                {

                    buffer.append("Roll_no:"+c.getString(0));
                    buffer.append("Name:"+c.getString(1));
                    buffer.append("Marks:"+c.getString(2));
                }
                showMessage("Student Details", buffer.toString());

            }
        });

    }
*/
    }
   public void showMessage(String title,String Message)
   {
       AlertDialog.Builder builder=new AlertDialog.Builder(this);
       builder.setCancelable(true);
       builder.setTitle(title);
       builder.setMessage(Message);
       builder.show();
   }

public void clearTest()
{
    roll.setText("");
    name.setText("");
    marks.setText("");
    roll.requestFocus();
}





}