How to create a Local Database in Android?

Question

I am facing a problem when i tried to create a database... I guess I am doing something wrong during the creation of DB in android. I also searched web but not find out any good clues. Here is my code:

     package com.example.raqib.database;

        import android.database.Cursor;
        import android.database.sqlite.SQLiteDatabase;
        import android.os.Bundle;
        import android.support.design.widget.FloatingActionButton;
        import android.support.design.widget.Snackbar;
        import android.support.v7.app.AppCompatActivity;
        import android.support.v7.widget.Toolbar;
        import android.util.Log;
        import android.view.Gravity;
        import android.view.View;
        import android.widget.Button;
        import android.widget.EditText;
        import android.widget.Toast;

        public class MainActivity extends AppCompatActivity  implements View.OnClickListener {

            static EditText stName;
            static EditText stRollNo;
            static EditText stSem;
            Button enterDB;
            private Object context;


            // creating database



            @Override
            protected void onCreate(Bundle savedInstanceState) {
                super.onCreate(savedInstanceState);
                setContentView(R.layout.activity_main);
                Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
                setSupportActionBar(toolbar);


                getIntent();


                FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
                fab.setOnClickListener(new View.OnClickListener() {
                    @Override
                    public void onClick(View view) {
                        Snackbar.make(view, "Replace with your own action", Snackbar.LENGTH_LONG)
                                .setAction("Action", null).show();
                    }
                });
            }


            public void enterDetails(View view) {

                stName = (EditText) findViewById(R.id.stName);

                stRollNo = (EditText) findViewById(R.id.stRollNo);

                stSem = (EditText) findViewById(R.id.stSem);

                enterDB = (Button) findViewById(R.id.enterDB);
                enterDB.setOnClickListener(this);

            }

            public void onClick(View v){

                String st_Name = stName.getText().toString();
                int st_RollNo = Integer.parseInt(stRollNo.getText().toString());
                String st_sem = stSem.getText().toString();




               SQLiteDatabase db = openOrCreateDatabase("database", MODE_PRIVATE, null);
               db.execSQL("create table if not exists student (name varchar, roll_no int(4), semester varchar);");
            //   db.execSQL("insert into student( name , roll_no , semester )" + "values"+" ('"+ st_Name + "'," + st_RollNo + ",'" +st_sem + "');");


ContentValues values = new ContentValues();
values.put("name", st_Name); 
values.put("roll_no", st_RollNo); values.put("semester", st_sem); db.insert("student", null, values);



                Cursor cursor = db.rawQuery("select * from student", null);
                cursor.moveToFirst();
                Log.e("Name : ", cursor.getString(cursor.getColumnIndex("name")));

                Toast toast = Toast.makeText(MainActivity.this, " WOW! Successfully entered data in Database", Toast.LENGTH_LONG);
                toast.setGravity(Gravity.CENTER,0, 0);
                toast.show();


               // student registeredData = new student(st_Name,st_RollNo,st_sem);
            }



        }

**The error that i am facing is : it displays st_Name rather than its value by this statement:

Log.e("Name : ", cursor.getString(cursor.getColumnIndex("name")));

**

Answer 1

You should read something about the SQLiteOpenHelper. It is the much better approach to use a SQLite Database in Android.

But for your case, you forgot to wrap your text values in '

db.execSQL("insert into student( name , roll_no , semester )" + " values "+
"('"+ st_Name + "'," + st_RollNo + ",'" +st_sem + "');");

The android approach to avoid such problems would be to set your values to insert in a ContentValues Object and use database's insert function:

ContentValues values = new ContentValues();
values.put("name", st_Name);
values.put("roll_no", st_RollNo);
values.put("semester", st_sem);
db.insert("student", null, values);

Or you could use Prepared Statements:

SQLiteStatement stmt = db.compileStatement("insert into student( name , roll_no , semester ) values (?,?,?);");
stmt.bindString(1, st_Name);
stmt.bindInt(1, st_RollNo);
stmt.bindString(1, st_sem);
stmt.execute();

The Insert function as well as using prepared Statements are the more secure way to insert data. They will take of escaping Strings and putting them into the right format.