Multiple database tables in SQLite

Question

I am new in Android and SQLite. I am trying to create multiple tables in my database where first, I save the email details in the database then through that saved email, I'm able to retrieve the email from the server. When I try to save that email data to the second database table, it throws a null pointer exception. It happens because of the Sqlitedatabase db = this.writeabledatabase; statement alwasy becomes null, meaning that the database is unable to fetch the path. This is my code:

  package com.elevenvalues.baig.db2listviewupdatedeleteinsert;
 import android.content.ContentValues;
 import android.content.Context;
 import android.database.sqlite.SQLiteDatabase;
 import android.database.sqlite.SQLiteOpenHelper;

 import java.sql.SQLException;


 public class DbHelper extends SQLiteOpenHelper {


 //  table name
 public static final String EMAIL_INSERT_TABLE = "EmailInsertDetail";
 public static final String EMAIL_LOG = "EmailLog";
 // Database Version
 private static final int DATABASE_VERSION = 1;

 // Database Name
 public static String DATABASE_NAME = "My database";

 public static final String KEY_USERNAME = "username";
 public static final String KEY_EMAIL = "email";
 public static final String KEY_PASSWORD = "password";
 public static final String KEY_SERVER = "server";
 public static final String KEY_PORT = "port";
 public static final String KEY_SECURITY_TYPE = "securityType";
 public static final String KEY_ACTIVE_TIME = "activeTime";
 public static final String KEY_ID = "id";

 public static final String KEY_ROWID = "id";
 public static final String KEY_FROM = "froom";
 public static final String KEY_TO = "too";
 public static final String KEY_MESSAGE_BODY = "messagebody";


 SQLiteDatabase db;
 private boolean isUpdate;
 String id;



 public DbHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);


 }

 @Override
 public void onCreate(SQLiteDatabase db) {


    String CREATE_TABLE = "CREATE TABLE " + EMAIL_INSERT_TABLE + " (" + KEY_ID + " INTEGER PRIMARY KEY, "
            + KEY_USERNAME + " TEXT, " + KEY_EMAIL + " TEXT," + KEY_PASSWORD + " TEXT," + KEY_SERVER + " TEXT," +
            KEY_PORT + " TEXT," + KEY_SECURITY_TYPE + " TEXT," + KEY_ACTIVE_TIME + " TEXT);";


    String CREATE_SERVER_DATABASE = "CREATE TABLE " + EMAIL_LOG + " (" + KEY_ROWID + " INTEGER PRIMARY KEY, "
            + KEY_TO + " TEXT, " + KEY_FROM + " TEXT," + KEY_MESSAGE_BODY + " TEXT);";



    db.execSQL(CREATE_SERVER_DATABASE);
    db.execSQL(CREATE_TABLE);

 }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + EMAIL_INSERT_TABLE);

    db.execSQL("DROP TABLE IF EXISTS " + EMAIL_LOG);

    // create new tables
    onCreate(db);

 }

  void EmailReadDataInsert(String from, String too, String messageBody) {


    db=this.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(DbHelper.KEY_FROM, from);
    cv.put(DbHelper.KEY_TO, too);
    cv.put(DbHelper.KEY_MESSAGE_BODY, messageBody);
    // Inserting Row
    db.insert(EMAIL_LOG, null, cv);

    }


  /**
  * save data into SQLite
  */
  public void AddEmailData(String username,String email,String password,String server,String port,String securityType,
  String   activeTime) {
    db = this.getWritableDatabase();
    ContentValues values = new ContentValues();

    values.put(DbHelper.KEY_USERNAME, username);
    values.put(DbHelper.KEY_EMAIL, email);
    values.put(DbHelper.KEY_PASSWORD, password);
    values.put(DbHelper.KEY_SERVER, server);
    values.put(DbHelper.KEY_PORT, port);
    values.put(DbHelper.KEY_SECURITY_TYPE, securityType);
    values.put(DbHelper.KEY_ACTIVE_TIME, activeTime);

    if (isUpdate) {
        //update database with new data
        db.update(DbHelper.EMAIL_INSERT_TABLE, values, DbHelper.KEY_ID + "=" + id, null);
    } else {
        //insert data into database
        db.insert(DbHelper.EMAIL_INSERT_TABLE, null, values);
    }
    //close database
    db.close();

  } 


  //---opens the database---
  public DbHelper open() throws SQLException
  {
    db = this.getWritableDatabase();
    return this;
  }


  //---closes the database---
  public void close()
  {
    this.close();
 }

 }

The issue occurs in this method where db becomes null:

 void EmailReadDataInsert(String from, String too, String messageBody) {


    db=this.getWritableDatabase();
    ContentValues cv = new ContentValues();
    cv.put(DbHelper.KEY_FROM, from);
    cv.put(DbHelper.KEY_TO, too);
    cv.put(DbHelper.KEY_MESSAGE_BODY, messageBody);
    // Inserting Row
    db.insert(EMAIL_LOG, null, cv);

    }

How do I resolve my problem? Any suggestions?

Answer 1

If you are trying to create multiple tables in a database, you don't have to always create a new database. Your class that manages the entries in your database needs to be tweaked slightly in order to provide for both of your tables. Trying adding this code instead:

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase; 
import android.database.sqlite.SQLiteOpenHelper; 
import android.util.Log;

