Android, handle inserting null using String.format()

Question

How to insert null in SQLite using String.format() like this?

String sql = "INSERT INTO tblCustomers (idCustomer , customerName  )" +
            " VALUES (%d , '%s')";
db.execSQL(String.format(Locale.US,
                sql,
                1, "Ali"));
db.execSQL(String.format(Locale.US,
                sql,
                1, null));// this time it inserts 'null' value as string in database, not (NULL)

Pankaj Kumar · Accepted Answer · 2016-07-12 06:55:43Z

2

null is getting converted into "null" because of String.format(). So you need to only insert the idCustomer field for new row and left customerName field.

For that you need to insert only id field.

String id_sql = "INSERT INTO tblCustomers (idCustomer)" +
            " VALUES (%d)";

db.execSQL(String.format(Locale.US,
                id_sql,
                1));

Rather than using your current way, you need use ContentValues. It will handle your null value also, as you want. So your query would be

  ContentValues values = new ContentValues();
  values.put(COLUMN_ID, valueOfID);
  values.put(COLUMN_NAME, valueOfName);
  values.put(COLUMN_OTHER, valueOfOther);
  // Insert query will be
  long insertId = database.insert(TABLE_NAME, null,
      values);

edited Jul 12, 2016 at 6:55

answered Jul 12, 2016 at 5:47

Pankaj Kumar

83.2k29 gold badges172 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Kamran Akbary Over a year ago

So what about the first query? where I inserted "Ali"? you men I have to have some String.format()?? and check for their nulls???? I don't think it is the best solution.

Pankaj Kumar Over a year ago

Yes you need to check for null before inserting to DB. And if name id empty/ null then I do not think there would be any good reason to insert id only to DB.

Kamran Akbary Over a year ago

I have 25 fields to insert, you mean I check 25 fields, can you imagine how much conditions can happen?? No, I don't agree with you.

Pankaj Kumar Over a year ago

Yes you are correct. But what I say, the way you are using you have to do checks. But there is another way to handle this case. See my update..

Pankaj Kumar Over a year ago

Example at programcreek.com/java-api-examples/…

|

Kamran Akbary · Accepted Answer · 2016-07-23 05:13:25Z

0

Finally I used a simple way that works like a charm!

String sql = "INSERT INTO tblCustomers (idCustomer , customerName  )" +
        " VALUES (%d , %s)";
db.execSQL(Locale.US,
                sql , 
                item.getID(), item.getNameForQuery());

My CustomerObject.java:

    public String getCustomerNameForQuery() {
         return customerName == null ? null : "'"+name+"'";
    }

and as you can see, I moved the single quote from the query string to my object.

answered Jul 23, 2016 at 5:13

Kamran Akbary

3,0734 gold badges34 silver badges64 bronze badges

Collectives™ on Stack Overflow

Android, handle inserting null using String.format()

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related