Accessing SQLite database from fragment

Question

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

score 3 · Answer 1 · edited May 23 '17 at 12:25

3

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.

edited May 23 '17 at 12:25

Community

1
1

answered Apr 18 '14 at 12:28

laalto

150,114
66
286
303

Thnk you! Should i also keep the declaration in the activity? – evyamiz Apr 18 '14 at 12:35
If you need it, you can keep it. If you don't, you can remove it. – laalto Apr 18 '14 at 12:37
I understand from you that if I don't plan on interacting with the database from the activity I can remove this declaration?! Great, I will remove it! – evyamiz Apr 18 '14 at 12:40
MySQLiteOpenHelper onCreate is the one that sets up the table so it should be okay according to you. How do I find the table and delete it? should I just use an SQL query? – evyamiz Apr 18 '14 at 13:11
Thanks alot for the help! Problem solved. It was caused as you said because of a previous query that i've made and one i've deleted it, the problem was fixed. – evyamiz Apr 18 '14 at 13:34

score 1 · Answer 2 · answered Apr 18 '14 at 12:28

1

You should change this

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

With

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

answered Apr 18 '14 at 12:28

M D

47,665
9
93
114

score 1 · Answer 3 · edited Apr 04 '15 at 11:39

1

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);
}

edited Apr 04 '15 at 11:39

Ian Kemp

28,293
19
112
138

answered Apr 18 '14 at 12:28

Biraj Zalavadia

28,348
10
61
77

Thank's! The app doesn't crush now but it seems like noyhing happens.. It doesn't show anything on Logcat and if it really did commit the addUser method it should print to Logcat.. Any suggestion? – evyamiz Apr 18 '14 at 12:33
For that you need to post MySQLiteOpenHelper.java file here – Biraj Zalavadia Apr 18 '14 at 12:35
Iv'e added it to the question now – evyamiz Apr 18 '14 at 12:38
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? – evyamiz Apr 18 '14 at 12:49

Accessing SQLite database from fragment

3 Answers3