public class Database {
    static final String databaseName = "My database";
    static final int databaseVersion = 0;
    static final String rowId = “id”; 
    static final String rowUsername = “username”; 
    static final String rowEmail = “email”; 
    static final String rowPassword = "password";
    static final String rowServer = “server”;
    static final String rowPort = "port";
    static final String rowSecurityType = "securityType";
    static final String rowActiveTime = "activeTime";

    static final String emailTable = "EmailInsertDetail";
    static final String logTable = "EmailLog";

    static final String rowFrom = "from";
    static final String rowTo = "to";
    static final String rowMessage = "message";
    final Context context;

    SqlLiteDatabase database;
    DatabaseHelper helper;

    public Database(Context context) {
        this.context = context;
        this.helper = new DatabaseHelper(context);
    }

    private static class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context) {
            super(context, databaseName, null, databaseVersion); 
        }

        @Override
        public void onCreate(SQLiteDatabase database) {
            try {
                database.execSQL("CREATE TABLE IF NOT EXISTS " + emailTable);
                database.execSQL("CREATE TABLE IF NOT EXISTS " + logTable);
            } catch (SQLException ex) {
                  ex.printStackTrace();
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
            Log.w("Database", “Upgrading database from version “ + oldVersion + “ to “ + newVersion + “, which will destroy all old data”);
            db.execSQL(“DROP TABLE IF EXISTS " + emailTable + " (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT, password TEXT, server TEXT, port TEXT, securityType TEXT, activeTime TEXT)");
            db.execSQL("DROP TABLE IF EXISTS " + logTable + " (id INTEGER PRIMARY KEY AUTOINCREMENT, from TEXT, to TEXT, message TEXT)");
            onCreate(database);
         }
    }

    public Database open() throws SQLException {
        this.database = DatabaseHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        DatabaseHelper.close();
    }

     public long insertEmail(String username, String email, String password, String server, String port, String securityType, String activeTime) {
         ContentValues contentValues = new ContentValues();

         contentValues.put(rowUsername, username);
         contentValues.put(rowEmail, email);
         contentValues.put(rowPassword, password);
         contentValues.put(rowServer, server);
         contentValues.put(rowPort, port);
         contentValues.put(rowSecurityType, securityType);
         contentValues.put(rowActiveTime, activeTime);

         return database.insert(emailTable, null, contentValues);
     }

     public long insertData(String from, String to, String message) {
         ContentValues contentValues = new ContentValues();

         contentValues.put(rowFrom, from);
         contentValues.put(rowTo, to);
         contentValues.put(rowMessage, message);

         return database.insert(logTable, null, contentValues);
     }

     public boolean updateEmail(long id, String username, String email, String password, String server, String port, String securityType, String activeTime) {
         ContentValues contentValues = new ContentValues();

         contentValues.put(rowUsername, username);
         contentValues.put(rowEmail, email);
         contentValues.put(rowPassword, password);
         contentValues.put(rowServer, server);
         contentValues.put(rowPort, port);
         contentValues.put(rowSecurityType, securityType);
         contentValues.put(rowActiveTime, activeTime);

         return database.update(emailTable, contentValues, rowId + "=" + id, null) > 0;
     }

     public boolean updateEmail(long id, String from, String to, String message) {
         ContentValues contentValues = new ContentValues();

         contentValues.put(rowFrom, from);
         contentValues.put(rowTo, to);
         contentValues.put(rowMessage, message);

         return database.update(logTable, contentValues, rowId + "=" + id, null) > 0;
     }

     public Cursor getAllEmails() {
         return database.query(emailTable, new String[] {rowId, rowUsername, rowEmail, rowPassword, rowServer, rowPort, rowSecurityType, rowActiveTime}, null, null, null, null, null);
     }

     public Cursor getAllData() {
         return database.query(logTable, new String[] {rowId, rowFrom, rowTo, rowMessage}, null, null, null, null, null);
     }

     public Cursor getEmail(long id) throws SQLException {
         Cursor cursor = db.query(true, emailTable, new String[] {rowId, rowUsername, rowEmail, rowPassword, rowServer, rowPort, rowSecurityType, rowActiveTime}, rowId + “=” + id, null, null, null, null, null);

         if (cursor != null) {
             cursor.moveToFirst();
         }
         return cursor;
     }

     public Cursor getData(long id) throws SQLException {
         Cursor cursor = db.query(true, logTable, new String[] {rowId, rowFrom, rowTo, rowMessage}, rowId + “=” + id, null, null, null, null, null);

         if (cursor != null) {
             cursor.moveToFirst();
         }
         return cursor;
     }
}

In the activity or class you want to insert or retrieve data, type:

Database database = new Database(context);
database.open();

// To insert whatever data you want
long insert = database.insertEmail(parametres go in here);

// To update whatever data you want
if (database.updateEmail(the parametres))
    // more code to be executed

// To retrieve all items in a table
Cursor cursor = database.getAllEmails();

if (cursor.moveToFirst()) {
    do {
        Toast.makeText(this, "Id: " + cursor.getInt(0) + " Email: " + cursor.getString(2), Toast.LENGTH_SHORT).show();
    } while (cursor.moveToNext();
}

// To retrieve a certain item
Cursor cursor = database.getEmail(the id);
    Toast.makeText(this, "Id: " + cursor.getInt(0) + " From: " + cursor.getString(1), Toast.LENGTH_SHORT).show();
if (cursor.moveToFirst()) {

}

database.close();

Remember to replace context with either this, getContext() or a Context variable. Remember to replace the Toast statements with your own code. If you still have problems, be sure to refer to this eBook which should help you or leave some comments below.