I am trying to create an app that will show children's activities for parents to look at. I have created an SQLite database using an external program and have then imported it into android. With some help, I have then managed to make a class that opens the database and reads it - I think. The database imported with the SQL code such as 'CREATE TABLE Activities' etc. My code for my database class that opens the database is below:
public class Database extends SQLiteOpenHelper {
private static final int VERSION = 1;
private static final String DATABASE_NAME = "ParentTripDB";
private File DATABASE_FILE;
private static final String TABLE_NAME = "Activities";
public static final String ACTIVITY_ID = "ID";
public static final String ACTIVITY_TITLE = "Title";
// This is an indicator if we need to copy the
// database file.
private boolean mInvalidDatabaseFile = false;
private boolean mIsUpgraded = false;
private Context mContext;
private int mOpenConnections = 0;
private Database mInstance;
public Database(Context context) {
super(context, DATABASE_NAME, null, VERSION);
Log.i("databasehelper", "in constructor");
mContext = context;
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.i("databasehelper", "in onCreate");
InputStream is = null;
try {
is = mContext.getResources().openRawResource(R.raw.parent_trip_db);
InputStreamReader isReader = new InputStreamReader(is);
BufferedReader reader = new BufferedReader(isReader);
String initSql="";
String line = "";
line = reader.readLine();
while (line!=null) {
initSql+=line.toString();
Log.i("createDB", initSql);
line = reader.readLine();
}
db.execSQL(initSql);
} catch (IOException e) {
Log.e("createDB", "Error loading init SQL from raw", e);
}
Log.i("databasehelper", "created");
}
@Override
public void onUpgrade(SQLiteDatabase database,
int old_version, int new_version) {
mInvalidDatabaseFile = true;
mIsUpgraded = true;
}
@Override
public synchronized void onOpen(SQLiteDatabase db) {
super.onOpen(db);
Log.i("databasehelper", "db opened");
// increment the number of users of the database connection.
mOpenConnections++;
if (!db.isReadOnly()) {
// Enable foreign key constraints
db.execSQL("PRAGMA foreign_keys=ON;");
}
}
/**
* implementation to avoid closing the database connection while it is in
* use by others.
*/
@Override
public synchronized void close() {
mOpenConnections--;
if (mOpenConnections == 0) {
super.close();
}
}
private void copyDatabase() {
Log.i("databasehelper", "in copy database");
AssetManager assetManager = mContext.getResources().getAssets();
InputStream in = null;
OutputStream out = null;
try {
in = assetManager.open(DATABASE_NAME);
out = new FileOutputStream(DATABASE_FILE);
byte[] buffer = new byte[1024];
int read = 0;
while ((read = in.read(buffer)) != -1) {
out.write(buffer, 0, read);
// Log.i("databasehelper", out.);
out.flush();
}
Log.i("databasehelper", "database copied");
} catch (IOException e) {
} finally {
if (in != null) {
try {
in.close();
} catch (IOException e) {}
}
if (out != null) {
try {
out.close();
} catch (IOException e) {}
}
}
setDatabaseVersion();
mInvalidDatabaseFile = false;
}
public void setDatabaseVersion() {
SQLiteDatabase db = null;
try {
db = SQLiteDatabase.openDatabase(DATABASE_FILE.getAbsolutePath(), null,
SQLiteDatabase.OPEN_READWRITE);
db.execSQL("PRAGMA user_version = " + VERSION);
} catch (SQLiteException e ) {
} finally {
if (db != null && db.isOpen()) {
db.close();
}
}
}
public Cursor fetchAllEventItems(long rowId) {
SQLiteDatabase db = getReadableDatabase();
return db.query(TABLE_NAME, new String[] {ACTIVITY_ID, ACTIVITY_TITLE,
}, ACTIVITY_ID + "=" + rowId, null, null, null, null);
}
}
I don't know how to implement my method or even if my method is correct (the fetchAllEventItems() one). I am trying to correct my method of viewing from the database and then display onto my activity_view_all.xml with my viewAll class attached. Currently I am just using a text view to try to display the titles of the actvities for testing purposes and then, once I know how to do one, I will go on to displaying more of the database in a list view.
My imported database is this:
BEGIN TRANSACTION;
CREATE TABLE 'Review_photos' (
'Photo_ID' INTEGER PRIMARY KEY AUTOINCREMENT,
'Review_ID' INTEGER,
'Photo' TEXT
);
CREATE TABLE 'Review' (
'Review_ID' INTEGER PRIMARY KEY AUTOINCREMENT,
'Activity_ID' INTEGER,
'Title' TEXT,
'Description' TEXT,
'Overall_rating' INTEGER,
'Fun_factor' INTEGER,
'Value_money' INTEGER
);
CREATE TABLE "Activities" (
'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
'Title' TEXT,
'Overview' TEXT,
'Phone_number' INTEGER,
'Web_address' TEXT,
'Address' TEXT,
'Prof_photo' TEXT,
'Disabled_access' TEXT,
'Pram_access' TEXT,
'Toilets' TEXT,
'Baby_changing' TEXT,
'Parking' TEXT,
'Bookable' TEXT,
'Suitable_youngest' INTEGER,
'Suitable_oldest' INTEGER,
'Price' TEXT,
'Category' TEXT,
'Postcode' INTEGER,
'Eating' TEXT,
'Price_list' TEXT,
'Opening_days' TEXT,
'Opening_times' TEXT
);
INSERT INTO 'Activities' VALUES (2,'McDonald''s','Classic, long-running fast-food chain known for its burgers, fries & shakes.',1159814122,'www.mcdonalds.co.uk','55A Radcliffe Road, West Bridgford, Nottinghamshire',NULL,'Y','Y','Y','Y','Y','N',NULL,NULL,'£','Eating','NG2 5FX','Y',NULL,'Everyday','24hrs');
INSERT INTO 'Activities' VALUES (3,'KFC','Fast-food chain known for its buckets of fried chicken, plus wings & sides.',1159861853,'www.kfc.co.uk','Daleside Road, Nottingham',NULL,'Y','Y','Y','Y','Y','N',NULL,NULL,'£','Eating','NG2 3GG','Y',NULL,'Everyday','10:30 - 23:00');
INSERT INTO 'Activities' VALUES (4,'Subway','Casual counter-serve chain for build-your-own sandwiches & salads, with health-conscious options.',1159505621,'www.subway.co.uk','Houndsgate, Nottingham',NULL,'Y','Y','N','N','Y','N',NULL,NULL,'£','Eating','NG1 7AB','Y',NULL,'Everyday','8:00 - 20:00');
INSERT INTO 'Activities' VALUES (5,'Eden Softplay','Eden Softplay is a large indoor play centre with a huge 4-tier soft play frame and separate toddler area.',1159864118,'http://ift.tt/1RR02YM','St Saviours Church, Nottingham',NULL,'Y','Y','Y','Y','Y','Y','',10,'£','Indoor','NG2 3LH','Y','Age 3+: £4, Under 3: £2.50, Under 1: Free','Everyday','9:30 - 18:00');
INSERT INTO 'Activities' VALUES (6,'Pirate''s Play Centre','Fantastic 3 level play area with giant slides, rope bridges, sky glide, rollers and more classic soft play features.',1159603363,'http://ift.tt/1RBR6Rz','Rowley Drive, Sherwood, Nottinghamshire',NULL,'Y','Y','Y','Y','Y','Y',NULL,10,'£','Indoor','NG5 1GD','Y','Age 4+: £4.50 for 2hrs, 1-3 yrs: £3.50 for 2hrs, Under1: £1','Everyday','9:30 - 18:00');
INSERT INTO 'Activities' VALUES (7,'Nottingham Industrial Museum','Nottingham Industrial Museum allows you to explore the different aspects of the Industrial Revolution.',1159153936,'http://ift.tt/1f7Ly1Z','Wollaton Hall, Nottinghamshire',NULL,'Y','N','Y','N','Y','Y',5,16,'£','Indoor','NG8 2AE ','N','Child: £1, Adult: £2','Weekends','11:00 - 17:00');
INSERT INTO 'Activities' VALUES (8,'Attenborough Nature Reserve','Attenborough Nature Centre is an award winning facility surrounded by the waters of the Attenborough Nature Reserve. ',1159721777,'http://ift.tt/1RBR6RC','Beeston, Nottinghamshire',NULL,'Y','Y','Y','Y','Y','N',NULL,NULL,'£','Outdoor','NG9 6DY','N','Free','Everyday','9:00 - 17:00 Weekdays, 9:00 - 18:00 Weekends');
INSERT INTO 'Activities' VALUES (9,'Planet Bounce','Active kids can leap across over 50 interconnected trampolines on the main court, or play dodgeball, basketball and more! ',1159881745,'http://ift.tt/1RR04jb','Huntingdon Street, Nottinghamshire',NULL,'N','Y','Y','Y','N','Y',4,'','£££','Indoor','NG1 3NL','Y','£10 per hr pp','Everyday','10:00 - 22:00');
INSERT INTO 'Activities' VALUES (10,'Wheelgate Adventure Park','A land of thrillig rides plus amazing attractions and exhilarating activities are waiting to be discovered!',1623882773,'http://ift.tt/1lrlDQI','White Post, Newark, Farnsfield, Nottinghamshire',NULL,'Y','Y','Y','Y','Y','Y',NULL,'10 approx.','££','Outdoor','NG22 8HX','Y','Adults: £9 - £15, Kids: £5.50 - £8','Everyday','10:00 - Varies');
COMMIT;
I have looked at many different examples of how to do this but I can't work out how to fit any of them with my code.
Aucun commentaire:
Enregistrer un commentaire