Using String.format() to create a sqlite table

Question

Stack Overflow,

I am trying to understand how to use String.format() to create a sqlite database for my Android app.

I have a public class that extends SQLiteOpenHelper and a private inner class that contains private static final strings which I intend to be the column names for my database.

Music Database Class Code

public class SaturnMusicDatabase extends SQLiteOpenHelper{
    private Context mContext;

   // Necessary public constructor to use SQLiteOpenHelper 
    public SaturnMusicDatabase(Context context){
        super(context, "SaturnMusic.db"), null,1);
        mContext = context;
    }
    // The columns beginning with an underscore are the primary key
private static final class Songs{
     private static final String TABLE = "songs";
     private static final String COL_ARTIST ="_artist";
     private static final String COL_ALBUM="_album";
     private static final String COL_TITLE ="_title";
     private static final String COL_GENRE = "genre";
     private static final String COL_LENGTH ="length";
     private static final String COL_Number ="number";
 }
@Override
 public void onCreate(SQLiteDatabase db) {
     String queryMakeSong = String.format
             ("create table %s (primary key(%s, %s, %s)%s, %s, %s)",
                     Songs.TABLE, Songs.COL_ARTIST, Songs.COL_ALBUM, Songs.COL_TITLE,
                    Songs.COL_GENRE, Songs.COL_LENGTH, Songs.COL_Number);
     db.execSQL(queryMakeSong);
 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 }
} 

My confusion is with the create statement. The way I understand how String.format() works is that the % sign followed by a modifier determines the data type to use when formatting the data that is passed into the function.

My intention is to create a table named songs that has six fields, three of which are primary keys: Artist, Album, and Title.

It makes sense that the field names should be strings, so that is why I chose to use the 's' modifier. Does this mean that the data that will be stored in these fields will be strings? Or does it mean that the field names are strings and I have to specify what data type these fields should hold in the create table statement?

If the former, that is not what I want for all of my fields. Things like LENGTH and NUMBER should be storing integers. Which makes me think that they should use the 'd' modifier.

Please share if any of my syntax is wrong, and how I can correct it.

Answer 1

You shouldn't. Use bind variables instead. Using String.format leaves you vulnerable to SQL injection security bugs, bind variables avoid that. If you aren't doing a dynamic query (like for a create table) then just put in the raw SQL rather than doing a format, it will be more readable down the line when maintaining the query.

But to answer your question- the symbols in format have nothing to do with the data types stored in it in the db, because String.format doesn't know anything about the database, or that its being used for a database query. It just outputs formatted strings. If the actual value you're putting into that string is a number, use %d. If its a string like a table name, column name, etc, use %s.