Creating Multiple tables in SQLite on Android
Hi there i want to create two distinct tables in a database. These tables are User_details and Creditcard_details I have created the following DBAdapter class to implement the database operations, however when i call the insertCreditcard() method the values are not inserted. I wonder if the second table is even being created. Im going wrong somewhere, but cant figure out where and what should i do in order to rectify the issue.
What i was precisely trying to do was, check the id field from the User_details table against the username and password supplied at the login activity and then assign the value of this id variable to another variable called ccid which is used to search rows in the Creditcard_details table or insert values into it.
Can someone please guide me.
package com.androidbook.LoginForm;
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.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;
public class DBAdapter {
/*------------------------User Details ---------------------------------*/
public static Cursor d;
public static final String KEY_ROWID = "_id";
public static final String KEY_Name = "name";
public static final String KEY_Username = "username";
public static final String KEY_Password = "password";
private static final String TAG = "DBAdapter";
private static final String DATABASE_NAME = "Wallet";
private static final String DATABASE_TABLE = "User_Details";
/*------------------------Credit Cards Table----------------------*/
private static final String KEY_CCID = "_ccid";
private static final String KEY_CCUNAME= "cuname";
private static final String KEY_CCNO = "ccno";
private static final String KEY_CVV = "cvvno";
private static final String EXP_DATE = "expdate";
private static final String CREDITCARDS_TABLE = "Creditcard_Details";
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_CREATE =
"create table User_Details (_id integer primary key autoincrement, "
+ "name text not null, username text not null, "
+ "password text not null);";
/*---------------------Create Credit Card Table -------------------------------*/
private static final String CCTABLE_CREATE =
"create table Creditcard_Details ( _ccid integer primary key , "
+ "cuname text not null, ccno text not null, "
+ "cvvno text not null" + "expdate text not null )";//+ "FOREIGN KEY(_ccid) REFERENCES User_Details(_id))";
private final Context context;
public DatabaseHelper DBHelper;
private SQLiteDatabase db;
public DBAdapter(Context ctx)
{
this.context = ctx;
DBHelper = new DatabaseHelper(context);
}
private static class DatabaseHelper extends SQLiteOpenHelper
{
DatabaseHelper(Context context)
{
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db)
{
db.execSQL(DATABASE_CREATE);
db.execSQL(CCTABLE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion,
int newVersion)
{
Log.w(TAG, "Upgrading database from version " + oldVersion
+ " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS titles");
onCreate(db);
}
}
public int Login(String username,String password)
{
try
{
Cursor c = null;
c = db.rawQuery("select * from User_Details where username =" + "\""+ username + "\""+" and password="+ "\""+ password + "\"", null);
c.moveToFirst();
String tempid = c.getString(0);
//Toast.makeText(DBAdapter.this, "correct"+" "+c,Toast.LENGTH_LONG).show();
d= c;//CCview(tempid);
return c.getCount();
}
catch(Exception e)
{
e.printStackTrace();
}
return 0;
}
//-----------------------Display Credit Card -----------------------------
/* public int Getid(String tempid)
{ Cursor c;
c = db.rawQuery("select id from User_Details where username ="
+ "\""+ username + "\"", null);
return Integer.parseInt(c.getString(0));
}*/
public Cursor cursordisplay()
{ return d;
}
public Cursor CCview(long menuid)throws SQLException
{
Cursor mCursor =
db.query(true, CREDITCARDS_TABLE, new String[] {
KEY_CCID,
KEY_CCUNAME,
KEY_CCNO,
KEY_CVV,
EXP_DATE,
},
KEY_CCID + "=" + menuid,
null,
null,
null,
null,
null);
if (mCursor != null) {
mCursor.moveToFirst();
}
return mCursor;
}
//--------------------Entries into Credit Card Table------------------------------------
//---insert a title into the database---
public long insertCreditcard(int i, String j, String k, String l, String date)
{
ContentValues creditValues = new ContentValues();
creditValues.put(KEY_CCID, i);
creditValues.put(KEY_CCUNAME, j);
creditValues.put(KEY_CCNO, k);
creditValues.put(KEY_CVV, l);
creditValues.put(EXP_DATE, date);
return db.insert(CREDITCARDS_TABLE, null, creditValues);
}
//---opens the database---
public DBAdapter open() throws SQLException
{
db = DBHelper.getWritableDatabase();
return this;
}
//---closes the database---
public void close()
{
DBHelper.close();
}
//---insert a title into the database---
public long insertTitle(String name, String username, String password)
{
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_Name, name);
initialValues.put(KEY_Username, username);
initialValues.put(KEY_Password, password);
return db.insert(DATABASE_TABLE, null, initialValues);
}
//---deletes a particular title---
public boolean deleteTitle(long rowId)
{
return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
}
//---retrieves all the titles---
public Cursor getAllTitles()
{
return db.query(DATABASE_TABLE, new String[] {
KEY_ROWID,
KEY_Name,
KEY_Username,
KEY_Password},
null,
null,
null,
null,
null);
}
//---retrieves a particular title---
public Cursor getTitle(long rowId) throws SQLException
{
Cursor mCursor =
db.query(true, DATABASE_TABLE, new String[] {
KEY_ROWID,
KEY_Name,
KEY_Username,
KEY_Password
},
KEY_ROWID + "=" + rowId,
null,
null,
null,
null,
null);
if (mCursor != null) {
mCursor.moveToFirst();
}
return mCursor;
}
//---updates a title---
public boolean updateTitle(long rowId, String name,
String username, String password)
{
ContentValues args = new ContentValues();
args.put(KEY_Name, name);
args.put(KEY_Username, username);
args.put(KEY_Password, password);
return db.update(DATABASE_TABLE, args,
KEY_ROWID + "=" + rowId, null) > 0;
}
}
Answer
As I cannot comment because of my low rating I can surely answer your Question in the comment:
The .getWritableDatabase(); functions gives you a - as the name says - writable Database. All Tables part of the database are now ready to be used
For example:
public Cursor getStuff(String string) {
return db.query(true, TABLE_1, new String[] { KEY_COLUMN1, KEY_COLUMN1,
KEY_COLUMN1 }, KEY_ID + " = ?", new String[] { string },
null, null, null, null);
}
Everything written in Capital letters are String constants defined in my class - because I'm lazy. If you want to query a different Table just replace TABLE_1 with the Table you want