CODEWITHSUNDEEP
androidsqlite
0xFrenky
0xFrenky

Reputation: 53

Why Android still input null/empty string in NOT NULL column on SQLite?

I create a table 'PLAN' in SQLite and have column 'NAME' set to TEXT NOT NULL, i try to insert to that table with value from EditText. When the EditText have no value and i click submit the value is inserted as empty string to the column. What happen? I have created simple data validation as below but still the empty value still inserted. I use ContentValues to execute the query.

DatabaseHelper

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

public class DatabaseHelper extends SQLiteOpenHelper {
// Logcat tag
public static final String LOG = "SODatabaseHelper";

// Database Version
public static final int DATABASE_VERSION = 1;

// Database Name
public static final String DATABASE_NAME = "Planner.db";

// Table Names - column names
public static final String TABLE_PLAN = "Plan";
public static final String TABLE_CATEGORY = "Category";
public static final String TABLE_GEAR = "Gear";
public static final String TABLE_GEARSET = "GearSet";
public static final String TABLE_GEARSETSHAVEGEAR = "GearSetsHaveGear";
public static final String TABLE_PLANHAVEGEAR = "PlanHaveGear";

// PLAN Table - column names
public static final String KEY_PLANID = "planID";
public static final String COLUMN_PLANCATID = "planCatID";
public static final String COLUMN_PLANNAME = "planName";
public static final String COLUMN_PLANLOCATION = "planLocation";
public static final String COLUMN_DATECREATED = "planCreated";
public static final String COLUMN_PLANSUMMARY = "planSummary";
public static final String COLUMN_DATESTART = "planStart";
public static final String COLUMN_DATEEND = "planEnd";

// CATEGORY - column names
public static final String KEY_CATID = "catID";
public static final String COLUMN_CATNAME = "catName";
public static final String COLUMN_CATDESC = "catDescription";

// GEAR Table - column names
public static final String KEY_GEARID = "gearID";
public static final String COLUMN_GEARNAME = "gearName";
public static final String COLUMN_GEARDESC = "gearDescription";

// GEARSET Table - column names
public static final String KEY_GEARSETID = "gearSetID";
public static final String COLUMN_GEARSETNAME = "gearSetName";
public static final String COLUMN_GEARSETDESC = "gearSetDescription";

// PLANHAVEGEARSET Table - column names
public static final String KEY_PHG_PLANID = "phgPlanID";
public static final String KEY_PHG_GEARSETID = "phgGearSetID";

// GEARSETHAVEGEAR Table - column names
public static final String KEY_GHG_GEARSETID = "ghgGearSetID";
public static final String KEY_GHG_GEARID = "ghgGearID";

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

// TABLE CREATIONS
private static final String CREATE_TABLE_PLAN = "CREATE TABLE "
        + TABLE_PLAN + "("
        + KEY_PLANID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + COLUMN_PLANCATID + " INTEGER NOT NULL, "
        + COLUMN_PLANNAME + " TEXT NOT NULL, "
        + COLUMN_PLANLOCATION + " TEXT NULL, "
        + COLUMN_DATECREATED + " STRING NOT NULL, "
        + COLUMN_PLANSUMMARY + " TEXT NULL, "
        + COLUMN_DATESTART + " STRING NULL, "
        + COLUMN_DATEEND + " STRING NULL" + ")";

private static final String CREATE_TABLE_CATEGORY = "CREATE TABLE "
        + TABLE_CATEGORY + "("
        + KEY_CATID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + COLUMN_CATNAME + " TEXT NOT NULL, "
        + COLUMN_CATDESC + " TEXT NULL" + ")";

private static final String CREATE_TABLE_GEAR = "CREATE TABLE "
        + TABLE_GEAR + "("
        + KEY_GEARID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + COLUMN_GEARNAME + " TEXT NOT NULL, "
        + COLUMN_GEARDESC + " TEXT NULL" + ")";

private static final String CREATE_TABLE_GEARSET = "CREATE TABLE "
        + TABLE_GEARSET + "("
        + KEY_GEARSETID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + COLUMN_GEARSETNAME + " TEXT NOT NULL, "
        + COLUMN_GEARSETDESC + "TEXT NULL" + ")";

private static final String CREATE_TABLE_GEARSETHAVEGEAR = "CREATE TABLE "
        + TABLE_GEARSETSHAVEGEAR + "("
        + KEY_GHG_GEARID + " INTEGER NOT NULL, "
        + KEY_GHG_GEARSETID + " INTEGER NOT NULL" + ")";

private static final String CREATE_TABLE_PLANHAVEGEARSET = "CREATE TABLE "
        + TABLE_PLANHAVEGEAR + "("
        + KEY_PHG_PLANID + " INTEGER NOT NULL, "
        + KEY_PHG_GEARSETID + " INTEGER NOT NULL" + ")";

@Override
public void onCreate(SQLiteDatabase db) {
    // creating required tables
    db.execSQL(CREATE_TABLE_PLAN);
    db.execSQL(CREATE_TABLE_CATEGORY);
    db.execSQL(CREATE_TABLE_GEAR);
    db.execSQL(CREATE_TABLE_GEARSET);
    db.execSQL(CREATE_TABLE_PLANHAVEGEARSET);
    db.execSQL(CREATE_TABLE_GEARSETHAVEGEAR);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    Log.w(DatabaseHelper.class.getName(),
            "UPDATING DATABASE FROM VERSION " + oldVersion + " TO VERSION " + newVersion
                    + " WHICH DESTROY ALL DATA.");

    // on upgrade drop older tables
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_PLAN);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORY);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_GEAR);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_GEARSET);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_GEARSETSHAVEGEAR);
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_PLANHAVEGEAR);

    // create new tables
    onCreate(db);
}
// TODO: Create each object data source class
}

