To add a database, after designing the database (lets say you want to store an identifier for the user (which may be in another user table), obviously the bmi and for historical references the date and perhaps time that the bmi is for) then:-
Create the Database helper (DBHelper.java for demonstration)
The first step, if using SQLite on an Android device (assumes a reltively simple approach taking advantage of the SQLiteOpenHelper class), would be to create a sub-class of the SQLiteOpenHelper (aka your DBHelper) i.e. A class that extends SQLiteOpenHelper. e.g. :-
public class DBhelper extends SQLiteOpenHelper {
...... stuff to be added as yet
}
You will need a suitable constructor that will pass the context, the database name, the factory method (null will do), and the database version.
So the above could then become :-
public class DBHelper extends SQLiteOpenHelper {
public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
}
However, such a class must include overides for the onCreate and onUpgrade methods.
- the onCreate method is called when the Database has just been created (i.e. the first time getReadableDatabase or getWritabledatabase is called).
- Note once the database has been created the
onCreate method is not called. That is onCreate IS NOT called everytime you run the App as once the Database is created a file is created (data/data//databases/)
- Changing the code in the
onCreate method WILL NOT change the database unless the database is deleted (there other ways).
- This is a very common issue, hence why it is explained.
So the above could now be :-
public class DBHelper extends SQLiteOpenHelper {
public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
Assuming that the database is going to be called bmirecorder.db and that it will have one table, which will be named bmirecord and that there will be three fields(columns) for each entry(row), namely record_userid, record_date and record_bmi. The it is advisable to have these names defined in a single place and that the name is always obtained from that single place and that the single place equates to a constant then we should add :-
public static final String DBNAME = "bmirecorder.db";
public static final int DBVESRION = 1;
public static final String TABLE_BMIRECORD = "bmirecord";
public static final String COLUMN_BMIRECORD_USERID = "record_userid";
public static final String COLUMN_BMIRECORD_DATE = "record_date";
public static final String COLUMN_BMIRECORD_BMI = "record_bmi";
- The above will be added in due course
As we know that the database will always be the same and that the database version will not change much and that we are not going to utilise the factory. The constructor can be simplified to just need the Context passed.
So the above code could be changed to :-
public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "bmirecorder.db";
public static final int DBVESRION = 1;
public static final String TABLE_BMIRECORD = "bmirecord";
public static final String COLUMN_BMIRECORD_USERID = "record_userid";
public static final String COLUMN_BMIRECORD_DATE = "record_date";
public static final String COLUMN_BMIRECORD_BMI = "record_bmi";
public DBHelper(Context context) {
super(context, DBNAME, null, DBVESRION);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
- So the constants have been added and some used (DBNAME and DBVERSION)
- To create an instance, in an activity you would code something like DBHelper myDBHelper = new DBHelper(this);
- The above would work and actually create an empty database (but only if an attempt is made to use getReadableDatabase or getWritableDatabase).
To make it so that the bmirecord table is created we can add some code to create the table in the onCreate method. Such as:-
String crtsql = "CREATE TABLE IF NOT EXISTS " + TABLE_BMIRECORD +
"(" +
COLUMN_BMIRECORD_USERID + " INTEGER," +
COLUMN_BMIRECORD_DATE + " TEXT DEFAULT CURRENT_TIMESTAMP," +
COLUMN_BMIRECORD_BMI + " REAL NOT NULL" +
")";
sqLiteDatabase.execSQL(crtsql)
So the code now becomes :-
public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "bmirecorder.db";
public static final int DBVESRION = 1;
public static final String TABLE_BMIRECORD = "bmirecord";
public static final String COLUMN_BMIRECORD_USERID = "record_userid";
public static final String COLUMN_BMIRECORD_DATE = "record_date";
public static final String COLUMN_BMIRECORD_BMI = "record_bmi";
public DBHelper(Context context) {
super(context, DBNAME, null, DBVESRION);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
String crtsql = "CREATE TABLE IF NOT EXISTS " + TABLE_BMIRECORD +
"(" +
COLUMN_BMIRECORD_USERID + " INTEGER," +
COLUMN_BMIRECORD_DATE + " TEXT DEFAULT CURRENT_TIMESTAMP," +
COLUMN_BMIRECORD_BMI + " REAL NOT NULL" +
")";
sqLiteDatabase.execSQL(crtsql);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
The above will create a database, when/if an attempt is made to use getWritableDatabase or getReadableDatabase, but not if only creating an instance of the DBHelper.
Modify your MainActivity
To do both you could chage your MainActivity to include :-
DBHelper myDBHelper = new DBHelper(this); //<<<< Creates an instance of the DBHelper
SQLiteDatabase db = myDBHelper.getWritableDatabase(); // Invoke getWritabelFatabase to create the database
e.g.
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
height = (EditText) findViewById(R.id.height);
weight = (EditText) findViewById(R.id.weight);
result = (TextView) findViewById(R.id.result);
DBHelper myDBHelper = new DBHelper(this);
SQLiteDatabase db = myDBHelper.getWritableDatabase();
}
Running the App would now create the database.
How to see if the database exists
If you Create a new class called CommonSQLiteUtilities and then copy the code from this link and then add the following line CommonSQLiteUtilities.logDatabaseInfo(db); as per :-
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
height = (EditText) findViewById(R.id.height);
weight = (EditText) findViewById(R.id.weight);
result = (TextView) findViewById(R.id.result);
DBHelper myDBHelper = new DBHelper(this);
SQLiteDatabase db = myDBHelper.getWritableDatabase();
CommonSQLiteUtilities.logDatabaseInfo(db);
}
and run the App the following will appear in the log :-
05-10 06:07:29.002 1300-1300/bmirecorder.mybmirecorder D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/bmirecorder.mybmirecorder/databases/bmirecorder.db
Database Version = 1
05-10 06:07:29.006 1300-1300/bmirecorder.mybmirecorder D/SQLITE_CSU: Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
Table Name = bmirecord Created Using = CREATE TABLE bmirecord(record_userid INTEGER,record_date TEXT DEFAULT CURRENT_TIMESTAMP,record_bmi REAL NOT NULL)
Table = bmirecord ColumnName = record_userid ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 0
Table = bmirecord ColumnName = record_date ColumnType = TEXT Default Value = CURRENT_TIMESTAMP PRIMARY KEY SEQUENCE = 0
Table = bmirecord ColumnName = record_bmi ColumnType = REAL Default Value = null PRIMARY KEY SEQUENCE = 0
Confirming that the Database exists and includes the bmirecord table (android_metadata tables is created by the SQLiteOpenHelper and is used to store the locale).
