I'm creating a database in android using SQLite.
I wrote my code to create a table called products in onCreate() method.
Then when I call add() method to add some values into the table, I'm getting an error that there is no such table called products.
Here is my SQLiteHelper class :
public class MySQLiteHelper extends SQLiteOpenHelper {
//variable declarations and some code
...
@Override
public void onCreate(SQLiteDatabase db) {
// SQL statement to create a products table
String CREATE_PRODUCTS_TABLE = "CREATE TABLE "+TABLE_NAME+ " ( " +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"url TEXT, " +
"title TEXT, " +
"price INTEGER );";
// create products table
db.execSQL(CREATE_PRODUCTS_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
// Create tables again
onCreate(db);
}
void add(String url, String title, String price) {
this.url = url;
this.title = title;
this.price = price;
// 1. get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
// 2. create ContentValues to add key "column"/value
ContentValues values = new ContentValues();
values.put(KEY_URL, url);
values.put(KEY_TITLE, title);
values.put(KEY_PRICE, price);
// 3. insert
db.insert(TABLE_NAME, // table
null, //nullColumnHack
values); // key/value -> keys = column names/ values = column values
// 4. close
db.close();
}
I read from a similar question that onCreate() is not a constructor and it is called only when database doesn't exist, but database doesn't exists in my case too, so why is it not called?
I also learnt that we need to call getWritableDatabase() or getReadableDatabase()
But I'm not sure where to make such a call in my code.
I tried to put the 'table creating code' which is in onCreate() in my add() method. For first run, I didn't get anything, but from the next time, I keep getting an error that 'a table already exists'.
Now, how can I ensure that table is created properly only once, before I call my add() method to insert some values. Any help please?
EDIT :
My Logcat reads :
03-07 00:59:22.684 2107-2147/com.example.nikhil.amazon1 W/EGL_emulation﹕ eglSurfaceAttrib not implemented
03-07 00:59:22.684 2107-2147/com.example.nikhil.amazon1 W/OpenGLRenderer﹕ Failed to set EGL_SWAP_BEHAVIOR on surface 0xa5683060, error=EGL_SUCCESS
03-07 00:59:24.922 2107-2120/com.example.nikhil.amazon1 I/art﹕ Background sticky concurrent mark sweep GC freed 1464(89KB) AllocSpace objects, 7(2MB) LOS objects, 7% free, 9MB/9MB, paused 75.596ms total 203.616ms
03-07 00:59:25.338 2107-2413/com.example.nikhil.amazon1 E/SQLiteLog﹕ (1) no such table: products
03-07 00:59:25.339 2107-2413/com.example.nikhil.amazon1 E/SQLiteDatabase﹕ Error inserting price= 24,599.00 title=Google Nexus 5 D821 (16GB, Black) url=http://www.amazon.in/Google-Nexus-D821-16GB-Black/dp/B00GC1J55C/ref=sr_1_1?s=electronics&ie=UTF8&qid=1421161258&sr=1-1&keywords=Google
android.database.sqlite.SQLiteException: no such table: products (code 1): , while compiling: INSERT INTO products(price,title,url) VALUES (?,?,?)
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
at com.example.nikhil.amazon1.MySQLiteHelper.add(MySQLiteHelper.java:86)
at com.example.nikhil.amazon1.FirstParsing$1.run(FirstParsing.java:71)
at java.lang.Thread.run(Thread.java:818)
My problem is exactly similar to : 1) Android SQLiteOpenHelper : onCreate() method is not called. Why?
2) SQLiteOpenHelper failing to call onCreate?
3) SQLiteOpenHelper "onCreate" is not called? (the DB does not exist)
But I'm finding it hard to understand it and apply it to my code.
This may help with future development
MySQLiteHelper - Defines Database, tables etc.
public class MySQLiteHelper extends SQLiteOpenHelper {
private static final String FILE_NAME = "application.db";
private static final int DB_VERSION = 1;
private final String TAG = MySQLiteHelper.class.getCanonicalName();
private static SQLiteDatabase database = null;
public MySQLiteHelper(Context context) {
super(context, FILE_NAME, null, DB_VERSION);
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
//version 1
database.execSQL(ProductsTbl.CREATE_SQL);
//can create new tables if necessary, make sure to increase database version
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
Log.i(TAG, "Upgrading from version " + oldVersion + " to version "
+ newVersion);
for (int i = oldVersion + 1; i <= newVersion; i++) {
Log.i(TAG, "version is becoming current " + i);
switch (i) {
case 2:
db.execSQL(ProductsTbl.CREATE_SQL);
break;
//add more cases for each additional table added
}
}
}
public static class ProductsTbl {
public static final String TABLE_NAME = "products";
public static final String ID = "_id";
public static final String URL = "url";
public static final String TITLE = "title";
public static final String PRICE = "price";
public static final String[] TABLE_COLUMNS = { ID, URL, TITLE, PRICE };
public static final String CREATE_SQL = "create table " + TABLE_NAME
+ "(" + ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ URL + " TEXT,"
+ TITLE + " TEXT,"
+ PRICE + " INTEGER);";
}
}
MySQLiteDatasource - Defines access methods, Insert, Delete, etc.
public class MySQLiteDatasource {
//Database fields
private SQLiteDatabase database;
private MySQLiteHelper dbHelper;
public MySQLiteDatasource(Context context) {
dbHelper = new MySQLiteHelper(context);
}
public void open() throws SQLException {
database = dbHelper.getWritableDatabase();
}
public void close() {
dbHelper.close();
}
public void insertProduct(String url, String title, String price) {
ContentValues values = new ContentValues();
values.put(MySQLiteHelper.ProductsTbl.URL, url);
values.put(MySQLiteHelper.ProductsTbl.TITLE, title);
values.put(MySQLiteHelper.ProductsTbl.PRICE, price);
try {
database.insertOrThrow(MySQLiteHelper.ProductsTbl.TABLE_NAME, null, values);
} catch (SQLiteConstraintException e) {
//System.out.println(e);
Log.e("SQLite Database", "Unable to INSERT into Database, possible duplicate topic already exists.");
}
}
}
Here is how you could use the 2 classes inside an activity or something similar.
public static void addProductToDB(String url, String title, String price) {
MySQLiteDatasource datasource = new MySQLiteDatasource(this);
datasource.open();
datasource.insertProduct(url, title, price);
datasource.close();
}
You may also want to look into defining a Product object to hold data that is obtained from the database at a later point.