Accessing SQLite database from fragment

My app contains MainActivity class and several fragments. One of the is the registration fragment which I want it to be able tho interact with my database which should contain only one table named USERS to whom I will CRUD. I've opened the MySQLiteOpenHelper class that extends SQLiteOpenHelper and created a User class as well. I've declared:

MySQLiteOpenHelper db = new MySQLiteOpenHelper(this);

in the activity's onCreate method. My question is how do I interact with this db from the fragments? I've tried the following code but it didn't work:

import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v4.app.Fragment;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.Button;
import android.widget.EditText;

public class registration extends Fragment {
    public registration() {

}

@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container,
        Bundle savedInstanceState) {
    View rootView = inflater.inflate(R.layout.registration, container,
            false);

    return rootView;
}

@Override
public void onActivityCreated(Bundle savedInstanceState) {
    // TODO Auto-generated method stub
    super.onActivityCreated(savedInstanceState);
    final EditText etFirstName = (EditText) getActivity().findViewById(
            R.id.etFirstName);
    final EditText etLastName = (EditText) getActivity().findViewById(
            R.id.etLastName);
    final EditText etEmail = (EditText) getActivity().findViewById(
            R.id.etEmail);
    final EditText etUsername = (EditText) getActivity().findViewById(
            R.id.etUsername);
    final EditText etPassword = (EditText) getActivity().findViewById(
            R.id.etPassword);
    Button bDone = (Button) getActivity().findViewById(R.id.bDone);
    bDone.setOnClickListener(new View.OnClickListener() {

        @Override
        public void onClick(View v) {
            // TODO Auto-generated method stub
            MySQLiteOpenHelper db = new MySQLiteOpenHelper(null);//do I need this??
            db.addUser(new User(etFirstName.getText().toString(),
                    etLastName.getText().toString(), etEmail.getText()
                            .toString(), etUsername.getText().toString(),
                    etPassword.getText().toString()));
        }
    });

}
}

What happens now is that the moment I click the DONE button the app crushes... Thank's!!

MySQLiteOpenHelper looks like this:

import java.util.LinkedList;
import java.util.List;

import android.R.string;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION=1;
    private static final String DATABASE_NAME="MyDB";

    private static final String TABLE_USERS="USERS";

    private static final String FIRST_NAME="First_name";
    private static final String LAST_NAME="Last_name";
    private static final String EMAIL="Email";
    private static final String USERNAME="Username";
    private static final String PASSWORD="Password";

    private static final String COLUMNS[]={FIRST_NAME, LAST_NAME, EMAIL, USERNAME, PASSWORD};



    public MySQLiteOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        String CREATE_MyDB_TABLE = "CREATE TABLE USERS ( " +
                "First_name TEXT, " + 
                "Last_name TEXT, "+
                "Email KEY TEXT NOT NULL, "+
                "Username TEXT NOT NULL, "+
                "Password TEXT NOT NULL)";
        db.execSQL(CREATE_MyDB_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS USERS");

        this.onCreate(db);
    }

    public void addUser(User user){
        Log.d("addUser", user.toString());

        SQLiteDatabase db=this.getWritableDatabase();

        ContentValues values=new ContentValues();
        values.put(FIRST_NAME, user.getFirstName());
        values.put(LAST_NAME, user.getLastName());
        values.put(EMAIL, user.getEmail());
        values.put(USERNAME, user.getUsername());
        values.put(PASSWORD, user.getPassword());

        db.insert(TABLE_USERS, null, values);

        db.close();
    }

    public User getUser(String Email){
        SQLiteDatabase db=this.getReadableDatabase();

        Cursor cursor=
                db.query(TABLE_USERS, COLUMNS, "Email =?", new String[] {String.valueOf(Email)}, null, null, null);

        if (cursor != null)
            cursor.moveToFirst();

        User user=new User();
        user.setFirstName(cursor.getString(0));
        user.setLastName(cursor.getString(1));
        user.setEmail(cursor.getString(2));
        user.setUsername(cursor.getString(3));
        user.setPassword(cursor.getString(4));

        Log.d("getUser ("+Email+")", user.toString());

        return user;
    }

    public List<User> getAllUsers(){
        List<User> users=new LinkedList<User>();

        String query="SELECT * FROM "+TABLE_USERS;

        SQLiteDatabase db=this.getWritableDatabase();
        Cursor cursor= db.rawQuery(query, null);

        User user=null;
        if (cursor.moveToFirst()){
            do{
                user=new User();
                user.setFirstName(cursor.getString(0));
                user.setLastName(cursor.getString(1));
                user.setEmail(cursor.getString(2));
                user.setUsername(cursor.getString(3));
                user.setPassword(cursor.getString(4));

                users.add(user);
            } while (cursor.moveToNext());
        }

        Log.d("getAllUsers", users.toString());

        return users;
    }

    public int updateUser(User user){
        SQLiteDatabase db=this.getWritableDatabase();

        ContentValues values=new ContentValues();
        values.put(FIRST_NAME, user.getFirstName());
        values.put(LAST_NAME, user.getLastName());
        values.put(EMAIL, user.getEmail());
        values.put(USERNAME, user.getUsername());
        values.put(PASSWORD, user.getPassword());

        int i=db.update(TABLE_USERS, values, "Email =?", new String[]{String.valueOf(user.getEmail())});

        db.close();

        return i;
    }

    public void deleteUser(User user){
        SQLiteDatabase db=this.getWritableDatabase();

        db.delete(TABLE_USERS, "Email =?", new String[]{String.valueOf(user.getEmail())});

        db.close();

        Log.d("deleteUser", user.toString());
    }

}