Plan Datasource Class

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.SQLiteException;

import org.joda.time.DateTime;

import java.util.ArrayList;
import java.util.List;

public class PlanDataSource {
private SQLiteDatabase db;
private DatabaseHelper databaseHelper;
private String[] allColumns = {
        DatabaseHelper.KEY_PLANID,
        DatabaseHelper.COLUMN_PLANCATID,
        DatabaseHelper.COLUMN_PLANNAME,
        DatabaseHelper.COLUMN_PLANLOCATION,
        DatabaseHelper.COLUMN_DATECREATED,
        DatabaseHelper.COLUMN_PLANSUMMARY,
        DatabaseHelper.COLUMN_DATESTART,
        DatabaseHelper.COLUMN_DATEEND
};

/**
 * PlanDataSource constructor
 * @param context Context
 */
public PlanDataSource(Context context) {
    databaseHelper = new DatabaseHelper(context);
}

public void open() throws SQLException {
    db = databaseHelper.getWritableDatabase();
}

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

/**
 * Method for inserting new plan to the database
 * @param newPlan plan class object, as Plan
 * @return true if the plan successfully inserted to database
 * @throws android.database.SQLException if there are invalid input
 */
public Boolean insertPlan(Plan newPlan) {
    ContentValues contentPlan = new ContentValues();

    contentPlan.put(DatabaseHelper.COLUMN_PLANCATID, newPlan.getCatId());
    contentPlan.put(DatabaseHelper.COLUMN_PLANNAME, newPlan.getName());
    contentPlan.put(DatabaseHelper.COLUMN_PLANLOCATION, newPlan.getDestination());
    contentPlan.put(DatabaseHelper.COLUMN_DATECREATED, newPlan.getCreated().toString());
    contentPlan.put(DatabaseHelper.COLUMN_PLANSUMMARY, newPlan.getSummary());

    if (newPlan.getStart() != null) {
        contentPlan.put(DatabaseHelper.COLUMN_DATESTART,
                newPlan.getStart().toString());
    }

    if (newPlan.getEnd() != null) {
        contentPlan.put(DatabaseHelper.COLUMN_DATEEND,
                newPlan.getEnd().toString());
    }

    try {
        db.insert(DatabaseHelper.TABLE_PLAN, null, contentPlan);
        return true;

    } catch (SQLiteException ex) {
        throw new SQLiteException("Error performing insertPlan()");
    } finally {
        db.close();
    }
}

/**
 * Method for deleting one plan based on its id
 * @param planId primary key of the plan
 * @return true if plan is successfully deleted
 * @throws android.database.SQLException if there is an error
 */
public Boolean deletePlan(int planId) {
    try {
        /** try delete from TABLE_PLAN */
        db.delete(
                DatabaseHelper.TABLE_PLAN,
                DatabaseHelper.KEY_PLANID + "=" + planId,
                null);

        /** returning true value is the action is success */
        return true;
    } catch (SQLException ex) {
        /** throw the exception */
        throw new SQLException("Error performing deletePlan()");
    } finally {
        /** close the database connection */
        db.close();
    }
}

/**
 * Method to get all plan from the database
 * @return List of all Plan from the database
 */
public List<Plan> getAllPlans() {
    List<Plan> planList = new ArrayList<Plan>();

    Cursor cursor = db.query(DatabaseHelper.TABLE_PLAN,
            allColumns, null, null, null, null, null);

    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        Plan plan = cursorToPlan(cursor);
        planList.add(plan);
        cursor.moveToNext();
    }

