Sqlite select statement not working

Question

I was making a sample app where I had created a database for sqlite, I have also inserted values inside the table. But when I am using the select query, I am getting nullpointer exception.

This is my class:

public class MainActivity extends Activity {

    Button insert;
    Button show;
    EditText name;
    EditText mobile;
    SQLiteDatabase db;
    String name_str;
    String mob_str;

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);


        name = (EditText)findViewById(R.id.name_editText);


        insert = (Button) findViewById(R.id.insert_button);

        insert.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
                //Toast.makeText(getApplicationContext(), "Onclick works", Toast.LENGTH_LONG).show();



                name_str=name.getText().toString();
                Toast.makeText(getApplicationContext(), name_str, Toast.LENGTH_LONG).show();

                // create or open database file
                db = openOrCreateDatabase("Test.db" , SQLiteDatabase.CREATE_IF_NECESSARY,  
                                                                                      null);
                db.setVersion(1);
               // db.setLocale(Locale.getDefault());
                db.setLockingEnabled(true);

                //System.out.println("Database Created");

             // creating table in database
                db.execSQL("CREATE TABLE IF NOT EXISTS "+"contacts"+" " +
                                "( id INTEGER PRIMARY KEY AUTOINCREMENT," +
                                "  name varchar, " +
                                "  mobile varchar ); ");

                db.execSQL("INSERT INTO contacts(name, mobile)VALUES ('Swift','9830098300');");

                db.execSQL("drop table contacts");


                //Database Exists check
                File database=getApplicationContext().getDatabasePath("Test.db");

                if (!database.exists()) {
                    // Database does not exist so copy it from assets here
                    Log.i("Database", "Not Found");
                } else {
                    Log.i("Database", "Found");
                }






            }



        });
        show = (Button) findViewById(R.id.show_button);

        show.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
//                //Toast.makeText(getApplicationContext(), "Onclick works", Toast.LENGTH_LONG).show();
                Cursor c =db.rawQuery("SELECT COUNT() FROM sqlite_master WHERE name ='contacts';",null);
                if (c != null ) {
                    if  (c.moveToFirst()) {
                          do {
        String firstName = c.getString(c.getColumnIndex("name"));

                          }while (c.moveToNext());
                    }
               }

            }
        });

    }





    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }





}

This is the main.xml:

<RelativeLayout 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" >

    <TextView
        android:id="@+id/name_textView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_alignParentTop="true"
        android:layout_marginTop="60dp"
        android:text="Name:"
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <EditText
        android:id="@+id/name_editText"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/name_textView"
        android:layout_alignBottom="@+id/name_textView"
        android:layout_marginLeft="14dp"
        android:layout_toRightOf="@+id/name_textView"
        android:ems="10" >

        <requestFocus />
    </EditText>

    <TextView
        android:id="@+id/mobile_textView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_below="@+id/name_editText"
        android:layout_marginTop="53dp"
        android:text="Mobile: "
        android:textAppearance="?android:attr/textAppearanceMedium" />

    <EditText
        android:id="@+id/mobile_editText"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/mobile_textView"
        android:layout_alignBottom="@+id/mobile_textView"
        android:layout_alignLeft="@+id/name_editText"
        android:ems="10"
        android:inputType="phone" />

    <Button
        android:id="@+id/insert_button"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignLeft="@+id/mobile_editText"
        android:layout_centerVertical="true"
        android:text="Insert" />

    <Button
        android:id="@+id/show_button"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignBaseline="@+id/insert_button"
        android:layout_alignBottom="@+id/insert_button"
        android:layout_alignRight="@+id/mobile_editText"
        android:text="Show" />

</RelativeLayout>

Logcat:

10-16 17:16:28.938: D/AndroidRuntime(413): Shutting down VM
10-16 17:16:28.938: W/dalvikvm(413): threadid=1: thread exiting with uncaught exception (group=0x4001d800)
10-16 17:16:28.958: E/AndroidRuntime(413): FATAL EXCEPTION: main
10-16 17:16:28.958: E/AndroidRuntime(413): java.lang.NullPointerException
10-16 17:16:28.958: E/AndroidRuntime(413):  at com.cyberswift.database.example.MainActivity$2.onClick(MainActivity.java:97)
10-16 17:16:28.958: E/AndroidRuntime(413):  at android.view.View.performClick(View.java:2408)
10-16 17:16:28.958: E/AndroidRuntime(413):  at android.view.View$PerformClick.run(View.java:8816)
10-16 17:16:28.958: E/AndroidRuntime(413):  at android.os.Handler.handleCallback(Handler.java:587)
10-16 17:16:28.958: E/AndroidRuntime(413):  at android.os.Handler.dispatchMessage(Handler.java:92)
10-16 17:16:28.958: E/AndroidRuntime(413):  at android.os.Looper.loop(Looper.java:123)
10-16 17:16:28.958: E/AndroidRuntime(413):  at android.app.ActivityThread.main(ActivityThread.java:4627)
10-16 17:16:28.958: E/AndroidRuntime(413):  at java.lang.reflect.Method.invokeNative(Native Method)
10-16 17:16:28.958: E/AndroidRuntime(413):  at java.lang.reflect.Method.invoke(Method.java:521)
10-16 17:16:28.958: E/AndroidRuntime(413):  at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
10-16 17:16:28.958: E/AndroidRuntime(413):  at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
10-16 17:16:28.958: E/AndroidRuntime(413):  at dalvik.system.NativeStart.main(Native Method)
10-16 17:16:30.998: I/Process(413): Sending signal. PID: 413 SIG: 9

Exactly where am I going wrong...............

Answer 1

As Mohamed says you didn't select name column in the query.

As I know, in Android to count rows you should select whatever columns you want and then get cursor.size() as count.(best practice)

If you want to get data from cursor, first you should verify that cursor.size() > 0 in your if (c != null ). An example to get data from database :

if (cursor != null && cursor.size() > 0)
{
//  get count
int count = cursor.size();

//  to get data
cursor.moveToFirst();
while(!cursor.isAfterLast())
{
//  do get data from cursor
}
}

Answer 2

You did not select name column in your query, so when looping the cursor ( String firstName = c.getString(c.getColumnIndex("name"));), it cannot find it.

Also, you should specify the column you count or count all in your SQL query:

db.rawQuery("SELECT name, COUNT(*) AS "+ "\""+ "count"+ "\""+ " FROM sqlite_master WHERE name ='contacts'",null);

Then you can use String firstName = c.getString(c.getColumnIndex("name")); and count will have the needed value