LogCat:

04-18 12:16:09.132: E/Trace(1051): error opening trace file: No such file or directory (2)
    04-18 12:16:09.404: D/libEGL(1051): loaded /system/lib/egl/libEGL_emulation.so
    04-18 12:16:09.416: D/(1051): HostConnection::get() New Host Connection established 0xb9324060, tid 1051
    04-18 12:16:09.492: D/libEGL(1051): loaded /system/lib/egl/libGLESv1_CM_emulation.so
    04-18 12:16:09.492: D/libEGL(1051): loaded /system/lib/egl/libGLESv2_emulation.so
    04-18 12:16:09.672: W/EGL_emulation(1051): eglSurfaceAttrib not implemented
    04-18 12:16:09.708: D/OpenGLRenderer(1051): Enabling debug mode 0
    04-18 12:16:09.800: D/OpenGLRenderer(1051): TextureCache::get: create texture(0xb931a8e0): name, size, mSize = 1, 1048576, 1048576
    04-18 12:16:09.960: D/OpenGLRenderer(1051): TextureCache::get: create texture(0xb9331d10): name, size, mSize = 2, 5184, 1053760
    04-18 12:16:10.068: D/OpenGLRenderer(1051): TextureCache::get: create texture(0xb9309b60): name, size, mSize = 4, 20736, 1074496
    04-18 12:16:10.072: D/OpenGLRenderer(1051): TextureCache::get: create texture(0xb932d4b8): name, size, mSize = 5, 9216, 1083712
    04-18 12:16:10.140: D/OpenGLRenderer(1051): TextureCache::get: create texture(0xb9312810): name, size, mSize = 7, 7488, 1091200
    04-18 12:16:10.152: D/OpenGLRenderer(1051): TextureCache::get: create texture(0xb9325d70): name, size, mSize = 8, 100, 1091300
    04-18 12:16:10.212: D/OpenGLRenderer(1051): TextureCache::get: create texture(0xb93176a0): name, size, mSize = 10, 7488, 1098788
    04-18 12:16:10.252: D/OpenGLRenderer(1051): TextureCache::get: create texture(0xb9373798): name, size, mSize = 11, 7488, 1106276
    04-18 12:16:10.336: D/OpenGLRenderer(1051): TextureCache::get: create texture(0xb9315840): name, size, mSize = 12, 2304, 1108580
    04-18 12:16:42.528: D/OpenGLRenderer(1051): TextureCache::get: create texture(0xb9378b38): name, size, mSize = 78, 7488, 1116068
    04-18 12:16:42.800: D/dalvikvm(1051): GC_CONCURRENT freed 142K, 3% free 8216K/8391K, paused 14ms+0ms, total 25ms
    04-18 12:16:44.200: D/OpenGLRenderer(1051): TextureCache::get: create texture(0xb9308998): name, size, mSize = 85, 7488, 1123556
    04-18 12:16:44.216: D/OpenGLRenderer(1051): TextureCache::get: create texture(0xb9316848): name, size, mSize = 86, 7488, 1131044
    04-18 12:16:53.192: D/addUser(1051): First name: dsfLast name: dsfdsfEmail: dsfdsfUsername: Password: 
    04-18 12:16:53.196: D/AndroidRuntime(1051): Shutting down VM
    04-18 12:16:53.196: W/dalvikvm(1051): threadid=1: thread exiting with uncaught exception (group=0xa627b288)
    04-18 12:16:53.200: E/AndroidRuntime(1051): FATAL EXCEPTION: main
    04-18 12:16:53.200: E/AndroidRuntime(1051): java.lang.NullPointerException
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:224)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:164)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at com.craftyonhand.www.MySQLiteOpenHelper.addUser(MySQLiteOpenHelper.java:60)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at com.craftyonhand.www.registration$1.onClick(registration.java:47)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at android.view.View.performClick(View.java:4084)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at android.view.View$PerformClick.run(View.java:16966)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at android.os.Handler.handleCallback(Handler.java:615)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at android.os.Handler.dispatchMessage(Handler.java:92)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at android.os.Looper.loop(Looper.java:137)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at android.app.ActivityThread.main(ActivityThread.java:4745)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at java.lang.reflect.Method.invokeNative(Native Method)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at java.lang.reflect.Method.invoke(Method.java:511)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
    04-18 12:16:53.200: E/AndroidRuntime(1051):     at dalvik.system.NativeStart.main(Native Method)
    04-18 12:16:55.792: I/Process(1051): Sending signal. PID: 1051 SIG: 9