    /** closing the database connection */
    cursor.close();

    /** return the list */
    return planList;
}

public Plan getPlan(int planId) {
    Plan plan = null;
    Cursor cursor = db.query(
            DatabaseHelper.TABLE_PLAN,
            allColumns,
            DatabaseHelper.KEY_PLANID + "=?",
            new String[]{String.valueOf(planId)},
            null, null, null, "1");

    cursor.moveToFirst();
    if (!cursor.isAfterLast()) {
        plan = cursorToPlan(cursor);
    }

    /** closing the database connection */
    db.close();
    cursor.close();

    /** return the plan */
    return plan;
}

/**
 * Method to convert from cursor object to Plan object
 * @param cursor the database query adapter
 * @return Plan class object based information from the cursor
 */
private Plan cursorToPlan(Cursor cursor) {
    return new Plan(
            cursor.getInt(0),
            cursor.getInt(1),
            cursor.getString(2),
            cursor.getString(3),
            new DateTime(cursor.getString(4)),
            cursor.getString(5),
            new DateTime(cursor.getString(6)),
            new DateTime(cursor.getString(7))
    );
}
}

And this is to retrieve value from view

case R.id.action_planeditor_saveplan:
            planDataSource = new PlanDataSource(PlanEditorActivity.this);
            planDataSource.open();

            try {
                Plan newPlan = new Plan(
                    Integer.valueOf(
                            planCategorySpinner.getSelectedItem().toString()),
                    planNameEditText.getText().toString(),
                    planLocationEditText.getText().toString(),
                    DateTime.now(),
                    planSummaryEditText.getText().toString(),
                    startDate,
                    endDate);

                planDataSource.insertPlan(newPlan);
                Toast.makeText(PlanEditorActivity.this, "Success", Toast.LENGTH_SHORT).show();
                setResult(1, null);
                finish();
                return true;

            } catch (SQLiteException ex) {
                Toast.makeText(getBaseContext(), ex.getMessage(), Toast.LENGTH_LONG).show();
                return false;
            } finally {
                planDataSource.close();
            }


public void setName(String name) {
    if (name.trim().equals("") || name.isEmpty())
        this.name = null;
    this.name = name;
}

Please help.

Upvotes: 0

Views: 4101

Answers (2)

NigelK
NigelK

Reputation: 8480

Your column NAME is defined as NOT NULL. So when your EditText has no value, you have no choice but to store that as an empty string.

If your intention is store an empty string as NULL, you will need to remove NOT NULL from your column definition. Then, when you are setting up your ContentValues, check the string you are about to save. If it is null or empty, use this instead:

contentPlan.putNull(DatabaseHelper.COLUMN_PLANNAME);

Also note that an empty string is NOT a null value as far as SQLite is concerned. An empty string is still a string and counts as text. Having NOT NULL simply means you cannot set it to NULL as per the statement above or give it no value on an insert.

Upvotes: 0

Stefan de Bruijn
Stefan de Bruijn

Reputation: 6319

You are always still accepting the argument String name.

When an empty name is given, you set this.name on NULL, but after that it continues with this.name = name; nevertheless.

You should try to add proper brackets in if/else constructions to make it more clear for yourself and others;

What you want is something like this

public void setName(String name) {
    if(name.trim().equals("") || name.isEmpty()){
       this.name = null;
     }else{
       this.name = name;
    }
}

Upvotes: 2

Related Questions