How to create a Local Database in Android?


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

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.

source: stackoverflow.com
js interview questions