Upvotes: 1

Answers (3)

Biraj Zalavadia

Reputation: 28484

Replace

MySQLiteOpenHelper db = new MySQLiteOpenHelper(null);

with

MySQLiteOpenHelper db = new MySQLiteOpenHelper(getActivity());

Update onUpgrade() method

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
     String CREATE_MyDB_TABLE = "CREATE TABLE IF NOT EXISTS USERS ( " +
                "First_name TEXT, " + 
                "Last_name TEXT, "+
                "Email KEY TEXT NOT NULL, "+
                "Username TEXT NOT NULL, "+
                "Password TEXT NOT NULL)";
        db.execSQL(CREATE_MyDB_TABLE);
    this.onCreate(db);
}

Upvotes: 1

laalto

Reputation: 152817

MySQLiteOpenHelper db = new MySQLiteOpenHelper(null);//do I need this??

Yes you need it and you also need to pass a non-null valid Context as an argument. In a fragment, you can use getActivity() to get the activity to use as a Contextonce the fragment is attached to its host activity.

Okay so it does show on logcat but for some reason it says that there is no such table USERS... As far as I know it should open it if it does not exsits. Am I wrong?

Possibly you have an older version of the database file around. Clear app data or uninstall it to remove it and make the database helper onCreate() create set up the tables for you. See When is SQLiteOpenHelper onCreate() / onUpgrade() run? for more.

Upvotes: 3

M D

Reputation: 47817

You should change this

   MySQLiteOpenHelper db = new MySQLiteOpenHelper(null);//do I need this??

With

   MySQLiteOpenHelper db = new MySQLiteOpenHelper(getActivity());//do I need this??

Upvotes: 1

Accessing SQLite database from fragment

Answers (3)

Related Questions